Re: comparing rows

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)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 like
CASE 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