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

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(at)gmail(dot)com>:

> Björn Kautler <Bjoern(at)kautler(dot)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
>