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

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-3314?focusedWorklogId=271194&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-271194 ]

ASF GitHub Bot logged work on TRAFODION-3314:
---------------------------------------------

                Author: ASF GitHub Bot
            Created on: 02/Jul/19 20:45
            Start Date: 02/Jul/19 20:45
    Worklog Time Spent: 10m 
      Work Description: DaveBirdsall commented on pull request #1846: [TRAFODION-3314] Avoid generating redundant DDL in OSIM for unique constraints
URL: https://github.com/apache/trafodion/pull/1846
 
 
   The problem: When a table has a unique constraint, OSIM generates both the ALTER TABLE statement to create the constraint and the CREATE INDEX statement for the system-generated index into CREATE_TABLES_DDL.txt. When we then do OSIM LOAD, the CREATE INDEX is executed, then the ALTER TABLE. The ALTER TABLE fails because the index already exists.
   
   The fix: Don't write the CREATE INDEX to the CREATE_TABLES_DDL.txt file.
 
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Issue Time Tracking
-------------------

            Worklog Id:     (was: 271194)
            Time Spent: 10m
    Remaining Estimate: 0h

> 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: 10m
>  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)