[jira] Created: (DERBY-4599) alter table alter column restart with is also changing identity column start value

["Farid Zidan (JIRA)" <ji...@apache.org>]

alter table alter column restart with is also changing identity column start value
----------------------------------------------------------------------------------

                 Key: DERBY-4599
                 URL: https://issues.apache.org/jira/browse/DERBY-4599
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.5.3.0
         Environment: All
            Reporter: Farid Zidan


When using alter table alter column restart with ddl to change the next value for an identity column generated by default, the identity column start value from the  table ddl is lost. Example,

create table test_identity (col1 integer  generated by default as identity (start with 100, increment by 10),
col2 varchar(30),
constraint pk_test_identity primary key (col1));

select 
'TEST_IDENTITY' "table",
'COL1' "column",
(select min(COL1) from TEST_IDENTITY) "col_min_val",
(select max(COL1) from TEST_IDENTITY) "col_max_val",
c.AUTOINCREMENTVALUE,
c.AUTOINCREMENTSTART,
c.AUTOINCREMENTINC
from
sys.systables t,
sys.sysschemas s,
sys.syscolumns c
where
t.tableid = c.REFERENCEID and
t.schemaid = s.schemaid and
s.SCHEMANAME = <your schema name> and
t.TABLENAME = 'TEST_IDENTITY' and
c.COLUMNNAME = 'COL1';

--I get correctly 100 for both next identity value AUTOINCREMENTVALUE and identity column start AUTOINCREMENTSTART

insert into test_identity (col1, col2) values(200, 'a');
insert into test_identity (col1, col2) values(210, 'b');

-- adjust identity column next value
alter table TEST_IDENTITY alter column COL1 restart with 220;

Both next value and start value for the column are now 220. I only wanted to adjust the identity column next value but in the process I lost the identity column ddl start value of 100 which now crept up to 220. Therefore seem to have no way of just adjusting the identity column next value but leaving the column identity start value AUTOINCREMENTSTART intact. This causes loss of information as you load data into the table as the identity column start value keeps creeping up and there is no way to recover the original AUTOINCREMENTSTART for the identity column


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.