public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tender Wang <[email protected]>
To: Björn Kautler <[email protected]>
Cc: [email protected]
Cc: Laurenz Albe <[email protected]>
Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
Date: Wed, 4 Mar 2026 21:42:21 +0800
Message-ID: <CAHewXNnVarUKNXrJXyDH=PKs-wST7bMzyqg61_SA2Mo5PPePgw@mail.gmail.com> (raw)
In-Reply-To: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>
References: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>

Björn Kautler <[email protected]> 于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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
  In-Reply-To: <CAHewXNnVarUKNXrJXyDH=PKs-wST7bMzyqg61_SA2Mo5PPePgw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox