Re: [HACKERS] Phantom row from aggregate in self-join in 6.5

Don Baccus <dhogaza(at)pacifier(dot)com> writes:
> Still, I must say that a row returning "0" in response to a
> count(*) isn't at all suprising, I guess it's a matter of
> whether or not the count(*) or the specific column being
> extracted determines the behavior.

Neither, it's GROUP BY that creates the issue.

If you do an ungrouped query with aggregates, say

SELECT count(*) FROM table WHERE someCondition;

you will get one and only one row produced, with default values for
the aggregates if there are no input rows (ie, either an empty table
to start with, or nothing gets by the WHERE). Everybody seems
to be happy with this.

The question is what happens when GROUP BY enters the picture.
There is a faction that thinks that if there are no input rows
then you should still get one default row out. That makes no
sense to me; it seems to me you should get one aggregated row per
group if you have aggregates with GROUP BY, and if there are
no input rows then there are no groups. But I have not burrowed
into the SQL standard to try to develop a bulletproof argument
for that position.

>>> You'll find several past discussions of this point in the pgsql-hackers
>>> archives, and they all seem to have ended inconclusively.

>> I had a quick look at discussions involving informix, but could not find
> anything.

Informix is not the issue. Look for "GROUP BY" and aggregates.

regards, tom lane