Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated

On Wed, 2026-03-04 at 11:46 +0100, Björn Kautler wrote:
> If you have the query
>
> WITH FOO AS (
>          SELECT 0 AS GROUPING
>      )
> SELECT CASE
>     WHEN GROUPING >= 0 THEN 'non-negative'
>     ELSE CAST((1 / 0) AS VARCHAR)
> END
> FROM FOO;
>
> it works successfully, having the ELSE as a safeguard against having coded a bug, having forgotten a WHEN branch, so it fails fast.
>
> So if you have
>
> WITH FOO AS (
>          SELECT -1 AS GROUPING
>      )
> SELECT CASE
>     WHEN GROUPING >= 0 THEN 'non-negative'
>     ELSE CAST((1 / 0) AS VARCHAR)
> END
> FROM FOO;
>
> it fails with a division by zero error.
>
> But if you have the query
>
> WITH FOO AS (
>          SELECT 0 AS GROUPING
>          GROUP BY 1
>      )
> SELECT CASE
>     WHEN GROUPING >= 0 THEN 'non-negative'
>     ELSE CAST((1 / 0) AS VARCHAR)
> END
> FROM FOO;
>
> then it always fails with division by zero error, even though the result should still be 'non-negative'.

This is working as it should, see
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. For example, this is an untrustworthy way of trying to avoid division
by zero in a WHERE clause:

SELECT ... WHERE x > 0 AND y/x > 1.5;

But this is safe:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

A CASE construct used in this fashion will defeat optimization attempts, so it
should only be done when necessary. (In this particular example, it would be better
to sidestep the problem by writing y > 1.5*x instead.)

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

Yours,
Laurenz Albe