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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Mark Hollomon"
Дата:
Сообщение: Re: [HACKERS] Maximum query string length
Следующее
От: Thomas Lockhart
Дата:
Сообщение: Re: [HACKERS] Another reason to redesign querytree representation