Re: BUG #5053: domain constraints still leak

On Mon, Sep 14, 2009 at 11:16:23AM -0400, Robert Haas wrote:
> I haven't read the code in this area, but for what it's worth, I guess
> I lean toward the view that treating a row of NULLs as being the same
> thing as an undecorated NULL does not make very much sense.

I agree; when compared to most languages it doesn't. When compared
to the semantics of the other operators in SQL it gets better. I
personally think PG should strive to be internally consistent rather
than consistency with other (non-SQL based) languages.

> If I have
> a table row which contains (1, NULL, NULL) and I update the first
> column to be NULL, I feel like I now have (NULL, NULL, NULL), not just
> NULL. Every other programming language I'm aware of makes this
> distinction - for good reasons - and I don't really see any reason why
> SQL should do anything different.

I'm not aware of any other language that does the automatic "lifting"
(to borrow nomenclature from Haskell) that SQL does, allowing NULL
appear in *every* type. Java, for example, has null references,
but these are very different creatures from nulls in databases--the
programmer has to explicitly deal with them all the time and also they
only apply to references. Taken another way, each object in a normal
imperative language has its own identity, but in a database two rows
that "look" the same are the same. Thirdly, IS NULL is defined to look
"inside" composite values to see if they're "really" null. Its these
differences in semantics that seem to make it all OK.

> Under that view, null::test is not itself a test, but denotes the
> absence of one.

OK, but how can you distinguish NULL from ROW(NULL,NULL)?

SELECT v IS NULL, v.a, v.b
FROM (SELECT NULL, NULL) v(a,b);

Would appear to return the same thing if ROW(NULL,NULL) evaluated to
NULL or not. The only time it would show up is when you're trying to
save the value into a table and I think this would tend to do the right
thing more often. For example:

INSERT INTO t (id,rv)
SELECT f.id, b
FROM foo f
LEFT JOIN bar b ON (f.id = b.id);

Would fail if any bar's didn't exist, whereas the current behavior is
to insert a row with rv containing all null values. You can't test for
this case because IS NULL would return the "wrong" thing as it looks
inside composites.

--
Sam http://samason.me.uk/