Re: Celko: help with an experiment

Here is the result running oracle 9i rel 1 on NT, wint SQL*Plus: Release 9.0.1.0.1

Arne

SQL> CREATE TABLE Foobar (I INTEGER NOT NULL);

Tabel er oprettet.

SQL>
SQL> INSERT INTO Foobar VALUES (1);

1 række er oprettet.

SQL> SELECT * FROM Foobar;

         I

         1

SQL> SQL> SELECT MAX(i) FROM Foobar;

    MAX(I)

         1

SQL> SELECT * FROM Foobar WHERE 1 = 0;

Ingen rækker er valgt

SQL> SELECT MAX(i) FROM Foobar WHERE 1 = 0;

    MAX(I)

SQL> SELECT CASE WHEN 1 = 1

  2                 THEN 99
  3                 ELSE COUNT(*) END AS x
  4       FROM Foobar
  5      WHERE 1 = 0;

         X
----------
        99

SQL> SELECT CASE WHEN 1 = 0
  2                 THEN 99
  3                 ELSE COUNT(*) END AS x
  4       FROM Foobar
  5      WHERE 1 = 0;

         X
----------
         0

"--CELKO--" <71062.1056_at_compuserve.com> skrev i en meddelelse 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.
>
> 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 Fri Jul 05 2002 - 15:26:19 CEST