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/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs