"Tom Lane" <tgl@xxxxxxxxxxxxx> writes:
> Sam Mason <sam@xxxxxxxxxxxxx> 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!
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
