Re: handling of COUNT(record) vs IS NULL

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

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