Thread

Re: BUG #19350: Short circuit optimization missed when running sqlscriptes in JDBC

Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-10T16:07:01Z

"=?utf-8?B?WmhhbmdDaGk=?=" <798604270@qq.com> writes:
> Yes! You are right. I mean, when I run this test case with the wrapper I provide, which executes each SQL statement with JDBC (but without the prepare API of JDBC), then the test case will trigger the error. But if I run it in CLI, for example `psql -U root -W root -p 5433 -h 127.0.0.1 < test.sql`, then there is no error. I do not modify the test caes. But it has different behaviours. I wonder the reason for this discrepancy.

It's not the same query, or at least not the same plan.

regression=# PREPARE prepare_query (int8, int8) AS SELECT
regression-# ((((((upper(t0.c0))))/($1)))*(($2::int8))) FROM ONLY t0;
PREPARE
regression=# EXECUTE prepare_query(0, NULL);
 ?column? 
----------
         
(1 row)

regression=# explain verbose EXECUTE prepare_query(0, NULL);
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on public.t0  (cost=0.00..23.60 rows=1360 width=8)
   Output: NULL::bigint
(2 rows)

regression=# SET plan_cache_mode = force_generic_plan;
SET

regression=# EXECUTE prepare_query(0, NULL);
ERROR:  division by zero
regression=# explain verbose EXECUTE prepare_query(0, NULL);
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on public.t0  (cost=0.00..33.80 rows=1360 width=8)
   Output: ((upper(c0) / $1) * $2)
(2 rows)

In the first case (with plan_cache_mode = auto), the EXECUTE
will generate a plan in which the given parameter values are
inserted into the query and then the result is constant-folded
before execution.  With a generic plan, that doesn't happen,
so we reach the division and fail.

Yeah, this isn't super consistent, but we're unlikely to
change it.  The only thing we could do to make it consistent
is to mostly-disable constant folding, which would be a
performance disaster.  What you should do if you want closer
consistency of prepared statements and direct execution
is to set plan_cache_mode = custom.

			regards, tom lane