Re: Celko: help with an experiment

Hmm. Where have I seen this before? The one that puzzles me the most, and that you've left out, is

select max(99) from Foobar

particularly when Foobar contains more than 1 row. I believe the SQL-92 standard says this should return as many 99's as there are rows in Foobar, as silly as that sounds - did you ask anyone on the standards committee about it?

Anyway, how about a credit, or a footnote?

Steve Kass
Drew University

--CELKO-- wrote:

> 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.
>
> CREATE TABLE Foobar (I INTEGER NOT NULL);
>
> INSERT INTO Foobar VALUES (1);
>
> 1) SELECT * FROM Foobar;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 2) SELECT MAX(i) FROM Foobar;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 3) SELECT * FROM Foobar WHERE 1 = 0;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 4) SELECT MAX(i) FROM Foobar WHERE 1 = 0;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 5) SELECT CASE WHEN 1 = 1
> THEN 99
> ELSE COUNT(*) END AS x
> FROM Foobar
> WHERE 1 = 0;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 6) SELECT CASE WHEN 1 = 0
> THEN 99
> ELSE COUNT(*) END AS x
> FROM Foobar
> WHERE 1 = 0;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> --CELKO--
Received on Sat Jul 06 2002 - 04:18:10 CEST