>>
>>cqd TRAF_MULTI_COL_FAM 'ON';

--- SQL operation complete.
>>cqd traf_aligned_row_format 'OFF';

--- SQL operation complete.
>>
>>drop table if exists t027t01;

--- SQL operation complete.
>>create table t027t01 (a int not null primary key);

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:09:22 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.
>>
>>drop table if exists t027t01;

--- SQL operation complete.
>>create table t027t01 (a int not null primary key) attribute default column family 'cf';

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:09:35 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

--- SQL operation complete.
>>
>>drop table if exists t027t01;

--- SQL operation complete.
>>create table t027t01 (a int not null primary key, "cf2".b int) attribute default column family 'cf';

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:09:45 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

--- SQL operation complete.
>>
>>drop table if exists t027t01;

--- SQL operation complete.
>>create table t027t01 (a int not null primary key, "cf2".b int, cf3.c int) 
+>       attribute default column family 'cf';

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:09:55 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

--- SQL operation complete.
>>
>>create index t027t01i1 on t027t01(b);

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:10:02 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01
  (
    B ASC
  )
;

--- SQL operation complete.
>>
>>insert into t027t01 values (1,2,3);

--- 1 row(s) inserted.
>>select * from t027t01;

A            B            C          
-----------  -----------  -----------

          1            2            3

--- 1 row(s) selected.
>>update t027t01 set c = 33 where a = 1;

--- 1 row(s) updated.
>>select * from t027t01;

A            B            C          
-----------  -----------  -----------

          1            2           33

--- 1 row(s) selected.
>>delete from t027t01;

--- 1 row(s) deleted.
>>select * from t027t01;

--- 0 row(s) selected.
>>
>>alter table t027t01 add column "cf2".d int;

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:10:13 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL /*added_col*/
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL /*added_col*/
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01
  (
    B ASC
  )
;

--- SQL operation complete.
>>
>>alter table t027t01 add column "cf4".e int;

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:10:21 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL /*added_col*/
  , "cf4".E                          INT DEFAULT NULL /*added_col*/
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL /*added_col*/
  , "cf4".E                          INT DEFAULT NULL /*added_col*/
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01
  (
    B ASC
  )
;

--- SQL operation complete.
>>
>>alter table t027t01 drop column d;

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:10:25 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL /*added_col*/
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL /*added_col*/
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01
  (
    B ASC
  )
;

--- SQL operation complete.
>>
>>alter table t027t01 add column "cf2".d int;

--- SQL operation complete.
>>invoke t027t01;

-- Definition of Trafodion table TRAFODION.SCH027.T027T01
-- Definition current  Fri Mar 17 06:10:28 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL /*added_col*/
  , "cf2".D                          INT DEFAULT NULL /*added_col*/
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t01;

CREATE TABLE TRAFODION.SCH027.T027T01
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL /*added_col*/
  , "cf2".D                          INT DEFAULT NULL /*added_col*/
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01
  (
    B ASC
  )
;

--- SQL operation complete.
>>
>>create table t027t011 like t027t01;

--- SQL operation complete.
>>invoke t027t011;

-- Definition of Trafodion table TRAFODION.SCH027.T027T011
-- Definition current  Fri Mar 17 06:10:34 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t011;

CREATE TABLE TRAFODION.SCH027.T027T011
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

--- SQL operation complete.
>>
>>insert into t027t011 values (1, 2, 3, 4, 5);

--- 1 row(s) inserted.
>>select * from t027t011;

A            B            C            E            D          
-----------  -----------  -----------  -----------  -----------

          1            2            3            4            5

--- 1 row(s) selected.
>>purgedata t027t011;

--- SQL operation complete.
>>invoke t027t011;

-- Definition of Trafodion table TRAFODION.SCH027.T027T011
-- Definition current  Fri Mar 17 06:10:37 2017

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t011;

CREATE TABLE TRAFODION.SCH027.T027T011
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

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

--- 0 row(s) selected.
>>
>>drop table t027t011;

--- SQL operation complete.
>>create table t027t011 as select * from t027t01;

--- 0 row(s) inserted.
>>invoke t027t011;

-- Definition of Trafodion table TRAFODION.SCH027.T027T011
-- Definition current  Fri Mar 17 06:10:45 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT DEFAULT NULL
  , C                                INT DEFAULT NULL
  , E                                INT DEFAULT NULL
  , D                                INT DEFAULT NULL
  )

