| >> |
| >>cqd traf_tinyint_support 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd traf_tinyint_return_values 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd traf_tinyint_input_params 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(setup_tiny); |
| >>----------------------------------------------------------- |
| >>-------------- TINYINT datatype --------------------------- |
| >>----------------------------------------------------------- |
| >> |
| >>drop table if exists t003t1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t1_like; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t1_as; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t1(a tinyint not null primary key, b tinyint, |
| +> c tinyint unsigned default 10 not null, d tinyint unsigned); |
| |
| --- SQL operation complete. |
| >>invoke t003t1; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T1 |
| -- Definition current Wed Feb 22 23:04:05 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED DEFAULT 10 NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t1_like like t003t1; |
| |
| --- SQL operation complete. |
| >>invoke t003t1_like; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T1_LIKE |
| -- Definition current Wed Feb 22 23:04:11 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED DEFAULT 10 NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t1_as primary key (a) as select * from t003t1; |
| |
| --- 0 row(s) inserted. |
| >>invoke t003t1_as; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T1_AS |
| -- Definition current Wed Feb 22 23:04:16 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create view t003t1_view as select * from t003t1; |
| |
| --- SQL operation complete. |
| >>invoke t003t1_view; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T003T1_VIEW |
| -- Definition current Wed Feb 22 23:04:19 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(dml_tiny); |
| >>insert into t003t1 values (1, 2, 3, 4); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t1 values (-1, -2, 255, 255); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t1 values (-128, 127, 0, 0); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select * from t003t1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>insert into t003t1_as select * from t003t1; |
| |
| --- 3 row(s) inserted. |
| >>select * from t003t1_as; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>select * from t003t1 where a = 1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| 1 2 3 4 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t1 where a = -1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -1 -2 255 255 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t1 where a < 1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t1 where a <= -1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t1 where a < 1000; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where a < 100000; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where a > -1000; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where a > -100000; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>select * from t003t1 where d = 4; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| 1 2 3 4 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t1 where d < -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t1 where d < 1000; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where d > -1000; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>select a+10 from t003t1 where a = 1 or a = -1; |
| |
| (EXPR) |
| ------ |
| |
| 9 |
| 11 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select cast(100 as tinyint unsigned) from (values(1)) x(a); |
| |
| (EXPR) |
| ------ |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>select cast(-100 as tinyint) from (values(1)) x(a); |
| |
| (EXPR) |
| ------ |
| |
| -100 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(a as char(10)), cast (b as varchar(11)) from t003t1; |
| |
| (EXPR) (EXPR) |
| ---------- ----------- |
| |
| -128 127 |
| -1 -2 |
| 1 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>prepare s from insert into t003t1 values (?, ?, ?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using -3, 10, 251, 0; |
| |
| --- 1 row(s) inserted. |
| >>select * from t003t1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -3 10 251 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 4 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>delete from t003t1 where b = -2; |
| |
| --- 1 row(s) deleted. |
| >>select * from t003t1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -3 10 251 0 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>update t003t1 set b = b + 1 where b <> 127; |
| |
| --- 3 row(s) updated. |
| >>select * from t003t1; |
| |
| A B C D |
| ---- ---- --- --- |
| |
| -128 127 0 0 |
| -3 11 251 0 |
| -1 -1 255 255 |
| 1 3 3 4 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(hive_tiny); |
| >>process hive statement 'drop table ttiny'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table ttiny(a tinyint, b tinyint)'; |
| |
| --- SQL operation complete. |
| >>sh echo "insert into ttiny values (1, -1);" > TEST003_junk; |
| >>sh regrhive.ksh -f TEST003_junk; |
| >> |
| >>invoke hive.hive.ttiny; |
| |
| -- Definition of hive table HIVE.HIVE.TTINY |
| -- Definition current Wed Feb 22 23:04:49 2017 |
| |
| ( |
| A TINYINT |
| , B TINYINT |
| ) |
| /* stored as textfile */ |
| |
| --- SQL operation complete. |
| >>select * from hive.hive.ttiny; |
| |
| A B |
| ---- ---- |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >>insert into hive.hive.ttiny values (127, 10), (-128, -50); |
| |
| --- 2 row(s) inserted. |
| >>select * from hive.hive.ttiny; |
| |
| A B |
| ---- ---- |
| |
| 1 -1 |
| 127 10 |
| -128 -50 |
| |
| --- 3 row(s) selected. |
| >>insert overwrite table hive.hive.ttiny select a, b from t003t1; |
| |
| --- 4 row(s) inserted. |
| >>select * from hive.hive.ttiny; |
| |
| A B |
| ---- ---- |
| |
| -128 127 |
| -3 10 |
| -1 -2 |
| 1 2 |
| |
| --- 4 row(s) selected. |
| >> |
| >>obey TEST003(errors_tiny); |
| >>update t003t1 set b = b + 1; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) updated. |
| >>select cast(1.0 as numeric(1,1) not null) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:TINYINT SIGNED(MBIN8S) Source Value:10 Target Type:LARGEINT(IBIN64S) Max Target Value:9. Instruction:RANGE_HIGH_S8S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) selected. |
| >> |
| >>delete from t003t1; |
| |
| --- 4 row(s) deleted. |
| >> |
| >>insert into t003t1 values (128, 2, 3, 4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t1 values (2, -129, 3, 4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-129 Target Type:LARGEINT(IBIN64S) Min Target Value:-128. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t1 values (3, 4, 256, 4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:256 Target Type:LARGEINT(IBIN64S) Max Target Value:255. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t1 values (4, 4, 256, -4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:256 Target Type:LARGEINT(IBIN64S) Max Target Value:255. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>select cast(-1 as tinyint unsigned) from (values(1)) x(a); |
| |
| *** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. |
| |
| --- 0 row(s) selected. |
| >>select cast(256 as tinyint unsigned) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:256 Target Type:LARGEINT(IBIN64S) Max Target Value:255. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) selected. |
| >>select cast(-129 as tinyint) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-129 Target Type:LARGEINT(IBIN64S) Min Target Value:-128. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. |
| |
| --- 0 row(s) selected. |
| >>select cast(128 as tinyint) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) selected. |
| >> |
| >>prepare s from insert into t003t1 values (?, ?, ?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 128, 2, 3, 4; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII) Source Value:0x313238 to Target Type:TINYINT SIGNED(REC_BIN8_SIGNED). |
| |
| *** ERROR[15015] PARAM ?(UNNAMED_1) (value 128) cannot be converted to type TINYINT. |
| |
| --- 0 row(s) inserted. |
| >>execute s using 4, 4, 256, -4; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII) Source Value:0x323536 to Target Type:TINYINT UNSIGNED(REC_BIN8_UNSIGNED). |
| |
| *** ERROR[15015] PARAM ?(UNNAMED_3) (value 256) cannot be converted to type TINYINT UNSIGNED. |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII) Source Value:0x2D34 to Target Type:TINYINT UNSIGNED(REC_BIN8_UNSIGNED). |
| |
| *** ERROR[15015] PARAM ?(UNNAMED_4) (value -4) cannot be converted to type TINYINT UNSIGNED. |
| |
| --- 0 row(s) inserted. |
| >> |
| >> |
| >> |
| >>cqd traf_tinyint_support 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd traf_tinyint_return_values 'OFF'; |
| |
| --- SQL operation complete. |
| >>cqd traf_tinyint_input_params 'OFF'; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(setup_tiny); |
| >>----------------------------------------------------------- |
| >>-------------- TINYINT datatype --------------------------- |
| >>----------------------------------------------------------- |
| >> |
| >>drop table if exists t003t1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t1_like; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t1_as; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t1(a tinyint not null primary key, b tinyint, |
| +> c tinyint unsigned default 10 not null, d tinyint unsigned); |
| |
| --- SQL operation complete. |
| >>invoke t003t1; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T1 |
| -- Definition current Wed Feb 22 23:05:19 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED DEFAULT 10 NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t1_like like t003t1; |
| |
| --- SQL operation complete. |
| >>invoke t003t1_like; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T1_LIKE |
| -- Definition current Wed Feb 22 23:05:25 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED DEFAULT 10 NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t1_as primary key (a) as select * from t003t1; |
| |
| --- 0 row(s) inserted. |
| >>invoke t003t1_as; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T1_AS |
| -- Definition current Wed Feb 22 23:05:30 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create view t003t1_view as select * from t003t1; |
| |
| --- SQL operation complete. |
| >>invoke t003t1_view; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T003T1_VIEW |
| -- Definition current Wed Feb 22 23:05:32 2017 |
| |
| ( |
| A TINYINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B TINYINT DEFAULT NULL |
| , C TINYINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , D TINYINT UNSIGNED DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(dml_tiny); |
| >>insert into t003t1 values (1, 2, 3, 4); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t1 values (-1, -2, 255, 255); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t1 values (-128, 127, 0, 0); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select * from t003t1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>insert into t003t1_as select * from t003t1; |
| |
| --- 3 row(s) inserted. |
| >>select * from t003t1_as; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>select * from t003t1 where a = 1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| 1 2 3 4 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t1 where a = -1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -1 -2 255 255 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t1 where a < 1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t1 where a <= -1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t1 where a < 1000; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where a < 100000; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where a > -1000; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where a > -100000; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>select * from t003t1 where d = 4; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| 1 2 3 4 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t1 where d < -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t1 where d < 1000; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>select * from t003t1 where d > -1000; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >> |
| >>select a+10 from t003t1 where a = 1 or a = -1; |
| |
| (EXPR) |
| ------ |
| |
| 9 |
| 11 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select cast(100 as tinyint unsigned) from (values(1)) x(a); |
| |
| (EXPR) |
| ------ |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >>select cast(-100 as tinyint) from (values(1)) x(a); |
| |
| (EXPR) |
| ------ |
| |
| -100 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(a as char(10)), cast (b as varchar(11)) from t003t1; |
| |
| (EXPR) (EXPR) |
| ---------- ----------- |
| |
| -128 127 |
| -1 -2 |
| 1 2 |
| |
| --- 3 row(s) selected. |
| >> |
| >>prepare s from insert into t003t1 values (?, ?, ?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using -3, 10, 251, 0; |
| |
| --- 1 row(s) inserted. |
| >>select * from t003t1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -3 10 251 0 |
| -1 -2 255 255 |
| 1 2 3 4 |
| |
| --- 4 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>delete from t003t1 where b = -2; |
| |
| --- 1 row(s) deleted. |
| >>select * from t003t1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -3 10 251 0 |
| 1 2 3 4 |
| |
| --- 3 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>update t003t1 set b = b + 1 where b <> 127; |
| |
| --- 3 row(s) updated. |
| >>select * from t003t1; |
| |
| A B C D |
| ------ ------ ----- ----- |
| |
| -128 127 0 0 |
| -3 11 251 0 |
| -1 -1 255 255 |
| 1 3 3 4 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(hive_tiny); |
| >>process hive statement 'drop table ttiny'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table ttiny(a tinyint, b tinyint)'; |
| |
| --- SQL operation complete. |
| >>sh echo "insert into ttiny values (1, -1);" > TEST003_junk; |
| >>sh regrhive.ksh -f TEST003_junk; |
| >> |
| >>invoke hive.hive.ttiny; |
| |
| -- Definition of hive table HIVE.HIVE.TTINY |
| -- Definition current Wed Feb 22 23:05:57 2017 |
| |
| ( |
| A TINYINT |
| , B TINYINT |
| ) |
| /* stored as textfile */ |
| |
| --- SQL operation complete. |
| >>select * from hive.hive.ttiny; |
| |
| A B |
| ------ ------ |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >>insert into hive.hive.ttiny values (127, 10), (-128, -50); |
| |
| --- 2 row(s) inserted. |
| >>select * from hive.hive.ttiny; |
| |
| A B |
| ------ ------ |
| |
| 1 -1 |
| 127 10 |
| -128 -50 |
| |
| --- 3 row(s) selected. |
| >>insert overwrite table hive.hive.ttiny select a, b from t003t1; |
| |
| --- 4 row(s) inserted. |
| >>select * from hive.hive.ttiny; |
| |
| A B |
| ------ ------ |
| |
| -128 127 |
| -3 10 |
| -1 -2 |
| 1 2 |
| |
| --- 4 row(s) selected. |
| >> |
| >>obey TEST003(errors_tiny); |
| >>update t003t1 set b = b + 1; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) updated. |
| >>select cast(1.0 as numeric(1,1) not null) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:TINYINT SIGNED(MBIN8S) Source Value:10 Target Type:LARGEINT(IBIN64S) Max Target Value:9. Instruction:RANGE_HIGH_S8S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) selected. |
| >> |
| >>delete from t003t1; |
| |
| --- 4 row(s) deleted. |
| >> |
| >>insert into t003t1 values (128, 2, 3, 4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t1 values (2, -129, 3, 4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-129 Target Type:LARGEINT(IBIN64S) Min Target Value:-128. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t1 values (3, 4, 256, 4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:256 Target Type:LARGEINT(IBIN64S) Max Target Value:255. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t1 values (4, 4, 256, -4); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:256 Target Type:LARGEINT(IBIN64S) Max Target Value:255. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>select cast(-1 as tinyint unsigned) from (values(1)) x(a); |
| |
| *** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. |
| |
| --- 0 row(s) selected. |
| >>select cast(256 as tinyint unsigned) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:256 Target Type:LARGEINT(IBIN64S) Max Target Value:255. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) selected. |
| >>select cast(-129 as tinyint) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-129 Target Type:LARGEINT(IBIN64S) Min Target Value:-128. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. |
| |
| --- 0 row(s) selected. |
| >>select cast(128 as tinyint) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) selected. |
| >> |
| >>prepare s from insert into t003t1 values (?, ?, ?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 128, 2, 3, 4; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >>execute s using 4, 4, 256, -4; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII) Source Value:0x2D34 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED). |
| |
| *** ERROR[15015] PARAM ?(UNNAMED_4) (value -4) cannot be converted to type SMALLINT UNSIGNED. |
| |
| --- 0 row(s) inserted. |
| >> |
| >> |
| >> |
| >>cqd traf_largeint_unsigned_io 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd traf_create_signed_numeric_literal 'ON'; |
| |
| --- SQL operation complete. |
| >>obey TEST003(setup_lu); |
| >>drop table if exists t003t2 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t2_like; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t2_as; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2(a largeint unsigned not null primary key, b largeint unsigned); |
| |
| --- SQL operation complete. |
| >>invoke t003t2; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2 |
| -- Definition current Wed Feb 22 23:06:12 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2_like like t003t2; |
| |
| --- SQL operation complete. |
| >>invoke t003t2_like; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2_LIKE |
| -- Definition current Wed Feb 22 23:06:18 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2_as primary key(a) as select * from t003t2; |
| |
| --- 0 row(s) inserted. |
| >>invoke t003t2_as; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2_AS |
| -- Definition current Wed Feb 22 23:06:22 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create view t003t2_view as select * from t003t2; |
| |
| --- SQL operation complete. |
| >>invoke t003t2_view; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T003T2_VIEW |
| -- Definition current Wed Feb 22 23:06:25 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(dml_lu); |
| >>insert into t003t2 values (1, 2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t2 values (18446744073709551615, 18446744073709551615); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>insert into t003t2_as select * from t003t2; |
| |
| --- 2 row(s) inserted. |
| >>select * from t003t2_as; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select * from t003t2 where a = 1; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where a = -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a < 1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a <= -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a < 1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where a > -1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t2 where a = 18446744073709551615; |
| |
| A B |
| -------------------- -------------------- |
| |
| 18446744073709551615 18446744073709551615 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select * from t003t2 where b = 2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where b < -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where b < 1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where b > -1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t2 where b = 18446744073709551615; |
| |
| A B |
| -------------------- -------------------- |
| |
| 18446744073709551615 18446744073709551615 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select a, cast(cast(a as varchar(40)) as largeint unsigned) from t003t2; |
| |
| A (EXPR) |
| -------------------- -------------------- |
| |
| 1 1 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select cast('-9223372036854775808' as largeint) from (values(1)) x(a); |
| |
| (EXPR) |
| -------------------- |
| |
| -9223372036854775808 |
| |
| --- 1 row(s) selected. |
| >>select cast('-9223372036854775809' as largeint) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,20 BYTES,ISO88591) Source Value:-9223372036854775809 to Target Type:LARGEINT(REC_BIN64_SIGNED). |
| |
| --- 0 row(s) selected. |
| >> |
| >>select a+10 from t003t2 where a = 1 or a = -1; |
| |
| (EXPR) |
| -------------------- |
| |
| 11 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(100 as largeint unsigned) from (values(1)) x(a); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(a as char(21)), cast (b as varchar(22)) from t003t2; |
| |
| (EXPR) (EXPR) |
| --------------------- ---------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare s from insert into t003t2 values (?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 251, 0; |
| |
| --- 1 row(s) inserted. |
| >>execute s using 11, 18446744073709551615; |
| |
| --- 1 row(s) inserted. |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>delete from t003t2 where b = 1; |
| |
| --- 0 row(s) deleted. |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>update t003t2 set b = b + 1 where b = 2; |
| |
| --- 1 row(s) updated. |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 3 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(errors_lu); |
| >>update t003t2 set b = b + 1; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) updated. |
| >> |
| >>delete from t003t2; |
| |
| --- 4 row(s) deleted. |
| >> |
| >>insert into t003t2 values (18446744073709551616, 2); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t2 values (2, 18446744073709551616); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t2 values (-1, 1); |
| |
| *** 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. 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); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) selected. |
| >> |
| >>prepare s from insert into t003t2 values (?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 18446744073709551616, 2; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,20 BYTES,ISO88591) Source Value:18446744073709551616 to Target Type:LARGEINT(REC_BIN64_SIGNED). |
| |
| *** ERROR[15015] PARAM ?(UNNAMED_1) (value 18446744073709551616) cannot be converted to type LARGEINT UNSIGNED. |
| |
| --- 0 row(s) inserted. |
| >> |
| >> |
| >> |
| >>cqd traf_create_signed_numeric_literal 'OFF'; |
| |
| --- SQL operation complete. |
| >>obey TEST003(setup_lu); |
| >>drop table if exists t003t2 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t2_like; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t2_as; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2(a largeint unsigned not null primary key, b largeint unsigned); |
| |
| --- SQL operation complete. |
| >>invoke t003t2; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2 |
| -- Definition current Wed Feb 22 23:06:51 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2_like like t003t2; |
| |
| --- SQL operation complete. |
| >>invoke t003t2_like; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2_LIKE |
| -- Definition current Wed Feb 22 23:06:57 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2_as primary key(a) as select * from t003t2; |
| |
| --- 0 row(s) inserted. |
| >>invoke t003t2_as; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2_AS |
| -- Definition current Wed Feb 22 23:07:03 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create view t003t2_view as select * from t003t2; |
| |
| --- SQL operation complete. |
| >>invoke t003t2_view; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T003T2_VIEW |
| -- Definition current Wed Feb 22 23:07:06 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(dml_lu); |
| >>insert into t003t2 values (1, 2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t2 values (18446744073709551615, 18446744073709551615); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>insert into t003t2_as select * from t003t2; |
| |
| --- 2 row(s) inserted. |
| >>select * from t003t2_as; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select * from t003t2 where a = 1; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where a = -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a < 1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a <= -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a < 1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where a > -1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t2 where a = 18446744073709551615; |
| |
| A B |
| -------------------- -------------------- |
| |
| 18446744073709551615 18446744073709551615 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select * from t003t2 where b = 2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where b < -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where b < 1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where b > -1000; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t2 where b = 18446744073709551615; |
| |
| A B |
| -------------------- -------------------- |
| |
| 18446744073709551615 18446744073709551615 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select a, cast(cast(a as varchar(40)) as largeint unsigned) from t003t2; |
| |
| A (EXPR) |
| -------------------- -------------------- |
| |
| 1 1 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select cast('-9223372036854775808' as largeint) from (values(1)) x(a); |
| |
| (EXPR) |
| -------------------- |
| |
| -9223372036854775808 |
| |
| --- 1 row(s) selected. |
| >>select cast('-9223372036854775809' as largeint) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,20 BYTES,ISO88591) Source Value:-9223372036854775809 to Target Type:LARGEINT(REC_BIN64_SIGNED). |
| |
| --- 0 row(s) selected. |
| >> |
| >>select a+10 from t003t2 where a = 1 or a = -1; |
| |
| (EXPR) |
| -------------------- |
| |
| 11 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(100 as largeint unsigned) from (values(1)) x(a); |
| |
| (EXPR) |
| -------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(a as char(21)), cast (b as varchar(22)) from t003t2; |
| |
| (EXPR) (EXPR) |
| --------------------- ---------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare s from insert into t003t2 values (?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 251, 0; |
| |
| --- 1 row(s) inserted. |
| >>execute s using 11, 18446744073709551615; |
| |
| --- 1 row(s) inserted. |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>delete from t003t2 where b = 1; |
| |
| --- 0 row(s) deleted. |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 2 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>update t003t2 set b = b + 1 where b = 2; |
| |
| --- 1 row(s) updated. |
| >>select * from t003t2; |
| |
| A B |
| -------------------- -------------------- |
| |
| 1 3 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(errors_lu); |
| >>update t003t2 set b = b + 1; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) updated. |
| >> |
| >>delete from t003t2; |
| |
| --- 4 row(s) deleted. |
| >> |
| >>insert into t003t2 values (18446744073709551616, 2); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t2 values (2, 18446744073709551616); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t2 values (-1, 1); |
| |
| *** 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. 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); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) selected. |
| >> |
| >>prepare s from insert into t003t2 values (?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 18446744073709551616, 2; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,20 BYTES,ISO88591) Source Value:18446744073709551616 to Target Type:LARGEINT(REC_BIN64_SIGNED). |
| |
| *** ERROR[15015] PARAM ?(UNNAMED_1) (value 18446744073709551616) cannot be converted to type LARGEINT UNSIGNED. |
| |
| --- 0 row(s) inserted. |
| >> |
| >> |
| >> |
| >>cqd traf_create_signed_numeric_literal 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd traf_largeint_unsigned_io 'OFF'; |
| |
| --- SQL operation complete. |
| >>obey TEST003(setup_lu); |
| >>drop table if exists t003t2 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t2_like; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t2_as; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2(a largeint unsigned not null primary key, b largeint unsigned); |
| |
| --- SQL operation complete. |
| >>invoke t003t2; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2 |
| -- Definition current Wed Feb 22 23:07:37 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2_like like t003t2; |
| |
| --- SQL operation complete. |
| >>invoke t003t2_like; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2_LIKE |
| -- Definition current Wed Feb 22 23:07:42 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create table t003t2_as primary key(a) as select * from t003t2; |
| |
| --- 0 row(s) inserted. |
| >>invoke t003t2_as; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T003T2_AS |
| -- Definition current Wed Feb 22 23:07:47 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>create view t003t2_view as select * from t003t2; |
| |
| --- SQL operation complete. |
| >>invoke t003t2_view; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T003T2_VIEW |
| -- Definition current Wed Feb 22 23:07:50 2017 |
| |
| ( |
| A LARGEINT UNSIGNED NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , B LARGEINT UNSIGNED DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(dml_lu); |
| >>insert into t003t2 values (1, 2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t2 values (18446744073709551615, 18446744073709551615); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select * from t003t2; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>insert into t003t2_as select * from t003t2; |
| |
| --- 2 row(s) inserted. |
| >>select * from t003t2_as; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select * from t003t2 where a = 1; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where a = -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a < 1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a <= -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where a < 1000; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where a > -1000; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t2 where a = 18446744073709551615; |
| |
| A B |
| --------------------- --------------------- |
| |
| 18446744073709551615 18446744073709551615 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select * from t003t2 where b = 2; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where b < -1; |
| |
| --- 0 row(s) selected. |
| >>select * from t003t2 where b < 1000; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>select * from t003t2 where b > -1000; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >>select * from t003t2 where b = 18446744073709551615; |
| |
| A B |
| --------------------- --------------------- |
| |
| 18446744073709551615 18446744073709551615 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select a, cast(cast(a as varchar(40)) as largeint unsigned) from t003t2; |
| |
| A (EXPR) |
| --------------------- --------------------- |
| |
| 1 1 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select cast('-9223372036854775808' as largeint) from (values(1)) x(a); |
| |
| (EXPR) |
| -------------------- |
| |
| -9223372036854775808 |
| |
| --- 1 row(s) selected. |
| >>select cast('-9223372036854775809' as largeint) from (values(1)) x(a); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,20 BYTES,ISO88591) Source Value:-9223372036854775809 to Target Type:LARGEINT(REC_BIN64_SIGNED). |
| |
| --- 0 row(s) selected. |
| >> |
| >>select a+10 from t003t2 where a = 1 or a = -1; |
| |
| (EXPR) |
| -------------------- |
| |
| 11 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(100 as largeint unsigned) from (values(1)) x(a); |
| |
| (EXPR) |
| --------------------- |
| |
| 100 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select cast(a as char(21)), cast (b as varchar(22)) from t003t2; |
| |
| (EXPR) (EXPR) |
| --------------------- ---------------------- |
| |
| 1 2 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare s from insert into t003t2 values (?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 251, 0; |
| |
| --- 1 row(s) inserted. |
| >>execute s using 11, 18446744073709551615; |
| |
| --- 1 row(s) inserted. |
| >>select * from t003t2; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>delete from t003t2 where b = 1; |
| |
| --- 0 row(s) deleted. |
| >>select * from t003t2; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 2 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>begin work; |
| |
| --- SQL operation complete. |
| >>update t003t2 set b = b + 1 where b = 2; |
| |
| --- 1 row(s) updated. |
| >>select * from t003t2; |
| |
| A B |
| --------------------- --------------------- |
| |
| 1 3 |
| 11 18446744073709551615 |
| 251 0 |
| 18446744073709551615 18446744073709551615 |
| |
| --- 4 row(s) selected. |
| >>rollback work; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST003(errors_lu); |
| >>update t003t2 set b = b + 1; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) updated. |
| >> |
| >>delete from t003t2; |
| |
| --- 4 row(s) deleted. |
| >> |
| >>insert into t003t2 values (18446744073709551616, 2); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t2 values (2, 18446744073709551616); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) inserted. |
| >>insert into t003t2 values (-1, 1); |
| |
| *** 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. 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); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) selected. |
| >> |
| >>prepare s from insert into t003t2 values (?, ?); |
| |
| --- SQL command prepared. |
| >>execute s using 18446744073709551616, 2; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00000000000000000100 to Target Type:LARGEINT UNSIGNED(REC_BIN64_UNSIGNED). |
| |
| --- 0 row(s) inserted. |
| >> |
| >> |
| >> |
| >>cqd traf_boolean_io 'ON'; |
| |
| --- SQL operation complete. |
| >>obey TEST003(setup_bool); |
| >>----------------------------------------------------------- |
| >>-------------- BOOLEAN datatype --------------------------- |
| >>----------------------------------------------------------- |
| >> |
| >>drop table if exists t003t3 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t3_like; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t3_as; |
| |
| --- SQL operation complete. |
| >> |
| >>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 Wed Feb 22 23:08:05 2017 |
| |
| ( |
| 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 Wed Feb 22 23:08:11 2017 |
| |
| ( |
| 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 Wed Feb 22 23:08:16 2017 |
| |
| ( |
| 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 Wed Feb 22 23:08:19 2017 |
| |
| ( |
| 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. |
| >> |
| >>create view t003t3_view as select * from t003t3; |
| |
| --- SQL operation complete. |
| >>invoke t003t3_view; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T003T3_VIEW |
| -- Definition current Wed Feb 22 23:08:22 2017 |
| |
| ( |
| A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE |
| , B BOOLEAN DEFAULT NULL |
| ) |
| |
| --- 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); |
| |
| --- 1 row(s) inserted. |
| >>insert into t003t3 values (1, true); |
| |
| *** ERROR[4035] Type NUMERIC(1) cannot be cast to type BOOLEAN. |
| |
| *** 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); |
| >>process hive statement 'drop table tbool'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table tbool(a boolean)'; |
| |
| --- SQL operation complete. |
| >>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 HIVE.HIVE.TBOOL |
| -- Definition current Wed Feb 22 23:08:45 2017 |
| |
| ( |
| 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; |
| |
| --- 1 row(s) inserted. |
| >>select * from hive.hive.tbool; |
| |
| A |
| ----- |
| |
| TRUE |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>cqd traf_boolean_io 'OFF'; |
| |
| --- SQL operation complete. |
| >>obey TEST003(setup_bool); |
| >>----------------------------------------------------------- |
| >>-------------- BOOLEAN datatype --------------------------- |
| >>----------------------------------------------------------- |
| >> |
| >>drop table if exists t003t3 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t3_like; |
| |
| --- SQL operation complete. |
| >>drop table if exists t003t3_as; |
| |
| --- SQL operation complete. |
| >> |
| >>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 Wed Feb 22 23:09:08 2017 |
| |
| ( |
| 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 Wed Feb 22 23:09:15 2017 |
| |
| ( |
| 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 Wed Feb 22 23:09:20 2017 |
| |
| ( |
| 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 Wed Feb 22 23:09:21 2017 |
| |
| ( |
| 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. |
| >> |
| >>create view t003t3_view as select * from t003t3; |
| |
| --- SQL operation complete. |
| >>invoke t003t3_view; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T003T3_VIEW |
| -- Definition current Wed Feb 22 23:09:23 2017 |
| |
| ( |
| A BOOLEAN NO DEFAULT NOT NULL NOT DROPPABLE |
| , B BOOLEAN DEFAULT NULL |
| ) |
| |
| --- 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; |
