Re: handling of COUNT(record) vs IS NULL

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Sam Mason <sam@samason.me.uk> writes:
>> I've just noticed that the handling of COUNT(record) and (record IS
>> NULL) aren't consistent with my understanding of them.  If I run the
>> following query:
>
>>   SELECT
>>      NULL       IS NULL, COUNT( NULL      ),
>>     (NULL,NULL) IS NULL, COUNT((NULL,NULL));
>
>> The IS NULL checks both return TRUE as I'd expect them to, but the
>> second count doesn't return 0.
>
> THe fourth of those isn't really valid SQL.  According to SQL99,
> IS NULL takes a <row value expression> as argument, so it's valid
> to do (NULL,NULL) IS NULL, but COUNT takes a <value expression>.
>
> I don't see anything in the spec suggesting that we are supposed
> to drill down into a rowtype value to see whether all its fields
> are null, in any context other than the IS [NOT] NULL predicate.

Well it's not just in the predicate, we handle it for other strict operators
and functions:

postgres=# select (ROW(null,null)=row(1,2)) IS NULL;
 ?column?
----------
 t
(1 row)


It does seem a bit inconsistent:

postgres=# select count(ROW(null,null)=row(1,2));
 count
-------
     0
(1 row)

postgres=# select count(ROW(null,null));
 count
-------
     1
(1 row)

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!