Submitted by: Adam Gardner (s3057043)

Assigned to: Claudio Valderrama C. (robocop)

Duplicates CORE514
Is related to QA92

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 script is at end of post.

Notes / Observations:
* This bug looks similar to the bug listed here which was apparently fixed in 1.0
CORE514
* There is a pretty simple work around, simply use a cast in the view definition, simply cast the field as the base datatype
* This bug only occurs if the field is defined as a domain, and that domain has a not null constraint.

Related Support List Post:
http://groups.yahoo.com/group/firebird-support/message/78126 (archive)

Script to Duplicate:

--
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;

--

Commits: 8b74a50