Skip site navigation (1) Skip section navigation (2)

Re: comparing rows

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
Cc: Magnus Hagander <mha(at)sollentuna(dot)net>, Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: comparing rows
Date: 2000-08-03 14:07:24
Message-ID: 7503.965311644@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2000-08-03 14:10:06
Subject: RE: comparing rows
Previous:From: Magnus HaganderDate: 2000-08-03 14:00:37
Subject: RE: comparing rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group