| >> |
| >>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; |
