Message-ID: <3242d4f3942eef5230229e22b08d4dd24ae6d19f.camel@cybertec.at>
Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely
 evaluated
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?ISO-8859-1?Q?Bj=F6rn?= Kautler <Bjoern@kautler.net>, 
	pgsql-bugs@lists.postgresql.org
Date: Wed, 04 Mar 2026 13:55:53 +0100
In-Reply-To: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>
References: 
	<CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/3242d4f3942eef5230229e22b08d4dd24ae6d19f.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-03-04 at 11:46 +0100, Bj=C3=B6rn Kautler wrote:
> If you have the query
>=20
> WITH FOO AS (
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT 0 AS GROUPING
> =C2=A0 =C2=A0 =C2=A0)
> SELECT CASE
> =C2=A0 =C2=A0 WHEN GROUPING >=3D 0 THEN 'non-negative'
> =C2=A0 =C2=A0 ELSE CAST((1 / 0) AS VARCHAR)
> END
> FROM FOO;
>=20
> it works successfully, having the ELSE as a safeguard against having code=
d a bug, having forgotten a WHEN branch, so it fails fast.
>=20
> So if you have
>=20
> WITH FOO AS (
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT -1 AS GROUPING
> =C2=A0 =C2=A0 =C2=A0)
> SELECT CASE
> =C2=A0 =C2=A0 WHEN GROUPING >=3D 0 THEN 'non-negative'
> =C2=A0 =C2=A0 ELSE CAST((1 / 0) AS VARCHAR)
> END
> FROM FOO;
>=20
> it fails with a division by zero error.
>=20
> But if you have the query
>=20
> WITH FOO AS (
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT 0 AS GROUPING
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0GROUP BY 1
> =C2=A0 =C2=A0 =C2=A0)
> SELECT CASE
> =C2=A0 =C2=A0 WHEN GROUPING >=3D 0 THEN 'non-negative'
> =C2=A0 =C2=A0 ELSE CAST((1 / 0) AS VARCHAR)
> END
> FROM FOO;
>=20
> 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 Sec=
tion 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 te=
chnique
  illustrated above is that it does not prevent early evaluation of constan=
t
  subexpressions. As described in Section 36.7, functions and operators mar=
ked
  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 tryi=
ng 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