Re: When is a record NULL?

On Fri, Jul 24, 2009 at 09:31:13AM -0400, Tom Lane wrote:
> Brendan Jurd <direvus(at)gmail(dot)com> writes:
> > 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 worse than that, because there actually is also such a thing as
> the row value being NULL --- ie, there's no row structure at all.
> At least internally, that's a completely different thing from having
> a row all of whose fields are null.

Hope nobody minds if I go off on a somewhat pedagogic bent here!

Not quite sure what you mean by the above; but I think maybe something
like:

SELECT NULL::RECORD AS r;

PG may well treat this internally as a special case, but from a type
level I don't see any difference between the above and, say:

SELECT ROW(1)::RECORD AS r;

In both cases we get a result that has exactly one column and this
column is of type RECORD (or ROW depending how you spell things).
The fact that it happens to be a NULL *value* in one case shouldn't
affect things at the level of *types*--unless PG has suddenly become
dependently-typed which I don't believe it wants to be.

I'm also aware that PG's handling of types with ROW values is somewhat
inconsistent when compared to other values, for example:

SELECT (r).a
FROM (
SELECT a,b
FROM (VALUES
(1,2),
(2,3)) x(a,b)) r;

here, we can look inside the RECORD named by "r" and pull out the value
associated with attribute "a", but inside:

SELECT (r).a
FROM (VALUES
(ROW(1,2)),
(ROW(2,3))) x(r);

we get a message saying that the "record type has not been registered"
when I'd expect to get an error saying that it doesn't know which
attribute "a" is. We also fail to get an error in the following case:

SELECT r
FROM (VALUES
(ROW(1,2)),
(ROW('a','b'))) x(r);

which (to me) seems wrong. The (allegedly) static types in PG appear
dynamic when it comes to RECORDs.

I'd be interested in fixing this behavior but every time I've started
trying to fix this I've tended to get horribly lost in the code.

> SQL doesn't provide a test for this case that's separate from the test
> involving null-ness of individual fields. Not much we can do about
> it though. I'm not entirely sure that exposing the distinction would
> be helpful anyway ...

I think it would; I tend to write the following and have just realized
that it doesn't do what I thought it did:

SELECT a.*
FROM tbla a
LEFT JOIN tblb b ON a.id = b.id
WHERE b IS NULL;

The intuition being that the row valued "b" would only be considered to
be NULL (i.e. IS NULL returned TRUE) when the LEFT JOIN failed. The
standard way to write this is of course to write "WHERE b.id IS NULL",
but I'm unsure why it's necessary to "look inside" the record "b" to get
out attribute "id" to see if it's NULL when it should just be possible
to look at "b" directly.

--
Sam http://samason.me.uk/