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