Re: Celko: help with an experiment

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 Sun Jul 07 2002 - 22:15:36 CEST