On 11/26/2012 09:05 PM, Tom Lane wrote:
> Hannu Krosing <hannu@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
