Re: When is a record NULL?

2009/7/24 David E. Wheeler <david(at)kineticode(dot)com>:
> It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the whole
> thing totally bizarre. Is it me?
>

*shrug* The IS [NOT] NULL tests mean something different when applied
to a ROW than they do when applied to a scalar value or an array.

"SELECT 1 IS NULL"
=> means "is this scalar set to the special value NULL?".

"SELECT ROW(1, 2) IS NULL"
=> means "are all the member values of this row set to the special
value NULL?"

So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't
have the property of being NULL or not NULL, because it is a composite
value. "ROW(NULL, NULL) IS NULL" returns true, but that is not the
same as saying that it actually is NULL, because of the different
semantics above.

It's slightly different semantics from what you get with ordinary
scalar values, but that is cognisant with the fact that composites are
fundamentally different things from ordinary scalar values.

Cheers,
BJ