""Adam"" <s3057043@...> wrote in message
news:<ea6v34+o2fs@...>...

> Hello Group,
>
> I have what I believe may be a bug with null in a view in Firebird
> 1.5.3 (though happy to be proven incorrect). If within a view one uses
> a left outer join and returns a field from the right table (defined as
> a domain with a not null constraint), the view incorrectly returns a 0
> instead of <null>.
>
> The following code should duplicate the problem in iSQL.
>
> ---
>
> CREATE DOMAIN D INTEGER NOT NULL;
> CREATE TABLE T (A D);
> CREATE TABLE U (B D);
> CREATE VIEW V (A, B) AS
> SELECT T.A, U.B FROM T LEFT JOIN U ON (T.A = U.B);
>
> COMMIT;
>
> INSERT INTO T VALUES(1);
> COMMIT;
>
> SELECT * FROM V;
>
> ---
>
> The following is returned:
>
> A B
> ============ ============
>
> 1 0
>
> I believe the above is incorrect, but should rather be:
>
> A B
> ============ ============
>
> 1 <null>
>
>
> Notes / Observations:
>
> * This bug looks similar to the bug listed here which was apparently
> fixed in 1.0
> http://tracker.firebirdsql.org/browse/CORE-514
>

This is exactly the bug that I solved five years ago. I remember having
asked Arno or Dmitry in the last three years if one of their DSQL changes
would defeat the fix and they said NO. At least I now know that undoing my
change (that was marking view fields as nullable) was bad idea.
:-(

DSQL cache doesn't have enough information to decide, hence it's safe to
simply tell it that any view field can be nullable. This solves problems
with having left joins, views based on views, etc.

C.