db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6550) Bulk-insert causes identity columns to cycle when they shouldn't
Date Wed, 08 Jun 2016 01:32:21 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6550?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Bryan Pendleton updated DERBY-6550:
    Attachment: updatedTest.diff

I think that, in addition to creating the table, and the function,
we have to perform the two INSERT statements shown in the
reproduction script, and verify that they both fail with the expected
SQLState error.

We want to make sure that we test that both forms of the INSERT
statement fail in the same way, since the original bug was that
one of the INSERT statements unexpectedly succeeded.

I updated the test to include the INSERT statements, and
attached it as "updatedTest.diff".

Can you please have a look at the updated test and try it
on your system, and tell me if it seems to be behaving
properly for you?

> 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:
>            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
> 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

View raw message