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

Björn Kautler <Bjoern@kautler.net> 于2026年3月4日周三 20:31写道：

Hi

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.

In the planner, the cte FOO is pulled up as a subquery and finally
is transformed to be RTE_RESULT,
and the GROUPING in the output of top query will be transformed to:
"WHEN 0 >= 0 THEN 'non-negative'

When the planner evaluates the targetList of the query, it finds that
0 >= 0 is true and 'non-negative' is a constant, so it returns
immediately.
The ELSE branch has no change to process.
So the query works successfully.

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

If you write CTE with group by, the CTE will continue to be
transformed into a subquery,
but the subquery can't be pulled up, seeing is_simple_subquery ().
The GROUPING in the targetList will continue to be the Var node. It
can't be simplified directly when pressing "WHEN GROUPING >= 0 THEN
'non-negative'"
So ELSE CAST((1 / 0) AS VARCHAR) has the chance to process, then
"division by zero" error is reported.

--
Thanks,
Tender Wang

Oh, ok, thanks to you two for the explanations.
Didn't think this is an optimization-caused issue. :-(

Using the CASE as a safeguard also does not work here.
I now tried

WITH FOO AS (
SELECT 0 AS GROUPING
GROUP BY 1
)
SELECT CASE
WHEN GROUPING >= 0 THEN 'non-negative'
WHEN GROUPING < 0 THEN CAST((1 / 0) AS VARCHAR)
END
FROM FOO;

But even there the optimization does evaluate the 1/0 when it sees it and
causes the division by zero.

As this now turned to an XY-problem,
let me ask about my actual X.

Is there a safe way to intentionally fail a query under a given condition?
What I tried to do was to provoke the division-by-zero error if I happened
to forget some WHEN branch as this is a bug.
Much like throwing an `AssertionError` in Java when you assume that this
code line should never be possible to execute.
Is there some safe way to do something like this you are aware of, or is
this just not safely possible with SQL?

Cheers
Björn

Am Mi., 4. März 2026 um 14:42 Uhr schrieb Tender Wang <tndrwang@gmail.com>:

On Wednesday, March 4, 2026, Björn Kautler <Bjoern@kautler.net> wrote:

What I tried to do was to provoke the division-by-zero error if I happened
to forget some WHEN branch as this is a bug.
Is there some safe way to do something like this you are aware of

Write a volatile “fail()” plpgsql function, and call it in the else block.
Raise exception there.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wednesday, March 4, 2026, Björn Kautler <Bjoern@kautler.net> wrote:

What I tried to do was to provoke the division-by-zero error if I happened
to forget some WHEN branch as this is a bug.
Is there some safe way to do something like this you are aware of

Write a volatile “fail()” plpgsql function, and call it in the else block.
Raise exception there.

Right. An intentional failure is a side-effect, and immutable
subexpressions are not supposed to have side-effects. As long
as you mark the fail() function volatile, the planner will avoid
pre-evaluating it.

regards, tom lane