Bug #112790 Statement.getGeneratedKeys() returns unexpected value
Submitted: 21 Oct 2023 3:57 Modified: 4 Oct 22:53
Reporter: Wenqian Deng Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.1.0 OS:Any
Assigned to: CPU Architecture:Any

[21 Oct 2023 3:57] Wenqian Deng 
Description:
In my test case, when I use Statement to execute and then attempt to retrieve the latest GeneratedKeys, I expect to get a value of 3. 
However, the returned values are 1 and 2, which are the values I inserted previously. I think this behavior is incorrect. I also have this test case on MariaDB Connector J, and it returns 3.

How to repeat:
@Test
public void test() throws SQLException {
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?user=user&password=password");
    Statement stmt = con.createStatement();
    stmt.addBatch("DROP TABLE IF EXISTS table0_0;");
    stmt.addBatch("CREATE TABLE table0_0(id INT AUTO_INCREMENT PRIMARY KEY,value INT);");
    stmt.addBatch("INSERT INTO table0_0 VALUES(1, -179653912)");
    stmt.addBatch("INSERT INTO table0_0 VALUES(2, 1207965915)");
    stmt.executeBatch();

    stmt.executeUpdate("INSERT INTO table0_0 (value) VALUES(667711856)", Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stmt.getGeneratedKeys();
    while (rs.next()) {
        System.out.println(rs.getInt(1));
    }
}
[23 Oct 2023 7:28] MySQL Verification Team 
Hello Wenqian Deng,

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

regards,
Umesh Shastry
[4 Oct 22:53] Daniel So 
Posted by developer:
 
Added the following entry to the Connector/J 9.1.0 changelog: 

"After using executeBatch() to insert rows into a table and adding more rows with executeUpdate(), getGeneratedKeys() returned the wrong keys for the inserted rows."