trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ansha...@apache.org
Subject [2/4] incubator-trafodion git commit: JIRA TRAFODION-2072 plus few more
Date Tue, 21 Jun 2016 23:48:46 GMT
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b39f7524/core/sql/regress/hive/EXPECTED009
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/EXPECTED009 b/core/sql/regress/hive/EXPECTED009
index 8d88288..0cb36f7 100644
--- a/core/sql/regress/hive/EXPECTED009
+++ b/core/sql/regress/hive/EXPECTED009
@@ -39,7 +39,7 @@
 
 --- SQL operation complete.
 >>
->>-- should create an schema called "_HV_HIVE_"
+>>-- should see a schema called "_HV_HIVE_"
 >>execute get_hv_schemas;
 
 SCHEMA_NAME
@@ -52,121 +52,22 @@ _HV_HIVE_
 >>-- Verify tables were created
 >>showddl trafodion."_HV_HIVE_".customer;
 
-CREATE EXTERNAL TABLE TRAFODION."_HV_HIVE_".CUSTOMER
-  (
-    C_CUSTOMER_SK                    INT DEFAULT NULL
-  , C_CUSTOMER_ID                    VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_CURRENT_CDEMO_SK               INT DEFAULT NULL
-  , C_CURRENT_HDEMO_SK               INT DEFAULT NULL
-  , C_CURRENT_ADDR_SK                INT DEFAULT NULL
-  , C_FIRST_SHIPTO_DATE_SK           INT DEFAULT NULL
-  , C_FIRST_SALES_DATE_SK            INT DEFAULT NULL
-  , C_SALUTATION                     VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_FIRST_NAME                     VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_LAST_NAME                      VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_PREFERRED_CUST_FLAG            VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_BIRTH_DAY                      INT DEFAULT NULL
-  , C_BIRTH_MONTH                    INT DEFAULT NULL
-  , C_BIRTH_YEAR                     INT DEFAULT NULL
-  , C_BIRTH_COUNTRY                  VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_LOGIN                          VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_EMAIL_ADDRESS                  VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , C_LAST_REVIEW_DATE               VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  )
- ATTRIBUTES NO AUDIT
+CREATE EXTERNAL TABLE CUSTOMER
+  FOR HIVE.HIVE.CUSTOMER
 ;
 
 --- SQL operation complete.
 >>showddl trafodion."_HV_HIVE_".item;
 
-CREATE EXTERNAL TABLE TRAFODION."_HV_HIVE_".ITEM
-  (
-    I_ITEM_SK                        INT DEFAULT NULL
-  , I_ITEM_ID                        VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_REC_START_DATE                 TIMESTAMP(6) DEFAULT NULL
-  , I_REC_END_DATE                   TIMESTAMP(6) DEFAULT NULL
-  , I_ITEM_DESC                      VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_CURRENT_PRICE                  REAL DEFAULT NULL
-  , I_WHOLESALE_COST                 REAL DEFAULT NULL
-  , I_BRAND_ID                       INT DEFAULT NULL
-  , I_BRAND                          VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_CLASS_ID                       INT DEFAULT NULL
-  , I_CLASS                          VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_CATEGORY_ID                    INT DEFAULT NULL
-  , I_CATEGORY                       VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_MANUFACT_ID                    INT DEFAULT NULL
-  , I_MANUFACT                       VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_SIZE                           VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_FORMULATION                    VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_COLOR                          VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_UNITS                          VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_CONTAINER                      VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , I_MANAGER_ID                     INT DEFAULT NULL
-  , I_PRODUCT_NAME                   VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  )
- ATTRIBUTES NO AUDIT
+CREATE EXTERNAL TABLE ITEM
+  FOR HIVE.HIVE.ITEM
 ;
 
 --- SQL operation complete.
 >>showddl trafodion."_HV_HIVE_".promotion;
 
-CREATE EXTERNAL TABLE TRAFODION."_HV_HIVE_".PROMOTION
-  (
-    P_PROMO_SK                       INT DEFAULT NULL
-  , P_PROMO_ID                       VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_START_DATE_SK                  INT DEFAULT NULL
-  , P_END_DATE_SK                    INT DEFAULT NULL
-  , P_ITEM_SK                        INT DEFAULT NULL
-  , P_COST                           REAL DEFAULT NULL
-  , P_RESPONSE_TARGET                INT DEFAULT NULL
-  , P_PROMO_NAME                     VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_DMAIL                  VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_EMAIL                  VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_CATALOG                VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_TV                     VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_RADIO                  VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_PRESS                  VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_EVENT                  VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_DEMO                   VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_CHANNEL_DETAILS                VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_PURPOSE                        VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  , P_DISCOUNT_ACTIVE                VARCHAR(32000 BYTES) CHARACTER SET UTF8
-      COLLATE DEFAULT DEFAULT NULL
-  )
- ATTRIBUTES NO AUDIT
+CREATE EXTERNAL TABLE PROMOTION
+  FOR HIVE.HIVE.PROMOTION
 ;
 
 --- SQL operation complete.
@@ -190,25 +91,15 @@ _HV_SCH_T009_
 --- 2 row(s) selected.
 >>showddl trafodion."_HV_SCH_T009_".t009t1;
 
-CREATE EXTERNAL TABLE TRAFODION."_HV_SCH_T009_".T009T1
-  (
-    A                                INT DEFAULT NULL
-  , B                                INT DEFAULT NULL
-  , C                                INT DEFAULT NULL
-  )
- ATTRIBUTES NO AUDIT
+CREATE EXTERNAL TABLE T009T1
+  FOR HIVE.SCH_T009.T009T1
 ;
 
 --- SQL operation complete.
 >>showddl trafodion."_HV_SCH_T009_".t009t2;
 
-CREATE EXTERNAL TABLE TRAFODION."_HV_SCH_T009_".T009T2
-  (
-    A                                INT DEFAULT NULL
-  , B                                INT DEFAULT NULL
-  , C                                INT DEFAULT NULL
-  )
- ATTRIBUTES NO AUDIT
+CREATE EXTERNAL TABLE T009T2
+  FOR HIVE.SCH_T009.T009T2
 ;
 
 --- SQL operation complete.
@@ -338,17 +229,16 @@ PROMOTION
 --- SQL operation failed with errors.
 >>create external table seabase.customer like hive.hive.customer;
 
-*** ERROR[1180] Trying to create an external HIVE table with a different schema or table name (SEABASE) than the source table (HIVE).  The external schema and table name must be the same as the source.
+*** ERROR[3242] This statement is not supported. Reason: 'like' clause cannot be specified when creating an external table.
+
+*** ERROR[8822] The statement was not prepared.
 
---- SQL operation failed with errors.
 >>create external table customer1 like hive.hive.customer;
 
-*** ERROR[1180] Trying to create an external HIVE table with a different schema or table name (CUSTOMER1) than the source table (CUSTOMER).  The external schema and table name must be the same as the source.
+*** ERROR[3242] This statement is not supported. Reason: 'like' clause cannot be specified when creating an external table.
 
---- SQL operation failed with errors.
->>create table t009t1 like "_HV_SCH_T009_".t009t1;
+*** ERROR[8822] The statement was not prepared.
 
---- SQL operation complete.
 >>create table t009t2 as select * from "_HV_SCH_T009_".t009t2;
 
 *** ERROR[4258] Trying to access external table TRAFODION."_HV_SCH_T009_".T009T2 through its external name format. Please use the native table name.
