Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!elroy.jpl.nasa.gov!ncar!gatech!mcnc!rti!mozart!kent
From: kent@manzi.unx.sas.com (Paul Kent)
Newsgroups: comp.databases
Subject: SQL GROUP BY queries and empty tables
Message-ID: <1991May16.212202.22391@unx.sas.com>
Date: 16 May 91 21:22:02 GMT
Sender: news@unx.sas.com (Noter of Newsworthy Events)
Followup-To: comp.databases
Organization: SAS Institute Inc.
Lines: 58
Nntp-Posting-Host: manzi.unx.sas.com


hello,

SQL queries with summary functions when the where clause 
eliminates all rows from consideration have a strange
quirk that i'd like to know more about.


> create table gr ( a int, b char );
> insert into gr values( 1, 'a' );
> insert into gr values( 2, 'b' );


> select count(*) from gr;

this gets predictable results.. a single row with "2"



> select count(*) from gr where a > 10;

this too. there are no rows that satisfy the where clause, so we
get a single row showing that "0" records were counted.



> select    count(*) from gr where a > 10 group by b ;
> select b, count(*) from gr where a > 10 group by b ;

this is wierd. there were no rows, so presumably there are
no groups. however 

  o) DB2(v2r2)
  o) Oracle(v6 on a vax)
  o) RDB(sorry, no version details, vms5.3 however)

dont produce any output for these latter queries.

does anyone know the rationale behind this. 
can they point at the rules in the standard 
(ansi 135.1 or draft stndard for sql2)
that say that this they way it should be?

our implementation of SQL produces a single row for all
of these queries. and a user has asked why the difference.
unfortunately, thats the way we read it in the standard.


what does your favorite SQL database do?
and do you like it that way?


thanks,
paul.

--
Paul Kent (SQL r&d)                   " nothing ventured, nothing disclaimed "
kent@unx.sas.com         SAS Institute Inc, SAS Campus Dr, Cary NC 27513-2414.