Horton

Thread

  1. Re: BUG #19350: Short circuit optimization missed when runningsqlscriptes in JDBC

    ZhangChi <798604270@qq.com> — 2025-12-17T01:40:29Z

    Hi Greg,


Thanks for your reply. I can understand what you describe.


But my question is that in the CLI, the test case (like foo2) I showed can return the results. But when I run the same test case in JDBC, the test case triggers an error. I wonder why there is an inconsistency.


```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 int4range);
INSERT INTO t0(c0) VALUES('[-1920846305,-1018839689)'::int4range);
PREPARE prepare_query (int8, int8) AS SELECT
((((((upper(t0.c0))))/($1)))*(($2::int8))) FROM ONLY t0;
EXECUTE prepare_query(0, NULL);   -- trigger error in JDBC but no error in CLI
```


         Original
         
       
From: Greg Sabino Mullane <htamfids@gmail.com&gt;
Date: 2025-12-16 23:17
To: ZhangChi <798604270@qq.com&gt;
Cc: David G. Johnston <david.g.johnston@gmail.com&gt;, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org&gt;
Subject: Re: BUG #19350: Short circuit optimization missed when runningsqlscriptes in JDBC



I wonder why this query triggers an error under JDBC but not in the CLI.


Fails as expected from the command line:


prepare foo1 as select 99 / $1::int * $2;
PREPARE
execute foo1(0, null);
ERROR: &nbsp;division by zero


prepare foo2 as select 99 / ($1::int * $2);
PREPARE
execute foo2(0, null);
&nbsp;?column?
----------
&nbsp; &nbsp; &nbsp; &nbsp; ☃
(1 row)

prepare foo3 as select (99 / $1::int) * $2;
PREPARE
execute foo3(0, null);
ERROR: &nbsp;division by zero


If you want the "null combined with anything = null" to always happen before the "divide by zero is wrong" make your query specific like in foo2 above.




Cheers,
Greg


--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products &amp; Tech Support