@@ -357,11 +247,15 @@ PROMOTION
 
 >>
 >>-- this create succeeds
->>create table t009t1 as select * from hive.sch_t009.t009t1;
+>>create table t009t1 like "_HV_SCH_T009_".t009t1;
 
-*** ERROR[1390] Object TRAFODION.HIVE_T009.T009T1 already exists in Trafodion.
+--- SQL operation complete.
+>>drop table t009t1;
+
+--- SQL operation complete.
+>>create table t009t1 as select * from hive.sch_t009.t009t1;
 
---- 0 row(s) inserted.
+--- 10 row(s) inserted.
 >>
 >>get tables;
 
@@ -395,31 +289,17 @@ T009T1
 >>-- should fail - column mismatch
 >>select count(*) from hive.sch_t009.t009t1;
 
-(EXPR)              
---------------------
+*** ERROR[8437] Mismatch detected between external table and underlying hive table definitions.
 
-                  10
+*** ERROR[8822] The statement was not prepared.
 
---- 1 row(s) selected.
->>
 >>sh sqlci -i "TEST009(select_stmts)";
 >>select * from hive.sch_t009.t009t1;
 
-A            B            C            D          
------------  -----------  -----------  -----------
+*** ERROR[8437] Mismatch detected between external table and underlying hive table definitions.
 
-         10           15           10         1973
-          9           26           10         1966
-          8           26           12         1938
-          7           24            4         1985
-          6            4           12         1925
-          5            8            5         1956
-          4            7            6         1983
-          3           18            9         1979
-          2            9            4         1966
-          1            9           12         1936
+*** ERROR[8822] The statement was not prepared.
 
---- 10 row(s) selected.
 >>select * from hive.sch_t009.t009t2;
 
 A            B            C          
@@ -450,24 +330,20 @@ CATALOG_NAME
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
-HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              SCH_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                T009T1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
 HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              SCH_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
 TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_HIVE_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
 TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_SCH_T009_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
 TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _MD_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                OBJECTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 
---- 6 row(s) selected.
+--- 5 row(s) selected.
 >>drop external table t009t1 for hive.sch_t009.t009t1;
 
-*** ERROR[1389] Object TRAFODION."_HV_SCH_T009_".T009T1 does not exist in Trafodion.
-
---- SQL operation failed with errors.
+--- SQL operation complete.
 >>get tables in schema "_HV_SCH_T009_";
 
 Tables in Schema TRAFODION._HV_SCH_T009_
 ========================================
 
-T009T1
 T009T2
 
 --- SQL operation complete.
@@ -476,14 +352,37 @@ T009T2
 ROW_ID      CATALOG_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                            OBJECT_NAME
 ----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
+         4  HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                            PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
+         5  HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              SCH_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                            T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
+         2  TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_HIVE_                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                            PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
+         3  TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_SCH_T009_                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                            T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          1  TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _MD_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                            OBJECTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 
---- 1 row(s) selected.
+--- 5 row(s) selected.
 >>create external table t009t1 for hive.sch_t009.t009t1;
 
-*** ERROR[1390] Object TRAFODION."_HV_SCH_T009_".T009T1 already exists in Trafodion.
+--- SQL operation complete.
+>>invoke hive.sch_t009.t009t1;
 
---- SQL operation failed with errors.
+-- Definition of hive table T009T1
+-- Definition current  Thu Jun 16 19:45:00 2016
+
+  (
+    A                                INT
+  , B                                INT
+  , C                                INT
+  , D                                INT
+  )
+  /* stored as textfile */
+
+--- SQL operation complete.
+>>showddl trafodion."_HV_SCH_T009_".t009t1;
+
+CREATE EXTERNAL TABLE T009T1
+  FOR HIVE.SCH_T009.T009T1
+;
+
+--- SQL operation complete.
 >>
 >>-- select should now succeed
 >>select count(*) from hive.sch_t009.t009t1;
@@ -494,6 +393,23 @@ ROW_ID      CATALOG_NAME
                   10
 
 --- 1 row(s) selected.
+>>select * from hive.sch_t009.t009t1;
+
+A            B            C            D          
+-----------  -----------  -----------  -----------
+
+         10           15           10         1973
+          9           26           10         1966
+          8           26           12         1938
+          7           24            4         1985
+          6            4           12         1925
+          5            8            5         1956
+          4            7            6         1983
+          3           18            9         1979
+          2            9            4         1966
+          1            9           12         1936
+
+--- 10 row(s) selected.
 >>
 >>obey TEST009(test_hbase);
 >>-- create external tables for precreated tables
@@ -516,22 +432,24 @@ _HB__CELL__
 --- 2 row(s) selected.
 >>
 >>-- Verify tables were created
->>showddl trafodion."_HB__ROW__"."baseball";
+>>invoke trafodion."_HB__ROW__"."baseball";
+
+-- Definition of Trafodion external table TRAFODION."_HB__ROW__"."baseball"
+-- Definition current  Thu Jun 16 19:45:06 2016
 
-CREATE EXTERNAL TABLE TRAFODION."_HB__ROW__"."baseball"
   (
     ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
       COLLATE DEFAULT DEFAULT NULL NOT NULL NOT DROPPABLE
   , COLUMN_DETAILS                   VARCHAR(10000) CHARACTER SET ISO88591
       COLLATE DEFAULT DEFAULT NULL NOT NULL NOT DROPPABLE
   )
- ATTRIBUTES NO AUDIT
-;
 
 --- SQL operation complete.
->>showddl trafodion."_HB__CELL__"."baseball";
+>>invoke trafodion."_HB__CELL__"."baseball";
+
+-- Definition of Trafodion external table TRAFODION."_HB__CELL__"."baseball"
+-- Definition current  Thu Jun 16 19:45:07 2016
 
-CREATE EXTERNAL TABLE TRAFODION."_HB__CELL__"."baseball"
   (
     ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
       COLLATE DEFAULT DEFAULT NULL NOT NULL NOT DROPPABLE
@@ -544,7 +462,19 @@ CREATE EXTERNAL TABLE TRAFODION."_HB__CELL__"."baseball"
   , COL_VALUE                        VARCHAR(1000) CHARACTER SET ISO88591
       COLLATE DEFAULT DEFAULT NULL NOT NULL NOT DROPPABLE
   )
- ATTRIBUTES NO AUDIT
+
+--- SQL operation complete.
+>>showddl trafodion."_HB__ROW__"."baseball";
+
+CREATE EXTERNAL TABLE baseball
+  FOR HBASE."_ROW_"."baseball"
+;
+
+--- SQL operation complete.
+>>showddl trafodion."_HB__CELL__"."baseball";
+
+CREATE EXTERNAL TABLE baseball
+  FOR HBASE."_CELL_"."baseball"
 ;
 
 --- SQL operation complete.
@@ -592,4 +522,879 @@ ROW_ID      COLS
 
 --- SQL operation complete.
 >>
