开发者

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?


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