>>obey TEST020(setup);
>>--------------------------------------------------------------------------
>>
>>set schema hive.hive;

--- SQL operation complete.
>>cqd HIVE_MAX_STRING_LENGTH '20' ;

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

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

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

--- SQL operation complete.
>>
>>prepare explainIt from
+>  select substring(cast(SEQ_NUM+100 as char(3)),2,2) s,
+>         substring(operator,1,16) operator,
+>         cast(LEFT_CHILD_SEQ_NUM as char(2)) lc,
+>         cast(RIGHT_CHILD_SEQ_NUM as char(2)) rc,
+>         substring
+>         (substring(substring(tname from (1+locate('.',tname))),1,case locate(')',tname) when 0 then 0 else locate(')',substring(tname from (1+locate('.',tname))))-1 end),
+>         (locate('.',substring(tname from (1+locate('.',tname)))))+1,
+>         10
+>        ) tab_name
+>         from table (explain(NULL,'XX'))
+>         order by 1 desc;

--- SQL command prepared.
>>
>>obey TEST020(tests);
>>--------------------------------------------------------------------------
>>-- ORC file metadata info
>>invoke hive.hive.store_orc;

-- Definition of hive table STORE_ORC
-- Definition current  Fri Dec  4 19:48:03 2015

  (
    S_STORE_SK                       INT
  , S_STORE_ID                       VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_REC_START_DATE                 TIMESTAMP(6)
  , S_REC_END_DATE                   TIMESTAMP(6)
  , S_CLOSED_DATE_SK                 INT
  , S_STORE_NAME                     VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_NUMBER_EMPLOYEES               INT
  , S_FLOOR_SPACE                    INT
  , S_HOURS                          VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_MANAGER                        VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_MARKET_ID                      INT
  , S_GEOGRAPHY_CLASS                VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_MARKET_DESC                    VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_MARKET_MANAGER                 VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_DIVISION_ID                    INT
  , S_DIVISION_NAME                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_COMPANY_ID                     INT
  , S_COMPANY_NAME                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_STREET_NUMBER                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_STREET_NAME                    VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_STREET_TYPE                    VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_SUITE_NUMBER                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_CITY                           VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_COUNTY                         VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_STATE                          VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_ZIP                            VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_COUNTRY                        VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , S_GMT_OFFSET                     REAL
  , S_TAX_PRECENTAGE                 REAL
  )
   /* stored as orc */

--- SQL operation complete.
>>
>>-- select one row from ORC table
>>select [first 1] * from hive.hive.store_orc;

S_STORE_SK   S_STORE_ID            S_REC_START_DATE            S_REC_END_DATE              S_CLOSED_DATE_SK  S_STORE_NAME          S_NUMBER_EMPLOYEES  S_FLOOR_SPACE  S_HOURS               S_MANAGER             S_MARKET_ID  S_GEOGRAPHY_CLASS     S_MARKET_DESC         S_MARKET_MANAGER      S_DIVISION_ID  S_DIVISION_NAME       S_COMPANY_ID  S_COMPANY_NAME        S_STREET_NUMBER       S_STREET_NAME         S_STREET_TYPE         S_SUITE_NUMBER        S_CITY                S_COUNTY              S_STATE               S_ZIP                 S_COUNTRY             S_GMT_OFFSET     S_TAX_PRECENTAGE
-----------  --------------------  --------------------------  --------------------------  ----------------  --------------------  ------------------  -------------  --------------------  --------------------  -----------  --------------------  --------------------  --------------------  -------------  --------------------  ------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  ---------------  ----------------

          1  AAAAAAAABAAAAAAA      ?                           ?                                    2451189  ought                                245        5250760  8AM-4PM               William Ward                    2  Unknown               Enough high areas st  Charles Bartley                   1  Unknown                          1  Unknown               767                   Spring                Wy                    Suite 250             Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    2.9999999E-002

--- 1 row(s) selected.
>>
>>-- select all rows from ORC table
>>select * from hive.hive.store_orc;

