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 2016/11/11 02:06:57 UTC
[1/2] incubator-trafodion git commit: [TRAFODION-2312] Add support
for GROUPING_ID function
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 0cf28ae54 -> 575490c5f
[TRAFODION-2312] Add support for GROUPING_ID 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/449691b2
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/449691b2
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/449691b2
Branch: refs/heads/master
Commit: 449691b25489218b458281ee7c4044f6ad088e86
Parents: 12ead26
Author: Anoop Sharma <an...@esgyn.com>
Authored: Mon Nov 7 19:58:52 2016 +0000
Committer: Anoop Sharma <an...@esgyn.com>
Committed: Mon Nov 7 20:00:22 2016 +0000
----------------------------------------------------------------------
core/sql/common/OperTypeEnum.h | 1 +
core/sql/executor/ex_sort_grby.cpp | 8 +-
core/sql/optimizer/BindItemExpr.cpp | 9 +++
core/sql/optimizer/BindRelExpr.cpp | 88 ++++++++++++++++++++
core/sql/optimizer/RelMisc.h | 1 +
core/sql/optimizer/RelSequence.cpp | 3 +-
core/sql/parser/ParKeyWords.cpp | 1 +
core/sql/parser/sqlparser.y | 6 ++
core/sql/regress/seabase/EXPECTED033 | 129 +++++++++++++++++-------------
core/sql/regress/seabase/TEST033 | 18 +++--
10 files changed, 199 insertions(+), 65 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/common/OperTypeEnum.h
----------------------------------------------------------------------
diff --git a/core/sql/common/OperTypeEnum.h b/core/sql/common/OperTypeEnum.h
index 6f2129a..097be49 100644
--- a/core/sql/common/OperTypeEnum.h
+++ b/core/sql/common/OperTypeEnum.h
@@ -354,6 +354,7 @@ enum OperatorTypeEnum {
ITM_GROUPING = 2078,
ITM_AGGR_GROUPING_FUNC = 2079,
+ ITM_GROUPING_ID = 2080,
// custom functions
ITM_USER_DEF_FUNCTION = 2100,
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/executor/ex_sort_grby.cpp
----------------------------------------------------------------------
diff --git a/core/sql/executor/ex_sort_grby.cpp b/core/sql/executor/ex_sort_grby.cpp
index 424b8c1..219e1a1 100644
--- a/core/sql/executor/ex_sort_grby.cpp
+++ b/core/sql/executor/ex_sort_grby.cpp
@@ -178,7 +178,7 @@ short ex_sort_grby_tcb::handleCancel(sort_grby_step &step, short &rc)
ex_queue_entry * centry = qchild_.up->getHeadEntry();
ex_assert(centry->upState.parentIndex == qparent_.down->getHeadIndex(),
- "ex_sort_grby_tcb::work() child's reply out of sync");
+ "ex_sort_grby_tcb::handleCancel() child's reply out of sync");
ex_queue::up_status child_status = centry->upState.status;
switch(child_status)
@@ -199,7 +199,7 @@ short ex_sort_grby_tcb::handleCancel(sort_grby_step &step, short &rc)
break;
case ex_queue::Q_INVALID:
- ex_assert(0,"ex_sort_grby_tcb::work() Invalid state returned by child");
+ ex_assert(0,"ex_sort_grby_tcb::handleCancel() Invalid state returned by child");
break;
}; // end of switch on status of child queue
@@ -348,9 +348,9 @@ short ex_sort_grby_tcb::handleDone(sort_grby_step &step, short &rc,
if (NOT noAssert)
{
ex_assert(centry->upState.status == ex_queue::Q_NO_DATA,
- "ex_sort_grby_tcb::work() expecting Q_NO_DATA");
+ "ex_sort_grby_tcb::handleDone() expecting Q_NO_DATA");
ex_assert(centry->upState.parentIndex == qparent_.down->getHeadIndex(),
- "ex_sort_grby_tcb::work() child's reply out of sync");
+ "ex_sort_grby_tcb::handleDone() child's reply out of sync");
}
qchild_.up->removeHead();
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/optimizer/BindItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp
index af6a46d..5dbb64a 100644
--- a/core/sql/optimizer/BindItemExpr.cpp
+++ b/core/sql/optimizer/BindItemExpr.cpp
@@ -11951,6 +11951,15 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
}
break;
+ case ITM_GROUPING_ID:
+ {
+ *CmpCommon::diags() << DgSqlCode(-3242)
+ << DgString0("GROUPING_ID function must be specified in the select list of a GROUP BY ROLLUP statement.");
+ bindWA->setErrStatus();
+ return this;
+ }
+ break;
+
default:
{
bindWA->setErrStatus();
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 596c2c5..307fade 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -3904,6 +3904,62 @@ RelRoot * RelRoot::transformOrderByWithExpr(BindWA *bindWA)
return this;
}
+//////////////////////////////////////////////////////////////////////
+// GROUPING functions returns a 1 or 0 depending on whether a null
+// value was moved as a rollup group or not.
+//
+// GROUPING_ID(a,b,c) returns a value corresponding to the bit vector
+// where each bit entry represents the GROUPING result for the argument
+// of GROUPING_ID function.
+//
+// For ex: GROUPING_ID(a,b,c) will have 3 bit entries,
+// and is equivalent to:
+// GROUPING(a)*4 + GROUPING(b)*2 + GROUPING(c)*1
+//////////////////////////////////////////////////////////////////////
+ItemExpr * RelRoot::processGroupingID(ItemExpr * ie, BindWA *bindWA)
+{
+ if (ie->getOperatorType() != ITM_GROUPING_ID)
+ return ie;
+
+ ItemExpr * groupingIdExpr = NULL;
+
+ ItemExprList childExprList(bindWA->wHeap());
+ childExprList.insertTree(ie->child(0)->castToItemExpr());
+
+ Int64 multiplier = (Int64)pow(2, (childExprList.entries()-1));
+ SQLLargeInt * li =
+ new(bindWA->wHeap()) SQLLargeInt(FALSE, FALSE); // +ve value, no nulls
+ for (CollIndex i = 0; i < (CollIndex)childExprList.entries(); i++)
+ {
+ ItemExpr * currChildIE =
+ ((ItemExpr *) childExprList[i])->castToItemExpr();
+
+ ItemExpr * groupingClause =
+ new(bindWA->wHeap()) Aggregate(ITM_GROUPING, currChildIE, FALSE);
+
+ ItemExpr * multiplierClause = new(bindWA->wHeap())
+ ConstValue(li, (void*)&multiplier, sizeof(Int64));
+ ItemExpr * groupingExpr = new(bindWA->wHeap())
+ BiArith(ITM_TIMES, groupingClause, multiplierClause);
+
+ if (i == 0)
+ {
+ groupingIdExpr = groupingExpr;
+ }
+ else
+ {
+ groupingIdExpr = new(bindWA->wHeap())
+ BiArith(ITM_PLUS, groupingIdExpr, groupingExpr);
+ }
+
+ multiplier = multiplier / 2;
+ }
+
+ groupingIdExpr = new(bindWA->wHeap()) Cast(groupingIdExpr, li);
+
+ return groupingIdExpr;
+}
+
///////////////////////////////////////////////////////////////////////////
//
// This methods performs the following in this order:
@@ -3974,8 +4030,40 @@ RelRoot * RelRoot::transformGroupByWithOrdinalPhase1(BindWA *bindWA)
{
origGrbyList.insertTree(groupExprTree);
}
+
if (NOT compExprTreeIsNull)
{
+ // expand GROUPING_ID in terms of GROUPING aggregates
+ if (grby->isRollup())
+ {
+ NABoolean groupingIDfound = FALSE;
+
+ ItemExprList selList(getCompExprTree(), bindWA->wHeap());
+ ItemExprList newSelList(bindWA->wHeap());
+ for (CollIndex ii = 0; ii < selList.entries(); ii++)
+ {
+ ItemExpr * ie = selList[ii];
+ if (ie->getOperatorType() == ITM_GROUPING_ID)
+ {
+ ItemExpr * newIE = processGroupingID(ie, bindWA);
+ if (bindWA->errStatus())
+ return this;
+
+ groupingIDfound = TRUE;
+ newSelList.insert(newIE);
+ }
+ else
+ newSelList.insert(ie);
+ } // for
+
+ if (groupingIDfound)
+ {
+ ItemExpr * newCompExprTree = newSelList.convertToItemExpr();
+ removeCompExprTree();
+ addCompExprTree(newCompExprTree);
+ }
+ }
+
origSelectList.insertTree(getCompExprTree());
origSelectListCount = origSelectList.entries();
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/optimizer/RelMisc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelMisc.h b/core/sql/optimizer/RelMisc.h
index 9fd5f1a..7812d0b 100644
--- a/core/sql/optimizer/RelMisc.h
+++ b/core/sql/optimizer/RelMisc.h
@@ -448,6 +448,7 @@ public:
RelRoot * transformGroupByWithOrdinalPhase1(BindWA *bindWA);
RelRoot * transformGroupByWithOrdinalPhase2(BindWA *bindWA);
RelRoot * transformOrderByWithExpr(BindWA *bindWA);
+ ItemExpr * processGroupingID(ItemExpr * ie, BindWA *bindWA);
// MV --
NABoolean virtual isIncrementalMV() { return getFirstNRows()==-1 && !needFirstSortedRows(); }
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/optimizer/RelSequence.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelSequence.cpp b/core/sql/optimizer/RelSequence.cpp
index 0047812..89ed201 100644
--- a/core/sql/optimizer/RelSequence.cpp
+++ b/core/sql/optimizer/RelSequence.cpp
@@ -253,7 +253,8 @@ RelSequence::copyTopNode(RelExpr *derivedNode, CollHeap *outHeap)
//need to review the commented code below
//result->requiredOrderTree_ = requiredOrderTree_->copyTree(outHeap)->castToItemExpr();
- //result->partitionBy_ = partitionBy_->copyTree(outHeap)->castToItemExpr();
+ if (partitionBy_)
+ result->partitionBy_ = partitionBy_->copyTree(outHeap)->castToItemExpr();
//result->cancelExprTree_ = cancelExprTree_->copyTree(outHeap)->castToItemExpr();
// Copy any data members from the classes lower in the derivation chain.
//
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/parser/ParKeyWords.cpp
----------------------------------------------------------------------
diff --git a/core/sql/parser/ParKeyWords.cpp b/core/sql/parser/ParKeyWords.cpp
index c838bf7..332cb6c 100644
--- a/core/sql/parser/ParKeyWords.cpp
+++ b/core/sql/parser/ParKeyWords.cpp
@@ -468,6 +468,7 @@ ParKeyWord ParKeyWords::keyWords_[] = {
ParKeyWord("GROUP", TOK_GROUP, ANS_|RESWORD_|MPWORD_),
ParKeyWord("GROUP_CONCAT", TOK_GROUP_CONCAT, NONRESTOKEN_),
ParKeyWord("GROUPING", TOK_GROUPING, COMPAQ_|RESWORD_),
+ ParKeyWord("GROUPING_ID", TOK_GROUPING_ID, NONRESTOKEN_),
ParKeyWord("GZIP", TOK_GZIP, NONRESTOKEN_),
ParKeyWord("HARDWARE", TOK_HARDWARE, NONRESTOKEN_),
ParKeyWord("HASH", TOK_HASH, NONRESTOKEN_),
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index 1338974..d116579 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -1500,6 +1500,7 @@ static void enableMakeQuotedStringISO88591Mechanism()
%token <tokval> TOK_FUNCTION
%token <tokval> TOK_FUNCTIONS
%token <tokval> TOK_GROUPING
+%token <tokval> TOK_GROUPING_ID
%token <tokval> TOK_HOST
%token <tokval> TOK_ITERATE
@@ -10132,6 +10133,10 @@ misc_function :
{
$$ = new (PARSERHEAP()) HbaseTimestampRef($3);
}
+ | TOK_GROUPING_ID '(' value_expression_list ')'
+ {
+ $$ = new (PARSERHEAP()) ZZZBinderFunction(ITM_GROUPING_ID, $3);
+ }
hbase_column_create_list : '(' hbase_column_create_value ')'
{
@@ -33685,6 +33690,7 @@ nonreserved_func_word: TOK_ABS
| TOK_FLOOR
| TOK_FN
| TOK_GREATEST
+ | TOK_GROUPING_ID
| TOK_HASHPARTFUNC
| TOK_HASH2PARTFUNC
| TOK_HBASE_TIMESTAMP
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/regress/seabase/EXPECTED033
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED033 b/core/sql/regress/seabase/EXPECTED033
index eb8e727..af0dc54 100644
--- a/core/sql/regress/seabase/EXPECTED033
+++ b/core/sql/regress/seabase/EXPECTED033
@@ -14,12 +14,12 @@
--- SQL operation complete.
>>
>>-- empty table
->>select a, sum(b), grouping(a) from t033t1 group by rollup (a);
+>>select a, sum(b), grouping(a), grouping_id(a) from t033t1 group by rollup (a);
-A (EXPR) (EXPR)
------------ -------------------- ----------
+A (EXPR) (EXPR) (EXPR)
+----------- -------------------- ---------- --------------------
- ? ? 1
+ ? ? 1 1
--- 1 row(s) selected.
>>select b+1, sum(b) from t033t1 group by rollup (b);
@@ -50,17 +50,18 @@ A B C D
--- 6 row(s) selected.
>>
->>select cast(d as nullable), grouping(cast(d as nullable)) from t033t1
+>>select cast(d as nullable), grouping(cast(d as nullable)),
++> grouping_id(cast(d as nullable)) from t033t1
+> group by rollup (cast(d as nullable));
-(EXPR) (EXPR)
------------ ----------
+(EXPR) (EXPR) (EXPR)
+----------- ---------- --------------------
- 3 0
- 4 0
- 5 0
- 6 0
- ? 1
+ 3 0 0
+ 4 0 0
+ 5 0 0
+ 6 0 0
+ ? 1 1
--- 5 row(s) selected.
>>select cast(d as nullable) dd, grouping(cast(d as nullable)) from t033t1
@@ -90,7 +91,8 @@ DD (EXPR)
--- 5 row(s) selected.
>>
->>explain options 'f' select a,b,c,sum(d),grouping(a),grouping(b),grouping(c)
+>>explain options 'f' select a,b,c,sum(d),
++> grouping(a),grouping(b),grouping(c), grouping_id(a,b,c)
+> from t033t1
+> group by rollup(a,b,c) order by 1,2,3;
@@ -105,30 +107,30 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>select a,b,c,
-+> grouping(a), grouping(b), grouping(c),
++> grouping(a), grouping(b), grouping(c), grouping_id(a,b,c),
+> min(d),max(d),sum(d),avg(d),count(d),count(*),count(c)
+> from t033t1 group by rollup(a,b,c)
+> order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
-A B C (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
------------ ----------- ----------- ---------- ---------- ---------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- --------------------
-
- 1 2 3 0 0 0 4 4 4 4 1 1 1
- 1 2 ? 0 0 1 4 4 4 4 1 1 1
- 1 3 3 0 0 0 3 3 3 3 1 1 1
- 1 3 ? 0 0 1 3 3 3 3 1 1 1
- 1 ? ? 0 1 1 3 4 7 3 2 2 2
- 2 3 4 0 0 0 5 5 5 5 1 1 1
- 2 3 ? 0 0 1 5 5 5 5 1 1 1
- 2 ? ? 0 1 1 5 5 5 5 1 1 1
- 3 3 3 0 0 0 3 3 3 3 1 1 1
- 3 3 4 0 0 0 5 5 5 5 1 1 1
- 3 3 ? 0 0 1 3 5 8 4 2 2 2
- 3 ? ? 0 1 1 3 5 8 4 2 2 2
- ? ? ? 0 0 0 6 6 6 6 1 1 0
- ? ? ? 0 0 1 6 6 6 6 1 1 0
- ? ? ? 0 1 1 6 6 6 6 1 1 0
- ? ? ? 1 1 1 3 6 26 4 6 6 5
+A B C (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
+----------- ----------- ----------- ---------- ---------- ---------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- --------------------
+
+ 1 2 3 0 0 0 0 4 4 4 4 1 1 1
+ 1 2 ? 0 0 1 1 4 4 4 4 1 1 1
+ 1 3 3 0 0 0 0 3 3 3 3 1 1 1
+ 1 3 ? 0 0 1 1 3 3 3 3 1 1 1
+ 1 ? ? 0 1 1 3 3 4 7 3 2 2 2
+ 2 3 4 0 0 0 0 5 5 5 5 1 1 1
+ 2 3 ? 0 0 1 1 5 5 5 5 1 1 1
+ 2 ? ? 0 1 1 3 5 5 5 5 1 1 1
+ 3 3 3 0 0 0 0 3 3 3 3 1 1 1
+ 3 3 4 0 0 0 0 5 5 5 5 1 1 1
+ 3 3 ? 0 0 1 1 3 5 8 4 2 2 2
+ 3 ? ? 0 1 1 3 3 5 8 4 2 2 2
+ ? ? ? 0 0 0 0 6 6 6 6 1 1 0
+ ? ? ? 0 0 1 1 6 6 6 6 1 1 0
+ ? ? ? 0 1 1 3 6 6 6 6 1 1 0
+ ? ? ? 1 1 1 7 3 6 26 4 6 6 5
--- 16 row(s) selected.
>>
@@ -564,7 +566,8 @@ A
--- SQL operation complete.
>>explain options 'f' select a,b,c,sum(d),
-+> grouping(a), grouping(b), grouping(c) from t033t2
++> grouping(a), grouping(b), grouping(c),
++> grouping_id(a,b,c) from t033t2
+> group by rollup(a,b,c) order by 1,2,3;
LC RC OP OPERATOR OPT DESCRIPTION CARD
@@ -582,28 +585,28 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>select a,b,c,sum(d),
-+> grouping(a),grouping(b), grouping(c) from t033t2
++> grouping(a),grouping(b), grouping(c), grouping_id(a,b,c) from t033t2
+> group by rollup(a,b,c) order by 1,2,3;
-A B C (EXPR) (EXPR) (EXPR) (EXPR)
------------ ----------- ----------- -------------------- ---------- ---------- ----------
-
- 1 2 3 4 0 0 0
- 1 2 ? 4 0 0 1
- 1 3 3 3 0 0 0
- 1 3 ? 3 0 0 1
- 1 ? ? 7 0 1 1
- 2 3 4 5 0 0 0
- 2 3 ? 5 0 0 1
- 2 ? ? 5 0 1 1
- 3 3 3 3 0 0 0
- 3 3 4 5 0 0 0
- 3 3 ? 8 0 0 1
- 3 ? ? 8 0 1 1
- ? ? ? 6 0 0 0
- ? ? ? 6 0 0 1
- ? ? ? 6 0 1 1
- ? ? ? 26 1 1 1
+A B C (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
+----------- ----------- ----------- -------------------- ---------- ---------- ---------- --------------------
+
+ 1 2 3 4 0 0 0 0
+ 1 2 ? 4 0 0 1 1
+ 1 3 3 3 0 0 0 0
+ 1 3 ? 3 0 0 1 1
+ 1 ? ? 7 0 1 1 3
+ 2 3 4 5 0 0 0 0
+ 2 3 ? 5 0 0 1 1
+ 2 ? ? 5 0 1 1 3
+ 3 3 3 3 0 0 0 0
+ 3 3 4 5 0 0 0 0
+ 3 3 ? 8 0 0 1 1
+ 3 ? ? 8 0 1 1 3
+ ? ? ? 6 0 0 0 0
+ ? ? ? 6 0 0 1 1
+ ? ? ? 6 0 1 1 3
+ ? ? ? 26 1 1 1 7
--- 16 row(s) selected.
>>control query shape cut;
@@ -747,5 +750,23 @@ A B (EXPR) (EXPR) AA
*** ERROR[8822] The statement was not prepared.
+>>select grouping_id(a) from t033t1;
+
+*** ERROR[3242] This statement is not supported. Reason: GROUPING_ID function must be specified in the select list of a GROUP BY ROLLUP statement.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select grouping_id(a) from t033t1 group by (a);
+
+*** ERROR[3242] This statement is not supported. Reason: GROUPING_ID function must be specified in the select list of a GROUP BY ROLLUP statement.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select * from t033t1 where grouping_id(a) = 1;
+
+*** ERROR[3242] This statement is not supported. Reason: GROUPING_ID function must be specified in the select list of a GROUP BY ROLLUP statement.
+
+*** ERROR[8822] The statement was not prepared.
+
>>
>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/449691b2/core/sql/regress/seabase/TEST033
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST033 b/core/sql/regress/seabase/TEST033
index 582c2f3..ce79022 100644
--- a/core/sql/regress/seabase/TEST033
+++ b/core/sql/regress/seabase/TEST033
@@ -31,7 +31,7 @@ drop table if exists t033t3 cascade;
create table t033t1 (a int, b int, c int, d int not null);
-- empty table
-select a, sum(b), grouping(a) from t033t1 group by rollup (a);
+select a, sum(b), grouping(a), grouping_id(a) from t033t1 group by rollup (a);
select b+1, sum(b) from t033t1 group by rollup (b);
insert into t033t1 values (1,2,3,4),(2,3,4,5),(3,3,3,3),(3,3,4,5),(1,3,3,3),
@@ -39,18 +39,20 @@ insert into t033t1 values (1,2,3,4),(2,3,4,5),(3,3,3,3),(3,3,4,5),(1,3,3,3),
select * from t033t1 order by 1,2,3,4;
-select cast(d as nullable), grouping(cast(d as nullable)) from t033t1
+select cast(d as nullable), grouping(cast(d as nullable)),
+ grouping_id(cast(d as nullable)) from t033t1
group by rollup (cast(d as nullable));
select cast(d as nullable) dd, grouping(cast(d as nullable)) from t033t1
group by rollup (dd);
select cast(d as nullable) dd, grouping(cast(d as nullable)) from t033t1
group by rollup (1);
-explain options 'f' select a,b,c,sum(d),grouping(a),grouping(b),grouping(c)
+explain options 'f' select a,b,c,sum(d),
+ grouping(a),grouping(b),grouping(c), grouping_id(a,b,c)
from t033t1
group by rollup(a,b,c) order by 1,2,3;
select a,b,c,
- grouping(a), grouping(b), grouping(c),
+ grouping(a), grouping(b), grouping(c), grouping_id(a,b,c),
min(d),max(d),sum(d),avg(d),count(d),count(*),count(c)
from t033t1 group by rollup(a,b,c)
order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
@@ -160,10 +162,11 @@ insert into t033t2 values (10,1,2,3,4),(11,2,3,4,5),(12,3,3,3,3),
(13,3,3,4,5),(14,1,3,3,3),(15,null,null,null,6);
control query shape esp_exchange(cut);
explain options 'f' select a,b,c,sum(d),
- grouping(a), grouping(b), grouping(c) from t033t2
+ grouping(a), grouping(b), grouping(c),
+ grouping_id(a,b,c) from t033t2
group by rollup(a,b,c) order by 1,2,3;
select a,b,c,sum(d),
- grouping(a),grouping(b), grouping(c) from t033t2
+ grouping(a),grouping(b), grouping(c), grouping_id(a,b,c) from t033t2
group by rollup(a,b,c) order by 1,2,3;
control query shape cut;
@@ -204,6 +207,9 @@ select grouping(b) from t033t1;
select a,grouping(b) from t033t1 group by (a);
select a,grouping(b) from t033t1 group by rollup (a);
select a+1, grouping(a) from t033t1 group by rollup (a+1);
+select grouping_id(a) from t033t1;
+select grouping_id(a) from t033t1 group by (a);
+select * from t033t1 where grouping_id(a) = 1;
log;
[2/2] incubator-trafodion git commit: Merge [TRAFODION-2312] PR-824
Add support for GROUPING_ID function
Posted by an...@apache.org.
Merge [TRAFODION-2312] PR-824 Add support for GROUPING_ID 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/575490c5
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/575490c5
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/575490c5
Branch: refs/heads/master
Commit: 575490c5f456052cfa0178cb96c0497e712d2fbc
Parents: 0cf28ae 449691b
Author: Anoop Sharma <an...@esgyn.com>
Authored: Fri Nov 11 02:06:10 2016 +0000
Committer: Anoop Sharma <an...@esgyn.com>
Committed: Fri Nov 11 02:06:10 2016 +0000
----------------------------------------------------------------------
core/sql/common/OperTypeEnum.h | 1 +
core/sql/executor/ex_sort_grby.cpp | 8 +-
core/sql/optimizer/BindItemExpr.cpp | 9 +++
core/sql/optimizer/BindRelExpr.cpp | 88 ++++++++++++++++++++
core/sql/optimizer/RelMisc.h | 1 +
core/sql/optimizer/RelSequence.cpp | 3 +-
core/sql/parser/ParKeyWords.cpp | 1 +
core/sql/parser/sqlparser.y | 6 ++
core/sql/regress/seabase/EXPECTED033 | 129 +++++++++++++++++-------------
core/sql/regress/seabase/TEST033 | 18 +++--
10 files changed, 199 insertions(+), 65 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/575490c5/core/sql/common/OperTypeEnum.h
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/575490c5/core/sql/optimizer/BindItemExpr.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/575490c5/core/sql/parser/ParKeyWords.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/575490c5/core/sql/parser/sqlparser.y
----------------------------------------------------------------------