Bug #113326 Statement.getResultSetType () seems failed to change the result set type

• Submitted: 5 Dec 2023 4:41
• Modified: 29 Jul 6:46
• Reporter: Wenqian Deng
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.1.0
• OS: Any
• CPU Architecture: Any

[5 Dec 2023 4:41] Wenqian Deng

Description:
When a Statement is created with a specific ResultSet type 1003, the expectation is that any ResultSet derived from this Statement should have the same type. However, in the provided test case, the ResultSet obtained from getGeneratedKeys() reports a type of ResultSet.TYPE_SCROLL_INSENSITIVE (1004), which is inconsistent with the type specified when creating the Statement.

How to repeat:
@Test
public void test() throws SQLException {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    con = DriverManager.getConnection("jdbc:mysql://localhost:3366/test69?user=user&password=password");
    stmt = con.createStatement(1003, 1008, 2);
    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);
    rs = stmt.getGeneratedKeys();
    System.out.println(stmt.getResultSetType()); // 1003
    System.out.println(rs.getType()); // 1004
}

[6 Dec 2023 7:21] MySQL Verification Team

Hello Wenqian Deng,

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

regards,
Umesh

[29 Jul 6:46] Axyoan Marcelo

Posted by developer:
 
The ResultSet obtained from getGeneratedKeys() is not like the usual ResultSets one would obtain from executing queries. It's implementation is driver specific, as stated in the specification.
According to the JDBC 4.3 specification (section 13.6 “Retrieving Auto Generated Values”):
...“The type of the ResultSet object (returned from Statement.getGeneratedKeys()) must be either TYPE_FORWARD_ONLY or TYPE_SCROLL_INSENSITIVE..

Connector/J uses TYPE_SCROLL_INSENSITIVE when creating the generated‑keys ResultSet. This fully conforms to the spec.

Similarly, the concurrency will always be CONCUR_READ_ONLY, as found in the specification (same section as above):
"The concurrency of the ResultSet object returned by getGeneratedKeys must be CONCUR_READ_ONLY."

Closing as not a bug.