Celko: need help with experiment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

Celko: need help with experiment

Author

Topic

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-07-02 : 11:19:04

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
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] 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
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

--CELKO--

--CELKO--
Joe Celko, SQL Guru

izaltsman
A custom title

1139 Posts

Posted - 2002-07-02 : 11:43:35

Ran these samples on SQL Server 2000 Personal Edition SP2, SQL Server 2000 Standard Edition SP2, and SQL Server 7 Desktop Edition. Got the same results on all three:

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (empty recordset)

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

[ ] 0
[ ] 1
[ ] 99
[X] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[X] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

Hope this helps.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 11:44:16

Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (Empty Rowset)

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

[ ] 0
[ ] 1
[ ] 99
[X] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[X] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 11:46:53

SQL 2000 standard edition (unsure of SP.. but I think none):

Same results as izaltsman

-----------------------
Take my advice, I dare ya

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-02 : 12:09:14

sql 7.00.623....results same as page47....1,1,no rows,null,99,0

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-02 : 13:22:00

Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (I got nothing, but it was not NULL)

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

[ ] 0
[ ] 1
[ ] 99
[X] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[X] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

Michael

<Yoda>Use the Search page you must. Find answer you will.

Nazim
A custom title

1408 Posts

Posted - 2002-07-03 : 00:44:04

Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Small Business Server Edition on Windows NT 4.0 (Build 1381: Service Pack 4)

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X ] Other (Empty Recordset)

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

[ ] 0
[ ] 1
[X ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[X ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

Same Results

The fourth and fifth Query surprises me. i thought it should return empty rows. might be Sir Celko will put some light on it.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - Nazim on 07/03/2002 00:45:14

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-03 : 04:08:10

quote:

The fourth and fifth Query surprises me. i thought it should return empty rows. might be Sir Celko will put some light on it.

The COUNT(*) makes that CASE expression an aggregate expression, even though 99 is a scalar. If you say

SELECT CASE WHEN 1 = 1
THEN 99
ELSE COUNT(*) END AS x, I
FROM Foobar
WHERE 1 = 0;

instead, then you should get an error since there is no GROUP BY I.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-03 : 04:15:45

Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: )

1)[ ] 1

2)[ ] 1

3)[ ] Other (empty recordset)

4)[ ] NULL

5)[ ] 99

6)[ ] 0

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 04:41:56

Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (No resultset)

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

[ ] 0
[ ] 1
[ ] 99
[X] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[X] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

macka.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-03 : 05:08:13

Is there an echo in here?

Is there an echo in here?

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-07-03 : 06:14:55

Oracle 8i (8.1.7.0.0); Microsoft SQL Server 2000 - 8.00.534 (Build 2195: Service Pack 2)

3 and 4 produced different results and are commented

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (Oracle / message "no rows returned" SQL Server nothing)

4) SELECT MAX(i) FROM Foobar WHERE 1 = 0;
Oracle
[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (nothing, displays an empty cell)

SQL Server
Oracle
[ ] 0
[ ] 1
[ ] 99
[X] NULL
[ ] Warning
[ ] Fatal error
[ ] Other (nothing, displays an empty cell)

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

[ ] 0
[ ] 1
[X] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

acollins74
Yak Posting Veteran

82 Posts

Posted - 2002-07-10 : 16:58:15

Microsoft SQL Server 2000 (Intel X86) Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

Same results as izaltsman

(1,1,Other,Null,99,0)

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-10 : 17:35:55

Oracle 9.2.0.1.0

I
----------
1

1 row selected.

MAX(I)
----------
1

1 row selected.

no rows selected

MAX(I)
----------

1 row selected.

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

1 row selected.

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

1 row selected.

Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 2)

1,1,Other,Null,99,0

HTH
Jasper Smith

redlam
Starting Member

7 Posts

Posted - 2002-07-10 : 17:40:44

Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
****************************************
CREATE TABLE Foobar (I INTEGER NOT NULL);

INSERT INTO Foobar VALUES (1);

1) SELECT * FROM Foobar;

[ ] 0
[x] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[x] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[x] Other (empty)

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

[ ] 0
[ ] 1
[ ] 99
[x] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[x] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-07-11 : 05:58:43

Hmmm ... my previous post seems MIA, so here, again:

DB2 v6 for OS/390

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (no rows)

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

[ ] 0
[ ] 1
[ ] 99
[X] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[X] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

HTH

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-15 : 09:48:31

Knowing the Difference

Jay White
{0}

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-01-15 : 10:19:15

Microsoft SQL Server 6.50 - 6.50.416 (Intel X86) Jan 23 1999 14:10:24 Copyright (c) 1988-1997 Microsoft Corporation

1) SELECT * FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

2) SELECT MAX(i) FROM Foobar;

[ ] 0
[X] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

3) SELECT * FROM Foobar WHERE 1 = 0;

[ ] 0
[ ] 1
[ ] 99
[ ] NULL
[ ] Warning
[ ] Fatal error
[X] Other (empty rs)

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

[ ] 0
[ ] 1
[ ] 99
[X] NULL
[ ] Warning
[ ] Fatal error
[ ] Other

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

[ ] 0
[ ] 1
[X] 99
[ ] NULL
[ ] 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
[ ] Warning
[ ] Fatal error
[ ] Other

stocksr
Starting Member

13 Posts

Posted - 2003-01-16 : 05:06:16

Ran This Script


SELECT @@VERSION
CREATE TABLE Foobar (I INTEGER NOT NULL)
INSERT INTO Foobar VALUES (1)
SELECT ' Question 1'
SELECT * FROM Foobar 
SELECT ' Question 2'
SELECT MAX(i) FROM Foobar
SELECT ' Question 3'
SELECT * FROM Foobar WHERE 1 = 0
SELECT ' Question 4'
SELECT MAX(i) FROM Foobar WHERE 1 = 0
SELECT ' Question 5'
SELECT CASE WHEN 1 = 1 
THEN 99 
ELSE COUNT(*) END AS x 
FROM Foobar 
WHERE 1 = 0
SELECT ' Question 6'
SELECT CASE WHEN 1 = 0 
THEN 99 
ELSE COUNT(*) END AS x 
FROM Foobar 
WHERE 1 = 0
DROP TABLE Foobar

With These Results

 
                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Microsoft SQL Server  7.00 - 7.00.1063 (Intel X86) 
	Apr  9 2002 14:18:16 
	Copyright (c) 1988-2002 Microsoft Corporation
	Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)


(1 row(s) affected)


(1 row(s) affected)

            
----------- 
 Question 1

(1 row(s) affected)

I           
----------- 
1

(1 row(s) affected)

            
----------- 
 Question 2

(1 row(s) affected)

            
----------- 
1

(1 row(s) affected)

            
----------- 
 Question 3

(1 row(s) affected)

I           
----------- 

(0 row(s) affected)

            
----------- 
 Question 4

(1 row(s) affected)

            
----------- 
NULL

(1 row(s) affected)

            
----------- 
 Question 5

(1 row(s) affected)

x           
----------- 
99

(1 row(s) affected)

            
----------- 
 Question 6

(1 row(s) affected)

x           
----------- 
0

(1 row(s) affected)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-16 : 07:49:15

I think the experiment is finished; look at Jay's last post; Joe wrote the article already.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources