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

It is desirable to have IDENTITY_VAL_LOCAL() function return last recent user specified value or system generated value for BY DEFAULT identity columns.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0
    • Fix Version/s: 10.1.3.2, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Generic

      Description

      Derby was recently enhanced to support BY DEFAULT identity column. While the behavior of this feature is not documented yet, I think, it is desirable for IDENTITY_VAL_LOCAL() function, that is used to retrieve last single statement insert value for identity column, to return user specified value for the default column.

      For GENERATED ALWAYS identity columns, this issue doesn't apply, since users can't provide a value. But for GENERATED BY DEFAULT identity columns, users can optionally specify a value. IDENTITY_VAL_LOCAL() function should return this value. Derby currently doesn't support this behavior.

      ij> create table tauto ( i int generated by default as identity, j int, k int);
      0 rows inserted/updated/deleted
      ij> insert into tauto (j,k) values (1,1);
      1 row inserted/updated/deleted
      ij> values identity_val_local();
      1
      -------------------------------
      1

      1 row selected
      ij> insert into tauto (j,k) values (1,1);
      1 row inserted/updated/deleted
      ij> values identity_val_local();
      1
      -------------------------------
      2

      1 row selected
      ij> insert into tauto values (5,1,1);
      1 row inserted/updated/deleted
      ij> values identity_val_local();
      1
      -------------------------------
      2 <<<<<<<<<<<<<<============= This needs be '5'

      1 row selected
      ij> select * from tauto;
      I |J |K
      -----------------------------------
      1 |1 |1
      2 |1 |1
      5 |1 |1

      3 rows selected

        Attachments

        1. patch353.diff
          25 kB
          V.Narayanan

          Issue Links

            Activity

              People

              • Assignee:
                narayanan V.Narayanan
                Reporter:
                bandaram Satheesh E. Bandaram
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: