>>
>>set schema hive.hive;

--- SQL operation complete.
>>
>>set terminal_charset utf8;
>>
>>cqd AUTO_QUERY_RETRY_WARNINGS 'ON';

--- SQL operation complete.
>>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '25' ;

--- SQL operation complete.
>>cqd mode_seahive 'ON';

--- SQL operation complete.
>>cqd HIST_ROWCOUNT_REQUIRING_STATS '50000';

--- SQL operation complete.
>>
>>------------------------------------------------------------
>>-- Testing query plan invalidation in the compiler, but
>>-- not the executor. Perform DML/DDL operations on a
>>-- table and try re-executing the old plan as well as
>>-- getting a query cache hit and updating the changed
>>-- Hive and HDFS metadata
>>------------------------------------------------------------
>>prepare s1 from 
+>  select c_preferred_cust_flag,
+>         count(*) 
+>  from customer_ddl 
+>  group by 1 
+>  order by 1
+>  ;

--- SQL command prepared.
>>execute s1;

--- 0 row(s) selected.
>>-- expect 0 rows
>>
>>prepare s1part from 
+>  -- selecting part col not supported right now
+>  select --c_preferred_cust_flag,
+>         count(*) 
+>  from customer_bp 
+>  --group by 1 
+>  --order by 1
+>  ;

--- SQL command prepared.
>>execute s1part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>-- expect 0 rows
>>
>>-- insert some data and add one more partition
>>sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_b.hive.sql;
>>
>>-- query cache hit, no validation at all
>>  select c_preferred_cust_flag,
+>         count(*) 
+>  from customer_ddl 
+>  group by 1 
+>  order by 1
+>  ;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149450876, failedModTS = 1529149578260, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl

C_PREFERRED_CUST_FLAG      (EXPR)              
-------------------------  --------------------

                                            685
N                                          9789
Y                                          9525

--- 3 row(s) selected.
>>
>>-- vary query to avoid query cache hit
>>prepare s2 from 
+>  select c_preferred_cust_flag,
+>         count(c_customer_sk) 
+>  from customer_ddl 
+>  group by 1 
+>  order by 1
+>  ;

--- SQL command prepared.
>>
>>prepare s2part from
+>  -- selecting part col not supported right now
+>  select --c_preferred_cust_flag,
+>         count(*) 
+>  from customer_bp 
+>  --group by 1 
+>  --order by 1
+>  ;

--- SQL command prepared.
>>execute s1;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149450876, failedModTS = 1529149578260, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl

C_PREFERRED_CUST_FLAG      (EXPR)              
-------------------------  --------------------

                                            685
N                                          9789
Y                                          9525

--- 3 row(s) selected.
>>-- because we don't invalidate in the executor,
>>-- this should still return 0 rows
>>
>>execute s2;

C_PREFERRED_CUST_FLAG      (EXPR)              
-------------------------  --------------------

                                            685
N                                          9789
Y                                          9525

--- 3 row(s) selected.
>>-- should get an NATable cache
>>-- hit, we should notice the change in the table
>>-- and return the correct result
>>
>>execute s1part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>-- because we don't invalidate in the executor,
>>-- this should still return 0 rows
>>
>>execute s2part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>-- although this should get an NATable cache
>>-- hit, we should notice the change in the table
>>-- and return the correct result
>>
>>insert into customer_temp 
+>select * from customer 
+>where c_customer_sk between 20000 and 39999;

*** WARNING[6008] Statistics for column (C_CUSTOMER_SK) from table HIVE.HIVE.CUSTOMER were not available. As a result, the access path chosen might not be the best possible.

--- 20000 row(s) inserted.
>>
>>select * from newtable;

--- 0 row(s) selected.
>>-- no rows, but should know the new table
>>insert into newtable values ('abc');

--- 1 row(s) inserted.
>>select * from newtable;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149581595, failedModTS = 1529149591176, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable

A                        
-------------------------

abc                      

--- 1 row(s) selected.
>>-- expect to see the row, but only because query cache is off
>>
>>insert into hiveregr5.newtable2 values ('xyz');

--- 1 row(s) inserted.
>>select * from hiveregr5.newtable2;

A                        
-------------------------

xyz                      

--- 1 row(s) selected.
>>
>>-- add a second partition to customer_bp
>>sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_c.hive.sql;
>>-- add more files to customer_ddl
>>sh regrhadoop.ksh dfs -cp /user/trafodion/hive/exttables/customer_temp/* /user/trafodion/hive/exttables/customer_ddl;
>>
>>-- no query cache hit, but NATable cache hit
>>prepare s3 from 
+>  select count(*) 
+>  from customer_ddl 
+>  ;

--- SQL command prepared.
>>
>>-- no query cache hit, but NATable cache hit
>>prepare s3part from
+>  -- selecting part col not supported right now
+>  select --c_preferred_cust_flag,
+>         count(c_customer_id) 
+>  from customer_bp 
+>  --group by 1 
+>  --order by 1
+>  ;

--- SQL command prepared.
>>execute s1;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149578260, failedModTS = 1529149609405, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl

C_PREFERRED_CUST_FLAG      (EXPR)              
-------------------------  --------------------

                                           1384
N                                         19631
Y                                         18984

--- 3 row(s) selected.
>>-- s1 should still return 0 rows - for now
>>execute s2;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149578260, failedModTS = 1529149609405, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl

C_PREFERRED_CUST_FLAG      (EXPR)              
-------------------------  --------------------

                                           1384
N                                         19631
Y                                         18984

--- 3 row(s) selected.
>>execute s3;

(EXPR)              
--------------------

               39999

--- 1 row(s) selected.
>>execute s1part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>-- s1 should still return 0 rows - for now
>>execute s2part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>execute s3part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>
>>select a,b from newtable;

--- 0 row(s) selected.
>>-- should return 0 rows
>>
>>insert into newtable values (1, 'def');

--- 1 row(s) inserted.
>>select a,b from newtable;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149606436, failedModTS = 1529149616936, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable

A            B                        
-----------  -------------------------

          1  def                      

--- 1 row(s) selected.
>>
>>-- overwrite the table with auto-generated partitions
>>sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_d.hive.sql;
>>
>>prepare s4 from 
+>  select c_preferred_cust_flag,
+>         count(*) 
+>  from customer_ddl 
+>  group by 1 
+>  order by 1
+>  ;

--- SQL command prepared.
>>prepare s4part from
+>  -- selecting part col not supported right now
+>  select --c_preferred_cust_flag,
+>         count(*) 
+>  from customer_bp 
+>  --group by 1 
+>  --order by 1
+>  ;

--- SQL command prepared.
>>execute s2;

C_PREFERRED_CUST_FLAG      (EXPR)              
-------------------------  --------------------

                                           1384
N                                         19631
Y                                         18984

--- 3 row(s) selected.
>>execute s4;

C_PREFERRED_CUST_FLAG      (EXPR)              
-------------------------  --------------------

                                           1384
N                                         19631
Y                                         18984

--- 3 row(s) selected.
>>execute s2part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>execute s4part;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>select count(*) from tbl_utf8;

(EXPR)              
--------------------

                  75

--- 1 row(s) selected.
>>select * from tbl_utf8 where id between 8 and 12;

ID           CHAPTER                    ENGLISH                    TRANSLATOR
-----------  -------------------------  -------------------------  -------------------------

          8  Shin fukatoku 心不可  The Mind Cannot Be Got     (Bielefeldt)             
          9  Kobutsushin 古佛心      The Old Buddha Mind        (Bielefeldt)             
         10  Daigo 大悟               Great Awakening            (Bielefeldt)             
         11  Zazen gi 坐禪儀         Principles of Zazen        (Bielefeldt)             
         12  Zazen shin 坐禪箴       Lancet of Zazen            (Bielefeldt)             

--- 5 row(s) selected.
>>select * from tbl_utf8 where chapter like '%三%';

ID           CHAPTER                    ENGLISH                    TRANSLATOR
-----------  -------------------------  -------------------------  -------------------------

         13  Kaiin zanmai 海印三昧  The Ocean Seal Samadhi     (Bielefeldt & Radich)    
         41  Sangai yuishin 三界唯  The Three Realms Are Only  (Weinstein)              
         66  Zanmai ō zanmai 三昧  The King of Samadhis Sama  (Bielefeldt)             
         69  Jishō zanmai 自證三  The Samadhi of Self Verif  (Bielefeldt)             

--- 4 row(s) selected.
>>select * from tbl_utf8 where chapter like '%海印_昧%';

ID           CHAPTER                    ENGLISH                    TRANSLATOR
-----------  -------------------------  -------------------------  -------------------------

         13  Kaiin zanmai 海印三昧  The Ocean Seal Samadhi     (Bielefeldt & Radich)    

--- 1 row(s) selected.
>>
>>insert into tbl_utf8_temp 
+>select * from tbl_utf8;

--- 75 row(s) inserted.
>>
>>select count(*) from tbl_utf8_temp;

(EXPR)              
--------------------

                  75

--- 1 row(s) selected.
>>select * from tbl_utf8_temp where id between 8 and 12;

ID           CHAPTER                    ENGLISH                    TRANSLATOR
-----------  -------------------------  -------------------------  -------------------------

          8  Shin fukatoku 心不可  The Mind Cannot Be Got     (Bielefeldt)             
          9  Kobutsushin 古佛心      The Old Buddha Mind        (Bielefeldt)             
         10  Daigo 大悟               Great Awakening            (Bielefeldt)             
         11  Zazen gi 坐禪儀         Principles of Zazen        (Bielefeldt)             
         12  Zazen shin 坐禪箴       Lancet of Zazen            (Bielefeldt)             

--- 5 row(s) selected.
>>select * from tbl_utf8_temp where chapter like '%海印_昧%';

ID           CHAPTER                    ENGLISH                    TRANSLATOR
-----------  -------------------------  -------------------------  -------------------------

         13  Kaiin zanmai 海印三昧  The Ocean Seal Samadhi     (Bielefeldt & Radich)    

--- 1 row(s) selected.
>>
>>select count(*) from tbl_utf8p;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>select * from tbl_utf8p where id between 8 and 12;

--- 0 row(s) selected.
>>select * from tbl_utf8p where chapter like '%海印_昧%';

--- 0 row(s) selected.
>>
>>select * from tbl_type;

TINT  SM      I            BIG                   STR                        F                D                          T                              DT          VC                                        D10                   D18           D181                  D30
----  ------  -----------  --------------------  -------------------------  ---------------  -------------------------  -----------------------------  ----------  ----------------------------------------  --------------------  ------------  --------------------  -------------------------------

 101     202          203                   204  two hundred                 2.0000000E+002   2.00000000000000000E+002  2022-02-02 22:22:22.222222000  2022-02-02  varchar                                             1234567890     123456.11                 12345    11111111111111111111111111111

--- 1 row(s) selected.
>>insert into tbl_type_temp select * from tbl_type;

--- 1 row(s) inserted.
>>select * from tbl_type_temp;

TINT  SM      I            BIG                   STR                        F                D                          T                              DT          VC                                        D10                   D18           D181                  D30
----  ------  -----------  --------------------  -------------------------  ---------------  -------------------------  -----------------------------  ----------  ----------------------------------------  --------------------  ------------  --------------------  -------------------------------

 101     202          203                   204  two hundred                 2.0000000E+002   2.00000000000000000E+002  2022-02-02 22:22:22.222222000  2022-02-02  varchar                                             1234567890     123456.11                 12345    11111111111111111111111111111

--- 1 row(s) selected.
>>
>>
>>cqd HIVE_FILE_CHARSET 'GBK';

--- SQL operation complete.
>>select c1, CONVERTTOHEX(c2) from tbl_gbk;

C1           (EXPR)                                            
-----------  --------------------------------------------------

          3  EC8B90EC978E                                      
          5  EC8B90EC978E                                      
          2  EC8B90EC978E                                      
          4  EC8B90EC978E                                      
          6  EC8B90EC978E                                      
          7  EC8B90EC978E                                      
          8  EC8B90EC978E                                      
          3  ECBB93EB9F8FECAB97EB9B91                          
          2  ECBB93EB9F8FECAB97EB9B91                          
          6  ECBB93EB9F8FECAB97EB9B91                          
         19  ECBB93EB9F8FECAB97EB9B91                          
          8  ECBB93EB9F8FECAB97EB9B91                          

--- 12 row(s) selected.
>>cqd HIVE_FILE_CHARSET reset;

--- SQL operation complete.
>>
>>cqd HIVE_SCAN_SPECIAL_MODE '1';

--- SQL operation complete.
>>select * from tbl_dos;

C1           C2           C3           C4                       
-----------  -----------  -----------  -------------------------

          0        39478         8147  2008-07-17               
          1        21944         8327  2005-05-12               
          2        32730         9999  2000-11-05               
          3        19653         5727  2005-06-24               
          4        67794         6012  2008-07-01               
          5        93265         5823  2012-06-26               
          6        28219          909  2009-04-26               
          7        23967         8290  2006-02-21               
          8        24265         8663  2006-10-06               
          9        70273         3363  2001-03-17               

--- 10 row(s) selected.
>>cqd HIVE_SCAN_SPECIAL_MODE reset;

--- SQL operation complete.
>>drop table if exists trafodion.seabase.tbl_dos_num;

--- SQL operation complete.
>>create table trafodion.seabase.tbl_dos_num (c1 int, c2 int);

--- SQL operation complete.
>>load with NO OUTPUT into trafodion.seabase.tbl_dos_num select * from tbl_dos_num;

*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 33393437380d

--- 0 row(s) loaded.
>>cqd HIVE_SCAN_SPECIAL_MODE '1';

--- SQL operation complete.
>>load with no output into trafodion.seabase.tbl_dos_num select * from tbl_dos_num;

--- 10 row(s) loaded.
>>select * from trafodion.seabase.tbl_dos_num;

C1           C2         
-----------  -----------

          0        39478
          1        21944
          2        32730
          3        19653
          4        67794
          5        93265
          6            ?
          7            ?
          8            ?
          9           17

--- 10 row(s) selected.
>>cqd HIVE_SCAN_SPECIAL_MODE reset;

--- SQL operation complete.
>>select * from tbl_bad;

*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61

--- 0 row(s) selected.
>>cqd HIVE_SCAN_SPECIAL_MODE '2';

--- SQL operation complete.
>>select * from tbl_bad;

C1           C2                    C3                         C4               C5      C6                             C7                         C8
-----------  --------------------  -------------------------  ---------------  ------  -----------------------------  -------------------------  ----

          ?                     ?  c                                        ?       ?  ?                                                      ?     ?
          ?                     ?  c                                        ?       ?  2017-01-01 10:10:10.000000000   1.01000000000000000E+000     1
          ?                     ?                                           ?       ?  ?                                                      ?     ?
          1                     1  averylongstring            -1.0000000E+000       0  2017-01-01 10:10:10.000000000   1.00010000000000000E+002     1
          2                     2  good                        1.1000000E+000       2  2017-01-01 10:10:10.000000000   2.00000000000000000E+002   100
          3                     3  good                        1.0000000E+000       2  2017-01-01 10:10:10.000000000   2.10000000000000000E+002    10
          ?            4294967295  good                        3.3999999E+038       ?  2017-01-01 10:10:10.000000000   1.69999999999999968E+308    10
          0            9999999999  bad                                      ?       ?  ?                                                      ?     ?

--- 8 row(s) selected.
>>cqd HIVE_SCAN_SPECIAL_MODE reset;

--- SQL operation complete.
>>drop table if exists trafodion.seabase.traf_tbl_bad;

--- SQL operation complete.
>>create table trafodion.seabase.traf_tbl_bad (
+>c1 int,
+>c2 largeint,
+>c3 varchar(25),
+>c4 real,
+>c5 smallint,
+>c6 timestamp(6),
+>c7 float(54),
+>c8 smallint
+>);

--- SQL operation complete.
>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad;

*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61

--- 0 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>load with continue on error into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task:  LOAD            Status: Started    Object: TRAFODION.SEABASE.TRAF_TBL_BAD
Task:  CLEANUP         Status: Started    Time: 2018-06-16 11:47:50.919693
Task:  CLEANUP         Status: Ended      Time: 2018-06-16 11:47:50.927679
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.008
Task:  LOADING DATA    Status: Started    Time: 2018-06-16 11:47:50.927713
       Rows Processed: 8 
       Error Rows:     5 
Task:  LOADING DATA    Status: Ended      Time: 2018-06-16 11:47:51.85803
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:00.158
Task:  COMPLETION      Status: Started    Time: 2018-06-16 11:47:51.85854
       Rows Loaded:    3 
Task:  COMPLETION      Status: Ended      Time: 2018-06-16 11:47:52.476978
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:01.391

--- 3 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;

(EXPR)              
--------------------

                   3

--- 1 row(s) selected.
>>delete from trafodion.seabase.traf_tbl_bad ;

--- 3 row(s) deleted.
>>load with log error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task:  LOAD            Status: Started    Object: TRAFODION.SEABASE.TRAF_TBL_BAD
Task:  CLEANUP         Status: Started    Time: 2018-06-16 11:47:53.526469
Task:  CLEANUP         Status: Ended      Time: 2018-06-16 11:47:53.536440
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.010
       Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180616_114753
Task:  LOADING DATA    Status: Started    Time: 2018-06-16 11:47:53.536510
       Rows Processed: 8 
       Error Rows:     5 
Task:  LOADING DATA    Status: Ended      Time: 2018-06-16 11:47:53.760748
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:00.224
Task:  COMPLETION      Status: Started    Time: 2018-06-16 11:47:53.760793
       Rows Loaded:    3 
Task:  COMPLETION      Status: Ended      Time: 2018-06-16 11:47:56.167112
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:02.406

--- 3 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;

(EXPR)              
--------------------

                   3

--- 1 row(s) selected.
>>load with log error rows to '/user/trafodion/bulkload/logs/TEST005' into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task:  LOAD            Status: Started    Object: TRAFODION.SEABASE.TRAF_TBL_BAD
Task:  CLEANUP         Status: Started    Time: 2018-06-16 11:47:57.157930
Task:  CLEANUP         Status: Ended      Time: 2018-06-16 11:47:57.165444
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.008
       Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180616_114757
Task:  LOADING DATA    Status: Started    Time: 2018-06-16 11:47:57.165499
       Rows Processed: 8 
       Error Rows:     5 
Task:  LOADING DATA    Status: Ended      Time: 2018-06-16 11:47:57.352294
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:00.187
Task:  COMPLETION      Status: Started    Time: 2018-06-16 11:47:57.352327
       Rows Loaded:    3 
Task:  COMPLETION      Status: Ended      Time: 2018-06-16 11:47:57.916072
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.564

--- 3 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;

(EXPR)              
--------------------

                   6

--- 1 row(s) selected.
>>delete from trafodion.seabase.traf_tbl_bad ;

--- 6 row(s) deleted.
>>load with stop after 3 error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task:  LOAD            Status: Started    Object: TRAFODION.SEABASE.TRAF_TBL_BAD
Task:  CLEANUP         Status: Started    Time: 2018-06-16 11:47:58.929152
Task:  CLEANUP         Status: Ended      Time: 2018-06-16 11:47:58.937786
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.009
Task:  LOADING DATA    Status: Started    Time: 2018-06-16 11:47:58.937815

*** ERROR[8113] The maximum number of error rows is exceeded.

--- 0 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>load with log error rows, stop after 3 error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task:  LOAD            Status: Started    Object: TRAFODION.SEABASE.TRAF_TBL_BAD
Task:  CLEANUP         Status: Started    Time: 2018-06-16 11:48:00.37617
Task:  CLEANUP         Status: Ended      Time: 2018-06-16 11:48:00.45699
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.008
       Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180616_114800
Task:  LOADING DATA    Status: Started    Time: 2018-06-16 11:48:00.45732

*** ERROR[8113] The maximum number of error rows is exceeded.

--- 0 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;

*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61

--- 0 row(s) inserted.
>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;

*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61

--- 0 row(s) inserted.
>>cqd HIVE_SCAN_SPECIAL_MODE '2';

--- SQL operation complete.
>>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad;

--- 8 row(s) loaded.
>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;

--- 8 row(s) inserted.
>>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;

--- 8 row(s) inserted.
>>cqd HIVE_SCAN_SPECIAL_MODE reset;

--- SQL operation complete.
>>drop table if exists trafodion.seabase.traf_tbl_bad;

--- SQL operation complete.
>>create table trafodion.seabase.traf_tbl_bad (
+>c1 int not null,
+>c2 largeint not null,
+>c3 varchar(25),
+>c4 real,
+>c5 smallint,
+>c6 timestamp(6) not null,
+>c7 float(54) not null,
+>c8 smallint
+>);

--- SQL operation complete.
>>cqd HIVE_SCAN_SPECIAL_MODE '2';

--- SQL operation complete.
>>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad;

*** ERROR[8421] NULL cannot be assigned to a NOT NULL column.

--- 0 row(s) inserted.
>>cqd HIVE_SCAN_SPECIAL_MODE reset;

--- SQL operation complete.
>>
>>-- tests for hive timestamp mismatch check
>>cqd auto_query_retry_warnings 'ON';

--- SQL operation complete.
>>
>>drop table if exists hive.hive.thive;

--- SQL operation complete.
>>create table hive.hive.thive(a int);

--- SQL operation complete.
>>
>>select a from hive.hive.thive;

--- 0 row(s) selected.
>>
>>--process hive statement 'insert into thive values (1)';
>>insert into hive.hive.thive values (1);

--- 1 row(s) inserted.
>>
>>select a from hive.hive.thive;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149699813, failedModTS = 1529149706160, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive

A          
-----------

          1

--- 1 row(s) selected.
>>insert into hive.hive.thive values (2);

--- 1 row(s) inserted.
>>select a from hive.hive.thive;

A          
-----------

          1
          2

--- 2 row(s) selected.
>>
>>drop table hive.hive.thive;

--- SQL operation complete.
>>create table hive.hive.thive(a int, b int);

--- SQL operation complete.
>>
>>--process hive statement 'insert into thive values (1,2)';
>>insert into hive.hive.thive values (1,2);

--- 1 row(s) inserted.
>>
>>select a from hive.hive.thive;

A          
-----------

          1

--- 1 row(s) selected.
>>
>>select * from hive.hive.thive;

A            B          
-----------  -----------

          1            2

--- 1 row(s) selected.
>>
>>-- truncate of regular hive table
>>truncate table hive.hive.thive;

--- SQL operation complete.
>>select * from hive.hive.thive;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149722198, failedModTS = 1529149723553, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive

--- 0 row(s) selected.
>>insert into hive.hive.thive values (10, 20);

--- 1 row(s) inserted.
>>select * from hive.hive.thive;

A            B          
-----------  -----------

         10           20

--- 1 row(s) selected.
>>truncate table hive.hive.thive;

--- SQL operation complete.
>>truncate table hive.hive.thive;

--- SQL operation complete.
>>select * from hive.hive.thive;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149724776, failedModTS = 1529149727335, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive

--- 0 row(s) selected.
>>
>>-- truncate of partitioned hive table
>>-- When partitioned hive tables are supported, then inserts and selects below
>>-- can be executed directly from traf instead of through hive shell.
>>drop table if exists hive.hive.t005part;

--- SQL operation complete.
>>create table hive.hive.t005part(a int) partitioned by (b int, c int);

--- SQL operation complete.
>>
>>sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk;
>>sh echo "select * from t005part;" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
t005part.a	t005part.b	t005part.c
5	10	11
>>
>>truncate table hive.hive.t005part;

--- SQL operation complete.
>>sh echo "select * from t005part;" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
t005part.a	t005part.b	t005part.c
>>
>>sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk;
>>truncate table hive.hive.t005part partition (b=10);

--- SQL operation complete.
>>sh echo "select * from t005part;" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
t005part.a	t005part.b	t005part.c
>>
>>sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk;
>>truncate table hive.hive.t005part partition (b=10,c=11);

--- SQL operation complete.
>>sh echo "select * from t005part;" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
t005part.a	t005part.b	t005part.c
>>
>>sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk;
>>sh echo "insert into t005part partition (b=10,c=12) values (6);" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk;
>>sh echo "select * from t005part;" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
t005part.a	t005part.b	t005part.c
5	10	11
6	10	12
>>
>>truncate table hive.hive.t005part partition (b=10,c=11);

--- SQL operation complete.
>>sh echo "select * from t005part;" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
t005part.a	t005part.b	t005part.c
6	10	12
>>
>>truncate table hive.hive.t005part partition (b=10);

--- SQL operation complete.
>>sh echo "select * from t005part;" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
t005part.a	t005part.b	t005part.c
>>
>>-- should return error
>>truncate table hive.hive.t005part partition (b=10,c=13);

*** ERROR[1214] Error encountered when executing HiveQL statement "TRUNCATE TABLE `default`.T005PART partition (b=10,c=13)". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10006]: Partition not found {b=10, c=13}

--- SQL operation failed with errors.
>>
>>-- should return error
>>purgedata hive.hive.thive;

*** ERROR[3242] This statement is not supported. Reason: Purgedata is not allowed for Hive tables. Use 'Truncate Table' command.

--- SQL operation failed with errors.
>>
>>-- tests for hive insert error modes
>>invoke hive.hive.thive_insert_smallint;

-- Definition of hive table HIVE.HIVE.THIVE_INSERT_SMALLINT
-- Definition current  Sat Jun 16 11:51:20 2018

  (
    A                                SMALLINT
  )
  /* stored as textfile */

