Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated - Mailing list pgsql-bugs

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
itfails 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