Re: When is a record NULL?

On Thu, 2009-07-23 at 17:32 -0700, David E. Wheeler wrote:
> On Jul 23, 2009, at 4:22 PM, Tom Lane wrote:
>
> > Dunno how you can possibly come to that conclusion. Those row
> > values are certainly distinct (according to both PG and the spec).
>
> Are they not both null?

[ Is that a play on words? ;) ]

Here's the logical breakdown of your argument:

x = ROW(1, NULL)

0. x IS NOT NULL = false
1. => NOT x IS NOT NULL = true
2. => NOT NOT x IS NULL = true
3. => x IS NULL = true

However, in step 2, you transformed:
x IS NOT NULL => NOT x IS NULL

But in SQL that is not a tautology!

I don't think it's wise to assume SQL is consistent. I think it is
possible to create a consistent 3VL system, but you have to give up some
other very important property. I can't remember the details at the
moment, but there's an interesting proof in "Logic and Databases" by
C.J. Date.

Does the SQL spec lay out the tautologies anywhere, so that we can get a
clear picture of what's going on with NULLs? I won't make the claim that
SQL is inconsistent without actually seeing the system as a whole, but,
at a minimum, many of the tautologies that people are accustomed to are
not true in SQL.

Regards,
Jeff Davis