[ https://issues.apache.org/jira/browse/TRAFODION-3314?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wayne Birdsall resolved TRAFODION-3314. --------------------------------------------- Resolution: Fixed Fix Version/s: 2.4 > 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 > Fix For: 2.4 > > Time Spent: 50m > 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)