MIME-Version: 1.0
References: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>
In-Reply-To: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Wed, 4 Mar 2026 21:42:21 +0800
Message-ID: <CAHewXNnVarUKNXrJXyDH=PKs-wST7bMzyqg61_SA2Mo5PPePgw@mail.gmail.com>
Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
To: =?UTF-8?Q?Bj=C3=B6rn_Kautler?= <Bjoern@kautler.net>
Cc: pgsql-bugs@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAHewXNnVarUKNXrJXyDH%3DPKs-wST7bMzyqg61_SA2Mo5PPePgw%40mail.gmail.com>
Precedence: bulk

Bj=C3=B6rn Kautler <Bjoern@kautler.net> =E4=BA=8E2026=E5=B9=B43=E6=9C=884=
=E6=97=A5=E5=91=A8=E4=B8=89 20:31=E5=86=99=E9=81=93=EF=BC=9A
>
> Hi
>
> If you have the query
>
> WITH FOO AS (
>          SELECT 0 AS GROUPING
>      )
> SELECT CASE
>     WHEN GROUPING >=3D 0 THEN 'non-negative'
>     ELSE CAST((1 / 0) AS VARCHAR)
> END
> FROM FOO;
>
> it works successfully, having the ELSE as a safeguard against having code=
d 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 >=3D 0 THEN 'non-negative'

When the planner evaluates the targetList of the query, it finds that
0 >=3D 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 >=3D 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 >=3D 0 THEN
'non-negative'"
So ELSE CAST((1 / 0) AS VARCHAR) has the chance to process, then
"division by zero" error is reported.


--=20
Thanks,
Tender Wang