From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: =?UTF-8?Q?Bj=C3=B6rn_Kautler?= <Bjoern@kautler.net>,
        Tender Wang <tndrwang@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>,
        Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
In-reply-to: <CAKFQuwZT0UaOiV+2h6f42_U_tU34wXH3Gv8BrjF_U168SWS5PQ@mail.gmail.com>
References: <CAKChYSo2n2_q_XtvO-3ow+Q0dSuWdzwydLbtr3Q12X65bqhL=g@mail.gmail.com> <CAHewXNnVarUKNXrJXyDH=PKs-wST7bMzyqg61_SA2Mo5PPePgw@mail.gmail.com> <CAKChYSrNec-ucGwK+-gjCGGSkcq1EFxi6C3RBCz5+-DwS2feJg@mail.gmail.com> <CAKFQuwZT0UaOiV+2h6f42_U_tU34wXH3Gv8BrjF_U168SWS5PQ@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 04 Mar 2026 12:32:04 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1656486.1772656642.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 04 Mar 2026 15:37:22 -0500
Message-ID: <1656487.1772656642@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1656487.1772656642%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wednesday, March 4, 2026, Bj=C3=B6rn Kautler <Bjoern@kautler.net> wro=
te:
>> What I tried to do was to provoke the division-by-zero error if I happe=
ned
>> to forget some WHEN branch as this is a bug.
>> Is there some safe way to do something like this you are aware of

> Write a volatile =E2=80=9Cfail()=E2=80=9D plpgsql function, and call it =
in the else block.
> Raise exception there.

Right.  An intentional failure is a side-effect, and immutable
subexpressions are not supposed to have side-effects.  As long
as you mark the fail() function volatile, the planner will avoid
pre-evaluating it.

			regards, tom lane