[jira] [Created] (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)]

Rick Hillegas created DERBY-6557:
------------------------------------

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