[TRAFODION-1114] LP Bug: 1438925 - Duplicate rows removed, but alter table add constraint still returns error

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 1.1 (pre-incubation)
• Component/s: sql-cmu
• External issue URL: https://bugs.launchpad.net/bugs/1438925
• Assignee: Roberta Marton
• Reporter: Paul Low
• Created: 22/Jul/15 18:19
• Updated: 21/Aug/15 23:35
• Resolved: 21/Aug/15 23:35

Description

Error returned seems to indicate that the previous failed command left behind something.
It says that the constraint already exist.

SQL>alter table tab2 add constraint con1 primary key(a);

• • • ERROR[1390] Object TRAFODION.SCHEMA1.CON1 already exists in Trafodion.
    • ERROR[20123] A user-defined transaction has been started. This DDL operation cannot be performed.
    • ERROR[1029] Object TRAFODION.SCHEMA1.CON1 could not be created.

User creates schema and table.
USer inserts rows into table.
User tries to create constraint adding primary key, but was unsucessful as expected.
User removed offending rows and tries to create constraint again.
Unexpected error returns.

See log below:

SQL>set schema schema1;

— SQL operation complete.

SQL>create table tab2(a int, b int) no partition;

— SQL operation complete.

SQL>insert into tab2 values(9,1);

— 1 row(s) inserted.

SQL>insert into tab2 values(9,1),(8,2);

— 2 row(s) inserted.

SQL>alter table tab2 add constraint con1 primary key(a);

• • • ERROR[8110] Duplicate rows detected.
    • ERROR[20123] A user-defined transaction has been started. This DDL operation cannot be performed.
    • ERROR[1029] Object TRAFODION.SCHEMA1.CON1 could not be created.

SQL>delete from tab2 where a=9;

— 2 row(s) deleted.

SQL>alter table tab2 add constraint con1 primary key(a);

• • • ERROR[1390] Object TRAFODION.SCHEMA1.CON1 already exists in Trafodion.
    • ERROR[20123] A user-defined transaction has been started. This DDL operation cannot be performed.
    • ERROR[1029] Object TRAFODION.SCHEMA1.CON1 could not be created.

SQL>drop schema schema1 cascade;

— SQL operation complete.