This is what the doc promises. But how can you see it as anything but a bug? The subquery evaluates to "null", and only then is the attempt made to create a new row which self-evidently violates the domain's constraint. How is it any different from this:
Because NULL is doing double-duty. The absence of any possible value is represented by null, as is knowing that a value exists but not knowing what that value is. The most obvious example of this problem is:
(not tested, may have typos)
SELECT a.vala, b.valb, b.lbl
FROM (values (1)) AS a (vala)
LEFT JOIN (values (2, null)) AS b (valb, lbl) ON vala = valb;
vs.
SELECT a.vala, b.valb, b.lbl
FROM (values (1)) AS a (vala)
LEFT JOIN (values (1, null)) AS b (valb, lbl) ON vala = valb;
The resultant value of "b.lbl" is null in both queries, but not for the same reason (left join missing semantics pre-empt null type value semantics).
So, yes, queries can produce NULL even for domains that are defined not null. If you want protection from null values in your database tables you must define your columns to not accept nulls. It becomes a habit and can be readily checked for in the catalogs (I suggest adding a column comment for why columns defined as null are allowed to be null, then you can query the column contents to exclude those columns where null was intended - or go with your idea and just report every column as non-conforming. COMMENT ON table.column IS '@NULLABLE - optional information the customer might not provide').
David J.
