BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions
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
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.
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
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
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