You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by li...@apache.org on 2016/11/12 00:35:46 UTC
[1/4] incubator-trafodion git commit: [TRAFODION-2270] support ORDER
BY in PIVOT_GROUP/GROUP_CONCAT function
Repository: incubator-trafodion
Updated Branches:
refs/heads/master e17bdcd2d -> a20935955
[TRAFODION-2270] support ORDER BY in PIVOT_GROUP/GROUP_CONCAT function
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/3fb43cb0
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/3fb43cb0
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/3fb43cb0
Branch: refs/heads/master
Commit: 3fb43cb056f9e93c533ef3706937df0664b505f6
Parents: 7092997
Author: Liu Ming <ov...@sina.com>
Authored: Fri Oct 28 02:44:59 2016 -0400
Committer: Liu Ming <ov...@sina.com>
Committed: Fri Oct 28 02:44:59 2016 -0400
----------------------------------------------------------------------
core/sql/optimizer/BindRelExpr.cpp | 22 ++++++++++++++++++++--
core/sql/optimizer/ItemExpr.cpp | 4 +++-
core/sql/optimizer/ItemFunc.h | 2 ++
core/sql/optimizer/NormRelExpr.cpp | 3 +++
core/sql/optimizer/OptPhysRelExpr.cpp | 22 ++++++++++++++++++----
core/sql/optimizer/RelCache.cpp | 10 ++++++++++
core/sql/optimizer/RelExpr.cpp | 2 ++
core/sql/optimizer/RelGrby.h | 12 +++++++++++-
core/sql/parser/sqlparser.y | 4 ----
core/sql/regress/executor/EXPECTED002.SB | 2 +-
core/sql/regress/executor/TEST002 | 2 +-
11 files changed, 71 insertions(+), 14 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 7804900..91efb2d 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -4396,7 +4396,6 @@ RelRoot * RelRoot::transformGroupByWithOrdinalPhase2(BindWA *bindWA)
{
groupExprCpy.remove(vid);
groupExprCpy.insert(grpById);
-
if (grby->isRollup())
{
CollIndex idx = grby->rollupGroupExprList().index(vid);
@@ -4539,6 +4538,26 @@ RelRoot * RelRoot::transformGroupByWithOrdinalPhase2(BindWA *bindWA)
}
}
+ //looking for extra order requirement, currently, aggregate function PIVOT_GROUP will need extra order
+ //so loop through the aggregation expression and check if there is PIVOT_GROUP and it needs explicit order
+ //if found, populate the extraOrderExpr for the GroupAggBy
+ //so later optimizer can add correct sort key
+ ValueIdSet &groupAggExpr = grby->aggregateExpr();
+
+ for (ValueId vid = groupAggExpr.init();
+ groupAggExpr.next(vid);
+ groupAggExpr.advance(vid))
+ {
+ if (vid.getItemExpr()->getOperatorType() == ITM_PIVOT_GROUP)
+ {
+ if( ((PivotGroup*)vid.getItemExpr())->orderBy() )
+ {
+ //((PivotGroup*)vid.getItemExpr())->getOrderbyItemExpr()->bindNode(bindWA);
+ grby->setExtraGrpOrderby(((PivotGroup*)vid.getItemExpr())->getOrderbyItemExpr());
+ grby->getExtraGrpOrderby()->convertToValueIdSet(grby->extraOrderExpr(), bindWA, ITM_ITEM_LIST);
+ }
+ }
+ }
// recreate the groupExpr expression after updating the value ids
grby->setGroupExpr (groupExprCpy);
@@ -5747,7 +5766,6 @@ RelExpr *RelRoot::bindNode(BindWA *bindWA)
if (! returnedRoot)
return NULL;
- // ItemExpr *orderByTree = removeOrderByTree();
ItemExpr *orderByTree = removeOrderByTree();
if (orderByTree) {
//
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp
index f48f26d..151c3b3 100644
--- a/core/sql/optimizer/ItemExpr.cpp
+++ b/core/sql/optimizer/ItemExpr.cpp
@@ -7181,7 +7181,8 @@ PivotGroup::PivotGroup(OperatorTypeEnum otype,
case ORDER_BY_:
{
orderBy_ = TRUE;
- // TBD: populate reqdOrder. May need to move to bindNode
+ // optionNode_ contains the ItemExpr
+ orgReqOrder_ = (ItemExpr *)po->optionNode_;
}
break;
}
@@ -7204,6 +7205,7 @@ ItemExpr * PivotGroup::copyTopNode(ItemExpr *derivedNode, CollHeap* outHeap)
result->delim_ = delim_;
result->orderBy_ = orderBy_;
result->reqdOrder_ = reqdOrder_;
+ result->orgReqOrder_ = orgReqOrder_;
result->maxLen_ = maxLen_;
return Aggregate::copyTopNode(result, outHeap);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h
index a922fcd..58e715b 100644
--- a/core/sql/optimizer/ItemFunc.h
+++ b/core/sql/optimizer/ItemFunc.h
@@ -546,6 +546,7 @@ public:
NABoolean orderBy() { return orderBy_;}
ValueIdList &reqdOrder() { return reqdOrder_; }
Lng32 maxLen() { return maxLen_; }
+ ItemExpr *getOrderbyItemExpr() { return orgReqOrder_; }
private:
NAList<PivotOption*> * pivotOptionsList_;
@@ -553,6 +554,7 @@ private:
NABoolean orderBy_;
ValueIdList reqdOrder_; // ORDER BY list
+ ItemExpr *orgReqOrder_;
Lng32 maxLen_;
}; // class PivotGroup
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index 997cff8..d83bb81 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -5218,6 +5218,9 @@ void GroupByAgg::rewriteNode(NormWA & normWARef)
if (rollupGroupExprList().normalizeNode(normWARef))
{
}
+ if (extraOrderExpr().normalizeNode(normWARef))
+ {
+ }
// ---------------------------------------------------------------------
// Rewrite the expressions that are aggregate expressions
// ---------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/OptPhysRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/OptPhysRelExpr.cpp b/core/sql/optimizer/OptPhysRelExpr.cpp
index 61969e9..de7e5b1 100644
--- a/core/sql/optimizer/OptPhysRelExpr.cpp
+++ b/core/sql/optimizer/OptPhysRelExpr.cpp
@@ -11814,10 +11814,23 @@ void SortGroupBy::addArrangementAndOrderRequirements(
{
// Shouldn't/Can't add a sort order type requirement
// if we are in DP2
- if (rg.getStartRequirements()->executeInDP2())
- rg.addArrangement(groupExpr(),NO_SOT);
- else
- rg.addArrangement(groupExpr(),ESP_SOT);
+
+ if( NOT extraOrderExpr().isEmpty())
+ {
+ ValueIdList groupExprCpy(groupExpr());
+ for (ValueId vid = extraOrderExpr().init(); extraOrderExpr().next(vid);
+ extraOrderExpr().advance(vid))
+ {
+ groupExprCpy.insert(vid);
+ }
+ rg.addSortKey(groupExprCpy);
+ }
+ else {
+ if (rg.getStartRequirements()->executeInDP2())
+ rg.addArrangement(groupExpr(),NO_SOT);
+ else
+ rg.addArrangement(groupExpr(),ESP_SOT);
+ }
}
}
@@ -11856,6 +11869,7 @@ SortGroupBy::synthPhysicalProperty(const Context* myContext,
planNumber,
pws);
+ //PhysicalProperty(((isRollup() ||NOT extraOrderExpr().isEmpty()) ? ValueIdList() : sppOfChild->getSortKey()),
PhysicalProperty* sppForMe =
new (CmpCommon::statementHeap())
PhysicalProperty((isRollup() ? ValueIdList() : sppOfChild->getSortKey()),
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/RelCache.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelCache.cpp b/core/sql/optimizer/RelCache.cpp
index ccd5f64..1c35fd1 100644
--- a/core/sql/optimizer/RelCache.cpp
+++ b/core/sql/optimizer/RelCache.cpp
@@ -429,6 +429,16 @@ void GroupByAgg::generateCacheKey(CacheWA &cwa) const
ie->generateCacheKey(cwa);
}
}
+ if (NOT extraOrderExpr().isEmpty() )
+ {
+ cwa += " extraOrder:";
+
+ ItemExpr * ie = extraOrderExpr().rebuildExprTree(ITM_ITEM_LIST);
+ if (ie)
+ {
+ ie->generateCacheKey(cwa);
+ }
+ }
grpExpr->generateCacheKey(cwa);
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/RelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelExpr.cpp b/core/sql/optimizer/RelExpr.cpp
index 0bd7fdf..b9fa013 100644
--- a/core/sql/optimizer/RelExpr.cpp
+++ b/core/sql/optimizer/RelExpr.cpp
@@ -7577,6 +7577,8 @@ RelExpr * GroupByAgg::copyTopNode(RelExpr *derivedNode, CollHeap* outHeap)
result->aggrExprsToBeDeleted_ = aggrExprsToBeDeleted_;
result->isRollup_ = isRollup_;
+ result->extraGrpOrderby_= extraGrpOrderby_;
+ result->extraOrderExpr_= extraOrderExpr_;
return RelExpr::copyTopNode(result, outHeap);
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/optimizer/RelGrby.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelGrby.h b/core/sql/optimizer/RelGrby.h
index 72717fb..455a365 100644
--- a/core/sql/optimizer/RelGrby.h
+++ b/core/sql/optimizer/RelGrby.h
@@ -112,6 +112,7 @@ public:
isMarkedForElimination_(FALSE),
aggDistElimRuleCreates_(FALSE),
groupByOnJoinRuleCreates_(FALSE),
+ extraGrpOrderby_(NULL),
isRollup_(FALSE)
{}
@@ -130,6 +131,7 @@ public:
isMarkedForElimination_(FALSE),
aggDistElimRuleCreates_(FALSE),
groupByOnJoinRuleCreates_(FALSE),
+ extraGrpOrderby_(NULL),
isRollup_(FALSE)
{}
@@ -152,6 +154,9 @@ public:
inline void setGroupExpr(ValueIdSet &expr) { groupExpr_ = expr;}
inline void addGroupExpr(ValueIdSet &expr) { groupExpr_ += expr;}
+ inline ValueIdSet & extraOrderExpr() { return extraOrderExpr_; }
+ inline const ValueIdSet & extraOrderExpr() const { return extraOrderExpr_; }
+
ValueIdList & rollupGroupExprList() { return rollupGroupExprList_; }
const ValueIdList & rollupGroupExprList() const { return rollupGroupExprList_; }
void setRollupGroupExprList(ValueIdList &expr) { rollupGroupExprList_ = expr;}
@@ -528,6 +533,9 @@ public:
NABoolean isRollup() { return isRollup_; }
const NABoolean isRollup() const { return isRollup_; }
+ ItemExpr * getExtraGrpOrderby() { return extraGrpOrderby_; }
+ void setExtraGrpOrderby(ItemExpr *ie) { extraGrpOrderby_ = ie; }
+
//////////////////////////////////////////////////////
private:
@@ -552,7 +560,9 @@ private:
// ---------------------------------------------------------------------
ItemExpr * groupExprTree_;
ValueIdSet groupExpr_;
-
+
+ ItemExpr * extraGrpOrderby_;
+ ValueIdSet extraOrderExpr_;
// --------------------------------------
// used for processing groupby rollup
// --------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index 8bd9a0b..ed5d7ca 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -7293,21 +7293,17 @@ set_function_specification : set_function_type '(' set_quantifier value_expressi
}
| TOK_GROUP_CONCAT '(' set_quantifier value_expression concat_options ')'
{
- //comehere
- CheckModeSpecial4;
$$ = new (PARSERHEAP())
PivotGroup(ITM_PIVOT_GROUP, $4, $5, $3);
}
| TOK_PIVOT '(' set_quantifier value_expression pivot_options ')'
{
- CheckModeSpecial4;
$$ = new (PARSERHEAP()) PivotGroup(ITM_PIVOT_GROUP, $4, $5, $3);
}
| TOK_PIVOT_GROUP '(' set_quantifier value_expression pivot_options ')'
{
- CheckModeSpecial4;
$$ = new (PARSERHEAP()) PivotGroup(ITM_PIVOT_GROUP, $4, $5, $3);
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/regress/executor/EXPECTED002.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED002.SB b/core/sql/regress/executor/EXPECTED002.SB
index 079aaaf..d05c6d4 100644
--- a/core/sql/regress/executor/EXPECTED002.SB
+++ b/core/sql/regress/executor/EXPECTED002.SB
@@ -6616,7 +6616,7 @@ s3 90
>>
>>SELECT student_name,
+> GROUP_CONCAT(DISTINCT test_score
-+> ORDER BY test_score SEPARATOR '-')
++> ORDER BY test_score desc SEPARATOR '-')
+> FROM student
+> GROUP BY student_name;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3fb43cb0/core/sql/regress/executor/TEST002
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST002 b/core/sql/regress/executor/TEST002
index f1c6f82..f4c7d5c 100755
--- a/core/sql/regress/executor/TEST002
+++ b/core/sql/regress/executor/TEST002
@@ -1102,7 +1102,7 @@ SELECT student_name,
SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
- ORDER BY test_score SEPARATOR '-')
+ ORDER BY test_score desc SEPARATOR '-')
FROM student
GROUP BY student_name;
drop table student;
[4/4] incubator-trafodion git commit: Merge [TRAFODION-2270] support
ORDER BY in PIVOT_GROUP/GROUP_CONCAT function
Posted by li...@apache.org.
Merge [TRAFODION-2270] support ORDER BY in PIVOT_GROUP/GROUP_CONCAT function
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a2093595
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a2093595
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a2093595
Branch: refs/heads/master
Commit: a20935955f041d8aa3b3074855a867f000bb2827
Parents: e17bdcd 91390a3
Author: Liu Ming <li...@apache.org>
Authored: Sat Nov 12 00:35:09 2016 +0000
Committer: Liu Ming <li...@apache.org>
Committed: Sat Nov 12 00:35:09 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/BindRelExpr.cpp | 23 +++++-
core/sql/optimizer/ItemExpr.cpp | 4 +-
core/sql/optimizer/ItemFunc.h | 2 +
core/sql/optimizer/NormRelExpr.cpp | 3 +
core/sql/optimizer/OptPhysRelExpr.cpp | 31 +++++---
core/sql/optimizer/RelCache.cpp | 10 +++
core/sql/optimizer/RelExpr.cpp | 2 +
core/sql/optimizer/RelGrby.h | 13 ++-
core/sql/parser/sqlparser.y | 4 -
core/sql/regress/executor/EXPECTED002.SB | 109 ++++++++++++++++++++++----
core/sql/regress/executor/TEST002 | 38 +++++++--
11 files changed, 200 insertions(+), 39 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a2093595/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a2093595/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a2093595/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a2093595/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a2093595/core/sql/optimizer/RelExpr.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a2093595/core/sql/optimizer/RelGrby.h
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a2093595/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
[3/4] incubator-trafodion git commit: fix more review comments for
TRAFODION-2270
Posted by li...@apache.org.
fix more review comments for TRAFODION-2270
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/91390a3f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/91390a3f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/91390a3f
Branch: refs/heads/master
Commit: 91390a3f579274758a27dcceb31e1964457b0b36
Parents: 0bba8d6
Author: Liu Ming <ov...@sina.com>
Authored: Thu Nov 10 09:18:42 2016 -0500
Committer: Liu Ming <ov...@sina.com>
Committed: Thu Nov 10 09:18:42 2016 -0500
----------------------------------------------------------------------
core/sql/optimizer/BindRelExpr.cpp | 4 ++-
core/sql/optimizer/NormRelExpr.cpp | 6 ++--
core/sql/optimizer/OptPhysRelExpr.cpp | 44 +++++++++++++++---------------
core/sql/optimizer/RelGrby.h | 3 +-
4 files changed, 30 insertions(+), 27 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/91390a3f/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 4a56d4e..f82d238 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -4553,7 +4553,9 @@ RelRoot * RelRoot::transformGroupByWithOrdinalPhase2(BindWA *bindWA)
if( ((PivotGroup*)vid.getItemExpr())->orderBy() )
{
grby->setExtraGrpOrderby(((PivotGroup*)vid.getItemExpr())->getOrderbyItemExpr());
- grby->getExtraGrpOrderby()->convertToValueIdList(grby->extraOrderExpr(), bindWA, ITM_ITEM_LIST);
+ ValueIdList tmpList;
+ grby->getExtraGrpOrderby()->convertToValueIdList(tmpList, bindWA, ITM_ITEM_LIST);
+ grby->setExtraOrderExpr(tmpList);
}
}
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/91390a3f/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index d83bb81..252deac 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -5218,9 +5218,9 @@ void GroupByAgg::rewriteNode(NormWA & normWARef)
if (rollupGroupExprList().normalizeNode(normWARef))
{
}
- if (extraOrderExpr().normalizeNode(normWARef))
- {
- }
+
+ normalizeExtraOrderExpr(normWARef);
+
// ---------------------------------------------------------------------
// Rewrite the expressions that are aggregate expressions
// ---------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/91390a3f/core/sql/optimizer/OptPhysRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/OptPhysRelExpr.cpp b/core/sql/optimizer/OptPhysRelExpr.cpp
index 91d67cc..5d87610 100644
--- a/core/sql/optimizer/OptPhysRelExpr.cpp
+++ b/core/sql/optimizer/OptPhysRelExpr.cpp
@@ -11808,29 +11808,30 @@ void SortGroupBy::addArrangementAndOrderRequirements(
// A GROUP BY ROLLUP needs the exact order as specified.
if (isRollup() && (NOT rollupGroupExprList().isEmpty()))
{
- rg.addSortKey(rollupGroupExprList());
+ if( NOT extraOrderExpr().isEmpty())
+ {
+ ValueIdList groupExprCpy(rollupGroupExprList());
+ groupExprCpy.insert(extraOrderExpr());
+ rg.addSortKey(groupExprCpy);
+ }
+ else
+ rg.addSortKey(rollupGroupExprList());
}
else if (NOT groupExpr().isEmpty())
- {
- // Shouldn't/Can't add a sort order type requirement
- // if we are in DP2
-
- if( NOT extraOrderExpr().isEmpty())
- {
- ValueIdList groupExprCpy(groupExpr());
- for (CollIndex i=0; i< extraOrderExpr().entries(); i++)
- {
- groupExprCpy.insert(extraOrderExpr().at(i));
- }
- rg.addSortKey(groupExprCpy);
- }
- else {
- if (rg.getStartRequirements()->executeInDP2())
- rg.addArrangement(groupExpr(),NO_SOT);
- else
- rg.addArrangement(groupExpr(),ESP_SOT);
- }
- }
+ {
+ if( NOT extraOrderExpr().isEmpty())
+ {
+ ValueIdList groupExprCpy(groupExpr());
+ groupExprCpy.insert(extraOrderExpr());
+ rg.addSortKey(groupExprCpy);
+ }
+ else {
+ if (rg.getStartRequirements()->executeInDP2())
+ rg.addArrangement(groupExpr(),NO_SOT);
+ else
+ rg.addArrangement(groupExpr(),ESP_SOT);
+ }
+ }
}
//<pb>
@@ -11868,7 +11869,6 @@ SortGroupBy::synthPhysicalProperty(const Context* myContext,
planNumber,
pws);
- //PhysicalProperty(((isRollup() ||NOT extraOrderExpr().isEmpty()) ? ValueIdList() : sppOfChild->getSortKey()),
PhysicalProperty* sppForMe =
new (CmpCommon::statementHeap())
PhysicalProperty((isRollup() ? ValueIdList() : sppOfChild->getSortKey()),
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/91390a3f/core/sql/optimizer/RelGrby.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelGrby.h b/core/sql/optimizer/RelGrby.h
index b493dd6..fe4c96a 100644
--- a/core/sql/optimizer/RelGrby.h
+++ b/core/sql/optimizer/RelGrby.h
@@ -154,8 +154,9 @@ public:
inline void setGroupExpr(ValueIdSet &expr) { groupExpr_ = expr;}
inline void addGroupExpr(ValueIdSet &expr) { groupExpr_ += expr;}
- inline ValueIdList & extraOrderExpr() { return extraOrderExpr_; }
+ inline void setExtraOrderExpr(const ValueIdList &newExtraOrder) { extraOrderExpr_ = newExtraOrder; }
inline const ValueIdList & extraOrderExpr() const { return extraOrderExpr_; }
+ void normalizeExtraOrderExpr( NormWA & normWARef ) { extraOrderExpr_.normalizeNode(normWARef); }
ValueIdList & rollupGroupExprList() { return rollupGroupExprList_; }
const ValueIdList & rollupGroupExprList() const { return rollupGroupExprList_; }
[2/4] incubator-trafodion git commit: [TRAFODION-2270] support ORDER
BY in PIVOT_GROUP/GROUP_CONCAT function, fix review comments
Posted by li...@apache.org.
[TRAFODION-2270] support ORDER BY in PIVOT_GROUP/GROUP_CONCAT function, fix review 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/0bba8d6d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/0bba8d6d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/0bba8d6d
Branch: refs/heads/master
Commit: 0bba8d6d83a19a9b48e2baa942083c37b69f6c09
Parents: 3fb43cb
Author: Liu Ming <ov...@sina.com>
Authored: Sat Oct 29 20:29:32 2016 -0400
Committer: Liu Ming <ov...@sina.com>
Committed: Sat Oct 29 20:29:32 2016 -0400
----------------------------------------------------------------------
core/sql/optimizer/BindRelExpr.cpp | 3 +-
core/sql/optimizer/OptPhysRelExpr.cpp | 5 +-
core/sql/optimizer/RelGrby.h | 6 +-
core/sql/regress/executor/EXPECTED002.SB | 107 ++++++++++++++++++++++----
core/sql/regress/executor/TEST002 | 36 +++++++--
5 files changed, 129 insertions(+), 28 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0bba8d6d/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 91efb2d..4a56d4e 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -4552,9 +4552,8 @@ RelRoot * RelRoot::transformGroupByWithOrdinalPhase2(BindWA *bindWA)
{
if( ((PivotGroup*)vid.getItemExpr())->orderBy() )
{
- //((PivotGroup*)vid.getItemExpr())->getOrderbyItemExpr()->bindNode(bindWA);
grby->setExtraGrpOrderby(((PivotGroup*)vid.getItemExpr())->getOrderbyItemExpr());
- grby->getExtraGrpOrderby()->convertToValueIdSet(grby->extraOrderExpr(), bindWA, ITM_ITEM_LIST);
+ grby->getExtraGrpOrderby()->convertToValueIdList(grby->extraOrderExpr(), bindWA, ITM_ITEM_LIST);
}
}
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0bba8d6d/core/sql/optimizer/OptPhysRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/OptPhysRelExpr.cpp b/core/sql/optimizer/OptPhysRelExpr.cpp
index de7e5b1..91d67cc 100644
--- a/core/sql/optimizer/OptPhysRelExpr.cpp
+++ b/core/sql/optimizer/OptPhysRelExpr.cpp
@@ -11818,10 +11818,9 @@ void SortGroupBy::addArrangementAndOrderRequirements(
if( NOT extraOrderExpr().isEmpty())
{
ValueIdList groupExprCpy(groupExpr());
- for (ValueId vid = extraOrderExpr().init(); extraOrderExpr().next(vid);
- extraOrderExpr().advance(vid))
+ for (CollIndex i=0; i< extraOrderExpr().entries(); i++)
{
- groupExprCpy.insert(vid);
+ groupExprCpy.insert(extraOrderExpr().at(i));
}
rg.addSortKey(groupExprCpy);
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0bba8d6d/core/sql/optimizer/RelGrby.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelGrby.h b/core/sql/optimizer/RelGrby.h
index 455a365..b493dd6 100644
--- a/core/sql/optimizer/RelGrby.h
+++ b/core/sql/optimizer/RelGrby.h
@@ -154,8 +154,8 @@ public:
inline void setGroupExpr(ValueIdSet &expr) { groupExpr_ = expr;}
inline void addGroupExpr(ValueIdSet &expr) { groupExpr_ += expr;}
- inline ValueIdSet & extraOrderExpr() { return extraOrderExpr_; }
- inline const ValueIdSet & extraOrderExpr() const { return extraOrderExpr_; }
+ inline ValueIdList & extraOrderExpr() { return extraOrderExpr_; }
+ inline const ValueIdList & extraOrderExpr() const { return extraOrderExpr_; }
ValueIdList & rollupGroupExprList() { return rollupGroupExprList_; }
const ValueIdList & rollupGroupExprList() const { return rollupGroupExprList_; }
@@ -562,7 +562,7 @@ private:
ValueIdSet groupExpr_;
ItemExpr * extraGrpOrderby_;
- ValueIdSet extraOrderExpr_;
+ ValueIdList extraOrderExpr_;
// --------------------------------------
// used for processing groupby rollup
// --------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0bba8d6d/core/sql/regress/executor/EXPECTED002.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED002.SB b/core/sql/regress/executor/EXPECTED002.SB
index d05c6d4..451eda1 100644
--- a/core/sql/regress/executor/EXPECTED002.SB
+++ b/core/sql/regress/executor/EXPECTED002.SB
@@ -6596,42 +6596,121 @@ A B C E
>>insert into student values('s3', 90);
--- 1 row(s) inserted.
->>
->>cqd mode_special_4 'on';
+>>insert into student values('s1', 88);
---- SQL operation complete.
+--- 1 row(s) inserted.
+>>insert into student values('s1', 60);
+
+--- 1 row(s) inserted.
+>>insert into student values('s1', 100);
+
+--- 1 row(s) inserted.
+>>insert into student values('s1', 50);
+
+--- 1 row(s) inserted.
+>>insert into student values('s1', 101);
+
+--- 1 row(s) inserted.
+>>insert into student values('s1', 40);
+
+--- 1 row(s) inserted.
+>>insert into student values('s3', 40);
+
+--- 1 row(s) inserted.
+>>insert into student values('s3', 40);
+
+--- 1 row(s) inserted.
+>>insert into student values('s3', 100);
+
+--- 1 row(s) inserted.
+>>insert into student values('s2', 88);
+
+--- 1 row(s) inserted.
+>>insert into student values('s2', 188);
+
+--- 1 row(s) inserted.
+>>insert into student values('s2', 18);
+
+--- 1 row(s) inserted.
+>>insert into student values('s2', 58);
+
+--- 1 row(s) inserted.
+>>
>>SELECT student_name,
-+> GROUP_CONCAT(test_score)
-+> FROM student
-+> GROUP BY student_name;
++> GROUP_CONCAT(DISTINCT test_score
++> ORDER BY test_score SEPARATOR '-')
++> FROM student
++> GROUP BY student_name;
STUDENT_NAME (EXPR)
------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------
-s1 88,79
-s2 88
-s3 90
+s1 40-50-60-79-88-100-101
+s2 18-58-88-188
+s3 40-90-100
--- 3 row(s) selected.
>>
>>SELECT student_name,
++> GROUP_CONCAT(test_score
++> ORDER BY test_score SEPARATOR '-')
++> FROM student
++> GROUP BY student_name;
+
+STUDENT_NAME (EXPR)
+------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------
+
+s1 40-50-60-79-88-88-100-101
+s2 18-58-88-88-188
+s3 40-40-90-100
+
+--- 3 row(s) selected.
+>>
+>>cqd ATTEMPT_ESP_PARALLELISM 'ON';
+
+--- SQL operation complete.
+>>control query shape exchange(cut);
+
+--- SQL operation complete.
+>>prepare s1 from SELECT student_name,
+> GROUP_CONCAT(DISTINCT test_score
+> ORDER BY test_score desc SEPARATOR '-')
+> FROM student
+> GROUP BY student_name;
+--- SQL command prepared.
+>>explain options 'f' s1;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+7 . 8 root 2.00E+000
+6 . 7 esp_exchange 1:2(hash2) 2.00E+000
+5 . 6 sort_groupby 2.00E+000
+4 . 5 sort_partial_groupby 4.00E+000
+3 . 4 sort 4.00E+000
+2 . 3 esp_exchange 2(hash2):1 4.00E+000
+1 . 2 hash_partial_groupby 4.00E+000
+. . 1 trafodion_scan STUDENT 1.00E+002
+
+--- SQL operation complete.
+>>execute s1;
+
STUDENT_NAME (EXPR)
------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------
-s1 88-79
-s2 88
-s3 90
+s2 188-88-58-18
+s1 101-100-88-79-60-50-40
+s3 100-90-40
--- 3 row(s) selected.
->>drop table student;
+>>control query shape cut;
--- SQL operation complete.
->>cqd mode_special_4 reset;
+>>cqd ATTEMPT_ESP_PARALLELISM 'OFF';
+
+--- SQL operation complete.
+>>drop table student;
--- SQL operation complete.
>>------------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0bba8d6d/core/sql/regress/executor/TEST002
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST002 b/core/sql/regress/executor/TEST002
index f4c7d5c..bfddf5d 100755
--- a/core/sql/regress/executor/TEST002
+++ b/core/sql/regress/executor/TEST002
@@ -1093,20 +1093,44 @@ insert into student values('s1', 88);
insert into student values('s1', 79);
insert into student values('s2', 88);
insert into student values('s3', 90);
+insert into student values('s1', 88);
+insert into student values('s1', 60);
+insert into student values('s1', 100);
+insert into student values('s1', 50);
+insert into student values('s1', 101);
+insert into student values('s1', 40);
+insert into student values('s3', 40);
+insert into student values('s3', 40);
+insert into student values('s3', 100);
+insert into student values('s2', 88);
+insert into student values('s2', 188);
+insert into student values('s2', 18);
+insert into student values('s2', 58);
-cqd mode_special_4 'on';
SELECT student_name,
- GROUP_CONCAT(test_score)
- FROM student
- GROUP BY student_name;
+ GROUP_CONCAT(DISTINCT test_score
+ ORDER BY test_score SEPARATOR '-')
+ FROM student
+ GROUP BY student_name;
SELECT student_name,
+ GROUP_CONCAT(test_score
+ ORDER BY test_score SEPARATOR '-')
+ FROM student
+ GROUP BY student_name;
+
+cqd ATTEMPT_ESP_PARALLELISM 'ON';
+control query shape exchange(cut);
+prepare s1 from SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
ORDER BY test_score desc SEPARATOR '-')
FROM student
- GROUP BY student_name;
+ GROUP BY student_name;
+explain options 'f' s1;
+execute s1;
+control query shape cut;
+cqd ATTEMPT_ESP_PARALLELISM 'OFF';
drop table student;
-cqd mode_special_4 reset;
------------------------------------------------------------------------
obey TEST002(BR0198_MULTI);