All we need is an easy explanation of the problem, so here it is.
Can somebody please explain what is happening here and why?
select case when y = 0 then -1 else sum(1/y) end from (select 0 y group by y) x group by y
This query produces division by zero error, even though CASE explicitly checks for 0. Pay no attention to the fact that the query is meaningless and uses constants; I have tried to create a minimal example. It looks like the query planner decides to evaluate aggregation function before checking WHEN clause, but why? And is there a way to force it to short circuit? The only workaround I have found yet is to use
sum(1/nullif(y, 0)), which is then discarded anyway upon evaluation of WHEN.
How to solve :
This is Working As Designed™:
When it is essential to force evaluation order, a
CASEconstruct (see Section 9.18) can be used. …]
CASEis not a cure-all for such issues, however. One limitation of the technique illustrated above is that it does not prevent early evaluation of constant subexpressions. As described in Section 38.7, functions and operators marked
IMMUTABLEcan be evaluated when the query is planned rather than when it is executed. Thus for example
SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;
is likely to result in a division-by-zero failure due to the planner trying to simplify the constant subexpression, even if every row in the table has x > 0 so that the
ELSEarm would never be entered at run time.
In your (obviously contrived) example the value of
y is known at the query compilation time, so
1/y causes the division by zero error before the query is executed.
If you try to avoid the immutability using something like this, for example:
with t (r) as (values (random()::int) ) select case when y = 0 then -1 else sum(1/y) end from (select r y from t ) x group by y;
you will hit another caveat, though only half of the time, because in this case division by zero will be happening at run time:
Another limitation of the same kind is that a
CASEcannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before other expressions in a
HAVINGclause are considered.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