Re: Celko: help with an experiment

From: JRStern <JXSternChangeX2R_at_gte.net>
Date: Wed, 03 Jul 2002 20:29:35 GMT
Message-ID: <3d235c9b.23032228@news.verizon.net>

I have an antique version of SQLServer 7.0 here.

Yes, I can see some results that might be considered inconsistent, but I don't know what we think (a) the standards say *should* happen, (b) what inconsistencies the standards might recommend.

I presume that processing orders should be fixed, and that whatever physical execution plan is involved, logically the results should be determined by doing a select first, then filtering by the where clause.

However, I can see the problem complicated by the fact that some selects are scalar values and others are sets. If a select generates a scalar value, should it be filtered when the where clause is executed?

Looking forward to your discussion.

J.

On 3 Jul 2002 12:05:20 -0700, 71062.1056_at_compuserve.com (--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
>[x] 1
>[ ] 99
>[ ] NULL
>[ ] Empty set
>[ ] Warning
>[ ] Fatal error
>[ ] Other

I

---

1

(1 row(s) affected)

>2) SELECT MAX(i) FROM Foobar;
>
>[ ] 0
>[x] 1
>[ ] 99
>[ ] NULL
>[ ] Empty set
>[ ] Warning
>[ ] Fatal error
>[ ] Other
           

---

1

(1 row(s) affected)

>3) SELECT * FROM Foobar WHERE 1 = 0;
>
>[ ] 0
>[ ] 1
>[ ] 99
>[ ] NULL
>[x] Empty set
>[ ] Warning
>[ ] Fatal error
>[ ] Other

I

---

(0 row(s) affected)

>4) SELECT MAX(i) FROM Foobar WHERE 1 = 0;
>
>[ ] 0
>[ ] 1
>[ ] 99
>[x] NULL
>[ ] Empty set
>[ ] Warning
>[ ] Fatal error
>[ ] Other
          

---

NULL (1 row(s) affected)

>5) SELECT CASE WHEN 1 = 1
> THEN 99
> ELSE COUNT(*) END AS x
> FROM Foobar
> WHERE 1 = 0;
>
>[ ] 0
>[ ] 1
>[x] 99
>[ ] NULL
>[ ] Empty set
>[ ] Warning
>[ ] Fatal error
>[ ] Other

x

---

99

(1 row(s) affected)

>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

x

---

0

(1 row(s) affected) Received on Wed Jul 03 2002 - 15:29:35 CDT