Re: Celko: help with an experiment

Peter,

  I'll have to stop by your page and pick up the latest Ocelot - I'd say "I'll try not to break it," but that wouldn't be true...

  One question I still have on this issue is when the aggregate does not refer to any columns of the table, as in

  select max(12) as Twelve from T

for different cardinalities of T. My best try at the SQL-92 standard suggests this should return as many rows as there are rows in T. I don't have a copy of the 99 standard to try to decipher its verdict.

Steve

Peter Gulutzan wrote:

> 71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0207031105.996ef17_at_posting.google.com>...
> > Please be so kind as to run this set of queries on any SQL products
> > you have. They are short. Then send me your answers and the name and
> > version of the SQL product you used.
> >
> > I want to use this information in a article or my next book. I think
> > there is a serious flaw in the Standards and want to investigate.
> >
> THE OCELOT SQL DBMS Version 03.02.0705, under Windows, says:
> 1) SELECT * FROM Foobar;
> 1
> 2) SELECT MAX(i) FROM Foobar
> 1
> 3) SELECT * FROM Foobar WHERE 1 = 0
> empty set
> 4) SELECT MAX(i) FROM Foobar WHERE 1 = 0;
> null
> 5) SELECT CASE WHEN 1 = 1 THEN 99 ELSE COUNT(*) END AS x FROM Foobar WHERE 1 = 0;
> 99
> 6)SELECT CASE WHEN 1 = 0 THEN 99 ELSE COUNT(*) END AS x FROM Foobar WHERE 1 = 0;
> 0
>
> This is a recent fix, which is only evident in the without-source-code
> download at http://ourworld.compuserve.com/homepages/ocelotsql/download.htm.
> As Mr Kass has pointed out, the inspiration for Mr Celko's question evidently
> is an earlier thread initiated by Mr Kass. That earlier thread discussed an
> Ocelot error which is now, I believe, taken care of.
>
> Also in that thread, there were references to a 1-row grouped table that is
> automatically created. I gather that the SQL-92 standard actually said that.
> In SQL-99 there is of course a statement that there is an implied clause
> "GROUP BY ()" i.e. group by <grand total> if (a) there is no GROUP BY, and
> (b) there is a set function "contained" in a select-list expression. That
> is obviously true for query (4). However, I did not see that the result of
> "GROUP BY <grouped table>" is one row if COUNT(*) is not "executed" in some
> sense. The standard is rather dense in this section so I could easily have
> missed the drift.
>
> Peter Gulutzan
> Ocelot Computer Services Inc.
Received on Tue Jul 09 2002 - 04:01:39 CEST