http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/compGeneral/EXPECTED071 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED071 b/core/sql/regress/compGeneral/EXPECTED071 index 8c1813c..0182828 100644 --- a/core/sql/regress/compGeneral/EXPECTED071 +++ b/core/sql/regress/compGeneral/EXPECTED071 @@ -1015,7 +1015,7 @@ TRAFODION_SCAN MTDN1 OPERATOR TNAME BKEY EKEY ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -TRAFODION_SCAN MTDN3 begin_key: (_SALT_ = <min>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'ab '), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - 1))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - 1))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <min>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'ab'), (PC4 = <min>) end_key: (_SALT_ = <max>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'abcf'), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - 1))) - (cast(1900-01-01) - cast((cast(dayofweek( 1900-01-01)) - 1))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <max>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'abcf'), (PC4 = <max>) +TRAFODION_SCAN MTDN3 begin_key: (_SALT_ = <min>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'ab '), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <min>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'ab'), (PC4 = <min>) end_key: (_SALT_ = <max>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'abcf'), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayo fweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <max>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'abcf'), (PC4 = <max>) --- 1 row(s) selected. >>-- ("_DIVISION_1_", "_DIVISION_2_", "_DIVISION_3_", "_DIVISION_4_") has (=, >>between, = between, none) predicates @@ -1033,7 +1033,7 @@ TRAFODION_SCAN MTDN3 OPERATOR TNAME BKEY EKEY ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -TRAFODION_SCAN MTDN3 begin_key: (_SALT_ = <min>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'ab '), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - 1))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - 1))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <min>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'ab'), (PC4 = <min>) end_key: (_SALT_ = <max>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'abcf'), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - 1))) - (cast(1900-01-01) - cast((cast(dayofweek( 1900-01-01)) - 1))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <max>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'abcf'), (PC4 = <max>) +TRAFODION_SCAN MTDN3 begin_key: (_SALT_ = <min>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'ab '), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <min>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'ab'), (PC4 = <min>) end_key: (_SALT_ = <max>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'abcf'), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayo fweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(1)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <max>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'abcf'), (PC4 = <max>) --- 1 row(s) selected. >>
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/core/EXPECTED037.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED037.SB b/core/sql/regress/core/EXPECTED037.SB index be117f4..5a5a317 100755 --- a/core/sql/regress/core/EXPECTED037.SB +++ b/core/sql/regress/core/EXPECTED037.SB @@ -465,11 +465,9 @@ SELECT BIT_LENGTH BIT_LENGTH from (values(0)) BIT_LENGTH(BIT_LENGTH); *** ERROR[8822] The statement was not prepared. >> ->>-- Expect error [3128] +>>-- Expect error [15001] >>prepare s1 from SELECT BOOLEAN BOOLEAN from (values(0)) BOOLEAN(BOOLEAN); -*** ERROR[3128] BOOLEAN is a reserved word. It must be delimited by double-quotes to be used as an identifier. - *** ERROR[15001] A syntax error occurred at or before: SELECT BOOLEAN BOOLEAN from (values(0)) BOOLEAN(BOOLEAN); ^ (22 characters from start of SQL statement) http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/core/EXPECTED038.LINUX ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED038.LINUX b/core/sql/regress/core/EXPECTED038.LINUX index 8bb083a..80b9c0e 100644 --- a/core/sql/regress/core/EXPECTED038.LINUX +++ b/core/sql/regress/core/EXPECTED038.LINUX @@ -243,10 +243,10 @@ ORDER_NUM ORDER_DATE ORDER_TIME ORDER_QTY ----------- ---------- ---------- ----------- 100 1997-01-30 13:40:05 1000 - 200 2014-07-02 21:22:19 99 + 200 2016-07-09 05:13:55 99 300 1996-08-10 10:20:10 6000 - 400 1997-05-12 21:22:19 99 - 500 2014-07-02 21:22:19 99 + 400 1997-05-12 05:13:55 99 + 500 2016-07-09 05:13:55 99 --- 5 row(s) selected. >> @@ -264,7 +264,7 @@ ORDER_NUM ORDER_DATE ORDER_TIME ORDER_QTY T038PART_NUM ORDER_TIME ------------ -------------------------- - 600 2014-07-02 21:22:21.465352 + 600 2016-07-09 05:13:57.638206 --- 1 row(s) selected. >> @@ -743,9 +743,9 @@ EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE ----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ + 100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 100 RAO 6500 6500 QA DENNIS ? ? ? - 100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000 --- 3 row(s) selected. >> @@ -1920,7 +1920,7 @@ A1 >>select a1 from T038a +>where 94/0 between -47 and a1 + 0; -*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:9400 Target Type:LARGEINT(IBIN64S) Max Target Value:999. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH. +*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:9400 Target Type:LARGEINT(IBIN64S) Max Target Value:999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH. --- 0 row(s) selected. >> @@ -1972,7 +1972,7 @@ A1 >> >>execute s; -*** ERROR[8419] An arithmetic expression attempted a division by zero. +*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:-145000000000 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV. --- 0 row(s) selected. >> @@ -1985,7 +1985,7 @@ A1 >> >>execute s; -*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:18800 Target Type:LARGEINT(IBIN64S) Max Target Value:9999. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH. +*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:18800 Target Type:LARGEINT(IBIN64S) Max Target Value:9999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH. --- 0 row(s) selected. >> @@ -3164,17 +3164,17 @@ E1 --- 0 row(s) selected. >>select * from T038g where g1 = 2 / (1 - 1); -*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:20000 Target Type:LARGEINT(IBIN64S) Max Target Value:999. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH. +*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:20000 Target Type:LARGEINT(IBIN64S) Max Target Value:999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH. --- 0 row(s) selected. >>select * from T038g where g1 = 0 / ((1 - 1) * (2 + 3)) + g1; -*** ERROR[8419] An arithmetic expression attempted a division by zero. +*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:0 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV. --- 0 row(s) selected. >>select * from T038g where g1 = g1 + 0 / ((1 - 1) * (2 + 3)); -*** ERROR[8419] An arithmetic expression attempted a division by zero. +*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:0 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV. --- 0 row(s) selected. >>delete from T038g; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/core/TEST037 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/TEST037 b/core/sql/regress/core/TEST037 index 711632f..e80295a 100755 --- a/core/sql/regress/core/TEST037 +++ b/core/sql/regress/core/TEST037 @@ -226,7 +226,7 @@ prepare s1 from SELECT BEFORE BEFORE from (values(0)) BEFORE(BEFORE); -- Expect error [3128] prepare s1 from SELECT BIT_LENGTH BIT_LENGTH from (values(0)) BIT_LENGTH(BIT_LENGTH); --- Expect error [3128] +-- Expect error [15001] prepare s1 from SELECT BOOLEAN BOOLEAN from (values(0)) BOOLEAN(BOOLEAN); -- Expect error [3128] http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/executor/EXPECTED002.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED002.SB b/core/sql/regress/executor/EXPECTED002.SB index b0511c2..fa9bd67 100644 --- a/core/sql/regress/executor/EXPECTED002.SB +++ b/core/sql/regress/executor/EXPECTED002.SB @@ -52,10 +52,10 @@ >>?ifMP >>table t002v; -W X ------- ------ +W X +---- ---- - 1 11 + 1 11 --- 1 row(s) selected. >> -- should work @@ -3002,20 +3002,20 @@ A B C E +>on (T1.a = T2.a) +>; -A C ------------ ------ - - 2 79 - 10 79 - 11 79 - 12 79 - 13 79 - 14 79 - 15 79 - 16 79 - 17 79 - 18 79 - 19 79 +A C +----------- ---- + + 2 79 + 10 79 + 11 79 + 12 79 + 13 79 + 14 79 + 15 79 + 16 79 + 17 79 + 18 79 + 19 79 --- 11 row(s) selected. >> @@ -4298,20 +4298,20 @@ A B C E +>on (T1.a = T2.a) +>; -A C ------------ ------ - - 2 79 - 10 79 - 14 79 - 15 79 - 19 79 - 12 79 - 13 79 - 17 79 - 11 79 - 16 79 - 18 79 +A C +----------- ---- + + 2 79 + 10 79 + 14 79 + 15 79 + 19 79 + 12 79 + 13 79 + 17 79 + 11 79 + 16 79 + 18 79 --- 11 row(s) selected. >> @@ -6692,10 +6692,10 @@ A B C E +> WHERE ( EXISTS ( +> SELECT 27 FROM T002T1 )); -FOO ------- +FOO +---- - 54 + 54 --- 1 row(s) selected. >> -- 0 rows EMPTY, 1 MULTI http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/executor/EXPECTED012 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED012 b/core/sql/regress/executor/EXPECTED012 index 8259112..5c85c16 100755 --- a/core/sql/regress/executor/EXPECTED012 +++ b/core/sql/regress/executor/EXPECTED012 @@ -2465,7 +2465,7 @@ A B C --- 9 row(s) selected. >> ->>select bitextract(3, 15, 1) from (values(1)) x(a); +>>select bitextract(cast(3 as smallint), 15, 1) from (values(1)) x(a); (EXPR) ----------- @@ -2473,7 +2473,7 @@ A B C 1 --- 1 row(s) selected. ->>select bitextract(3, 14, 1) from (values(1)) x(a); +>>select bitextract(cast(3 as smallint), 14, 1) from (values(1)) x(a); (EXPR) ----------- @@ -2481,7 +2481,7 @@ A B C 1 --- 1 row(s) selected. ->>select bitextract(3, 14, 2) from (values(1)) x(a); +>>select bitextract(cast(3 as smallint), 14, 2) from (values(1)) x(a); (EXPR) ----------- @@ -2489,7 +2489,7 @@ A B C 3 --- 1 row(s) selected. ->>select bitextract(3, 0, 1) from (values(1)) x(a); +>>select bitextract(cast(3 as smallint), 0, 1) from (values(1)) x(a); (EXPR) ----------- @@ -2497,7 +2497,7 @@ A B C 0 --- 1 row(s) selected. ->>select bitextract(3, 0, 15) from (values(1)) x(a); +>>select bitextract(cast(3 as smallint), 0, 15) from (values(1)) x(a); (EXPR) ----------- @@ -2505,7 +2505,7 @@ A B C 1 --- 1 row(s) selected. ->>select bitextract(3, 0, 16) from (values(1)) x(a); +>>select bitextract(cast(3 as smallint), 0, 16) from (values(1)) x(a); (EXPR) ----------- @@ -2513,6 +2513,30 @@ A B C 3 --- 1 row(s) selected. +>>select bitextract(3, 0, 8) from (values(1)) x(a); + +(EXPR) +----------- + + 3 + +--- 1 row(s) selected. +>>select bitextract(3, 7, 1) from (values(1)) x(a); + +(EXPR) +----------- + + 1 + +--- 1 row(s) selected. +>>select bitextract(3, 8, 1) from (values(1)) x(a); + +(EXPR) +----------- + + 0 + +--- 1 row(s) selected. >> >>select a from t012t3 where a = bitand(a,a); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/executor/EXPECTED022.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED022.SB b/core/sql/regress/executor/EXPECTED022.SB index d19b72e..80301e8 100644 --- a/core/sql/regress/executor/EXPECTED022.SB +++ b/core/sql/regress/executor/EXPECTED022.SB @@ -2686,10 +2686,10 @@ A B >> >>select cast(1 as numeric(10) unsigned) from (values(0))x; -(EXPR) ------------ +(EXPR) +-------------------- - 1 + 1 --- 1 row(s) selected. >>select cast(1 as decimal(10) unsigned) from (values(0))x; @@ -4025,7 +4025,7 @@ Hello --- 0 row(s) selected. >>select cast(N'''\2' as double precision) from t022u; -*** ERROR[8413] The string argument contains characters that cannot be converted. Source data 275c32 +*** ERROR[8413] The string argument contains characters that cannot be converted. Source data 275c32 --- 0 row(s) selected. >>?ifNT @@ -4261,7 +4261,7 @@ Hello (EXPR) (EXPR) (EXPR) ------------- ------------- -------------------------- -10:13:14.0000 10:13:14.0000 2016-06-11 16:50:13.692785 +10:13:14.0000 10:13:14.0000 2016-07-10 05:20:08.800751 --- 1 row(s) selected. >>--test on precision of time @@ -7456,10 +7456,10 @@ A B >> >>select cast(1 as numeric(10) unsigned) from (values(0))x; -(EXPR) ------------ +(EXPR) +-------------------- - 1 + 1 --- 1 row(s) selected. >>select cast(1 as decimal(10) unsigned) from (values(0))x; @@ -9031,7 +9031,7 @@ Hello (EXPR) (EXPR) (EXPR) ------------- ------------- -------------------------- -10:13:14.0000 10:13:14.0000 2016-06-11 16:52:59.057377 +10:13:14.0000 10:13:14.0000 2016-07-10 05:23:22.454801 --- 1 row(s) selected. >>--test on precision of time http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/executor/TEST012 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/TEST012 b/core/sql/regress/executor/TEST012 index 814adf0..c5e734e 100755 --- a/core/sql/regress/executor/TEST012 +++ b/core/sql/regress/executor/TEST012 @@ -438,12 +438,15 @@ select converttobits(a), converttobits(b), converttobits(c), converttobits(d), converttobits(m), converttobits(n), converttobits(o) from t012t3; -select bitextract(3, 15, 1) from (values(1)) x(a); -select bitextract(3, 14, 1) from (values(1)) x(a); -select bitextract(3, 14, 2) from (values(1)) x(a); -select bitextract(3, 0, 1) from (values(1)) x(a); -select bitextract(3, 0, 15) from (values(1)) x(a); -select bitextract(3, 0, 16) from (values(1)) x(a); +select bitextract(cast(3 as smallint), 15, 1) from (values(1)) x(a); +select bitextract(cast(3 as smallint), 14, 1) from (values(1)) x(a); +select bitextract(cast(3 as smallint), 14, 2) from (values(1)) x(a); +select bitextract(cast(3 as smallint), 0, 1) from (values(1)) x(a); +select bitextract(cast(3 as smallint), 0, 15) from (values(1)) x(a); +select bitextract(cast(3 as smallint), 0, 16) from (values(1)) x(a); +select bitextract(3, 0, 8) from (values(1)) x(a); +select bitextract(3, 7, 1) from (values(1)) x(a); +select bitextract(3, 8, 1) from (values(1)) x(a); select a from t012t3 where a = bitand(a,a); select b from t012t3 where b = bitor(b,b); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/hive/EXPECTED005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED005 b/core/sql/regress/hive/EXPECTED005 index 8026bc1..75a64aa 100644 --- a/core/sql/regress/hive/EXPECTED005 +++ b/core/sql/regress/hive/EXPECTED005 @@ -834,7 +834,7 @@ t005part.a t005part.b t005part.c >>invoke hive.hive.thive_insert_smallint; -- Definition of hive table THIVE_INSERT_SMALLINT --- Definition current Sat Jul 2 07:06:59 2016 +-- Definition current Fri Jul 8 09:45:59 2016 ( A SMALLINT @@ -939,7 +939,7 @@ A >>invoke hive.hive.thive_insert_varchar; -- Definition of hive table THIVE_INSERT_VARCHAR --- Definition current Sat Jul 2 07:07:07 2016 +-- Definition current Fri Jul 8 09:46:08 2016 ( A VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/hive/EXPECTED015 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED015 b/core/sql/regress/hive/EXPECTED015 index 162a3bc..1ede641 100644 --- a/core/sql/regress/hive/EXPECTED015 +++ b/core/sql/regress/hive/EXPECTED015 @@ -210,9 +210,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.T015T2 Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.T015T2 Task: PREPARATION Status: Started Object: TRAFODION.HBASE.T015T2 Rows Processed: 5 -Task: PREPARATION Status: Ended ET: 00:00:00.051 +Task: PREPARATION Status: Ended ET: 00:00:00.083 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.T015T2 -Task: COMPLETION Status: Ended ET: 00:00:00.781 +Task: COMPLETION Status: Ended ET: 00:00:00.344 --- 5 row(s) loaded. >> @@ -234,9 +234,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.T015T2 Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.T015T2 Task: PREPARATION Status: Started Object: TRAFODION.HBASE.T015T2 Rows Processed: 5 -Task: PREPARATION Status: Ended ET: 00:00:00.162 +Task: PREPARATION Status: Ended ET: 00:00:00.068 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.T015T2 -Task: COMPLETION Status: Ended ET: 00:00:00.183 +Task: COMPLETION Status: Ended ET: 00:00:00.151 --- 5 row(s) loaded. >> @@ -259,9 +259,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.T015T2 Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.T015T2 Task: PREPARATION Status: Started Object: TRAFODION.HBASE.T015T2 Rows Processed: 5 -Task: PREPARATION Status: Ended ET: 00:00:00.064 +Task: PREPARATION Status: Ended ET: 00:00:00.075 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.T015T2 -Task: COMPLETION Status: Ended ET: 00:00:00.327 +Task: COMPLETION Status: Ended ET: 00:00:00.275 --- 5 row(s) loaded. >> @@ -289,9 +289,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.T015T2 Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.T015T2 Task: PREPARATION Status: Started Object: TRAFODION.HBASE.T015T2 Rows Processed: 5 -Task: PREPARATION Status: Ended ET: 00:00:00.164 +Task: PREPARATION Status: Ended ET: 00:00:00.068 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.T015T2 -Task: COMPLETION Status: Ended ET: 00:00:00.268 +Task: COMPLETION Status: Ended ET: 00:00:00.279 --- 5 row(s) loaded. >> @@ -378,8 +378,8 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD --- SQL operation complete. >>log; sort_key ............... (HashDistPartHash(cast(TRAFODION.HBASE.T015T1.A)) - Hash2Distrib cast(2)), TRAFODION.HBASE.T015T1.A - + Hash2Distrib cast(cast(2))), + TRAFODION.HBASE.T015T1.A >>prepare s5 from load transform into t015t5 select a,a,a from t015t1 <<+ >>cardinality 10e0 >> ; --- SQL command prepared. @@ -462,9 +462,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.CUSTOMER_ADDRE Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.CUSTOMER_ADDRESS Task: PREPARATION Status: Started Object: TRAFODION.HBASE.CUSTOMER_ADDRESS Rows Processed: 5000 -Task: PREPARATION Status: Ended ET: 00:00:08.885 +Task: PREPARATION Status: Ended ET: 00:00:07.833 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.CUSTOMER_ADDRESS -Task: COMPLETION Status: Ended ET: 00:00:01.379 +Task: COMPLETION Status: Ended ET: 00:00:00.608 --- 5000 row(s) loaded. >> @@ -544,9 +544,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.CUSTOMER_ADDRE Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.CUSTOMER_ADDRESS_NOPK Task: PREPARATION Status: Started Object: TRAFODION.HBASE.CUSTOMER_ADDRESS_NOPK Rows Processed: 5000 -Task: PREPARATION Status: Ended ET: 00:00:01.054 +Task: PREPARATION Status: Ended ET: 00:00:00.953 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.CUSTOMER_ADDRESS_NOPK -Task: COMPLETION Status: Ended ET: 00:00:00.820 +Task: COMPLETION Status: Ended ET: 00:00:00.399 --- 5000 row(s) loaded. >> @@ -645,9 +645,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOG Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS Task: PREPARATION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS Rows Processed: 5000 -Task: PREPARATION Status: Ended ET: 00:00:03.594 +Task: PREPARATION Status: Ended ET: 00:00:03.825 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS -Task: COMPLETION Status: Ended ET: 00:00:00.323 +Task: COMPLETION Status: Ended ET: 00:00:00.594 --- 5000 row(s) loaded. >> @@ -734,12 +734,12 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD ESP_EXCHANGE ============================== SEQ_NO 6 ONLY CHILD 5 child_partitioning_func range partitioned 4 ways on ((HashDistPartHash(cast(HIVE.CUSTOMER_DEMOGRAPHICS - .CD_DEMO_SK)) Hash2Distrib cast(4)), + .CD_DEMO_SK)) Hash2Distrib cast(cast(4))), HIVE.HIVE.CUSTOMER_DEMOGRAPHICS.CD_DEMO_SK) with ESP_EXCHANGE ============================== SEQ_NO 2 ONLY CHILD 1 parent_partitioning_fun range partitioned 4 ways on ((HashDistPartHash(cast(HIVE.CUSTOMER_DEMOGRAPHICS - .CD_DEMO_SK)) Hash2Distrib cast(4)), + .CD_DEMO_SK)) Hash2Distrib cast(cast(4))), HIVE.HIVE.CUSTOMER_DEMOGRAPHICS.CD_DEMO_SK) with >> >>load into customer_demographics_salt @@ -749,9 +749,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOG Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT Task: PREPARATION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT Rows Processed: 5000 -Task: PREPARATION Status: Ended ET: 00:00:05.596 +Task: PREPARATION Status: Ended ET: 00:00:06.638 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT -Task: COMPLETION Status: Ended ET: 00:00:01.203 +Task: COMPLETION Status: Ended ET: 00:00:01.913 --- 5000 row(s) loaded. >> @@ -1261,9 +1261,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOG Task: CLEANUP Status: Ended Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS Task: PREPARATION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS Rows Processed: 5000 -Task: PREPARATION Status: Ended ET: 00:00:04.891 +Task: PREPARATION Status: Ended ET: 00:00:06.553 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS -Task: COMPLETION Status: Ended ET: 00:00:02.090 +Task: COMPLETION Status: Ended ET: 00:00:01.562 --- 5000 row(s) loaded. >> @@ -1304,11 +1304,11 @@ Task: DISABLE INDEXE Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOG Task: DISABLE INDEXE Status: Ended Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS Task: PREPARATION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS Rows Processed: 1000 -Task: PREPARATION Status: Ended ET: 00:00:04.588 +Task: PREPARATION Status: Ended ET: 00:00:03.781 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS -Task: COMPLETION Status: Ended ET: 00:00:01.075 +Task: COMPLETION Status: Ended ET: 00:00:01.479 Task: POPULATE INDEX Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS -Task: POPULATE INDEX Status: Ended ET: 00:00:11.865 +Task: POPULATE INDEX Status: Ended ET: 00:00:13.703 --- 1000 row(s) loaded. >> @@ -1353,11 +1353,11 @@ Task: DISABLE INDEXE Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOG Task: DISABLE INDEXE Status: Ended Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT Task: PREPARATION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT Rows Processed: 5000 -Task: PREPARATION Status: Ended ET: 00:00:05.812 +Task: PREPARATION Status: Ended ET: 00:00:05.478 Task: COMPLETION Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT -Task: COMPLETION Status: Ended ET: 00:00:02.106 +Task: COMPLETION Status: Ended ET: 00:00:02.063 Task: POPULATE INDEX Status: Started Object: TRAFODION.HBASE.CUSTOMER_DEMOGRAPHICS_SALT -Task: POPULATE INDEX Status: Ended ET: 00:00:12.813 +Task: POPULATE INDEX Status: Ended ET: 00:00:15.035 --- 5000 row(s) loaded. >> @@ -1531,9 +1531,9 @@ Task: CLEANUP Status: Started Object: TRAFODION.HBASE."customer_addr Task: CLEANUP Status: Ended Object: TRAFODION.HBASE."customer_address_delim" Task: PREPARATION Status: Started Object: TRAFODION.HBASE."customer_address_delim" Rows Processed: 5000 -Task: PREPARATION Status: Ended ET: 00:00:03.585 +Task: PREPARATION Status: Ended ET: 00:00:03.841 Task: COMPLETION Status: Started Object: TRAFODION.HBASE."customer_address_delim" -Task: COMPLETION Status: Ended ET: 00:00:01.575 +Task: COMPLETION Status: Ended ET: 00:00:01.934 --- 5000 row(s) loaded. >> http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d48a0c94/core/sql/regress/seabase/EXPECTED003 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED003 b/core/sql/regress/seabase/EXPECTED003 index d46cdb8..79347c9 100644 --- a/core/sql/regress/seabase/EXPECTED003 +++ b/core/sql/regress/seabase/EXPECTED003 @@ -10,6 +10,10 @@ --- SQL operation complete. >> >>obey TEST003(setup_tiny); +>>----------------------------------------------------------- +>>-------------- TINYINT datatype --------------------------- +>>----------------------------------------------------------- +>> >>create table t003t1(a tinyint not null primary key, b tinyint, +> c tinyint unsigned default 10 not null, d tinyint unsigned); @@ -17,7 +21,7 @@ >>invoke t003t1; -- Definition of Trafodion table TRAFODION.SCH.T003T1 --- Definition current Sat Jun 25 16:24:14 2016 +-- Definition current Sun Jul 10 06:36:25 2016 ( A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -36,7 +40,7 @@ >>invoke t003t1_like; -- Definition of Trafodion table TRAFODION.SCH.T003T1_LIKE --- Definition current Sat Jun 25 16:24:23 2016 +-- Definition current Sun Jul 10 06:36:36 2016 ( A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -55,7 +59,7 @@ >>invoke t003t1_as; -- Definition of Trafodion table TRAFODION.SCH.T003T1_AS --- Definition current Sat Jun 25 16:24:27 2016 +-- Definition current Sun Jul 10 06:36:41 2016 ( A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -301,7 +305,7 @@ A B C D >>invoke hive.hive.ttiny; -- Definition of hive table TTINY --- Definition current Sat Jun 25 16:25:08 2016 +-- Definition current Sun Jul 10 06:37:34 2016 ( A TINYINT @@ -446,7 +450,7 @@ A B >>invoke t003t2; -- Definition of Trafodion table TRAFODION.SCH.T003T2 --- Definition current Sat Jun 25 16:25:17 2016 +-- Definition current Sun Jul 10 06:37:44 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -463,7 +467,7 @@ A B >>invoke t003t2_like; -- Definition of Trafodion table TRAFODION.SCH.T003T2_LIKE --- Definition current Sat Jun 25 16:25:22 2016 +-- Definition current Sun Jul 10 06:37:48 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -480,7 +484,7 @@ A B >>invoke t003t2_as; -- Definition of Trafodion table TRAFODION.SCH.T003T2_AS --- Definition current Sat Jun 25 16:25:25 2016 +-- Definition current Sun Jul 10 06:37:53 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -726,13 +730,13 @@ A B --- 0 row(s) inserted. >>insert into t003t2 values (-1, 1); -*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. +*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:LARGEINT(MBIN64S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW. --- 0 row(s) inserted. >> >>select cast(-1 as largeint unsigned) from (values(1)) x(a); -*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. +*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:LARGEINT(MBIN64S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW. --- 0 row(s) selected. >>select cast(18446744073709551616 as largeint unsigned) from (values(1)) x(a); @@ -774,7 +778,7 @@ A B >>invoke t003t2; -- Definition of Trafodion table TRAFODION.SCH.T003T2 --- Definition current Sat Jun 25 16:25:51 2016 +-- Definition current Sun Jul 10 06:38:21 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -791,7 +795,7 @@ A B >>invoke t003t2_like; -- Definition of Trafodion table TRAFODION.SCH.T003T2_LIKE --- Definition current Sat Jun 25 16:25:55 2016 +-- Definition current Sun Jul 10 06:38:25 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -808,7 +812,7 @@ A B >>invoke t003t2_as; -- Definition of Trafodion table TRAFODION.SCH.T003T2_AS --- Definition current Sat Jun 25 16:25:58 2016 +-- Definition current Sun Jul 10 06:38:28 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -1054,13 +1058,13 @@ A B --- 0 row(s) inserted. >>insert into t003t2 values (-1, 1); -*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. +*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:LARGEINT(MBIN64S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW. --- 0 row(s) inserted. >> >>select cast(-1 as largeint unsigned) from (values(1)) x(a); -*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. +*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:LARGEINT(MBIN64S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW. --- 0 row(s) selected. >>select cast(18446744073709551616 as largeint unsigned) from (values(1)) x(a); @@ -1105,7 +1109,7 @@ A B >>invoke t003t2; -- Definition of Trafodion table TRAFODION.SCH.T003T2 --- Definition current Sat Jun 25 16:26:23 2016 +-- Definition current Sun Jul 10 06:38:55 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -1122,7 +1126,7 @@ A B >>invoke t003t2_like; -- Definition of Trafodion table TRAFODION.SCH.T003T2_LIKE --- Definition current Sat Jun 25 16:26:28 2016 +-- Definition current Sun Jul 10 06:38:59 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -1139,7 +1143,7 @@ A B >>invoke t003t2_as; -- Definition of Trafodion table TRAFODION.SCH.T003T2_AS --- Definition current Sat Jun 25 16:26:31 2016 +-- Definition current Sun Jul 10 06:39:03 2016 ( A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT @@ -1385,13 +1389,13 @@ A B --- 0 row(s) inserted. >>insert into t003t2 values (-1, 1); -*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. +*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:LARGEINT(MBIN64S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW. --- 0 row(s) inserted. >> >>select cast(-1 as largeint unsigned) from (values(1)) x(a); -*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. +*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:LARGEINT(MBIN64S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW. --- 0 row(s) selected. >>select cast(18446744073709551616 as largeint unsigned) from (values(1)) x(a); @@ -1411,4 +1415,570 @@ A B >> >> >> +>>cqd traf_boolean_io 'ON'; + +--- SQL operation complete. +>>obey TEST003(setup_bool); +>>----------------------------------------------------------- +>>-------------- BOOLEAN datatype --------------------------- +>>----------------------------------------------------------- +>> +>>create table if not exists t003t3(a boolean not null primary key, b boolean) ++> attribute aligned format; + +--- SQL operation complete. +>>invoke t003t3; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3 +-- Definition current Sun Jul 10 06:39:10 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , B BOOLEAN DEFAULT NULL + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>create table if not exists t003t3_like like t003t3; + +--- SQL operation complete. +>>invoke t003t3_like; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3_LIKE +-- Definition current Sun Jul 10 06:39:14 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , B BOOLEAN DEFAULT NULL + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>create table if not exists t003t3_as primary key (a) as select * from t003t3; + +--- 0 row(s) inserted. +>>invoke t003t3_as; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3_AS +-- Definition current Sun Jul 10 06:39:18 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , B BOOLEAN DEFAULT NULL + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>create table if not exists t003t3_salt(a boolean not null primary key) ++> salt using 2 partitions; + +--- SQL operation complete. +>>invoke t003t3_salt; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3_SALT +-- Definition current Sun Jul 10 06:39:20 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , "_SALT_" INT UNSIGNED NO DEFAULT NOT NULL NOT + DROPPABLE + ) + PRIMARY KEY ("_SALT_" ASC, A ASC) + +--- SQL operation complete. +>> +>>obey TEST003(dml_bool); +>>insert into t003t3 values (true, true); + +--- 1 row(s) inserted. +>>insert into t003t3 values (false, false); + +--- 1 row(s) inserted. +>> +>>select * from t003t3; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>>insert into t003t3_as select * from t003t3; + +--- 2 row(s) inserted. +>>select * from t003t3_as; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>>select * from t003t3 where a = true; + +A B +----- ----- + +TRUE TRUE + +--- 1 row(s) selected. +>>select * from t003t3 where a = false; + +A B +----- ----- + +FALSE FALSE + +--- 1 row(s) selected. +>>select * from t003t3 where b = true; + +A B +----- ----- + +TRUE TRUE + +--- 1 row(s) selected. +>>select * from t003t3 where b = false; + +A B +----- ----- + +FALSE FALSE + +--- 1 row(s) selected. +>>select * from t003t3 where a != true; + +A B +----- ----- + +FALSE FALSE + +--- 1 row(s) selected. +>>select * from t003t3 where a != false; + +A B +----- ----- + +TRUE TRUE + +--- 1 row(s) selected. +>>select * from t003t3 where b != true; + +A B +----- ----- + +FALSE FALSE + +--- 1 row(s) selected. +>>select * from t003t3 where b != false; + +A B +----- ----- + +TRUE TRUE + +--- 1 row(s) selected. +>>select * from t003t3 where a is null; + +--- 0 row(s) selected. +>>select * from t003t3 where b is not null; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>>select * from t003t3 where a = true or a = false; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>>select * from t003t3 where b = true or b = false; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>>select cast('true' as boolean) from (values(1)) x(a); + +(EXPR) +------ + +TRUE + +--- 1 row(s) selected. +>>select cast(' faLse ' as boolean) from (values(1)) x(a); + +(EXPR) +------ + +FALSE + +--- 1 row(s) selected. +>> +>>select cast(true as boolean not null) from (values(1)) x(a); + +(EXPR) +------ + +TRUE + +--- 1 row(s) selected. +>>select cast(false as boolean) from (values(1)) x(a); + +(EXPR) +------ + +FALSE + +--- 1 row(s) selected. +>> +>>select cast(a as char(10)), cast (b as varchar(11)) from t003t3; + +(EXPR) (EXPR) +---------- ----------- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>>delete from t003t3; + +--- 2 row(s) deleted. +>>prepare s from insert into t003t3 values (?, ?); + +--- SQL command prepared. +>>execute s using true, true; + +--- 1 row(s) inserted. +>>execute s using 'false', ' falSE '; + +--- 1 row(s) inserted. +>>select * from t003t3; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>>select !a, !b from t003t3; + +(EXPR) (EXPR) +------ ------ + +TRUE TRUE +FALSE FALSE + +--- 2 row(s) selected. +>>select * from t003t3 where !a = false; + +A B +----- ----- + +TRUE TRUE + +--- 1 row(s) selected. +>>update t003t3 set b = !b; + +--- 2 row(s) updated. +>>select * from t003t3; + +A B +----- ----- + +FALSE TRUE +TRUE FALSE + +--- 2 row(s) selected. +>> +>>begin work; + +--- SQL operation complete. +>>delete from t003t3 where b = false; + +--- 1 row(s) deleted. +>>select * from t003t3; + +A B +----- ----- + +FALSE TRUE + +--- 1 row(s) selected. +>>rollback work; + +--- SQL operation complete. +>>select * from t003t3; + +A B +----- ----- + +FALSE TRUE +TRUE FALSE + +--- 2 row(s) selected. +>> +>>begin work; + +--- SQL operation complete. +>>update t003t3 set b = false where b != false; + +--- 1 row(s) updated. +>>select * from t003t3; + +A B +----- ----- + +FALSE FALSE +TRUE FALSE + +--- 2 row(s) selected. +>>rollback work; + +--- SQL operation complete. +>>select * from t003t3; + +A B +----- ----- + +FALSE TRUE +TRUE FALSE + +--- 2 row(s) selected. +>> +>>obey TEST003(errors_bool); +>>update t003t3 set b = b + 1; + +*** ERROR[4034] The operation (BOOLEAN + NUMERIC(1)) is not allowed. + +*** ERROR[8822] The statement was not prepared. + +>> +>>delete from t003t3; + +--- 2 row(s) deleted. +>> +>>insert into t003t3 values ('true', false); + +*** ERROR[4039] Column A is of type BOOLEAN, incompatible with the value's type, CHAR(4). + +*** ERROR[8822] The statement was not prepared. + +>>insert into t003t3 values (1, true); + +*** ERROR[4039] Column A is of type BOOLEAN, incompatible with the value's type, NUMERIC(1). + +*** ERROR[8822] The statement was not prepared. + +>> +>>select cast(' truee' as boolean) from (values(1)) x(a); + +*** ERROR[8426] The provided value, TRUEE, is an invalid BOOLEAN value. + +--- 0 row(s) selected. +>> +>>prepare s from insert into t003t3 values (?, ?); + +--- SQL command prepared. +>>execute s using 1, 2; + +*** ERROR[8426] The provided value, 2, is an invalid BOOLEAN value. + +*** ERROR[15015] PARAM ?(UNNAMED_2) (value 2) cannot be converted to type BOOLEAN. + +--- 0 row(s) inserted. +>> +>>obey TEST003(hive_bool); +>>sh echo "drop table tbool;" > TEST003_junk; +>>sh regrhive.ksh -f TEST003_junk; +>> +>>sh echo "create table tbool(a boolean);" > TEST003_junk; +>>sh regrhive.ksh -f TEST003_junk; +>> +>>sh echo "insert into tbool values (true), (false), (NULL);" > TEST003_junk; +>>sh regrhive.ksh -f TEST003_junk; +>> +>>invoke hive.hive.tbool; + +-- Definition of hive table TBOOL +-- Definition current Sun Jul 10 06:40:13 2016 + + ( + A BOOLEAN + ) + /* stored as textfile */ + +--- SQL operation complete. +>>select * from hive.hive.tbool; + +A +----- + +TRUE +FALSE +? + +--- 3 row(s) selected. +>>insert into hive.hive.tbool values (false), (true), (null); + +--- 3 row(s) inserted. +>>select * from hive.hive.tbool; + +A +----- + +TRUE +FALSE +? +FALSE +TRUE +? + +--- 6 row(s) selected. +>>insert overwrite table hive.hive.tbool select a from t003t3; + +--- 0 row(s) inserted. +>>select * from hive.hive.tbool; + +--- 0 row(s) selected. +>> +>> +>>cqd traf_boolean_io 'OFF'; + +--- SQL operation complete. +>>obey TEST003(setup_bool); +>>----------------------------------------------------------- +>>-------------- BOOLEAN datatype --------------------------- +>>----------------------------------------------------------- +>> +>>create table if not exists t003t3(a boolean not null primary key, b boolean) ++> attribute aligned format; + +--- SQL operation complete. +>>invoke t003t3; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3 +-- Definition current Sun Jul 10 06:40:19 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , B BOOLEAN DEFAULT NULL + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>create table if not exists t003t3_like like t003t3; + +--- SQL operation complete. +>>invoke t003t3_like; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3_LIKE +-- Definition current Sun Jul 10 06:40:20 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , B BOOLEAN DEFAULT NULL + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>create table if not exists t003t3_as primary key (a) as select * from t003t3; + +--- 0 row(s) inserted. +>>invoke t003t3_as; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3_AS +-- Definition current Sun Jul 10 06:40:22 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , B BOOLEAN DEFAULT NULL + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>create table if not exists t003t3_salt(a boolean not null primary key) ++> salt using 2 partitions; + +--- SQL operation complete. +>>invoke t003t3_salt; + +-- Definition of Trafodion table TRAFODION.SCH.T003T3_SALT +-- Definition current Sun Jul 10 06:40:23 2016 + + ( + A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE + , "_SALT_" INT UNSIGNED NO DEFAULT NOT NULL NOT + DROPPABLE + ) + PRIMARY KEY ("_SALT_" ASC, A ASC) + +--- SQL operation complete. +>> +>>obey TEST003(dml_bool_short); +>>insert into t003t3 values (true, true); + +--- 1 row(s) inserted. +>>insert into t003t3 values (false, false); + +--- 1 row(s) inserted. +>> +>>select * from t003t3; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>>delete from t003t3; + +--- 2 row(s) deleted. +>>prepare s from insert into t003t3 values (cast(? as boolean), cast(? as boolean)); + +--- SQL command prepared. +>>execute s using true, true; + +--- 1 row(s) inserted. +>>execute s using 'false', 'falSE'; + +--- 1 row(s) inserted. +>>select * from t003t3; + +A B +----- ----- + +FALSE FALSE +TRUE TRUE + +--- 2 row(s) selected. +>> +>> >>log;
