pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions
5+ messages / 4 participants
[nested] [flat]
* BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions
@ 2025-06-20 17:42 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2025-06-20 17:42 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: brandystodd@gmail.com
The following bug has been logged on the website:
Bug reference: 18965
Logged by: Todd Brandys
Email address: brandystodd@gmail.com
PostgreSQL version: 17.5
Operating system: Linux
Description:
In the circumstance where a function evaluation is performed within Boolean
expression, the evaluation seems to continue past a function returning a
TRUE value. Here is a very boiled down version of my code, but it results
in the same issue. In the first SELECT statement, I get the expected
result, a single row with a TRUE value. In the other two SELECT statements,
an EXCEPTION is thrown, which is unexpected.
CREATE OR REPLACE FUNCTION raise(
IN i_msg text
)
RETURNS text AS $$
BEGIN
RAISE EXCEPTION '%', i_msg;
RETURN ''::text;
END;$$
LANGUAGE PLPGSQL
IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION test( )
RETURNS boolean AS $$
SELECT TRUE; $$
LANGUAGE SQL
IMMUTABLE STRICT;
SELECT TRUE OR public.raise('this exception should not be
raised')::boolean;
SELECT pg_catalog.PG_HAS_ROLE('postgres', 'postgres', 'member')
OR public.raise('this exception should not be raised')::boolean;
SELECT public.test() OR public.raise('this exception should not be
raised')::boolean;
Again, I am using PostgreSQL 17.5, compiled from source. I have no
extensions installed in the database. Here is the configure script I used
to build the distribution:
export PYTHON=/var/lib/pgsql/venv/bin/python3
./configure \
--prefix=/usr/local/installed/postgresql-17.5 \
--enable-atomics --enable-largefile --with-llvm --with-perl
--with-readline --with-python \
--with-uuid=ossp --with-zlib --with-ssl=openssl --with-libxml
--with-libxslt
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions
@ 2025-06-20 18:14 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2025-06-20 18:14 UTC (permalink / raw)
To: brandystodd@gmail.com <brandystodd@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Friday, June 20, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18965
> Logged by: Todd Brandys
> Email address: brandystodd@gmail.com
> PostgreSQL version: 17.5
> Operating system: Linux
> Description:
>
> In the circumstance where a function evaluation is performed within Boolean
> expression, the evaluation seems to continue past a function returning a
> TRUE value.
Where did we claim we perform short-circuiting?
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions
@ 2025-06-20 18:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-06-20 18:37 UTC (permalink / raw)
To: brandystodd@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> CREATE OR REPLACE FUNCTION raise(
> IN i_msg text
> )
> RETURNS text AS $$
> BEGIN
> RAISE EXCEPTION '%', i_msg;
> RETURN ''::text;
> END;$$
> LANGUAGE PLPGSQL
> IMMUTABLE STRICT;
I think the fundamental problem you're having is that you marked
this function IMMUTABLE, which gives the planner license to
pre-evaluate it. It had better be VOLATILE to discourage advance
evaluation.
https://www.postgresql.org/docs/current/xfunc-volatility.html
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions
@ 2025-07-08 17:34 Jeff Davis <pgsql@j-davis.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Davis @ 2025-07-08 17:34 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; brandystodd@gmail.com <brandystodd@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Fri, 2025-06-20 at 11:14 -0700, David G. Johnston wrote:
> > In the circumstance where a function evaluation is performed within
> > Boolean
> > expression, the evaluation seems to continue past a function
> > returning a
> > TRUE value.
> >
>
> Where did we claim we perform short-circuiting?
Even if you force the execution-time evaluation order with CASE, you
can still get an error:
EXPLAIN SELECT CASE WHEN random() < 2 THEN TRUE ELSE (1/0 = 0) END;
ERROR: division by zero
The expression "random() < 2" is always true, so at execution time the
second branch will never be reached. But it is reached at planning
time.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions
@ 2025-07-08 17:48 Jeff Davis <pgsql@j-davis.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Davis @ 2025-07-08 17:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; brandystodd@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
On Fri, 2025-06-20 at 14:37 -0400, Tom Lane wrote:
> I think the fundamental problem you're having is that you marked
> this function IMMUTABLE, which gives the planner license to
> pre-evaluate it. It had better be VOLATILE to discourage advance
> evaluation.
>
> https://www.postgresql.org/docs/current/xfunc-volatility.html
What you say is true in the narrow sense that VOLATILE prevents
planner-time evaluation. But it doesn't generalize into a mathematical
rule about how to mark a function: one might conclude (falsely)
that IMMUTABLE functions must not be able to throw exceptions, but
clearly they can. For instance, int4div():
EXPLAIN SELECT * FROM a WHERE (1/1 = 0);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)
EXPLAIN SELECT * FROM a WHERE (1/0 = 0);
ERROR: division by zero
If you try to fix that by marking int4div as VOLATILE, you'd have to do
the same for int4pl (which can overflow), and any function that can run
out of memory.
The way I see it, the problem is not the function marking, nor is it
the evaluation order. It's that there's an expression in the query
that's impossible to evaluate.
Todd, you mentioned that you started from a more complex scenario, can
you give some more details about how such an expression ended up in the
original query, and what you'd like to happen?
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-07-08 17:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-20 17:42 BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions PG Bug reporting form <noreply@postgresql.org>
2025-06-20 18:14 ` David G. Johnston <david.g.johnston@gmail.com>
2025-07-08 17:34 ` Jeff Davis <pgsql@j-davis.com>
2025-06-20 18:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-07-08 17:48 ` Jeff Davis <pgsql@j-davis.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox