Netezza does not do lazy evaluation of case statements?
I'm performing a computation which might contain division by 0, in which case I want the result to be an arbitrary value (55). To my surprise, wrapping the computation with a case statement did not do the job!
select case when 1=0 then 3/0 else 55 end
ERROR HY000: Divide by 0
Why is that? Is there another workaround?
Answer
ok, I was being inaccurate. This is the exact query that fails with "divide by 0":
select case when min(baba) = 0 then 55 else sum(1/baba) end from t group by baba
This looks like a lazy evaluation failure out of Netezza, as notice that I group by baba, so whenever baba is 0, it also means that min(baba) is 0, and the evaluation should have been gracefully stopped without ever getting to the 1/baba term and failing on division by 0. Right? well, no.
What I guess is the gotcha here and the reason for the failure is that Netezza evaluates the rows terms before it can evaluate the aggregate terms. So it must evaluate 1/baba and baba at every row, and only then can it evaluate the aggregate terms min(baba) and sum(1/baba)
so, the workaround (for me) was: select case when min(baba) = 0 then 55 else 1/min(baba) end from t group by baba, which has the same meaning.