Re: When is a record NULL?

On Jul 23, 2009, at 6:52 PM, David E. Wheeler wrote:

> No, that's not the problem I see -- that solved the problem in my
> particular code. The problem I see is that, given that the standard
> says (according to Tom) that if any value is NULL then the record is
> NULL, then I would expect this to return false:
>
> SELECT ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL);
>
> But it returns true.

Sorry, I'm confused. Understandable I think. So:

ROW(1, NULL) is neither NULL nor NOT NULL. I've no idea what state it
is, but I guess that's the standard. In this case, IS DISTINCT FROM
gives me a sensible return value -- it assumes that the records are
NOT NULL, I guess, for its purposes. This is still inconsistent, since
the records are neither NULL nor NOT NULL, but perhaps sensible.

It's pretty insane, frankly. Gotta love SQL.

Best,

David