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