Alter table add column returns error but columns are still added

[Weishiun Tsai]

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.

[Trafodion-Gerrit (neo-devtools)]

Fix proposed to branch: master
Review: https://review.trafodion.org/1495

[Trafodion-Gerrit (neo-devtools)]

Reviewed: https://review.trafodion.org/1495
Committed: https://github.com/trafodion/core/commit/84b0f58810b7afb8c383a5fb3d82029ce0180cae
Submitter: Trafodion Jenkins
Branch: master

commit 84b0f58810b7afb8c383a5fb3d82029ce0180cae
Author: Anoop Sharma <email address hidden>
Date: Mon Apr 13 13:57:29 2015 -0700

    LP Bug 1442949

    -- alter add col was not running under an internal Xn.
      that caused rollback to not work.
    -- change to allow internal cleanup command to proceed if
      running under a transaction. This is needed for internal
      cleanups after error.
    -- better error msg if index population fails.

    Change-Id: I0fd85be97a2ab96544cd037f920df0a3f5868542

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0415 build installed on a workstation. This problem is now fixed:

>>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[1053] Unique index TRAFODION.MYTEST.A12TAB2_865741655_1997 could not be created because the specified column(s) contain duplicate data.

*** ERROR[1029] Object TRAFODION.MYTEST.A12TAB2_865741655_1997 could not be created.

*** ERROR[8839] Transaction was aborted.

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

*** ERROR[8110] Duplicate rows detected.

*** ERROR[1053] Unique index TRAFODION.MYTEST.A12TAB2_214151655_1997 could not be created because the specified column(s) contain duplicate data.

*** ERROR[1029] Object TRAFODION.MYTEST.A12TAB2_214151655_1997 could not be created.

*** ERROR[8839] Transaction was aborted.

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

*** ERROR[8110] Duplicate rows detected.

*** ERROR[1053] Unique index TRAFODION.MYTEST.A12TAB2_887251655_1997 could not be created because the specified column(s) contain duplicate data.

*** ERROR[1029] Object TRAFODION.MYTEST.A12TAB2_887251655_1997 could not be created.

*** ERROR[8839] Transaction was aborted.

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

*** ERROR[8110] Duplicate rows detected.

*** ERROR[1053] Unique index TRAFODION.MYTEST.A12TAB2_993451655_1997 could not be created because the specified column(s) contain duplicate data.

*** ERROR[1029] Object TRAFODION.MYTEST.A12TAB2_993451655_1997 could not be created.

*** ERROR[8839] Transaction was aborted.

--- 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
  )
  STORE BY (INT1 ASC)
;

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

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

          1 111
          2 222

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

--- SQL operation complete.