[DERBY-6849] Statement.RETURN_GENERATED_KEYS returns a 1 row result set even if there are no auto-generated fields

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.9.1.0
• Component/s: JDBC
• Assignee: Unassigned
• Reporter: John Hendrikx
• Created: 31/Dec/15 19:26
• Updated: 23/May/17 00:13

Description

If:
1) A JDBC INSERT statement is executed, with Statement.RETURN_GENERATED_KEYS enabled, and
2) A call is then made to Statement.getGeneratedKeys, and
3) The table which was inserted into has NO generated columns,

then getGeneratedKeys() returns a ResultSet object with a single row in it.

This behavior seems incorrect; it seems that the correct behavior
would be to return a ResultSet object which has NO rows in it, so
that ResultSet.next() returns FALSE the first time it is called.

I have a very simple table:

    CREATE TABLE images (
      url varchar(1000) NOT NULL,
      image blob NOT NULL,
  
      CONSTRAINT images_url PRIMARY KEY (url)
    );

No auto-generated fields. However when I do an insert, JDBC tells me there are auto-generated keys (rs.next() does not return false and a LONG value is returned):

      try(PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
        setParameters(parameterValues, statement);

        statement.execute();

        try(ResultSet rs = statement.getGeneratedKeys()) {
          if(rs.next()) {
            return rs.getObject(1);
          }

          return null;
        }
      }
      catch(SQLException e) {
        throw new DatabaseException(this, sql + ": " + parameters, e);
      }

This sounds like a bug to me. For comparison, PostgreSQL does not have the same behaviour.

Issue Links

• is related to: DERBY-3609 Wrong functionality of auto-generated keys support
• is related to: DERBY-6934 Lock conflicts between Statement.getGeneratedKeys() and sequence-generator-based identity columns
• is related to: DERBY-5823 Multi-row insert fails on table without generated keys with RETURN_GENERATED_KEYS