http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/seabase/EXPECTED027 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED027 b/core/sql/regress/seabase/EXPECTED027 index 8c48546..2465664 100644 --- a/core/sql/regress/seabase/EXPECTED027 +++ b/core/sql/regress/seabase/EXPECTED027 @@ -12,11 +12,12 @@ >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:16 2016 +-- Definition current Sat Mar 12 02:58:49 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t01; @@ -39,11 +40,12 @@ CREATE TABLE TRAFODION.SCH027.T027T01 >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:25 2016 +-- Definition current Sat Mar 12 02:58:59 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t01; @@ -67,12 +69,13 @@ CREATE TABLE TRAFODION.SCH027.T027T01 >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:30 2016 +-- Definition current Sat Mar 12 02:59:05 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE , "cf2".B INT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t01; @@ -98,13 +101,14 @@ CREATE TABLE TRAFODION.SCH027.T027T01 >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:39 2016 +-- Definition current Sat Mar 12 02:59:14 2016 ( "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; @@ -127,13 +131,14 @@ CREATE TABLE TRAFODION.SCH027.T027T01 >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:40 2016 +-- Definition current Sat Mar 12 02:59:16 2016 ( "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; @@ -191,7 +196,7 @@ A B C >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:46 2016 +-- Definition current Sat Mar 12 02:59:22 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -199,6 +204,7 @@ A B C , CF3.C INT DEFAULT NULL , "cf2".D INT DEFAULT NULL /*added_col*/ ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t01; @@ -228,7 +234,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:49 2016 +-- Definition current Sat Mar 12 02:59:26 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -237,6 +243,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 , "cf2".D INT DEFAULT NULL /*added_col*/ , "cf4".E INT DEFAULT NULL /*added_col*/ ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t01; @@ -267,7 +274,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:54 2016 +-- Definition current Sat Mar 12 02:59:31 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -275,6 +282,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 , CF3.C INT DEFAULT NULL , "cf4".E INT DEFAULT NULL /*added_col*/ ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t01; @@ -304,7 +312,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 >>invoke t027t01; -- Definition of Trafodion table TRAFODION.SCH027.T027T01 --- Definition current Fri Feb 26 20:36:55 2016 +-- Definition current Sat Mar 12 02:59:32 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -313,6 +321,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 , "cf4".E INT DEFAULT NULL /*added_col*/ , "cf2".D INT DEFAULT NULL /*added_col*/ ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t01; @@ -343,7 +352,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 >>invoke t027t011; -- Definition of Trafodion table TRAFODION.SCH027.T027T011 --- Definition current Fri Feb 26 20:37:02 2016 +-- Definition current Sat Mar 12 02:59:40 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -352,6 +361,7 @@ CREATE INDEX T027T01I1 ON TRAFODION.SCH027.T027T01 , "cf4".E INT DEFAULT NULL , "cf2".D INT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t011; @@ -387,7 +397,7 @@ A B C E D >>invoke t027t011; -- Definition of Trafodion table TRAFODION.SCH027.T027T011 --- Definition current Fri Feb 26 20:37:06 2016 +-- Definition current Sat Mar 12 02:59:43 2016 ( "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -396,6 +406,7 @@ A B C E D , "cf4".E INT DEFAULT NULL , "cf2".D INT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>showddl t027t011; @@ -426,10 +437,11 @@ CREATE TABLE TRAFODION.SCH027.T027T011 >>invoke t027t011; -- Definition of Trafodion table TRAFODION.SCH027.T027T011 --- Definition current Fri Feb 26 20:37:15 2016 +-- Definition current Sat Mar 12 02:59:53 2016 ( - A INT NO DEFAULT NOT NULL NOT DROPPABLE + 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 @@ -460,10 +472,11 @@ CREATE TABLE TRAFODION.SCH027.T027T011 >>invoke t027t011; -- Definition of Trafodion table TRAFODION.SCH027.T027T011 --- Definition current Fri Feb 26 20:37:19 2016 +-- Definition current Sat Mar 12 02:59:57 2016 ( - "cf".A INT NO DEFAULT NOT NULL NOT DROPPABLE + 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 @@ -491,13 +504,14 @@ CREATE TABLE TRAFODION.SCH027.T027T011 >>invoke t027t03; -- Definition of Trafodion volatile table T027T03 --- Definition current Fri Feb 26 20:37:25 2016 +-- Definition current Sat Mar 12 03:00:03 2016 ( "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; @@ -522,13 +536,14 @@ CREATE VOLATILE TABLE T027T03 >>invoke t027t03; -- Definition of Trafodion volatile table T027T03 --- Definition current Fri Feb 26 20:37:45 2016 +-- Definition current Sat Mar 12 03:00:24 2016 ( "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; @@ -570,10 +585,11 @@ CREATE VOLATILE TABLE T027T03 >>invoke t027t02; -- Definition of Trafodion table TRAFODION.SCH027.T027T02 --- Definition current Fri Feb 26 20:38:04 2016 +-- Definition current Sat Mar 12 03:00:45 2016 ( - "cf".A0A0 INT DEFAULT NULL + "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 @@ -621,7 +637,7 @@ CREATE VOLATILE TABLE T027T03 >>invoke t027t03; -- Definition of Trafodion table TRAFODION.SCH027.T027T03 --- Definition current Fri Feb 26 20:38:26 2016 +-- Definition current Sat Mar 12 03:01:07 2016 ( "cf1".A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -629,6 +645,7 @@ CREATE VOLATILE TABLE T027T03 , 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); @@ -657,7 +674,7 @@ A B C D >>invoke t027t02; -- Definition of Trafodion table TRAFODION.SCH027.T027T02 --- Definition current Fri Feb 26 20:38:40 2016 +-- Definition current Sat Mar 12 03:01:20 2016 ( OBJECT_UID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -669,6 +686,7 @@ A B C D , 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; @@ -769,7 +787,7 @@ create index t027t01i2 on t027t01("cf2".b); >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:39:06 2016 +-- Definition current Sat Mar 12 03:01:46 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -778,7 +796,10 @@ create index t027t01i2 on t027t01("cf2".b); , 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); @@ -798,7 +819,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:39:13 2016 +-- Definition current Sat Mar 12 03:01:54 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -807,7 +828,10 @@ A B C Z , 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; @@ -816,7 +840,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:39:35 2016 +-- Definition current Sat Mar 12 03:02:19 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -825,7 +849,10 @@ A B C Z , 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; @@ -834,7 +861,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:39:50 2016 +-- Definition current Sat Mar 12 03:02:42 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -843,7 +870,10 @@ A B C Z , 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. >> @@ -853,7 +883,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:40:08 2016 +-- Definition current Sat Mar 12 03:03:05 2016 ( A LARGEINT DEFAULT NULL /*altered_col*/ @@ -862,7 +892,10 @@ A B C Z , 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; @@ -871,7 +904,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:40:26 2016 +-- Definition current Sat Mar 12 03:03:25 2016 ( A LARGEINT DEFAULT NULL /*altered_col*/ @@ -880,7 +913,10 @@ A B C Z , 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); @@ -889,7 +925,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:40:43 2016 +-- Definition current Sat Mar 12 03:03:42 2016 ( A LARGEINT DEFAULT NULL /*altered_col*/ @@ -898,7 +934,10 @@ A B C Z , 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); @@ -907,7 +946,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:40:58 2016 +-- Definition current Sat Mar 12 03:04:05 2016 ( A LARGEINT DEFAULT NULL /*altered_col*/ @@ -916,7 +955,10 @@ A B C Z , 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; @@ -925,7 +967,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:41:12 2016 +-- Definition current Sat Mar 12 03:04:25 2016 ( A INT DEFAULT NULL /*altered_col*/ @@ -934,7 +976,10 @@ A B C Z , 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; @@ -943,7 +988,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:41:30 2016 +-- Definition current Sat Mar 12 03:04:44 2016 ( A SMALLINT DEFAULT 0 NOT NULL NOT DROPPABLE @@ -953,7 +998,10 @@ A B C Z , 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; @@ -966,7 +1014,7 @@ A B C Z --- 1 row(s) selected. >> >>-- aligned format ->>drop table if exists t027t7; +>>drop table if exists t027t7 cascade; --- SQL operation complete. >>create table t027t7(a int not null, b varchar(2), @@ -977,7 +1025,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:41:52 2016 +-- Definition current Sat Mar 12 03:05:07 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -986,7 +1034,10 @@ A B C Z , 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); @@ -1006,7 +1057,7 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:42:09 2016 +-- Definition current Sat Mar 12 03:05:32 2016 ( A LARGEINT DEFAULT NULL /*altered_col*/ @@ -1015,7 +1066,10 @@ A B C Z , 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; @@ -1032,14 +1086,17 @@ A B C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:42:35 2016 +-- Definition current Sat Mar 12 03:05:57 2016 ( 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; @@ -1056,16 +1113,19 @@ A C Z >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:42:38 2016 +-- Definition current Sat Mar 12 03:06:00 2016 ( 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; @@ -1082,14 +1142,17 @@ A C Z B >>invoke t027t7; -- Definition of Trafodion table TRAFODION.SCH027.T027T7 --- Definition current Fri Feb 26 20:43:02 2016 +-- Definition current Sat Mar 12 03:06:25 2016 ( 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; @@ -1100,6 +1163,46 @@ 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 +============================ + +SCH027.T027V1 + +--- SQL operation complete. +>>invoke t027v1; + +-- Definition of Trafodion view TRAFODION.SCH027.T027V1 +-- Definition current Sat Mar 12 03:06:33 2016 + + ( + 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 Sat Mar 12 03:07:06 2016 + + ( + 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; @@ -1116,8 +1219,6 @@ A C Z *** 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[8839] Transaction was aborted. - *** ERROR[1404] Column C cannot be altered. Reason: Old data could not be updated using the altered column definition. --- SQL operation failed with errors. @@ -1126,9 +1227,327 @@ A C Z *** 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[1146] Object TRAFODION.SCH027.T027V1 could not be altered because it either does not exist or is not a table. + +--- SQL operation failed with errors. +>>get all views on table t027t7; + +Views on Table SCH027.T027T7 +============================ + +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) ++>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 Sat Mar 12 03:09:01 2016 + + ( + 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 Sat Mar 12 03:09:12 2016 + + ( + 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 Sat Mar 12 03:09:24 2016 + + ( + 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); + +--- 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 +============================ + +SCH027.T027V1 +SCH027.T027V11 +SCH027.T027V113 +SCH027.T027V12 +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 +============================ + +SCH027.T027V1 +SCH027.T027V11 +SCH027.T027V113 +SCH027.T027V12 +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[20123] A user-defined transaction has been started. ALTER cannot be performed. + +*** ERROR[8839] Transaction was aborted. + +--- SQL operation failed with errors. +>>rollback work; + +*** ERROR[8609] Waited rollback performed without starting a transaction. + +--- SQL operation failed with errors. +>>begin work; + +--- SQL operation complete. +>>alter table t027t7 alter column b largeint; + +*** ERROR[20123] A user-defined transaction has been started. ALTER cannot be performed. + +*** ERROR[8839] Transaction was aborted. + +--- SQL operation failed with errors. +>>rollback work; + +*** ERROR[8609] Waited rollback performed without starting a transaction. + +--- SQL operation failed with errors. +>> +>>-- 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 Thu Mar 17 14:48:27 2016 + + ( + 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. >> >>-- cleanup ->>drop table if exists t027t7; +>>?section clean_up +>>drop table if exists t027t7 cascade; --- SQL operation complete. >>drop table if exists t027t01;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/seabase/EXPECTED030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED030 b/core/sql/regress/seabase/EXPECTED030 new file mode 100644 index 0000000..3d85727 --- /dev/null +++ b/core/sql/regress/seabase/EXPECTED030 @@ -0,0 +1,599 @@ +>> +>>select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('03/01/2016', 'MM/DD/YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('01.03.2016', 'DD.MM.YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('2016-03', 'YYYY-MM') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('2016/03/01', 'YYYY/MM/DD') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('20160301', 'YYYYMMDD') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('16/03/01', 'YY/MM/DD') from (values(1)) x(a); + +(EXPR) +---------- + +0016-03-01 + +--- 1 row(s) selected. +>>select to_date('03/01/16', 'MM/DD/YY') from (values(1)) x(a); + +(EXPR) +---------- + +0016-03-01 + +--- 1 row(s) selected. +>>select to_date('03-01-2016', 'MM-DD-YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('201603', 'YYYYMM') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('01-03-2016', 'DD-MM-YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('01-MAR-2016', 'DD-MON-YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('01MAR2016', 'DDMONYYYY') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date('20160301101112', 'YYYYMMDDHH24MISS') from (values(1)) x(a); + +(EXPR) +-------------------------- + +2016-03-01 10:11:12.000000 + +--- 1 row(s) selected. +>>select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +-------------------------- + +2016-03-01 10:11:12.000000 + +--- 1 row(s) selected. +>>select to_date('2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +-------------------------- + +2016-03-01 10:11:12.000000 + +--- 1 row(s) selected. +>>select to_date('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +-------------------------- + +2016-03-01 10:11:12.000000 + +--- 1 row(s) selected. +>>select to_date('01-MAR-2016 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a); + +(EXPR) +-------------------------- + +2016-03-01 10:11:12.000000 + +--- 1 row(s) selected. +>>select to_date('March 01, 2016, 10:11', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a); + +(EXPR) +-------------------------- + +2016-03-01 10:11:00.000000 + +--- 1 row(s) selected. +>>select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +-------- + +10:23:34 + +--- 1 row(s) selected. +>>select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a); + +(EXPR) +-------- + +10:23:34 + +--- 1 row(s) selected. +>>select to_time ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +-------- + +10:23:34 + +--- 1 row(s) selected. +>>select to_time ('10:23:34', 'HH:MI:SS') from (values(1)) x(a); + +(EXPR) +-------- + +10:23:34 + +--- 1 row(s) selected. +>> +>>select to_char(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'MM/DD/YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +03/01/2016 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'DD.MM.YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +01.03.2016 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'YYYY-MM') from (values(1)) x(a); + +(EXPR) +------- + +2016-03 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'YYYY/MM/DD') from (values(1)) x(a); + +(EXPR) +---------- + +2016/03/01 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'YYYYMMDD') from (values(1)) x(a); + +(EXPR) +-------- + +20160301 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'YY/MM/DD') from (values(1)) x(a); + +(EXPR) +-------- + +16/03/01 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'MM/DD/YY') from (values(1)) x(a); + +(EXPR) +-------- + +03/01/16 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'MM-DD-YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +03-01-2016 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'YYYYMM') from (values(1)) x(a); + +(EXPR) +------ + +201603 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'DD-MM-YYYY') from (values(1)) x(a); + +(EXPR) +---------- + +01-03-2016 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'DD-MON-YYYY') from (values(1)) x(a); + +(EXPR) +----------- + +01-MAR-2016 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'DDMONYYYY') from (values(1)) x(a); + +(EXPR) +--------- + +01MAR2016 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'YYYYMMDDHH24MISS') from (values(1)) x(a); + +(EXPR) +-------------- + +2016030100:00: + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +------------------- + +01.03.2016:00:00:00 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +------------------- + +2016-03-01 00:00:00 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +------------------- + +03/01/2016 00:00:00 + +--- 1 row(s) selected. +>>select to_char(DATE '2016-03-01', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a); + +(EXPR) +-------------------- + +01-MAR-2016 00:00:00 + +--- 1 row(s) selected. +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYYMMDDHH24MISS') from (values(1)) x(a); + +(EXPR) +-------------- + +20160301101112 + +--- 1 row(s) selected. +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +------------------- + +01.03.2016:10:11:12 + +--- 1 row(s) selected. +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +------------------- + +2016-03-01 10:11:12 + +--- 1 row(s) selected. +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +------------------- + +03/01/2016 10:11:12 + +--- 1 row(s) selected. +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a); + +(EXPR) +-------------------- + +01-MAR-2016 10:11:12 + +--- 1 row(s) selected. +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a); + +(EXPR) +------------------------- + +March 01, 2016, 10:11:12 + +--- 1 row(s) selected. +>>select to_char (time '10:23:34', 'HH24:MI:SS') from (values(1)) x(a); + +(EXPR) +-------- + +10:23:34 + +--- 1 row(s) selected. +>>select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a); + +(EXPR) +-------- + +10:23:34 + +--- 1 row(s) selected. +>> +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'HH:MI:SS') from (values(1)) x(a); + +(EXPR) +-------- + +10:11:12 + +--- 1 row(s) selected. +>> +>>select dateformat(time '10:11:12.1', default) from (values(1)) x(a); + +(EXPR) +---------- + +10:11:12.1 + +--- 1 row(s) selected. +>>select dateformat(time '10:11:12.123', usa) from (values(1)) x(a); + +(EXPR) +--------------- + +10:11:12.123 AM + +--- 1 row(s) selected. +>>select dateformat(time '10:11:12', european) from (values(1)) x(a); + +(EXPR) +-------- + +10.11.12 + +--- 1 row(s) selected. +>>select dateformat(timestamp '2016-03-01 10:11:12.1', default) from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select dateformat(timestamp '2016-03-01 10:11:12', usa) from (values(1)) x(a); + +(EXPR) +---------- + +03/01/2016 + +--- 1 row(s) selected. +>>select dateformat(timestamp '2016-03-01 10:11:12.123', european) from (values(1)) x(a); + +(EXPR) +---------- + +01.03.2016 + +--- 1 row(s) selected. +>> +>>drop table if exists t030t1; + +--- SQL operation complete. +>>create table t030t1 (a date, b char(30), c varchar(30)); + +--- SQL operation complete. +>>insert into t030t1 values (date '2016-03-01', '2016-03-01', '2016-03-01'); + +--- 1 row(s) inserted. +>> +>>select to_char(a, 'YYYYMMDD') from t030t1; + +(EXPR) +-------- + +20160301 + +--- 1 row(s) selected. +>>select a (date, format 'YYYYMMDD') from t030t1; + +(EXPR) +-------- + +20160301 + +--- 1 row(s) selected. +>>select to_date(b, 'YYYY-MM-DD') from t030t1; + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date(c, 'YYYY-MM-DD') from t030t1; + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>> +>>-- negative tests +>>select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a); + +*** ERROR[4065] The format, "YYYYMM-DD", specified in the TO_DATE function is not supported. + +*** ERROR[8822] The statement was not prepared. + +>>select to_date(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); + +*** ERROR[4043] The operand of function TO_DATE must be character. + +*** ERROR[8822] The statement was not prepared. + +>>select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a); + +*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. + +--- 0 row(s) selected. +>>select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); + +*** ERROR[4071] The first operand of function TO_CHAR must be a datetime. + +*** ERROR[8822] The statement was not prepared. + +>>select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a); + +*** ERROR[4043] The operand of function TO_DATE must be character. + +*** ERROR[8822] The statement was not prepared. + +>>select to_char(date '2016-03-01', 'HH:MI:SS') from (values(1)) x(a); + +*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a time. + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- some formats only enabled in special mode. Not externalized. +>>cqd mode_special_4 'ON'; + +--- SQL operation complete. +>>select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a); + +(EXPR) +---------- + +2016-03-01 + +--- 1 row(s) selected. +>>select to_date(12345678, '99:99:99:99') from (values(1)) x(a); + +(EXPR) +----------- + +12:34:56:78 + +--- 1 row(s) selected. +>>select to_date(-12345678, '-99:99:99:99') from (values(1)) x(a); + +(EXPR) +------------ + +-12:34:56:78 + +--- 1 row(s) selected. +>> +>>-- ms4 error cases +>>select to_date(123456789, '99:99:99:99') from (values(1)) x(a); + +*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. + +*** ERROR[8001] An internal executor error occurred. + +--- 0 row(s) selected. +>>select to_date(-12345678, '99:99:99:99') from (values(1)) x(a); + +*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. + +*** ERROR[8001] An internal executor error occurred. + +--- 0 row(s) selected. +>>select to_date(1e0, '99:99:99:99') from (values(1)) x(a); + +*** ERROR[4046] The operands of function TO_DATE must be exact numeric. + +*** ERROR[8822] The statement was not prepared. + +>>select to_date(1.2, '99:99:99:99') from (values(1)) x(a); + +*** ERROR[4047] The operands of function TO_DATE must have a scale of 0. + +*** ERROR[8822] The statement was not prepared. + +>> +>>log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/seabase/EXPECTED031 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED031 b/core/sql/regress/seabase/EXPECTED031 new file mode 100644 index 0000000..5d0ea78 --- /dev/null +++ b/core/sql/regress/seabase/EXPECTED031 @@ -0,0 +1,214 @@ +>> +>>-- tests for PRIMARY KEY constraint usage +>>drop table if exists t031t1; + +--- SQL operation complete. +>>create table t031t1 (a int not null, b int not null, constraint ppk primary key(a)); + +--- SQL operation complete. +>>alter table t031t1 add constraint ppk primary key(b); + +*** ERROR[1043] Constraint TRAFODION.SCH.PPK already exists. + +--- SQL operation failed with errors. +>>alter table t031t1 add constraint ppk2 primary key(b); + +*** ERROR[1256] PRIMARY KEY constraint cannot be added since table TRAFODION.SCH.T031T1 already has a user specified clustering key. + +--- SQL operation failed with errors. +>>alter table t031t1 add constraint ppk unique(b); + +*** ERROR[1043] Constraint TRAFODION.SCH.PPK already exists. + +--- SQL operation failed with errors. +>>alter table t031t1 drop constraint ppk; + +*** ERROR[1255] Constraint TRAFODION.SCH.PPK is the clustering key constraint for table TRAFODION.SCH.T031T1 and cannot be dropped. + +--- SQL operation failed with errors. +>> +>>drop table if exists t031t1; + +--- SQL operation complete. +>>create table t031t1 (a int not null, b int not null); + +--- SQL operation complete. +>>alter table t031t1 add constraint ppk primary key(a); + +--- SQL operation complete. +>>alter table t031t1 add constraint ppk primary key(b); + +*** ERROR[1043] Constraint TRAFODION.SCH.PPK already exists. + +--- SQL operation failed with errors. +>>alter table t031t1 add constraint ppk2 primary key(b); + +--- SQL operation complete. +>>alter table t031t1 add constraint ppk unique(b); + +*** ERROR[1043] Constraint TRAFODION.SCH.PPK already exists. + +--- SQL operation failed with errors. +>>alter table t031t1 drop constraint ppk; + +--- SQL operation complete. +>> +>>drop table if exists t031t1; + +--- SQL operation complete. +>>create table t031t1 (a int not null, b int not null) store by (a); + +--- SQL operation complete. +>>alter table t031t1 add constraint ppk primary key(a); + +*** ERROR[1256] PRIMARY KEY constraint cannot be added since table TRAFODION.SCH.T031T1 already has a user specified clustering key. + +--- SQL operation failed with errors. +>>alter table t031t1 add constraint ppk unique(b); + +--- SQL operation complete. +>>alter table t031t1 drop constraint ppk; + +--- SQL operation complete. +>> +>>drop table if exists t031t1; + +--- SQL operation complete. +>>create table t031t1 (a int not null primary key, b int not null); + +--- SQL operation complete. +>>alter table t031t1 add constraint ppk primary key(a); + +*** ERROR[1254] Duplicate unique constraints are not allowed with same set of columns. + +--- SQL operation failed with errors. +>>alter table t031t1 add constraint ppk primary key(b); + +*** ERROR[1256] PRIMARY KEY constraint cannot be added since table TRAFODION.SCH.T031T1 already has a user specified clustering key. + +--- SQL operation failed with errors. +>> +>>-- primary key update transformed into delete/insert incorrectly +>>-- deletes row after conflict +>>create table if not exists t031t1 (a int not null primary key, b int not null); + +--- SQL operation complete. +>>delete from t031t1; + +--- 0 row(s) deleted. +>>insert into t031t1 values (1,1), (2,2), (3,3), (4,4); + +--- 4 row(s) inserted. +>>update t031t1 set a = 4 where a = 2; + +*** ERROR[8102] The operation is prevented by a unique constraint. + +--- 0 row(s) updated. +>>select * from t031t1; + +A B +----------- ----------- + + 1 1 + 2 2 + 3 3 + 4 4 + +--- 4 row(s) selected. +>> +>>-- incorrect ddl with salt clause should not crash +>>drop table if exists t031t1; + +--- SQL operation complete. +>>create table t031t1 ( ++>T2C1 int not null not droppable, ++>T2C1 int not null not droppable, ++>T2C1 int) ++>salt using 2 partitions on (T2C1, T2C2) ++>store by (T2C1, T2C2); + +*** ERROR[1009] Column T2C2 does not exist in the specified table. + +--- SQL operation failed with errors. +>> +>>-- cannot rename table with check constraints. +>>-- cascade option with rename not supported. +>>drop table if exists t031t1; + +--- SQL operation complete. +>>create table t031t1 (a int); + +--- SQL operation complete. +>>alter table t031t1 add constraint t031t1_c1 check (a > 0); + +--- SQL operation complete. +>>alter table t031t1 rename to t031t1_ren cascade; + +*** ERROR[1427] Table cannot be renamed. Reason: Cascade option not supported. + +--- SQL operation failed with errors. +>>alter table t031t1 rename to t031t1_ren; + +*** ERROR[1427] Table cannot be renamed. Reason: Operation not allowed if check constraints are present. Drop the constraints and recreate them after rename. + +--- SQL operation failed with errors. +>> +>>-- time datatype conversion was returning incorrect results +>>drop table if exists t031t1; + +--- SQL operation complete. +>>create table t031t1 ++>(id int not null, ++>time1 time default null, ++>time2 time default null, ++>type1 time default null, ++>type2 char(5) default null, ++>diff char(6) default null, ++>primary key (id)); + +--- SQL operation complete. +>>insert into t031t1 (id, time1, time2, diff) values (1, time '00:00:30.758788', time '00:00:29.615308', 'MATCH'); + +--- 1 row(s) inserted. +>>select * from t031t1 order by 1; + +ID TIME1 TIME2 TYPE1 TYPE2 DIFF +----------- -------- -------- -------- ----- ------ + + 1 00:00:30 00:00:29 ? ? MATCH + +--- 1 row(s) selected. +>>update t031t1 ++>set type1 = ++>case when (time1 + interval '1' second) < time2 then time1 else time2 ++>end, ++>type2 = ++>case when (time1 + interval '1' second) < time2 then 'T1' else 'T2' ++>end ++>where id = 1; + +--- 1 row(s) updated. +>>select ++>type2, diff, type1, ++>case ++>when diff = 'MATCH' then 'PASS' else 'FAIL' ++>end ++>from t031t1 ++>where id = 1; + +TYPE2 DIFF TYPE1 (EXPR) +----- ------ -------- ------ + +T2 MATCH 00:00:29 PASS + +--- 1 row(s) selected. +>>select * from t031t1; + +ID TIME1 TIME2 TYPE1 TYPE2 DIFF +----------- -------- -------- -------- ----- ------ + + 1 00:00:30 00:00:29 00:00:29 T2 MATCH + +--- 1 row(s) selected. +>> +>>log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/seabase/TEST020 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST020 b/core/sql/regress/seabase/TEST020 index 64afa3c..a7ab1f4 100755 --- a/core/sql/regress/seabase/TEST020 +++ b/core/sql/regress/seabase/TEST020 @@ -90,6 +90,7 @@ drop table test020_t33 cascade; drop table test020t40 cascade; drop table test020t41 cascade; drop table test020t42 cascade; +drop table test020t43 cascade; ?section tests create table test020t1 (c1 int not null primary key, @@ -136,11 +137,10 @@ create table test020t31 (g0 smallint default 32768 not null primary key); -- Test columns named SYSKEY -- --- Expecting error 1080 message: Duplicate references to column SYSKEY --- in the create request --- when the following Create Table command is executed. +-- Expecting error 1269 message: Col name SYSKEY is reserved. create table test020t5(syskey int); +cqd traf_allow_reserved_colnames 'ON'; create table test020t5(c int not null not droppable primary key not droppable, syskey char(4)); invoke test020t5; @@ -151,6 +151,9 @@ alter table test020t5 add constraint test020t5c1 check (syskey >= 'aaaa'); showddl test020t5; -- Expecting an error message when the following insert statement is executed. insert into test020t5 values (3, '1234'); + +cqd traf_allow_reserved_colnames reset; + -------------------------------------------- -- test cases for solution 10-040607-6721 -- -------------------------------------------- @@ -815,3 +818,9 @@ select * from table(index_table test020t42ix) ; -- check if the updated d column is reflected in the index upsert into test020t42 (a,b,e) values (1, 'c', 4); select * from table(index_table test020t42ix) ; + +create table test020t43(c1 int, c2 int ) attribute aligned format ; +upsert into test020t43 values (1,1); +upsert into test020t43 (c1) values(1); +select * from test020t43 ; + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/seabase/TEST027 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST027 b/core/sql/regress/seabase/TEST027 index d828640..b3d2710 100644 --- a/core/sql/regress/seabase/TEST027 +++ b/core/sql/regress/seabase/TEST027 @@ -21,14 +21,12 @@ -- -- tests for support of multiple column families -- tests for alter table alter column +-- tests for reserved words usage in create/add/alter stmts create schema trafodion.sch027; set schema trafodion.sch027; -drop table if exists t027t01; -drop table if exists t027t011; -drop table t027t02; -drop table t027t03; +obey TEST027(clean_up); log LOG027 clear; @@ -210,7 +208,7 @@ invoke t027t7; select * from t027t7; -- aligned format -drop table if exists t027t7; +drop table if exists t027t7 cascade; 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; @@ -229,15 +227,90 @@ select * from t027t7; alter table t027t7 drop column b; invoke t027t7; select * from t027t7; +create view t027v1 as select * from t027t7; +get all views on table t027t7; +invoke t027v1; +alter table t027t7 alter column a smallint; +invoke t027v1; -- negative tests for alter/drop column alter table t027t7 alter column b int; alter table t027t7 alter column a char(10); alter table t027t7 alter column c char(1); alter table t027t7 drop column e; +alter table t027t7 drop column a; +alter table t027v1 alter column a smallint; +get all views on table t027t7; + +-- tests for alter column rename +drop table if exists t027t7 cascade; +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; +create index t027t7i1 on t027t7(b); +insert into t027t7 values (1, 'ab', 'cd', 10); +invoke t027t7; +select * from t027t7; +alter table t027t7 alter column b rename to bb; +invoke t027t7; +select * from t027t7; +alter table t027t7 alter column z rename to zz; +invoke t027t7; +select * from t027t7; +create view t027v1(zz) as select zz from t027t7; +alter table t027t7 alter column c rename to cc; +create view t027v2(zz) as select zz from t027t7 where cc = 'a'; +showddl t027v2; + +-- negative tests for alter column rename +alter table t027t7 alter column zz rename to a; +alter table t027t7 alter column "_SALT_" rename to nosalt; +alter table t027t7 alter column cc rename to "_SALT_"; +alter table t027t7 alter column zz rename to zzz; +alter table t027t7 alter column cc rename to ccc; + +-- alter col with views +drop table if exists t027t7 cascade; +create table t027t7 (a varchar(10), b int); +create view t027v1 as select * from t027t7; +create view t027v12 as select * from t027v1; +create view t027v11 as select * from t027t7; +create view t027v122 as select * from t027v12; +create view t027v113 as select * from t027v11; +get all views on table t027t7; +alter table t027t7 alter column a varchar(20); +get all views on table t027t7; + +-- some alter operations cannot be performed within a user xn +cqd ddl_transactions 'ON'; +begin work; +alter table t027t7 drop column b; +rollback work; +begin work; +alter table t027t7 alter column b largeint; +rollback work; + +-- reserved words cannot be used as colnames in create/add/alter stmts +drop table if exists t027t7 cascade; +create table t027t7 (syskey int); +create table t027t7 ("_SALT_" int); +create table t027t7 ("_DIVISION_2_" int); +create table t027t7 (a int not null primary key); +alter table t027t7 add column "_SALT_" int; +alter table t027t7 alter column a rename to SYSKEY; + +-- if cqd is specified, then reserved cols can be used. +-- Use this cqd carefully. +cqd traf_allow_reserved_colnames 'ON'; +drop table if exists t027t7 cascade; +create table t027t7 (syskey int not null primary key, b int); +alter table t027t7 add column "_DIVISION_1" int; +alter table t027t7 alter column b rename to "_SALT_"; +invoke t027t7; -- cleanup -drop table if exists t027t7; +?section clean_up +drop table if exists t027t7 cascade; drop table if exists t027t01; drop table if exists t027t02; drop table if exists t027t011; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/seabase/TEST030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST030 b/core/sql/regress/seabase/TEST030 new file mode 100644 index 0000000..62d5a0b --- /dev/null +++ b/core/sql/regress/seabase/TEST030 @@ -0,0 +1,117 @@ +-- @@@ START COPYRIGHT @@@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. +-- +-- @@@ END COPYRIGHT @@@ +-- + +-- tests for TO_DATE, TO_CHAR, TO_TIMESTAMP, TO_NUMBER functions + +log LOG030 clear; + +select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); +select to_date('03/01/2016', 'MM/DD/YYYY') from (values(1)) x(a); +select to_date('01.03.2016', 'DD.MM.YYYY') from (values(1)) x(a); +select to_date('2016-03', 'YYYY-MM') from (values(1)) x(a); +select to_date('2016/03/01', 'YYYY/MM/DD') from (values(1)) x(a); +select to_date('20160301', 'YYYYMMDD') from (values(1)) x(a); +select to_date('16/03/01', 'YY/MM/DD') from (values(1)) x(a); +select to_date('03/01/16', 'MM/DD/YY') from (values(1)) x(a); +select to_date('03-01-2016', 'MM-DD-YYYY') from (values(1)) x(a); +select to_date('201603', 'YYYYMM') from (values(1)) x(a); +select to_date('01-03-2016', 'DD-MM-YYYY') from (values(1)) x(a); +select to_date('01-MAR-2016', 'DD-MON-YYYY') from (values(1)) x(a); +select to_date('01MAR2016', 'DDMONYYYY') from (values(1)) x(a); +select to_date('20160301101112', 'YYYYMMDDHH24MISS') from (values(1)) x(a); +select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a); +select to_date('2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a); +select to_date('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a); +select to_date('01-MAR-2016 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a); +select to_date('March 01, 2016, 10:11', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a); +select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a); +select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a); +select to_time ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a); +select to_time ('10:23:34', 'HH:MI:SS') from (values(1)) x(a); + +select to_char(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'MM/DD/YYYY') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'DD.MM.YYYY') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'YYYY-MM') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'YYYY/MM/DD') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'YYYYMMDD') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'YY/MM/DD') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'MM/DD/YY') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'MM-DD-YYYY') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'YYYYMM') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'DD-MM-YYYY') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'DD-MON-YYYY') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'DDMONYYYY') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'YYYYMMDDHH24MISS') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a); +select to_char(DATE '2016-03-01', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a); +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYYMMDDHH24MISS') from (values(1)) x(a); +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a); +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a); +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a); +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a); +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a); +select to_char (time '10:23:34', 'HH24:MI:SS') from (values(1)) x(a); +select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a); + +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD') from (values(1)) x(a); +select to_char(TIMESTAMP '2016-03-01 10:11:12', 'HH:MI:SS') from (values(1)) x(a); + +select dateformat(time '10:11:12.1', default) from (values(1)) x(a); +select dateformat(time '10:11:12.123', usa) from (values(1)) x(a); +select dateformat(time '10:11:12', european) from (values(1)) x(a); +select dateformat(timestamp '2016-03-01 10:11:12.1', default) from (values(1)) x(a); +select dateformat(timestamp '2016-03-01 10:11:12', usa) from (values(1)) x(a); +select dateformat(timestamp '2016-03-01 10:11:12.123', european) from (values(1)) x(a); + +drop table if exists t030t1; +create table t030t1 (a date, b char(30), c varchar(30)); +insert into t030t1 values (date '2016-03-01', '2016-03-01', '2016-03-01'); + +select to_char(a, 'YYYYMMDD') from t030t1; +select a (date, format 'YYYYMMDD') from t030t1; +select to_date(b, 'YYYY-MM-DD') from t030t1; +select to_date(c, 'YYYY-MM-DD') from t030t1; + +-- negative tests +select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a); +select to_date(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); +select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a); +select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a); +select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a); +select to_char(date '2016-03-01', 'HH:MI:SS') from (values(1)) x(a); + +-- some formats only enabled in special mode. Not externalized. +cqd mode_special_4 'ON'; +select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a); +select to_date(12345678, '99:99:99:99') from (values(1)) x(a); +select to_date(-12345678, '-99:99:99:99') from (values(1)) x(a); + +-- ms4 error cases +select to_date(123456789, '99:99:99:99') from (values(1)) x(a); +select to_date(-12345678, '99:99:99:99') from (values(1)) x(a); +select to_date(1e0, '99:99:99:99') from (values(1)) x(a); +select to_date(1.2, '99:99:99:99') from (values(1)) x(a); + +log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/seabase/TEST031 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST031 b/core/sql/regress/seabase/TEST031 new file mode 100644 index 0000000..5265339 --- /dev/null +++ b/core/sql/regress/seabase/TEST031 @@ -0,0 +1,108 @@ +-- @@@ START COPYRIGHT @@@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. +-- +-- @@@ END COPYRIGHT @@@ +-- + +-- tests for various misc fixes + +log LOG031 clear; + +-- tests for PRIMARY KEY constraint usage +drop table if exists t031t1; +create table t031t1 (a int not null, b int not null, constraint ppk primary key(a)); +alter table t031t1 add constraint ppk primary key(b); +alter table t031t1 add constraint ppk2 primary key(b); +alter table t031t1 add constraint ppk unique(b); +alter table t031t1 drop constraint ppk; + +drop table if exists t031t1; +create table t031t1 (a int not null, b int not null); +alter table t031t1 add constraint ppk primary key(a); +alter table t031t1 add constraint ppk primary key(b); +alter table t031t1 add constraint ppk2 primary key(b); +alter table t031t1 add constraint ppk unique(b); +alter table t031t1 drop constraint ppk; + +drop table if exists t031t1; +create table t031t1 (a int not null, b int not null) store by (a); +alter table t031t1 add constraint ppk primary key(a); +alter table t031t1 add constraint ppk unique(b); +alter table t031t1 drop constraint ppk; + +drop table if exists t031t1; +create table t031t1 (a int not null primary key, b int not null); +alter table t031t1 add constraint ppk primary key(a); +alter table t031t1 add constraint ppk primary key(b); + +-- primary key update transformed into delete/insert incorrectly +-- deletes row after conflict +create table if not exists t031t1 (a int not null primary key, b int not null); +delete from t031t1; +insert into t031t1 values (1,1), (2,2), (3,3), (4,4); +update t031t1 set a = 4 where a = 2; +select * from t031t1; + +-- incorrect ddl with salt clause should not crash +drop table if exists t031t1; +create table t031t1 ( +T2C1 int not null not droppable, +T2C1 int not null not droppable, +T2C1 int) +salt using 2 partitions on (T2C1, T2C2) +store by (T2C1, T2C2); + +-- cannot rename table with check constraints. +-- cascade option with rename not supported. +drop table if exists t031t1; +create table t031t1 (a int); +alter table t031t1 add constraint t031t1_c1 check (a > 0); +alter table t031t1 rename to t031t1_ren cascade; +alter table t031t1 rename to t031t1_ren; + +-- time datatype conversion was returning incorrect results +drop table if exists t031t1; +create table t031t1 +(id int not null, +time1 time default null, +time2 time default null, +type1 time default null, +type2 char(5) default null, +diff char(6) default null, +primary key (id)); +insert into t031t1 (id, time1, time2, diff) values (1, time '00:00:30.758788', time '00:00:29.615308', 'MATCH'); +select * from t031t1 order by 1; +update t031t1 +set type1 = +case when (time1 + interval '1' second) < time2 then time1 else time2 +end, +type2 = +case when (time1 + interval '1' second) < time2 then 'T1' else 'T2' +end +where id = 1; +select +type2, diff, type1, +case +when diff = 'MATCH' then 'PASS' else 'FAIL' +end +from t031t1 +where id = 1; +select * from t031t1; + +log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/tools/dll-compile.ksh ---------------------------------------------------------------------- diff --git a/core/sql/regress/tools/dll-compile.ksh b/core/sql/regress/tools/dll-compile.ksh index e4ae68f..cf6a522 100755 --- a/core/sql/regress/tools/dll-compile.ksh +++ b/core/sql/regress/tools/dll-compile.ksh @@ -66,12 +66,12 @@ TARGET= fi CC_OPTS="-g " CC_OPTS="$CC_OPTS -I$MY_SQROOT/sql/sqludr" - CC_OPTS="$CC_OPTS -I$MY_SQROOT/export/include/sql -I$MY_SQROOT/export/include/nsk -I${TOOLSDIR}/${HADOOP_DIST}/${HADOOP_INC_DIR} -I${JAVA_HOME}/include -I${JAVA_HOME}/include/linux" + CC_OPTS="$CC_OPTS -I$MY_SQROOT/export/include/sql -I$MY_SQROOT/export/include/nsk -I${JAVA_HOME}/include -I${JAVA_HOME}/include/linux" CC_OPTS="$CC_OPTS -w -O0 -Wno-unknown-pragmas -fPIC -fshort-wchar -c -o $BASE.o $1" TARGET=$BASE.dll LD=$CC LD_OPTS=" -w -O0 -Wno-unknown-pragmas -fshort-wchar" - LD_OPTS="$LD_OPTS -shared -rdynamic -o $TARGET -lc -lhdfs -ljvm -L$MY_SQROOT/export/lib${SQ_MBTYPE} -ltdm_sqlcli -L${TOOLSDIR}/${HADOOP_DIST}/${HADOOP_LIB_DIR} -L${JAVA_HOME}/jre/lib/amd64/server $2 $BASE.o" + LD_OPTS="$LD_OPTS -shared -rdynamic -o $TARGET -lc -lhdfs -ljvm -L$MY_SQROOT/export/lib${SQ_MBTYPE} -ltdm_sqlcli -L${JAVA_HOME}/jre/lib/amd64/server $2 $BASE.o" LONGLINE=\ ------------------------------------------------------------------------------ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/tools/runregr_udr.ksh ---------------------------------------------------------------------- diff --git a/core/sql/regress/tools/runregr_udr.ksh b/core/sql/regress/tools/runregr_udr.ksh index 45904a0..9fe4994 100755 --- a/core/sql/regress/tools/runregr_udr.ksh +++ b/core/sql/regress/tools/runregr_udr.ksh @@ -53,22 +53,6 @@ if [ $NSK -eq 1 -o $LINUX -eq 1 ]; then USE_NDCS=1 fi -# -# getJavaCompFuncs contains functions to display vprocs for Java -# components on NSK only. Here we simply load the shell functions that -# do the work. The functions will be called later. -# -typeset -i DISPLAY_NSK_JAVA_COMPS=0 -if [ $NSK -ne 0 -a "$SKIP_JAVA_CHECKS" != "" ]; then - if [ -s $scriptsdir/tools/getJavaCompFuncs ]; then - . $scriptsdir/tools/getJavaCompFuncs - DISPLAY_NSK_JAVA_COMPS=1 - else - echo "\n*** WARNING: Unable to locate $scriptsdir/tools/getJavaCompFuncs" - echo "*** Continuing" - fi -fi - function USAGE { cat <<EOF @@ -183,25 +167,8 @@ function PRINT_SKIP_FILES function PRINT_JAVA_COMPONENTS { - if [ $DISPLAY_NSK_JAVA_COMPS -eq 1 ]; then - - HEADER "Java components" - - getJdbcMxJarComp v - getJavaComp T0083 v - getJavaComp T1225 v - getLMComp v - getMxServerComp MXUDR T0083 v - getMxServerComp MXUDR T1225 v - getMxServerComp MXUDR T1231 v - - testJdbcInJavaAndUdrServer - testJdbcAndUdrServer - testJavaAndUdrServer - testJdbcInJarAndJava - testLMInJarAndUdrServer - - fi + # empty for now + true } # TESTS that make a JDBC Connection. These tests need ODBC assoc server @@ -366,8 +333,6 @@ typeset TESTFILES="$*" TESTFILES=`echo $TESTFILES | tr a-z A-Z` if [ "$TESTFILES" = "" ]; then TESTFILES="TEST???" -else - DISPLAY_NSK_JAVA_COMPS=0 fi PRETTY_FILES "$TESTFILES" TESTFILES="$PFILES" @@ -481,7 +446,7 @@ done seabase="$SEABASE_REGRESS" # sbtestfiles contains the list of tests to be run in seabase mode if [ "$seabase" -ne 0 ]; then - TESTFILES="TEST001 TEST002 TEST100 TEST101 TEST107 TEST108 TEST163" + TESTFILES="TEST001 TEST002 TEST100 TEST101 TEST102 TEST107 TEST108 TEST163" SBPFILES= for i in $PFILES; do for j in $TESTFILES; do @@ -613,7 +578,7 @@ function DO_DIFF if [ $OK -eq 0 ]; then RESULT="### FAIL (missing files) ###" printf "\n$RESULT\n" - printf "$(date '+%m/%d/%Y %R') $T\t $RESULT\n" >> $rgrlog + printf "$(date '+%m/%d/%Y %R') $T $RESULT\n" >> $rgrlog return fi @@ -744,7 +709,7 @@ function DO_DIFF # printf "$RESULT\n" # printf "$(date '+%m/%d/%Y %R') $T\t $RESULT\n" >> $rgrlog modtime=`stat --printf=%y $LOG | cut -d'.' -f1` - printf "$modtime $T\t $RESULT\n" >> $rgrlog + printf "$modtime $T $RESULT\n" >> $rgrlog } function DO_TEST @@ -760,12 +725,6 @@ function DO_TEST fi fi - # On NSK and LINUX, set CLASSPATH. If tests use a JDBC application, - # CLASSPATH should have JDBC jar. - if [ $NSK -ne 0 ]; then - export CLASSPATH=$SQLMX_JAVA_EXTENSIONS:$CLASSPATH:. - fi - # create a tempfile which is used as timestamp for deleting # saveabend files after the test completes touch TimestampFile @@ -835,26 +794,10 @@ echo "schema=$TEST_SCHEMA" >schema.prop echo "$(date '+%m/%d/%Y %R')" >> $rgrlog -# Following export is to workaround an MXCI problem where -# mxci crashes when a command such as set envvar X '$x:abc' used and -# x is not defined. -# -# _RLD_LIB_PATH is not used on MIPS and 'set envvar' crashes mxci. Right now -# there is no way to differentiate between MIPS and YOS from mxci prompt. -# So we are adding this workaround in this shell script. -# -if [ $NSK -ne 0 -a `uname -r` != H06 ]; then - export _RLD_LIB_PATH=/G/system/system -fi - -if [ $LINUX -ne 0 ]; then - export CLASSPATH=$MY_SQROOT/export/lib/hpt4jdbc.jar:$CLASSPATH:. -fi - export JDBC_T4_URL="jdbc:t4jdbc://localhost:23400/:" -if [ -r $MY_SQROOT/sql/scripts/swenv.sh ]; then +if [ -r $MY_SQROOT/sql/scripts/sw_env.sh ]; then # use a custom port for the JDBC Type 4 driver - . $MY_SQROOT/sql/scripts/swenv.sh + . $MY_SQROOT/sql/scripts/sw_env.sh export JDBC_T4_URL="jdbc:t4jdbc://localhost:${MY_DCS_MASTER_PORT}/:" fi @@ -885,7 +828,7 @@ do TEST_CAN_BE_RUN=0 RESULT="### FAIL (!! Test SKIPPED because NDCS SETUP is NOT RIGHT !! ) ###" printf "\n$RESULT\n" - printf "$(date '+%m/%d/%Y %R') $T\t $RESULT\n" >> $rgrlog + printf "$(date '+%m/%d/%Y %R') $T $RESULT\n" >> $rgrlog break; fi done
