public inbox for [email protected]  

help / color / mirror / Atom feed

From: Björn Kautler <[email protected]>
To: [email protected]
Subject: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
Date: Wed, 4 Mar 2026 11:46:12 +0100
Message-ID: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com> (raw)

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.

So if you have

WITH FOO AS (
         SELECT -1 AS GROUPING
     )
SELECT CASE
    WHEN GROUPING >= 0 THEN 'non-negative'
    ELSE CAST((1 / 0) AS VARCHAR)
END
FROM FOO;

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'.

Cheers
Björn

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]
  Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
  In-Reply-To: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@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