You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by se...@apache.org on 2017/09/13 23:23:22 UTC
[04/15] incubator-trafodion git commit: Following changes are done in
BMO memory quota
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/00e3b874/core/sql/regress/hive/EXPECTED030
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/EXPECTED030 b/core/sql/regress/hive/EXPECTED030
new file mode 100644
index 0000000..7539f28
--- /dev/null
+++ b/core/sql/regress/hive/EXPECTED030
@@ -0,0 +1,2636 @@
+>>obey TEST030(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.
+>>cqd hive_use_ext_table_attrs 'ON';
+
+--- SQL operation complete.
+>>cqd hist_missing_stats_warning_level '0';
+
+--- SQL operation complete.
+>>cqd ORC_NJS_PROBES_THRESHOLD '1000000';
+
+--- SQL operation complete.
+>>cqd HIVE_MIN_NUM_ESPS_PER_DATANODE '0';
+
+--- 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 TEST030(tests);
+>>--------------------------------------------------------------------------
+>>-- ORC file metadata info
+>>invoke hive.hive.store_orc;
+
+-- Definition of hive table STORE_ORC
+-- Definition current Thu Mar 16 02:23:43 2017
+
+ (
+ S_STORE_SK INT
+ , S_STORE_ID VARCHAR(20 BYTES) CHARACTER SET UTF8
+ COLLATE DEFAULT
+ , S_REC_START_DATE DATE
+ , S_REC_END_DATE DATE
+ , 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 1997-03-13 ? 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 order by s_store_sk;
+
+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 1997-03-13 ? 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 1997-03-13 2000-03-12 ? 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 2000-03-13 ? ? 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 1997-03-13 1999-03-13 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 1999-03-14 2001-03-12 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 2001-03-13 ? ? 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 1997-03-13 ? ? 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 1997-03-13 2000-03-12 ? 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 2000-03-13 ? ? 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 1997-03-13 1999-03-13 ? 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 1999-03-14 2001-03-12 ? 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-001
+ 12 AAAAAAAAKAAAAAAA 2001-03-13 ? ? 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 ORC_SCAN ? ? STORE_ORC
+01 ORC_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 HYBRID_HASH_JOIN 2 1
+02 HIVE_SCAN ? ? STORE
+01 ORC_SCAN ? ? STORE_ORC
+
+--- 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.
+>>
+>>-- column list pushdown test
+>>cqd orc_columns_pushdown 'OFF';
+
+--- SQL operation complete.
+>>showplan option 'tr' select s_store_sk from store_orc;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 12664
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 568
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
+Flag = 0000000100101001
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 00100000001100000000101000000000
+rtFlags2_ = 00000000000000000000000000000000
+rtFlags3_ = 0000000000000000
+rtFlags4_ = 00000000000000000010000000000000
+rtFlags5_ = 00000000000000000000000000000000
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 2992
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_ORC_SCAN [1]:
+----------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 448
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 41032
+estimatedRowUsed = 100, estimatedRowsAccessed = 100, expressionMode = 0
+Flag = 0000000000001001
+
+For ComTdbOrcScan :
+tableName_ = HIVE.HIVE.STORE_ORC
+hostName_ = localhost, port_ = 27000
+recordDelimiter_ = 10, columnDelimiter_ = 1
+hdfsBufSize_ = 115, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 8
+tuppIndex_ = 2, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 8
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 16
+convertSkipListSize_ = 33, convertSkipList_ = 3
+outputRowLength_ = 16
+Flag = 0000000000001100
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 3 2853 store_orc/000000_0
+
+Summary of bytes read per ESP (2853 = 100 percent):
+
+ESP 0 reads 2853 bytes ( 100 percent of avg)
+
+Number of columns to retrieve: 29
+ColNumber: 1, ColName: S_STORE_SK
+ColNumber: 2, ColName: S_STORE_ID
+ColNumber: 3, ColName: S_REC_START_DATE
+ColNumber: 4, ColName: S_REC_END_DATE
+ColNumber: 5, ColName: S_CLOSED_DATE_SK
+ColNumber: 6, ColName: S_STORE_NAME
+ColNumber: 7, ColName: S_NUMBER_EMPLOYEES
+ColNumber: 8, ColName: S_FLOOR_SPACE
+ColNumber: 9, ColName: S_HOURS
+ColNumber: 10, ColName: S_MANAGER
+ColNumber: 11, ColName: S_MARKET_ID
+ColNumber: 12, ColName: S_GEOGRAPHY_CLASS
+ColNumber: 13, ColName: S_MARKET_DESC
+ColNumber: 14, ColName: S_MARKET_MANAGER
+ColNumber: 15, ColName: S_DIVISION_ID
+ColNumber: 16, ColName: S_DIVISION_NAME
+ColNumber: 17, ColName: S_COMPANY_ID
+ColNumber: 18, ColName: S_COMPANY_NAME
+ColNumber: 19, ColName: S_STREET_NUMBER
+ColNumber: 20, ColName: S_STREET_NAME
+ColNumber: 21, ColName: S_STREET_TYPE
+ColNumber: 22, ColName: S_SUITE_NUMBER
+ColNumber: 23, ColName: S_CITY
+ColNumber: 24, ColName: S_COUNTY
+ColNumber: 25, ColName: S_STATE
+ColNumber: 26, ColName: S_ZIP
+ColNumber: 27, ColName: S_COUNTRY
+ColNumber: 28, ColName: S_GMT_OFFSET
+ColNumber: 29, ColName: S_TAX_PRECENTAGE
+hdfsRootDir: hdfs://localhost:27000/user/hive/warehouse/store_orc
+modTSforDir_ = 1473728428, numOfPartCols_ = 0
+
+# of Expressions = 6
+
+Expression: selectExpr_ is NULL
+Expression: moveExpr_ is not NULL
+Expression: convertExpr_ is NULL
+Expression: moveColsConvertExpr_ is not NULL
+Expression: partElimExpr_ is NULL
+Expression: orcOperExpr_ is NULL
+
+--- SQL operation complete.
+>>select s_store_sk from store_orc;
+
+S_STORE_SK
+-----------
+
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+
+--- 12 row(s) selected.
+>>cqd orc_columns_pushdown 'ON';
+
+--- SQL operation complete.
+>>showplan option 'tr' select s_store_sk from store_orc;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 10184
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 568
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
+Flag = 0000000100101001
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 00100000001100000000101000000000
+rtFlags2_ = 00000000000000000000000000000000
+rtFlags3_ = 0000000000000000
+rtFlags4_ = 00000000000000000010000000000000
+rtFlags5_ = 00000000000000000000000000000000
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 2984
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_ORC_SCAN [1]:
+----------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 448
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 41032
+estimatedRowUsed = 100, estimatedRowsAccessed = 100, expressionMode = 0
+Flag = 0000000000001001
+
+For ComTdbOrcScan :
+tableName_ = HIVE.HIVE.STORE_ORC
+hostName_ = localhost, port_ = 27000
+recordDelimiter_ = 10, columnDelimiter_ = 1
+hdfsBufSize_ = 115, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 8
+tuppIndex_ = 2, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 8
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 16
+convertSkipListSize_ = 33, convertSkipList_ = 3
+outputRowLength_ = 16
+Flag = 0000000000001100
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 3 2853 store_orc/000000_0
+
+Summary of bytes read per ESP (2853 = 100 percent):
+
+ESP 0 reads 2853 bytes ( 100 percent of avg)
+
+Number of columns to retrieve: 1
+ColNumber: 1, ColName: S_STORE_SK
+hdfsRootDir: hdfs://localhost:27000/user/hive/warehouse/store_orc
+modTSforDir_ = 1473728428, numOfPartCols_ = 0
+
+# of Expressions = 6
+
+Expression: selectExpr_ is NULL
+Expression: moveExpr_ is not NULL
+Expression: convertExpr_ is NULL
+Expression: moveColsConvertExpr_ is not NULL
+Expression: partElimExpr_ is NULL
+Expression: orcOperExpr_ is NULL
+
+--- SQL operation complete.
+>>select s_store_sk from store_orc;
+
+S_STORE_SK
+-----------
+
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+
+--- 12 row(s) selected.
+>>
+>>-- predicate pushdown to ORC layer
+>>cqd orc_pred_pushdown 'OFF';
+
+--- SQL operation complete.
+>>select s_store_sk from store_orc where s_store_sk = 3;
+
+S_STORE_SK
+-----------
+
+ 3
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk < 2;
+
+S_STORE_SK
+-----------
+
+ 1
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk >= 11;
+
+S_STORE_SK
+-----------
+
+ 11
+ 12
+
+--- 2 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk >= 4 and s_store_sk < 6;
+
+S_STORE_SK
+-----------
+
+ 4
+ 5
+
+--- 2 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk in (7,8,9);
+
+S_STORE_SK
+-----------
+
+ 7
+ 8
+ 9
+
+--- 3 row(s) selected.
+>>showplan option 'tr' select s_store_sk from store_orc where s_store_sk = 3;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 10504
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 568
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
+Flag = 0000000100101001
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 00100000001100000000101000000000
+rtFlags2_ = 00000000000000000000000000000000
+rtFlags3_ = 0000000000000000
+rtFlags4_ = 00000000000000000010000000000000
+rtFlags5_ = 00000000000000000000000000000000
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 3088
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_ORC_SCAN [1]:
+----------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 448
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840
+estimatedRowUsed = 100, estimatedRowsAccessed = 100, expressionMode = 0
+Flag = 0000000000001001
+
+For ComTdbOrcScan :
+tableName_ = HIVE.HIVE.STORE_ORC
+hostName_ = localhost, port_ = 27000
+recordDelimiter_ = 10, columnDelimiter_ = 1
+hdfsBufSize_ = 115, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 16
+tuppIndex_ = 3, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 8
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 8
+convertSkipListSize_ = 33, convertSkipList_ = 2
+outputRowLength_ = 8
+Flag = 0000000000001100
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 3 2853 store_orc/000000_0
+
+Summary of bytes read per ESP (2853 = 100 percent):
+
+ESP 0 reads 2853 bytes ( 100 percent of avg)
+
+Number of columns to retrieve: 1
+ColNumber: 1, ColName: S_STORE_SK
+hdfsRootDir: hdfs://localhost:27000/user/hive/warehouse/store_orc
+modTSforDir_ = 1473728428, numOfPartCols_ = 0
+
+# of Expressions = 6
+
+Expression: selectExpr_ is not NULL
+Expression: moveExpr_ is NULL
+Expression: convertExpr_ is not NULL
+Expression: moveColsConvertExpr_ is NULL
+Expression: partElimExpr_ is NULL
+Expression: orcOperExpr_ is NULL
+
+--- SQL operation complete.
+>>
+>>cqd orc_pred_pushdown 'ON';
+
+--- SQL operation complete.
+>>select s_store_sk from store_orc where s_store_sk = 3;
+
+S_STORE_SK
+-----------
+
+ 3
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk < 2;
+
+S_STORE_SK
+-----------
+
+ 1
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk >= 11;
+
+S_STORE_SK
+-----------
+
+ 11
+ 12
+
+--- 2 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk >= 4 and s_store_sk < 6;
+
+S_STORE_SK
+-----------
+
+ 4
+ 5
+
+--- 2 row(s) selected.
+>>select s_store_sk from store_orc where s_store_sk in (7,8,9);
+
+S_STORE_SK
+-----------
+
+ 7
+ 8
+ 9
+
+--- 3 row(s) selected.
+>>showplan option 'tr' select s_store_sk from store_orc where s_store_sk = 3;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 15648
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 568
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0, estimatedRowsAccessed = 0, expressionMode = 0
+Flag = 0000000100101001
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 00100000001100000000101000000000
+rtFlags2_ = 00000000000000000000000000000000
+rtFlags3_ = 0000000000000000
+rtFlags4_ = 00000000000000000010000000000000
+rtFlags5_ = 00000000000000000000000000000000
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 3184
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_ORC_SCAN [1]:
+----------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 448
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840
+estimatedRowUsed = 100, estimatedRowsAccessed = 100, expressionMode = 0
+Flag = 0000000000001001
+
+For ComTdbOrcScan :
+tableName_ = HIVE.HIVE.STORE_ORC
+hostName_ = localhost, port_ = 27000
+recordDelimiter_ = 10, columnDelimiter_ = 1
+hdfsBufSize_ = 115, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 16
+tuppIndex_ = 3, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 8
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 8
+convertSkipListSize_ = 33, convertSkipList_ = 2
+outputRowLength_ = 8
+Flag = 0000000000001100
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 3 2853 store_orc/000000_0
+
+Summary of bytes read per ESP (2853 = 100 percent):
+
+ESP 0 reads 2853 bytes ( 100 percent of avg)
+
+Number of columns to retrieve: 1
+ColNumber: 1, ColName: S_STORE_SK
+hdfsRootDir: hdfs://localhost:27000/user/hive/warehouse/store_orc
+modTSforDir_ = 1473728428, numOfPartCols_ = 0
+
+Number of PPI entries: 3
+PPI: #1
+ type: STARTAND(1)
+PPI: #2
+ type: EQUALS(5)
+ operAttrIndex: 0
+ colName_: S_STORE_SK
+PPI: #3
+ type: END(4)
+Num Of orcAllColInfoList entries: 33
+
+# of Expressions = 6
+
+Expression: selectExpr_ is not NULL
+Expression: moveExpr_ is NULL
+Expression: convertExpr_ is not NULL
+Expression: moveColsConvertExpr_ is NULL
+Expression: partElimExpr_ is NULL
+Expression: orcOperExpr_ is not NULL
+
+--- SQL operation complete.
+>>
+>>-- local join predicate is not pushed down
+>>explain select * from store_sales_orc where ss_sold_date_sk = ss_item_sk ;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... NOT NAMED
+PLAN_ID .................. 212356391057229402
+ROWS_OUT ......... 1,440,202
+EST_TOTAL_COST ......... 176.18
+STATEMENT ................ select *
+ from store_sales_orc
+ where ss_sold_date_sk = ss_item_sk;
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 1,440,202
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ......... 176.18
+DESCRIPTION
+ est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
+ max_card_est ........... 2.8804e+06
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 132
+ statement_index ........ 0
+ affinity_value ......... 0
+ max_max_cardinal 1,440,202
+ total_overflow_size .... 0.00 KB
+ esp_2_node_map ......... (\NSK:-1:-1:-1:-1)
+ 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 .......... NOT AVAILABLE
+ SCHEMA ................. HIVE.HIVE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_ORC_FORMAT ON
+ HIST_ROWCOUNT_REQU 50,000
+ HIVE_USE_EXT_TABLE_ATTR ON
+ HIST_MISSING_STATS_WARN 0
+ ORC_NJS_PROBES_T 1,000,000
+ HIVE_MIN_NUM_ESPS_PER_D 0
+ ORC_COLUMNS_PUSHDOWN ... ON
+ ORC_PRED_PUSHDOWN ...... ON
+ GENERATE_EXPLAIN ....... ON
+ select_list ............ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK,
+ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK,
+ HIVE.STORE_SALES_ORC.SS_CDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_HDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_ADDR_SK,
+ HIVE.STORE_SALES_ORC.SS_STORE_SK,
+ HIVE.STORE_SALES_ORC.SS_PROMO_SK,
+ HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER,
+ HIVE.STORE_SALES_ORC.SS_QUANTITY,
+ HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT,
+ HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_TAX,
+ HIVE.STORE_SALES_ORC.SS_COUPON_AMT,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX,
+ HIVE.STORE_SALES_ORC.SS_NET_PROFIT
+
+
+ESP_EXCHANGE ============================== SEQ_NO 2 ONLY CHILD 1
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 1,440,202
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST ......... 176.18
+DESCRIPTION
+ max_card_est ........... 2.8804e+06
+ fragment_id ............ 2
+ parent_frag ............ 0
+ fragment_type .......... esp
+ est_memory_per_node .... 153.262 KB
+ record_length ........ 132
+ buffer_size ....... 30,388
+ parent_processes ....... 1
+ child_processes ........ 4
+ child_partitioning_func hash2 partitioned 4 ways on (randomNum)
+
+
+ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 1,440,202
+EST_OPER_COST .......... 176.18
+EST_TOTAL_COST ......... 176.18
+DESCRIPTION
+ max_card_est ........... 2.8804e+06
+ fragment_id ............ 2
+ parent_frag ............ 0
+ fragment_type .......... esp
+ record_length ........ 132
+ scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC
+ object_type ............ Hive_Orc
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 23
+ probes ................. 1
+ rows_accessed .......... 2.8804e+06
+ executor_predicates .... (HIVE.STORE_SALES_ORC.SS_ITEM_SK =
+ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK)
+
+--- SQL operation complete.
+>>
+>>-- Boolean constant predicate is not pushed down
+>>explain select * from store_sales_orc where 1=2;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... NOT NAMED
+PLAN_ID .................. 212356391057807027
+ROWS_OUT ................. 1
+EST_TOTAL_COST ........... 0.01
+STATEMENT ................ select * from store_sales_orc where 1=2;
+
+
+------------------------------------------------------------------ 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
+ est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
+ max_card_est ........... 0
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 138
+ 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 .......... NOT AVAILABLE
+ SCHEMA ................. HIVE.HIVE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_ORC_FORMAT ON
+ HIST_ROWCOUNT_REQU 50,000
+ HIVE_USE_EXT_TABLE_ATTR ON
+ HIST_MISSING_STATS_WARN 0
+ ORC_NJS_PROBES_T 1,000,000
+ HIVE_MIN_NUM_ESPS_PER_D 0
+ ORC_COLUMNS_PUSHDOWN ... ON
+ ORC_PRED_PUSHDOWN ...... ON
+ GENERATE_EXPLAIN ....... ON
+ select_list ............ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK,
+ HIVE.STORE_SALES_ORC.SS_ITEM_SK,
+ HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK,
+ HIVE.STORE_SALES_ORC.SS_CDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_HDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_ADDR_SK,
+ HIVE.STORE_SALES_ORC.SS_STORE_SK,
+ HIVE.STORE_SALES_ORC.SS_PROMO_SK,
+ HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER,
+ HIVE.STORE_SALES_ORC.SS_QUANTITY,
+ HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT,
+ HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_TAX,
+ HIVE.STORE_SALES_ORC.SS_COUPON_AMT,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX,
+ HIVE.STORE_SALES_ORC.SS_NET_PROFIT
+
+
+ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST ........... 0.01
+DESCRIPTION
+ max_card_est ........... 0
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 138
+ scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC
+ object_type ............ Hive_Orc
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 23
+ probes ................. 1
+ rows_accessed .......... 1
+ part_elim_compiled ..... 0.
+
+--- SQL operation complete.
+>>
+>>-- aggregate pushdown to ORC layer
+>>cqd orc_aggr_pushdown 'ON';
+
+--- SQL operation complete.
+>>explain options 'f' select count(*) from store_orc;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 orc_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select count(*) from store_orc;
+
+(EXPR)
+--------------------
+
+ 12
+
+--- 1 row(s) selected.
+>>select count(*), count(*) from store_orc;
+
+(EXPR) (EXPR)
+-------------------- --------------------
+
+ 12 12
+
+--- 1 row(s) selected.
+>>select min(s_store_sk) from store_orc;
+
+(EXPR)
+-----------
+
+ 1
+
+--- 1 row(s) selected.
+>>select max(s_store_sk) from store_orc;
+
+(EXPR)
+-----------
+
+ 12
+
+--- 1 row(s) selected.
+>>select sum(s_store_sk) from store_orc;
+
+(EXPR)
+--------------------
+
+ 78
+
+--- 1 row(s) selected.
+>>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk)
++> from store_orc;
+
+(EXPR) (EXPR) (EXPR) (EXPR)
+-------------------- ----------- ----------- --------------------
+
+ 12 1 12 78
+
+--- 1 row(s) selected.
+>>explain options 'f' select count(*), min(s_store_sk), max(s_store_sk),
++> sum(s_store_sk) from store_orc;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 orc_aggr 1.00E+000
+
+--- SQL operation complete.
+>>
+>>explain options 'f'
++> select count(*) from hive.hive.store_orc union all
++> select count(*) from hive.hive.store_orc;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+3 . 4 root 2.00E+000
+1 2 3 merge_union 2.00E+000
+. . 2 orc_aggr 1.00E+000
+. . 1 orc_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select count(*) from hive.hive.store_orc union all
++> select count(*) from hive.hive.store_orc;
+
+(EXPR)
+--------------------
+
+ 12
+ 12
+
+--- 2 row(s) selected.
+>>
+>>explain options 'f'
++> select min(s_store_sk) from hive.hive.store_orc union all
++> select min(s_store_sk) from hive.hive.store_orc;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+3 . 4 root 2.00E+000
+1 2 3 merge_union 2.00E+000
+. . 2 orc_aggr 1.00E+000
+. . 1 orc_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select min(s_store_sk) from hive.hive.store_orc union all
++> select min(s_store_sk) from hive.hive.store_orc;
+
+(EXPR)
+-----------
+
+ 1
+ 1
+
+--- 2 row(s) selected.
+>>
+>>explain options 'f' select count(s_store_sk) from store_orc;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+2 . 3 root 1.00E+000
+1 . 2 sort_scalar_aggr 1.00E+000
+. . 1 orc_scan STORE_ORC 1.00E+002
+
+--- SQL operation complete.
+>>select count(s_store_sk) from store_orc;
+
+(EXPR)
+--------------------
+
+ 12
+
+--- 1 row(s) selected.
+>>
+>>explain options 'f' select count(*) from store_orc
++> having sum(s_store_sk) = 78;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 orc_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select count(*) from store_orc having sum(s_store_sk) = 78;
+
+(EXPR)
+--------------------
+
+ 12
+
+--- 1 row(s) selected.
+>>
+>>select count(*) from store_orc having sum(s_store_sk) = 77;
+
+--- 0 row(s) selected.
+>>
+>>cqd orc_aggr_pushdown 'OFF';
+
+--- SQL operation complete.
+>>explain options 'f' select count(*), min(s_store_sk), max(s_store_sk),
++> sum(s_store_sk) from store_orc;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+2 . 3 root 1.00E+000
+1 . 2 sort_scalar_aggr 1.00E+000
+. . 1 orc_scan STORE_ORC 1.00E+002
+
+--- SQL operation complete.
+>>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk)
++> from store_orc;
+
+(EXPR) (EXPR) (EXPR) (EXPR)
+-------------------- ----------- ----------- --------------------
+
+ 12 1 12 78
+
+--- 1 row(s) selected.
+>>
+>>-- test query cache
+>>prepare xx from select cd_gender from customer_demographics_orc where cd_gender = 'male' ;
+
+--- SQL command prepared.
+>>select num_hits, num_params from table(querycacheentries('user', 'local'))
++>where substring(text, 1, 16) = 'select cd_gender' order by 1,2;
+
+NUM_HITS NUM_PARAMS
+---------- ----------
+
+ 0 1
+
+--- 1 row(s) selected.
+>>
+>>prepare xx from select cd_gender from customer_demographics_orc where cd_gender = 'female' ;
+
+--- SQL command prepared.
+>>select num_hits, num_params from table(querycacheentries('user', 'local'))
++>where substring(text, 1, 16) = 'select cd_gender' order by 1,2;
+
+NUM_HITS NUM_PARAMS
+---------- ----------
+
+ 1 1
+
+--- 1 row(s) selected.
+>>
+>>
+>>-- test external table attributes
+>>set schema trafodion.sch;
+
+--- SQL operation complete.
+>>drop external table if exists store_sales_orc for hive.hive.store_sales_orc;
+
+--- SQL operation complete.
+>>create external table store_sales_orc
++> for hive.hive.store_sales_orc;
+
+--- SQL operation complete.
+>>invoke hive.hive.store_sales_orc;
+
+-- Definition of hive table STORE_SALES_ORC
+-- Definition current Thu Mar 16 02:24:38 2017
+
+ (
+ 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 orc */
+
+--- SQL operation complete.
+>>
+>>set schema hive.hive;
+
+--- SQL operation complete.
+>>prepare s from select * from store_sales_orc where ss_item_sk = 1;
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212356391078690910
+ROWS_OUT ............. 1,698
+EST_TOTAL_COST ......... 176.17
+STATEMENT ................ select * from store_sales_orc where ss_item_sk = 1;
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
+REQUESTS_IN .............. 1
+ROWS_OUT ............. 1,698
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ......... 176.17
+DESCRIPTION
+ est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
+ max_card_est ........... 2.73638e+06
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 138
+ statement_index ........ 0
+ affinity_value ......... 0
+ max_max_cardinality 1,698
+ total_overflow_size .... 0.00 KB
+ esp_2_node_map ......... (\NSK:-1:-1:-1:-1)
+ 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 .......... NOT AVAILABLE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_ORC_FORMAT ON
+ HIST_ROWCOUNT_REQU 50,000
+ HIVE_USE_EXT_TABLE_ATTR ON
+ HIST_MISSING_STATS_WARN 0
+ ORC_NJS_PROBES_T 1,000,000
+ HIVE_MIN_NUM_ESPS_PER_D 0
+ ORC_COLUMNS_PUSHDOWN ... ON
+ ORC_PRED_PUSHDOWN ...... ON
+ ORC_AGGR_PUSHDOWN ...... OFF
+ SCHEMA ................. HIVE.HIVE
+ select_list ............ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK, %(1),
+ HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK,
+ HIVE.STORE_SALES_ORC.SS_CDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_HDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_ADDR_SK,
+ HIVE.STORE_SALES_ORC.SS_STORE_SK,
+ HIVE.STORE_SALES_ORC.SS_PROMO_SK,
+ HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER,
+ HIVE.STORE_SALES_ORC.SS_QUANTITY,
+ HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT,
+ HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_TAX,
+ HIVE.STORE_SALES_ORC.SS_COUPON_AMT,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX,
+ HIVE.STORE_SALES_ORC.SS_NET_PROFIT
+ input_variables ........ %(1)
+
+
+ESP_EXCHANGE ============================== SEQ_NO 2 ONLY CHILD 1
+REQUESTS_IN .............. 1
+ROWS_OUT ............. 1,698
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST ......... 176.17
+DESCRIPTION
+ max_card_est ........... 2.73638e+06
+ fragment_id ............ 2
+ parent_frag ............ 0
+ fragment_type .......... esp
+ est_memory_per_node .... 29.297 KB
+ record_length ........ 132
+ buffer_size ........ 5,000
+ parent_processes ....... 1
+ child_processes ........ 4
+ child_partitioning_func hash2 partitioned 4 ways on (randomNum)
+
+
+ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC
+REQUESTS_IN .............. 1
+ROWS_OUT ............. 1,698
+EST_OPER_COST .......... 176.17
+EST_TOTAL_COST ......... 176.17
+DESCRIPTION
+ max_card_est ........... 2.73638e+06
+ fragment_id ............ 2
+ parent_frag ............ 0
+ fragment_type .......... esp
+ record_length ........ 132
+ scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC
+ object_type ............ Hive_Orc
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 23
+ probes ................. 1
+ rows_accessed .......... 2.8804e+06
+ orc_pred_pushdown ...... yes
+ orc_search_arguments ... and( HIVE.STORE_SALES_ORC.SS_ITEM_SK = cast(%(1)) )
+ executor_predicates .... (HIVE.STORE_SALES_ORC.SS_ITEM_SK = %(1))
+
+--- SQL operation complete.
+>>
+>>-- join with nested join
+>>cqd orc_njs 'on';
+
+--- SQL operation complete.
+>>control query shape nested_join(scan(path 'CUSTOMER_ORC'),
++> scan(path 'STORE_SALES_ORC'));
+
+--- SQL operation complete.
+>>prepare s from select * from customer_orc, store_sales_orc
++> where store_sales_orc.ss_item_sk = customer_orc.c_customer_sk;
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+3 . 4 root 2.88E+006
+1 2 3 nested_join 2.88E+006
+. . 2 orc_scan STORE_SALES_ORC 2.88E+001
+. . 1 orc_scan CUSTOMER_ORC 1.00E+005
+
+--- SQL operation complete.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212356391080900762
+ROWS_OUT ......... 2,880,404
+EST_TOTAL_COST ... 9,688,410
+STATEMENT ................ select *
+ from customer_orc, store_sales_orc
+ where store_sales_orc.ss_item_sk =
+ customer_orc.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,880,404
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ... 9,688,410
+DESCRIPTION
+ est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
+ max_card_est ........... 2.8804e+06
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 402
+ statement_index ........ 0
+ affinity_value ......... 0
+ max_max_cardinal 2,880,404
+ 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 .......... NOT AVAILABLE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_ORC_FORMAT ON
+ HIST_ROWCOUNT_REQU 50,000
+ HIVE_USE_EXT_TABLE_ATTR ON
+ HIST_MISSING_STATS_WARN 0
+ ORC_NJS_PROBES_T 1,000,000
+ HIVE_MIN_NUM_ESPS_PER_D 0
+ ORC_COLUMNS_PUSHDOWN ... ON
+ ORC_PRED_PUSHDOWN ...... ON
+ ORC_AGGR_PUSHDOWN ...... OFF
+ SCHEMA ................. HIVE.HIVE
+ ORC_NJS ................ ON
+ select_list ............ HIVE.CUSTOMER_ORC.C_CUSTOMER_SK,
+ HIVE.CUSTOMER_ORC.C_CUSTOMER_ID,
+ HIVE.CUSTOMER_ORC.C_CURRENT_CDEMO_SK,
+ HIVE.CUSTOMER_ORC.C_CURRENT_HDEMO_SK,
+ HIVE.CUSTOMER_ORC.C_CURRENT_ADDR_SK,
+ HIVE.CUSTOMER_ORC.C_FIRST_SHIPTO_DATE_SK,
+ HIVE.CUSTOMER_ORC.C_FIRST_SALES_DATE_SK,
+ HIVE.CUSTOMER_ORC.C_SALUTATION,
+ HIVE.CUSTOMER_ORC.C_FIRST_NAME,
+ HIVE.CUSTOMER_ORC.C_LAST_NAME,
+ HIVE.CUSTOMER_ORC.C_PREFERRED_CUST_FLAG,
+ HIVE.CUSTOMER_ORC.C_BIRTH_DAY,
+ HIVE.CUSTOMER_ORC.C_BIRTH_MONTH,
+ HIVE.CUSTOMER_ORC.C_BIRTH_YEAR,
+ HIVE.CUSTOMER_ORC.C_BIRTH_COUNTRY,
+ HIVE.CUSTOMER_ORC.C_LOGIN,
+ HIVE.CUSTOMER_ORC.C_EMAIL_ADDRESS,
+ HIVE.CUSTOMER_ORC.C_LAST_REVIEW_DATE,
+ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK,
+ HIVE.CUSTOMER_ORC.C_CUSTOMER_SK,
+ HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK,
+ HIVE.STORE_SALES_ORC.SS_CDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_HDEMO_SK,
+ HIVE.STORE_SALES_ORC.SS_ADDR_SK,
+ HIVE.STORE_SALES_ORC.SS_STORE_SK,
+ HIVE.STORE_SALES_ORC.SS_PROMO_SK,
+ HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER,
+ HIVE.STORE_SALES_ORC.SS_QUANTITY,
+ HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT,
+ HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST,
+ HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE,
+ HIVE.STORE_SALES_ORC.SS_EXT_TAX,
+ HIVE.STORE_SALES_ORC.SS_COUPON_AMT,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID,
+ HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX,
+ HIVE.STORE_SALES_ORC.SS_NET_PROFIT
+
+
+NESTED_JOIN =============================== SEQ_NO 3 CHILDREN 1, 2
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 2,880,404
+EST_OPER_COST ............ 0.2
+EST_TOTAL_COST ... 9,688,410
+DESCRIPTION
+ max_card_est ........... 2.8804e+06
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 402
+ join_type .............. inner
+ join_method ............ nested
+
+
+ORC_SCAN ================================== SEQ_NO 2 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC
+REQUESTS_IN ........ 100,000
+ROWS/REQUEST ............ 28.8
+EST_OPER_COST .... 9,688,403
+EST_TOTAL_COST ... 9,688,403
+DESCRIPTION
+ max_card_est ........... 2.8804e+06
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 132
+ scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC
+ object_type ............ Hive_Orc
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 23
+ probes ........... 100,000
+ successful_probes 100,000
+ unique_probes ...... 5,000
+ duplicated_succ_pr 95,000
+ rows_accessed .......... 2.8804e+06
+ orc_pred_pushdown ...... yes
+ orc_search_arguments ... and( HIVE.STORE_SALES_ORC.SS_ITEM_SK =
+ cast(HIVE.CUSTOMER_ORC.C_CUSTOMER_SK) )
+ executor_predicates .... (HIVE.STORE_SALES_ORC.SS_ITEM_SK =
+ HIVE.CUSTOMER_ORC.C_CUSTOMER_SK)
+
+
+ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.CUSTOMER_ORC
+REQUESTS_IN .............. 1
+ROWS_OUT ........... 100,000
+EST_OPER_COST ............ 6.3
+EST_TOTAL_COST ........... 6.3
+DESCRIPTION
+ max_card_est ..... 100,000
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 270
+ scan_type .............. full scan of table HIVE.HIVE.CUSTOMER_ORC
+ object_type ............ Hive_Orc
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 18
+ probes ................. 1
+ rows_accessed .... 100,000
+ orc_pred_pushdown ...... yes
+ orc_search_arguments ... and( not( HIVE.CUSTOMER_ORC.C_CUSTOMER_SK is null )
+ )
+ executor_predicates .... HIVE.CUSTOMER_ORC.C_CUSTOMER_SK is not null
+
+--- SQL operation complete.
+>>cqd orc_njs reset;
+
+--- SQL operation complete.
+>>
+>>-- join with parallel nested join
+>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>cqd HIVE_USE_EXT_TABLE_ATTRS 'off';
+
+--- SQL operation complete.
+>>cqd ncm_orc_costing 'on';
+
+--- SQL operation complete.
+>>cqd orc_njs 'on';
+
+--- SQL operation complete.
+>>cqd parallel_num_esps '4';
+
+--- SQL operation complete.
+>>
+>>prepare s from
++>select [last 0] ss_net_profit from
++>date_dim_orc dim, store_sales_sorted_orc ss
++>where
++>dim.d_date_sk = ss.ss_sold_date_sk
++>and d_year in (2001) and d_dom = 30 -- produce 12 rows
++>;
+
+--- SQL command prepared.
+>>
+>>explain options 'f' s;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+6 . 7 root 1.00E+000
+5 . 6 firstn 1.00E+000
+4 . 5 esp_exchange 1:4(hash2) 2.88E+006
+3 2 4 hybrid_hash_join 2.88E+006
+. . 3 orc_scan STORE_SALES_SORTED_O 2.88E+006
+1 . 2 esp_exchange 4(rep-b):2(hash2) 2.00E+000
+. . 1 orc_scan DATE_DIM_ORC 2.00E+000
+
+--- SQL operation complete.
+>>
+>>cqd HIVE_USE_EXT_TABLE_ATTRS reset;
+
+--- SQL operation complete.
+>>cqd ncm_orc_costing reset;
+
+--- SQL operation complete.
+>>cqd orc_njs reset;
+
+--- SQL operation complete.
+>>cqd parallel_num_esps reset;
+
+--- SQL operation complete.
+>>
+>>
+>>-- more external table tests
+>>
+>>control query shape cut;
+
+--- SQL operation complete.
+>>set schema trafodion.sch;
+
+--- SQL operation complete.
+>>drop external table if exists date_dim_orc for hive.hive.date_dim_orc;
+
+--- SQL operation complete.
+>>cqd volatile_table_find_suitable_key 'SYSTEM';
+
+--- SQL operation complete.
+>>create external table date_dim_orc
++> (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_orc;
+
+--- SQL operation complete.
+>>invoke hive.hive.date_dim_orc;
+
+-- Definition of hive table DATE_DIM_ORC
+-- Definition current Thu Mar 16 02:24:50 2017
+
+ (
+ 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 orc */
+
+--- SQL operation complete.
+>>showddl hive.hive.date_dim_orc;
+
+/* Hive DDL */
+CREATE TABLE DEFAULT.DATE_DIM_ORC
+ (
+ D_DATE_SK int
+ , D_DATE_ID string
+ , 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 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 orc
+;
+
+/* Trafodion DDL */
+
+CREATE EXTERNAL TABLE DATE_DIM_ORC
+ (
+ 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_ORC
+;
+
+--- SQL operation complete.
+>>prepare s from select * from hive.hive.date_dim_orc where d_date = date '2016-01-27';
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212356391094205166
+ROWS_OUT ............... 271
+EST_TOTAL_COST ........... 0.59
+STATEMENT ................ select *
+ from hive.hive.date_dim_orc
+ where d_date = date '2016-01-27';
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
+REQUESTS_IN .............. 1
+ROWS_OUT ............... 271
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ........... 0.59
+DESCRIPTION
+ est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
+ max_card_est ...... 69,397.5
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ...... 1,477
+ statement_index ........ 0
+ affinity_value ......... 0
+ max_max_cardinality 271
+ 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 .......... NOT AVAILABLE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_ORC_FORMAT ON
+ HIST_ROWCOUNT_REQU 50,000
+ HIST_MISSING_STATS_WARN 0
+ ORC_NJS_PROBES_T 1,000,000
+ HIVE_MIN_NUM_ESPS_PER_D 0
+ ORC_COLUMNS_PUSHDOWN ... ON
+ ORC_PRED_PUSHDOWN ...... ON
+ ORC_AGGR_PUSHDOWN ...... OFF
+ SCHEMA ................. TRAFODION.SCH
+ VOLATILE_TABLE_FIND_SUI SYSTEM
+ select_list ............ HIVE.DATE_DIM_ORC.D_DATE_SK,
+ HIVE.DATE_DIM_ORC.D_DATE_ID, %(2016-01-27),
+ HIVE.DATE_DIM_ORC.D_MONTH_SEQ,
+ HIVE.DATE_DIM_ORC.D_WEEK_SEQ,
+ HIVE.DATE_DIM_ORC.D_QUARTER_SEQ,
+ HIVE.DATE_DIM_ORC.D_YEAR, HIVE.DATE_DIM_ORC.D_DOW,
+ HIVE.DATE_DIM_ORC.D_MOY, HIVE.DATE_DIM_ORC.D_DOM,
+ HIVE.DATE_DIM_ORC.D_QOY,
+ HIVE.DATE_DIM_ORC.D_FY_YEAR,
+ HIVE.DATE_DIM_ORC.D_FY_QUARTER_SEQ,
+ HIVE.DATE_DIM_ORC.D_FY_WEEK_SEQ,
+ HIVE.DATE_DIM_ORC.D_DAY_NAME,
+ HIVE.DATE_DIM_ORC.D_QUARTER_NAME,
+ HIVE.DATE_DIM_ORC.D_HOLIDAY,
+ HIVE.DATE_DIM_ORC.D_WEEKEND,
+ HIVE.DATE_DIM_ORC.D_FOLLOWING_HOLIDAY,
+ HIVE.DATE_DIM_ORC.D_FIRST_DOM,
+ HIVE.DATE_DIM_ORC.D_LAST_DOM,
+ HIVE.DATE_DIM_ORC.D_SAME_DAY_LY,
+ HIVE.DATE_DIM_ORC.D_SAME_DAY_LQ,
+ HIVE.DATE_DIM_ORC.D_CURRENT_DAY,
+ HIVE.DATE_DIM_ORC.D_CURRENT_WEEK,
+ HIVE.DATE_DIM_ORC.D_CURRENT_MONTH,
+ HIVE.DATE_DIM_ORC.D_CURRENT_QUARTER,
+ HIVE.DATE_DIM_ORC.D_CURRENT_YEAR
+ input_variables ........ %(2016-01-27)
+
+
+ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.DATE_DIM_ORC
+REQUESTS_IN .............. 1
+ROWS_OUT ............... 271
+EST_OPER_COST ............ 0.59
+EST_TOTAL_COST ........... 0.59
+DESCRIPTION
+ max_card_est ...... 69,397.5
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ...... 1,471
+ scan_type .............. full scan of table HIVE.HIVE.DATE_DIM_ORC
+ object_type ............ Hive_Orc
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 28
+ probes ................. 1
+ rows_accessed ..... 73,049
+ orc_pred_pushdown ...... yes
+ orc_search_arguments ... and( HIVE.DATE_DIM_ORC.D_DATE = cast(%(2016-01-27))
+ )
+ executor_predicates .... (HIVE.DATE_DIM_ORC.D_DATE = %(2016-01-27))
+
+--- SQL operation complete.
+>>
+>>drop external table if exists date_dim_orc for hive.hive.date_dim_orc;
+
+--- SQL operation complete.
+>>create external table date_dim_orc
++> (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
++> for hive.hive.date_dim_orc;
+
+--- SQL operation complete.
+>>invoke hive.hive.date_dim_orc;
+
+-- Definition of hive table DATE_DIM_ORC
+-- Definition current Thu Mar 16 02:25:05 2017
+
+ (
+ 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 orc */
+
+--- SQL operation complete.
+>>showddl hive.hive.date_dim_orc;
+
+/* Hive DDL */
+CREATE TABLE DEFAULT.DATE_DIM_ORC
+ (
+ D_DATE_SK int
+ , D_DATE_ID string
+ , 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 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 orc
+;
+
+/* Trafodion DDL */
+
+CREATE EXTERNAL TABLE DATE_DIM_ORC
+ (
+ 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_ORC
+;
+
+--- SQL operation complete.
+>>prepare s from select * from hive.hive.date_dim_orc where d_date = date '2016-01-27';
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212356391108806821
+ROWS_OUT ............... 271
+EST_TOTAL_COST ........... 0.59
+STATEMENT ................ select *
+ from hive.hive.date_dim_orc
+ where d_date = date '2016-01-27';
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
+REQUESTS_IN .............. 1
+ROWS_OUT ............... 271
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ........... 0.59
+DESCRIPTION
+ est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
+ max_card_est ...... 69,397.5
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 446
+ statement_index ........ 0
+ affinity_value ......... 0
+ max_max_cardinality 271
+ 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 .......... NOT AVAILABLE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_ORC_FORMAT ON
+ HIST_ROWCOUNT_REQU 50,000
+ HIST_MISSING_STATS_WARN 0
+ ORC_NJS_PROBES_T 1,000,000
+ HIVE_MIN_NUM_ESPS_PER_D 0
+ ORC_COLUMNS_PUSHDOWN ... ON
+ ORC_PRED_PUSHDOWN ...... ON
+ ORC_AGGR_PUSHDOWN ...... OFF
+ SCHEMA ................. TRAFODION.SCH
+ VOLATILE_TABLE_FIND_SUI SYSTEM
+ select_list ............ HIVE.DATE_DIM_ORC.D_DATE_SK,
+ HIVE.DATE_DIM_ORC.D_DATE_ID, %(2016-01-27),
+ HIVE.DATE_DIM_ORC.D_MONTH_SEQ,
+ HIVE.DATE_DIM_ORC.D_WEEK_SEQ,
+ HIVE.DATE_DIM_ORC.D_QUARTER_SEQ,
+ HIVE.DATE_DIM_ORC.D_YEAR, HIVE.DATE_DIM_ORC.D_DOW,
+ HIVE.DATE_DIM_ORC.D_MOY, HIVE.DATE_DIM_ORC.D_DOM,
+ HIVE.DATE_DIM_ORC.D_QOY,
+ HIVE.DATE_DIM_ORC.D_FY_YEAR,
+ HIVE.DATE_DIM_ORC.D_FY_QUARTER_SEQ,
+ HIVE.DATE_DIM_ORC.D_FY_WEEK_SEQ,
+ HIVE.DATE_DIM_ORC.D_DAY_NAME,
+ HIVE.DATE_DIM_ORC.D_QUARTER_NAME,
+ HIVE.DATE_DIM_ORC.D_HOLIDAY,
+ HIVE.DATE_DIM_ORC.D_WEEKEND,
+ HIVE.DATE_DIM_ORC.D_FOLLOWING_HOLIDAY,
+ HIVE.DATE_DIM_ORC.D_FIRST_DOM,
+ HIVE.DATE_DIM_ORC.D_LAST_DOM,
+ HIVE.DATE_DIM_ORC.D_SAME_DAY_LY,
+ HIVE.DATE_DIM_ORC.D_SAME_DAY_LQ,
+ HIVE.DATE_DIM_ORC.D_CURRENT_DAY,
+ HIVE.DATE_DIM_ORC.D_CURRENT_WEEK,
+ HIVE.DATE_DIM_ORC.D_CURRENT_MONTH,
+ HIVE.DATE_DIM_ORC.D_CURRENT_QUARTER,
+ HIVE.DATE_DIM_ORC.D_CURRENT_YEAR
+ input_variables ........ %(2016-01-27)
+
+
+ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.DATE_DIM_ORC
+REQUESTS_IN .............. 1
+ROWS_OUT ............... 271
+EST_OPER_COST ............ 0.59
+EST_TOTAL_COST ........... 0.59
+DESCRIPTION
+ max_card_est ...... 69,397.5
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length ........ 440
+ scan_type .............. full scan of table HIVE.HIVE.DATE_DIM_ORC
+ object_type ............ Hive_Orc
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 28
+ probes ................. 1
+ rows_accessed ..... 73,049
+ orc_pred_pushdown ...... yes
+ orc_search_arguments ... and( HIVE.DATE_DIM_ORC.D_DATE = cast(%(2016-01-27))
+ )
+ executor_predicates .... (HIVE.DATE_DIM_ORC.D_DATE = %(2016-01-27))
+
+--- SQL operation complete.
+>>
+>>
+>>-- error cases
+>>drop external table if exists date_dim_orc for hive.hive.date_dim_orc;
+
+--- SQL operation complete.
+>>
+>>-- column d_date_skk doesn't exist in native hive table
+>>create external table date_dim_orc
++> (d_date_skk int)
++> for hive.hive.date_dim_orc;
+
+*** ERROR[1009] Column D_DATE_SKK does not exist in the specified table.
+
+--- SQL operation failed with errors.
+>>
+>>-- del/update not supported on orc or hive
+>>prepare s from delete from hive.hive.store2_sales_orc;
+
+*** ERROR[4223] Update/Delete on ORC table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from update hive.hive.store2_sales_orc set ss_ext_tax = 1;
+
+*** ERROR[4223] Update/Delete on ORC table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from delete from hive.hive.store_sales;
+
+*** ERROR[4223] Update/Delete on Hive table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from update hive.hive.store_sales set ss_ext_tax = 1;
+
+*** ERROR[4223] Update/Delete on Hive table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- upsert/insert cannot specify column list and must provide all column values.
+>>prepare s from upsert into hive.hive.store2_sales_orc values (1);
+
+*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23).
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from upsert using load into hive.hive.store2_sales_orc values (1);
+
+*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23).
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from insert into hive.hive.store2_sales_orc values (1);
+
+*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23).
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from update hive.hive.store2_sales_orc set ss_net_paid = 1;
+
+*** ERROR[4223] Update/Delete on ORC table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from upsert into hive.hive.store2_sales_orc(ss_sold_date_sk) values (1);
+
+*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from insert into hive.hive.store2_sales_orc(ss_sold_date_sk) values (1);
+
+*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from upsert into hive.hive.store_sales(ss_sold_date_sk) values (1);
+
+*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from insert into hive.hive.store_sales(ss_sold_date_sk) values (1);
+
+*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in
<TRUNCATED>