| 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: | Whole Thread | Raw Message | 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 |