Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-1114

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

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.1 (pre-incubation)
    • Component/s: sql-cmu
    • Labels:
      None

      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.

        Attachments

          Activity

            People

            • Assignee:
              rmarton Roberta Marton
              Reporter:
              paul.low Paul Low
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: