| Bug #113130 | getGeneratedKeys() returns a zero resultset with non-key-generating statements | ||
|---|---|---|---|
| Submitted: | 19 Nov 2023 2:36 | Modified: | 14 Jan 21:18 |
| Reporter: | Wenqian Deng | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 8.1.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 Nov 2023 6:49]
MySQL Verification Team
Hello Wenqian Deng, Thank you for the report and test case. Verified as described. regards, Umesh
[14 Jan 21:18]
Edward Gilmore
Posted by developer:
Added the following note to the MySQL Connector/J 9.6.0 release notes:
Connector/J returned a result set of generated keys
containing only 0 values after a batch of queries was
rewritten into a multi-query, even though no keys were generated.
Connector/J now checks the value of the last
generated key for each executed query and excludes keys with a
value of zero from the result set. As a result,
Statement.getGeneratedKeys() now accurately returns an empty
result set when no keys are generated, including for multi-query
batch executions.
Description: 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: @Test 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)"); stmt.executeBatch(); ResultSet rs = stmt.getGeneratedKeys(); while (rs.next()) { System.out.println(rs.getInt(1)); // returns 0, 0 } }