Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6849

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

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: None
    • Component/s: JDBC
    • Labels:
      None

      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.

        Attachments

        1. generated-keys-with-tests.diff
          5 kB
          Bryan Pendleton
        2. firstExperimentNotForCommit.diff
          7 kB
          Bryan Pendleton
        3. DERBY6849Repro.java
          1 kB
          Bryan Pendleton

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                john16384 John Hendrikx
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: