Short circuit evaluation in CASE in PostgreSQL

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment