[
https://issues.apache.org/jira/browse/DERBY-6550?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13978856#comment-13978856
]
Rick Hillegas commented on DERBY-6550:
--------------------------------------
Linking to DERBY-6542 because this bug was discovered while investigating that
issue.
> 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.0.0
> Reporter: Rick Hillegas
>
> 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.2#6252)