--- SQL operation complete.
>>showddl hive.hive.thive_insert_smallint;

/* Hive DDL */
CREATE TABLE HIVE.HIVE.THIVE_INSERT_SMALLINT
  (
    A                                smallint
  )
  stored as textfile
;

/* Trafodion DDL */

REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE_INSERT_SMALLINT;
/* ObjectUID = 7436169711789392123 */

--- SQL operation complete.
>>
>>truncate table hive.hive.thive_insert_smallint;

--- SQL operation complete.
>>cqd hive_insert_error_mode '0';

--- SQL operation complete.
>>insert into hive.hive.thive_insert_smallint select * from 
+> (values (10), (11111111), (21), (22222222));

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149537134, failedModTS = 1529149881411, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive_insert_smallint

--- 4 row(s) inserted.
>>select * from hive.hive.thive_insert_smallint;

A     
------

    10

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,8 BYTES,ISO88591) Source Value:11111111 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).

--- 1 row(s) selected.
>>
>>truncate table hive.hive.thive_insert_smallint;

--- SQL operation complete.
>>cqd hive_insert_error_mode '1';

--- SQL operation complete.
>>insert into hive.hive.thive_insert_smallint select * from
+> (values (10), (11111111), (21), (22222222));

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:11111111 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.

--- 0 row(s) inserted.
>>select * from hive.hive.thive_insert_smallint;

