Short circuit evaluation in CASE in PostgreSQL

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 :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is Working As Designed™:

When it is essential to force evaluation order, a CASE construct (see Section 9.18) can be used. …]

CASE is 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 IMMUTABLE can 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 ELSE arm 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 CASE cannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before other expressions in a SELECT list or HAVING clause are considered.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply