[jira] [Updated] (DERBY-6557) The identity counter is reset to its start point if you truncate a table and then perform an insert driven by a select from a table function

["Rick Hillegas (JIRA)" <ji...@apache.org>]

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

Rick Hillegas updated DERBY-6557:
---------------------------------

     Affects Version/s: 10.11.0.0
    Bug behavior facts: Deviation from standard

Marking this as a deviation from the Standard. According to the 2011 version of the Standard, part 2, section 14.10 (Truncate table statement), syntax rule 5, the default behavior of TRUNCATE is TRUNCATE CONTINUE IDENTITY.

> The identity counter is reset to its start point if you truncate a table and then perform an insert driven by a select from a table function
> --------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6557
>                 URL: https://issues.apache.org/jira/browse/DERBY-6557
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>
> The following script shows this problem:
> {noformat}
> connect 'jdbc:derby:memory:db;create=true';
> create table t
> (
>     a bigint generated always as identity,
>     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';
> insert into t( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );
> insert into t( b ) select b from table( integerList() ) il;
> select * from t;
> -- truncate followed by ordinary insert doesn't reset the identity counter
> truncate table t;
> insert into t( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );
> select * from t;
> -- but truncate followed by an insert driven by a table function does
> truncate table t;
> insert into t( b ) select b from table( integerList() ) il;
> select * from t;
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.2#6252)