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

* 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.

Is my head on straight today?

Adam