Bug #113130 getGeneratedKeys() returns a zero resultset with non-key-generating statements
Submitted: 19 Nov 2023 2:36 Modified: 24 Nov 2023 6:49
Reporter: Wenqian Deng Email Updates:
Status: Verified Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.1.0 OS:Any
Assigned to: CPU Architecture:Any

[19 Nov 2023 2:36] Wenqian Deng
According to JDBC specifications, if a Statement object does not generate any keys, an empty ResultSet object should be returned by getGeneratedKeys(). However, in the provided test case, using the MySQL JDBC connector, getGeneratedKeys() returns a ResultSet with rows containing zeros (0) instead of an empty ResultSet.

In the test case, a batch of INSERT statements is executed on a table where no auto-generated keys are expected (since the keys are explicitly provided in the INSERT statements). Despite this, the call to stmt.getGeneratedKeys() yields a ResultSet where each row contains a zero, which is inconsistent with the expected behavior of returning an empty ResultSet.

The expected behavior, as per JDBC documentation and observed in the MariaDB connector, is to return an empty ResultSet when no keys are generated by the executed statement.

How to repeat:
public void test() throws SQLException {
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3366/test399?user=user&password=password&allowMultiQueries=true");
    con.createStatement().execute("CREATE TABLE table399_0(id INT PRIMARY KEY,value BOOL);");
    Statement stmt = con.createStatement();
    // this method returns empty resultset
    // stmt.execute("INSERT INTO table399_0 VALUES(1, false)", 1);
    stmt.addBatch("INSERT INTO table399_0 VALUES(1, false)");
    stmt.addBatch("INSERT INTO table399_0 VALUES(2, false)");
    ResultSet rs = stmt.getGeneratedKeys();
    while (rs.next()) {
        System.out.println(rs.getInt(1)); // returns 0, 0
[24 Nov 2023 6:49] MySQL Verification Team
Hello Wenqian Deng,

Thank you for the report and test case.
Verified as described.