[jira] [Resolved] (TRAFODION-1159) LP Bug: 1442949 - Alter table add column returns error but columns are still added

["Suresh Subbiah (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-1159?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Suresh Subbiah resolved TRAFODION-1159.
---------------------------------------
    Resolution: Fixed

> LP Bug: 1442949 - Alter table add column returns error but columns are still added
> ----------------------------------------------------------------------------------
>
>                 Key: TRAFODION-1159
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1159
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-exe
>            Reporter: Weishiun Tsai
>            Assignee: Anoop Sharma
>            Priority: Blocker
>             Fix For: 1.1 (pre-incubation)
>
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)