Re: comparing rows

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> btw, it appears that SQL99 (haven't checked SQL92) specifies that

> test=# select (1,2,3) = (1,2,null);
>  ?column? 
> ----------
> (1 row)

> should return FALSE, not NULL.

What?  If so, they broke it pretty badly.  This should be equivalent to
1 = 1 AND 2 = 2 AND 3 = NULL, which should reduce to TRUE AND TRUE AND NULL,
which should reduce to NULL.  Anything else is not self-consistent.

>> Summary of MS:
>> When it runs in ANSI mode, null != null.

> *sigh* If it actually *had* an ANSI mode, then "foo = NULL" would be
> rejected. Period.

Well, mumble, that is an overly literal interpretation of the spec if
you ask me.  It is not unreasonable to allow NULL as a literal constant,
especially since it doesn't create any issues that you can't get to with
100%-plain-vanilla-SQL92 constructs likeCASE WHEN TRUE THEN NULL END
Where MS blew it was in not following SQL92-compatible semantics of
operations on nulls.  (We can't throw *too* many stones, since we had
a number of problems with logical ops on nulls too, up till 7.0 ...)

> afaict the option will be "M$" vs "published standards" support, and it
> seems the wrong way to head.

I don't want an option either.  I want to change our code (back to) SQL
compliant semantics of NULL comparisons, ie remove the parser kluge.
        regards, tom lane