[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);