>>
>>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 = 1499978885, failedModTS = 1499978946, 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 = 1499978885, failedModTS = 1499978946, 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 = 1499978950, failedModTS = 1499978958, 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 = 1499978946, failedModTS = 1499978976, 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 = 1499978946, failedModTS = 1499978976, 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 = 1499978973, failedModTS = 1499978982, 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.222222  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.222222  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.000000   1.01000000000000000E+000     1
          ?                     ?                                           ?       ?  ?                                                   ?     ?
          1                     1  averylongstring            -1.0000000E+000       0  2017-01-01 10:10:10.000000   1.00010000000000000E+002     1
          2                     2  good                        1.1000000E+000       2  2017-01-01 10:10:10.000000   2.00000000000000000E+002   100
          3                     3  good                        1.0000000E+000       2  2017-01-01 10:10:10.000000   2.10000000000000000E+002    10
          ?            4294967295  good                        3.3999999E+038       ?  2017-01-01 10:10:10.000000   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: 2017-07-13 20:50:54.956
Task:  CLEANUP         Status: Ended      Time: 2017-07-13 20:50:54.975
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.018
Task:  LOADING DATA    Status: Started    Time: 2017-07-13 20:50:54.975
       Rows Processed: 8 
       Error Rows:     5 
Task:  LOADING DATA    Status: Ended      Time: 2017-07-13 20:50:55.307
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:00.332
Task:  COMPLETION      Status: Started    Time: 2017-07-13 20:50:55.307
       Rows Loaded:    3 
Task:  COMPLETION      Status: Ended      Time: 2017-07-13 20:50:55.655
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.348

--- 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: 2017-07-13 20:50:56.697
Task:  CLEANUP         Status: Ended      Time: 2017-07-13 20:50:56.705
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.009
       Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205056
Task:  LOADING DATA    Status: Started    Time: 2017-07-13 20:50:56.705
       Rows Processed: 8 
       Error Rows:     5 
Task:  LOADING DATA    Status: Ended      Time: 2017-07-13 20:50:57.153
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:00.448
Task:  COMPLETION      Status: Started    Time: 2017-07-13 20:50:57.153
       Rows Loaded:    3 
Task:  COMPLETION      Status: Ended      Time: 2017-07-13 20:50:57.695
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.542

--- 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: 2017-07-13 20:50:58.677
Task:  CLEANUP         Status: Ended      Time: 2017-07-13 20:50:58.685
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.008
       Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205058
Task:  LOADING DATA    Status: Started    Time: 2017-07-13 20:50:58.685
       Rows Processed: 8 
       Error Rows:     5 
Task:  LOADING DATA    Status: Ended      Time: 2017-07-13 20:50:59.124
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:00.439
Task:  COMPLETION      Status: Started    Time: 2017-07-13 20:50:59.124
       Rows Loaded:    3 
Task:  COMPLETION      Status: Ended      Time: 2017-07-13 20:50:59.453
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:00.329

--- 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: 2017-07-13 20:51:00.456
Task:  CLEANUP         Status: Ended      Time: 2017-07-13 20:51:00.475
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.018
Task:  LOADING DATA    Status: Started    Time: 2017-07-13 20:51:00.475

*** 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: 2017-07-13 20:51:01.786
Task:  CLEANUP         Status: Ended      Time: 2017-07-13 20:51:01.795
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.009
       Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205101
Task:  LOADING DATA    Status: Started    Time: 2017-07-13 20:51:01.795

*** 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.
>>
>>process hive statement 'drop table thive';

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

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

--- 0 row(s) selected.
>>
>>sh echo "insert into thive values (1);" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk;
>>
>>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 = 1499979080, failedModTS = 1499979093, 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.
>>
>>process hive statement 'drop table thive';

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

--- SQL operation complete.
>>
>>sh echo "insert into thive values (1,2);" > TEST005_junk;
>>sh regrhive.ksh -f TEST005_junk;
>>
>>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 = 1499979096, failedModTS = 1499979110, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive

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

          1

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

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

          1            2

--- 1 row(s) selected.
>>
>>-- truncate of hive data
>>--cqd query_cache '0';
>>truncate 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 = 1499979110, failedModTS = 1499979116, 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 hive.hive.thive;

--- SQL operation complete.
>>truncate 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 = 1499979118, failedModTS = 1499979120, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive

--- 0 row(s) selected.
>>
>>-- truncate of partitioned hive table
>>process hive statement 'drop table t005part';

--- SQL operation complete.
>>process hive statement 'create table 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 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 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 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 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 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 hive.hive.t005part partition ('b=12');

*** ERROR[8442] Unable to access HDFS interface. Call to ExpLOBInterfaceEmptyDirectory returned error LOB_DIR_NAME_ERROR(535). Error detail 0.

*** ERROR[8035] Truncation of hive table failed. Reason: specified partition does not exist

--- 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 command.

*** ERROR[8822] The statement was not prepared.

>>
>>-- tests for hive insert error modes
>>invoke hive.hive.thive_insert_smallint;

-- Definition of hive table THIVE_INSERT_SMALLINT
-- Definition current  Thu Jul 13 20:54:19 2017

  (
    A                                SMALLINT
  )
  /* stored as textfile */

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

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

--- SQL operation complete.
>>
>>truncate 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));

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

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

A     
------

    10
    21

--- 2 row(s) selected.
>>
>>truncate 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 THIVE_INSERT_VARCHAR
-- Definition current  Thu Jul 13 20:54:28 2017

  (
    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 THIVE_INSERT_VARCHAR
  (
    A                                varchar(1)
  , B                                string
  )
  stored as textfile
;

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

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

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

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