Обсуждение: BUG #19350: Short circuit optimization missed when running sql scriptes in JDBC

The following bug has been logged on the website:

Bug reference:      19350
Logged by:          Chi Zhang
Email address:      798604270@qq.com
PostgreSQL version: 17.6
Operating system:   ubuntu 24.04 with docker
Description:

Hi,

In the following test case, if I run it in JDBC, the prepared statement will
trigger a divide by zero error. But if run it in command line, there will
not be the same error.

```
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
SELECT ((((((upper(t0.c0))))/(0::int8)))*((NULL::int8))) FROM ONLY t0;   --
always no error
```

This is the Java workload:
```
import java.io.*;
import java.sql.*;
import java.util.Properties;

public class PostgresExecutor {

    public static void main(String[] args) {
        if (args.length != 1) {
            System.err.println("usage: java PostgresExecutor <sql file
path>");
            System.exit(1);
        }

        String filePath = args[0];
        String url = "jdbc:postgresql://localhost:5433/test";
        String user = "sqlancer";
        String password = "sqlancer";

        try (Connection conn = DriverManager.getConnection(url, user,
password);
             BufferedReader reader = new BufferedReader(new
FileReader(filePath))) {

            System.out.println("Successfully connected to Postgres
database.");

            String line;
            try (Statement stmt = conn.createStatement()) {
                while ((line = reader.readLine()) != null) {
                    line = line.trim();
                    if (line.isEmpty() || line.startsWith("--")) {
                        continue;
                    }

                    try {
                        System.out.println("Executing SQL: " + line);
                        boolean hasResult = stmt.execute(line);
                        if (hasResult) {
                            try (ResultSet rs = stmt.getResultSet()) {
                                ResultSetMetaData meta = rs.getMetaData();
                                int columnCount = meta.getColumnCount();

                                while (rs.next()) {
                                    for (int i = 1; i <= columnCount; i++) {
                                        System.out.print(rs.getString(i));
                                        if (i < columnCount)
System.out.print("\t");
                                    }
                                    System.out.println();
                                }
                            }
                        } else {
                            int updateCount = stmt.getUpdateCount();
                            System.out.println("Execute successfully: " +
updateCount + " rows affected");
                        }
                    } catch (SQLException e) {
                        System.err.println("Execute error: " + line);
                        e.printStackTrace();
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

I can reproduce this with postgresql-42.7.8.jar
```
java -cp
/root/.m2/repository/org/postgresql/postgresql/42.7.8/postgresql-42.7.8.jar
PostgresExecutor.java test.sql

The following bug has been logged on the website:

Bug reference:      19350
Logged by:          Chi Zhang
Email address:      798604270@qq.com
PostgreSQL version: 17.6
Operating system:   ubuntu 24.04 with docker
Description:       

Hi,

In the following test case, if I run it in JDBC, the prepared statement will
trigger a divide by zero error. But if run it in command line, there will
not be the same error.

The following bug has been logged on the website:

Bug reference:      19350
Logged by:          Chi Zhang
Email address:      798604270@qq.com
PostgreSQL version: 17.6
Operating system:   ubuntu 24.04 with docker
Description:       

Hi,

In the following test case, if I run it in JDBC, the prepared statement will
trigger a divide by zero error. But if run it in command line, there will
not be the same error.

Hi David J.

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

Hi David J.

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

"=?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
withJDBC (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.
Butit 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

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

Dave Cramer <davecramer@postgres.rocks> writes:
> On Tue, 16 Dec 2025 at 20:40, ZhangChi <798604270@qq.com> wrote:
>> 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.

> Possibly because JDBC does everything using the V3 protocol whereas psql
> does not. psql uses simple query.

The error will occur if we try to build a "custom plan" for the
prepared query.  I suspect a discrepancy in the plan_cache_mode
settings between JDBC and the user's psql setup.

            regards, tom lane