Re: When is a record NULL?

David E. Wheeler wrote:
> On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:

>> I guess the spec authors figured they might as well make IS [NOT] NULL
>> do something useful when applied to a row rather than throwing an
>> error. I tend to agree.
>
> Frankly, I find the state where a record with a NULL and a not-null
> value being neither NULL nor not NULL bizarre.

I'm guessing the justification (and presumably this was worked out based
on the behaviour of one or more of the big DB providers and then
justified afterwards) is that the composite is "partially unknown". Of
course you should either introduce a new code or throw an error, but
four-valued logic isn't going to win you any friends.

If the argument *is* that because you know part of the overall value the
composite isn't null then I'd argue that ('abc' || null) isn't null
either. After all, the first three characters are perfectly well
established.

>> I hope that provides some clarity.
>
> It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the
> whole thing totally bizarre. Is it me?

Yes, just you. None of the rest of us have any problems with this at all :-)

--
Richard Huxton
Archonet Ltd