MySQL Bugs: #119793: Incorrect result of prepared SELECT when running with JDBC

Bug #119793	Incorrect result of prepared SELECT when running with JDBC
Submitted:	27 Jan 10:32	Modified:	4 Feb 16:32
Reporter:	chi zhang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	9.5.0	OS:	Any
Assigned to:		CPU Architecture:	Any

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

Thank you for your interest in MySQL Connector/J and for taking the time to write this report. 

I reviewed your queries using the MySQL client: the SELECT statement returned NULL, while the prepared statement returned an empty set. This appears to match the behavior you observed in your Java code. 

If you are still experiencing an issue, could you please provide a clearer test case or additional details?

Thank you!

Hi Filipe Silva,

Thanks for your work! I want to say that these two queries are equivalent, but have different results, so there maybe a logic bug in MySQL.

Hi Chi Zhang,

Still not clear to me. You are saying you see the same behavior in the mysql client and your Java program using Connector/J?

If so, then the issue as you describe it, is that the direct SELECT query returns one row with NULL value, while the prepared statement returns no rows. Is that so?

If so, then this is not a Connector/J bug and needs to be re-categorized.

Thanks,

Hi Filipe Silva,

I tried this case again on my computer, and found that in CLI, these two queries have the same result. I run MySQL in Docker with command:

```
docker run -itd --name mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:latest
```

Then run this test case with command:

```
docker exec -i mysql mysql -u root -proot -f < test.sql
```

The output is:
```
ref0
NULL
ref0
NULL
```

Could you please try with these commands? 
Thanks

Hi Chi Zhang,

In fact, running the queries from within the same container does have the result you mentioned. However, if I run the same from my local system then the result is different:

```
$ docker exec -i mysql mysql -u root -proot -f < test.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ref0
NULL
ref0
NULL
```

vs

```
$ ./mysql --protocol=tcp --port=3306 --user=root --password=root < test.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ref0
NULL
```

My assumption at this point is that this has to do with the character set defined in the system where the client is running. In any case, Connector/J should have no interference in the results as it simply hands over the queries to the server, same as the mysql client does.

I'm changing this bug category so that the appropriate team may look into it.

Meanwhile, if you can prove that a similar use case using Java + Connector/J and the mysql client, under the same circumstances, have different results, then please provide a complete reproducible test case here or in a new bug report.

Thank you for the bug report.
Verified as a problem with prepared statements.

When SQL statement to be prepared contains characters
specific to UTF8MB4 character set, these are silently
replaced with '?' characters. This causes different
execution compared to regular SQL statements.