Re: When is a record NULL?

On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:

> Well, a ROW is an ordered set of values, each one of which may be
> either NULL or NOT NULL.

Right.

> It doesn't really make sense to talk about the ROW itself being NULL
> or NOT NULL, only its member values (but for extra confusion, contrast
> with the treatment of arrays, which can themselves be NULL).

Well then maybe a record (row) should *never* be null.

> It does make sense, however, to talk about the ROW's member values
> being entirely NULL or entirely NOT NULL, and that's what the IS NULL
> and IS NOT NULL tests tell you about.

Ah! So that's where the three-valued logic comes in to play with  
records:

try=#   SELECT ROW(1, NULL) IS NULL, ROW (1, 1) IS NULL, ROW(NULL,  
NULL) IS NULL; ?column? | ?column? | ?column?
----------+----------+---------- f        | f        | t

> 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 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?

Best,

David