--- 0 row(s) selected.
>>
>>truncate table hive.hive.thive_insert_smallint;

--- SQL operation complete.
>>cqd hive_insert_error_mode '2';

--- SQL operation complete.
>>insert into hive.hive.thive_insert_smallint select * from 
+> (values (10), (11111111), (21), (22222222));

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149883896, failedModTS = 1529149886157, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive_insert_smallint

--- 2 row(s) inserted.
>>select * from hive.hive.thive_insert_smallint;

A     
------

    10
    21

--- 2 row(s) selected.
>>
>>truncate table hive.hive.thive_insert_smallint;

--- SQL operation complete.
>>cqd hive_insert_error_mode '3';

--- SQL operation complete.
>>insert into hive.hive.thive_insert_smallint select * from
+> (values (10), (11111111), (21), (22222222));

--- 4 row(s) inserted.
>>select * from hive.hive.thive_insert_smallint;

A     
------

    10
     ?
    21
     ?

--- 4 row(s) selected.
>>
>>cqd hive_max_string_length_in_bytes '2';

--- SQL operation complete.
>>invoke hive.hive.thive_insert_varchar;

-- Definition of hive table HIVE.HIVE.THIVE_INSERT_VARCHAR
-- Definition current  Sat Jun 16 11:51:32 2018

  (
    A                                VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
      DEFAULT
  , B                                VARCHAR(2 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  )
  /* stored as textfile */

--- SQL operation complete.
>>showddl hive.hive.thive_insert_varchar;

/* Hive DDL */
CREATE TABLE HIVE.HIVE.THIVE_INSERT_VARCHAR
  (
    A                                varchar(1)
  , B                                string
  )
  stored as textfile
;

/* Trafodion DDL */

REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE_INSERT_VARCHAR;
/* ObjectUID = 7436169711789392374 */

--- SQL operation complete.
>>cqd hive_insert_error_mode '1';

--- SQL operation complete.
>>truncate table hive.hive.thive_insert_varchar;

--- SQL operation complete.
>>insert into hive.hive.thive_insert_varchar values ('abcddcba','efghijkl');

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. 

*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1529149539689, failedModTS = 1529149893202, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive_insert_varchar

--- 1 row(s) inserted.
>>
>>cqd hive_max_string_length_in_bytes '20';

--- SQL operation complete.
>>select * from hive.hive.thive_insert_varchar;

A     B                   
----  --------------------

abcd  efghijkl            

--- 1 row(s) selected.
>>
>>log;
