Re: Puzzle: What should this query return?

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 29 Jun 2002 08:34:23 -0700
Message-ID: <c0d87ec0.0206290734.5519c229@posting.google.com>

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 - 10:34:23 CDT