S_STORE_SK   S_STORE_ID            S_REC_START_DATE            S_REC_END_DATE              S_CLOSED_DATE_SK  S_STORE_NAME          S_NUMBER_EMPLOYEES  S_FLOOR_SPACE  S_HOURS               S_MANAGER             S_MARKET_ID  S_GEOGRAPHY_CLASS     S_MARKET_DESC         S_MARKET_MANAGER      S_DIVISION_ID  S_DIVISION_NAME       S_COMPANY_ID  S_COMPANY_NAME        S_STREET_NUMBER       S_STREET_NAME         S_STREET_TYPE         S_SUITE_NUMBER        S_CITY                S_COUNTY              S_STATE               S_ZIP                 S_COUNTRY             S_GMT_OFFSET     S_TAX_PRECENTAGE
-----------  --------------------  --------------------------  --------------------------  ----------------  --------------------  ------------------  -------------  --------------------  --------------------  -----------  --------------------  --------------------  --------------------  -------------  --------------------  ------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  ---------------  ----------------

          1  AAAAAAAABAAAAAAA      ?                           ?                                    2451189  ought                                245        5250760  8AM-4PM               William Ward                    2  Unknown               Enough high areas st  Charles Bartley                   1  Unknown                          1  Unknown               767                   Spring                Wy                    Suite 250             Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    2.9999999E-002
          2  AAAAAAAACAAAAAAA      ?                           ?                                          ?  able                                 236        5285950  8AM-4PM               Scott Smith                     8  Unknown               Parliamentary candid  David Lamontagne                  1  Unknown                          1  Unknown               255                   Sycamore              Dr.                   Suite 410             Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    2.9999999E-002
          3  AAAAAAAACAAAAAAA      ?                           ?                                          ?  able                                 236        7557959  8AM-4PM               Scott Smith                     7  Unknown               Impossible, true arm  David Lamontagne                  1  Unknown                          1  Unknown               877                   Park Laurel           Road                  Suite T               Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    2.9999999E-002
          4  AAAAAAAAEAAAAAAA      ?                           ?                                    2451044  ese                                  218        9341467  8AM-4PM               Edwin Adams                     4  Unknown               Events would achieve  Thomas Pollack                    1  Unknown                          1  Unknown               27                    Lake                  Ln                    Suite 260             Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    2.9999999E-002
          5  AAAAAAAAEAAAAAAA      ?                           ?                                    2450910  anti                                 288        9078805  8AM-4PM               Edwin Adams                     8  Unknown               Events would achieve  Thomas Pollack                    1  Unknown                          1  Unknown               27                    Lee 6th               Court                 Suite 80              Fairview              Williamson County     TN                    35709                 United States         -5.0000000E+000    2.9999999E-002
          6  AAAAAAAAEAAAAAAA      ?                           ?                                          ?  cally                                229        9026222  8AM-4PM               Edwin Adams                    10  Unknown               Events would achieve  Thomas Pollack                    1  Unknown                          1  Unknown               220                   6th                   Lane                  Suite 140             Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    2.9999999E-002
          7  AAAAAAAAHAAAAAAA      ?                           ?                                          ?  ation                                297        8954883  8AM-4PM               David Thomas                    9  Unknown               Architects coul       Thomas Benton                     1  Unknown                          1  Unknown               811                   Lee                   Circle                Suite T               Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    9.9999997E-003
          8  AAAAAAAAIAAAAAAA      ?                           ?                                          ?  eing                                 278        6995995  8AM-4PM               Brett Yates                     2  Unknown               Various bars make mo  Dean Morrison                     1  Unknown                          1  Unknown               226                   12th                  Lane                  Suite D               Fairview              Williamson County     TN                    35709                 United States         -5.0000000E+000    7.9999998E-002
          9  AAAAAAAAIAAAAAAA      ?                           ?                                          ?  eing                                 271        6995995  8AM-4PM               Brett Yates                     2  Unknown               Formal, psychologica  Dean Morrison                     1  Unknown                          1  Unknown               226                   Hill                  Boulevard             Suite 190             Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    7.9999998E-002
         10  AAAAAAAAKAAAAAAA      ?                           ?                                          ?  bar                                  294        9294113  8AM-4PM               Raymond Jacobs                  8  Unknown               Little expectations   Michael Wilson                    1  Unknown                          1  Unknown               175                   4th                   Court                 Suite C               Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    5.9999998E-002
         11  AAAAAAAAKAAAAAAA      ?                           ?                                          ?  ought                                294        9294113  8AM-4PM               Raymond Jacobs                  6  Unknown               Mysterious employe    Michael Wilson                    1  Unknown                          1  Unknown               175                   Park Green            Court                 Suite 160             Midway                Williamson County     TN                    31904                 United States         -5.0000000E+000    1.0999999E-002
         12  AAAAAAAAKAAAAAAA      ?                           ?                                          ?  ought                                294        5219562  8AM-12AM              Robert Thompson                 6  Unknown               Events develop i      Dustin Kelly                      1  Unknown                          1  Unknown               337                   College               Boulevard             Suite 100             Fairview              Williamson County     TN                    31904                 United States         -5.0000000E+000    9.9999997E-003

