Hi Adam,

> 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
>
> * There is a pretty simple work around, simply use a cast in the view
> definition, eg:
>
> CREATE VIEW V (A,B) AS
> SELECT T.A, CAST(U.B AS INTEGER) FROM T LEFT JOIN U ON (T.A = U.B);
>
> * This bug does not occur if the field is not defined as a domain with
> a not null constraint. In all other cases I have tested, Firebird
> behaves the way I expect.
>
> * I have not tested whether this is still an issue in FB 2, but I did
> not see any reference in the release notes.

The same behaviour is there in Firebird 2.0 RC3.



Regards,
Thomas