+>>obey TEST009(test_ext);
+>>-- test external table attributes
+>>set schema trafodion.sch;
+
+--- SQL operation complete.
+>>drop external table if exists store_sales for hive.hive.store_sales;
+
+--- SQL operation complete.
+>>create external table store_sales 
++>  for hive.hive.store_sales;
+
+--- SQL operation complete.
+>>invoke hive.hive.store_sales;
+
+-- Definition of hive table STORE_SALES
+-- Definition current  Thu Jun 16 19:45:13 2016
+
+  (
+    SS_SOLD_DATE_SK                  INT
+  , SS_SOLD_TIME_SK                  INT
+  , SS_ITEM_SK                       INT
+  , SS_CUSTOMER_SK                   INT
+  , SS_CDEMO_SK                      INT
+  , SS_HDEMO_SK                      INT
+  , SS_ADDR_SK                       INT
+  , SS_STORE_SK                      INT
+  , SS_PROMO_SK                      INT
+  , SS_TICKET_NUMBER                 INT
+  , SS_QUANTITY                      INT
+  , SS_WHOLESALE_COST                REAL
+  , SS_LIST_PRICE                    REAL
+  , SS_SALES_PRICE                   REAL
+  , SS_EXT_DISCOUNT_AMT              REAL
+  , SS_EXT_SALES_PRICE               REAL
+  , SS_EXT_WHOLESALE_COST            REAL
+  , SS_EXT_LIST_PRICE                REAL
+  , SS_EXT_TAX                       REAL
+  , SS_COUPON_AMT                    REAL
+  , SS_NET_PAID                      REAL
+  , SS_NET_PAID_INC_TAX              REAL
+  , SS_NET_PROFIT                    REAL
+  )
+  /* stored as textfile */
+
+--- SQL operation complete.
+>>
+>>set schema hive.hive;
+
+--- SQL operation complete.
+>>prepare s from select * from store_sales where ss_item_sk = 1;
+
+*** WARNING[6008] Statistics for column (SS_ITEM_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212332866313934337
+ROWS_OUT ............. 1,709
+EST_TOTAL_COST ........... 0.27
+STATEMENT ................ select * from store_sales where ss_item_sk = 1;
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
+REQUESTS_IN .............. 1
+ROWS_OUT ............. 1,709
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ........... 0.27
+DESCRIPTION
+  max_card_est ........... 2.77461e+06
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  statement_index ........ 0
+  affinity_value ......... 0
+  max_max_cardinality  1,709
+  total_overflow_size .... 0.00 KB
+  xn_access_mode ......... read_only
+  xn_autoabort_interval    0
+  auto_query_retry ....... enabled
+  plan_version ....... 2,600
+  embedded_arkcmp ........ used
+  IS_SQLCI ............... ON
+  LDAP_USERNAME
+  HIVE_MAX_STRING_LENGTH  20
+  SHOWCONTROL_SHOW_ALL ... OFF
+  SCHEMA ................. HIVE.HIVE
+  select_list ............ HIVE.STORE_SALES.SS_SOLD_DATE_SK,
+                             HIVE.STORE_SALES.SS_SOLD_TIME_SK, %(1),
+                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
+                             HIVE.STORE_SALES.SS_CDEMO_SK,
+                             HIVE.STORE_SALES.SS_HDEMO_SK,
+                             HIVE.STORE_SALES.SS_ADDR_SK,
+                             HIVE.STORE_SALES.SS_STORE_SK,
+                             HIVE.STORE_SALES.SS_PROMO_SK,
+                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
+                             HIVE.STORE_SALES.SS_QUANTITY,
+                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
+                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_TAX,
+                             HIVE.STORE_SALES.SS_COUPON_AMT,
+                             HIVE.STORE_SALES.SS_NET_PAID,
+                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
+                             HIVE.STORE_SALES.SS_NET_PROFIT
+  input_variables ........ %(1)
+
+
+HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES
+REQUESTS_IN .............. 1
+ROWS_OUT ............. 1,709
+EST_OPER_COST ............ 0.27
+EST_TOTAL_COST ........... 0.27
+DESCRIPTION
+  max_card_est ........... 2.77461e+06
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  scan_type .............. subset scan of table HIVE.HIVE.STORE_SALES
+  object_type ............ Hive_Text
+  scan_direction ......... forward
+  lock_mode .............. not specified, defaulted to lock cursor
+  access_mode ............ not specified, defaulted to read committed
+  columns_retrieved ..... 23
+  key_columns ............ HIVE.STORE_SALES.SS_SOLD_DATE_SK,
+                             HIVE.STORE_SALES.SS_SOLD_TIME_SK,
+                             HIVE.STORE_SALES.SS_ITEM_SK,
+                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
+                             HIVE.STORE_SALES.SS_CDEMO_SK,
+                             HIVE.STORE_SALES.SS_HDEMO_SK,
+                             HIVE.STORE_SALES.SS_ADDR_SK,
+                             HIVE.STORE_SALES.SS_STORE_SK,
+                             HIVE.STORE_SALES.SS_PROMO_SK,
+                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
+                             HIVE.STORE_SALES.SS_QUANTITY,
+                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
+                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_TAX,
+                             HIVE.STORE_SALES.SS_COUPON_AMT,
+                             HIVE.STORE_SALES.SS_NET_PAID,
+                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
+                             HIVE.STORE_SALES.SS_NET_PROFIT
+  executor_predicates .... (HIVE.STORE_SALES.SS_ITEM_SK = %(1))
+
+--- SQL operation complete.
+>>
+>>-- join with nested join
+>>control query shape nested_join(scan(path 'CUSTOMER'), 
++>      scan(path 'STORE_SALES'));
+
+--- SQL operation complete.
+>>prepare s from select * from customer, store_sales 
++>    where store_sales.ss_item_sk = customer.c_customer_sk;
+
+*** 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.
+
+*** WARNING[6008] Statistics for column (SS_ITEM_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
+---- ---- ---- --------------------  --------  --------------------  ---------
+
+3    .    4    root                                                  2.92E+006
+1    2    3    nested_join                                           2.92E+006
+.    .    2    hive_scan                       STORE_SALES           2.78E+001
+.    .    1    hive_scan                       CUSTOMER              1.04E+005
+
+--- SQL operation complete.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212332866314848871
+ROWS_OUT ......... 2,920,643
+EST_TOTAL_COST ........... 1.07
+STATEMENT ................ select *
+                           from customer, store_sales
+                           where store_sales.ss_item_sk =
+                             customer.c_customer_sk;
+MUST_MATCH ............... forced nested join(forced scan, forced scan)
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ======================================  SEQ_NO 4        ONLY CHILD 3
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 2,920,643
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ........... 1.07
+DESCRIPTION
+  max_card_est ........... 2.92064e+06
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  statement_index ........ 0
+  affinity_value ......... 0
+  max_max_cardinal  2,920,643
+  total_overflow_size .... 0.00 KB
+  xn_access_mode ......... read_only
+  xn_autoabort_interval    0
+  auto_query_retry ....... enabled
+  plan_version ....... 2,600
+  embedded_arkcmp ........ used
+  IS_SQLCI ............... ON
+  LDAP_USERNAME
+  HIVE_MAX_STRING_LENGTH  20
+  SHOWCONTROL_SHOW_ALL ... OFF
+  SCHEMA ................. HIVE.HIVE
+  select_list ............ HIVE.CUSTOMER.C_CUSTOMER_SK,
+                             HIVE.CUSTOMER.C_CUSTOMER_ID,
+                             HIVE.CUSTOMER.C_CURRENT_CDEMO_SK,
+                             HIVE.CUSTOMER.C_CURRENT_HDEMO_SK,
+                             HIVE.CUSTOMER.C_CURRENT_ADDR_SK,
+                             HIVE.CUSTOMER.C_FIRST_SHIPTO_DATE_SK,
+                             HIVE.CUSTOMER.C_FIRST_SALES_DATE_SK,
+                             HIVE.CUSTOMER.C_SALUTATION,
+                             HIVE.CUSTOMER.C_FIRST_NAME,
+                             HIVE.CUSTOMER.C_LAST_NAME,
+                             HIVE.CUSTOMER.C_PREFERRED_CUST_FLAG,
+                             HIVE.CUSTOMER.C_BIRTH_DAY,
+                             HIVE.CUSTOMER.C_BIRTH_MONTH,
+                             HIVE.CUSTOMER.C_BIRTH_YEAR,
+                             HIVE.CUSTOMER.C_BIRTH_COUNTRY,
+                             HIVE.CUSTOMER.C_LOGIN, HIVE.CUSTOMER.C_EMAIL_ADDRE
+                             SS, HIVE.CUSTOMER.C_LAST_REVIEW_DATE,
+                             HIVE.STORE_SALES.SS_SOLD_DATE_SK,
+                             HIVE.STORE_SALES.SS_SOLD_TIME_SK,
+                             HIVE.CUSTOMER.C_CUSTOMER_SK,
+                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
+                             HIVE.STORE_SALES.SS_CDEMO_SK,
+                             HIVE.STORE_SALES.SS_HDEMO_SK,
+                             HIVE.STORE_SALES.SS_ADDR_SK,
+                             HIVE.STORE_SALES.SS_STORE_SK,
+                             HIVE.STORE_SALES.SS_PROMO_SK,
+                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
+                             HIVE.STORE_SALES.SS_QUANTITY,
+                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
+                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_TAX,
+                             HIVE.STORE_SALES.SS_COUPON_AMT,
+                             HIVE.STORE_SALES.SS_NET_PAID,
+                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
+                             HIVE.STORE_SALES.SS_NET_PROFIT
+
+
+NESTED_JOIN ===============================  SEQ_NO 3        CHILDREN 1, 2
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 2,920,642
+EST_OPER_COST ............ 0.5
+EST_TOTAL_COST ........... 1.07
+DESCRIPTION
+  max_card_est ........... 2.92064e+06
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  join_type .............. inner
+  join_method ............ nested
+
+
+HIVE_SCAN =================================  SEQ_NO 2        NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES
+REQUESTS_IN ........ 104,843
+ROWS/REQUEST ............ 27.86
+EST_OPER_COST ............ 0.55
+EST_TOTAL_COST ........... 0.55
+DESCRIPTION
+  max_card_est ........... 2.92064e+06
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  scan_type .............. subset scan of table HIVE.HIVE.STORE_SALES
+  object_type ............ Hive_Text
+  scan_direction ......... forward
+  lock_mode .............. not specified, defaulted to lock cursor
+  access_mode ............ not specified, defaulted to read committed
+  columns_retrieved ..... 23
+  key_columns ............ HIVE.STORE_SALES.SS_SOLD_DATE_SK,
+                             HIVE.STORE_SALES.SS_SOLD_TIME_SK,
+                             HIVE.STORE_SALES.SS_ITEM_SK,
+                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
+                             HIVE.STORE_SALES.SS_CDEMO_SK,
+                             HIVE.STORE_SALES.SS_HDEMO_SK,
+                             HIVE.STORE_SALES.SS_ADDR_SK,
+                             HIVE.STORE_SALES.SS_STORE_SK,
+                             HIVE.STORE_SALES.SS_PROMO_SK,
+                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
+                             HIVE.STORE_SALES.SS_QUANTITY,
+                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
+                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
+                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
+                             HIVE.STORE_SALES.SS_EXT_TAX,
+                             HIVE.STORE_SALES.SS_COUPON_AMT,
+                             HIVE.STORE_SALES.SS_NET_PAID,
+                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
+                             HIVE.STORE_SALES.SS_NET_PROFIT
+  executor_predicates .... (HIVE.STORE_SALES.SS_ITEM_SK =
+                             HIVE.CUSTOMER.C_CUSTOMER_SK)
+
+
+HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.CUSTOMER
+REQUESTS_IN .............. 1
+ROWS_OUT ........... 104,843
+EST_OPER_COST ............ 0.02
+EST_TOTAL_COST ........... 0.02
+DESCRIPTION
+  max_card_est ..... 104,843
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  scan_type .............. subset scan of table HIVE.HIVE.CUSTOMER
+  object_type ............ Hive_Text
+  scan_direction ......... forward
+  lock_mode .............. not specified, defaulted to lock cursor
+  access_mode ............ not specified, defaulted to read committed
+  columns_retrieved ..... 18
+  key_columns ............ HIVE.CUSTOMER.C_CUSTOMER_SK,
+                             HIVE.CUSTOMER.C_CUSTOMER_ID,
+                             HIVE.CUSTOMER.C_CURRENT_CDEMO_SK,
+                             HIVE.CUSTOMER.C_CURRENT_HDEMO_SK,
+                             HIVE.CUSTOMER.C_CURRENT_ADDR_SK,
+                             HIVE.CUSTOMER.C_FIRST_SHIPTO_DATE_SK,
+                             HIVE.CUSTOMER.C_FIRST_SALES_DATE_SK,
+                             HIVE.CUSTOMER.C_SALUTATION,
+                             HIVE.CUSTOMER.C_FIRST_NAME,
+                             HIVE.CUSTOMER.C_LAST_NAME,
+                             HIVE.CUSTOMER.C_PREFERRED_CUST_FLAG,
+                             HIVE.CUSTOMER.C_BIRTH_DAY,
+                             HIVE.CUSTOMER.C_BIRTH_MONTH,
+                             HIVE.CUSTOMER.C_BIRTH_YEAR,
+                             HIVE.CUSTOMER.C_BIRTH_COUNTRY,
+                             HIVE.CUSTOMER.C_LOGIN, HIVE.CUSTOMER.C_EMAIL_ADDRE
+                             SS, HIVE.CUSTOMER.C_LAST_REVIEW_DATE
+  executor_predicates .... HIVE.CUSTOMER.C_CUSTOMER_SK is not null
+
+--- SQL operation complete.
+>>
+>>control query shape cut;
+
+--- SQL operation complete.
+>>set schema trafodion.sch;
+
+--- SQL operation complete.
+>>drop external table if exists date_dim for hive.hive.date_dim;
+
+--- SQL operation complete.
+>>cqd volatile_table_find_suitable_key 'SYSTEM';
+
+--- SQL operation complete.
+>>create external table date_dim 
++>  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date, 
++>   d_month_seq int, d_week_seq int, d_quarter_seq int, d_year int, d_dow int,
++>   d_moy int, d_dom int, d_qoy int, d_fy_year int, d_fy_quarter_seq int,
++>   d_fy_week_seq int,
++>   d_day_name varchar(120 bytes) character set utf8, d_quarter_name varchar(200 bytes) character set utf8, d_holiday varchar(100 bytes) character set utf8,
++>   d_weekend varchar(100 bytes) character set utf8, d_following_holiday varchar(100 bytes) character set utf8, 
++>   d_first_dom int, d_last_dom int, d_same_day_ly int, d_same_day_lq int,
++>   d_current_day varchar(100 bytes) character set utf8, d_current_week varchar(111 bytes) character set utf8,
++>   d_current_month varchar(200 bytes) character set utf8, d_current_quarter varchar(100 bytes) character set utf8, 
++>   d_current_year varchar(100 bytes) character set utf8)
++>  for hive.hive.date_dim;
+
+--- SQL operation complete.
+>>invoke hive.hive.date_dim;
+
+-- Definition of hive table DATE_DIM
+-- Definition current  Thu Jun 16 19:45:17 2016
+
+  (
+    D_DATE_SK                        INT
+  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_DATE                           DATE
+  , D_MONTH_SEQ                      INT
+  , D_WEEK_SEQ                       INT
+  , D_QUARTER_SEQ                    INT
+  , D_YEAR                           INT
+  , D_DOW                            INT
+  , D_MOY                            INT
+  , D_DOM                            INT
+  , D_QOY                            INT
+  , D_FY_YEAR                        INT
+  , D_FY_QUARTER_SEQ                 INT
+  , D_FY_WEEK_SEQ                    INT
+  , D_DAY_NAME                       VARCHAR(120 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_QUARTER_NAME                   VARCHAR(200 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_HOLIDAY                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_WEEKEND                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_FOLLOWING_HOLIDAY              VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_FIRST_DOM                      INT
+  , D_LAST_DOM                       INT
+  , D_SAME_DAY_LY                    INT
+  , D_SAME_DAY_LQ                    INT
+  , D_CURRENT_DAY                    VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_WEEK                   VARCHAR(111 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_MONTH                  VARCHAR(200 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_QUARTER                VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_YEAR                   VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  )
+  /* stored as textfile */
+
+--- SQL operation complete.
+>>showddl hive.hive.date_dim;
+
+/* Hive DDL */
+CREATE TABLE DATE_DIM
+  (
+    D_DATE_SK                        int
+  , D_DATE_ID                        string
+  , D_DATE                           timestamp
+  , D_MONTH_SEQ                      int
+  , D_WEEK_SEQ                       int
+  , D_QUARTER_SEQ                    int
+  , D_YEAR                           int
+  , D_DOW                            int
+  , D_MOY                            int
+  , D_DOM                            int
+  , D_QOY                            int
+  , D_FY_YEAR                        int
+  , D_FY_QUARTER_SEQ                 int
+  , D_FY_WEEK_SEQ                    int
+  , D_DAY_NAME                       string
+  , D_QUARTER_NAME                   string
+  , D_HOLIDAY                        string
+  , D_WEEKEND                        string
+  , D_FOLLOWING_HOLIDAY              string
+  , D_FIRST_DOM                      int
+  , D_LAST_DOM                       int
+  , D_SAME_DAY_LY                    int
+  , D_SAME_DAY_LQ                    int
+  , D_CURRENT_DAY                    string
+  , D_CURRENT_WEEK                   string
+  , D_CURRENT_MONTH                  string
+  , D_CURRENT_QUARTER                string
+  , D_CURRENT_YEAR                   string
+  )
+  stored as textfile
+;
+
+/* Trafodion DDL */
+
+CREATE EXTERNAL TABLE DATE_DIM
+  (
+    D_DATE_SK                        INT DEFAULT NULL
+  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_DATE                           DATE DEFAULT NULL
+  , D_MONTH_SEQ                      INT DEFAULT NULL
+  , D_WEEK_SEQ                       INT DEFAULT NULL
+  , D_QUARTER_SEQ                    INT DEFAULT NULL
+  , D_YEAR                           INT DEFAULT NULL
+  , D_DOW                            INT DEFAULT NULL
+  , D_MOY                            INT DEFAULT NULL
+  , D_DOM                            INT DEFAULT NULL
+  , D_QOY                            INT DEFAULT NULL
+  , D_FY_YEAR                        INT DEFAULT NULL
+  , D_FY_QUARTER_SEQ                 INT DEFAULT NULL
+  , D_FY_WEEK_SEQ                    INT DEFAULT NULL
+  , D_DAY_NAME                       VARCHAR(120 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_QUARTER_NAME                   VARCHAR(200 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_HOLIDAY                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_WEEKEND                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_FOLLOWING_HOLIDAY              VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_FIRST_DOM                      INT DEFAULT NULL
+  , D_LAST_DOM                       INT DEFAULT NULL
+  , D_SAME_DAY_LY                    INT DEFAULT NULL
+  , D_SAME_DAY_LQ                    INT DEFAULT NULL
+  , D_CURRENT_DAY                    VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_WEEK                   VARCHAR(111 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_MONTH                  VARCHAR(200 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_QUARTER                VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_YEAR                   VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  )
+  FOR HIVE.HIVE.DATE_DIM
+;
+
+--- SQL operation complete.
+>>prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';
+
+*** WARNING[6008] Statistics for column (D_DATE) from table HIVE.HIVE.DATE_DIM were not available. As a result, the access path chosen might not be the best possible.
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212332866319821558
+ROWS_OUT ................. 1
+EST_TOTAL_COST ........... 0.01
+STATEMENT ................ select *
+                           from hive.hive.date_dim
+                           where d_date = date '2016-01-27';
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ........... 0.01
+DESCRIPTION
+  max_card_est ........... 1
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  statement_index ........ 0
+  affinity_value ......... 0
+  max_max_cardinality .... 1
+  total_overflow_size .... 0.00 KB
+  xn_access_mode ......... read_only
+  xn_autoabort_interval    0
+  auto_query_retry ....... enabled
+  plan_version ....... 2,600
+  embedded_arkcmp ........ used
+  IS_SQLCI ............... ON
+  LDAP_USERNAME
+  HIVE_MAX_STRING_LENGTH  20
+  SHOWCONTROL_SHOW_ALL ... OFF
+  SCHEMA ................. TRAFODION.SCH
+  VOLATILE_TABLE_FIND_SUI  SYSTEM
+  select_list ............ HIVE.DATE_DIM.D_DATE_SK, HIVE.DATE_DIM.D_DATE_ID,
+                             %(2016-01-27), HIVE.DATE_DIM.D_MONTH_SEQ,
+                             HIVE.DATE_DIM.D_WEEK_SEQ,
+                             HIVE.DATE_DIM.D_QUARTER_SEQ, HIVE.DATE_DIM.D_YEAR,
+                             HIVE.DATE_DIM.D_DOW, HIVE.DATE_DIM.D_MOY,
+                             HIVE.DATE_DIM.D_DOM, HIVE.DATE_DIM.D_QOY,
+                             HIVE.DATE_DIM.D_FY_YEAR,
+                             HIVE.DATE_DIM.D_FY_QUARTER_SEQ,
+                             HIVE.DATE_DIM.D_FY_WEEK_SEQ,
+                             HIVE.DATE_DIM.D_DAY_NAME,
+                             HIVE.DATE_DIM.D_QUARTER_NAME,
+                             HIVE.DATE_DIM.D_HOLIDAY, HIVE.DATE_DIM.D_WEEKEND,
+                             HIVE.DATE_DIM.D_FOLLOWING_HOLIDAY,
+                             HIVE.DATE_DIM.D_FIRST_DOM,
+                             HIVE.DATE_DIM.D_LAST_DOM,
+                             HIVE.DATE_DIM.D_SAME_DAY_LY,
+                             HIVE.DATE_DIM.D_SAME_DAY_LQ,
+                             HIVE.DATE_DIM.D_CURRENT_DAY,
+                             HIVE.DATE_DIM.D_CURRENT_WEEK,
+                             HIVE.DATE_DIM.D_CURRENT_MONTH,
+                             HIVE.DATE_DIM.D_CURRENT_QUARTER,
+                             HIVE.DATE_DIM.D_CURRENT_YEAR
+  input_variables ........ %(2016-01-27)
+
+
+HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.DATE_DIM
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST ........... 0.01
+DESCRIPTION
+  max_card_est ........... 1
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  scan_type .............. subset scan of table HIVE.HIVE.DATE_DIM
+  object_type ............ Hive_Text
+  scan_direction ......... forward
+  lock_mode .............. not specified, defaulted to lock cursor
+  access_mode ............ not specified, defaulted to read committed
+  columns_retrieved ..... 28
+  executor_predicates .... (HIVE.DATE_DIM.D_DATE = %(2016-01-27))
+
+--- SQL operation complete.
+>>
+>>drop external table if exists date_dim for hive.hive.date_dim;
+
+--- SQL operation complete.
+>>create external table date_dim 
++>  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
++>  for hive.hive.date_dim;
+
+--- SQL operation complete.
+>>invoke hive.hive.date_dim;
+
+-- Definition of hive table DATE_DIM
+-- Definition current  Thu Jun 16 19:45:27 2016
+
+  (
+    D_DATE_SK                        INT
+  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_DATE                           DATE
+  , D_MONTH_SEQ                      INT
+  , D_WEEK_SEQ                       INT
+  , D_QUARTER_SEQ                    INT
+  , D_YEAR                           INT
+  , D_DOW                            INT
+  , D_MOY                            INT
+  , D_DOM                            INT
+  , D_QOY                            INT
+  , D_FY_YEAR                        INT
+  , D_FY_QUARTER_SEQ                 INT
+  , D_FY_WEEK_SEQ                    INT
+  , D_DAY_NAME                       VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_QUARTER_NAME                   VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_HOLIDAY                        VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_WEEKEND                        VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_FOLLOWING_HOLIDAY              VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_FIRST_DOM                      INT
+  , D_LAST_DOM                       INT
+  , D_SAME_DAY_LY                    INT
+  , D_SAME_DAY_LQ                    INT
+  , D_CURRENT_DAY                    VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_WEEK                   VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_MONTH                  VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_QUARTER                VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  , D_CURRENT_YEAR                   VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT
+  )
+  /* stored as textfile */
+
+--- SQL operation complete.
+>>showddl hive.hive.date_dim;
+
+/* Hive DDL */
+CREATE TABLE DATE_DIM
+  (
+    D_DATE_SK                        int
+  , D_DATE_ID                        string
+  , D_DATE                           timestamp
+  , D_MONTH_SEQ                      int
+  , D_WEEK_SEQ                       int
+  , D_QUARTER_SEQ                    int
+  , D_YEAR                           int
+  , D_DOW                            int
+  , D_MOY                            int
+  , D_DOM                            int
+  , D_QOY                            int
+  , D_FY_YEAR                        int
+  , D_FY_QUARTER_SEQ                 int
+  , D_FY_WEEK_SEQ                    int
+  , D_DAY_NAME                       string
+  , D_QUARTER_NAME                   string
+  , D_HOLIDAY                        string
+  , D_WEEKEND                        string
+  , D_FOLLOWING_HOLIDAY              string
+  , D_FIRST_DOM                      int
+  , D_LAST_DOM                       int
+  , D_SAME_DAY_LY                    int
+  , D_SAME_DAY_LQ                    int
+  , D_CURRENT_DAY                    string
+  , D_CURRENT_WEEK                   string
+  , D_CURRENT_MONTH                  string
+  , D_CURRENT_QUARTER                string
+  , D_CURRENT_YEAR                   string
+  )
+  stored as textfile
+;
+
+/* Trafodion DDL */
+
+CREATE EXTERNAL TABLE DATE_DIM
+  (
+    D_DATE_SK                        INT DEFAULT NULL
+  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_DATE                           DATE DEFAULT NULL
+  , D_MONTH_SEQ                      INT DEFAULT NULL
+  , D_WEEK_SEQ                       INT DEFAULT NULL
+  , D_QUARTER_SEQ                    INT DEFAULT NULL
+  , D_YEAR                           INT DEFAULT NULL
+  , D_DOW                            INT DEFAULT NULL
+  , D_MOY                            INT DEFAULT NULL
+  , D_DOM                            INT DEFAULT NULL
+  , D_QOY                            INT DEFAULT NULL
+  , D_FY_YEAR                        INT DEFAULT NULL
+  , D_FY_QUARTER_SEQ                 INT DEFAULT NULL
+  , D_FY_WEEK_SEQ                    INT DEFAULT NULL
+  , D_DAY_NAME                       VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_QUARTER_NAME                   VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_HOLIDAY                        VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_WEEKEND                        VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_FOLLOWING_HOLIDAY              VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_FIRST_DOM                      INT DEFAULT NULL
+  , D_LAST_DOM                       INT DEFAULT NULL
+  , D_SAME_DAY_LY                    INT DEFAULT NULL
+  , D_SAME_DAY_LQ                    INT DEFAULT NULL
+  , D_CURRENT_DAY                    VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_WEEK                   VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_MONTH                  VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_QUARTER                VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  , D_CURRENT_YEAR                   VARCHAR(20 BYTES) CHARACTER SET UTF8
+      COLLATE DEFAULT DEFAULT NULL
+  )
+  FOR HIVE.HIVE.DATE_DIM
+;
+
+--- SQL operation complete.
+>>prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';
+
+*** WARNING[6008] Statistics for column (D_DATE) from table HIVE.HIVE.DATE_DIM were not available. As a result, the access path chosen might not be the best possible.
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212332866330134922
+ROWS_OUT ................. 1
+EST_TOTAL_COST ........... 0.01
+STATEMENT ................ select *
+                           from hive.hive.date_dim
+                           where d_date = date '2016-01-27';
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ........... 0.01
+DESCRIPTION
+  max_card_est ........... 1
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  statement_index ........ 0
+  affinity_value ......... 0
+  max_max_cardinality .... 1
+  total_overflow_size .... 0.00 KB
+  xn_access_mode ......... read_only
+  xn_autoabort_interval    0
+  auto_query_retry ....... enabled
+  plan_version ....... 2,600
+  embedded_arkcmp ........ used
+  IS_SQLCI ............... ON
+  LDAP_USERNAME
+  HIVE_MAX_STRING_LENGTH  20
+  SHOWCONTROL_SHOW_ALL ... OFF
+  SCHEMA ................. TRAFODION.SCH
+  VOLATILE_TABLE_FIND_SUI  SYSTEM
+  select_list ............ HIVE.DATE_DIM.D_DATE_SK, HIVE.DATE_DIM.D_DATE_ID,
+                             %(2016-01-27), HIVE.DATE_DIM.D_MONTH_SEQ,
+                             HIVE.DATE_DIM.D_WEEK_SEQ,
+                             HIVE.DATE_DIM.D_QUARTER_SEQ, HIVE.DATE_DIM.D_YEAR,
+                             HIVE.DATE_DIM.D_DOW, HIVE.DATE_DIM.D_MOY,
+                             HIVE.DATE_DIM.D_DOM, HIVE.DATE_DIM.D_QOY,
+                             HIVE.DATE_DIM.D_FY_YEAR,
+                             HIVE.DATE_DIM.D_FY_QUARTER_SEQ,
+                             HIVE.DATE_DIM.D_FY_WEEK_SEQ,
+                             HIVE.DATE_DIM.D_DAY_NAME,
+                             HIVE.DATE_DIM.D_QUARTER_NAME,
+                             HIVE.DATE_DIM.D_HOLIDAY, HIVE.DATE_DIM.D_WEEKEND,
+                             HIVE.DATE_DIM.D_FOLLOWING_HOLIDAY,
+                             HIVE.DATE_DIM.D_FIRST_DOM,
+                             HIVE.DATE_DIM.D_LAST_DOM,
+                             HIVE.DATE_DIM.D_SAME_DAY_LY,
+                             HIVE.DATE_DIM.D_SAME_DAY_LQ,
+                             HIVE.DATE_DIM.D_CURRENT_DAY,
+                             HIVE.DATE_DIM.D_CURRENT_WEEK,
+                             HIVE.DATE_DIM.D_CURRENT_MONTH,
+                             HIVE.DATE_DIM.D_CURRENT_QUARTER,
+                             HIVE.DATE_DIM.D_CURRENT_YEAR
+  input_variables ........ %(2016-01-27)
+
+
+HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.DATE_DIM
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST ........... 0.01
+DESCRIPTION
+  max_card_est ........... 1
+  fragment_id ............ 0
+  parent_frag ............ (none)
+  fragment_type .......... master
+  scan_type .............. subset scan of table HIVE.HIVE.DATE_DIM
+  object_type ............ Hive_Text
+  scan_direction ......... forward
+  lock_mode .............. not specified, defaulted to lock cursor
+  access_mode ............ not specified, defaulted to read committed
+  columns_retrieved ..... 28
+  executor_predicates .... (HIVE.DATE_DIM.D_DATE = %(2016-01-27))
+
+--- SQL operation complete.
+>>
+>>
+>>-- error cases
+>>drop external table if exists date_dim for hive.hive.date_dim;
+
+--- SQL operation complete.
+>>
+>>-- column d_date_skk doesn't exist in native hive table
+>>create external table date_dim 
++>  (d_date_skk int)
++>  for hive.hive.date_dim;
+
+*** ERROR[1009] Column D_DATE_SKK does not exist in the specified table.
+
+--- SQL operation failed with errors.
+>>
+>>-- cannot have primary key on hive/text tables
+>>drop external table if exists store_sales for hive.hive.store_sales;
+
+--- SQL operation complete.
+>>create external table store_sales 
++>  for hive.hive.store_sales primary key (ss_item_sk);
+
+*** ERROR[3242] This statement is not supported. Reason: Cannot specify key attribute for external tables.
+
+--- SQL operation failed with errors.
+>>
+>>-- column d_date_sk has incompatible type
+>>create external table date_dim 
++>  (d_date_sk date)
++>  for hive.hive.date_dim;
+
+--- SQL operation complete.
 >>log;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b39f7524/core/sql/regress/hive/FILTER009
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/FILTER009 b/core/sql/regress/hive/FILTER009
new file mode 100755
index 0000000..6c0e15a
--- /dev/null
+++ b/core/sql/regress/hive/FILTER009
@@ -0,0 +1,49 @@
+#! /bin/sh
+# @@@ START COPYRIGHT @@@
+#
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+# @@@ END COPYRIGHT @@@
+
+
+fil=$1
+if [ "$fil" = "" ]; then
+  echo "Usage: $0 filename"
+  exit 1
+fi
+
+sed "
+s/^\([ ]*PLAN_ID[ ]*[\.]*\) .*/\1 removed/g
+s/Fragment ID: 0, Length: [0-9]*/Fragment ID: 0, Length: AnyLength/g
+s/^\([ ]*EST_TOTAL_COST[ ]*[\.]*\) .*/EST_TOTAL_COST... removed/g
+s/^\([ ]*EST_OPER_COST[ ]*[\.]*\) .*/EST_OPER_COST... removed/g
+s/^\(ROWS_OUT[ ]*[\.]*\) .*/ROWS_OUT... removed/g
+s/explain_plan_size = [0-9]*/explain_plan_size = removed/g
+s/max_card_est[ ]*[\.]* .*/max_card_est... removed/g
+s/max_max_cardinality[ ]*[\.]* .*/max_max_cardinality... removed/g
+s/max_max_cardinali[ ]*[\.]* .*/max_max_cardinality... removed/g
+s/max_max_cardinal[ ]*[\.]* .*/max_max_cardinality... removed/g
+s/est_memory_per_cpu[ ]*[\.]* .*/est_memory_per_cpu... removed/g
+s/probes[ ]*[\.]* .*/probes... removed/g
+s/duplicated_succ_pr[ ]*[\.]* .*/duplicated_succ_pr... removed/g
+s/rows_accessed[ ]*[\.]* .*/rows_accessed... removed/g
+s/num_cache_entries[ ]* .*/num_cache_entries... removed/g
+s/num_inner_tuples[ ]* .*/num_inner_tuples... removed/g
+s/REQUESTS_IN[ ]*[\.]* .*/REQUESTS_IN... removed/g
+s/ROWS\/REQUEST[ ]*[\.]* .*/ROWS\/REQUESTS... removed/g
+" $fil

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b39f7524/core/sql/regress/hive/TEST009
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/TEST009 b/core/sql/regress/hive/TEST009
index 1fe9641..11a4210 100755
--- a/core/sql/regress/hive/TEST009
+++ b/core/sql/regress/hive/TEST009
@@ -38,6 +38,7 @@ obey TEST009(create_db);
 obey TEST009(test_hive1);
 obey TEST009(test_hive2);
 obey TEST009(test_hbase);
+obey TEST009(test_ext);
 log;
 obey TEST009(clean_up);
 exit;
@@ -45,6 +46,9 @@ exit;
 ?section clean_up
 
 -- remove messed up table
+drop external table t009t1 for hive.sch_t009.t009t1;
+drop external table t009t2 for hive.sch_t009.t009t2;
+
 cleanup table "_HV_SCH_T009_".t009t1;
 -- remove schemas from Trafodion
 drop schema if exists "_HV_HIVE_" cascade;
@@ -55,6 +59,11 @@ drop schema if exists hive_t009 cascade;
 sh regrhadoop.ksh fs -rm   /user/hive/exttables/t009t1/*;
 sh regrhadoop.ksh fs -rm   /user/hive/exttables/t009t2/*;
 
+-- Remove external hive tables used by this test
+drop external table if exists customer for hive.hive.customer;
+drop external table if exists item for hive.hive.item;
+drop external table if exists promotion for hive.hive.promotion;
+
 -- drop hbase tables
 drop schema if exists "_HB__ROW__" cascade;
 drop schema if exists "_HB__CELL__" cascade;
@@ -69,6 +78,9 @@ drop schema "_HIVESTATS_" cascade;
 cleanup table "_HB__CELL__".t028tbl1;
 drop schema "_HB__CELL__" cascade;
 reset parserflags 131072;
+
+--cqd hive_use_ext_table_attrs 'ON';
+cqd hive_max_string_length '20';
  
 ?section create_db
 
@@ -97,7 +109,7 @@ create external table customer for hive.hive.customer;
 create external table item for hive.hive.item;
 create external table promotion for hive.hive.promotion;
 
--- should create an schema called "_HV_HIVE_"
+-- should see a schema called "_HV_HIVE_"
 execute get_hv_schemas;
 
 -- Verify tables were created
@@ -141,10 +153,11 @@ create table hive_customer like hive.hive.customer;
 create table newtable1 like hive.hive.customer;
 create external table seabase.customer like hive.hive.customer;
 create external table customer1 like hive.hive.customer;
-create table t009t1 like "_HV_SCH_T009_".t009t1;
 create table t009t2 as select * from "_HV_SCH_T009_".t009t2;
 
 -- this create succeeds
+create table t009t1 like "_HV_SCH_T009_".t009t1;
+drop table t009t1;
 create table t009t1 as select * from hive.sch_t009.t009t1;
 
 get tables;
@@ -163,7 +176,6 @@ sh regrhive.ksh -v -f $REGRTSTDIR/TEST009_b.hive.sql &> $REGRRUNDIR/LOG009_b.hiv
 
 -- should fail - column mismatch
 select count(*) from hive.sch_t009.t009t1;
-
 sh sqlci -i "TEST009(select_stmts)";
 
 -- drop and recreate external table
@@ -172,9 +184,12 @@ drop external table t009t1 for hive.sch_t009.t009t1;
 get tables in schema "_HV_SCH_T009_";
 select row_id, catalog_name, schema_name, object_name from table(natablecacheentries('user','local')) order by 2,3,4;
 create external table t009t1 for hive.sch_t009.t009t1;
+invoke hive.sch_t009.t009t1;
+showddl trafodion."_HV_SCH_T009_".t009t1;
 
 -- select should now succeed
 select count(*) from hive.sch_t009.t009t1;
+select * from hive.sch_t009.t009t1;
 
 ?section select_stmts
 log LOG009;
@@ -190,6 +205,8 @@ create external table "baseball" for hbase."_CELL_"."baseball";
 execute get_hb_schemas;
 
 -- Verify tables were created
+invoke trafodion."_HB__ROW__"."baseball";
+invoke trafodion."_HB__CELL__"."baseball";
 showddl trafodion."_HB__ROW__"."baseball";
 showddl trafodion."_HB__CELL__"."baseball";
 
@@ -204,3 +221,71 @@ select * from trafodion."_HB__ROW__"."baseball";
 drop external table trafodion."_HB__ROW__"."baseball";
 drop external table "baseball" for hbase."_CELL_"."baseball";
 
+?section test_ext
+-- test external table attributes
+set schema trafodion.sch;
+drop external table if exists store_sales for hive.hive.store_sales;
+create external table store_sales 
+  for hive.hive.store_sales;
+invoke hive.hive.store_sales;
+
+set schema hive.hive;
+prepare s from select * from store_sales where ss_item_sk = 1;
+explain s;
+
+-- join with nested join
+control query shape nested_join(scan(path 'CUSTOMER'), 
+      scan(path 'STORE_SALES'));
+prepare s from select * from customer, store_sales 
+    where store_sales.ss_item_sk = customer.c_customer_sk;
+explain options 'f' s;
+explain s;
+
+control query shape cut;
+set schema trafodion.sch;
+drop external table if exists date_dim for hive.hive.date_dim;
+cqd volatile_table_find_suitable_key 'SYSTEM';
+create external table date_dim 
+  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date, 
+   d_month_seq int, d_week_seq int, d_quarter_seq int, d_year int, d_dow int,
+   d_moy int, d_dom int, d_qoy int, d_fy_year int, d_fy_quarter_seq int,
+   d_fy_week_seq int,
+   d_day_name varchar(120 bytes) character set utf8, d_quarter_name varchar(200 bytes) character set utf8, d_holiday varchar(100 bytes) character set utf8,
+   d_weekend varchar(100 bytes) character set utf8, d_following_holiday varchar(100 bytes) character set utf8, 
+   d_first_dom int, d_last_dom int, d_same_day_ly int, d_same_day_lq int,
+   d_current_day varchar(100 bytes) character set utf8, d_current_week varchar(111 bytes) character set utf8,
+   d_current_month varchar(200 bytes) character set utf8, d_current_quarter varchar(100 bytes) character set utf8, 
+   d_current_year varchar(100 bytes) character set utf8)
+  for hive.hive.date_dim;
+invoke hive.hive.date_dim;
+showddl hive.hive.date_dim;
+prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';
+explain s;
+
+drop external table if exists date_dim for hive.hive.date_dim;
+create external table date_dim 
+  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
+  for hive.hive.date_dim;
+invoke hive.hive.date_dim;
+showddl hive.hive.date_dim;
+prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';
+explain s;
+
+
+-- error cases
+drop external table if exists date_dim for hive.hive.date_dim;
+
+-- column d_date_skk doesn't exist in native hive table
+create external table date_dim 
+  (d_date_skk int)
+  for hive.hive.date_dim;
+
+-- cannot have primary key on hive/text tables
+drop external table if exists store_sales for hive.hive.store_sales;
+create external table store_sales 
+  for hive.hive.store_sales primary key (ss_item_sk);
+
+-- column d_date_sk has incompatible type
+create external table date_dim 
+  (d_date_sk date)
+  for hive.hive.date_dim;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b39f7524/core/sql/regress/hive/TEST020
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/TEST020 b/core/sql/regress/hive/TEST020
index 58ec978..3c10b05 100644
--- a/core/sql/regress/hive/TEST020
+++ b/core/sql/regress/hive/TEST020
@@ -1,4 +1,4 @@
--- Tests for ORC file accedss
+-- Tests for ORC file access
 -- Added Nov 2014
 --
 -- @@@ START COPYRIGHT @@@

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b39f7524/core/sql/regress/privs2/EXPECTED129
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/EXPECTED129 b/core/sql/regress/privs2/EXPECTED129
index 6ce955a..848826e 100644
--- a/core/sql/regress/privs2/EXPECTED129
+++ b/core/sql/regress/privs2/EXPECTED129
@@ -604,7 +604,7 @@ X
 >>
 >>create view v3bd as select b,d from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -616,7 +616,7 @@ X
 
 >>create view v3b as select b from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -628,7 +628,7 @@ X
 
 >>create view v3d as select d from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -640,7 +640,7 @@ X
 
 >>create view v3bbbbbb (c1,c2,c3,c4,c5,c6) as select b,b,b,b,b,b from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -655,45 +655,45 @@ X
 >>
 >>create view v3ac as select a,c from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>create view v3a as select a from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>create view v3c as select c from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>
 >>create view v3 as select * from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>
 >>create view v3ab as select a,b from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>create view v3abcd as select a,b,c,d from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>create view v3bc as select b,c from t3;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -702,7 +702,7 @@ X
 >>
 >>create view v34bf as select b,f from t3, t4;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -714,7 +714,7 @@ X
 
 >>create view v34bdfg as select b,d,f,g from t3, t4;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -726,7 +726,7 @@ X
 
 >>create view v34bdfg2 (c1,c2,c3,c4) as select b,d,f,g from t3, t4;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -738,7 +738,7 @@ X
 
 >>create view v34gb as select g,b from t3, t4;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
@@ -753,19 +753,19 @@ X
 >>
 >>create view v34 as select * from t3,t4;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>create view v34af as select a,f from t3, t4;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 
 >>create view v34bh as select b,h from t3,t4;
 
-*** ERROR[4222] The DDL feature is not supported in this software version.
+*** ERROR[3242] This statement is not supported. Reason: DDL operations can only be done on trafodion or external tables.
 
 *** ERROR[8822] The statement was not prepared.
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b39f7524/core/sql/regress/seabase/EXPECTED003
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED003 b/core/sql/regress/seabase/EXPECTED003
index 16ca7b0..d526b7f 100644
--- a/core/sql/regress/seabase/EXPECTED003
+++ b/core/sql/regress/seabase/EXPECTED003
@@ -307,7 +307,7 @@ A     B     C    D
     A                                TINYINT
   , B                                TINYINT
   )
-   /* stored as text */
+   /* stored as textfile */
 
 --- SQL operation complete.
 >>select * from hive.hive.ttiny;



Mime
View raw message