From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Joshua Tolley <eggyknap(at)gmail(dot)com> |
Cc: | "David E(dot) Wheeler" <david(at)kineticode(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: When is a record NULL? |
Date: | 2009-07-24 14:01:10 |
Message-ID: | 26379.1248444070@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Joshua Tolley <eggyknap(at)gmail(dot)com> writes:
> On Thu, Jul 23, 2009 at 06:46:25PM -0700, David E. Wheeler
>> Yes, but given that the standard says that `ROW(1, NULL)` is NULL, then I
>> would expect it to be NOT DISTINCT from `ROW(2, NULL)`.
> Wait, didn't we decide upthread that the standard said ROW(1, NULL) isn't
> NULL?
David misspoke in the quoted statement, as I believe he figured out soon
thereafter. For that row value, neither IS NULL nor IS NOT NULL will
return true. The spec defines them in such a way that they are not inverses
for row values.
SQL2008 points out:
NOTE 219 - For all R, "R IS NOT NULL" has the same result as
"NOT R IS NULL" if and only if R is of degree 1. Table 14,
"<null predicate> semantics", specifies this behavior.
That table looks like this:
R IS R IS NOT NOT R IS NOT R IS NOT
_Expression_______NULL____NULL__________NULL__________NULL_________
| degree 1: null | true_ | false_ | false_ | true_ |
| | | | | |
| degree 1: not | false_| true_ | true_ | false_ |
null
| degree > 1: | true_ | false_ | false_ | true_ |
| all null | | | | |
| | | | | |
| degree > 1: | false_| false_ | true_ | true_ |
| some null | | | | |
| | | | | |
| degree > 1: | false_| true_ | true_ | false_ |
|_none_null______|_______|_____________|____________|______________|
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2009-07-24 14:02:38 | Re: query decorrelation in postgres |
Previous Message | Robert Haas | 2009-07-24 13:37:14 | Re: join removal |