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

Malcolm Beattie <mbeattie@sable.ox.ac.uk> writes:
> Using 6.5 (via Thomas Lockhart's Linux RPM build of Jul 2), I get a
> phantom row when doing the following:
>     create table foo (a int);
>     select t1.a, count(*) from foo t1, foo t2 group by t1.a;
> I get
>     a|count
>     -+-----
>      |    0
>     (1 row)
> instead of zero rows.

It's not a bug, it's a feature ... or at least there are some around
here who claim that the behavior is OK.  I think they're wrong, but
if you want it changed you'll need to cite chapter and verse from the
SQL92 standard, not just assert that Informix does it differently.
You'll find several past discussions of this point in the pgsql-hackers
archives, and they all seem to have ended inconclusively.

> is it just a buglet that can be fixed fairly easily?

I think it would not be hard to fix, if we have a consensus that the
behavior should change.
        regards, tom lane