Thread

1. BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions

   PG Bug reporting form <noreply@postgresql.org> — 2025-06-20T17:42:06Z

   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
   
   

2. Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions

   David G. Johnston <david.g.johnston@gmail.com> — 2025-06-20T18:14:22Z

   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.
   

3. Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-20T18:37:10Z

   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
   
   
   
   

4. Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions

   Jeff Davis <pgsql@j-davis.com> — 2025-07-08T17:34:23Z

   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
   
   
   
   
   

5. Re: BUG #18965: Issue with Short-Circuit Evaluation in Boolean Expressions

   Jeff Davis <pgsql@j-davis.com> — 2025-07-08T17:48:43Z

   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