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

At 18:57 22/07/99 -0700, you wrote:
>At 11:39 AM 7/23/99 +1000, Philip Warner wrote:
>
>>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?
>
>I'd like an answer to this, too :)

I have found a US based organization that sell 38MB file for $220...I guess I'll go to a library.

>It may be that you've stumbled into an area the standard's either
>left "implementation-dependent", "undefined", or simply forgotten
>or unthought-of. (can you tell I've been drafted into ANSI/ISO
>standards efforts in the past for Pascal and Modula-2?)

If that's the case, then the example below seems to produce an inconsistency: IMO, changing the columns selected should not change the number of rows returned.

>Still, I must say that a row returning "0" in response to a
>count(*) isn't at all suprising, I guess it's a matter of
>whether or not the count(*) or the specific column being
>extracted determines the behavior.

Count returning 0 is good, the problem is that:

select t1.a from foo t1, foo t2 group by t1.a;
^
+--- No count(*)

returns 0 rows (fine), but that

select t1.a, count(*) from foo t1, foo t2 group by t1.a;

returns 1 row, which is weird.

>
>First, I wouldn't trust Access to be much of an SQL standards judge.
>If nothing else, MS's collaboration with Sybase (SQL/Server) might
>perhaps color MS's view of what the standard sez. Not to mention
>the poaching of parser/semantic code, etc...

I agree, but it all adds a little weight to the argument - maybe?

>And doesn't DEC Rdb have some genealogical relationship to SQL/Server?
>(I could be WAY off base here)

I don't think so. RDB was at version 3 in 1986 - that's when I started using it. It has had AFAICT a totally separate development stream from MS/Sybase etc, at least since that time, and almost certainly from its genesis. It was purchsed by Oracle a year or two ago, but it still largely the same product. If anything, Oracle have improved it a little.

----------------------------------------------------------------
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 |/