Обсуждение: handling of COUNT(record) vs IS NULL

Hi,

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 Comparison Operator docs[1] describe the behaviour of IS NULL
changing, with respect to records, in version 8.2.  Is count still
exhibiting the old behaviour?


  Sam

 [1] http://www.postgresql.org/docs/8.2/static/functions-comparison.html

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.

            regards, tom lane

"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!

On Mon, Jan 28, 2008 at 04:38:01PM -0500, Tom Lane wrote:
> 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>.

But isn't COUNT(*), logically from a syntactic point of view, working
with records?  Or is it really supposed to be a special hack.  My
intuition of the original intent of COUNT(*) is that all tables have
at least one non-null column, so it's safe to rewrite COUNT(*) into
COUNT(1).  In general this doesn't seem correct to me though.

> 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.

I believe that somewhere in the spec COUNT is defined to return the
count of non-null rows.  If so, then the behaviour of COUNT isn't
consistent with IS NULL and if the spec only defines the behaviour for
non-record values then you can't look to it for guidance.  Wouldn't it
be better to either ban counts of records or make it follow the same
semantics as the IS NULL predicate.


  Sam