[jira] [Created] (TRAFODION-1196) LP Bug: 1447323 - Multiple IDENTITY columns should get an error at the create table time

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-1196:
-------------------------------------

             Summary: LP Bug: 1447323 - Multiple IDENTITY columns should get an error at the create table time
                 Key: TRAFODION-1196
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1196
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical
             Fix For: 2.0-incubating


SQL does not support multiple IDENTITY columns in the same table.  However, create table does not catch this.  As shown here in the example, create table t with 2 IDENTITY columns c1 and c2 goes through fine.  Showddl shows that the table is created, with only the sequence generator for c2.  Insert into such a table returns perplexing errors complaining that the sequence generator for c1 does not exist.

This is seen on the v1.1.0rc1 (v0417 build).

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

Here is the entire script to reproduce it:

create schema mytest;
set schema mytest;

create table t (
c1 largeint GENERATED ALWAYS AS IDENTITY,
c2 largeint GENERATED ALWAYS AS IDENTITY,
c3 largeint);

showddl t;

insert into t(c3) values (1);

drop schema mytest cascade;

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

Here is the execution output:

>>create schema mytest;

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

--- SQL operation complete.
>>
>>create table t (
+>c1 largeint GENERATED ALWAYS AS IDENTITY,
+>c2 largeint GENERATED ALWAYS AS IDENTITY,
+>c3 largeint);

--- SQL operation complete.
>>
>>showddl t;

CREATE TABLE TRAFODION.MYTEST.T
  (
    C1                               LARGEINT GENERATED ALWAYS AS IDENTITY (
      START WITH 1  INCREMENT BY 1  MAXVALUE 9223372036854775806  MINVALUE 1
      CACHE 25  NO CYCLE  LARGEINT  ) NOT NULL NOT DROPPABLE
  , C2                               LARGEINT GENERATED ALWAYS AS IDENTITY (
      START WITH 1  INCREMENT BY 1  MAXVALUE 9223372036854775806  MINVALUE 1
      CACHE 25  NO CYCLE  LARGEINT  ) NOT NULL NOT DROPPABLE
  , C3                               LARGEINT DEFAULT NULL
  )
;

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

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

-- GRANT USAGE ON TRAFODION.MYTEST."_TRAFODION_MYTEST_T_C2_" TO DB__ROOT WITH GRANT OPTION;

-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION.MYTEST.T TO DB__ROOT WITH GRANT OPTION;

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

*** ERROR[1389] Object _TRAFODION_MYTEST_T_C1_ does not exist in Trafodion.

*** ERROR[1389] Object TRAFODION.MYTEST."_TRAFODION_MYTEST_T_C1_" does not exist in Trafodion.

*** ERROR[7001] Default value SEQNUM(TRAFODION.MYTEST."_TRAFODION_MYTEST_T_C1_") is not valid for column TRAFODION.MYTEST.T.C1.

*** ERROR[8822] The statement was not prepared.

>>
>>drop schema mytest cascade;

--- SQL operation complete.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)