--- 12 row(s) selected.
>>
>>-- select of few columns with WHERE predicate
>>select s_store_sk, left(s_store_id, 20) from hive.hive.store_orc where s_store_sk < 7;

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

          1  AAAAAAAABAAAAAAA    
          2  AAAAAAAACAAAAAAA    
          3  AAAAAAAACAAAAAAA    
          4  AAAAAAAAEAAAAAAA    
          5  AAAAAAAAEAAAAAAA    
          6  AAAAAAAAEAAAAAAA    

--- 6 row(s) selected.
>>
>>-- select count of rows 
>>select count(*) from hive.hive.store_orc;

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

                  12

--- 1 row(s) selected.
>>
>>-- explain of join between 2 ORC tables
>>prepare XX from select x.s_suite_number, y.s_street_name
+> from hive.hive.store_orc x, hive.hive.store_orc y
+>  where x.s_store_sk = y.s_store_sk;

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

S   OPERATOR          LC  RC  TAB_NAME  
--  ----------------  --  --  ----------

04  ROOT              3   ?             
03  HYBRID_HASH_JOIN  2   1             
02  HIVE_SCAN         ?   ?   STORE_ORC 
01  HIVE_SCAN         ?   ?   STORE_ORC 

--- 4 row(s) selected.
>>
>>-- execute of join between 2 ORC tables
>>execute XX;

S_SUITE_NUMBER        S_STREET_NAME       
--------------------  --------------------

Suite 250             Spring              
Suite 410             Sycamore            
Suite T               Park Laurel         
Suite 260             Lake                
Suite 80              Lee 6th             
Suite 140             6th                 
Suite T               Lee                 
Suite D               12th                
Suite 190             Hill                
Suite C               4th                 
Suite 160             Park Green          
Suite 100             College             

--- 12 row(s) selected.
>>
>>-- explain of join between hive(hdfs) and ORC tables
>>prepare XX from select x.s_suite_number, y.s_street_name
+> from hive.hive.store x, hive.hive.store_orc y
+>  where x.s_store_sk = y.s_store_sk;

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

S   OPERATOR          LC  RC  TAB_NAME  
--  ----------------  --  --  ----------

04  ROOT              3   ?             
03  NESTED_JOIN       1   2             
02  HIVE_SCAN         ?   ?   STORE_ORC 
01  HIVE_SCAN         ?   ?   STORE     

--- 4 row(s) selected.
>>
>>-- execute of join between hive(hdfs) and ORC tables
>>execute XX;

S_SUITE_NUMBER        S_STREET_NAME       
--------------------  --------------------

Suite 250             Spring              
Suite 410             Sycamore            
Suite T               Park Laurel         
Suite 260             Lake                
Suite 80              Lee 6th             
Suite 140             6th                 
Suite T               Lee                 
Suite D               12th                
Suite 190             Hill                
Suite C               4th                 
Suite 160             Park Green          
Suite 100             College             

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