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