--- SQL operation complete.
>>showddl t027t011;

CREATE TABLE TRAFODION.SCH027.T027T011
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT DEFAULT NULL
  , C                                INT DEFAULT NULL
  , E                                INT DEFAULT NULL
  , D                                INT DEFAULT NULL
  )
;

--- SQL operation complete.
>>
>>drop table t027t011;

--- SQL operation complete.
>>create table t027t011("cf".a, "cf2".b, cf3.c, "cf4".e, "cf2".d) as select * from t027t01;

--- 0 row(s) inserted.
>>invoke t027t011;

-- Definition of Trafodion table TRAFODION.SCH027.T027T011
-- Definition current  Fri Mar 17 06:10:52 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL
  )

--- SQL operation complete.
>>showddl t027t011;

CREATE TABLE TRAFODION.SCH027.T027T011
  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , CF3.C                            INT DEFAULT NULL
  , "cf4".E                          INT DEFAULT NULL
  , "cf2".D                          INT DEFAULT NULL
  )
;

--- SQL operation complete.
>>
>>create volatile table t027t03 ("cf1".a int, "cf2".b int, c int);

--- SQL operation complete.
>>invoke t027t03;

-- Definition of Trafodion volatile table T027T03
-- Definition current  Fri Mar 17 06:11:08 2017

  (
    "cf1".A                          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , C                                INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t03;

CREATE VOLATILE TABLE T027T03
  (
    "cf1".A                          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.
>>
>>drop volatile table t027t03;

--- SQL operation complete.
>>create volatile table t027t03 ("cf1".a int, "cf2".b int, c int) attribute default column family 'cf';

--- SQL operation complete.
>>invoke t027t03;

-- Definition of Trafodion volatile table T027T03
-- Definition current  Fri Mar 17 06:11:42 2017

  (
    "cf1".A                          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , "cf".C                           INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>showddl t027t03;

CREATE VOLATILE TABLE T027T03
  (
    "cf1".A                          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT DEFAULT NULL
  , "cf".C                           INT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES DEFAULT COLUMN FAMILY 'cf'
;

--- SQL operation complete.
>>
>>drop table if exists t027t02;

--- SQL operation complete.
>>create table t027t02 (
+>     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
+>     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
+>     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
+>    a30.a30 int) attribute default column family 'cf';

--- SQL operation complete.
>>
>>drop table if exists t027t02;

--- SQL operation complete.
>>create table t027t02 (
+>     "cf".a0a0 int, "cf".a1a1 int, "cf".a2a2 int, "cf".a3a3 int, "cf".a4a4 int, "cf".a5a5 int, "cf".a6a6 int, "cf".a7a7 int, "cf".a8a8 int, "cf".a9a9 int,
+>     "cf".a10a10 int, "cf".a11a11 int, "cf".a12a12 int, "cf".a13a13 int, "cf".a14a14 int, "cf".a15a15 int, "cf".a16a16 int, "cf".a17a17 int, "cf".a18a18 int, "cf".a19a19 int,
+>     "cf".a20a20 int, "cf".a21a21 int, "cf".a22a22 int, "cf".a23a23 int, "cf".a24a24 int, "cf".a25a25 int, "cf".a26a26 int, "cf".a27a27 int, "cf".a28a28 int, "cf".a29a29 int,
+>    "cf".a30a30 int, "cf".a31a31 int, "cf".a32a32 int, "cf".a33a33 int) 
+>attribute default column family 'cf';

--- SQL operation complete.
>>invoke t027t02;

-- Definition of Trafodion table TRAFODION.SCH027.T027T02
-- Definition current  Fri Mar 17 06:12:21 2017

  (
    "cf".SYSKEY                      LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".A0A0                        INT DEFAULT NULL
  , "cf".A1A1                        INT DEFAULT NULL
  , "cf".A2A2                        INT DEFAULT NULL
  , "cf".A3A3                        INT DEFAULT NULL
  , "cf".A4A4                        INT DEFAULT NULL
  , "cf".A5A5                        INT DEFAULT NULL
  , "cf".A6A6                        INT DEFAULT NULL
  , "cf".A7A7                        INT DEFAULT NULL
  , "cf".A8A8                        INT DEFAULT NULL
  , "cf".A9A9                        INT DEFAULT NULL
  , "cf".A10A10                      INT DEFAULT NULL
  , "cf".A11A11                      INT DEFAULT NULL
  , "cf".A12A12                      INT DEFAULT NULL
  , "cf".A13A13                      INT DEFAULT NULL
  , "cf".A14A14                      INT DEFAULT NULL
  , "cf".A15A15                      INT DEFAULT NULL
  , "cf".A16A16                      INT DEFAULT NULL
  , "cf".A17A17                      INT DEFAULT NULL
  , "cf".A18A18                      INT DEFAULT NULL
  , "cf".A19A19                      INT DEFAULT NULL
  , "cf".A20A20                      INT DEFAULT NULL
  , "cf".A21A21                      INT DEFAULT NULL
  , "cf".A22A22                      INT DEFAULT NULL
  , "cf".A23A23                      INT DEFAULT NULL
  , "cf".A24A24                      INT DEFAULT NULL
  , "cf".A25A25                      INT DEFAULT NULL
  , "cf".A26A26                      INT DEFAULT NULL
  , "cf".A27A27                      INT DEFAULT NULL
  , "cf".A28A28                      INT DEFAULT NULL
  , "cf".A29A29                      INT DEFAULT NULL
  , "cf".A30A30                      INT DEFAULT NULL
  , "cf".A31A31                      INT DEFAULT NULL
  , "cf".A32A32                      INT DEFAULT NULL
  , "cf".A33A33                      INT DEFAULT NULL
  )

--- SQL operation complete.
>>
>>drop table if exists t027t03;

--- SQL operation complete.
>>create table t027t03("cf1".a int not null, "cf2".b int not null, c int not null, d int not null, 
+> primary key (a, b));

--- SQL operation complete.
>>invoke t027t03;

-- Definition of Trafodion table TRAFODION.SCH027.T027T03
-- Definition current  Fri Mar 17 06:12:54 2017

  (
    "cf1".A                          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf2".B                          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , D                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (A ASC, B ASC)

--- SQL operation complete.
>>insert into t027t03 values (1,1,1,1);

--- 1 row(s) inserted.
>>insert into t027t03 values (1,2,2,2);

--- 1 row(s) inserted.
>>select * from t027t03;

A            B            C            D          
-----------  -----------  -----------  -----------

          1            1            1            1
          1            2            2            2

--- 2 row(s) selected.
>>
>>-- create table like metadata table
>>drop table if exists t027t02;

--- SQL operation complete.
>>create table t027t02 like "_MD_".keys;

--- SQL operation complete.
>>invoke t027t02;

-- Definition of Trafodion table TRAFODION.SCH027.T027T02
-- Definition current  Fri Mar 17 06:13:14 2017

  (
    OBJECT_UID                       LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_NAME                      VARCHAR(256 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , KEYSEQ_NUMBER                    INT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_NUMBER                    INT NO DEFAULT NOT NULL NOT DROPPABLE
  , ORDERING                         INT NO DEFAULT NOT NULL NOT DROPPABLE
  , NONKEYCOL                        INT NO DEFAULT NOT NULL NOT DROPPABLE
  , FLAGS                            LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (OBJECT_UID ASC, KEYSEQ_NUMBER ASC)

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

--- 0 row(s) selected.
>>
>>-- negative tests
>>drop table if exists t027t02;

--- SQL operation complete.
>>
>>-- cannot have 3 part col name
>>create table t027t02 (a.a.a  int);

*** ERROR[15001] A syntax error occurred at or before: 
create table t027t02 (a.a.a  int);
                                ^ (33 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>-- cannot have col fam for aligned format tables
>>create table t027t02 ("cf".a int) attribute aligned format;

*** ERROR[4223] Column Family specification on columns of an aligned format table is not supported in this software version or edition.

--- SQL operation failed with errors.
>>
>>-- cannot specify col fam for dropped cols
>>alter table t027t01 drop column "cf2".d;

*** ERROR[15001] A syntax error occurred at or before: 
alter table t027t01 drop column "cf2".d;
                                     ^ (38 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>-- cannot create a different col fam for an index col
>>create index t027t01i2 on t027t01("cf2".b);

*** ERROR[15001] A syntax error occurred at or before: 
create index t027t01i2 on t027t01("cf2".b);
                                       ^ (40 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>-- cannot use col fam in dml stmts 
>>select * from t027t01 where "cf".a = 1;

*** ERROR[4002] Column "cf".A is not found.  Table "cf" not exposed.  Tables in scope: TRAFODION.SCH027.T027T01.  Default schema: TRAFODION.SCH027.

*** ERROR[8822] The statement was not prepared.

>>
>>-- cannot have > 32 col families
>>create table t027t02 (
+>     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
+>     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
+>     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
+>    a30.a30 int, a31.a31 int, a32.a32 int not null primary key);

*** ERROR[4225] Number of column families cannot exceed 32.

--- SQL operation failed with errors.
>>
>>create table t027t02 (
+>     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
+>     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
+>     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
+>    a30.a30 int, a31.a31 int);

*** ERROR[4225] Number of column families cannot exceed 32.

--- SQL operation failed with errors.
>>
>>create table t027t02 (
+>     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
+>     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
+>     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
+>    a30.a30 int, a31.a31 int) attribute default column family 'cf';

*** ERROR[4225] Number of column families cannot exceed 32.

--- SQL operation failed with errors.
>>
>>-- tests for alter varchar column 
>>drop table if exists t027t7;

--- SQL operation complete.
>>create table t027t7(a int not null, b varchar(2), 
+>   c varchar(4) character set utf8 not null, z int not null primary key)
+>salt using 2 partitions;

--- SQL operation complete.
>>
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:13:52 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>insert into t027t7 values (1, 'ab', 'cd', 10);

--- 1 row(s) inserted.
>>select * from t027t7;

A            B   C                 Z          
-----------  --  ----------------  -----------

          1  ab  cd                         10

--- 1 row(s) selected.
>>alter table t027t7 alter column b varchar(3);

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:14:06 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                VARCHAR(3) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>alter table t027t7 alter column c varchar(5) character set utf8;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:14:44 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                VARCHAR(3) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(5 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>alter table t027t7 alter column c varchar(4) character set utf8;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:15:18 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                VARCHAR(3) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>
>>alter table t027t7 alter column a largeint;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:15:52 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , B                                VARCHAR(3) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>alter table t027t7 alter column b varchar(4) character set utf8;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:16:26 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , B                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>alter table t027t7 alter column c varchar(6);

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:17:04 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , B                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(6) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>alter table t027t7 alter column b varchar(2);

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:17:38 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(6) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>alter table t027t7 alter column a int;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:18:11 2017

  (
    A                                INT DEFAULT NULL /*altered_col*/
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(6) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>alter table t027t7 alter column a smallint default 0 not null;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:18:44 2017

  (
    A                                SMALLINT DEFAULT 0 NOT NULL NOT DROPPABLE
      /*altered_col*/
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(6) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

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

A       B   C       Z          
------  --  ------  -----------

     1  ab  cd               10

--- 1 row(s) selected.
>>
>>-- aligned format
>>drop table if exists t027t7 cascade;

--- SQL operation complete.
>>create table t027t7(a int not null, b varchar(2), 
+>   c varchar(4) character set utf8 not null, z int not null primary key)
+>salt using 2 partitions attribute aligned format;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:19:18 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

--- SQL operation complete.
>>insert into t027t7 values (1, 'ab', 'cd', 10);

--- 1 row(s) inserted.
>>select * from t027t7;

A            B   C                 Z          
-----------  --  ----------------  -----------

          1  ab  cd                         10

--- 1 row(s) selected.
>>alter table t027t7 alter column a largeint;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:19:59 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

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

A                     B   C                 Z          
--------------------  --  ----------------  -----------

                   1  ab  cd                         10

--- 1 row(s) selected.
>>alter table t027t7 drop column b;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:20:41 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

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

A                     C                 Z          
--------------------  ----------------  -----------

                   1  cd                         10

--- 1 row(s) selected.
>>alter table t027t7 add column b char(10) default 'abc' not null;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:20:55 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  , B                                CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT _ISO88591'abc' NOT NULL NOT DROPPABLE /*added_col*/
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

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

A                     C                 Z            B         
--------------------  ----------------  -----------  ----------

                   1  cd                         10  abc       

--- 1 row(s) selected.
>>alter table t027t7 drop column b;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:21:36 2017

  (
    A                                LARGEINT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

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

A                     C                 Z          
--------------------  ----------------  -----------

                   1  cd                         10

--- 1 row(s) selected.
>>create view t027v1 as select * from t027t7;

--- SQL operation complete.
>>get all views on table t027t7;

Views on Table SCH027.T027T7
============================

TRAFODION.SCH027.T027V1

--- SQL operation complete.
>>invoke t027v1;

-- Definition of Trafodion view TRAFODION.SCH027.T027V1
-- Definition current  Fri Mar 17 06:21:54 2017

  (
    A                                LARGEINT DEFAULT NULL
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  )

--- SQL operation complete.
>>alter table t027t7 alter column a smallint;

--- SQL operation complete.
>>invoke t027v1;

-- Definition of Trafodion view TRAFODION.SCH027.T027V1
-- Definition current  Fri Mar 17 06:22:46 2017

  (
    A                                SMALLINT DEFAULT NULL
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  )

--- SQL operation complete.
>>
>>-- negative tests for alter/drop column
>>alter table t027t7 alter column b int;

*** ERROR[1009] Column B does not exist in the specified table.

--- SQL operation failed with errors.
>>alter table t027t7 alter column a char(10);

*** ERROR[1404] Column A cannot be altered. Reason: Old and New datatypes must be compatible.

--- SQL operation failed with errors.
>>alter table t027t7 alter column c char(1);

*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,2 BYTES,ISO88591) Source Value:cd to Target Type:CHAR(REC_BYTE_F_ASCII,1 BYTES,ISO88591).

*** ERROR[1404] Column C cannot be altered. Reason: Old data could not be updated using the altered column definition.

--- SQL operation failed with errors.
>>alter table t027t7 drop column e;

*** ERROR[1009] Column E does not exist in the specified table.

--- SQL operation failed with errors.
>>alter table t027t7 drop column a;

*** ERROR[4003] Column TRAFODION.SCH027.T027T7.A is not a column in table TRAFODION.SCH027.T027T7, or, after a NATURAL JOIN or JOIN USING, is no longer allowed to be specified with a table correlation name.

*** ERROR[1404] Column A cannot be altered. Reason: Error occurred while recreating views due to dependency on older column definition. Drop dependent views before doing the alter.

--- SQL operation failed with errors.
>>alter table t027v1 alter column a smallint;

*** ERROR[1127] The specified table TRAFODION.SCH027.T027V1 does not exist, is inaccessible or is not a base table. Please verify that the correct table was specified.

--- SQL operation failed with errors.
>>get all views on table t027t7;

Views on Table SCH027.T027T7
============================

TRAFODION.SCH027.T027V1

--- SQL operation complete.
>>
>>-- tests for alter column rename
>>drop table if exists t027t7 cascade;

--- SQL operation complete.
>>create table t027t7(a int not null, b varchar(2), 
+>   c varchar(4) character set utf8 not null, z int not null primary key)
+>attribute aligned format
+>salt using 2 partitions;

--- SQL operation complete.
>>create index t027t7i1 on t027t7(b);

--- SQL operation complete.
>>insert into t027t7 values (1, 'ab', 'cd', 10);

--- 1 row(s) inserted.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:25:45 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

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

A            B   C                 Z          
-----------  --  ----------------  -----------

          1  ab  cd                         10

--- 1 row(s) selected.
>>alter table t027t7 alter column b rename to bb;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:26:05 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , BB                               VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , Z                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, Z ASC)

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

A            BB  C                 Z          
-----------  --  ----------------  -----------

          1  ab  cd                         10

--- 1 row(s) selected.
>>alter table t027t7 alter column z rename to zz;

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:26:23 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , BB                               VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL /*altered_col*/
  , C                                VARCHAR(4 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , ZZ                               INT NO DEFAULT NOT NULL NOT DROPPABLE
      /*altered_col*/
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  )
  PRIMARY KEY ("_SALT_" ASC, ZZ ASC)

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

A            BB  C                 ZZ         
-----------  --  ----------------  -----------

          1  ab  cd                         10

--- 1 row(s) selected.
>>create view t027v1(zz) as select zz from t027t7;

--- SQL operation complete.
>>alter table t027t7 alter column c rename to cc;

--- SQL operation complete.
>>create view t027v2(zz) as select zz from t027t7 where cc = 'a';

--- SQL operation complete.
>>showddl t027v2;

CREATE VIEW TRAFODION.SCH027.T027V2 (ZZ) AS
  SELECT TRAFODION.SCH027.T027T7.ZZ FROM TRAFODION.SCH027.T027T7 WHERE
    TRAFODION.SCH027.T027T7.CC = 'a' ;

--- SQL operation complete.
>>
>>-- negative tests for alter column rename
>>alter table t027t7 alter column zz rename to a;

*** ERROR[1404] Column ZZ cannot be altered. Reason: Renamed column A already exist in the table.

--- SQL operation failed with errors.
>>alter table t027t7 alter column "_SALT_" rename to nosalt;

*** ERROR[1404] Column _SALT_ cannot be altered. Reason: Cannot rename system or computed column.

--- SQL operation failed with errors.
>>alter table t027t7 alter column cc rename to "_SALT_";

*** ERROR[1404] Column CC cannot be altered. Reason: Renamed column _SALT_ is reserved for internal system usage.

--- SQL operation failed with errors.
>>alter table t027t7 alter column zz rename to zzz;

*** ERROR[4003] Column TRAFODION.SCH027.T027T7.ZZ is not a column in table TRAFODION.SCH027.T027T7, or, after a NATURAL JOIN or JOIN USING, is no longer allowed to be specified with a table correlation name.

*** ERROR[1404] Column ZZ cannot be altered. Reason: Error occurred while recreating views due to dependency on older column definition. Drop dependent views before doing the alter.

--- SQL operation failed with errors.
>>alter table t027t7 alter column cc rename to ccc;

*** ERROR[4003] Column TRAFODION.SCH027.T027T7.CC is not a column in table TRAFODION.SCH027.T027T7, or, after a NATURAL JOIN or JOIN USING, is no longer allowed to be specified with a table correlation name.

*** ERROR[1404] Column CC cannot be altered. Reason: Error occurred while recreating views due to dependency on older column definition. Drop dependent views before doing the alter.

--- SQL operation failed with errors.
>>
>>-- alter col with views
>>drop table if exists t027t7 cascade;

--- SQL operation complete.
>>create table t027t7 (a varchar(10), b int)
+>attribute aligned format;

--- SQL operation complete.
>>create view t027v1 as select * from t027t7;

--- SQL operation complete.
>>create view t027v12 as select * from t027v1;

--- SQL operation complete.
>>create view t027v11 as select * from t027t7;

--- SQL operation complete.
>>create view t027v122 as select * from t027v12;

--- SQL operation complete.
>>create view t027v113 as select * from t027v11;

--- SQL operation complete.
>>get all views on table t027t7;

Views on Table SCH027.T027T7
============================

TRAFODION.SCH027.T027V1
TRAFODION.SCH027.T027V11
TRAFODION.SCH027.T027V113
TRAFODION.SCH027.T027V12
TRAFODION.SCH027.T027V122

--- SQL operation complete.
>>alter table t027t7 alter column a varchar(20);

--- SQL operation complete.
>>get all views on table t027t7;

Views on Table SCH027.T027T7
============================

TRAFODION.SCH027.T027V1
TRAFODION.SCH027.T027V11
TRAFODION.SCH027.T027V113
TRAFODION.SCH027.T027V12
TRAFODION.SCH027.T027V122

--- SQL operation complete.
>>
>>-- some alter operations cannot be performed within a user xn
>>cqd ddl_transactions 'ON';

--- SQL operation complete.
>>begin work;

--- SQL operation complete.
>>alter table t027t7 drop column b;

*** ERROR[20125] This ALTER operation cannot be performed if a user-defined transaction has been started or AUTOCOMMIT is OFF.

--- SQL operation failed with errors.
>>rollback work;

--- SQL operation complete.
>>begin work;

--- SQL operation complete.
>>alter table t027t7 alter column b largeint;

*** ERROR[20125] This ALTER operation cannot be performed if a user-defined transaction has been started or AUTOCOMMIT is OFF.

--- SQL operation failed with errors.
>>rollback work;

--- SQL operation complete.
>>
>>-- reserved words cannot be used as colnames in create/add/alter stmts
>>drop table if exists t027t7 cascade;

--- SQL operation complete.
>>create table t027t7 (syskey int);

*** ERROR[1269] Column name SYSKEY is reserved for internal system usage. It cannot be specified as a user column.

--- SQL operation failed with errors.
>>create table t027t7 ("_SALT_" int);

*** ERROR[1269] Column name _SALT_ is reserved for internal system usage. It cannot be specified as a user column.

--- SQL operation failed with errors.
>>create table t027t7 ("_DIVISION_2_" int);

*** ERROR[1269] Column name _DIVISION_2_ is reserved for internal system usage. It cannot be specified as a user column.

--- SQL operation failed with errors.
>>create table t027t7 (a int not null primary key);

--- SQL operation complete.
>>alter table t027t7 add column "_SALT_" int;

*** ERROR[1269] Column name _SALT_ is reserved for internal system usage. It cannot be specified as a user column.

--- SQL operation failed with errors.
>>alter table t027t7 alter column a rename to SYSKEY;

*** ERROR[1404] Column A cannot be altered. Reason: Renamed column SYSKEY is reserved for internal system usage.

--- SQL operation failed with errors.
>>
>>-- if cqd is specified, then reserved cols can be used. 
>>-- Use this cqd carefully.
>>cqd traf_allow_reserved_colnames 'ON';

--- SQL operation complete.
>>drop table if exists t027t7 cascade;

--- SQL operation complete.
>>create table t027t7 (syskey int not null primary key, b int);

--- SQL operation complete.
>>alter table t027t7 add column "_DIVISION_1" int;

--- SQL operation complete.
>>alter table t027t7 alter column b rename to "_SALT_";

--- SQL operation complete.
>>invoke t027t7;

-- Definition of Trafodion table TRAFODION.SCH027.T027T7
-- Definition current  Fri Mar 17 06:33:35 2017

  (
    SYSKEY                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "_SALT_"                         INT DEFAULT NULL /*altered_col*/
  , "_DIVISION_1"                    INT DEFAULT NULL /*added_col*/
  )
  PRIMARY KEY (SYSKEY ASC)

--- SQL operation complete.
>>
>>-- not null and default clause can appear in any order
>>drop table if exists t027t1 cascade;

--- SQL operation complete.
>>create table t027t1 (a int, b int not null, c int default 10,
+>   d int default 10 not null, e int not null default 10, 
+>   f int not null default 10 check (f > 0),
+>   g int not null not droppable default 10);

--- SQL operation complete.
>>invoke t027t1;

-- Definition of Trafodion table TRAFODION.SCH027.T027T1
-- Definition current  Fri Mar 17 06:33:56 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                INT DEFAULT NULL
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT 10
  , D                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  , E                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  , F                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  , G                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  )

--- SQL operation complete.
>>insert into t027t1 (b) values (10);

--- 1 row(s) inserted.
>>select * from t027t1;

A            B            C            D            E            F            G
-----------  -----------  -----------  -----------  -----------  -----------  -----------

          ?           10           10           10           10           10           10

--- 1 row(s) selected.
>>alter table t027t1 add column h int not null default 10;

--- SQL operation complete.
>>invoke t027t1;

-- Definition of Trafodion table TRAFODION.SCH027.T027T1
-- Definition current  Fri Mar 17 06:34:13 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                INT DEFAULT NULL
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT 10
  , D                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  , E                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  , F                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  , G                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
  , H                                INT DEFAULT 10 NOT NULL NOT DROPPABLE
      /*added_col*/
  )

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

A            B            C            D            E            F            G            H
-----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------

          ?           10           10           10           10           10           10           10

--- 1 row(s) selected.
>>
>>--should give error
>>create table t027t2 (a int default 10 not null default 20);

*** ERROR[3052] Duplicate DEFAULT clauses were specified in column definition A.

*** ERROR[8822] The statement was not prepared.

>>
>>-- cleanup
>>?section clean_up
>>drop table if exists t027t7 cascade;

--- SQL operation complete.
>>drop table if exists t027t01;

--- SQL operation complete.
>>drop table if exists t027t02;

--- SQL operation complete.
>>drop table if exists t027t011;

--- SQL operation complete.
>>drop table if exists t027t03;

--- SQL operation complete.
>>
>>log;