You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by su...@apache.org on 2016/08/23 16:26:15 UTC
[1/5] incubator-trafodion git commit: Intial change for unnesting
Repository: incubator-trafodion
Updated Branches:
refs/heads/master afdca3094 -> 0b0665b67
Intial change for unnesting
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/24e252c1
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/24e252c1
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/24e252c1
Branch: refs/heads/master
Commit: 24e252c1c00c9a4d26d0b233d8047c25b089c845
Parents: 2a0a301
Author: Suresh Subbiah <su...@apache.org>
Authored: Fri Aug 5 21:50:58 2016 +0000
Committer: Suresh Subbiah <su...@apache.org>
Committed: Fri Aug 5 21:50:58 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 41 ++++++++++++++++++++++++++-------
core/sql/sqlcomp/nadefaults.cpp | 5 ++++
2 files changed, 38 insertions(+), 8 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/24e252c1/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index 120e5e1..9fd9978 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -2358,7 +2358,13 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
GroupByAgg * gbyNode = (GroupByAgg *) child(1)->castToRelExpr();
RelExpr * oldRightGrandChild = child(1)->child(0)->castToRelExpr();
NABoolean candidateForLeftJoin = candidateForSubqueryLeftJoinConversion();
+ NABoolean nestedAggInSubQ = FALSE;
+ if (oldRightGrandChild->getOperator().match(REL_GROUPBY))
+ {
+ oldRightGrandChild = oldRightGrandChild->child(0)->castToRelExpr();
+ nestedAggInSubQ = TRUE ;
+ }
if (oldRightGrandChild->getOperator().match(REL_ANY_SEMIJOIN) ||
oldRightGrandChild->getOperator().match(REL_ANY_ANTI_SEMIJOIN) ||
oldRightGrandChild->getOperator().match(REL_GROUPBY))
@@ -2369,7 +2375,7 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
{
*CmpCommon::diags() << DgSqlCode(2997)
- << DgString1("Subquery was not unnested. Reason: Right grandchild of TSJ is a semijoin or a group by");
+ << DgString1("Subquery was not unnested. Reason: Right grandchild of TSJ is a semijoin or has more than one group by");
}
}
// -----------------------------------------------------------------------
@@ -2490,7 +2496,10 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
if (candidateForLeftJoin)
normWARef.incrementLeftJoinConversionCount();
- gbyNode->child(0) = predFilterNode;
+ if (nestedAggInSubQ)
+ gbyNode->child(0)->child(0) = predFilterNode;
+ else
+ gbyNode->child(0) = predFilterNode;
}
}
}
@@ -3104,15 +3113,25 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
}
RelExpr *oldGB = child(1)->castToRelExpr();
- // note that the child of oldGB is actually a Filter node, here
+ // note that typically child of oldGB is actually a Filter node, here
// oldGBgrandchild is the child of oldGB before the Filter was added.
RelExpr *oldGBgrandchild ;
-
- if (oldGB->child(0)->getOperatorType() == REL_FILTER)
- oldGBgrandchild = oldGB->child(0)->child(0)->castToRelExpr();
+ NABoolean nestedAggInSubQ = FALSE;
+
+ if ((oldGB->child(0)->getOperatorType() == REL_GROUPBY) &&
+ (oldGB->child(0)->child(0)->getOperatorType() == REL_FILTER))
+ {
+ oldGBgrandchild = oldGB->child(0)->child(0)->child(0)->castToRelExpr();
+ nestedAggInSubQ = TRUE;
+ }
+ else if (oldGB->child(0)->getOperatorType() == REL_FILTER)
+ oldGBgrandchild = oldGB->child(0)->child(0)->castToRelExpr();
else
oldGBgrandchild = oldGB->child(0)->castToRelExpr();
+ RelExpr *filterParent = nestedAggInSubQ ?
+ oldGB->child(0)->castToRelExpr() : oldGB;
+
RelExpr *oldLeftChild = child(0)->castToRelExpr();
// if subquery needs left joins some additional checks are done here to
@@ -3131,7 +3150,7 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
*CmpCommon::diags() << DgSqlCode(2997)
<< DgString1("Subquery was not unnested. Reason: Join with selectionPreds cannot be converted to LeftJoin.");
}
- oldGB->eliminateFilterChild();
+ filterParent->eliminateFilterChild();
return NULL ;
}
}
@@ -3156,6 +3175,12 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
newGrby->setRETDesc(getRETDesc());
newGrby->getGroupAttr()->clearLogProperties(); //logical prop. must be resynthesized
+ if (nestedAggInSubQ)
+ {
+ GroupByAgg *newSubQGrby = (GroupByAgg *)
+ copyNode(oldGB->child(0)->castToRelExpr(), stmtHeap);
+ newSubQGrby->getGroupAttr()->clearLogProperties();
+ }
// For multi-level subqueries it is possible that this Join is
// not a TSJ, but still contains outer references. This happens
@@ -3172,7 +3197,7 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
{
// The join contains aggregates
// Skip such this subquery .
- oldGB->eliminateFilterChild();
+ filterParent->eliminateFilterChild();
return NULL ;
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/24e252c1/core/sql/sqlcomp/nadefaults.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/nadefaults.cpp b/core/sql/sqlcomp/nadefaults.cpp
index b6311f6..18b9989 100644
--- a/core/sql/sqlcomp/nadefaults.cpp
+++ b/core/sql/sqlcomp/nadefaults.cpp
@@ -6963,6 +6963,11 @@ DefaultToken NADefaults::token(Int32 attrEnum,
isValid = TRUE;
break;
+ case SUBQUERY_UNNESTING_P2:
+ if (tok == DF_OFF || tok == DF_ON || tok == DF_INTERNAL)
+ isValid = TRUE;
+ break;
+
case SORT_INTERMEDIATE_SCRATCH_CLEANUP:
if(tok == DF_ON || tok == DF_OFF)
isValid = TRUE;
[2/5] incubator-trafodion git commit: Subquery unnest for subquery
with explicit groupby
Posted by su...@apache.org.
Subquery unnest for subquery with explicit groupby
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/f0cae7f8
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/f0cae7f8
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/f0cae7f8
Branch: refs/heads/master
Commit: f0cae7f83395f1f16c702efda4f1dbc427bebefa
Parents: 24e252c
Author: Suresh Subbiah <su...@apache.org>
Authored: Sat Aug 13 04:53:48 2016 +0000
Committer: Suresh Subbiah <su...@apache.org>
Committed: Sat Aug 13 04:53:48 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 186 ++++++++++++++++++-----
core/sql/optimizer/RelExpr.h | 5 +
core/sql/regress/compGeneral/EXPECTED011.SB | 162 ++++++++++++++++----
core/sql/regress/compGeneral/TEST011 | 10 ++
4 files changed, 296 insertions(+), 67 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/f0cae7f8/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index 9fd9978..d7ab060 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -2359,9 +2359,11 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
RelExpr * oldRightGrandChild = child(1)->child(0)->castToRelExpr();
NABoolean candidateForLeftJoin = candidateForSubqueryLeftJoinConversion();
NABoolean nestedAggInSubQ = FALSE;
+ GroupByAgg * subQGby = NULL ;
if (oldRightGrandChild->getOperator().match(REL_GROUPBY))
{
+ subQGby = (GroupByAgg *) oldRightGrandChild ;
oldRightGrandChild = oldRightGrandChild->child(0)->castToRelExpr();
nestedAggInSubQ = TRUE ;
}
@@ -2410,8 +2412,13 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
if (doNotUnnest == FALSE)
{
nonLocalPreds.clear();
- if (oldRightGrandChild->selectionPred().getReferencedPredicates
- (outerReferences, nonLocalPreds))
+ oldRightGrandChild->selectionPred().getReferencedPredicates
+ (outerReferences, nonLocalPreds) ;
+ if (nestedAggInSubQ)
+ subQGby->selectionPred().getReferencedPredicates
+ (outerReferences, nonLocalPreds);
+
+ if (!nonLocalPreds.isEmpty())
{
// Right grandchild selection pred has outer references
@@ -2442,15 +2449,21 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
if ((doNotUnnest == FALSE) && candidateForLeftJoin &&
(CmpCommon::getDefault(SUBQUERY_UNNESTING_P2) != DF_INTERNAL) &&
- (normWARef.getLeftJoinConversionCount() >= 1))
+ ((normWARef.getLeftJoinConversionCount() >= 1)||nestedAggInSubQ))
{
doNotUnnest = TRUE;
// For phase 2 we only unnest 1 level of subqueries
// containing NonNullRejecting Predicates
- if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
+ if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
+ {
+ if (!nestedAggInSubQ)
*CmpCommon::diags() << DgSqlCode(2997)
<< DgString1("Skipping unnesting of Subquery due to NonNullRejecting Predicates in more than one subquery");
+ else
+ *CmpCommon::diags() << DgSqlCode(2997)
+ << DgString1("Skipping unnesting of Subquery since we have both NonNullRejecting predicate and nested nested aggregate in subquery.");
+ }
}
@@ -2461,9 +2474,18 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
Filter(oldRightGrandChild);
predFilterNode->selectionPred() += nonLocalPreds;
oldRightGrandChild->selectionPred() -= nonLocalPreds;
-
- predFilterNode->getGroupAttr()->setCharacteristicInputs
+ if (nestedAggInSubQ)
+ {
+ subQGby->selectionPred() -= nonLocalPreds;
+ predFilterNode->getGroupAttr()->setCharacteristicInputs
+ (subQGby->getGroupAttr()->getCharacteristicInputs());
+ subQGby->recomputeOuterReferences();
+ }
+ else
+ {
+ predFilterNode->getGroupAttr()->setCharacteristicInputs
(oldRightGrandChild->getGroupAttr()->getCharacteristicInputs());
+ }
oldRightGrandChild->recomputeOuterReferences();
@@ -3054,11 +3076,50 @@ Join::pullUpGroupByTransformation()
// The predicates in the new groupBy and the new Join will have
// changed according to the comments above.
//
+// If there is an explicit groupby in the subquery the transformation above is extended as
+// TSJ GroupBy {pred3,agg2(agg1)}(grouping cols:
+// / \ | cluster_key of X (leftUniqueCols)+
+// / \ | other necessary columns of X)
+// X ScalarAgg {pred3} --> SubQ_GroupBy {agg1} (grouping cols: g1 +
+// | {agg2(agg1)} | cluster_key of X (leftUniqueCols) +
+// | | other necessary columns of X)
+// SubQ_GroupBy {agg1} newJoin {pred2}
+// | {grouping cols: g1} / \
+// | / \
+// Filter {pred2} newLeft newRight {pred1}
+// | Child Child
+// |
+// Y {pred1}
+//
+// If there is an explicy groupby in the subquery then the flag nestedAggInSubQ is set.
+
------------------------------------------------------------------------------*/
GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
{
CollHeap *stmtHeap = CmpCommon::statementHeap() ;
+ RelExpr *oldGB = child(1)->castToRelExpr();
+ // note that typically child of oldGB is actually a Filter node, here
+ // oldGBgrandchild is the child of oldGB before the Filter was added.
+ RelExpr *oldGBgrandchild ;
+ NABoolean nestedAggInSubQ = FALSE;
+
+ if ((oldGB->child(0)->getOperatorType() == REL_GROUPBY) &&
+ (oldGB->child(0)->child(0)->getOperatorType() == REL_FILTER))
+ {
+ oldGBgrandchild = oldGB->child(0)->child(0)->child(0)->castToRelExpr();
+ nestedAggInSubQ = TRUE;
+ }
+ else if (oldGB->child(0)->getOperatorType() == REL_FILTER)
+ oldGBgrandchild = oldGB->child(0)->child(0)->castToRelExpr();
+ else
+ oldGBgrandchild = oldGB->child(0)->castToRelExpr();
+
+ RelExpr *filterParent = nestedAggInSubQ ?
+ oldGB->child(0)->castToRelExpr() : oldGB;
+
+ RelExpr *oldLeftChild = child(0)->castToRelExpr();
+
// Determine a set of unique columns for the left sub-tree.
// Note: Scans and joins synthesize uniqueness constraints even for
@@ -3074,7 +3135,7 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
// Could not find a set of unique cols.
// If the left sub-tree contains a UNION/TRANSPOSE/SEQUENCE or SAMPLE
// then we will fail to unnest the subquery for this reason.
- child(1)->eliminateFilterChild();
+ filterParent->eliminateFilterChild();
// left child does not have a unique constraint
// cannot unnest this subquery
if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
@@ -3112,28 +3173,6 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
return NULL ;
}
- RelExpr *oldGB = child(1)->castToRelExpr();
- // note that typically child of oldGB is actually a Filter node, here
- // oldGBgrandchild is the child of oldGB before the Filter was added.
- RelExpr *oldGBgrandchild ;
- NABoolean nestedAggInSubQ = FALSE;
-
- if ((oldGB->child(0)->getOperatorType() == REL_GROUPBY) &&
- (oldGB->child(0)->child(0)->getOperatorType() == REL_FILTER))
- {
- oldGBgrandchild = oldGB->child(0)->child(0)->child(0)->castToRelExpr();
- nestedAggInSubQ = TRUE;
- }
- else if (oldGB->child(0)->getOperatorType() == REL_FILTER)
- oldGBgrandchild = oldGB->child(0)->child(0)->castToRelExpr();
- else
- oldGBgrandchild = oldGB->child(0)->castToRelExpr();
-
- RelExpr *filterParent = nestedAggInSubQ ?
- oldGB->child(0)->castToRelExpr() : oldGB;
-
- RelExpr *oldLeftChild = child(0)->castToRelExpr();
-
// if subquery needs left joins some additional checks are done here to
// see if pull up groupby transformation can be done while preserving
// semantic correctness. No changes for left joins or preserving nulls
@@ -3174,11 +3213,11 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
GroupByAgg *newGrby = (GroupByAgg *) copyNode(oldGB, stmtHeap);
newGrby->setRETDesc(getRETDesc());
newGrby->getGroupAttr()->clearLogProperties(); //logical prop. must be resynthesized
-
+ GroupByAgg *newSubQGrby = NULL;
if (nestedAggInSubQ)
{
- GroupByAgg *newSubQGrby = (GroupByAgg *)
- copyNode(oldGB->child(0)->castToRelExpr(), stmtHeap);
+ newSubQGrby = (GroupByAgg *) copyNode(oldGB->child(0)->castToRelExpr(),
+ stmtHeap);
newSubQGrby->getGroupAttr()->clearLogProperties();
}
@@ -3200,6 +3239,23 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
filterParent->eliminateFilterChild();
return NULL ;
}
+
+ if (nestedAggInSubQ)
+ {
+ safeToPullUpGrby = newJoin->pullUpPredsWithAggrs(newSubQGrby);
+ if (NOT safeToPullUpGrby )
+ {
+ filterParent->eliminateFilterChild();
+ return NULL ;
+ }
+ // inputs of newSubQGroupBy are same as the old
+ // TSJ/Join that we are replacing. Outputs are join's + aggregates
+ newSubQGrby->getGroupAttr()->addCharacteristicOutputs
+ (getGroupAttr()->getCharacteristicOutputs());
+ newSubQGrby->getGroupAttr()->setCharacteristicInputs
+ (getGroupAttr()->getCharacteristicInputs());
+ newSubQGrby->child(0) = newJoin ;
+ }
// inputs and outputs of new GroupBy are same as the old
// TSJ/Join that we are replacing
@@ -3207,7 +3263,11 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
(getGroupAttr()->getCharacteristicOutputs());
newGrby->getGroupAttr()->setCharacteristicInputs
(getGroupAttr()->getCharacteristicInputs());
- newGrby->child(0) = newJoin ;
+
+ if (nestedAggInSubQ)
+ newGrby->child(0) = newSubQGrby;
+ else
+ newGrby->child(0) = newJoin ;
// set the grouping cols for new GroupBy
@@ -3223,6 +3283,16 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
oldLeftChildOutputs,
normWARef
);
+ if (nestedAggInSubQ)
+ {
+ newSubQGrby->getGroupAttr()->
+ addCharacteristicOutputs(newGrby->groupExpr());
+ newSubQGrby->computeGroupExpr(newGrby->groupExpr(),
+ oldLeftChildOutputs,
+ normWARef);
+ }
+
+
// The newGrby cannot be a scalar groupby under any circumstance
// So if the group expression is empty, add a constant to the
@@ -3246,6 +3316,10 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
// decide to not unnest.
if (oldGB->child(0)->getOperatorType() == REL_FILTER)
newJoin->selectionPred() += oldGB->child(0)->castToRelExpr()->selectionPred();
+ else if (nestedAggInSubQ &&
+ oldGB->child(0)->child(0)->getOperatorType() == REL_FILTER)
+ newJoin->selectionPred() +=
+ oldGB->child(0)->child(0)->castToRelExpr()->selectionPred();
// If the new GroupBy contains any outer references (i.e. requiredInputs
// that are not provided by the user) then mark it as needing
@@ -3255,7 +3329,16 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
getOuterReferences(outerReferences);
if (NOT(outerReferences.isEmpty()))
{
- newGrby->setRequiresMoveUp(TRUE) ;
+ if (!nestedAggInSubQ)
+ newGrby->setRequiresMoveUp(TRUE) ;
+ else
+ {
+ filterParent->eliminateFilterChild();
+ if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
+ *CmpCommon::diags() << DgSqlCode(2997)
+ << DgString1("Subquery was not unnested. Reason: More than 1 level of nested subquery and nested aggregate are both present");
+ return NULL;
+ }
}
return newGrby ;
@@ -4081,7 +4164,11 @@ GroupByAgg* Join::moveUpGroupByTransformation(const GroupByAgg* topGrby,
NABoolean GroupByAgg::subqueryUnnestFinalize(ValueIdSet& newGrbyGroupExpr,
NormWA& normWARef)
{
- Join * newJoin = (Join*) child(0)->castToRelExpr();
+ Join * newJoin = NULL ;
+ if (child(0)->getOperatorType() == REL_GROUPBY)
+ newJoin = (Join*) child(0)->child(0)->castToRelExpr();
+ else
+ newJoin = (Join*) child(0)->castToRelExpr();
RelExpr * newLeftChild = newJoin->child(0)->castToRelExpr();
RelExpr * newRightChild = newJoin->child(1)->castToRelExpr();
@@ -4474,7 +4561,7 @@ RelExpr * Join::semanticQueryOptimizeNode(NormWA & normWARef)
// If comp_bool_221 is on we will unnest even if there is no filter node.
if ((CmpCommon::getDefault(COMP_BOOL_221) == DF_OFF) &&
((child(1)->getArity() != 1) ||
- child(1)->child(0)->getOperatorType() != REL_FILTER))
+ !(child(1)->castToRelExpr()->hasFilterChild())))
{
if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
*CmpCommon::diags() << DgSqlCode(2997)
@@ -4522,7 +4609,17 @@ RelExpr * Join::semanticQueryOptimizeNode(NormWA & normWARef)
// Apply MoveUp GroupBy transformation. Relevant only for subqueries with
// two or more levels of nesting. If moveUpGroupBy is not needed
// movedUpGroupByTail will be set to newGrby.
- Join * newJoin = (Join*) newGrby->child(0)->castToRelExpr();
+ RelExpr* gbChild = newGrby->child(0)->castToRelExpr();
+ NABoolean nestedAggInSubQ = FALSE;
+ Join * newJoin = NULL;
+ if (gbChild->getOperatorType() == REL_GROUPBY)
+ {
+ newJoin = (Join*) gbChild->child(0)->castToRelExpr();
+ nestedAggInSubQ = TRUE;
+ }
+ else
+ newJoin = (Join*) gbChild;
+
GroupByAgg* movedUpGrbyTail =
newJoin->moveUpGroupByTransformation(newGrby, normWARef);
@@ -4551,7 +4648,10 @@ RelExpr * Join::semanticQueryOptimizeNode(NormWA & normWARef)
{
newJoin = newJoin->leftLinearizeJoinTree(normWARef,
UNNESTING); // Unnesting
- movedUpGrbyTail->child(0) = newJoin ;
+ if (nestedAggInSubQ)
+ movedUpGrbyTail->child(0)->child(0) = newJoin ;
+ else
+ movedUpGrbyTail->child(0) = newJoin ;
}
//synthesize logical props for the new nodes.
@@ -7452,6 +7552,16 @@ RelExpr * SortLogical::normalizeNode(NormWA & normWARef)
return child(0);
} // SortLogical::normalizeNode()
+NABoolean RelExpr::hasFilterChild()
+ {
+ if (getArity() == 1 && child(0)->getOperatorType() == REL_FILTER)
+ return TRUE;
+ else if (getArity() == 1 && child(0)->getArity() == 1 &&
+ child(0)->child(0)->getOperatorType() == REL_FILTER)
+ return TRUE;
+ else
+ return FALSE;
+ }
// If subquery unnesting fails for some reason at a particular level
// then the Filter node at that level can be elimated by pushing
// its selection predicate to its child. This is not strictly necessary
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/f0cae7f8/core/sql/optimizer/RelExpr.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelExpr.h b/core/sql/optimizer/RelExpr.h
index 0966440..fac2298 100644
--- a/core/sql/optimizer/RelExpr.h
+++ b/core/sql/optimizer/RelExpr.h
@@ -566,6 +566,11 @@ public:
// phase
void eliminateFilterChild();
+ // used duirng SQO, to check if the child or grandchild of current
+ // node is a filter. Two acceptable patterns are (a) child(0) is filter
+ // child(0) is groupby an child(0)->child(0) is filter
+ NABoolean hasFilterChild();
+
// used during SQO phase to make the left sub tree of the join
// being unnested produce the extra outputs that is necessary to
// group by a set of unique columns of of the left sub tree
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/f0cae7f8/core/sql/regress/compGeneral/EXPECTED011.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/EXPECTED011.SB b/core/sql/regress/compGeneral/EXPECTED011.SB
index 43ebe8c..e15b033 100644
--- a/core/sql/regress/compGeneral/EXPECTED011.SB
+++ b/core/sql/regress/compGeneral/EXPECTED011.SB
@@ -821,7 +821,7 @@ A
*** WARNING[2997] (Attempting to unnest Subquery)
-*** WARNING[2997] (Subquery was not unnested. Reason: Right grandchild of TSJ is a semijoin or a group by)
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation found)
VCH7 NINT CH3 NNUM5
------- ------ --- ------------
@@ -1248,7 +1248,7 @@ A
*** WARNING[2997] (Attempting to unnest Subquery)
-*** WARNING[2997] (Subquery was not unnested. Reason: Right grandchild of TSJ is a semijoin or a group by)
+*** WARNING[2997] (Subquery was not unnested. Reason: Right grandchild of TSJ is a semijoin or has more than one group by)
--- SQL command prepared.
>>
@@ -1842,9 +1842,9 @@ ss ?
A (EXPR)
---------- ----------
+ss ?
xx ?
xx ?
-ss ?
--- 3 row(s) selected.
>>
@@ -1912,14 +1912,6 @@ abcd abc% abcdefg
abcd abc% abcdefgh
abcd abc% abcedf
abcd abcdef% aaa
-abcde abc% aaa
-abcde abc% abc
-abcde abc% abcd
-abcde abc% abcde
-abcde abc% abcdefg
-abcde abc% abcdefgh
-abcde abc% abcedf
-abcde abcdef% aaa
abcdefg abc% aaa
abcdefg abc% abc
abcdefg abc% abcd
@@ -1944,6 +1936,14 @@ abcedf abc% abcdefg
abcedf abc% abcdefgh
abcedf abc% abcedf
abcedf abcdef% aaa
+abcde abc% aaa
+abcde abc% abc
+abcde abc% abcd
+abcde abc% abcde
+abcde abc% abcdefg
+abcde abc% abcdefgh
+abcde abc% abcedf
+abcde abcdef% aaa
--- 56 row(s) selected.
>>
@@ -1967,12 +1967,7 @@ abcedf abcdef% aaa
>>
>>execute show_join_oper;
-OPERATOR
-------------------------------
-
-HYBRID_HASH_JOIN
-
---- 1 row(s) selected.
+--- 0 row(s) selected.
>>
>>-- Test LeftJoin to Inner Join transformation for LIKE
>>-- Should not see a LeftJoin in the explain output
@@ -1986,12 +1981,7 @@ HYBRID_HASH_JOIN
>>
>>execute show_join_oper;
-OPERATOR
-------------------------------
-
-HYBRID_HASH_JOIN
-
---- 1 row(s) selected.
+--- 0 row(s) selected.
>>
>>-- Test LeftJoin to Inner Join transformation for NOT
>>-- Should not see a LeftJoin in the explain output
@@ -2005,12 +1995,7 @@ HYBRID_HASH_JOIN
>>
>>execute show_join_oper;
-OPERATOR
-------------------------------
-
-HYBRID_HASH_JOIN
-
---- 1 row(s) selected.
+--- 0 row(s) selected.
>>
>>-- bug no. 4138 from bugzilla
>>-- abs of unsigned type used to give internal error
@@ -2061,5 +2046,124 @@ A B
--- SQL command prepared.
>>
+>>-- JIRA [TRAFODION-2159] Unnest correlated subquery with explicit groupby
+>>-- Q51
+>>prepare XX from
++>select t40.a
++>from t40
++>where t40.b >= (select avg(counta) from (select count(a) as counta from t41 where t40.c = t41.b group by t41.c))
++>order by 1 ;
+
+--- SQL command prepared.
+>>
+>>execute explainIt;
+
+--- 0 row(s) selected.
+>>execute XX ;
+
+A
+-----------
+
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+ 31
+ 32
+ 33
+ 34
+ 35
+ 36
+ 37
+ 38
+ 39
+ 40
+ 41
+ 42
+ 43
+ 44
+ 45
+ 46
+ 47
+ 48
+ 49
+ 50
+ 51
+ 52
+ 53
+ 54
+ 55
+ 56
+ 57
+ 58
+ 59
+ 60
+ 61
+ 62
+ 63
+ 64
+ 65
+ 66
+ 67
+ 68
+ 69
+ 70
+ 71
+ 72
+ 73
+ 74
+ 75
+ 76
+ 77
+ 78
+ 79
+ 80
+ 81
+ 82
+ 83
+ 84
+ 85
+ 86
+ 87
+ 88
+ 89
+ 90
+ 91
+ 92
+ 93
+ 94
+ 95
+ 96
+ 97
+ 98
+ 99
+
+--- 99 row(s) selected.
>>
>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/f0cae7f8/core/sql/regress/compGeneral/TEST011
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/TEST011 b/core/sql/regress/compGeneral/TEST011
index 326901a..debc625 100644
--- a/core/sql/regress/compGeneral/TEST011
+++ b/core/sql/regress/compGeneral/TEST011
@@ -1119,5 +1119,15 @@ execute XX;
-- bug #1893
prepare c1 from UPDATE SQLF177 SET (C2,C3) = (C2 + cast( ? as INT),?) WHERE C1 = ? ;
+-- JIRA [TRAFODION-2159] Unnest correlated subquery with explicit groupby
+-- Q51
+prepare XX from
+select t40.a
+from t40
+where t40.b >= (select avg(counta) from (select count(a) as counta from t41 where t40.c = t41.b group by t41.c))
+order by 1 ;
+
+execute explainIt;
+execute XX ;
log;
[4/5] incubator-trafodion git commit: Fix merge error by removing an
unneeded line
Posted by su...@apache.org.
Fix merge error by removing an unneeded line
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/78194bd0
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/78194bd0
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/78194bd0
Branch: refs/heads/master
Commit: 78194bd009f477444376a813609b1570fda9defc
Parents: a497e20
Author: Suresh Subbiah <su...@apache.org>
Authored: Thu Aug 18 02:40:49 2016 +0000
Committer: Suresh Subbiah <su...@apache.org>
Committed: Thu Aug 18 02:40:49 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 1 -
1 file changed, 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/78194bd0/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index c6c5f55..4cdf154 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -3131,7 +3131,6 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
// method getMoreOutputsIfPossible().
ValueIdSet leftUniqueCols ;
- child(0)->castToRelExpr()->synthLogProp() ;
if (NOT (child(0)->getGroupAttr()->findUniqueCols(leftUniqueCols)))
{
// Could not find a set of unique cols.
[5/5] incubator-trafodion git commit: Merge [TRAFODION-2159] PR-657
Unnest correlated subquery with explicit groupby
Posted by su...@apache.org.
Merge [TRAFODION-2159] PR-657 Unnest correlated subquery with explicit groupby
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/0b0665b6
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/0b0665b6
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/0b0665b6
Branch: refs/heads/master
Commit: 0b0665b671802b90b0d3ec6ca44fec08139725de
Parents: afdca30 78194bd
Author: Suresh Subbiah <su...@apache.org>
Authored: Tue Aug 23 16:25:42 2016 +0000
Committer: Suresh Subbiah <su...@apache.org>
Committed: Tue Aug 23 16:25:42 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 222 +++++++++++++----
core/sql/optimizer/OptLogRelExpr.cpp | 19 ++
core/sql/optimizer/RelExpr.h | 5 +
core/sql/regress/compGeneral/EXPECTED011.SB | 293 ++++++++++++++++++++---
core/sql/regress/compGeneral/TEST011 | 29 +++
core/sql/sqlcomp/nadefaults.cpp | 5 +
6 files changed, 489 insertions(+), 84 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0b0665b6/core/sql/sqlcomp/nadefaults.cpp
----------------------------------------------------------------------
[3/5] incubator-trafodion git commit: Adding a new change that will
allow unnest of two LeftJoin requiring subqueries as long as they are not
nested within each other. Rework due to Dave's comments.
Posted by su...@apache.org.
Adding a new change that will allow unnest of two LeftJoin requiring subqueries
as long as they are not nested within each other.
Rework due to Dave's comments.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a497e20d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a497e20d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a497e20d
Branch: refs/heads/master
Commit: a497e20d0c1f1f517af5035f3e73c69052b74b20
Parents: f0cae7f
Author: Suresh Subbiah <su...@apache.org>
Authored: Wed Aug 17 21:56:37 2016 +0000
Committer: Suresh Subbiah <su...@apache.org>
Committed: Wed Aug 17 21:56:37 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 32 ++----
core/sql/optimizer/OptLogRelExpr.cpp | 19 ++++
core/sql/regress/compGeneral/EXPECTED011.SB | 135 ++++++++++++++++++++++-
core/sql/regress/compGeneral/TEST011 | 19 ++++
4 files changed, 176 insertions(+), 29 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a497e20d/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index d7ab060..c6c5f55 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -2449,20 +2449,21 @@ void Join::createAFilterGrandChildIfNeeded(NormWA & normWARef)
if ((doNotUnnest == FALSE) && candidateForLeftJoin &&
(CmpCommon::getDefault(SUBQUERY_UNNESTING_P2) != DF_INTERNAL) &&
- ((normWARef.getLeftJoinConversionCount() >= 1)||nestedAggInSubQ))
+ ((normWARef.getLeftJoinConversionCount() >= 2)||nestedAggInSubQ))
{
doNotUnnest = TRUE;
- // For phase 2 we only unnest 1 level of subqueries
- // containing NonNullRejecting Predicates
+ // For phase 2 we only unnest 2 subqueries
+ // containing NonNullRejecting Predicates. Later we will ensure
+ // that these 2 subqueries are not nested.
if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
{
if (!nestedAggInSubQ)
*CmpCommon::diags() << DgSqlCode(2997)
- << DgString1("Skipping unnesting of Subquery due to NonNullRejecting Predicates in more than one subquery");
+ << DgString1("Skipping unnesting of Subquery due to NonNullRejecting Predicates in more than two subqueries");
else
*CmpCommon::diags() << DgSqlCode(2997)
- << DgString1("Skipping unnesting of Subquery since we have both NonNullRejecting predicate and nested nested aggregate in subquery.");
+ << DgString1("Skipping unnesting of Subquery since we have both NonNullRejecting predicate and nested aggregate in subquery.");
}
}
@@ -3130,6 +3131,7 @@ GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
// method getMoreOutputsIfPossible().
ValueIdSet leftUniqueCols ;
+ child(0)->castToRelExpr()->synthLogProp() ;
if (NOT (child(0)->getGroupAttr()->findUniqueCols(leftUniqueCols)))
{
// Could not find a set of unique cols.
@@ -3458,26 +3460,6 @@ RelExpr* GroupByAgg::nullPreservingTransformation(GroupByAgg* oldGB,
// that far.
ValueId ignoreReturnedVid;
ValueId oneTrueVid;
- NABoolean replaceOneTrue = FALSE;
-
- replaceOneTrue = newGrby->aggregateExpr().containsOneTrue(oneTrueVid);
- if( replaceOneTrue &&
- newGrby->getGroupAttr()->getCharacteristicOutputs().referencesTheGivenValue(oneTrueVid, ignoreReturnedVid, FALSE,FALSE))
- {
- // If someone above us needs the oneTrue, skip unnesting it as
- // we cannot map between a count(1) > 0 expression to
- // a oneTrue(return TRUE) one.
- // This since the only way to do so would be to add "count(1) > 0" to the
- // grouping expression. For phase 3 we want to evaluate if doing so would be
- // safe from a semantic point of view.
- if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
- {
- *CmpCommon::diags() << DgSqlCode(2997)
- << DgString1("Subquery was not unnested. Reason: Upper expressions require OneTrue.");
- }
- oldGB->eliminateFilterChild();
- return NULL ;
- }
// change newJoin into a left join and move preds into the join predicate
// we have already guaranteed that all selection preds in the newJoin are
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a497e20d/core/sql/optimizer/OptLogRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/OptLogRelExpr.cpp b/core/sql/optimizer/OptLogRelExpr.cpp
index 3c57e69..80360d4 100644
--- a/core/sql/optimizer/OptLogRelExpr.cpp
+++ b/core/sql/optimizer/OptLogRelExpr.cpp
@@ -4792,6 +4792,25 @@ MapValueIds::synthLogProp(NormWA * normWAPtr)
// Synthesize log. properties for me and my children.
RelExpr::synthLogProp(normWAPtr);
+ GroupAttributes &myGA = *getGroupAttr();
+
+ ValueIdSet nonRIConstraints;
+ for (ValueId x= child(0).getGroupAttr()->getConstraints().init();
+ child(0).getGroupAttr()->getConstraints().next(x);
+ child(0).getGroupAttr()->getConstraints().advance(x) )
+ {
+ if (x.getItemExpr()->getOperatorType() == ITM_UNIQUE_OPT_CONSTRAINT)
+ {
+ ValueIdSet uniqueCols ;
+ getMap().rewriteValueIdSetUp
+ (uniqueCols, ((UniqueOptConstraint *)x.getItemExpr())->uniqueCols());
+ myGA.addConstraint(new(CmpCommon::statementHeap())
+ UniqueOptConstraint(uniqueCols));
+ }
+ else if (x.getItemExpr()->getOperatorType() == ITM_CARD_CONSTRAINT)
+ myGA.addConstraint(x);
+ // func. dependency and check opt constraints can be added here if needed
+ }
} // MapValueIds::synthLogProp()
// -----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a497e20d/core/sql/regress/compGeneral/EXPECTED011.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/EXPECTED011.SB b/core/sql/regress/compGeneral/EXPECTED011.SB
index e15b033..f70659d 100644
--- a/core/sql/regress/compGeneral/EXPECTED011.SB
+++ b/core/sql/regress/compGeneral/EXPECTED011.SB
@@ -1106,7 +1106,7 @@ A
*** WARNING[2997] (Attempting to unnest Subquery)
-*** WARNING[2997] (Skipping unnesting of Subquery due to NonNullRejecting Predicates in more than one subquery)
+*** WARNING[2997] (Subquery was not unnested. Reason: Left subtree cannot produce output values required for grouping.)
--- SQL command prepared.
>>execute explainIt;
@@ -1218,8 +1218,6 @@ A
*** WARNING[2997] (Attempting to unnest Subquery)
-*** WARNING[2997] (Skipping unnesting of Subquery due to NonNullRejecting Predicates in more than one subquery)
-
--- SQL command prepared.
>>
>>execute explainIt;
@@ -1842,9 +1840,9 @@ ss ?
A (EXPR)
---------- ----------
-ss ?
xx ?
xx ?
+ss ?
--- 3 row(s) selected.
>>
@@ -2166,4 +2164,133 @@ A
--- 99 row(s) selected.
>>
+>>
+>>-- 2 correlated subqueries that require LOJ transformation. The two subqueries
+>>-- are NOT nested. Use of exists (i.e. oneTrue) predicate
+>>-- Q52
+>>prepare XX from
++>select t40.a
++>from t40
++>where exists (select *
++> from t44
++> where t40.c = t44.b)
++> OR
++> exists (select *
++> from t41
++> where t40.b = t41.b)
++>order by 1;
+
+--- SQL command prepared.
+>>
+>>execute explainIt;
+
+--- 0 row(s) selected.
+>>execute XX ;
+
+A
+-----------
+
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+ 31
+ 32
+ 33
+ 34
+ 35
+ 36
+ 37
+ 38
+ 39
+ 40
+ 41
+ 42
+ 43
+ 44
+ 45
+ 46
+ 47
+ 48
+ 49
+ 50
+ 51
+ 52
+ 53
+ 54
+ 55
+ 56
+ 57
+ 58
+ 59
+ 60
+ 61
+ 62
+ 63
+ 64
+ 65
+ 66
+ 67
+ 68
+ 69
+ 70
+ 71
+ 72
+ 73
+ 74
+ 75
+ 76
+ 77
+ 78
+ 79
+ 80
+ 81
+ 82
+ 83
+ 84
+ 85
+ 86
+ 87
+ 88
+ 89
+ 90
+ 91
+ 92
+ 93
+ 94
+ 95
+ 96
+ 97
+ 98
+ 99
+
+--- 100 row(s) selected.
+>>
>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a497e20d/core/sql/regress/compGeneral/TEST011
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/TEST011 b/core/sql/regress/compGeneral/TEST011
index debc625..ecb70cb 100644
--- a/core/sql/regress/compGeneral/TEST011
+++ b/core/sql/regress/compGeneral/TEST011
@@ -1130,4 +1130,23 @@ order by 1 ;
execute explainIt;
execute XX ;
+
+-- 2 correlated subqueries that require LOJ transformation. The two subqueries
+-- are NOT nested. Use of exists (i.e. oneTrue) predicate
+-- Q52
+prepare XX from
+select t40.a
+from t40
+where exists (select *
+ from t44
+ where t40.c = t44.b)
+ OR
+ exists (select *
+ from t41
+ where t40.b = t41.b)
+order by 1;
+
+execute explainIt;
+execute XX ;
+
log;