Phantom row from aggregate in self-join in 6.5

From: Malcolm Beattie <mbeattie(at)sable(dot)ox(dot)ac(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Phantom row from aggregate in self-join in 6.5
Date: 1999-07-22 13:38:19
Message-ID: E117J3D-00060u-00@sable.ox.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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. The row has an a column of "NULL". This happens
even if I create table foo as "(a int not null)".

I've checked that Informix 7.3LE gives zero rows as expected.

Further, if I add
having t1.a is not null
to the select query to try to get rid of the bogus row then it gives
ERROR: SELECT/HAVING requires aggregates to be valid
but I don't know quite what that's telling me.

Some of you might remember I had that other multi-aggregate/view
problem recently which turned out to be fairly fundamentally unfixable
due to the way postgres holds views internally in a close-to-SQL
format rather than the underlying relational algebra. Can anyone tell
me if this phantom row thing is another consequence of the
implementation of aggregates in postgres or is just a buglet that can
be fixed fairly easily?

Thanks,
--Malcolm

--
Malcolm Beattie <mbeattie(at)sable(dot)ox(dot)ac(dot)uk>
Unix Systems Programmer
Oxford University Computing Services

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Lockhart 1999-07-22 14:25:18 Re: [HACKERS] Another reason to redesign querytree representation
Previous Message Mark Hollomon 1999-07-22 12:39:28 Re: [HACKERS] Maximum query string length