On 11/26/2012 09:05 PM, Tom Lane wrote:
Hannu Krosing <ha...@2ndquadrant.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
