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

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