Re: Puzzle: What should this query return?

Joe,

  We agree it's weird. I spent some time reading the SQL-92 standard, and I can't determine what the standard says this should return. Do you think it's a "hole" in the standard, or do you think the standard provides an answer?

  Setting aside the problem of DBMS's crashing, I do want to know what the answer should be, or that there's something in the standard that says this is not a valid query.

Steve

--CELKO-- wrote:

> This gets weird in Standard SQL.
>
> CREATE TABLE Foobar (I INTEGER NOT NULL);
> INSERT INTO Foobar VALUES (1);
>
> SELECT * FROM Foobar; -- returns 1
> SELECT * FROM Foobar WHERE 1=0; -- returns empty set
> SELECT COUNT(*) FROM Foobar WHERE 1=0; -- returns 0
>
> CASE expressions are evaluated in the order they are written:
>
> SELECT CASE WHEN 1 = 1
> THEN 12
> ELSE COUNT(*) END AS x
> FROM Foobar
> WHERE 1 = 0;
>
> SQL Server reasoning seems to be that we have a COUNT() aggregate
> function, so we have to return a result. The SQL Server compiler
> keeps looking ahead to all of the WHEN-THEN clauses in a CASE
> expression, with the assumption that ALL branches will be used. This
> makes for some cute screw ups because it can find impossible
> division-by-zero errors and other things like that (I have a post on
> that problem).
>
> Ocelot reasoning seems to be that we have an ambigous syntax and we
> just blow up.
>
> Another line of reasoning is we can parse the SELECT clause and reduce
> it to:
>
> SELECT 12 FROM Foobar WHERE 1 = 0; --returns empty.
>
> Or if we had
>
> SELECT CASE WHEN 1 = 0
> THEN 12
> ELSE COUNT(*)+1 END AS x
> FROM Foobar
> WHERE 1 = 0;
>
> we could reduce it to:
>
> SELECT COUNT(*) + 1 FROM Foobar WHERE 1 = 0; --returns 1
>
> I think we might have a problem here ....
Received on Sat Jun 29 2002 - 18:39:05 CEST