Phantom row from aggregate in self-join in 6.5
От | Malcolm Beattie |
---|---|
Тема | Phantom row from aggregate in self-join in 6.5 |
Дата | |
Msg-id | E117J3D-00060u-00@sable.ox.ac.uk обсуждение исходный текст |
Ответы |
Re: [HACKERS] Phantom row from aggregate in self-join in 6.5
|
Список | 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@sable.ox.ac.uk> Unix Systems Programmer Oxford University Computing Services
В списке pgsql-hackers по дате отправления: