Create table like fails to set proper NEXT AVAILABLE VALUE for an identity column

[Weishiun Tsai]

As show here, table t2 was created like t1. Column ‘a’ is a primary column with default sequence numbers generated as a primary key. Data from t1 was populated into t2 when t2 was created. But the sequence ‘NEXT AVAILABLE VALUE’ for column ‘a’ was set to 1, instead of being recalculated for t2. It then causes any attempt to insert a row into t2 without specifying column ’a’ to return error 8102, since the sequence number 1 has already been used by a row inherited from t1.

This is seen on the v1.0.0 rc3 build.

------------------------------------------------------------------------

Here is the entire script to reproduce this problem:

create schema mytest;
set schema mytest;

create table t1
(a largeint generated by default as IDENTITY not null not droppable primary key, b char);
insert into t1 (b) values ('a'),('b');

create table t2 like t1 as select * from t1;

showddl t1;
select * from t1;

showddl t2;
insert into t2 values (3, 'd');
select * from t2;
showddl t2;
insert into t2 (b) values ('c');

drop table t1 cascade;
drop table t2 cascade;
drop schema mytest cascade;

------------------------------------------------------------------------

Here is the execution output:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create table t1
+>(a largeint generated by default as IDENTITY not null not droppable primary key, b char);

--- SQL operation complete.
>>insert into t1 (b) values ('a'),('b');

--- 2 row(s) inserted.
>>
>>create table t2 like t1 as select * from t1;

--- 2 row(s) inserted.
>>
>>showddl t1;

CREATE TABLE TRAFODION.MYTEST.T1
  (
    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
  , B CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
;

-- The following sequence is a system created sequence --

CREATE SEQUENCE TRAFODION.MYTEST."_TRAFODION_MYTEST_T1_A_" /* INTERNAL */
  START WITH 1 /* NEXT AVAILABLE VALUE 26 */
  INCREMENT BY 1
  MAXVALUE 9223372036854775806
  MINVALUE 1
  CACHE 25
  NO CYCLE
  LARGEINT
;

--- SQL operation complete.
>>select * from t1;

A B
-------------------- -

                   1 a
                   2 b

--- 2 row(s) selected.
>>
>>showddl t2;

CREATE TABLE TRAFODION.MYTEST.T2
  (
    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
  , B CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
;

-- The following sequence is a system created sequence --

CREATE SEQUENCE TRAFODION.MYTEST."_TRAFODION_MYTEST_T2_A_" /* INTERNAL */
  START WITH 1 /* NEXT AVAILABLE VALUE 1 */
  INCREMENT BY 1
  MAXVALUE 9223372036854775806
  MINVALUE 1
  CACHE 25
  NO CYCLE
  LARGEINT
;

--- SQL operation complete.
>>insert into t2 values (3, 'd');

--- 1 row(s) inserted.
>>select * from t2;

A B
-------------------- -

                   1 a
                   2 b
                   3 d

--- 3 row(s) selected.
>>showddl t2;

CREATE TABLE TRAFODION.MYTEST.T2
  (
    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
  , B CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
;

-- The following sequence is a system created sequence --

CREATE SEQUENCE TRAFODION.MYTEST."_TRAFODION_MYTEST_T2_A_" /* INTERNAL */
  START WITH 1 /* NEXT AVAILABLE VALUE 1 */
  INCREMENT BY 1
  MAXVALUE 9223372036854775806
  MINVALUE 1
  CACHE 25
  NO CYCLE
  LARGEINT
;

--- SQL operation complete.
>>insert into t2 (b) values ('c');

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>
>>drop table t1 cascade;

--- SQL operation complete.
>>drop table t2 cascade;

--- SQL operation complete.
>>drop schema mytest cascade;

--- SQL operation complete.

[Anoop Sharma (anoop-sharma)]

This is correct expected behavior.

If a column is created as 'generated by default as identity', it allows users to specify values to be inserted
as well as insertion of generated sequence numbers.
If users are mixing these two, then they need to be careful and aware of duplicates that may be generated.

A simpler example is:
  >>create table t1 (a largeint generated by default as identity not null primary key);

  --- SQL operation complete.
  >>insert into t1 values (1);

  --- 1 row(s) inserted.
  >>insert into t1 default values;

  *** ERROR[8102] The operation is prevented by a unique constraint.

  --- 0 row(s) inserted.
  >>insert into t1 default values;

  --- 1 row(s) inserted.
  >>

The first value inserted was a user specified value '1'. The next value inserted was
the default identity value and since that started at '1', it correctly returned a dup error.
The second insert succeeded as it generated next value '2' which is not a duplicate.

There is no concept of recalibration of generated identity values after user specified inserts
as users can insert any kind of values into the table, and those values may clash with generated
values.

One should not mix inserting user specified and generated values. The example in this bug
case did that by inserting all values from source table, and then inserting newly generated
values.

Users can specify 'generated always' which will prevent them from inserting explicitly specified
values in the table.

I am closing this case.