Re: Celko: help with an experiment
From: David Cressey <david_at_dcressey.com>
Date: Thu, 04 Jul 2002 05:37:46 GMT
Message-ID: <KaRU8.58$0U1.7220_at_petpeeve.ziplink.net>
4 from foobar
5 where 1=0;
4 from foobar
5 where 1=0;
Date: Thu, 04 Jul 2002 05:37:46 GMT
Message-ID: <KaRU8.58$0U1.7220_at_petpeeve.ziplink.net>
This is from using SQL*Plus to connect to an Oracle 8i Lite Database
SQL> SQL> SQL> create table foobar (i integer not null);
Table created.
SQL> insert into foobar values (1);
1 row created.
SQL> select * from foobar;
I
1
SQL> select max(i) from foobar;
MAX(I)
1
SQL> select * from foobar where 1=0;
no rows selected
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
0
SQL> select case when 1=0
2 then 99 3 else count(*) end as x
4 from foobar
5 where 1=0;
X
0
--
Regards,
David Cressey
www.dcressey.com
"--CELKO--" <71062.1056_at_compuserve.com> 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.
>
> CREATE TABLE Foobar (I INTEGER NOT NULL);
>
> INSERT INTO Foobar VALUES (1);
>
> 1) SELECT * FROM Foobar;
>
> [ ] 0
> [X ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 2) SELECT MAX(i) FROM Foobar;
>
> [ ] 0
> [X ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 3) SELECT * FROM Foobar WHERE 1 = 0;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [X] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> 4) SELECT MAX(i) FROM Foobar WHERE 1 = 0;
>
> [ ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [X ] Other Perhaps NULL converted to blank
>
>
> 5) SELECT CASE WHEN 1 = 1
> THEN 99
> ELSE COUNT(*) END AS x
> FROM Foobar
> WHERE 1 = 0;
>
> [ X] 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;
>
> [X ] 0
> [ ] 1
> [ ] 99
> [ ] NULL
> [ ] Empty set
> [ ] Warning
> [ ] Fatal error
> [ ] Other
>
> --CELKO--
Received on Thu Jul 04 2002 - 07:37:46 CEST