Short circuit evaluation in CASE in PostgreSQL



  • 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.



  • This is https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL :

    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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2