Derby
  1. Derby
  2. DERBY-3609

Wrong functionality of auto-generated keys support

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3.2.1
    • Fix Version/s: None
    • Component/s: Documentation, JDBC
    • Environment:
      N/A
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Repro attached
    • Bug behavior facts:
      Deviation from standard

      Description

      Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").

      Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").

      1. TestGeneratedKeys.java
        3 kB
        Oleksandr Alesinskyy
      2. generated-keys.diff
        2 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Oleksandr Alesinskyy created issue -
          Hide
          Bernt M. Johnsen added a comment -

          Formally, Derby does not support getGeneratedKeys since DatabaseMetaData.supportsGetGeneratedKeys() returns false. However, Statement.getGeneratedKeys() is partially implemented, and behaves very similar to IDENTITY_VAL_LOCAL() since it will only return a meaningful result when an single row insert is done with INSERT...VALUES. However, you have to specify Statement.RETURN_GENERATED_KEYS, e.g. like this

          s.executeUpdate("insert into t values (1)", Statement.RETURN_GENERATED_KEYS);

          I agree that returning null from getGeneratedKeys is not according to spec and that an ResultSet with no rows would be more appropriate. Also, the docs for "Autogenerated keys" in the ref.man. would need a rewrite to be comprehendable. Another quirk is that getGeneratedKeys() will return a resultset with one row with an SQL null-value if the statement was an insert of multiple rows, and not like DENTITY_VAL_LOCAL() which returns the value of the generated key from the last single-row insert.

          Show
          Bernt M. Johnsen added a comment - Formally, Derby does not support getGeneratedKeys since DatabaseMetaData.supportsGetGeneratedKeys() returns false. However, Statement.getGeneratedKeys() is partially implemented, and behaves very similar to IDENTITY_VAL_LOCAL() since it will only return a meaningful result when an single row insert is done with INSERT...VALUES. However, you have to specify Statement.RETURN_GENERATED_KEYS, e.g. like this s.executeUpdate("insert into t values (1)", Statement.RETURN_GENERATED_KEYS); I agree that returning null from getGeneratedKeys is not according to spec and that an ResultSet with no rows would be more appropriate. Also, the docs for "Autogenerated keys" in the ref.man. would need a rewrite to be comprehendable. Another quirk is that getGeneratedKeys() will return a resultset with one row with an SQL null-value if the statement was an insert of multiple rows, and not like DENTITY_VAL_LOCAL() which returns the value of the generated key from the last single-row insert.
          Hide
          Oleksandr Alesinskyy added a comment -

          Regrettably, after INSERT...SELECT returned resultset contains not null as you stated, but key generated by last single row INSERT..VALUES on the same table.
          This behavior is completely misleading. It is bad enough for IDENTITY_VAL_LOCAL(), but (IMHO) absolutely unacceptable for getGeneratedKeys().

          And here is log from test programm that proves my point:

          ---------------------------------------------------------------------------
          insert values has generated id 1, is null false
          insert as select same table has inserted 1 rows
          insert as select from same table has generated id 1, is null false
          insert as select from another table has inserted 1 rows
          insert as select from another has generated id 1, is null false
          MUMU contents id 1, is null false
          MUMU contents id 2, is null false
          MUMU contents id 3, is null false
          Done.
          ------------------------------------------------------------------------------

          Progam source is attached

          Show
          Oleksandr Alesinskyy added a comment - Regrettably, after INSERT...SELECT returned resultset contains not null as you stated, but key generated by last single row INSERT..VALUES on the same table. This behavior is completely misleading. It is bad enough for IDENTITY_VAL_LOCAL(), but (IMHO) absolutely unacceptable for getGeneratedKeys(). And here is log from test programm that proves my point: --------------------------------------------------------------------------- insert values has generated id 1, is null false insert as select same table has inserted 1 rows insert as select from same table has generated id 1, is null false insert as select from another table has inserted 1 rows insert as select from another has generated id 1, is null false MUMU contents id 1, is null false MUMU contents id 2, is null false MUMU contents id 3, is null false Done. ------------------------------------------------------------------------------ Progam source is attached
          Hide
          Oleksandr Alesinskyy added a comment -

          Source of above mentioned test programm

          Show
          Oleksandr Alesinskyy added a comment - Source of above mentioned test programm
          Oleksandr Alesinskyy made changes -
          Field Original Value New Value
          Attachment TestGeneratedKeys.java [ 12379736 ]
          Hide
          Rick Hillegas added a comment -

          The correct behavior (if DatabaseMetaData.supportsGetGeneratedKeys() returns true) is defined in the JDBC3 spec, section 13.6 (Retrieving Auto Generated Keys).

          Show
          Rick Hillegas added a comment - The correct behavior (if DatabaseMetaData.supportsGetGeneratedKeys() returns true) is defined in the JDBC3 spec, section 13.6 (Retrieving Auto Generated Keys).
          Hide
          Oleksandr Alesinskyy added a comment -

          Exactly,. and its requires support for multirow inserts, namely
          "In some cases, such as in an insert select statement, more than one key may be
          returned. The ResultSet object returned by getGeneratedKeys will contain a
          row for each key that a statement generated. If no keys are generated, an empty
          result set will be returned."

          Show
          Oleksandr Alesinskyy added a comment - Exactly,. and its requires support for multirow inserts, namely "In some cases, such as in an insert select statement, more than one key may be returned. The ResultSet object returned by getGeneratedKeys will contain a row for each key that a statement generated. If no keys are generated, an empty result set will be returned."
          Kathey Marsden made changes -
          Derby Categories [High Value Fix]
          Hide
          shlomi v. added a comment -

          Hey,

          is there any progress on this issue?

          is there any way around this? like perhaps get the next n identity numbers for the next n inserts?

          Show
          shlomi v. added a comment - Hey, is there any progress on this issue? is there any way around this? like perhaps get the next n identity numbers for the next n inserts?
          Hide
          Kathey Marsden added a comment -

          >is there any progress on this issue?
          No, I'm sorry to say and I don't know of a work around.

          Show
          Kathey Marsden added a comment - >is there any progress on this issue? No, I'm sorry to say and I don't know of a work around.
          Dag H. Wanvik made changes -
          Issue & fix info [High Value Fix]
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.
          Knut Anders Hatlen made changes -
          Bug behavior facts [Deviation from standard]
          Urgency Normal
          Issue & fix info [High Value Fix] [High Value Fix, Repro attached]
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Kathey Marsden made changes -
          Labels derby_triage10_5_2 derby_triage10_11
          Gavin made changes -
          Workflow jira [ 12428541 ] Default workflow, editable Closed status [ 12802582 ]
          Hide
          Knut Anders Hatlen added a comment -

          It turns out Derby does actually collect all generated keys during inserts, also for multi-row inserts. It just doesn't expose theses values and returns the result of calling VALUES IDENTITY_VAL_LOCAL().

          The attached patch (generated-keys.diff) shows how EmbedStatement.getGeneratedKeys() can be made to return the collected keys. In my small experiments this appears to make getGeneratedKeys() work for multi-row inserts.

          Show
          Knut Anders Hatlen added a comment - It turns out Derby does actually collect all generated keys during inserts, also for multi-row inserts. It just doesn't expose theses values and returns the result of calling VALUES IDENTITY_VAL_LOCAL(). The attached patch (generated-keys.diff) shows how EmbedStatement.getGeneratedKeys() can be made to return the collected keys. In my small experiments this appears to make getGeneratedKeys() work for multi-row inserts.
          Knut Anders Hatlen made changes -
          Attachment generated-keys.diff [ 12611038 ]
          Kim Haase made changes -
          Link This issue is related to DERBY-6389 [ DERBY-6389 ]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-5823 [ DERBY-5823 ]
          Hide
          Knut Anders Hatlen added a comment -

          When this is fixed, we should also update the "Autogenerated keys" topic in the reference manual. DERBY-6389 changed that topic so it says that getGeneratedKeys() doesn't return anything meaningful for multi-row inserts.

          Added "Documentation" component to this bug so that we don't forget to update the docs.

          Show
          Knut Anders Hatlen added a comment - When this is fixed, we should also update the "Autogenerated keys" topic in the reference manual. DERBY-6389 changed that topic so it says that getGeneratedKeys() doesn't return anything meaningful for multi-row inserts. Added "Documentation" component to this bug so that we don't forget to update the docs.
          Knut Anders Hatlen made changes -
          Component/s Documentation [ 11406 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Oleksandr Alesinskyy
            • Votes:
              5 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:

                Development