[
https://issues.apache.org/jira/browse/TRAFODION-2371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15702789#comment-15702789
]
David Wayne Birdsall commented on TRAFODION-2371:
-------------------------------------------------
When renaming a table that has an identity column, the sequence object
associated with the identity column is not itself properly updated, so
subsequent DML operations may fail.
The following script illustrates the problem:
CREATE TABLE Tbadseq
(
a LARGEINT GENERATED BY DEFAULT AS IDENTITY
( START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1
CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE NOT SERIALIZED
, b LARGEINT DEFAULT NULL NOT SERIALIZED
, PRIMARY KEY (a ASC)
)
SALT USING 4 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
;
alter table Tbadseq rename to Tbadseqx;
insert into Tbadseqx(b) values (1);
alter table Tbadseqx rename to Tbadseq;
insert into Tbadseq(b) values (2);
drop table Tbadseq;
When run, it produces the following:
>>CREATE TABLE Tbadseq
+> (
+> a LARGEINT GENERATED BY DEFAULT AS IDENTITY
+> ( START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1
+> CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE NOT SERIALIZED
+> , b LARGEINT DEFAULT NULL NOT SERIALIZED
+> , PRIMARY KEY (a ASC)
+> )
+> SALT USING 4 PARTITIONS
+> ATTRIBUTES ALIGNED FORMAT
+>;
--- SQL operation complete.
>>
>>alter table Tbadseq rename to Tbadseqx;
--- SQL operation complete.
>>
>>insert into Tbadseqx(b) values (1);
*** ERROR[1389] Object _TRAFODION_SCH_TBADSEQX_A_ does not exist in Trafodion.
*** ERROR[1389] Object TRAFODION.SCH."_TRAFODION_SCH_TBADSEQX_A_" does not
exist in Trafodion.
*** ERROR[7001] Default value
SEQNUM(TRAFODION.SCH."_TRAFODION_SCH_TBADSEQX_A_") is not valid for column
TRAFODION.SCH.TBADSEQX.A.
*** ERROR[8822] The statement was not prepared.
>>
>>alter table Tbadseqx rename to Tbadseq;
--- SQL operation complete.
>>
>>insert into Tbadseq(b) values (2);
--- 1 row(s) inserted.
>>
>>drop table Tbadseq;
--- SQL operation complete.
>>
> Renaming a table having an identity column doesn't quite work
> -------------------------------------------------------------
>
> Key: TRAFODION-2371
> URL: https://issues.apache.org/jira/browse/TRAFODION-2371
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.1-incubating
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
