From: Tom Lane <tgl@sss.pgh.pa.us>
To: "=?utf-8?B?WmhhbmdDaGk=?=" <798604270@qq.com>
cc: "=?utf-8?B?RGF2aWQgRy4gSm9obnN0b24=?=" <david.g.johnston@gmail.com>,
        "=?utf-8?B?cGdzcWwtYnVnc0BsaXN0cy5wb3N0Z3Jlc3FsLm9yZw==?="
 <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19350: Short circuit optimization missed when running
 sqlscriptes in JDBC
In-reply-to: <tencent_95746975D7E33A69DE8B0CD8642AAE343405@qq.com>
References: 
 <CAKFQuwYR8P-BMX_=z6OmzGoFg1CO6Gu+hA1JMO+2GOV_7DQe0w@mail.gmail.com>
 <tencent_95746975D7E33A69DE8B0CD8642AAE343405@qq.com>
Comments: In-reply-to "=?utf-8?B?WmhhbmdDaGk=?=" <798604270@qq.com>
	message dated "Wed, 10 Dec 2025 22:01:22 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <637604.1765382821.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 10 Dec 2025 11:07:01 -0500
Message-ID: <637605.1765382821@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/637605.1765382821%40sss.pgh.pa.us>
Precedence: bulk

"=3D?utf-8?B?WmhhbmdDaGk=3D?=3D" <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 pre=
pare 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 di=
fferent behaviours. I wonder the reason for this discrepancy.

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

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

----------
         =

(1 row)

regression=3D# explain verbose EXECUTE prepare_query(0, NULL);
                         QUERY PLAN                          =

-------------------------------------------------------------
 Seq Scan on public.t0  (cost=3D0.00..23.60 rows=3D1360 width=3D8)
   Output: NULL::bigint
(2 rows)

regression=3D# SET plan_cache_mode =3D force_generic_plan;
SET

regression=3D# EXECUTE prepare_query(0, NULL);
ERROR:  division by zero
regression=3D# explain verbose EXECUTE prepare_query(0, NULL);
                         QUERY PLAN                          =

-------------------------------------------------------------
 Seq Scan on public.t0  (cost=3D0.00..33.80 rows=3D1360 width=3D8)
   Output: ((upper(c0) / $1) * $2)
(2 rows)

In the first case (with plan_cache_mode =3D 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 =3D custom.

			regards, tom lane