[ https://issues.apache.org/jira/browse/DERBY-6550?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15321824#comment-15321824 ]
ASF subversion and git services commented on DERBY-6550: -------------------------------------------------------- Commit 1747486 from [~bryanpendleton] in branch 'code/trunk' [ https://svn.apache.org/r1747486 ] DERBY-6550: Bulk-insert causes identity columns to cycle when they shouldn't This change was contributed by Danoja Dias (danojadias at gmail dot com) Adds a new test case to verify that a bigint identity column correctly refuses to cycle when the identity value reaches the maximum value. > Bulk-insert causes identity columns to cycle when they shouldn't > ---------------------------------------------------------------- > > Key: DERBY-6550 > URL: https://issues.apache.org/jira/browse/DERBY-6550 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.11.1.1 > Reporter: Rick Hillegas > Assignee: Danoja Dias > Attachments: screenshot-1.png, test.diff, updatedTest.diff > > > 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: > {noformat} > 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; > {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)