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

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 10.9.1.0
    • None
    • JDBC
    • 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. DERBY6849Repro.java
          1 kB
          Bryan Pendleton
        2. firstExperimentNotForCommit.diff
          7 kB
          Bryan Pendleton
        3. generated-keys-with-tests.diff
          5 kB
          Bryan Pendleton

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated: