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/07/14 13:41:07 UTC
[2/3] incubator-trafodion git commit: Few fixes, details listed below.
Few fixes, details listed below.
-- fix an issue where multiple values inserted from a list would return
error but each value inserted on its own would succeed.
ex: create table ts (a timestamp);
insert into ts values ('2017-01-01 10:10:10'), ('2018-01-01 10:10:10');
-- sometimes errors returned from child during hive inserts were not
being returned. That has been fixed.
-- TRAFODION-2683 extension.
added a 'p' (prune) option which would cleanse and filter unneeded
explain output. This helps in reducing output especially
for larger explains.
Ex:
>>explain option 'p' select * from dual;
------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select * from dual;
------------------------------------------------------- NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
xn_access_mode ......... read_only
auto_query_retry ....... enabled
embedded_arkcmp ........ used
select_list ............ %(0)
input_variables ........ %(0), %(0), %(0)
VALUES ==================================== SEQ_NO 1 NO CHILDREN
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
tuple_expr ............. %(0)
--- SQL operation complete.
>>
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/5a244d53
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/5a244d53
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/5a244d53
Branch: refs/heads/master
Commit: 5a244d532425a6a4aff19a5cd2cd6ab637ea9b0a
Parents: b21f2a9
Author: Anoop Sharma <an...@esgyn.com>
Authored: Fri Jul 14 04:18:03 2017 +0000
Committer: Anoop Sharma <an...@esgyn.com>
Committed: Fri Jul 14 04:18:03 2017 +0000
----------------------------------------------------------------------
core/sql/comexe/ComTdbExeUtil.cpp | 5 +-
core/sql/comexe/ComTdbExeUtil.h | 6 +-
core/sql/executor/ExExeUtilExplain.cpp | 7 +
core/sql/executor/ExFastTransport.cpp | 9 +
core/sql/generator/GenRelExeUtil.cpp | 1 +
core/sql/optimizer/BindRelExpr.cpp | 13 ++
core/sql/optimizer/ItemOther.h | 8 +-
core/sql/optimizer/RelExeUtil.cpp | 8 +-
core/sql/optimizer/RelExeUtil.h | 10 +-
core/sql/optimizer/SynthType.cpp | 32 ++-
core/sql/regress/executor/EXPECTED131 | 104 +--------
core/sql/regress/executor/EXPECTED140 | 319 ++--------------------------
core/sql/regress/executor/TEST131 | 8 +-
core/sql/regress/executor/TEST140 | 22 +-
core/sql/regress/hive/EXPECTED005 | 117 +++++-----
core/sql/regress/hive/EXPECTED009 | 86 ++------
core/sql/regress/hive/TEST005 | 17 +-
core/sql/regress/hive/TEST009 | 4 +-
core/sql/regress/seabase/EXPECTED031 | 95 +++++++--
core/sql/regress/seabase/TEST031 | 25 +++
20 files changed, 322 insertions(+), 574 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/comexe/ComTdbExeUtil.cpp
----------------------------------------------------------------------
diff --git a/core/sql/comexe/ComTdbExeUtil.cpp b/core/sql/comexe/ComTdbExeUtil.cpp
index 96b2cd2..b295bc3 100644
--- a/core/sql/comexe/ComTdbExeUtil.cpp
+++ b/core/sql/comexe/ComTdbExeUtil.cpp
@@ -181,8 +181,9 @@ void ComTdbExeUtilDisplayExplain::displayContents(Space * space,ULng32 flag)
str_sprintf(buf, "\nFor ComTdbExeUtilDisplayExplain :");
space->allocateAndCopyToAlignedSpace(buf, str_len(buf), sizeof(short));
- str_sprintf(buf, "optionN = %d, optionF = %d, optionC = %d, optionE = %d, optionM = %d",
- isOptionN(), isOptionF(), isOptionC(), isOptionE(), isOptionM());
+ str_sprintf(buf, "optionN = %d, optionF = %d, optionC = %d, optionP = %d, optionE = %d, optionM = %d",
+ isOptionN(), isOptionF(), isOptionC(), isOptionP(),
+ isOptionE(), isOptionM());
space->allocateAndCopyToAlignedSpace(buf, str_len(buf), sizeof(short));
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/comexe/ComTdbExeUtil.h
----------------------------------------------------------------------
diff --git a/core/sql/comexe/ComTdbExeUtil.h b/core/sql/comexe/ComTdbExeUtil.h
index 5abbf1f..7e608b2 100644
--- a/core/sql/comexe/ComTdbExeUtil.h
+++ b/core/sql/comexe/ComTdbExeUtil.h
@@ -308,6 +308,7 @@ public:
NABoolean isOptionM() { return ((flags_ & OPTION_M) != 0); };
NABoolean isOptionN() { return ((flags_ & OPTION_N) != 0); };
NABoolean isOptionC() { return ((flags_ & OPTION_C) != 0); };
+ NABoolean isOptionP() { return ((flags_ & OPTION_P) != 0); };
void setOptionE(NABoolean v)
{(v ? flags_ |= OPTION_E : flags_ &= ~OPTION_E); };
@@ -319,6 +320,8 @@ public:
{(v ? flags_ |= OPTION_N : flags_ &= ~OPTION_N); };
void setOptionC(NABoolean v)
{(v ? flags_ |= OPTION_C : flags_ &= ~OPTION_C); };
+ void setOptionP(NABoolean v)
+ {(v ? flags_ |= OPTION_P : flags_ &= ~OPTION_P); };
private:
enum OpToFlag
@@ -327,7 +330,8 @@ private:
OPTION_E = 0x0002,
OPTION_M = 0x0004,
OPTION_N = 0x0008,
- OPTION_C = 0x0010
+ OPTION_C = 0x0010,
+ OPTION_P = 0x0020
};
UInt32 flags_; // 00-03
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/executor/ExExeUtilExplain.cpp
----------------------------------------------------------------------
diff --git a/core/sql/executor/ExExeUtilExplain.cpp b/core/sql/executor/ExExeUtilExplain.cpp
index 2880435..f9ccf73 100644
--- a/core/sql/executor/ExExeUtilExplain.cpp
+++ b/core/sql/executor/ExExeUtilExplain.cpp
@@ -2194,6 +2194,9 @@ typedef struct {
} FilterKeyValueStruct;
const FilterKeyValueStruct filterKeyValue[] =
{
+ {"MODULE_NAME", "###"},
+ {"plan_version", "###"},
+ {"statement_index", "###"},
{"PLAN_ID", "###"},
{"ROWS_OUT", "###"},
{"EST_OPER_COST", "###"},
@@ -2202,6 +2205,7 @@ const FilterKeyValueStruct filterKeyValue[] =
{"ROWS/REQUEST", "###"},
{"OPERATOR_COST", "###"},
{"ROLLUP_COST", "###"},
+ {"xn_autoabort_interval", "###"},
{"max_card_est", "###"},
{"max_max_cardinality", "###"},
{"total_overflow_size", "###"},
@@ -2317,6 +2321,9 @@ void ExExeUtilDisplayExplainTcb::FormatLine(const char *key, const char *inval,
{
val = valBuf;
valSize = strlen(val);
+
+ if (exeUtilTdb().isOptionP())
+ return; // prune
}
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/executor/ExFastTransport.cpp
----------------------------------------------------------------------
diff --git a/core/sql/executor/ExFastTransport.cpp b/core/sql/executor/ExFastTransport.cpp
index 5d3b9ec..13d6d85 100644
--- a/core/sql/executor/ExFastTransport.cpp
+++ b/core/sql/executor/ExFastTransport.cpp
@@ -975,6 +975,15 @@ ExWorkProcRetcode ExHdfsFastExtractTcb::work()
break;
case ex_queue::Q_SQLERROR:
{
+ if ((centry->getDiagsArea()) &&
+ (!pentry_down->getDiagsArea()))
+ {
+ ComDiagsArea *diagsArea = pentry_down->getDiagsArea();
+ diagsArea = ComDiagsArea::allocate(getGlobals()->getDefaultHeap());
+ pentry_down->setDiagsArea(diagsArea);
+ pentry_down->getDiagsArea()->mergeAfter(*centry->getDiagsArea());
+ }
+
pstate.step_ = EXTRACT_ERROR;
}
break;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/generator/GenRelExeUtil.cpp
----------------------------------------------------------------------
diff --git a/core/sql/generator/GenRelExeUtil.cpp b/core/sql/generator/GenRelExeUtil.cpp
index e6fb5a5..d514cb5 100644
--- a/core/sql/generator/GenRelExeUtil.cpp
+++ b/core/sql/generator/GenRelExeUtil.cpp
@@ -435,6 +435,7 @@ short ExeUtilDisplayExplain::codeGen(Generator * generator)
exe_util_tdb->setOptionM(isOptionM());
exe_util_tdb->setOptionN(isOptionN());
exe_util_tdb->setOptionC(isOptionC());
+ exe_util_tdb->setOptionP(isOptionP());
generator->initTdbFields(exe_util_tdb);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 45fa83d..2a5a1e6 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -8685,6 +8685,8 @@ RelExpr *TupleList::bindNode(BindWA *bindWA)
// the tuple list and target column
//
vidUnion->setSource(numTuples(), castToList()[j]);
+
+ vidUnion->setIsCastTo(TRUE);
}
vidUnion->bindNode(bindWA);
@@ -9703,6 +9705,17 @@ RelExpr *Insert::bindNode(BindWA *bindWA)
return this;
}
+ // if my child is a TupleList, then all tuples are to be converted/cast
+ // to the corresponding target type of the tgtColList.
+ // Pass on the tgtColList to TupleList so it can generate the Cast nodes
+ // with the target types during the TupleList::bindNode.
+ if (child(0)->getOperatorType() == REL_TUPLE_LIST) {
+ ValueIdList tgtColList;
+ getTableDesc()->getUserColumnList(tgtColList);
+ TupleList *tl = (TupleList *)child(0)->castToRelExpr();
+ tl->castToList() = tgtColList;
+ }
+
RelExpr *feResult = FastExtract::makeFastExtractTree(
getTableDesc(),
child(0).getPtr(),
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/optimizer/ItemOther.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemOther.h b/core/sql/optimizer/ItemOther.h
index de47998..30aef79 100644
--- a/core/sql/optimizer/ItemOther.h
+++ b/core/sql/optimizer/ItemOther.h
@@ -743,10 +743,16 @@ public:
void setIsTrueUnion(NABoolean v)
{ (v ? otherFlags_ |= IS_TRUE_UNION : otherFlags_ &= ~IS_TRUE_UNION); };
+ NABoolean isCastTo()
+ { return (otherFlags_ & IS_CAST_TO) != 0; }
+ void setIsCastTo(NABoolean v)
+ { (v ? otherFlags_ |= IS_CAST_TO : otherFlags_ &= ~IS_CAST_TO); };
+
private:
enum
{
- IS_TRUE_UNION = 0x0001
+ IS_TRUE_UNION = 0x0001,
+ IS_CAST_TO = 0x0002
};
// ValueIds of sources that are in corresponding positions
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/optimizer/RelExeUtil.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelExeUtil.cpp b/core/sql/optimizer/RelExeUtil.cpp
index ea6574d..4aad49d 100644
--- a/core/sql/optimizer/RelExeUtil.cpp
+++ b/core/sql/optimizer/RelExeUtil.cpp
@@ -1110,10 +1110,16 @@ short ExeUtilDisplayExplain::setOptionX(char c, Int32 &numOptions)
numOptions++;
break; // normal mode
case 'c' :
- if (isOptionC())
+ if (isOptionC() && (NOT isOptionP()))
return -1; // already specified
flags_ |= OPTION_C;
break; // cleansed mode
+ case 'p' :
+ if (isOptionP())
+ return -1; // already specified
+ flags_ |= OPTION_C;
+ flags_ |= OPTION_P;
+ break; // cleansed mode
default :
return -1; // error
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/optimizer/RelExeUtil.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelExeUtil.h b/core/sql/optimizer/RelExeUtil.h
index 0716eb6..db5855a 100644
--- a/core/sql/optimizer/RelExeUtil.h
+++ b/core/sql/optimizer/RelExeUtil.h
@@ -641,6 +641,11 @@ public:
// Filtererd patterns are discussed in executor/ExExeUtilExplain.cpp.
NABoolean isOptionC() { return ((flags_ & OPTION_C) != 0); };
+ // this option will cleanse(optionC()), prune and not return cleansed rows.
+ // This is useful to reduce the amount of explain output by eliminating
+ // cleansed rows.
+ NABoolean isOptionP() { return ((flags_ & OPTION_P) != 0); };
+
protected:
enum OpToFlag
{
@@ -657,7 +662,10 @@ protected:
OPTION_N = 0x0008,
// cleansed explain
- OPTION_C = 0x0010
+ OPTION_C = 0x0010,
+
+ // pruned explain
+ OPTION_P = 0x0020
};
short setOptionsX();
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/optimizer/SynthType.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/SynthType.cpp b/core/sql/optimizer/SynthType.cpp
index aa45243..c7362f6 100644
--- a/core/sql/optimizer/SynthType.cpp
+++ b/core/sql/optimizer/SynthType.cpp
@@ -5539,9 +5539,37 @@ const NAType *Translate::synthesizeType()
#pragma nowarn(1506) // warning elimination
const NAType *ValueIdUnion::synthesizeType()
{
- const NAType *result = 0;
-
+ const NAType *result = NULL;
CollIndex i = 0;
+
+ // if this is the case of insert values list tuples, then
+ // isTrueUnion() will not be set and isCastTo() will be set.
+ // Last entry of sources_ valueidlist will be set to target valueId,
+ // it is set in method TupleList::bindNode.
+ // Validate that each source entry is compatible with target type.
+ if ((NOT isTrueUnion()) &&
+ (isCastTo()))
+ {
+ result = &getSource(entries()-1).getType();
+ const NAType& opR = *result;
+ for (i = 0; i < entries()-1; i++)
+ {
+ getSource(i).coerceType(*result);
+ ValueId vidI = getSource(i);
+
+ const NAType& opI = vidI.getType();
+
+ if ((NOT opR.isCompatible(opI)) &&
+ (CmpCommon::getDefault(ALLOW_INCOMPATIBLE_OPERATIONS) == DF_OFF))
+ {
+ // 4055 The select lists or tuples must have compatible data types.
+ emitDyadicTypeSQLnameMsg(-4055, opR, opI);
+ return NULL;
+ }
+ } // for
+ return result;
+ }
+
for (i = 0; i < entries(); i++) {
result = &getSource(i).getType();
if (result->getTypeQualifier() != NA_UNKNOWN_TYPE)
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/executor/EXPECTED131
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED131 b/core/sql/regress/executor/EXPECTED131
index eb4185a..f5309b9 100644
--- a/core/sql/regress/executor/EXPECTED131
+++ b/core/sql/regress/executor/EXPECTED131
@@ -66,43 +66,27 @@
--- SQL operation complete.
>>-- should have small scanner on and endKey populated (JIRA 1446)
->>explain options 'c' select * from t1311k where uniq >2 and uniq <5;
+>>explain options 'p' select * from t1311k where uniq >2 and uniq <5;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select * from t1311k where uniq >2 and uniq <5;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
SCHEMA ................. T131SCH
HBASE_SMALL_SCANNER .... SYSTEM
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.T131SCH.T1311K.UNIQ,
TRAFODION.T131SCH.T1311K.C1000,
TRAFODION.T131SCH.T1311K.STR1
@@ -110,12 +94,7 @@ DESCRIPTION
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T1311K
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -124,52 +103,33 @@ DESCRIPTION
columns ................ all
begin_keys(excl) ....... 2
end_keys(excl) ......... 5
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:1
key_columns ............ UNIQ
executor_predicates .... (UNIQ > 2) and (UNIQ < 5)
--- SQL operation complete.
->>explain options 'c' select * from t1311k where uniq >2 and uniq <99;
+>>explain options 'p' select * from t1311k where uniq >2 and uniq <99;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select * from t1311k where uniq >2 and uniq <99;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
SCHEMA ................. T131SCH
HBASE_SMALL_SCANNER .... SYSTEM
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.T131SCH.T1311K.UNIQ,
TRAFODION.T131SCH.T1311K.C1000,
TRAFODION.T131SCH.T1311K.STR1
@@ -177,12 +137,7 @@ DESCRIPTION
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T1311K
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -191,9 +146,6 @@ DESCRIPTION
columns ................ all
begin_keys(excl) ....... 2
end_keys(excl) ........ 99
- cache_size ........... ###
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:1
key_columns ............ UNIQ
executor_predicates .... (UNIQ > 2) and (UNIQ < 99)
@@ -312,43 +264,27 @@ UNIQ C1000
--- 96 row(s) selected.
>>-- should get Small scanner off since the scanned rows do not fit in 64K block
->>explain options 'c' select * from t1311k where uniq >2 and uniq <30;
+>>explain options 'p' select * from t1311k where uniq >2 and uniq <30;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select * from t1311k where uniq >2 and uniq <30;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
SCHEMA ................. T131SCH
HBASE_SMALL_SCANNER .... SYSTEM
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.T131SCH.T1311K.UNIQ,
TRAFODION.T131SCH.T1311K.C1000,
TRAFODION.T131SCH.T1311K.STR1
@@ -356,12 +292,7 @@ DESCRIPTION
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T1311K
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -370,52 +301,33 @@ DESCRIPTION
columns ................ all
begin_keys(excl) ....... 2
end_keys(excl) ........ 30
- cache_size ........... ###
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:1
key_columns ............ UNIQ
executor_predicates .... (UNIQ > 2) and (UNIQ < 30)
--- SQL operation complete.
>>-- should get Small scanner on since the scanned rows fit in 128K block
->>explain options 'c' select * from t1311kbis where uniq >2 and uniq <30;
+>>explain options 'p' select * from t1311kbis where uniq >2 and uniq <30;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select * from t1311kbis where uniq >2 and uniq <30;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
SCHEMA ................. T131SCH
HBASE_SMALL_SCANNER .... SYSTEM
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.T131SCH.T1311KBIS.UNIQ,
TRAFODION.T131SCH.T1311KBIS.C1000,
TRAFODION.T131SCH.T1311KBIS.STR1
@@ -423,12 +335,7 @@ DESCRIPTION
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T1311KBIS
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -437,10 +344,7 @@ DESCRIPTION
columns ................ all
begin_keys(excl) ....... 2
end_keys(excl) ........ 30
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:1
key_columns ............ UNIQ
executor_predicates .... (UNIQ > 2) and (UNIQ < 30)
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/executor/EXPECTED140
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED140 b/core/sql/regress/executor/EXPECTED140
index c45d107..e3fec24 100644
--- a/core/sql/regress/executor/EXPECTED140
+++ b/core/sql/regress/executor/EXPECTED140
@@ -124,18 +124,18 @@
+> , SS_NET_PROFIT
+>from hive.hive.store_sales where ss_sold_date_sk is not null;
Task: LOAD Status: Started Object: TRAFODION.SCH.T140C
-Task: CLEANUP Status: Started Time: 2017-07-11 18:08:30.847
-Task: CLEANUP Status: Ended Time: 2017-07-11 18:08:30.869
-Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.022
-Task: LOADING DATA Status: Started Time: 2017-07-11 18:08:30.869
+Task: CLEANUP Status: Started Time: 2017-07-12 21:31:50.149
+Task: CLEANUP Status: Ended Time: 2017-07-12 21:31:50.167
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.019
+Task: LOADING DATA Status: Started Time: 2017-07-12 21:31:50.167
Rows Processed: 2750311
Error Rows: 0
-Task: LOADING DATA Status: Ended Time: 2017-07-11 18:09:00.406
-Task: LOADING DATA Status: Ended Elapsed Time: 00:00:29.538
-Task: COMPLETION Status: Started Time: 2017-07-11 18:09:00.406
+Task: LOADING DATA Status: Ended Time: 2017-07-12 21:32:23.296
+Task: LOADING DATA Status: Ended Elapsed Time: 00:00:33.128
+Task: COMPLETION Status: Started Time: 2017-07-12 21:32:23.296
Rows Loaded: 2750311
-Task: COMPLETION Status: Ended Time: 2017-07-11 18:09:01.113
-Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.706
+Task: COMPLETION Status: Ended Time: 2017-07-12 21:32:24.240
+Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.728
--- 2750311 row(s) loaded.
>>update statistics for table t140c on every column sample;
@@ -151,36 +151,21 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.706
>>obey TEST140(run);
>>-- test returned rows with or without adding key column and test of all pushdown functions with null or non null column
>>-- only one column retrieved
->>explain options 'c' select a from t140 where b>500;
+>>explain options 'p' select a from t140 where b>500;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select a from t140 where b>500;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -194,18 +179,12 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140.A
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -214,10 +193,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:3
pushed_down_rpn ........ (#1:4>?)
key_columns ............ UNIQ, UNIQ2
@@ -237,36 +213,21 @@ A
--- 6 row(s) selected.
>>--verify that the column retrieved for null handling is not the _SALT_ for optimization of skips, salt being the last physical column
->>explain options 'c' select an from t140b where b<=200;
+>>explain options 'p' select an from t140b where b<=200;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select an from t140b where b<=200;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -280,18 +241,12 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140B.AN
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140B
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -300,46 +255,28 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:2,#1:6
pushed_down_rpn ........ (#1:4<=?)
key_columns ............ _SALT_, UNIQ, UNIQ2
--- SQL operation complete.
>>-- we should have 2 columns retrieved since an is nullable
->>explain options 'c' select an from t140 where b<=200;
+>>explain options 'p' select an from t140 where b<=200;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select an from t140 where b<=200;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -353,18 +290,12 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140.AN
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -373,10 +304,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:2,#1:6
pushed_down_rpn ........ (#1:4<=?)
key_columns ............ UNIQ, UNIQ2
@@ -394,36 +322,21 @@ AN
--- 4 row(s) selected.
>>-- should not get back 2 column, only one since predicate says an is not null
->>explain options 'c' select an from t140 where b=200 and an is not null;
+>>explain options 'p' select an from t140 where b=200 and an is not null;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select an from t140 where b=200 and an is not null;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -437,19 +350,13 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140.AN
input_variables ........ %(200)
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -458,10 +365,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:6
pushed_down_rpn ........ (#1:4=?)(#1:6 is_not_null.)AND
key_columns ............ UNIQ, UNIQ2
@@ -476,36 +380,21 @@ AN
--- 1 row(s) selected.
>>-- should only get 2 column back since a is not null, no need to add key column
->>explain options 'c' select an, a from t140 where b!=500;
+>>explain options 'p' select an, a from t140 where b!=500;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select an, a from t140 where b!=500;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -519,18 +408,12 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140.AN, TRAFODION.SCH.T140.A
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -539,10 +422,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:3,#1:6
pushed_down_rpn ........ (#1:4<?)(#1:4>?)OR
key_columns ............ UNIQ, UNIQ2
@@ -594,36 +474,21 @@ AN
13
--- 4 row(s) selected.
->>explain options 'c' select an from t140 where bn=201 and an is not null;
+>>explain options 'p' select an from t140 where bn=201 and an is not null;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select an from t140 where bn=201 and an is not null;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -637,19 +502,13 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140.AN
input_variables ........ %(201)
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -658,10 +517,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:6
pushed_down_rpn ........ (#1:7=.?)(#1:6 is_not_null.)AND
key_columns ............ UNIQ, UNIQ2
@@ -675,36 +531,21 @@ AN
21
--- 1 row(s) selected.
->>explain options 'c' select an, a from t140 where bn!=501;
+>>explain options 'p' select an, a from t140 where bn!=501;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select an, a from t140 where bn!=501;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -718,18 +559,12 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140.AN, TRAFODION.SCH.T140.A
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -738,10 +573,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:3,#1:6
pushed_down_rpn ........ (#1:7<.?)(#1:7>.?)OR
key_columns ............ UNIQ, UNIQ2
@@ -808,36 +640,21 @@ A
--- 5 row(s) selected.
>>-- make sure that we only retrieve one column as an cannot be null in the result set.
->>explain options 'c' select an from t140 where an between 20 and 40;
+>>explain options 'p' select an from t140 where an between 20 and 40;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select an from t140 where an between 20 and 40;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -851,18 +668,12 @@ DESCRIPTION
SCHEMA ................. TRAFODION.SCH
TRAF_ALIGNED_ROW_FORMAT OFF
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ TRAFODION.SCH.T140.AN
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -871,10 +682,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
small_scanner .......... ON
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:6
pushed_down_rpn ........ (#1:6>=.?)(#1:6<=.?)AND
key_columns ............ UNIQ, UNIQ2
@@ -913,36 +721,21 @@ AN
>>cqd hbase_dop_parallel_scanner '2.0';
--- SQL operation complete.
->>explain options 'c' select avg(a) from t140b;
+>>explain options 'p' select avg(a) from t140b;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select avg(a) from t140b;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -958,19 +751,13 @@ DESCRIPTION
PARALLEL_NUM_ESPS ...... 1
HBASE_DOP_PARALLEL_SCAN 2
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T140B.
A)) * 10000 ...0)) / cast(count(1 )))) / 10000
...0)))
SORT_SCALAR_AGGR ========================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -979,12 +766,7 @@ DESCRIPTION
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140B
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -993,10 +775,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
parallel_scanner ....... 2
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:3
key_columns ............ _SALT_, UNIQ, UNIQ2
@@ -1013,36 +792,21 @@ DESCRIPTION
>>cqd hbase_dop_parallel_scanner '1.0';
--- SQL operation complete.
->>explain options 'c' select avg(a) from t140b;
+>>explain options 'p' select avg(a) from t140b;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select avg(a) from t140b;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -1058,19 +822,13 @@ DESCRIPTION
PARALLEL_NUM_ESPS ...... 1
HBASE_DOP_PARALLEL_SCAN 1
GENERATE_EXPLAIN ....... ON
- ObjectUIDs ........... ###
select_list ............ cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T140B.
A)) * 10000 ...0)) / cast(count(1 )))) / 10000
...0)))
SORT_SCALAR_AGGR ========================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -1079,12 +837,7 @@ DESCRIPTION
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140B
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -1093,10 +846,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
parallel_scanner ....... 1
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:3
key_columns ............ _SALT_, UNIQ, UNIQ2
@@ -1109,36 +859,21 @@ DESCRIPTION
45
--- 1 row(s) selected.
->>explain options 'c' select avg(a) from t140b;
+>>explain options 'p' select avg(a) from t140b;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select avg(a) from t140b;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 3 ONLY CHILD 2
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -1153,19 +888,13 @@ DESCRIPTION
HBASE_SMALL_SCANNER .... OFF
PARALLEL_NUM_ESPS ...... 1
HBASE_DOP_PARALLEL_SCAN 1
- ObjectUIDs ........... ###
select_list ............ cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T140B.
A)) * 10000 ...0)) / cast(count(1 )))) / 10000
...0)))
SORT_SCALAR_AGGR ========================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -1174,12 +903,7 @@ DESCRIPTION
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T140B
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -1188,10 +912,7 @@ DESCRIPTION
columns ................ all
begin_keys(incl)
end_keys(incl)
- cache_size ........... ###
parallel_scanner ....... 1
- probes ............... ###
- rows_accessed ........ ###
column_retrieved ....... #1:3
key_columns ............ _SALT_, UNIQ, UNIQ2
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/executor/TEST131
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST131 b/core/sql/regress/executor/TEST131
index 0d7d4dd..8c78dd5 100644
--- a/core/sql/regress/executor/TEST131
+++ b/core/sql/regress/executor/TEST131
@@ -90,12 +90,12 @@ set schema t131sch;
-- suspect this is because histogram is cached per session and we are starting a new session, reloading Histogram...
cqd hbase_small_scanner 'SYSTEM';
-- should have small scanner on and endKey populated (JIRA 1446)
-explain options 'c' select * from t1311k where uniq >2 and uniq <5;
-explain options 'c' select * from t1311k where uniq >2 and uniq <99;
+explain options 'p' select * from t1311k where uniq >2 and uniq <5;
+explain options 'p' select * from t1311k where uniq >2 and uniq <99;
select uniq, c1000 from t1311k where uniq >2 and uniq <5;
select uniq, c1000 from t1311k where uniq >2 and uniq <99;
-- should get Small scanner off since the scanned rows do not fit in 64K block
-explain options 'c' select * from t1311k where uniq >2 and uniq <30;
+explain options 'p' select * from t1311k where uniq >2 and uniq <30;
-- should get Small scanner on since the scanned rows fit in 128K block
-explain options 'c' select * from t1311kbis where uniq >2 and uniq <30;
+explain options 'p' select * from t1311kbis where uniq >2 and uniq <30;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/executor/TEST140
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST140 b/core/sql/regress/executor/TEST140
index 3b08cd0..55aec83 100644
--- a/core/sql/regress/executor/TEST140
+++ b/core/sql/regress/executor/TEST140
@@ -143,31 +143,31 @@ cqd cache_histograms_in_kb reset;
?section run
-- test returned rows with or without adding key column and test of all pushdown functions with null or non null column
-- only one column retrieved
-explain options 'c' select a from t140 where b>500;
+explain options 'p' select a from t140 where b>500;
select a from t140 where b>=500;
--verify that the column retrieved for null handling is not the _SALT_ for optimization of skips, salt being the last physical column
-explain options 'c' select an from t140b where b<=200;
+explain options 'p' select an from t140b where b<=200;
-- we should have 2 columns retrieved since an is nullable
-explain options 'c' select an from t140 where b<=200;
+explain options 'p' select an from t140 where b<=200;
select an from t140 where b<=200;
-- should not get back 2 column, only one since predicate says an is not null
-explain options 'c' select an from t140 where b=200 and an is not null;
+explain options 'p' select an from t140 where b=200 and an is not null;
select an from t140 where b=200 and an is not null;
-- should only get 2 column back since a is not null, no need to add key column
-explain options 'c' select an, a from t140 where b!=500;
+explain options 'p' select an, a from t140 where b!=500;
select an, a from t140 where b!=500;
-- see if we handle null logic correctly
select a from t140 where bn>=501;
select an from t140 where bn<=201;
-explain options 'c' select an from t140 where bn=201 and an is not null;
+explain options 'p' select an from t140 where bn=201 and an is not null;
select an from t140 where bn=201 and an is not null;
-explain options 'c' select an, a from t140 where bn!=501;
+explain options 'p' select an, a from t140 where bn!=501;
select an, a from t140 where bn!=501;
select a from t140 where an is null;
select a from t140 where an is not null;
select a from t140 where a between 20 and 40;
-- make sure that we only retrieve one column as an cannot be null in the result set.
-explain options 'c' select an from t140 where an between 20 and 40;
+explain options 'p' select an from t140 where an between 20 and 40;
select an from t140 where an between 20 and 40;
select an from t140 where an in (21,41,51,61,10);
@@ -177,13 +177,13 @@ cqd hbase_small_scanner 'OFF';
cqd parallel_num_esps '1';
-- force 2 threads
cqd hbase_dop_parallel_scanner '2.0';
-explain options 'c' select avg(a) from t140b;
+explain options 'p' select avg(a) from t140b;
select avg(a) from t140b;
-- force 100% of threads (with 2 partition this is 2 threads)
cqd hbase_dop_parallel_scanner '1.0';
-explain options 'c' select avg(a) from t140b;
+explain options 'p' select avg(a) from t140b;
select avg(a) from t140b;
-explain options 'c' select avg(a) from t140b;
+explain options 'p' select avg(a) from t140b;
select avg(a) from t140b;
cqd parallel_num_esps '1';
cqd hbase_dop_parallel_scanner '1.0';
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/hive/EXPECTED005
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/EXPECTED005 b/core/sql/regress/hive/EXPECTED005
index 909b448..f91b49a 100644
--- a/core/sql/regress/hive/EXPECTED005
+++ b/core/sql/regress/hive/EXPECTED005
@@ -72,7 +72,7 @@
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661048, failedModTS = 1493661158, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/customer_ddl
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978885, failedModTS = 1499978946, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl
C_PREFERRED_CUST_FLAG (EXPR)
------------------------- --------------------
@@ -108,7 +108,7 @@ Y 9525
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661048, failedModTS = 1493661158, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/customer_ddl
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978885, failedModTS = 1499978946, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl
C_PREFERRED_CUST_FLAG (EXPR)
------------------------- --------------------
@@ -177,7 +177,7 @@ Y 9525
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661163, failedModTS = 1493661174, failedLoc = hdfs://localhost:25600/user/hive/warehouse/newtable
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978950, failedModTS = 1499978958, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable
A
-------------------------
@@ -227,7 +227,7 @@ xyz
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661158, failedModTS = 1493661205, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/customer_ddl
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978946, failedModTS = 1499978976, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl
C_PREFERRED_CUST_FLAG (EXPR)
------------------------- --------------------
@@ -242,7 +242,7 @@ Y 18984
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661158, failedModTS = 1493661205, failedLoc = hdfs://localhost:25600/user/trafodion/hive/exttables/customer_ddl
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978946, failedModTS = 1499978976, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl
C_PREFERRED_CUST_FLAG (EXPR)
------------------------- --------------------
@@ -298,7 +298,7 @@ Y 18984
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661199, failedModTS = 1493661218, failedLoc = hdfs://localhost:25600/user/hive/warehouse/newtable
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978973, failedModTS = 1499978982, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable
A B
----------- -------------------------
@@ -617,18 +617,18 @@ C1 C2 C3 C4 C
--- 1 row(s) selected.
>>load with continue on error into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD
-Task: CLEANUP Status: Started Time: 2017-05-01 17:54:50.429
-Task: CLEANUP Status: Ended Time: 2017-05-01 17:54:50.445
-Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.015
-Task: LOADING DATA Status: Started Time: 2017-05-01 17:54:50.445
+Task: CLEANUP Status: Started Time: 2017-07-13 20:50:54.956
+Task: CLEANUP Status: Ended Time: 2017-07-13 20:50:54.975
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.018
+Task: LOADING DATA Status: Started Time: 2017-07-13 20:50:54.975
Rows Processed: 8
Error Rows: 5
-Task: LOADING DATA Status: Ended Time: 2017-05-01 17:54:50.905
-Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.460
-Task: COMPLETION Status: Started Time: 2017-05-01 17:54:50.905
+Task: LOADING DATA Status: Ended Time: 2017-07-13 20:50:55.307
+Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.332
+Task: COMPLETION Status: Started Time: 2017-07-13 20:50:55.307
Rows Loaded: 3
-Task: COMPLETION Status: Ended Time: 2017-05-01 17:54:51.575
-Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.670
+Task: COMPLETION Status: Ended Time: 2017-07-13 20:50:55.655
+Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.348
--- 3 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;
@@ -644,19 +644,19 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.670
--- 3 row(s) deleted.
>>load with log error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD
-Task: CLEANUP Status: Started Time: 2017-05-01 17:54:52.708
-Task: CLEANUP Status: Ended Time: 2017-05-01 17:54:52.726
-Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.019
- Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170501_175452
-Task: LOADING DATA Status: Started Time: 2017-05-01 17:54:52.726
+Task: CLEANUP Status: Started Time: 2017-07-13 20:50:56.697
+Task: CLEANUP Status: Ended Time: 2017-07-13 20:50:56.705
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.009
+ Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205056
+Task: LOADING DATA Status: Started Time: 2017-07-13 20:50:56.705
Rows Processed: 8
Error Rows: 5
-Task: LOADING DATA Status: Ended Time: 2017-05-01 17:54:53.423
-Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.697
-Task: COMPLETION Status: Started Time: 2017-05-01 17:54:53.423
+Task: LOADING DATA Status: Ended Time: 2017-07-13 20:50:57.153
+Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.448
+Task: COMPLETION Status: Started Time: 2017-07-13 20:50:57.153
Rows Loaded: 3
-Task: COMPLETION Status: Ended Time: 2017-05-01 17:54:55.924
-Task: COMPLETION Status: Ended Elapsed Time: 00:00:02.501
+Task: COMPLETION Status: Ended Time: 2017-07-13 20:50:57.695
+Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.542
--- 3 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;
@@ -669,19 +669,19 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:02.501
--- 1 row(s) selected.
>>load with log error rows to '/user/trafodion/bulkload/logs/TEST005' into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD
-Task: CLEANUP Status: Started Time: 2017-05-01 17:54:56.974
-Task: CLEANUP Status: Ended Time: 2017-05-01 17:54:56.990
-Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.015
- Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170501_175456
-Task: LOADING DATA Status: Started Time: 2017-05-01 17:54:56.990
+Task: CLEANUP Status: Started Time: 2017-07-13 20:50:58.677
+Task: CLEANUP Status: Ended Time: 2017-07-13 20:50:58.685
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.008
+ Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205058
+Task: LOADING DATA Status: Started Time: 2017-07-13 20:50:58.685
Rows Processed: 8
Error Rows: 5
-Task: LOADING DATA Status: Ended Time: 2017-05-01 17:54:57.861
-Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.871
-Task: COMPLETION Status: Started Time: 2017-05-01 17:54:57.861
+Task: LOADING DATA Status: Ended Time: 2017-07-13 20:50:59.124
+Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.439
+Task: COMPLETION Status: Started Time: 2017-07-13 20:50:59.124
Rows Loaded: 3
-Task: COMPLETION Status: Ended Time: 2017-05-01 17:54:58.546
-Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.685
+Task: COMPLETION Status: Ended Time: 2017-07-13 20:50:59.453
+Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.329
--- 3 row(s) loaded.
>>select count(*) from trafodion.seabase.traf_tbl_bad;
@@ -697,10 +697,10 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.685
--- 6 row(s) deleted.
>>load with stop after 3 error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD
-Task: CLEANUP Status: Started Time: 2017-05-01 17:54:59.692
-Task: CLEANUP Status: Ended Time: 2017-05-01 17:54:59.705
-Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.013
-Task: LOADING DATA Status: Started Time: 2017-05-01 17:54:59.705
+Task: CLEANUP Status: Started Time: 2017-07-13 20:51:00.456
+Task: CLEANUP Status: Ended Time: 2017-07-13 20:51:00.475
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.018
+Task: LOADING DATA Status: Started Time: 2017-07-13 20:51:00.475
*** ERROR[8113] The maximum number of error rows is exceeded.
@@ -715,11 +715,11 @@ Task: LOADING DATA Status: Started Time: 2017-05-01 17:54:59.705
--- 1 row(s) selected.
>>load with log error rows, stop after 3 error rows into trafodion.seabase.traf_tbl_bad select * from tbl_bad;
Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD
-Task: CLEANUP Status: Started Time: 2017-05-01 17:55:01.163
-Task: CLEANUP Status: Ended Time: 2017-05-01 17:55:01.174
-Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.011
- Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170501_175501
-Task: LOADING DATA Status: Started Time: 2017-05-01 17:55:01.174
+Task: CLEANUP Status: Started Time: 2017-07-13 20:51:01.786
+Task: CLEANUP Status: Ended Time: 2017-07-13 20:51:01.795
+Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.009
+ Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205101
+Task: LOADING DATA Status: Started Time: 2017-07-13 20:51:01.795
*** ERROR[8113] The maximum number of error rows is exceeded.
@@ -807,7 +807,7 @@ Task: LOADING DATA Status: Started Time: 2017-05-01 17:55:01.174
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661327, failedModTS = 1493661347, failedLoc = hdfs://localhost:25600/user/hive/warehouse/thive
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979080, failedModTS = 1499979093, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive
A
-----------
@@ -842,7 +842,7 @@ A
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661349, failedModTS = 1493661370, failedLoc = hdfs://localhost:25600/user/hive/warehouse/thive
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979096, failedModTS = 1499979110, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive
A
-----------
@@ -869,7 +869,7 @@ A B
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661370, failedModTS = 1493661376, failedLoc = hdfs://localhost:25600/user/hive/warehouse/thive
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979110, failedModTS = 1499979116, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive
--- 0 row(s) selected.
>>insert into hive.hive.thive values (10, 20);
@@ -893,7 +893,7 @@ A B
*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
-*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1493661378, failedModTS = 1493661381, failedLoc = hdfs://localhost:25600/user/hive/warehouse/thive
+*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979118, failedModTS = 1499979120, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive
--- 0 row(s) selected.
>>
@@ -972,7 +972,6 @@ t005part.a t005part.b t005part.c
--- SQL operation failed with errors.
>>
->>
>>-- should return error
>>purgedata hive.hive.thive;
@@ -985,7 +984,7 @@ t005part.a t005part.b t005part.c
>>invoke hive.hive.thive_insert_smallint;
-- Definition of hive table THIVE_INSERT_SMALLINT
--- Definition current Mon May 1 17:59:39 2017
+-- Definition current Thu Jul 13 20:54:19 2017
(
A SMALLINT
@@ -1011,8 +1010,8 @@ CREATE TABLE THIVE_INSERT_SMALLINT
>>cqd hive_insert_error_mode '0';
--- SQL operation complete.
->>insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
-+> (22222222);
+>>insert into hive.hive.thive_insert_smallint select * from
++> (values (10), (11111111), (21), (22222222));
--- 4 row(s) inserted.
>>select * from hive.hive.thive_insert_smallint;
@@ -1032,8 +1031,8 @@ A
>>cqd hive_insert_error_mode '1';
--- SQL operation complete.
->>insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
-+> (22222222);
+>>insert into hive.hive.thive_insert_smallint select * from
++> (values (10), (11111111), (21), (22222222));
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:11111111 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.
@@ -1048,8 +1047,8 @@ A
>>cqd hive_insert_error_mode '2';
--- SQL operation complete.
->>insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
-+> (22222222);
+>>insert into hive.hive.thive_insert_smallint select * from
++> (values (10), (11111111), (21), (22222222));
--- 2 row(s) inserted.
>>select * from hive.hive.thive_insert_smallint;
@@ -1068,8 +1067,8 @@ A
>>cqd hive_insert_error_mode '3';
--- SQL operation complete.
->>insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
-+> (22222222);
+>>insert into hive.hive.thive_insert_smallint select * from
++> (values (10), (11111111), (21), (22222222));
--- 4 row(s) inserted.
>>select * from hive.hive.thive_insert_smallint;
@@ -1090,7 +1089,7 @@ A
>>invoke hive.hive.thive_insert_varchar;
-- Definition of hive table THIVE_INSERT_VARCHAR
--- Definition current Mon May 1 17:59:52 2017
+-- Definition current Thu Jul 13 20:54:28 2017
(
A VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/hive/EXPECTED009
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/EXPECTED009 b/core/sql/regress/hive/EXPECTED009
index e771e54..e318532 100644
--- a/core/sql/regress/hive/EXPECTED009
+++ b/core/sql/regress/hive/EXPECTED009
@@ -214,7 +214,7 @@ A B C
>>invoke t009hivecust1;
-- Definition of Trafodion table TRAFODION.HIVE_T009.T009HIVECUST1
--- Definition current Tue Jul 11 18:13:26 2017
+-- Definition current Wed Jul 12 21:37:45 2017
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -263,7 +263,7 @@ A B C
>>invoke t009hivecust2;
-- Definition of Trafodion table TRAFODION.HIVE_T009.T009HIVECUST2
--- Definition current Tue Jul 11 18:13:36 2017
+-- Definition current Wed Jul 12 21:37:55 2017
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -434,7 +434,7 @@ T009T2
>>invoke hive.sch_t009.t009t1;
-- Definition of hive table T009T1
--- Definition current Tue Jul 11 18:14:50 2017
+-- Definition current Wed Jul 12 21:39:07 2017
(
A INT
@@ -516,7 +516,7 @@ ROW_ID COLS
>>invoke bblike1;
-- Definition of Trafodion table TRAFODION.HIVE_T009.BBLIKE1
--- Definition current Tue Jul 11 18:15:03 2017
+-- Definition current Wed Jul 12 21:39:20 2017
(
ROW_ID VARCHAR(100) CHARACTER SET ISO88591
@@ -533,7 +533,7 @@ ROW_ID COLS
>>invoke bblike2;
-- Definition of Trafodion table TRAFODION.HIVE_T009.BBLIKE2
--- Definition current Tue Jul 11 18:15:07 2017
+-- Definition current Wed Jul 12 21:39:25 2017
(
ROW_ID VARCHAR(100) CHARACTER SET ISO88591
@@ -572,7 +572,7 @@ ROW_ID COLS
>>invoke hive.hive.store_sales;
-- Definition of hive table STORE_SALES
--- Definition current Tue Jul 11 18:15:13 2017
+-- Definition current Wed Jul 12 21:39:32 2017
(
SS_SOLD_DATE_SK INT
@@ -614,7 +614,7 @@ ROW_ID COLS
>>explain options 'c' s;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
+MODULE_NAME ............ ###
STATEMENT_NAME ........... S
PLAN_ID ................ ###
ROWS_OUT ............... ###
@@ -633,14 +633,14 @@ DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
+ statement_index ...... ###
affinity_value ....... ###
max_max_cardinality ###
total_overflow_size ###
xn_access_mode ......... read_only
- xn_autoabort_interval 0
+ xn_autoabort_interval ###
auto_query_retry ....... enabled
- plan_version ....... 2,600
+ plan_version ......... ###
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
@@ -740,14 +740,10 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
. . 1 hive_scan CUSTOMER 1.00E+002
--- SQL operation complete.
->>explain options 'c' s;
+>>explain options 'p' s;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... S
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select *
from customer, store_sales
where store_sales.ss_item_sk =
@@ -757,23 +753,12 @@ MUST_MATCH ............... forced nested join(forced scan, forced scan)
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -823,12 +808,7 @@ DESCRIPTION
NESTED_JOIN =============================== SEQ_NO 3 CHILDREN 1, 2
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -838,12 +818,7 @@ DESCRIPTION
HIVE_SCAN ================================= SEQ_NO 2 NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.STORE_SALES
-REQUESTS_IN ............ ###
-ROWS/REQUEST ........... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -882,12 +857,7 @@ DESCRIPTION
HIVE_SCAN ================================= SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.CUSTOMER
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
@@ -947,7 +917,7 @@ DESCRIPTION
>>invoke hive.hive.date_dim;
-- Definition of hive table DATE_DIM
--- Definition current Tue Jul 11 18:15:19 2017
+-- Definition current Wed Jul 12 21:39:40 2017
(
D_DATE_SK INT
@@ -1089,7 +1059,7 @@ CREATE EXTERNAL TABLE DATE_DIM
>>explain options 'c' s;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
+MODULE_NAME ............ ###
STATEMENT_NAME ........... S
PLAN_ID ................ ###
ROWS_OUT ............... ###
@@ -1110,14 +1080,14 @@ DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
+ statement_index ...... ###
affinity_value ....... ###
max_max_cardinality ###
total_overflow_size ###
xn_access_mode ......... read_only
- xn_autoabort_interval 0
+ xn_autoabort_interval ###
auto_query_retry ....... enabled
- plan_version ....... 2,600
+ plan_version ......... ###
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
@@ -1182,7 +1152,7 @@ DESCRIPTION
>>invoke hive.hive.date_dim;
-- Definition of hive table DATE_DIM
--- Definition current Tue Jul 11 18:15:35 2017
+-- Definition current Wed Jul 12 21:39:56 2017
(
D_DATE_SK INT
@@ -1321,14 +1291,10 @@ CREATE EXTERNAL TABLE DATE_DIM
*** WARNING[6008] Statistics for column (D_DATE) from table HIVE.HIVE.DATE_DIM were not available. As a result, the access path chosen might not be the best possible.
--- SQL command prepared.
->>explain options 'c' s;
+>>explain options 'p' s;
------------------------------------------------------------------ PLAN SUMMARY
-MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... S
-PLAN_ID ................ ###
-ROWS_OUT ............... ###
-EST_TOTAL_COST ......... ###
STATEMENT ................ select *
from hive.hive.date_dim
where d_date = date '2016-01-27';
@@ -1336,23 +1302,12 @@ STATEMENT ................ select *
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
- statement_index ........ 0
- affinity_value ....... ###
- max_max_cardinality ###
- total_overflow_size ###
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
@@ -1387,12 +1342,7 @@ DESCRIPTION
HIVE_SCAN ================================= SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.DATE_DIM
-REQUESTS_IN ............ ###
-ROWS_OUT ............... ###
-EST_OPER_COST .......... ###
-EST_TOTAL_COST ......... ###
DESCRIPTION
- max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/hive/TEST005
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/TEST005 b/core/sql/regress/hive/TEST005
index 6cb42c9..b0c6808 100644
--- a/core/sql/regress/hive/TEST005
+++ b/core/sql/regress/hive/TEST005
@@ -374,7 +374,6 @@ sh regrhive.ksh -f TEST005_junk | tee -a LOG005;
-- should return error
truncate hive.hive.t005part partition ('b=12');
-
-- should return error
purgedata hive.hive.thive;
@@ -384,26 +383,26 @@ showddl hive.hive.thive_insert_smallint;
truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '0';
-insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
- (22222222);
+insert into hive.hive.thive_insert_smallint select * from
+ (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;
truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '1';
-insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
- (22222222);
+insert into hive.hive.thive_insert_smallint select * from
+ (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;
truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '2';
-insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
- (22222222);
+insert into hive.hive.thive_insert_smallint select * from
+ (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;
truncate hive.hive.thive_insert_smallint;
cqd hive_insert_error_mode '3';
-insert into hive.hive.thive_insert_smallint values (10), (11111111), (21),
- (22222222);
+insert into hive.hive.thive_insert_smallint select * from
+ (values (10), (11111111), (21), (22222222));
select * from hive.hive.thive_insert_smallint;
cqd hive_max_string_length_in_bytes '2';
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a244d53/core/sql/regress/hive/TEST009
----------------------------------------------------------------------
diff --git a/core/sql/regress/hive/TEST009 b/core/sql/regress/hive/TEST009
index 5edb20b..991cba8 100755
--- a/core/sql/regress/hive/TEST009
+++ b/core/sql/regress/hive/TEST009
@@ -242,7 +242,7 @@ control query shape nested_join(scan(path 'CUSTOMER'),
prepare s from select * from customer, store_sales
where store_sales.ss_item_sk = customer.c_customer_sk;
explain options 'fc' s;
-explain options 'c' s;
+explain options 'p' s;
control query shape cut;
set schema trafodion.sch;
@@ -272,7 +272,7 @@ create external table date_dim
invoke hive.hive.date_dim;
showddl hive.hive.date_dim;
prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';
-explain options 'c' s;
+explain options 'p' s;
-- error cases