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

LP Bug: 1442949 - Alter table add column returns error but columns are still added

    XMLWordPrintableJSON

    Details

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

      Description

      This is a regression introduced between the v0407 build and the r1.1.0rc0 (v0410) build. As shown in the following example, the alter table add column statements returned errors, but a showddl and a select afterwards showed that the columns were still added.

      This is reproducible on a workstation as well. This bug report is marked as Critical only because it is a regression introduced in the past 3 days and it breaks the QA regression test suite. Mark it as Critical makes it easier to find out which check-ins in the past 3 days had caused this and to address it right away, as opposed to marking it as High and trying to track it down months later.

      -------------------------------

      Here is the entire script to reproduce it:

      create schema mytest;
      set schema mytest;

      create table a12tab2 (int1 int not null, vch2 varchar(3)) store by ( int1 );

      insert into a12tab2 values (1, '111'), (2, '222');
      select * from a12tab2 order by int1;

      alter table a12tab2 add c3 int primary key asc;
      alter table a12tab2 add c4 char(4) primary key desc;
      alter table a12tab2 add c5 decimal(4,2) primary key ascending;
      alter table a12tab2 add column c6 smallint primary key droppable;
      showddl a12tab2;
      select * from a12tab2;

      drop schema mytest cascade;

      -------------------------------

      Here is the execution output:

      >>create schema mytest;

      — SQL operation complete.
      >>set schema mytest;

      — SQL operation complete.
      >>
      >>create table a12tab2 (int1 int not null, vch2 varchar(3)) store by ( int1 );

      — SQL operation complete.
      >>
      >>insert into a12tab2 values (1, '111'), (2, '222');

      — 2 row(s) inserted.
      >>select * from a12tab2 order by int1;

      INT1 VCH2
      ----------- ----

      1 111
      2 222

      — 2 row(s) selected.
      >>
      >>alter table a12tab2 add c3 int primary key asc;

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

      — SQL operation failed with errors.
      >>alter table a12tab2 add c4 char(4) primary key desc;

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

      — SQL operation failed with errors.
      >>alter table a12tab2 add c5 decimal(4,2) primary key ascending;

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

      — SQL operation failed with errors.
      >>alter table a12tab2 add column c6 smallint primary key droppable;

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

      — SQL operation failed with errors.
      >>showddl a12tab2;

      CREATE TABLE TRAFODION.MYTEST.A12TAB2
      (
      INT1 INT NO DEFAULT NOT NULL NOT DROPPABLE
      , VCH2 VARCHAR(3) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
      , C3 INT DEFAULT NULL /* added col */
      , C4 CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /* added col */
      , C5 DECIMAL(4, 2) DEFAULT NULL /* added col */
      , C6 SMALLINT DEFAULT NULL /* added col */
      )
      STORE BY (INT1 ASC)
      ;

      — SQL operation complete.
      >>select * from a12tab2;

      INT1 VCH2 C3 C4 C5 C6
      ----------- ---- ----------- ---- ------ ------

      1 111 ? ? ? ?
      2 222 ? ? ? ?

      — 2 row(s) selected.
      >>
      >>drop schema mytest cascade;

      — SQL operation complete.

        Attachments

          Activity

            People

            • Assignee:
              anoopsharma Anoop Sharma
              Reporter:
              WTsai Weishiun Tsai
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: