public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Björn Kautler <[email protected]>
To: [email protected]
Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
Date: Wed, 04 Mar 2026 13:55:53 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>
References: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>

On Wed, 2026-03-04 at 11:46 +0100, Björn Kautler wrote:
> 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'.

This is working as it should, see
https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

  When it is essential to force evaluation order, a CASE construct (see Section 9.18)
  can be used. For example, this is an untrustworthy way of trying to avoid division
  by zero in a WHERE clause:

    SELECT ... WHERE x > 0 AND y/x > 1.5;

  But this is safe:

    SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

  A CASE construct used in this fashion will defeat optimization attempts, so it
  should only be done when necessary. (In this particular example, it would be better
  to sidestep the problem by writing y > 1.5*x instead.)

  CASE is not a cure-all for such issues, however. One limitation of the technique
  illustrated above is that it does not prevent early evaluation of constant
  subexpressions. As described in Section 36.7, functions and operators marked
  IMMUTABLE can be evaluated when the query is planned rather than when it is
  executed. Thus for example

    SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;

  is likely to result in a division-by-zero failure due to the planner trying to
  simplify the constant subexpression, even if every row in the table has x > 0
  so that the ELSE arm would never be entered at run time.

Yours,
Laurenz Albe

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]
  Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
  In-Reply-To: <[email protected]>

* 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