Re: When is a record NULL? - Mailing list pgsql-hackers

From Tom Lane
Subject Re: When is a record NULL?
Date
Msg-id 26379.1248444070@sss.pgh.pa.us
Whole thread Raw
In response to Re: When is a record NULL?  (Joshua Tolley <eggyknap@gmail.com>)
List pgsql-hackers
Joshua Tolley <eggyknap@gmail.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
ofdegree 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


pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: join removal
Next
From: "Kevin Grittner"
Date:
Subject: Re: query decorrelation in postgres