Netezza 不对 case 语句进行惰性求值吗？
我正在执行一个可能包含除以 0 的计算，在这种情况下我希望结果是任意值 (55)。令我惊讶的是，用 case 语句包装计算并没有完成这项工作！
select case when 1=0 then 3/0 else 55 end
错误 HY000：除以 0
这是为什么？还有其他解决方法吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
好吧，我不准确。这是因“除以 0”而失败的确切查询：
select case when min(baba) = 0 then 55 else sum(1/baba) end from t group by baba这看起来像是一个懒惰的 查询Netezza 评估失败，请注意，我按
baba分组，因此每当 baba 为 0 时，也意味着min(baba)为 0，并且评估应该具有被优雅地停止，没有到达1/baba项并且除以 0 失败。对吗？好吧，不。我猜想这里的问题和失败的原因是 Netezza 在评估聚合项之前先评估行项。因此，它必须在每一行计算
1/baba和baba，然后才能计算聚合项min(baba)和sum(1/baba)所以，解决方法（对我来说）是：
选择 min(baba) = 0 时的情况，然后 55 else 1/min(baba) end from t group by baba，其含义相同。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 babaThis looks like a lazy evaluation failure out of Netezza, as notice that I group by
baba, so whenever baba is 0, it also means thatmin(baba)is 0, and the evaluation should have been gracefully stopped without ever getting to the1/babaterm 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/babaandbabaat every row, and only then can it evaluate the aggregate termsmin(baba)andsum(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.