Re: WIP json generation enhancements : strange IS NULL behaviour

On 11/26/2012 09:05 PM, Tom Lane wrote:
> Hannu Krosing <hannu(at)2ndQuadrant(dot)com> writes:
>> In some previous mail Tom Lane claimed that by SQL standard
>> either an array of all NULLs or a record with all fields NULLs (I
>> don't remember which) is also considered NULL. If this is true,
>> then an empty array - which can be said to consist of nothing
>> but NULLs - should itself be NULL.
> What I think you're referring to is that the spec says that "foo IS
> NULL" should return true if foo is a record containing only null fields.
Is this requirement recursive ?

That is , should

ROW(NULL, NULL, ROW(NULL, ROW(NULL, NULL))) IS NULL
also be true ?

Currently PostgreSQL does this kind of IS NULL for "simple" rows

hannu=# SELECT ROW(NULL, NULL) IS NULL;
?column?
----------
t
(1 row)

and also for first level row types

hannu=# SELECT ROW(NULL, ROW(NULL, NULL)) IS NULL;
?column?
----------
t
(1 row)

but then mysteriously stops working at third level

hannu=# SELECT ROW(NULL, NULL, ROW(NULL, ROW(NULL, NULL))) IS NULL;
?column?
----------
f
(1 row)

> That's a fairly narrow statement. It does NOT say that NULL and
> (NULL,NULL,...) are indistinguishable for all purposes; only that
> this particular test doesn't distinguish them. Also I don't think they
> have the same statement for arrays.
>
> The analogy to other aggregates is probably a better thing to argue
> from. On the other hand, I don't know anyone outside the SQL standards
> committee who thinks it's actually a good idea that SUM() across no rows
> returns null rather than zero.
>
Might be done in order to be in sync with other aggregates - for
example the "return NULL for no rows" behaviour makes perfect
sense for MIN(), AVG(), etc.

------------------------
Hannu