Bug #119793 Incorrect result of prepared SELECT when running with JDBC

• Submitted: 27 Jan 10:32
• Reporter: chi zhang
• Status: Open
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[27 Jan 10:32] chi zhang

Description:
Hi,

In the following test case, there are two equivalent queries. When I run this test case in CLI, they have the same results. But when I run this test case through JDBC, the prepared SELECT will have an empty result.

```
CREATE TABLE IF NOT EXISTS t2(c0 DECIMAL ) ;
INSERT IGNORE INTO t2(c0) VALUES(1477094933);
SELECT LEAST(NULL, t2.c0) AS ref0 FROM t2 WHERE LEAST(CAST(CAST(0.7017757680214883 AS DOUBLE) AS SIGNED), "񜥪q<V") GROUP BY LEAST(NULL, t2.c0); -- NULL
SET @a = 0.7017757680214883;
PREPARE prepare_query FROM 'SELECT LEAST(NULL, t2.c0) AS ref0 FROM t2 WHERE LEAST(CAST(CAST(? AS DOUBLE) AS SIGNED), "񜥪q<V") GROUP BY LEAST(NULL, t2.c0)';
EXECUTE prepare_query USING @a; -- empty result
```

How to repeat:
This is the test case:

```
CREATE TABLE IF NOT EXISTS t2(c0 DECIMAL ) ;
INSERT IGNORE INTO t2(c0) VALUES(1477094933);
SELECT LEAST(NULL, t2.c0) AS ref0 FROM t2 WHERE LEAST(CAST(CAST(0.7017757680214883 AS DOUBLE) AS SIGNED), "񜥪q<V") GROUP BY LEAST(NULL, t2.c0); -- NULL
SET @a = 0.7017757680214883;
PREPARE prepare_query FROM 'SELECT LEAST(NULL, t2.c0) AS ref0 FROM t2 WHERE LEAST(CAST(CAST(? AS DOUBLE) AS SIGNED), "񜥪q<V") GROUP BY LEAST(NULL, t2.c0)';
EXECUTE prepare_query USING @a; -- empty result
```

This is the java program:

```
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLRunner {

    public static void main(String[] args) {
        // Default file path to the one specified by the user
        String filePath = "test.sql";
        if (args.length > 0) {
            filePath = args[0];
        }

        File file = new File(filePath);
        if (!file.exists()) {
            System.out.println("Error: File not found: " + filePath);
            return;
        }

        // Connection details (assuming local defaults based on context)
        String url = "jdbc:mysql://127.0.0.1:3306/?allowMultiQueries=true&nullCatalogMeansCurrent=true&useSSL=false";
        String user = "root";
        String password = "sqlancer";

        System.out.println("Connecting to database at " + url + " ...");
        try (Connection conn = DriverManager.getConnection(url, user, password);
             BufferedReader br = new BufferedReader(new FileReader(file));
             Statement stmt = conn.createStatement()) {

            System.out.println("Connected.");
            System.out.println("Reading SQL from: " + filePath);
            System.out.println("--------------------------------------------------");

            String line;
            StringBuilder sqlBuffer = new StringBuilder();

            while ((line = br.readLine()) != null) {
                line = line.trim();
                
                // Skip empty lines and comments
                if (line.isEmpty() || line.startsWith("--") || line.startsWith("#")) {
                    continue;
                }

                sqlBuffer.append(line).append(" ");

                // Assumption: SQL statements end with a semicolon
                if (line.endsWith(";")) {
                    String sql = sqlBuffer.toString().trim();
                    sqlBuffer.setLength(0); // clear buffer
                    
                    if (!sql.isEmpty()) {
                        executeSQL(stmt, sql);
                    }
                }
            }
            
            // Execute any remaining incomplete statement
            if (sqlBuffer.length() > 0) {
                 String sql = sqlBuffer.toString().trim();
                 if (!sql.isEmpty()) {
                     executeSQL(stmt, sql);
                 }
            }

        } catch (SQLException e) {
            System.out.println("Database connection error: " + e.getMessage());
            System.out.println("Make sure MySQL is running on 127.0.0.1:3306 and user/password are correct.");
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        }
    }

    private static void executeSQL(Statement stmt, String sql) {
        System.out.println("Executing: " + sql);
        try {
            boolean hasResultSet = stmt.execute(sql);
            if (hasResultSet) {
                try (ResultSet rs = stmt.getResultSet()) {
                    printResultSet(rs);
                }
            } else {
                System.out.println("Success");
            }
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
        System.out.println("--------------------------------------------------");
    }

    private static void printResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        System.out.println("Output:");
        // Print columns
        for (int i = 1; i <= columnCount; i++) {
            if (i > 1) System.out.print("\t");
            System.out.print(metaData.getColumnLabel(i));
        }
        System.out.println();

        // Print rows
        while (rs.next()) {
            for (int i = 1; i <= columnCount; i++) {
                if (i > 1) System.out.print("\t");
                String val = rs.getString(i);
                System.out.print(val);
            }
            System.out.println();
        }
    }
}

```

I can reproduce this with JDBC mysql-connector-j-8.0.33.jar