Skip site navigation (1) Skip section navigation (2)

Re: WIP json generation enhancements : strange IS NULL behaviour

From: Hannu Krosing <hannu(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Hannu Krosing <hannu(at)2ndQuadrant(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WIP json generation enhancements : strange IS NULL behaviour
Date: 2012-11-26 20:29:19
Message-ID: 50B3D11F.20408@2ndQuadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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





In response to

Responses

pgsql-hackers by date

Next:From: Jeff DavisDate: 2012-11-26 20:29:37
Subject: Re: Removing PD_ALL_VISIBLE
Previous:From: Hannu KrosingDate: 2012-11-26 20:09:17
Subject: Re: WIP json generation enhancements: fk-tree-to-json()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group