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@sable.ox.ac.uk>
Unix Systems Programmer
Oxford University Computing Services
