case when evaluation in SQL

Question

I've been working with SQLServer for many years now and always had the idea of CASE expression using lazy evaluation, which allows doing things such as check whether a string value contains a valid date before actually trying to convert it.

Today I found an scenario where this seems not true. After some simplification, this is an easy example to show it (don't try to find any sense to the calculation, just a fool example):

select b, sum(case when b<> 0 then a / b end)
from (
    select 1 as a, 1 as b union select 1, 0 union select 0, 1
) x group by b

This works fine, as I would expect.

select b, case when b<> 0 then sum(a / b) end
from (
    select 1 as a, 1 as b union select 1, 0 union select 0, 1
) x group by b

This throws an error (zero division).

Seems something related to the aggregate function, somehow the sum is evaluating before the case does, but I must be missing something and don't know what.

I'm mostly curious about this, as there are some workarounds and don't have a real problem with it.

By the way, I've test it on SQLServer 2005 and SQLServer 2008, and then on Oracle 11g, with same result always.

Answer 1

In the second version, the aggregate sum(a/b) has to be calculated for the entire result set, because there are no per-row conditionals in it. Then each row decides whether to include that value using the CASE expression.