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
notchange 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
developmentstream from MS/Sybase etc, at least since that time, and almost certainly from its genesis. It was purchsed
byOracle 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   |/