You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by an...@apache.org on 2017/12/13 19:02:31 UTC
[09/12] incubator-trafodion git commit: JIRA TRAFODION-2834
Streamline supported xn access modes
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6dd81240/core/sql/regress/hive/EXPECTED040
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/EXPECTED040 b/core/sql/regress/hive/EXPECTED040
new file mode 100644
index 0000000..971c8f8
--- /dev/null
+++ b/core/sql/regress/hive/EXPECTED040
@@ -0,0 +1,2527 @@
+>>obey TEST040(setup);
+>>--------------------------------------------------------------------------
+>>
+>>set schema hive.hive;
+
+--- SQL operation complete.
+>>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '20' ;
+
+--- SQL operation complete.
+>>cqd mode_seahive 'ON';
+
+--- SQL operation complete.
+>>cqd traf_enable_parquet_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 TEST040(tests);
+>>--------------------------------------------------------------------------
+>>-- PARQUET file metadata info
+>>--invoke hive.hive.store_parquet;
+>>--
+>>-- select one row from PARQUET table
+>>select [first 1] * from hive.hive.store_parquet;
+
+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 00:00:00.000000 ? 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 PARQUET table
+>>select * from hive.hive.store_parquet 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 00:00:00.000000 ? 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 00:00:00.000000 2000-03-12 00:00:00.000000 ? 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 00:00:00.000000 ? ? 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 00:00:00.000000 1999-03-13 00:00:00.000000 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 00:00:00.000000 2001-03-12 00:00:00.000000 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 00:00:00.000000 ? ? 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 00:00:00.000000 ? ? 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 00:00:00.000000 2000-03-12 00:00:00.000000 ? 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 00:00:00.000000 ? ? 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 00:00:00.000000 1999-03-13 00:00:00.000000 ? 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 00:00:00.000000 2001-03-12 00:00:00.000000 ? 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 00:00:00.000000 ? ? 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_parquet 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_parquet;
+
+(EXPR)
+--------------------
+
+ 12
+
+--- 1 row(s) selected.
+>>
+>>-- explain of join between 2 PARQUET tables
+>>prepare XX from select x.s_suite_number, y.s_street_name
++> from hive.hive.store_parquet x, hive.hive.store_parquet 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 PARQUET_SCAN ? ? STORE_PARQ
+01 PARQUET_SCAN ? ? STORE_PARQ
+
+--- 4 row(s) selected.
+>>
+>>-- execute of join between 2 PARQUET 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 PARQUET tables
+>>control query shape join(scan(path 'STORE'), scan(path 'STORE_PARQUET'));
+
+--- SQL operation complete.
+>>prepare XX from select x.s_suite_number, y.s_street_name
++> from hive.hive.store x, hive.hive.store_parquet y
++> where x.s_store_sk = y.s_store_sk;
+
+--- SQL command prepared.
+>>control query shape cut;
+
+--- SQL operation complete.
+>>execute explainIt;
+
+S OPERATOR LC RC TAB_NAME
+-- ---------------- -- -- ----------
+
+04 ROOT 3 ?
+03 HYBRID_HASH_JOIN 2 1
+02 HIVE_SCAN ? ? STORE
+01 PARQUET_SCAN ? ? STORE_PARQ
+
+--- 4 row(s) selected.
+>>
+>>-- execute of join between hive(hdfs) and PARQUET 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 parquet_columns_pushdown 'OFF';
+
+--- SQL operation complete.
+>>showplan option 'tr' select s_store_sk from store_parquet;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 58712
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 576
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0
+Flag = 0x129
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 0x20300a00
+rtFlags2_ = 0
+rtFlags3_ = 0
+rtFlags4_ = 0x2000
+rtFlags5_ = 0
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 3112
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_EXT_STORAGE_SCAN [1]:
+------------------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 464
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 41032
+estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0
+Flag = 0x9
+criDescDown_->noTuples() = 2, criDescUp_->noTuples() = 3
+
+For ComTdbExtStorageScan :
+tableName_ = HIVE.HIVE.STORE_PARQUET
+type_ = PARQUET
+hostName_ = localhost, port_ = 36000
+recordDelimiter_ = 10, columnDelimiter_ = 124
+hdfsBufSize_ = 735, rangeTailIOSize_ = 20984, hdfsSqlMaxRecLen_ = 8
+tuppIndex_ = 2, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 532
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 576
+convertSkipListSize_ = 33, convertSkipList_ = 3
+outputRowLength_ = 16
+Flag = 0xc
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 4 3681 store_parquet/000000_0
+
+Summary of bytes read per ESP (3681 = 100 percent):
+
+ESP 0 reads 3681 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:36000/user/trafodion/hive/tpcds/store_parquet
+modTSforDir_ = 1511634536, 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: extOperExpr_ is NULL
+
+--- SQL operation complete.
+>>select s_store_sk from store_parquet;
+
+S_STORE_SK
+-----------
+
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+
+--- 12 row(s) selected.
+>>cqd parquet_columns_pushdown 'ON';
+
+--- SQL operation complete.
+>>showplan option 'tr' select s_store_sk from store_parquet;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 14672
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 576
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0
+Flag = 0x129
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 0x20300a00
+rtFlags2_ = 0
+rtFlags3_ = 0
+rtFlags4_ = 0x2000
+rtFlags5_ = 0
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 3104
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_EXT_STORAGE_SCAN [1]:
+------------------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 464
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 41032
+estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0
+Flag = 0x9
+criDescDown_->noTuples() = 2, criDescUp_->noTuples() = 3
+
+For ComTdbExtStorageScan :
+tableName_ = HIVE.HIVE.STORE_PARQUET
+type_ = PARQUET
+hostName_ = localhost, port_ = 36000
+recordDelimiter_ = 10, columnDelimiter_ = 124
+hdfsBufSize_ = 115, rangeTailIOSize_ = 20984, hdfsSqlMaxRecLen_ = 8
+tuppIndex_ = 2, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 8
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 16
+convertSkipListSize_ = 33, convertSkipList_ = 3
+outputRowLength_ = 16
+Flag = 0xc
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 4 3681 store_parquet/000000_0
+
+Summary of bytes read per ESP (3681 = 100 percent):
+
+ESP 0 reads 3681 bytes ( 100 percent of avg)
+
+Number of columns to retrieve: 1
+ColNumber: 1, ColName: S_STORE_SK
+hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_parquet
+modTSforDir_ = 1511634536, 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: extOperExpr_ is NULL
+
+--- SQL operation complete.
+>>select s_store_sk from store_parquet;
+
+S_STORE_SK
+-----------
+
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+
+--- 12 row(s) selected.
+>>
+>>-- predicate pushdown to PARQUET layer
+>>cqd parquet_pred_pushdown 'OFF';
+
+--- SQL operation complete.
+>>select s_store_sk from store_parquet where s_store_sk = 3;
+
+S_STORE_SK
+-----------
+
+ 3
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_parquet where s_store_sk < 2;
+
+S_STORE_SK
+-----------
+
+ 1
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_parquet where s_store_sk >= 11;
+
+S_STORE_SK
+-----------
+
+ 11
+ 12
+
+--- 2 row(s) selected.
+>>select s_store_sk from store_parquet 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_parquet 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_parquet where s_store_sk = 3;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 14848
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 576
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0
+Flag = 0x129
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 0x20300a00
+rtFlags2_ = 0
+rtFlags3_ = 0
+rtFlags4_ = 0x2000
+rtFlags5_ = 0
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 3208
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_EXT_STORAGE_SCAN [1]:
+------------------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 464
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840
+estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0
+Flag = 0x9
+criDescDown_->noTuples() = 3, criDescUp_->noTuples() = 4
+
+For ComTdbExtStorageScan :
+tableName_ = HIVE.HIVE.STORE_PARQUET
+type_ = PARQUET
+hostName_ = localhost, port_ = 36000
+recordDelimiter_ = 10, columnDelimiter_ = 124
+hdfsBufSize_ = 115, rangeTailIOSize_ = 20984, hdfsSqlMaxRecLen_ = 16
+tuppIndex_ = 3, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 8
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 8
+convertSkipListSize_ = 33, convertSkipList_ = 2
+outputRowLength_ = 8
+Flag = 0xc
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 4 3681 store_parquet/000000_0
+
+Summary of bytes read per ESP (3681 = 100 percent):
+
+ESP 0 reads 3681 bytes ( 100 percent of avg)
+
+Number of columns to retrieve: 1
+ColNumber: 1, ColName: S_STORE_SK
+hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_parquet
+modTSforDir_ = 1511634536, 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: extOperExpr_ is NULL
+
+--- SQL operation complete.
+>>
+>>cqd parquet_pred_pushdown 'ON';
+
+--- SQL operation complete.
+>>select s_store_sk from store_parquet where s_store_sk = 3;
+
+S_STORE_SK
+-----------
+
+ 3
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_parquet where s_store_sk < 2;
+
+S_STORE_SK
+-----------
+
+ 1
+
+--- 1 row(s) selected.
+>>select s_store_sk from store_parquet where s_store_sk >= 11;
+
+S_STORE_SK
+-----------
+
+ 11
+ 12
+
+--- 2 row(s) selected.
+>>select s_store_sk from store_parquet 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_parquet 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_parquet where s_store_sk = 3;
+MASTER Executor fragment
+========================
+
+Fragment ID: 0, Length: 17104
+
+Contents of EX_ROOT [2]:
+------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 576
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0
+estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0
+Flag = 0x129
+
+For ComTdbRoot :
+FirstNRows = -1, baseTablenamePosition = -1
+queryType_ = 2, planVersion_ = 2600
+rtFlags1_ = 0x20300a00
+rtFlags2_ = 0
+rtFlags3_ = 0
+rtFlags4_ = 0x2000
+rtFlags5_ = 0
+queryType_ = 2
+inputVarsSize_ = 0
+querySimilarityInfo()->siList()->numEntries() = 1
+explain_plan_size = 3240
+
+# of Expressions = 4
+
+Expression: inputExpr_ is NULL
+Expression: outputExpr_ is not NULL
+Expression: pkeyExpr_ is NULL
+Expression: predExpr_ is NULL
+Contents of EX_EXT_STORAGE_SCAN [1]:
+------------------------------------
+
+For ComTdb :
+Class Version = 1, Class Size = 464
+InitialQueueSizeDown = 4, InitialQueueSizeUp = 4
+queueResizeLimit = 9, queueResizeFactor = 4
+queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840
+estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0
+Flag = 0x9
+criDescDown_->noTuples() = 3, criDescUp_->noTuples() = 4
+
+For ComTdbExtStorageScan :
+tableName_ = HIVE.HIVE.STORE_PARQUET
+type_ = PARQUET
+hostName_ = localhost, port_ = 36000
+recordDelimiter_ = 10, columnDelimiter_ = 124
+hdfsBufSize_ = 115, rangeTailIOSize_ = 20984, hdfsSqlMaxRecLen_ = 16
+tuppIndex_ = 3, workAtpIndex_ = 3
+asciiTuppIndex_ = 4, asciiRowLen_ = 8
+moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 8
+convertSkipListSize_ = 33, convertSkipList_ = 2
+outputRowLength_ = 8
+Flag = 0xc
+
+Number of ranges to scan: 1
+Number of esps to scan: 1
+
+ Esp# Range# StripeOffset Length FileName
+====== ====== ============ ============ ==============================
+
+ 0 0 4 3681 store_parquet/000000_0
+
+Summary of bytes read per ESP (3681 = 100 percent):
+
+ESP 0 reads 3681 bytes ( 100 percent of avg)
+
+Number of columns to retrieve: 1
+ColNumber: 1, ColName: S_STORE_SK
+hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_parquet
+modTSforDir_ = 1511634536, 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 extAllColInfoList entries: 33
+
+# of Expressions = 6
+
+Expression: selectExpr_ is NULL
+Expression: moveExpr_ is NULL
+Expression: convertExpr_ is not NULL
+Expression: moveColsConvertExpr_ is NULL
+Expression: partElimExpr_ is NULL
+Expression: extOperExpr_ is not NULL
+
+--- SQL operation complete.
+>>
+>>-- local join predicate is not pushed down
+>>explain select * from store_sales_parquet where ss_sold_date_sk = ss_item_sk ;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... NOT NAMED
+PLAN_ID .................. 212378573691398125
+ROWS_OUT ......... 1,440,202
+EST_TOTAL_COST ......... 234.75
+STATEMENT ................ select *
+ from store_sales_parquet
+ 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 ......... 234.75
+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 ......... (-1(4 times))
+ 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
+ HBASE_FILTER_PREDS ..... 2
+ TRAF_INDEX_CREATE_OPT ON
+ TRAF_USE_REGION_XN ..... ON
+ SCHEMA ................. HIVE.HIVE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_PARQUET_FOR 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
+ PARQUET_COLUMNS_PUSHDOW ON
+ PARQUET_PRED_PUSHDOWN ON
+ GENERATE_EXPLAIN ....... ON
+ select_list ............ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_PARQUET.SS_SOLD_TIME_SK,
+ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_PARQUET.SS_CUSTOMER_SK,
+ HIVE.STORE_SALES_PARQUET.SS_CDEMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_HDEMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_ADDR_SK,
+ HIVE.STORE_SALES_PARQUET.SS_STORE_SK,
+ HIVE.STORE_SALES_PARQUET.SS_PROMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_TICKET_NUMBER,
+ HIVE.STORE_SALES_PARQUET.SS_QUANTITY,
+ HIVE.STORE_SALES_PARQUET.SS_WHOLESALE_COST,
+ HIVE.STORE_SALES_PARQUET.SS_LIST_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_SALES_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_DISCOUNT_AMT,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_SALES_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_WHOLESALE_COST,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_LIST_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_TAX,
+ HIVE.STORE_SALES_PARQUET.SS_COUPON_AMT,
+ HIVE.STORE_SALES_PARQUET.SS_NET_PAID,
+ HIVE.STORE_SALES_PARQUET.SS_NET_PAID_INC_TAX,
+ HIVE.STORE_SALES_PARQUET.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 ......... 234.75
+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)
+
+
+PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_PARQUET
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 1,440,202
+EST_OPER_COST .......... 234.75
+EST_TOTAL_COST ......... 234.75
+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_PARQUET
+ object_type ............ Hive_Parquet
+ 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_PARQUET.SS_ITEM_SK =
+ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK)
+
+--- SQL operation complete.
+>>
+>>-- Boolean constant predicate is not pushed down
+>>explain select * from store_sales_parquet where 1=2;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... NOT NAMED
+PLAN_ID .................. 212378573691753629
+ROWS_OUT ................. 1
+EST_TOTAL_COST ........... 0
+STATEMENT ................ select * from store_sales_parquet 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
+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
+ HBASE_FILTER_PREDS ..... 2
+ TRAF_INDEX_CREATE_OPT ON
+ TRAF_USE_REGION_XN ..... ON
+ SCHEMA ................. HIVE.HIVE
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_PARQUET_FOR 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
+ PARQUET_COLUMNS_PUSHDOW ON
+ PARQUET_PRED_PUSHDOWN ON
+ GENERATE_EXPLAIN ....... ON
+ select_list ............ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_PARQUET.SS_SOLD_TIME_SK,
+ HIVE.STORE_SALES_PARQUET.SS_ITEM_SK,
+ HIVE.STORE_SALES_PARQUET.SS_CUSTOMER_SK,
+ HIVE.STORE_SALES_PARQUET.SS_CDEMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_HDEMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_ADDR_SK,
+ HIVE.STORE_SALES_PARQUET.SS_STORE_SK,
+ HIVE.STORE_SALES_PARQUET.SS_PROMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_TICKET_NUMBER,
+ HIVE.STORE_SALES_PARQUET.SS_QUANTITY,
+ HIVE.STORE_SALES_PARQUET.SS_WHOLESALE_COST,
+ HIVE.STORE_SALES_PARQUET.SS_LIST_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_SALES_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_DISCOUNT_AMT,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_SALES_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_WHOLESALE_COST,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_LIST_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_TAX,
+ HIVE.STORE_SALES_PARQUET.SS_COUPON_AMT,
+ HIVE.STORE_SALES_PARQUET.SS_NET_PAID,
+ HIVE.STORE_SALES_PARQUET.SS_NET_PAID_INC_TAX,
+ HIVE.STORE_SALES_PARQUET.SS_NET_PROFIT
+
+
+PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_PARQUET
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0
+EST_TOTAL_COST ........... 0
+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_PARQUET
+ object_type ............ Hive_Parquet
+ scan_direction ......... forward
+ lock_mode .............. not specified, defaulted to lock cursor
+ access_mode ............ not specified, defaulted to read committed
+ columns_retrieved ..... 23
+ probes ................. 1
+ part_elim_compiled ..... 0.
+
+--- SQL operation complete.
+>>
+>>-- aggregate pushdown to PARQUET layer
+>>cqd parquet_aggr_pushdown 'ON';
+
+--- SQL operation complete.
+>>explain options 'f' select count(*) from store_parquet;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 parquet_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select count(*) from store_parquet;
+
+(EXPR)
+--------------------
+
+ 12
+
+--- 1 row(s) selected.
+>>select count(*), count(*) from store_parquet;
+
+(EXPR) (EXPR)
+-------------------- --------------------
+
+ 12 12
+
+--- 1 row(s) selected.
+>>select min(s_store_sk) from store_parquet;
+
+(EXPR)
+-----------
+
+ 1
+
+--- 1 row(s) selected.
+>>select max(s_store_sk) from store_parquet;
+
+(EXPR)
+-----------
+
+ 12
+
+--- 1 row(s) selected.
+>>select sum(s_store_sk) from store_parquet;
+
+(EXPR)
+--------------------
+
+ 78
+
+--- 1 row(s) selected.
+>>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk)
++> from store_parquet;
+
+(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_parquet;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+2 . 3 root 1.00E+000
+1 . 2 sort_scalar_aggr 1.00E+000
+. . 1 parquet_scan STORE_PARQUET 1.00E+002
+
+--- SQL operation complete.
+>>
+>>explain options 'f'
++> select count(*) from hive.hive.store_parquet union all
++> select count(*) from hive.hive.store_parquet;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+3 . 4 root 2.00E+000
+1 2 3 merge_union 2.00E+000
+. . 2 parquet_aggr 1.00E+000
+. . 1 parquet_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select count(*) from hive.hive.store_parquet union all
++> select count(*) from hive.hive.store_parquet;
+
+(EXPR)
+--------------------
+
+ 12
+ 12
+
+--- 2 row(s) selected.
+>>
+>>explain options 'f'
++> select min(s_store_sk) from hive.hive.store_parquet union all
++> select min(s_store_sk) from hive.hive.store_parquet;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+3 . 4 root 2.00E+000
+1 2 3 merge_union 2.00E+000
+. . 2 parquet_aggr 1.00E+000
+. . 1 parquet_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select min(s_store_sk) from hive.hive.store_parquet union all
++> select min(s_store_sk) from hive.hive.store_parquet;
+
+(EXPR)
+-----------
+
+ 1
+ 1
+
+--- 2 row(s) selected.
+>>
+>>explain options 'f' select count(s_store_sk) from store_parquet;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 parquet_aggr 1.00E+000
+
+--- SQL operation complete.
+>>select count(s_store_sk) from store_parquet;
+
+(EXPR)
+--------------------
+
+ 12
+
+--- 1 row(s) selected.
+>>
+>>explain options 'f' select count(*) from store_parquet
++> having sum(s_store_sk) = 78;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+2 . 3 root 1.00E+000
+1 . 2 sort_scalar_aggr 1.00E+000
+. . 1 parquet_scan STORE_PARQUET 1.00E+002
+
+--- SQL operation complete.
+>>select count(*) from store_parquet having sum(s_store_sk) = 78;
+
+(EXPR)
+--------------------
+
+ 12
+
+--- 1 row(s) selected.
+>>
+>>select count(*) from store_parquet having sum(s_store_sk) = 77;
+
+--- 0 row(s) selected.
+>>
+>>cqd parquet_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_parquet;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+2 . 3 root 1.00E+000
+1 . 2 sort_scalar_aggr 1.00E+000
+. . 1 parquet_scan STORE_PARQUET 1.00E+002
+
+--- SQL operation complete.
+>>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk)
++> from store_parquet;
+
+(EXPR) (EXPR) (EXPR) (EXPR)
+-------------------- ----------- ----------- --------------------
+
+ 12 1 12 78
+
+--- 1 row(s) selected.
+>>
+>>-- test query cache
+>>prepare xx from select cd_gender from customer_demographics_parquet 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_parquet 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_parquet for hive.hive.store_sales_parquet;
+
+--- SQL operation complete.
+>>create external table store_sales_parquet
++> for hive.hive.store_sales_parquet;
+
+--- SQL operation complete.
+>>invoke hive.hive.store_sales_parquet;
+
+-- Definition of hive table STORE_SALES_PARQUET
+-- Definition current Mon Nov 27 20:15:06 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 parquet */
+
+--- SQL operation complete.
+>>
+>>set schema hive.hive;
+
+--- SQL operation complete.
+>>prepare s from select * from store_sales_parquet where ss_item_sk = 1;
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212378573706907158
+ROWS_OUT ............. 1,698
+EST_TOTAL_COST ......... 234.71
+STATEMENT ................ select *
+ from store_sales_parquet
+ 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 ......... 234.71
+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 ......... (-1(4 times))
+ 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
+ HBASE_FILTER_PREDS ..... 2
+ TRAF_INDEX_CREATE_OPT ON
+ TRAF_USE_REGION_XN ..... ON
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_PARQUET_FOR 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
+ PARQUET_COLUMNS_PUSHDOW ON
+ PARQUET_PRED_PUSHDOWN ON
+ PARQUET_AGGR_PUSHDOWN OFF
+ SCHEMA ................. HIVE.HIVE
+ select_list ............ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK,
+ HIVE.STORE_SALES_PARQUET.SS_SOLD_TIME_SK, %(1),
+ HIVE.STORE_SALES_PARQUET.SS_CUSTOMER_SK,
+ HIVE.STORE_SALES_PARQUET.SS_CDEMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_HDEMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_ADDR_SK,
+ HIVE.STORE_SALES_PARQUET.SS_STORE_SK,
+ HIVE.STORE_SALES_PARQUET.SS_PROMO_SK,
+ HIVE.STORE_SALES_PARQUET.SS_TICKET_NUMBER,
+ HIVE.STORE_SALES_PARQUET.SS_QUANTITY,
+ HIVE.STORE_SALES_PARQUET.SS_WHOLESALE_COST,
+ HIVE.STORE_SALES_PARQUET.SS_LIST_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_SALES_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_DISCOUNT_AMT,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_SALES_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_WHOLESALE_COST,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_LIST_PRICE,
+ HIVE.STORE_SALES_PARQUET.SS_EXT_TAX,
+ HIVE.STORE_SALES_PARQUET.SS_COUPON_AMT,
+ HIVE.STORE_SALES_PARQUET.SS_NET_PAID,
+ HIVE.STORE_SALES_PARQUET.SS_NET_PAID_INC_TAX,
+ HIVE.STORE_SALES_PARQUET.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 ......... 234.71
+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)
+
+
+PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_PARQUET
+REQUESTS_IN .............. 1
+ROWS_OUT ............. 1,698
+EST_OPER_COST .......... 234.71
+EST_TOTAL_COST ......... 234.71
+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_PARQUET
+ object_type ............ Hive_Parquet
+ 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
+ parquet_pred_pushdown yes
+ parquet_search_argument and( HIVE.STORE_SALES_PARQUET.SS_ITEM_SK = %(1) )
+
+--- SQL operation complete.
+>>
+>>-- join with nested join
+>>?ignore
+>>cqd orc_njs 'on';
+>>control query shape nested_join(scan(path 'CUSTOMER_PARQUET'),
++> scan(path 'STORE_SALES_PARQUET'));
+>>prepare s from select * from customer_parquet, store_sales_parquet
++> where store_sales_parquet.ss_item_sk = customer_parquet.c_customer_sk;
+>>explain options 'f' s;
+>>explain s;
+>>cqd orc_njs reset;
+>>?ignore
+>>
+>>-- 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_parquet dim, store_sales_sorted_parquet 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 parquet_scan STORE_SALES_SORTED_P 2.88E+006
+1 . 2 esp_exchange 4(rep-b):4(hash2) 2.00E+000
+. . 1 parquet_scan DATE_DIM_PARQUET 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_parquet for hive.hive.date_dim_parquet;
+
+--- SQL operation complete.
+>>cqd volatile_table_find_suitable_key 'SYSTEM';
+
+--- SQL operation complete.
+>>create external table date_dim_parquet
++> (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_parquet;
+
+--- SQL operation complete.
+>>invoke hive.hive.date_dim_parquet;
+
+-- Definition of hive table DATE_DIM_PARQUET
+-- Definition current Mon Nov 27 20:15:11 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 parquet */
+
+--- SQL operation complete.
+>>showddl hive.hive.date_dim_parquet;
+
+/* Hive DDL */
+CREATE TABLE DEFAULT.DATE_DIM_PARQUET
+ (
+ 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 parquet
+;
+
+/* Trafodion DDL */
+
+REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_PARQUET;
+/* ObjectUID = 1842681638780263725 */
+
+CREATE EXTERNAL TABLE DATE_DIM_PARQUET
+ (
+ 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_PARQUET
+;
+
+--- SQL operation complete.
+>>prepare s from select * from hive.hive.date_dim_parquet where d_date = date '2016-01-27';
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212378573717690486
+ROWS_OUT ............... 271
+EST_TOTAL_COST ......... 244.13
+STATEMENT ................ select *
+ from hive.hive.date_dim_parquet
+ 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 ......... 244.13
+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
+ HBASE_FILTER_PREDS ..... 2
+ TRAF_INDEX_CREATE_OPT ON
+ TRAF_USE_REGION_XN ..... ON
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_PARQUET_FOR 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
+ PARQUET_COLUMNS_PUSHDOW ON
+ PARQUET_PRED_PUSHDOWN ON
+ PARQUET_AGGR_PUSHDOWN OFF
+ SCHEMA ................. TRAFODION.SCH
+ VOLATILE_TABLE_FIND_SUI SYSTEM
+ select_list ............ HIVE.DATE_DIM_PARQUET.D_DATE_SK,
+ HIVE.DATE_DIM_PARQUET.D_DATE_ID, %(2016-01-27),
+ HIVE.DATE_DIM_PARQUET.D_MONTH_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_WEEK_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_QUARTER_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_YEAR,
+ HIVE.DATE_DIM_PARQUET.D_DOW,
+ HIVE.DATE_DIM_PARQUET.D_MOY,
+ HIVE.DATE_DIM_PARQUET.D_DOM,
+ HIVE.DATE_DIM_PARQUET.D_QOY,
+ HIVE.DATE_DIM_PARQUET.D_FY_YEAR,
+ HIVE.DATE_DIM_PARQUET.D_FY_QUARTER_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_FY_WEEK_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_DAY_NAME,
+ HIVE.DATE_DIM_PARQUET.D_QUARTER_NAME,
+ HIVE.DATE_DIM_PARQUET.D_HOLIDAY,
+ HIVE.DATE_DIM_PARQUET.D_WEEKEND,
+ HIVE.DATE_DIM_PARQUET.D_FOLLOWING_HOLIDAY,
+ HIVE.DATE_DIM_PARQUET.D_FIRST_DOM,
+ HIVE.DATE_DIM_PARQUET.D_LAST_DOM,
+ HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LY,
+ HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LQ,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_DAY,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_WEEK,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_MONTH,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_QUARTER,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_YEAR
+ input_variables ........ %(2016-01-27)
+
+
+PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.DATE_DIM_PARQUET
+REQUESTS_IN .............. 1
+ROWS_OUT ............... 271
+EST_OPER_COST .......... 244.13
+EST_TOTAL_COST ......... 244.13
+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_PARQUET
+ object_type ............ Hive_Parquet
+ 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
+ parquet_pred_pushdown yes
+ parquet_search_argument and( HIVE.DATE_DIM_PARQUET.D_DATE = %(2016-01-27) )
+
+--- SQL operation complete.
+>>
+>>drop external table if exists date_dim_parquet for hive.hive.date_dim_parquet;
+
+--- SQL operation complete.
+>>create external table date_dim_parquet
++> (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
++> for hive.hive.date_dim_parquet;
+
+--- SQL operation complete.
+>>invoke hive.hive.date_dim_parquet;
+
+-- Definition of hive table DATE_DIM_PARQUET
+-- Definition current Mon Nov 27 20:15:28 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 parquet */
+
+--- SQL operation complete.
+>>showddl hive.hive.date_dim_parquet;
+
+/* Hive DDL */
+CREATE TABLE DEFAULT.DATE_DIM_PARQUET
+ (
+ 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 parquet
+;
+
+/* Trafodion DDL */
+
+REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_PARQUET;
+/* ObjectUID = 1842681638780263725 */
+
+CREATE EXTERNAL TABLE DATE_DIM_PARQUET
+ (
+ 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_PARQUET
+;
+
+--- SQL operation complete.
+>>prepare s from select * from hive.hive.date_dim_parquet where d_date = date '2016-01-27';
+
+--- SQL command prepared.
+>>explain s;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... S
+PLAN_ID .................. 212378573717690486
+ROWS_OUT ............... 271
+EST_TOTAL_COST ......... 244.13
+STATEMENT ................ select *
+ from hive.hive.date_dim_parquet
+ 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 ......... 244.13
+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
+ HBASE_FILTER_PREDS ..... 2
+ TRAF_INDEX_CREATE_OPT ON
+ TRAF_USE_REGION_XN ..... ON
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_PARQUET_FOR 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
+ PARQUET_COLUMNS_PUSHDOW ON
+ PARQUET_PRED_PUSHDOWN ON
+ PARQUET_AGGR_PUSHDOWN OFF
+ SCHEMA ................. TRAFODION.SCH
+ VOLATILE_TABLE_FIND_SUI SYSTEM
+ select_list ............ HIVE.DATE_DIM_PARQUET.D_DATE_SK,
+ HIVE.DATE_DIM_PARQUET.D_DATE_ID, %(2016-01-27),
+ HIVE.DATE_DIM_PARQUET.D_MONTH_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_WEEK_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_QUARTER_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_YEAR,
+ HIVE.DATE_DIM_PARQUET.D_DOW,
+ HIVE.DATE_DIM_PARQUET.D_MOY,
+ HIVE.DATE_DIM_PARQUET.D_DOM,
+ HIVE.DATE_DIM_PARQUET.D_QOY,
+ HIVE.DATE_DIM_PARQUET.D_FY_YEAR,
+ HIVE.DATE_DIM_PARQUET.D_FY_QUARTER_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_FY_WEEK_SEQ,
+ HIVE.DATE_DIM_PARQUET.D_DAY_NAME,
+ HIVE.DATE_DIM_PARQUET.D_QUARTER_NAME,
+ HIVE.DATE_DIM_PARQUET.D_HOLIDAY,
+ HIVE.DATE_DIM_PARQUET.D_WEEKEND,
+ HIVE.DATE_DIM_PARQUET.D_FOLLOWING_HOLIDAY,
+ HIVE.DATE_DIM_PARQUET.D_FIRST_DOM,
+ HIVE.DATE_DIM_PARQUET.D_LAST_DOM,
+ HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LY,
+ HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LQ,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_DAY,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_WEEK,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_MONTH,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_QUARTER,
+ HIVE.DATE_DIM_PARQUET.D_CURRENT_YEAR
+ input_variables ........ %(2016-01-27)
+
+
+PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.DATE_DIM_PARQUET
+REQUESTS_IN .............. 1
+ROWS_OUT ............... 271
+EST_OPER_COST .......... 244.13
+EST_TOTAL_COST ......... 244.13
+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_PARQUET
+ object_type ............ Hive_Parquet
+ 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
+ parquet_pred_pushdown yes
+ parquet_search_argument and( HIVE.DATE_DIM_PARQUET.D_DATE = %(2016-01-27) )
+
+--- SQL operation complete.
+>>
+>>
+>>-- error cases
+>>drop external table if exists date_dim_parquet for hive.hive.date_dim_parquet;
+
+--- SQL operation complete.
+>>
+>>-- column d_date_skk doesn't exist in native hive table
+>>create external table date_dim_parquet
++> (d_date_skk int)
++> for hive.hive.date_dim_parquet;
+
+*** ERROR[1009] Column D_DATE_SKK does not exist in the specified table.
+
+--- SQL operation failed with errors.
+>>
+>>-- del/update not supported on parquet or hive
+>>prepare s from delete from hive.hive.store2_sales_parquet;
+
+*** ERROR[4223] Update/Delete on PARQUET table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from update hive.hive.store2_sales_parquet set ss_ext_tax = 1;
+
+*** ERROR[4223] Update/Delete on PARQUET 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_parquet 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_parquet 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_parquet 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_parquet set ss_net_paid = 1;
+
+*** ERROR[4223] Update/Delete on PARQUET table is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from upsert into hive.hive.store2_sales_parquet(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_parquet(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 this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare s from upsert into hive.hive.store_sales 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.store_sales 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.
+
+>>
+>>
+>>-- test min-max optimization
+>>
+>>update statistics for table hive.hive.date_dim on every column sample;
+
+--- SQL operation complete.
+>>update statistics for table hive.hive.time_dim on every column sample;
+
+--- SQL operation complete.
+>>
+>>cqd PARQUET_PRED_PUSHDOWN 'ON';
+
+--- SQL operation complete.
+>>cqd GEN_HSHJ_MIN_MAX_OPT 'on';
+
+--- SQL operation complete.
+>>cqd parallel_num_esps '4';
+
+--- SQL operation complete.
+>>cqd nested_joins 'off';
+
+--- SQL operation complete.
+>>
+>>prepare xx from select count(*) from
++>hive.hive.store2_sales_parquet, -- sorted on ss_sold_date_sk
++>hive.hive.date_dim,
++>hive.hive.time_dim
++> where ss_sold_date_sk = d_date_sk and
++> ss_sold_date_sk = t_time_sk and d_year = 2001 and t_hour = 10 ;
+
+--- SQL command prepared.
+>>
+>>-- display the push-down predicates, which should include the
+>>-- min/max expressions from dimension table date_dim and time_dim.
+>>select cast(tokenstr('parquet_search_arguments', description, 'executor_predicates')
++> as char(400))
++> from table (explain(NULL,'XX'))
++>where position('parquet_search_arguments' in description) > 0 ;
+
+(EXPR)
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
+and( and( and( and( and( not( HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK is null ) not( HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <\:_sys_MinVal0 ) ) HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <= \:_sys_MaxVal0 ) not( HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <\:_sys_MinVal1 ) ) HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <= \:_sys_MaxVal1 ) )
+
+--- 1 row(s) selected.
+>>
+>>execute xx;
+
+(EXPR)
+--------------------
+
+ 0
+
+--- 1 row(s) selected.
+>>
+>>cqd parallel_num_esps reset;
+
+--- SQL operation complete.
+>>cqd nested_joins reset;
+
+--- SQL operation complete.
+>>
+>>-- test NJ into a sorted PARQUET table
+>>
+>>cqd parquet_pred_pushdown 'ON';
+
+--- SQL operation complete.
+>>cqd orc_njs 'ON';
+
+--- SQL operation complete.
+>>cqd parallel_num_esps '4';
+
+--- SQL operation complete.
+>>cqd HIVE_USE_EXT_TABLE_ATTRS 'off';
+
+--- SQL operation complete.
+>>
+>>prepare xx from select count(*) from hive.hive.customer_parquet, hive.hive.store_sales_sorted_parquet
++> where ss_sold_date_sk = c_customer_sk
++> and c_first_sales_date_sk = 4;
+
+--- SQL command prepared.
+>>
+>>explain xx;
+
+------------------------------------------------------------------ PLAN SUMMARY
+MODULE_NAME .............. DYNAMICALLY COMPILED
+STATEMENT_NAME ........... XX
+PLAN_ID .................. 212378573801436592
+ROWS_OUT ................. 1
+EST_TOTAL_COST .......... 23.94
+STATEMENT ................ select count(*)
+ from hive.hive.customer_parquet,
+ hive.hive.store_sales_sorted_parquet
+ where ss_sold_date_sk = c_customer_sk and
+ c_first_sales_date_sk = 4;
+
+
+------------------------------------------------------------------ NODE LISTING
+ROOT ====================================== SEQ_NO 8 ONLY CHILD 7
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0
+EST_TOTAL_COST .......... 23.94
+DESCRIPTION
+ est_memory_per_node .... 10240.00(Limit), 0.13(BMOs), 0.00(nBMOs) MB
+ max_card_est ........... 1
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length .......... 8
+ statement_index ........ 0
+ affinity_value ......... 0
+ max_max_cardinal 2,880,404
+ total_overflow_size .... 0.00 KB
+ esp_2_node_map ......... (-1(4 times))
+ esp_3_node_map ......... (-1(4 times))
+ 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
+ HBASE_FILTER_PREDS ..... 2
+ TRAF_INDEX_CREATE_OPT ON
+ TRAF_USE_REGION_XN ..... ON
+ HIVE_MAX_STRING_LENGTH 20
+ MODE_SEAHIVE ........... ON
+ TRAF_ENABLE_PARQUET_FOR 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
+ PARQUET_COLUMNS_PUSHDOW ON
+ PARQUET_AGGR_PUSHDOWN OFF
+ SCHEMA ................. TRAFODION.SCH
+ VOLATILE_TABLE_FIND_SUI SYSTEM
+ GEN_HSHJ_MIN_MAX_OPT ... ON
+ PARQUET_PRED_PUSHDOWN ON
+ ORC_NJS ................ ON
+ PARALLEL_NUM_ESPS ...... 4
+ HIVE_USE_EXT_TABLE_ATTR OFF
+ select_list ............ cast(sum(count(1 )))
+ input_variables ........ %(4), execution_count
+
+
+SORT_PARTIAL_AGGR_ROOT ==================== SEQ_NO 7 ONLY CHILD 6
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST .......... 23.94
+DESCRIPTION
+ max_card_est ........... 1
+ fragment_id ............ 0
+ parent_frag ............ (none)
+ fragment_type .......... master
+ record_length .......... 8
+ aggregates ............. sum(count(1 ))
+
+
+ESP_EXCHANGE ============================== SEQ_NO 6 ONLY CHILD 5
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST .......... 23.94
+DESCRIPTION
+ max_card_est ........... 1
+ fragment_id ............ 2
+ parent_frag ............ 0
+ fragment_type .......... esp
+ est_memory_per_node .... 29.297 KB
+ record_length .......... 8
+ buffer_size ........ 5,000
+ parent_processes ....... 1
+ child_processes ........ 4
+ child_partitioning_func hash2 partitioned 4 ways on (randomNum)
+
+
+SORT_PARTIAL_AGGR_LEAF ==================== SEQ_NO 5 ONLY CHILD 4
+REQUESTS_IN .............. 1
+ROWS_OUT ................. 1
+EST_OPER_COST ............ 0.01
+EST_TOTAL_COST .......... 23.94
+DESCRIPTION
+ max_card_est ........... 1
+ fragment_id ............ 2
+ parent_frag ............ 0
+ fragment_type .......... esp
+ record_length .......... 8
+ aggregates ............. count(1 )
+
+
+HYBRID_HASH_JOIN ========================== SEQ_NO 4 CHILDREN 3, 2
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 2,880,404
+EST_OPER_COST ............ 0.02
+EST_TOTAL_COST .......... 23.94
+DESCRIPTION
+ memory_quota_per_instan 800 MB
+ max_card_est ........... 2.8804e+06
+ fragment_id ............ 2
+ parent_frag ............ 0
+ fragment_type .......... esp
+ est_memory_per_instance 66.631 KB
+ record_length .......... 0
+ join_type .............. inner
+ join_method ............ hash
+ parallel_join_type ..... 2
+ min_max_cols ........... HIVE.CUSTOMER_PARQUET.C_CUSTOMER_SK
+ min_max_expr ........... \:_sys_MinVal0, \:_sys_MaxVal0
+ hash_join_predicates ... (HIVE.STORE_SALES_SORTED_PARQUET.SS_SOLD_DATE_SK =
+ HIVE.CUSTOMER_PARQUET.C_CUSTOMER_SK)
+
+
+PARQUET_SCAN ============================== SEQ_NO 3 NO CHILDREN
+TABLE_NAME ............... HIVE.HIVE.STORE_SALES_SORTED_PARQUET
+REQUESTS_IN .............. 1
+ROWS_OUT ......... 2,880,404
+EST_OPER_COST ........... 19.89
+EST_TOTAL_COST .......... 19.89
+DESCRIPTION
+ max_car
<TRUNCATED>