[jira] [Work started] (TRAFODION-3314) OSIM generates redundant DDL for unique constraints

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-3314?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Work on TRAFODION-3314 started by David Wayne Birdsall.
-------------------------------------------------------
> OSIM generates redundant DDL for unique constraints
> ---------------------------------------------------
>
>                 Key: TRAFODION-3314
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-3314
>             Project: Apache Trafodion
>          Issue Type: Improvement
>          Components: sql-cmp
>    Affects Versions: 2.4
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>            Priority: Major
>          Time Spent: 0.5h
>  Remaining Estimate: 0h
>
> Unique constraints are usually implemented by creation of a unique index under the covers. When doing an OSIM CAPTURE of a query on a table that has a unique constraint, OSIM generates both an ALTER TABLE ADD CONSTRAINT statement and a CREATE UNIQUE INDEX statement in the CREATE_TABLE_DDLS.txt file. When this is replayed in OSIM LOAD, we get an error when creating the unique index. 
> To fix this, perhaps OSIM should generate the CREATE UNIQUE INDEX statement only in commented-out form.
> {{To reproduce, run the following script in sqlci:}}
> {{?section setup}}
> {{drop table if exists t1 cascade;}}
> {{create table t1 (a int not null, b int not null, c int not null, primary key (a));}}
> {{alter table t1 add constraint uniqueb unique(b);}}
> {{?section osimgen}}
> {{control osim capture location 'osimgen';}}
> {{prepare xx from select b,count(*) from t1 group by b;}}
> {{control osim capture stop;}}
> {{Then in a separate session, do:}}
> {{control osim load from 'osimgen';}}
> {{This will fail with the following output:}}
> {{>>control osim load from '.';}}
> {{[OSIM]loading tables and views ...}}
> {{[OSIM]DROP TABLE IF EXISTS TRAFODION.SCH.T1 CASCADE;}}
> {{[OSIM]Step 1 Create Schemas:}}
> {{[OSIM]CREATE SCHEMA IF NOT EXISTS TRAFODION.SCH}}
> {{[OSIM]Step 2 Create Tables:}}
> {{[OSIM]CREATE TABLE TRAFODION.SCH.T1}}
> {{[OSIM] The following index is a system created index --CREATE UNIQUE INDEX UNIQUEB ON TRAFODION.SCH.T1}}
> {{[OSIM]ALTER TABLE TRAFODION.SCH.T1 ADD CONSTRAINT TRAFODION.SCH.UNIQUEB UNIQUE}}
> {{*** ERROR[1043] Constraint TRAFODION.SCH.UNIQUEB already exists.}}
> {{*** ERROR[6009] The Optimizer Simulator (OSIM): Create Table Error: -1043}}
> {{*** ERROR[8822] The statement was not prepared.}}
> {{>>exit;}}
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)