Re: handling of COUNT(record) vs IS NULL

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Sam Mason <sam(at)samason(dot)me(dot)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!