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

Bulk-insert causes identity columns to cycle when they shouldn't

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Cannot Reproduce
    • 10.11.1.1
    • None
    • SQL
    • None
    • Normal
    • Deviation from standard

    Description

      According to the SQL Standard, an identity column is conceptually backed by a sequence generator. If you don't specify a cycle option (and for Derby's identity column, you can't), then the identity column is supposed to NOT cycle. This is described by the following sections of the 2011 edition of the SQL Standard:

      o Section 11.4 (column definition), syntax rule 16

      o Section 9.26 (Creation of a sequence generator), syntax rule 13

      If you aren't doing a bulk-insert, then Derby honors this contract. However, due to an optimization in InsertResultSet, this contract is not honored for bigint identity columns. Bulk-insert causes Derby to cycle past the biggest positive long value and to begin generating negative longs.

      The following script shows this behavior:

      connect 'jdbc:derby:memory:db;create=true';
      
      create table t
      (
          a bigint generated always as identity ( start with 9223372036854775806 ),
          b int
      );
      
      create function integerList() returns table
      (
          a int,
          b int,
          c int,
          d int
      )
      language java parameter style derby_jdbc_result_set no sql
      external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.integerList_023';
      
      -- this fails because bulk-insert isn't used and we go past the end of the identity column's range
      insert into t( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );
      
      -- inserting into an empty table from a table function uses bulk-insert
      --
      -- this should fail just like the previous statement, but it succeeds
      insert into t( b ) select b from table( integerList() ) il;
      
      select * from t;
      

      Attachments

        1. screenshot-1.png
          723 kB
          Sandeep Samdaria
        2. test.diff
          1 kB
          Danoja Dias
        3. updatedTest.diff
          2 kB
          Bryan Pendleton

        Issue Links

          Activity

            People

              Dnj Danoja Dias
              rhillegas Richard N. Hillegas
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: