Re: [HACKERS] Phantom row from aggregate in self-join in 6.5

At 20:52 22/07/99 -0400, you wrote:
>Malcolm Beattie <mbeattie(at)sable(dot)ox(dot)ac(dot)uk> writes:
>> 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.
>
>It's not a bug, it's a feature ... or at least there are some around
>here who claim that the behavior is OK. I think they're wrong, but
>if you want it changed you'll need to cite chapter and verse from the
>SQL92 standard, not just assert that Informix does it differently.

I've now checked Dec Rdb, SQL/Server, and MS-Access - and they return 0 rows. Add this to Informix, and one begins to wonder if there are any that match the Postgres behaviour?

Any idea where I can find a copy of the SQL92 standard on the net?

>You'll find several past discussions of this point in the pgsql-hackers
>archives, and they all seem to have ended inconclusively.

I had a quick look at discussions involving informix, but could not find anything. Can you give a little more information about the past discussions, and specifically, what the reasons for preserving this behaviour were?

>> is it just a buglet that can be fixed fairly easily?
>
>I think it would not be hard to fix, if we have a consensus that the
>behavior should change.
>

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/