trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sure...@apache.org
Subject [1/2] incubator-trafodion git commit: [TRAFODION-2312] Add support for GROUPING function for rollup aggr.
Date Thu, 27 Oct 2016 15:07:32 GMT
Repository: incubator-trafodion
Updated Branches:
  refs/heads/master 0cd14d566 -> 7230c7b50


[TRAFODION-2312] Add support for GROUPING function for rollup aggr.


Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a992f107
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a992f107
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a992f107

Branch: refs/heads/master
Commit: a992f107e0483866d217640d189881bd1088ea3b
Parents: 9c0e5ab
Author: Anoop Sharma <anoop.sharma@esgyn.com>
Authored: Wed Oct 26 16:45:43 2016 +0000
Committer: Anoop Sharma <anoop.sharma@esgyn.com>
Committed: Wed Oct 26 16:45:43 2016 +0000

----------------------------------------------------------------------
 core/sql/cli/Statement.cpp             |  11 +-
 core/sql/comexe/ComTdbHashGrby.h       |   4 +
 core/sql/comexe/ComTdbSortGrby.h       |   2 +
 core/sql/common/OperTypeEnum.h         |   3 +
 core/sql/executor/ex_hash_grby.cpp     |  38 +++--
 core/sql/executor/ex_hash_grby.h       |   8 +-
 core/sql/executor/ex_sort_grby.cpp     | 126 +++++++++------
 core/sql/executor/ex_sort_grby.h       |  11 +-
 core/sql/exp/ExpPackDefs.cpp           |   4 +
 core/sql/exp/exp_aggregate.cpp         | 105 ++++++++++++
 core/sql/exp/exp_clause.cpp            |  17 ++
 core/sql/exp/exp_clause.h              |   3 +-
 core/sql/exp/exp_clause_derived.h      |  37 +++++
 core/sql/exp/exp_eval.cpp              |  38 -----
 core/sql/exp/exp_expr.cpp              |  17 +-
 core/sql/exp/exp_expr.h                |  35 ++--
 core/sql/exp/exp_fixup.cpp             |   7 +
 core/sql/generator/GenExpGenerator.cpp |  48 +++++-
 core/sql/generator/GenItemFunc.cpp     |  24 +++
 core/sql/optimizer/BindRelExpr.cpp     |  52 +++++-
 core/sql/optimizer/ItemExpr.cpp        |  32 ++++
 core/sql/optimizer/ItemFunc.h          |  44 ++++-
 core/sql/optimizer/SynthType.cpp       |  22 ++-
 core/sql/parser/sqlparser.y            |   1 +
 core/sql/regress/seabase/EXPECTED033   | 240 +++++++++++++++++++---------
 core/sql/regress/seabase/TEST033       |  66 ++++++--
 26 files changed, 760 insertions(+), 235 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/cli/Statement.cpp
----------------------------------------------------------------------
diff --git a/core/sql/cli/Statement.cpp b/core/sql/cli/Statement.cpp
index 93a03cb..f7e9e2a 100644
--- a/core/sql/cli/Statement.cpp
+++ b/core/sql/cli/Statement.cpp
@@ -1880,20 +1880,17 @@ Lng32 Statement::unpackAndInit(ComDiagsArea &diagsArea,
       ex_root_tdb *newRoot = (ex_root_tdb *)
 	root_tdb->driveUnpack((void *) root_tdb, &dummyTdb, unpackSpace_);
       
-      assignRootTdb(newRoot);
-      
-      if (root_tdb == NULL)  
+      if (newRoot == NULL)  
 	{
-	  // ERROR during unpacking. Shouldn't occur here since we have just
-	  // freshly prepared the statement using the latest version of the
-	  // compiler. This is an unexpected error.
-	  //
+	  // ERROR during unpacking. 
 	  if ((indexIntoCompilerArray >= 0) &&
 	      (cliGlobals_->getArkcmp(indexIntoCompilerArray)->getDiags()))
 	    diagsArea.mergeAfter(*cliGlobals_->getArkcmp()->getDiags());
 	  diagsArea << DgSqlCode(- CLI_STMT_NOT_PREPARED);
           return prepareReturn (ERROR); 
 	}
+
+      assignRootTdb(newRoot);
     }
 
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/comexe/ComTdbHashGrby.h
----------------------------------------------------------------------
diff --git a/core/sql/comexe/ComTdbHashGrby.h b/core/sql/comexe/ComTdbHashGrby.h
index 72536f3..b787c24 100644
--- a/core/sql/comexe/ComTdbHashGrby.h
+++ b/core/sql/comexe/ComTdbHashGrby.h
@@ -178,6 +178,10 @@ NA_EIDPROC
 NA_EIDPROC
   Int32 orderedQueueProtocol() const;
 
+  AggrExpr * hbAggrExpr()  const { return (AggrExpr*)((ex_expr*)hbAggrExpr_); }
+  AggrExpr * ofAggrExpr()  const { return (AggrExpr*)((ex_expr*)ofAggrExpr_); }
+  AggrExpr * resAggrExpr() const { return (AggrExpr*)((ex_expr*)resAggrExpr_); }
+
   // ---------------------------------------------------------------------
   // Used by the internal SHOWPLAN command to get attributes of a TDB.
   // ---------------------------------------------------------------------

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/comexe/ComTdbSortGrby.h
----------------------------------------------------------------------
diff --git a/core/sql/comexe/ComTdbSortGrby.h b/core/sql/comexe/ComTdbSortGrby.h
index 0547413..4c0528f 100644
--- a/core/sql/comexe/ComTdbSortGrby.h
+++ b/core/sql/comexe/ComTdbSortGrby.h
@@ -98,6 +98,8 @@ NA_EIDPROC
 NA_EIDPROC
   ~ComTdbSortGrby();
 
+  AggrExpr * aggrExpr() { return (AggrExpr*)((ex_expr*)aggrExpr_); }
+
   void setNumRollupGroups(Int16 v) { numRollupGroups_ = v; }
   Int16 numRollupGroups() { return numRollupGroups_; }
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/common/OperTypeEnum.h
----------------------------------------------------------------------
diff --git a/core/sql/common/OperTypeEnum.h b/core/sql/common/OperTypeEnum.h
index f2099b3..6f66760 100644
--- a/core/sql/common/OperTypeEnum.h
+++ b/core/sql/common/OperTypeEnum.h
@@ -350,6 +350,9 @@ enum OperatorTypeEnum {
   
                         ITM_PIVOT_GROUP = 2077,
 
+                        ITM_GROUPING = 2078,
+                        ITM_AGGR_GROUPING_FUNC = 2079,
+
                         // custom functions
                         ITM_USER_DEF_FUNCTION = 2100,
                         ITM_BETWEEN = 2101,

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/executor/ex_hash_grby.cpp
----------------------------------------------------------------------
diff --git a/core/sql/executor/ex_hash_grby.cpp b/core/sql/executor/ex_hash_grby.cpp
index af3ec84..139e411 100644
--- a/core/sql/executor/ex_hash_grby.cpp
+++ b/core/sql/executor/ex_hash_grby.cpp
@@ -174,9 +174,11 @@ ex_hash_grby_tcb::ex_hash_grby_tcb(const ex_hash_grby_tdb &  hash_grby_tdb,
   hbMoveInExpr_ = hash_grby_tdb.hbMoveInExpr_;
   ofMoveInExpr_ = hash_grby_tdb.ofMoveInExpr_;
   resMoveInExpr_ = hash_grby_tdb.resMoveInExpr_;
-  hbAggrExpr_ = hash_grby_tdb.hbAggrExpr_;
-  ofAggrExpr_ = hash_grby_tdb.ofAggrExpr_;
-  resAggrExpr_ = hash_grby_tdb.resAggrExpr_;
+
+  hbAggrExpr_ = hash_grby_tdb.hbAggrExpr();
+  ofAggrExpr_ = hash_grby_tdb.ofAggrExpr();
+  resAggrExpr_ = hash_grby_tdb.resAggrExpr();
+
   havingExpr_ = hash_grby_tdb.havingExpr_;
   moveOutExpr_ = hash_grby_tdb.moveOutExpr_;
   hbSearchExpr_ = hash_grby_tdb.hbSearchExpr_;
@@ -975,9 +977,10 @@ void ex_hash_grby_tcb::returnResultCurrentRow(HashRow * dataPointer)
   
     // when called from EID (with aggr) - turn curr row into a partial group
     if (hbAggrExpr_) { 
-      ((AggrExpr *)(hbAggrExpr_))->initializeAggr(workAtp_);
-      if (hbAggrExpr_->eval(childEntry->getAtp(), workAtp_) ==
-	  ex_expr::EXPR_ERROR) {
+      hbAggrExpr_->initializeAggr(workAtp_);
+      if ((hbAggrExpr_->perrecExpr()) &&
+          (hbAggrExpr_->perrecExpr()->eval(childEntry->getAtp(), workAtp_) ==
+           ex_expr::EXPR_ERROR)) {
 	workHandleError(childEntry->getAtp());
 	return; 
       }
@@ -1122,15 +1125,16 @@ void ex_hash_grby_tcb::workReadChild() {
         // The expression does not expect the HashRow to be part of the row.
         // Adjust the datapointer in the work atp to point beyond the HashRow.
 	workAtp_->getTupp(hbRowAtpIndex_).setDataPointer(hashRow->getData());
-	if (hbAggrExpr_->eval(childEntry->getAtp(), workAtp_) ==
-	    ex_expr::EXPR_ERROR)
+	if ((hbAggrExpr_->perrecExpr()) &&
+            (hbAggrExpr_->perrecExpr()->eval(childEntry->getAtp(), workAtp_) ==
+             ex_expr::EXPR_ERROR))
 	  {
             workHandleError(childEntry->getAtp());
 	    return;
 	  }
 
 	// Reset the "missed to find a group in the exisitng hash table",
-	// since we are looking for concecutive misses before we 
+	// since we are looking for consecutive misses before we 
 	// clear (send to parent) the hash table.
 	partialGroupbyMissCounter_ = 0;
       };
@@ -1144,9 +1148,10 @@ void ex_hash_grby_tcb::workReadChild() {
 				    &rc_)) {
 	// row is inserted. Initialize group and aggregate row
 	if (hbAggrExpr_) {
-	  ((AggrExpr *)(hbAggrExpr_))->initializeAggr(workAtp_);
-	  if (hbAggrExpr_->eval(childEntry->getAtp(), workAtp_) ==
-	      ex_expr::EXPR_ERROR)
+	  hbAggrExpr_->initializeAggr(workAtp_);
+	  if ((hbAggrExpr_->perrecExpr()) &&
+              (hbAggrExpr_->perrecExpr()->eval(childEntry->getAtp(), workAtp_) ==
+               ex_expr::EXPR_ERROR))
 	  {
             workHandleError(childEntry->getAtp());
 	    return;
@@ -1488,12 +1493,12 @@ void ex_hash_grby_tcb::workReadOverFlowRow() {
     HashRow * hashRow = ht->getNext(&cursor_);
     if (hashRow) {
       // the group exists already. Aggregate the child row into the group
-      if (ofAggrExpr_) {
+      if (ofAggrExpr_ && ofAggrExpr_->perrecExpr()) {
 
         // The expression does not expect the HashRow to be part of the row.
         // Adjust the datapointer in the work atp to point beyond the HashRow.
 	workAtp_->getTupp(hbRowAtpIndex_).setDataPointer(hashRow->getData());
-	if (ofAggrExpr_->eval(workAtp_, workAtp_) == ex_expr::EXPR_ERROR)
+	if (ofAggrExpr_->perrecExpr()->eval(workAtp_, workAtp_) == ex_expr::EXPR_ERROR)
 	  {
             workHandleError(workAtp_);
 	    return;
@@ -1515,7 +1520,7 @@ void ex_hash_grby_tcb::workReadOverFlowRow() {
 				    doNotOverflow )) {
 	// row is inserted. Initialize group and aggregate row
 	if (hbAggrExpr_)
-	  ((AggrExpr *)(hbAggrExpr_))->initializeAggr(workAtp_);
+	  hbAggrExpr_->initializeAggr(workAtp_);
 	else  
 	  // no aggr: then return now the row that was just inserted, but only
 	  // if we now read the unreturned part (i.e., the head of the list) 
@@ -1525,7 +1530,8 @@ void ex_hash_grby_tcb::workReadOverFlowRow() {
 	    returnResultCurrentRow(cluster->getLastDataPointer()) ;
 
 	if ((ofAggrExpr_) &&
-	    (ofAggrExpr_->eval(workAtp_, workAtp_) == ex_expr::EXPR_ERROR))
+            (ofAggrExpr_->perrecExpr()) &&
+	    (ofAggrExpr_->perrecExpr()->eval(workAtp_, workAtp_) == ex_expr::EXPR_ERROR))
 	  {
             workHandleError(workAtp_);
 	    return;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/executor/ex_hash_grby.h
----------------------------------------------------------------------
diff --git a/core/sql/executor/ex_hash_grby.h b/core/sql/executor/ex_hash_grby.h
index 1e4390f..6e56fdb 100644
--- a/core/sql/executor/ex_hash_grby.h
+++ b/core/sql/executor/ex_hash_grby.h
@@ -160,9 +160,9 @@ class ex_hash_grby_tcb : public ex_tcb {
   ex_expr      * hbMoveInExpr_;
   ex_expr      * ofMoveInExpr_;
   ex_expr      * resMoveInExpr_;
-  ex_expr      * hbAggrExpr_;
-  ex_expr      * ofAggrExpr_;
-  ex_expr      * resAggrExpr_;
+  AggrExpr     * hbAggrExpr_;
+  AggrExpr     * ofAggrExpr_;
+  AggrExpr     * resAggrExpr_;
   ex_expr      * havingExpr_;
   ex_expr      * moveOutExpr_;
   ex_expr      * hbSearchExpr_;
@@ -302,7 +302,7 @@ NA_EIDPROC
   void returnResultCurrentRow(HashRow * dataPointer = NULL);
 
 NA_EIDPROC
-void resetClusterAndReadFromChild(); // Tmobile.
+  void resetClusterAndReadFromChild(); // Tmobile.
 
 public:
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/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 1f3ffb4..424b8c1 100644
--- a/core/sql/executor/ex_sort_grby.cpp
+++ b/core/sql/executor/ex_sort_grby.cpp
@@ -123,9 +123,10 @@ ex_sort_grby_tcb::ex_sort_grby_tcb(const ex_sort_grby_tdb &  sort_grby_tdb,
 
   // fixup aggr expression
   if (aggrExpr())
-    (void) aggrExpr()->fixup(0, getExpressionMode(), this, space, heap,
-			     glob->computeSpace(), glob);
-
+    (void) aggrExpr()->fixup(0, getExpressionMode(), this, 
+                             space, heap,
+                             glob->computeSpace(), glob);
+  
   // fixup move expression
   if (moveExpr())
     (void) moveExpr()->fixup(0, getExpressionMode(), this, space, heap,
@@ -534,7 +535,7 @@ short ex_sort_grby_tcb::work()
 		// initialize the aggregate
 		if (aggrExpr())	 
 		  {
-		    if (((AggrExpr *)aggrExpr())->initializeAggr(workAtp_) ==
+		    if (aggrExpr()->initializeAggr(workAtp_) ==
 			ex_expr::EXPR_ERROR)
 		      {
                         pstate->step_ =
@@ -543,8 +544,7 @@ short ex_sort_grby_tcb::work()
 		      }
 		    // a flag in the expression is set at compilation time
 		    // if this is a single row aggregate
-		    pstate->oneRowAggr_ =
-		      ((AggrExpr *)(aggrExpr()))->isOneRowAggr();
+		    pstate->oneRowAggr_ = aggrExpr()->isOneRowAggr();
 		  }
 
 		// if group by not being done(scalar aggrs) and no rows were 
@@ -553,7 +553,7 @@ short ex_sort_grby_tcb::work()
 		if ((!grbyExpr()) && 
 		    (centry->upState.status == ex_queue::Q_NO_DATA))
 		  {
-		    if (((AggrExpr *)aggrExpr())->finalizeNullAggr(workAtp_)
+		    if (aggrExpr()->finalizeNullAggr(workAtp_)
 			== ex_expr::EXPR_ERROR)
 		      {
                         pstate->step_ =
@@ -618,11 +618,12 @@ short ex_sort_grby_tcb::work()
 		  else
 		    {
 		      // aggregate the row.
-		      if (aggrExpr())
+		      if (aggrExpr() && aggrExpr()->perrecExpr())
 			{
                           ex_expr::exp_return_type retcode;
-                          retcode = aggrExpr()->eval(centry->getAtp(),
-						     workAtp_);
+                          retcode = 
+                            aggrExpr()->perrecExpr()->eval(centry->getAtp(),
+                                                           workAtp_);
                           if ( retcode == ex_expr::EXPR_OK &&
 			       pstate->oneRowAggr_ &&
                                moveExpr() &&
@@ -764,27 +765,60 @@ ex_sort_grby_rollup_tcb::ex_sort_grby_rollup_tcb
     }
 }
 
-
-short ex_sort_grby_rollup_tcb::rollupGrbyMoveNull(Int16 groupNum)
+// this method does 3 things:
+//  1) moves rollup nulls to return buffer
+//  2) evaluates grouping function for rollup nulls
+//  3) moves grouping function result to return buffer
+short ex_sort_grby_rollup_tcb::processRollupGrbyNulls(Int16 groupNum)
 {
-  ExpTupleDesc * returnRowTD =
-    sort_grby_tdb().getCriDescUp()->getTupleDescriptor
-    (sort_grby_tdb().tuppIndex_);
-
-  // returnRowTD has aggregate entries followed by group entries.
-  Int16 totalEntries = returnRowTD->numAttrs();
-  Int16 numAggr = totalEntries - sort_grby_tdb().numRollupGroups();
-  Int16 startEntry = numAggr + (groupNum > 0 ? groupNum : 0);
-  Int16 endEntry = totalEntries - 1;
-  char * grbyData = rollupGroupAggrArr_[groupNum];
-
-  // move nulls to rollup groups
-  for (Int16 i = startEntry; i <= endEntry; i++)
+  char * grbyData = (groupNum >= 0 ? rollupGroupAggrArr_[groupNum] : NULL);
+  Int16 startEntry = -1;
+  Int16 endEntry = -1;
+  Int16 numAggr = -1;
+  //  move rollup nulls to return buffer
+  if (grbyData)
+    {
+      // returnRowTD has aggregate entries followed by group entries.
+      ExpTupleDesc * returnRowTD =
+        sort_grby_tdb().getCriDescUp()->getTupleDescriptor
+        (sort_grby_tdb().tuppIndex_);
+      
+      Int16 totalEntries = returnRowTD->numAttrs();
+      numAggr = totalEntries - sort_grby_tdb().numRollupGroups();
+      startEntry = numAggr + (groupNum > 0 ? groupNum : 0);
+      endEntry = totalEntries - 1;
+      
+      // move nulls to rollup groups
+      for (Int16 i = startEntry; i <= endEntry; i++)
+        {
+          Attributes * attr = returnRowTD->getAttr(i);
+          if (attr->getNullFlag())
+            {
+              *(short*)&grbyData[attr->getNullIndOffset()] = -1;
+            }
+        }
+      
+      // copy data from rollupArr to work atp so it could be returned.
+      char * workDataPtr = 
+        workAtp_->getTupp(sort_grby_tdb().tuppIndex_).getDataPointer();
+      str_cpy_all(workDataPtr, grbyData, recLen());
+    }
+
+  // evaluate grouping function for null values
+  if (grbyData && aggrExpr() && aggrExpr()->groupingExpr())
     {
-      Attributes * attr = returnRowTD->getAttr(i);
-      if (attr->getNullFlag())
+      if (aggrExpr()->evalGroupingForNull(startEntry-numAggr, endEntry-numAggr))
         {
-          *(short*)&grbyData[attr->getNullIndOffset()] = -1;
+          return -1;
+        }
+    }
+
+  // move grouping function result to return buffer
+  if (aggrExpr() && aggrExpr()->groupingExpr())
+    {
+      if (aggrExpr()->groupingExpr()->eval(workAtp_, workAtp_) == ex_expr::EXPR_ERROR)
+        {
+          return -1;
         }
     }
 
@@ -828,7 +862,7 @@ short ex_sort_grby_rollup_tcb::rollupAggrInit()
     return 0;
 
   // initialize the regular aggrs
-  if (((AggrExpr *)aggrExpr())->initializeAggr(workAtp_) ==
+  if (aggrExpr()->initializeAggr(workAtp_) ==
       ex_expr::EXPR_ERROR)
     {
       return -1;
@@ -843,7 +877,7 @@ short ex_sort_grby_rollup_tcb::rollupAggrInit()
       workAtp_->getTupp(sort_grby_tdb().tuppIndex_)
         .setDataPointer(rollupGroupAggrArr_[i]);
 
-      if (((AggrExpr *)aggrExpr())->initializeAggr(workAtp_) ==
+      if (aggrExpr()->initializeAggr(workAtp_) ==
           ex_expr::EXPR_ERROR)
         {
           workAtp_->getTupp(sort_grby_tdb().tuppIndex_)
@@ -859,11 +893,11 @@ short ex_sort_grby_rollup_tcb::rollupAggrInit()
 
 short ex_sort_grby_rollup_tcb::rollupAggrEval(ex_queue_entry * centry)
 {
-  if (! aggrExpr())
+  if (! aggrExpr() || ! aggrExpr()->perrecExpr())
     return 0;
 
   // evaluate the regular aggr
-  if (aggrExpr()->eval(centry->getAtp(), workAtp_) == ex_expr::EXPR_ERROR)
+  if (aggrExpr()->perrecExpr()->eval(centry->getAtp(), workAtp_) == ex_expr::EXPR_ERROR)
     {
       return -1;
     }
@@ -877,7 +911,7 @@ short ex_sort_grby_rollup_tcb::rollupAggrEval(ex_queue_entry * centry)
       workAtp_->getTupp(sort_grby_tdb().tuppIndex_)
         .setDataPointer(rollupGroupAggrArr_[i]);
 
-      if (aggrExpr()->eval(centry->getAtp(), workAtp_) == ex_expr::EXPR_ERROR)
+      if (aggrExpr()->perrecExpr()->eval(centry->getAtp(), workAtp_) == ex_expr::EXPR_ERROR)
         {
           workAtp_->getTupp(sort_grby_tdb().tuppIndex_)
             .setDataPointer(tempWorkDataPtr);
@@ -1079,7 +1113,7 @@ short ex_sort_grby_rollup_tcb::work()
                     workAtp_->getTupp(sort_grby_tdb().tuppIndex_)
                       .setDataPointer(rollupGroupAggrArr_[0]);
                     
-		    if (((AggrExpr *)aggrExpr())->initializeAggr(workAtp_) ==
+		    if (aggrExpr()->initializeAggr(workAtp_) ==
 			ex_expr::EXPR_ERROR)
 		      {
                         pstate->step_ =
@@ -1221,6 +1255,12 @@ short ex_sort_grby_rollup_tcb::work()
 	case SORT_GRBY_FINALIZE:
 	case SORT_GRBY_FINALIZE_CANCEL:
 	  {
+            if (processRollupGrbyNulls(-1))
+              {
+                step_ = SORT_GRBY_LOCAL_ERROR;
+                break;
+              }
+
             if (handleFinalize(step_, rc))
               return rc;
 
@@ -1291,23 +1331,17 @@ short ex_sort_grby_rollup_tcb::work()
             if (qparent_.up->isFull())
               return WORK_OK; // parent queue is full. Just return
 
-            if (rollupGrbyMoveNull(currGroupNum_))
+            if (processRollupGrbyNulls(currGroupNum_))
               {
                 step_ = SORT_GRBY_LOCAL_ERROR;
                 break;
               }
 
-            // copy data from rollupArr to work atp so it could be returned.
-            char * currRollupDataPtr = rollupGroupAggrArr_[currGroupNum_];
-            char * workDataPtr = 
-              workAtp_->getTupp(sort_grby_tdb().tuppIndex_).getDataPointer();
-            str_cpy_all(workDataPtr, currRollupDataPtr, recLen());
-            
             if (handleFinalize(step_, rc))
               {
                 return rc;
               }
-            
+
             currGroupNum_--;
 
             step_ = SORT_GRBY_ROLLUP_GROUP_START;
@@ -1332,18 +1366,12 @@ short ex_sort_grby_rollup_tcb::work()
                 return WORK_OK; // parent queue is full. Just return
               }
 
-            if (rollupGrbyMoveNull(0))
+            if (processRollupGrbyNulls(0))
               {
                 step_ = SORT_GRBY_LOCAL_ERROR;
                 break;
               }
 
-            // copy data from rollupArr to work atp so it could be returned.
-            char * currRollupDataPtr = rollupGroupAggrArr_[0];
-            char * workDataPtr = 
-              workAtp_->getTupp(sort_grby_tdb().tuppIndex_).getDataPointer();
-            str_cpy_all(workDataPtr, currRollupDataPtr, recLen());
-            
             if (handleFinalize(step_, rc))
               {
                 return rc;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/executor/ex_sort_grby.h
----------------------------------------------------------------------
diff --git a/core/sql/executor/ex_sort_grby.h b/core/sql/executor/ex_sort_grby.h
index bf1dc07..63fa075 100644
--- a/core/sql/executor/ex_sort_grby.h
+++ b/core/sql/executor/ex_sort_grby.h
@@ -185,7 +185,8 @@ NA_EIDPROC
        Lng32 &numElems,      // inout, desired/actual elements
        Lng32 &pstateLength); // out, length of one element
 
-  inline ex_expr * aggrExpr() const { return sort_grby_tdb().aggrExpr_; };
+  AggrExpr * aggrExpr() const { return sort_grby_tdb().aggrExpr(); }
+
   inline ex_expr * grbyExpr() const { return sort_grby_tdb().grbyExpr_; };
   inline ex_expr * moveExpr() const { return sort_grby_tdb().moveExpr_; };
   inline ex_expr * havingExpr() const { return sort_grby_tdb().havingExpr_; };
@@ -236,9 +237,11 @@ public:
 private:
   short rollupAggrInit();
 
-  // move null to rollup array for all rollup groups impacted by change 
-  // in group groupNum
-  short rollupGrbyMoveNull(Int16 groupNum);
+  // this method does 3 things:
+  //  1) moves rollup nulls to return buffer
+  //  2) evaluates grouping function for rollup nulls
+  //  3) moves grouping function result to return buffer
+  short processRollupGrbyNulls(Int16 groupNum);
 
   // move values from child row to rollup array
   short rollupGrbyMoveValue(ex_queue_entry * centry);

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/ExpPackDefs.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/ExpPackDefs.cpp b/core/sql/exp/ExpPackDefs.cpp
index e319328..2b6874a 100644
--- a/core/sql/exp/ExpPackDefs.cpp
+++ b/core/sql/exp/ExpPackDefs.cpp
@@ -134,6 +134,8 @@ NA_EIDPROC Long AggrExpr::pack(void * space_)
   perrecExpr_.pack(space_);
   finalNullExpr_.pack(space_);
   finalExpr_.pack(space_);
+  groupingExpr_.pack(space_);
+
   return ex_expr::pack(space_);
 }
 
@@ -684,6 +686,8 @@ NA_EIDPROC Lng32 AggrExpr::unpack(void *base, void * reallocator)
   if (perrecExpr_.unpack(base, reallocator)) return -1;
   if (finalNullExpr_.unpack(base, reallocator)) return -1;
   if (finalExpr_.unpack(base, reallocator)) return -1;
+  if (groupingExpr_.unpack(base, reallocator)) return -1;
+
   return ex_expr::unpack(base, reallocator);
 }
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_aggregate.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_aggregate.cpp b/core/sql/exp/exp_aggregate.cpp
index 52b80ff..ab06402 100644
--- a/core/sql/exp/exp_aggregate.cpp
+++ b/core/sql/exp/exp_aggregate.cpp
@@ -47,6 +47,86 @@
 #include "str.h"
 
 ///////////////////////////////////////////////////
+// class AggrExpr
+//////////////////////////////////////////////////
+ex_expr::exp_return_type AggrExpr::initializeAggr(atp_struct * atp)
+{
+  if (initExpr_)
+    {
+      if (initExpr_->eval(atp, atp) == ex_expr::EXPR_ERROR)
+	return ex_expr::EXPR_ERROR;
+    }
+
+  if (perrecExpr_)
+    {
+      ex_clause *clause = perrecExpr_->getClauses();
+      while (clause)
+        {
+          if (clause->getType() == ex_clause::AGGREGATE_TYPE)
+            {
+              if (((ex_aggregate_clause *)clause)->init() == ex_expr::EXPR_ERROR)
+                return ex_expr::EXPR_ERROR;
+            }
+          
+          clause = clause->getNextClause();
+        }
+    }
+
+  if (groupingExpr_)
+    {
+      ex_clause *clause = groupingExpr_->getClauses();
+      while (clause)
+        {
+          if (clause->getType() == ex_clause::AGGREGATE_TYPE)
+            {
+              if (((ex_aggregate_clause *)clause)->init() == ex_expr::EXPR_ERROR)
+                return ex_expr::EXPR_ERROR;
+            }
+          
+          clause = clause->getNextClause();
+        }
+    }
+
+  return ex_expr::EXPR_OK;
+}
+
+ex_expr::exp_return_type AggrExpr::finalizeAggr(atp_struct * /*atp*/)
+{
+  return ex_expr::EXPR_OK;
+}
+
+ex_expr::exp_return_type AggrExpr::finalizeNullAggr(atp_struct * atp)
+{
+  if (finalNullExpr_)
+    return finalNullExpr_->eval(atp, atp);
+  else
+    return ex_expr::EXPR_OK;
+}
+
+ex_expr::exp_return_type AggrExpr::evalGroupingForNull(
+     Int16 startEntry, Int16 endEntry)
+{
+  if (groupingExpr_)
+    {
+      ex_clause *clause = groupingExpr_->getClauses();
+      while (clause)
+        {
+          if (clause->getOperType() == ITM_AGGR_GROUPING_FUNC)
+            {
+              ExFunctionGrouping * g = (ExFunctionGrouping*)clause;
+              if ((g->getRollupGroupIndex() >= startEntry) &&
+                  (g->getRollupGroupIndex() <= endEntry))
+                g->setRollupNull(-1);
+            }
+          
+          clause = clause->getNextClause();
+        }
+    }
+
+  return ex_expr::EXPR_OK;
+}
+
+///////////////////////////////////////////////////
 // class ex_aggregate_clause
 //////////////////////////////////////////////////
 ex_expr::exp_return_type ex_aggregate_clause::init()
@@ -211,6 +291,31 @@ ex_expr::exp_return_type ex_aggr_min_max_clause::eval(char * op_data[],
 }
 
 /////////////////////////////////////////////////
+// class ExFunctionGrouping
+/////////////////////////////////////////////////
+ex_expr::exp_return_type ExFunctionGrouping::init()
+{
+  ex_expr::exp_return_type retcode = ex_expr::EXPR_OK;
+
+  rollupNull_ = 0;
+
+  return retcode;  
+}
+
+ex_expr::exp_return_type ExFunctionGrouping::eval(char *op_data[],
+                                                  CollHeap *heap,
+                                                  ComDiagsArea** diagsArea)
+{
+  char * tgt = op_data[0];
+  if (rollupNull_)
+    *(UInt32*)tgt = 1;
+  else
+    *(UInt32*)tgt = 0;
+
+  return ex_expr::EXPR_OK;
+}
+
+/////////////////////////////////////////////////
 // class ex_pivot_group_clause
 /////////////////////////////////////////////////
 ex_expr::exp_return_type ex_pivot_group_clause::init()

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_clause.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_clause.cpp b/core/sql/exp/exp_clause.cpp
index ca018ab..f4b9114 100644
--- a/core/sql/exp/exp_clause.cpp
+++ b/core/sql/exp/exp_clause.cpp
@@ -452,6 +452,9 @@ ex_clause::ex_clause(clause_type type,
 	case ITM_AGGR_MIN_MAX:
 	  setClassID(AGGR_MIN_MAX_ID);
 	  break;
+	case ITM_AGGR_GROUPING_FUNC:
+	  setClassID(AGGR_GROUPING_ID);
+	  break;
 	case ITM_CURRENTEPOCH:
 	case ITM_VSBBROWTYPE:
 	case ITM_VSBBROWCOUNT:
@@ -897,6 +900,9 @@ NA_EIDPROC char *ex_clause::findVTblPtr(short classID)
     case ex_clause::AGGR_MIN_MAX_ID:
       GetVTblPtr(vtblPtr, ex_aggr_min_max_clause);
       break;
+    case ex_clause::AGGR_GROUPING_ID:
+      GetVTblPtr(vtblPtr, ExFunctionGrouping);
+      break;
     case ex_clause::AGGREGATE_TYPE:
       GetVTblPtr(vtblPtr, ex_aggregate_clause);
       break;
@@ -1153,6 +1159,7 @@ NA_EIDPROC const char * getOperTypeEnumAsString(Int16 /*OperatorTypeEnum*/ ote)
 
     // LCOV_EXCL_STOP
     case ITM_AGGR_MIN_MAX: return "ITM_AGGR_MIN_MAX";
+    case ITM_AGGR_GROUPING_FUNC: return "ITM_AGGR_GROUPING_FUNC";
 
     // custom functions
     // LCOV_EXCL_START
@@ -1838,6 +1845,16 @@ void ex_pivot_group_clause::displayContents(Space * space, const char * /*displa
   ex_clause::displayContents(space, "ex_pivot_group_clause", clauseNum, constsArea);
 }
 
+void ExFunctionGrouping::displayContents(Space * space, const char * /*displayStr*/, Int32 clauseNum, char * constsArea)
+{
+  ex_clause::displayContents(space, "ExFunctionGrouping", clauseNum, constsArea);
+
+  char buf[100];
+  str_sprintf(buf, "    rollupGroupIndex_ = %d\n",
+              rollupGroupIndex_);
+  space->allocateAndCopyToAlignedSpace(buf, str_len(buf), sizeof(short));
+}
+
 void ex_arith_clause::displayContents(Space * space, const char * /*displayStr*/, Int32 clauseNum, char * constsArea)
 {
   setInstruction();

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_clause.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_clause.h b/core/sql/exp/exp_clause.h
index 4fc36d2..c8b079b 100644
--- a/core/sql/exp/exp_clause.h
+++ b/core/sql/exp/exp_clause.h
@@ -202,7 +202,8 @@ public:
     FUNC_HBASE_VERSION       = 117,
     FUNC_ISIP_ID             = 118,
     FUNC_INETATON_ID         = 119,
-    FUNC_INETNTOA_ID         = 120
+    FUNC_INETNTOA_ID         = 120,
+    AGGR_GROUPING_ID         = 121
   };
 
   // max number of operands (including result) in a clause.

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_clause_derived.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_clause_derived.h b/core/sql/exp/exp_clause_derived.h
index 1104e46..5669b66 100644
--- a/core/sql/exp/exp_clause_derived.h
+++ b/core/sql/exp/exp_clause_derived.h
@@ -274,6 +274,43 @@ private:
   // ---------------------------------------------------------------------
 };
 
+class SQLEXP_LIB_FUNC  ExFunctionGrouping : public ex_aggregate_clause {
+public:
+  ExFunctionGrouping(OperatorTypeEnum oper_type,
+                     short num_operands,
+                     Attributes ** attr,
+                     Int16 rollupGroupIndex,
+                     Space * space)
+    : ex_aggregate_clause(oper_type, num_operands, attr, space),
+    rollupGroupIndex_(rollupGroupIndex),
+    rollupNull_(0)
+      {};
+  
+  ExFunctionGrouping(){};
+
+  ex_expr::exp_return_type init();
+  ex_expr::exp_return_type eval(char *op_data[], 
+                                CollHeap* = 0, 
+                                ComDiagsArea** = 0);  
+
+  NA_EIDPROC virtual short getClassSize() { return (short)sizeof(*this); }
+
+  // Display
+  //
+  NA_EIDPROC void displayContents(Space * space, const char * displayStr, 
+                                  Int32 clauseNum, char * constsArea);
+
+  Int16 getRollupGroupIndex() { return rollupGroupIndex_; }
+  void setRollupNull(short v) {  rollupNull_ = v; }
+
+private:
+  Int16 rollupGroupIndex_;
+  Int16 rollupNull_;
+  
+  char fillers_[60];
+  // ---------------------------------------------------------------------
+};
+
 class SQLEXP_LIB_FUNC  ex_pivot_group_clause : public ex_aggregate_clause {
 public:	
   // Construction

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_eval.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_eval.cpp b/core/sql/exp/exp_eval.cpp
index f94c6a0..d49c3d3 100644
--- a/core/sql/exp/exp_eval.cpp
+++ b/core/sql/exp/exp_eval.cpp
@@ -810,10 +810,8 @@ ex_expr::exp_return_type ex_expr::evalClauses(ex_clause *clause,
               (*nulldata) = 0;
             }
           }
-	  
 
 	} // for
-
 	  
       // -----------------------------------------------------------------
       // Call the NULL processing method for this clause and, if still
@@ -8588,39 +8586,3 @@ ex_expr::exp_return_type ex_clause::eval(char * /*op_data*/[],
   return ex_expr::EXPR_OK;
 };
 
-ex_expr::exp_return_type AggrExpr::initializeAggr(atp_struct * atp)
-{
-  if (initExpr_)
-    {
-      if (initExpr_->eval(atp, atp) == ex_expr::EXPR_ERROR)
-	return ex_expr::EXPR_ERROR;
-    }
-
-  ex_clause *clause = getClauses();
-  while (clause)
-    {
-      if (clause->getType() == ex_clause::AGGREGATE_TYPE)
-	{
-	  if (((ex_aggregate_clause *)clause)->init() == ex_expr::EXPR_ERROR)
-	    return ex_expr::EXPR_ERROR;
-	}
-      
-      clause = clause->getNextClause();
-    }
-
-  return ex_expr::EXPR_OK;
-}
-
-ex_expr::exp_return_type AggrExpr::finalizeAggr(atp_struct * /*atp*/)
-{
-  return ex_expr::EXPR_OK;
-}
-
-ex_expr::exp_return_type AggrExpr::finalizeNullAggr(atp_struct * atp)
-{
-  if (finalNullExpr_)
-    return finalNullExpr_->eval(atp, atp);
-  else
-    return ex_expr::EXPR_OK;
-}
-

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_expr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_expr.cpp b/core/sql/exp/exp_expr.cpp
index 0b4e1a6..f1f374e 100644
--- a/core/sql/exp/exp_expr.cpp
+++ b/core/sql/exp/exp_expr.cpp
@@ -525,11 +525,20 @@ void AggrExpr::displayContents(Space * space, short mode, const char * displaySt
       finalExpr_->displayContents(space, mode, buf, flag);
     }
 
-#ifndef __EID
-  str_sprintf(buf, "%s::perrecExpr_", displayStr);
-#endif
+  if (groupingExpr_)
+    {
+      str_sprintf(buf, "%s::groupingExpr_", displayStr);
+      
+      groupingExpr_->displayContents(space, mode, buf, flag);
+    }
+
+  if (perrecExpr_)
+    {
+      str_sprintf(buf, "%s::perrecExpr_", displayStr);
+
+      perrecExpr_->displayContents(space, mode, buf, flag);
+    }
 
-  ex_expr::displayContents(space, mode, buf, flag);
 }
 
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_expr.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_expr.h b/core/sql/exp/exp_expr.h
index 022f999..b66b5a7 100644
--- a/core/sql/exp/exp_expr.h
+++ b/core/sql/exp/exp_expr.h
@@ -896,14 +896,15 @@ class SQLEXP_LIB_FUNC  AggrExpr : public ex_expr {
   ExExprPtr perrecExpr_;        // 08-15
   ExExprPtr finalNullExpr_;     // 16-23
   ExExprPtr finalExpr_;         // 24-31
-  UInt32    flags_;             // 32-35
+  ExExprPtr groupingExpr_;      // 32-39
+  UInt32    flags_;             // 40-43
 
   // ---------------------------------------------------------------------
   // Fillers for potential future extensions without changing class size.
   // When a new member is added, size of this filler should be reduced so
   // that the size of the object remains the same (and is modulo 8).
   // ---------------------------------------------------------------------
-  char                   fillers_[12];  // 36-47
+  char                   fillers_[12];  // 44-55
 
 public:
 NA_EIDPROC
@@ -913,28 +914,36 @@ NA_EIDPROC
   AggrExpr( ex_expr * init_expr, 
 	    ex_expr * perrec_expr, 
 	    ex_expr * final_expr, 
-	    ex_expr * final_null_expr
+	    ex_expr * final_null_expr,
+            ex_expr * grouping_expr
 	  )
-    : ex_expr(ex_expr::exp_AGGR), 
-      initExpr_(init_expr),
-      perrecExpr_(perrec_expr), 
-      finalExpr_(final_expr),
-      finalNullExpr_(final_null_expr),
-      flags_(0)
-  {}
-
+  : ex_expr(ex_expr::exp_AGGR), 
+  initExpr_(init_expr),
+  perrecExpr_(perrec_expr), 
+  finalExpr_(final_expr),
+  finalNullExpr_(final_null_expr),
+  groupingExpr_(grouping_expr),
+  flags_(0)
+    {}
+ 
 NA_EIDPROC ~AggrExpr() {}
 
+ ex_expr * perrecExpr()   { return perrecExpr_;}
+ ex_expr * groupingExpr() { return groupingExpr_;}
+
 NA_EIDPROC
   exp_return_type initializeAggr(atp_struct *);
 NA_EIDPROC
   exp_return_type finalizeAggr(atp_struct *);
 NA_EIDPROC
   exp_return_type finalizeNullAggr(atp_struct *);
+
+ exp_return_type evalGroupingForNull(Int16 startEntry, Int16 endEntry);
+
 NA_EIDPROC
-  Long pack(void *);
+  virtual Long pack(void *);
 NA_EIDPROC
-  Lng32 unpack(void *, void * reallocator);
+  virtual Lng32 unpack(void *, void * reallocator);
 NA_EIDPROC
   virtual exp_return_type fixup(Lng32, unsigned short, const ex_tcb * tcb,
 				ComSpace * space = 0,

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/exp/exp_fixup.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_fixup.cpp b/core/sql/exp/exp_fixup.cpp
index ed39d1e..801e109 100644
--- a/core/sql/exp/exp_fixup.cpp
+++ b/core/sql/exp/exp_fixup.cpp
@@ -307,6 +307,13 @@ ex_expr::exp_return_type AggrExpr::fixup(Lng32 base, unsigned short mode,
 	return retcode;
     }
 
+  if (groupingExpr_)
+    {
+      if ((retcode = groupingExpr_->fixup(base, mode, tcb, space, 
+                                          exHeap, computeSpaceOnly, glob)) != EXPR_OK)
+	return retcode;
+    }
+
   // fixup the expression to do perrec aggregate evaluation.
   if ((retcode = ex_expr::fixup(base, mode, tcb, space, 
 				exHeap, computeSpaceOnly, glob)) != EXPR_OK)

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/generator/GenExpGenerator.cpp
----------------------------------------------------------------------
diff --git a/core/sql/generator/GenExpGenerator.cpp b/core/sql/generator/GenExpGenerator.cpp
index a7ff97f..4f66c8e 100644
--- a/core/sql/generator/GenExpGenerator.cpp
+++ b/core/sql/generator/GenExpGenerator.cpp
@@ -1249,7 +1249,7 @@ short ExpGenerator::generateAggrExpr(const ValueIdSet &val_id_set,
 				     ex_expr ** expr,
 				     short gen_last_clause,
 				     NABoolean groupByOperation,
-                                     const ValueIdSet *addedInitSet /*optional*/
+                                     const ValueIdSet *addedInitSet/*optional*/
                                      )
 {
   ///////////////////////////////////////////////////////////////////////
@@ -1946,14 +1946,48 @@ short ExpGenerator::generateAggrExpr(const ValueIdSet &val_id_set,
 
     }
 
-  initExprGen();
+  // generate expressions to evaluate GROUPING clause
+  ValueIdSet groupingValIdSet;
+  for (val_id = val_id_set.init(); 
+       val_id_set.next(val_id); 
+       val_id_set.advance(val_id))
+    {
+      ItemExpr * item_expr = val_id.getValueDesc()->getItemExpr();
+      if (item_expr->getOperatorType() == ITM_GROUPING)
+        {
+          generator->getMapInfo(val_id)->codeGenerated();
+          Aggregate * ag = (Aggregate*)item_expr;
+          ItemExpr * groupingExpr = 
+            new(wHeap()) AggrGrouping(ag->getRollupGroupIndex());
+	  groupingExpr->bindNode(generator->getBindWA());
+	  groupingValIdSet.insert(groupingExpr->getValueId());
+          
+          Attributes * attr = generator->getMapInfo(val_id)->getAttr();
+          map_info = generator->addMapInfo(groupingExpr->getValueId(), attr);
+          (map_info->getAttr())->copyLocationAttrs(attr);
+        }
+    } // for
+
+  ex_expr * grouping_expr = NULL;
+  if (NOT groupingValIdSet.isEmpty())
+    {
+      unsigned short pcm = getPCodeMode();
+      setPCodeMode(ex_expr::PCODE_NONE);
+
+      generateSetExpr(groupingValIdSet, ex_expr::exp_ARITH_EXPR,
+                      &grouping_expr);
+
+      setPCodeMode(pcm);
+    }
+
+  ex_expr * perrec_expr = NULL;
+  generateSetExpr(*newValIdSet, ex_expr::exp_ARITH_EXPR, &perrec_expr);
+
   *expr = new(getSpace()) AggrExpr(init_expr,
-				   0,//perrec_expr,
+				   perrec_expr,
 				   0, //final_expr,
-				   final_null_expr);
-
-  generateSetExpr(*newValIdSet, ex_expr::exp_ARITH_EXPR, expr,
-                  gen_last_clause);
+				   final_null_expr,
+                                   grouping_expr);
 
   for (CollIndex ns = 0; ns < NullSwitcharooVidList.entries(); ns++)
     {

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/generator/GenItemFunc.cpp
----------------------------------------------------------------------
diff --git a/core/sql/generator/GenItemFunc.cpp b/core/sql/generator/GenItemFunc.cpp
index bf08ca8..81e23ff 100644
--- a/core/sql/generator/GenItemFunc.cpp
+++ b/core/sql/generator/GenItemFunc.cpp
@@ -693,6 +693,30 @@ short AggrMinMax::codeGen(Generator * generator)
   return 0;
 }
 
+short AggrGrouping::codeGen(Generator * generator)
+{
+  Attributes ** attr;
+  
+  Space * space = generator->getSpace();
+  
+  if (generator->getExpGenerator()->genItemExpr(this, &attr, (1 + getArity()), -1) == 1)
+    return 0;
+  
+  GenAssert((rollupGroupIndex_ >= 0), 
+            "AggrGrouping::codeGen. rollupGroupIndex_ must be >= 0.");
+
+  ex_clause * function_clause = 
+    new(generator->getSpace()) ExFunctionGrouping(getOperatorType(),
+                                                  1,
+                                                  attr, 
+                                                  rollupGroupIndex_,
+                                                  generator->getSpace());
+  
+  generator->getExpGenerator()->linkClause(this, function_clause);
+  
+  return 0;
+}
+
 short AnsiUSERFunction::codeGen(Generator * generator)
 {
   Attributes ** attr;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 7804900..ee65f9e 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -4350,7 +4350,8 @@ RelRoot * RelRoot::transformGroupByWithOrdinalPhase2(BindWA *bindWA)
                    (ae->getOperatorType() == ITM_SUM) ||
                    (ae->getOperatorType() == ITM_AVG) ||
                    (ae->getOperatorType() == ITM_COUNT) ||
-                   (ae->getOperatorType() == ITM_COUNT_NONULL)))
+                   (ae->getOperatorType() == ITM_COUNT_NONULL) ||
+                   (ae->getOperatorType() == ITM_GROUPING)))
             {
               *CmpCommon::diags() << DgSqlCode(-4384)
                                   << DgString0("Unsupported rollup aggregate function.");
@@ -4369,7 +4370,54 @@ RelRoot * RelRoot::transformGroupByWithOrdinalPhase2(BindWA *bindWA)
               bindWA->setErrStatus();
               return NULL;
             }
-        }
+
+          // if grouping aggr, find the rollup group it corresponds to.
+          if (ae->getOperatorType() == ITM_GROUPING)
+            {
+              NABoolean found = FALSE;
+              ItemExpr * aggrChild = ae->child(0);
+              int i = 0;
+              while ((NOT found) and (i < grby->rollupGroupExprList().entries()))
+                {
+                  ValueId vid =  grby->rollupGroupExprList()[i];
+                  found =  aggrChild->duplicateMatch(*vid.getItemExpr());
+                  if (found)
+                    ag->setRollupGroupIndex(i);
+                  i++;
+                } // while
+
+              if (NOT found)
+                {
+                  // must find it.
+                  *CmpCommon::diags() << DgSqlCode(-4384)
+                                      << DgString0("GROUPING function can only be specified on a GROUP BY ROLLUP entry.");
+                  
+                  bindWA->setErrStatus();
+                  return NULL;
+                }
+            }
+        } // for
+    }
+  else
+    {
+      // not groupby rollup
+      for (ValueId valId = grby->aggregateExpr().init();
+           grby->aggregateExpr().next(valId);
+           grby->aggregateExpr().advance(valId))
+        {
+          ItemExpr * ae = valId.getItemExpr();
+
+          // grouping can only be specified with 'groupby rollup' clause
+          if (ae->getOperatorType() == ITM_GROUPING)
+            {
+              *CmpCommon::diags() << DgSqlCode(-3242)
+                                  << DgString0("GROUPING function can only be specified with GROUP BY ROLLUP clause.");
+              
+              bindWA->setErrStatus();
+              return NULL;
+            }
+ 
+        } // for
     }
 
   ValueIdSet &groupExpr = grby->groupExpr();

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp
index f48f26d..0b2c260 100644
--- a/core/sql/optimizer/ItemExpr.cpp
+++ b/core/sql/optimizer/ItemExpr.cpp
@@ -6204,6 +6204,9 @@ const NAString Aggregate::getText() const
       else
         result = "count_nonull";
       break;
+    case ITM_GROUPING:
+      result = "grouping";
+      break;
     case ITM_ONE_ROW:
       result = "one_Row";
       break;
@@ -6324,6 +6327,8 @@ ItemExpr * Aggregate::copyTopNode(ItemExpr *derivedNode, CollHeap* outHeap)
   result->frameStart_ = frameStart_;
   result->frameEnd_ = frameEnd_;
 
+  result->rollupGroupIndex_ = rollupGroupIndex_;
+
   return ItemExpr::copyTopNode(result, outHeap);
 }
 
@@ -6336,6 +6341,7 @@ NABoolean Aggregate::isSensitiveToDuplicates() const
     case ITM_MIN:
     case ITM_ANY_TRUE:
     case ITM_ONE_TRUE:
+    case ITM_GROUPING:
       return FALSE;
 
     case ITM_SUM:
@@ -6545,6 +6551,7 @@ ItemExpr * Aggregate::rewriteForStagedEvaluation(ValueIdList &initialAggrs,
     case ITM_SUM:
     case ITM_ANY_TRUE:
     case ITM_ONEROW:
+    case ITM_GROUPING:
       // in these cases, just do the same aggregate function twice
       partial = new (CmpCommon::statementHeap())
                      Aggregate(getOperatorType(), child(0));
@@ -6552,6 +6559,12 @@ ItemExpr * Aggregate::rewriteForStagedEvaluation(ValueIdList &initialAggrs,
       result = new (CmpCommon::statementHeap())
                     Aggregate(getOperatorType(), partial);
 
+      if (getOperatorType() == ITM_GROUPING)
+        {
+          ((Aggregate *)partial)->setRollupGroupIndex(getRollupGroupIndex());
+          ((Aggregate *)result)->setRollupGroupIndex(getRollupGroupIndex());
+        }
+
       if (inScalarGroupBy())
       {
         partial->setInScalarGroupBy();
@@ -7721,6 +7734,8 @@ const NAString BuiltinFunction::getText() const
     case ITM_LOBLOAD:
       return "lobload";
     
+    case ITM_AGGR_GROUPING_FUNC:
+      return "aggr_grouping";
 
     default:
       return "unknown func";
@@ -11790,6 +11805,23 @@ ItemExpr * AggrMinMax::copyTopNode(ItemExpr *derivedNode, CollHeap* outHeap)
   return BuiltinFunction::copyTopNode(result, outHeap);
 }
 
+// --------------------------------------------------------------
+// member functions for AggrGrouping function
+// --------------------------------------------------------------
+ItemExpr * AggrGrouping::copyTopNode(ItemExpr * derivedNode, CollHeap* outHeap)
+{
+  AggrGrouping *result;
+
+  if (derivedNode == NULL)
+    result = new (outHeap) AggrGrouping(-1);
+  else
+    result = (AggrGrouping*)derivedNode;
+
+  result->rollupGroupIndex_ = rollupGroupIndex_;
+
+  return BuiltinFunction::copyTopNode(result, outHeap);
+}
+
 // -----------------------------------------------------------------------
 // member functions for Exists subquery operators
 // -----------------------------------------------------------------------

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h
index a922fcd..c41fe16 100644
--- a/core/sql/optimizer/ItemFunc.h
+++ b/core/sql/optimizer/ItemFunc.h
@@ -116,7 +116,8 @@ public:
       olapPartitionBy_(NULL),
       olapOrderBy_(NULL),
       frameStart_(-INT_MAX),
-      frameEnd_(INT_MAX)
+      frameEnd_(INT_MAX),
+      rollupGroupIndex_(-1)
     { setOrigOpType(otypeSpecifiedByUser); }
   Aggregate(OperatorTypeEnum otype,
 	    ItemExpr *child0 = NULL,
@@ -133,7 +134,8 @@ public:
       olapPartitionBy_(NULL),
       olapOrderBy_(NULL),
       frameStart_(-INT_MAX),
-      frameEnd_(INT_MAX)
+      frameEnd_(INT_MAX),
+      rollupGroupIndex_(-1)
     {}
   Aggregate(OperatorTypeEnum otype,
 	    ItemExpr *child0,
@@ -151,7 +153,8 @@ public:
       olapPartitionBy_(NULL),
       olapOrderBy_(NULL),
       frameStart_(-INT_MAX),
-      frameEnd_(INT_MAX)
+      frameEnd_(INT_MAX),
+      rollupGroupIndex_(-1)
     {}
 
   // virtual destructor
@@ -329,7 +332,8 @@ public:
 
   virtual QR::ExprElement getQRExprElem() const;
 
-
+  void setRollupGroupIndex(Int16 v) { rollupGroupIndex_ = v; }
+  Int16 getRollupGroupIndex() { return rollupGroupIndex_; }
 
 private:
 
@@ -383,6 +387,10 @@ private:
 
   // true iff am top part of a rewritten aggregate
   NABoolean amTopPartOfAggr_;
+
+  // this field indicates the index into rollupGroupExprList of GroupByAgg
+  // that corresponds to the child of GROUPING aggr.
+  Int16 rollupGroupIndex_;
 }; // class Aggregate
 
 
@@ -996,6 +1004,34 @@ public:
 
 }; // class AggrMinMax
 
+/////////////////////////////////////////////////////////////////
+// This function is created by generator and is used to
+// evaluate GROUPING function.
+/////////////////////////////////////////////////////////////////
+class AggrGrouping : public BuiltinFunction
+{
+public:
+  AggrGrouping(Int16 rollupGroupIndex = -1)
+       : BuiltinFunction(ITM_AGGR_GROUPING_FUNC, CmpCommon::statementHeap()),
+         rollupGroupIndex_(rollupGroupIndex)
+  {}
+
+  // a virtual function for type propagating the node
+  virtual const NAType * synthesizeType();
+
+  // method to do code generation
+  virtual short codeGen(Generator*);
+
+  virtual ItemExpr * copyTopNode(ItemExpr *derivedNode = NULL,
+				 CollHeap* outHeap = 0);
+
+private:
+  // this field indicates the index into rollupGroupExprList of GroupByAgg
+  // that corresponds to the child of GROUPING aggr.
+  Int16 rollupGroupIndex_;
+
+}; // class AggrGrouping
+
 class AnsiUSERFunction : public BuiltinFunction
 {
 public:

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/optimizer/SynthType.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/SynthType.cpp b/core/sql/optimizer/SynthType.cpp
index 477e2cc..c0cca73 100644
--- a/core/sql/optimizer/SynthType.cpp
+++ b/core/sql/optimizer/SynthType.cpp
@@ -1499,6 +1499,16 @@ const NAType *Aggregate::synthesizeType()
       result = operand.newCopy(HEAP);
     break;
   }
+
+  case ITM_GROUPING:
+    {
+	  // grouping result is an unsigned int (32 bit)
+      result = new HEAP
+        SQLInt(FALSE /*unsigned*/,
+               FALSE /*not null*/);
+    }
+  break;
+
   case ITM_ONE_ROW:
   case ITM_ONEROW:  
   {
@@ -1534,13 +1544,23 @@ const NAType *AggrMinMax::synthesizeType()
   const NAType *result;
 
   const NAType& operand = child(0)->castToItemExpr()->getValueId().getType();
-  //    result = operand.synthesizeNullableType(HEAP);
   result = operand.newCopy(HEAP);
 
   return result;
 }
 
 // -----------------------------------------------------------------------
+// member functions for class AggGrouping
+// -----------------------------------------------------------------------
+const NAType *AggrGrouping::synthesizeType()
+{
+  // result unsigned 32 bit integer
+  const NAType *result = new HEAP SQLInt(FALSE, FALSE);
+
+  return result;
+}
+
+// -----------------------------------------------------------------------
 // member functions for class PivotGroup
 // -----------------------------------------------------------------------
 const NAType *PivotGroup::synthesizeType()

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index 8bd9a0b..e7dcd49 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -7334,6 +7334,7 @@ set_function_type :   TOK_AVG 		{ $$ = ITM_AVG; }
                     | TOK_COUNT 	{ $$ = ITM_COUNT; }
                     | TOK_VARIANCE 	{ $$ = ITM_VARIANCE; }
                     | TOK_STDDEV 	{ $$ = ITM_STDDEV; }
+                    | TOK_GROUPING 	{ $$ = ITM_GROUPING; }
 
 concat_options : empty
                        {

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/regress/seabase/EXPECTED033
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED033 b/core/sql/regress/seabase/EXPECTED033
index 754855c..66a0409 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) from t033t1 group by rollup (a);
+>>select a, sum(b), grouping(a) from t033t1 group by rollup (a);
 
-A            (EXPR)              
------------  --------------------
+A            (EXPR)                (EXPR)    
+-----------  --------------------  ----------
 
-          ?                     ?
+          ?                     ?           1
 
 --- 1 row(s) selected.
 >>select b+1, sum(b) from t033t1 group by rollup (b);
@@ -50,7 +50,8 @@ A            B            C            D
 
 --- 6 row(s) selected.
 >>
->>explain options 'f' select a,b,c,sum(d) from t033t1 
+>>explain options 'f' select a,b,c,sum(d),grouping(a),grouping(b),grouping(c) 
++>                 from t033t1 
 +>                 group by rollup(a,b,c) order by 1,2,3;
 
 LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
@@ -63,60 +64,76 @@ LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
 .    .    1    trafodion_scan                  T033T1                1.00E+002
 
 --- SQL operation complete.
->>select 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;
-
-A            B            C            (EXPR)       (EXPR)       (EXPR)                (EXPR)                (EXPR)                (EXPR)                (EXPR)
------------  -----------  -----------  -----------  -----------  --------------------  --------------------  --------------------  --------------------  --------------------
-
-          1            2            3            4            4                     4                     4                     1                     1                     1
-          1            2            ?            4            4                     4                     4                     1                     1                     1
-          1            3            3            3            3                     3                     3                     1                     1                     1
-          1            3            ?            3            3                     3                     3                     1                     1                     1
-          1            ?            ?            3            4                     7                     3                     2                     2                     2
-          2            3            4            5            5                     5                     5                     1                     1                     1
-          2            3            ?            5            5                     5                     5                     1                     1                     1
-          2            ?            ?            5            5                     5                     5                     1                     1                     1
-          3            3            3            3            3                     3                     3                     1                     1                     1
-          3            3            4            5            5                     5                     5                     1                     1                     1
-          3            3            ?            3            5                     8                     4                     2                     2                     2
-          3            ?            ?            3            5                     8                     4                     2                     2                     2
-          ?            ?            ?            3            6                    26                     4                     6                     6                     5
-          ?            ?            ?            6            6                     6                     6                     1                     1                     0
-          ?            ?            ?            6            6                     6                     6                     1                     1                     0
-          ?            ?            ?            6            6                     6                     6                     1                     1                     0
+>>select a,b,c,
++>       grouping(a), grouping(b), grouping(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
 
 --- 16 row(s) selected.
 >>
 >>select * from (
-+>select a,b,c,min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1 group by (a,b,c)
++>select a,b,c,
++>       cast(0 as int unsigned),cast(0 as int unsigned),cast(0 as int unsigned),
++>       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
++>       from t033t1 group by (a,b,c)
 +>union all
-+>select a,b,cast(null as int),min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1 group by (a,b)
++>select a,b,cast(null as int),
++>       cast(0 as int unsigned),cast(0 as int unsigned),cast(1 as int unsigned),
++>       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
++>       from t033t1 group by (a,b)
 +>union all
-+>select a,cast(null as int),cast(null as int),min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1 group by (a)
++>select a,cast(null as int),cast(null as int),
++>       cast(0 as int unsigned),cast(1 as int unsigned),cast(1 as int unsigned),
++>       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
++>       from t033t1 group by (a)
 +>union all
-+>select cast(null as int),cast(null as int),cast(null as int),min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1
-+>) x(a,b,c,d,e,f,g,h,i,j)
-+>order by a,b,c,d,e,f,g,h,i,j;
-
-A            B            C            D            E            F                     G                     H                     I                     J
------------  -----------  -----------  -----------  -----------  --------------------  --------------------  --------------------  --------------------  --------------------
-
-          1            2            3            4            4                     4                     4                     1                     1                     1
-          1            2            ?            4            4                     4                     4                     1                     1                     1
-          1            3            3            3            3                     3                     3                     1                     1                     1
-          1            3            ?            3            3                     3                     3                     1                     1                     1
-          1            ?            ?            3            4                     7                     3                     2                     2                     2
-          2            3            4            5            5                     5                     5                     1                     1                     1
-          2            3            ?            5            5                     5                     5                     1                     1                     1
-          2            ?            ?            5            5                     5                     5                     1                     1                     1
-          3            3            3            3            3                     3                     3                     1                     1                     1
-          3            3            4            5            5                     5                     5                     1                     1                     1
-          3            3            ?            3            5                     8                     4                     2                     2                     2
-          3            ?            ?            3            5                     8                     4                     2                     2                     2
-          ?            ?            ?            3            6                    26                     4                     6                     6                     5
-          ?            ?            ?            6            6                     6                     6                     1                     1                     0
-          ?            ?            ?            6            6                     6                     6                     1                     1                     0
-          ?            ?            ?            6            6                     6                     6                     1                     1                     0
++>select cast(null as int),cast(null as int),cast(null as int),
++>       cast(1 as int unsigned),cast(1 as int unsigned),cast(1 as int unsigned),
++>       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
++>       from t033t1
++>) x(a,b,c,d,e,f,g,h,i,j,k,l,m)
++>order by a,b,c,d,e,f,g,h,i,j,k,l,m;
+
+A            B            C            D           E           F           G            H            I                     J                     K                     L                     M
+-----------  -----------  -----------  ----------  ----------  ----------  -----------  -----------  --------------------  --------------------  --------------------  --------------------  --------------------
+
+          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
 
 --- 16 row(s) selected.
 >>
@@ -506,7 +523,8 @@ A
 >>control query shape esp_exchange(cut);
 
 --- SQL operation complete.
->>explain options 'f' select a,b,c,sum(d) from t033t2 
+>>explain options 'f' select a,b,c,sum(d), 
++>        grouping(a), grouping(b), grouping(c) from t033t2 
 +>        group by rollup(a,b,c) order by 1,2,3;
 
 LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
@@ -523,27 +541,29 @@ LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
 .    .    1    trafodion_scan                  T033T2                1.00E+002
 
 --- SQL operation complete.
->>select a,b,c,sum(d) from t033t2 group by rollup(a,b,c) order by 1,2,3;
-
-A            B            C            (EXPR)              
------------  -----------  -----------  --------------------
+>>select a,b,c,sum(d),
++>        grouping(a),grouping(b), grouping(c) from t033t2 
++>        group by rollup(a,b,c) order by 1,2,3;
 
-          1            2            3                     4
-          1            2            ?                     4
-          1            3            3                     3
-          1            3            ?                     3
-          1            ?            ?                     7
-          2            3            4                     5
-          2            3            ?                     5
-          2            ?            ?                     5
-          3            3            3                     3
-          3            3            4                     5
-          3            3            ?                     8
-          3            ?            ?                     8
-          ?            ?            ?                     6
-          ?            ?            ?                     6
-          ?            ?            ?                     6
-          ?            ?            ?                    26
+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
 
 --- 16 row(s) selected.
 >>control query shape cut;
@@ -599,6 +619,55 @@ LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
 
 --- SQL operation complete.
 >>
+>>-- group by rollup and grouping on hive tables
+>>sh echo "drop table t033hive1;" > TEST033_junk;
+>>sh regrhive.ksh -f TEST033_junk;
+>>sh echo "create table t033hive1(a int, b int);" > TEST033_junk;
+>>sh regrhive.ksh -f TEST033_junk;
+>>sh echo "drop table t033hive2;" > TEST033_junk;
+>>sh regrhive.ksh -f TEST033_junk;
+>>sh echo "create table t033hive2(a int, b int);" > TEST033_junk;
+>>sh regrhive.ksh -f TEST033_junk;
+>>insert into hive.hive.t033hive1 values (1,2), (null, null);
+
+--- 2 row(s) inserted.
+>>insert into hive.hive.t033hive2 values (1,2), (null, null);
+
+--- 2 row(s) inserted.
+>>prepare s from select t1.a, t2.b , grouping(t1.a), grouping(t2.b),
++>       grouping(t1.a)+grouping(t2.b) aa  
++>   from hive.hive.t033hive1 t1, hive.hive.t033hive2 t2 
++>   group by rollup (t1.a, t2.b) order by aa;
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
+---- ---- ---- --------------------  --------  --------------------  ---------
+
+6    .    7    root                                                  4.00E+000
+5    .    6    sort                                                  4.00E+000
+4    .    5    sort_groupby_rollup                                   4.00E+000
+3    .    4    sort                                                  1.00E+004
+2    1    3    hybrid_hash_join                                      1.00E+004
+.    .    2    hive_scan                       T033HIVE1             1.00E+002
+.    .    1    hive_scan                       T033HIVE2             1.00E+002
+
+--- SQL operation complete.
+>>execute s;
+
+A            B            (EXPR)      (EXPR)      AA                  
+-----------  -----------  ----------  ----------  --------------------
+
+          1            2           0           0                     0
+          ?            ?           0           0                     0
+          ?            2           0           0                     0
+          1            ?           0           0                     0
+          ?            ?           0           1                     1
+          1            ?           0           1                     1
+          ?            ?           1           1                     2
+
+--- 7 row(s) selected.
 >>
 >>-- error cases. Not allowed or currently not supported
 >>select a,a,sum(b) from t033t1 group by rollup (a,a);
@@ -614,4 +683,29 @@ LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
 *** ERROR[8822] The statement was not prepared.
 
 >>
+>>select grouping(b) from t033t1;
+
+*** ERROR[3242] This statement is not supported. Reason: GROUPING function can only be specified with GROUP BY ROLLUP clause.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select a,grouping(b) from t033t1 group by (a);
+
+*** ERROR[3242] This statement is not supported. Reason: GROUPING function can only be specified with GROUP BY ROLLUP clause.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select a,grouping(b) from t033t1 group by rollup (a);
+
+*** ERROR[4384] GROUP BY ROLLUP clause not allowed for this statement. Reason: GROUPING function can only be specified on a GROUP BY ROLLUP entry.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select a+1, grouping(a) from t033t1 group by rollup (a+1);
+
+*** ERROR[4384] GROUP BY ROLLUP clause not allowed for this statement. Reason: GROUPING function can only be specified on a GROUP BY ROLLUP entry.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
 >>log;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a992f107/core/sql/regress/seabase/TEST033
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST033 b/core/sql/regress/seabase/TEST033
index 4cd7e48..6d576f4 100644
--- a/core/sql/regress/seabase/TEST033
+++ b/core/sql/regress/seabase/TEST033
@@ -20,7 +20,7 @@
 -- @@@ END COPYRIGHT @@@
 --
 
--- Tests for GROUP BY ROLLUP feature
+-- Tests for GROUP BY ROLLUP and GROUPING feature
 
 log LOG033 clear;
 
@@ -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) from t033t1 group by rollup (a);
+select a, sum(b), grouping(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,20 +39,37 @@ 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;
 
-explain options 'f' select a,b,c,sum(d) from t033t1 
+explain options 'f' select a,b,c,sum(d),grouping(a),grouping(b),grouping(c) 
+                 from t033t1 
                  group by rollup(a,b,c) order by 1,2,3;
-select 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;
+select a,b,c,
+       grouping(a), grouping(b), grouping(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;
 
 select * from (
-select a,b,c,min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1 group by (a,b,c)
+select a,b,c,
+       cast(0 as int unsigned),cast(0 as int unsigned),cast(0 as int unsigned),
+       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
+       from t033t1 group by (a,b,c)
 union all
-select a,b,cast(null as int),min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1 group by (a,b)
+select a,b,cast(null as int),
+       cast(0 as int unsigned),cast(0 as int unsigned),cast(1 as int unsigned),
+       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
+       from t033t1 group by (a,b)
 union all
-select a,cast(null as int),cast(null as int),min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1 group by (a)
+select a,cast(null as int),cast(null as int),
+       cast(0 as int unsigned),cast(1 as int unsigned),cast(1 as int unsigned),
+       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
+       from t033t1 group by (a)
 union all
-select cast(null as int),cast(null as int),cast(null as int),min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) from t033t1
-) x(a,b,c,d,e,f,g,h,i,j)
-order by a,b,c,d,e,f,g,h,i,j;
+select cast(null as int),cast(null as int),cast(null as int),
+       cast(1 as int unsigned),cast(1 as int unsigned),cast(1 as int unsigned),
+       min(d),max(d),sum(d),avg(d),count(d),count(*),count(c) 
+       from t033t1
+) x(a,b,c,d,e,f,g,h,i,j,k,l,m)
+order by a,b,c,d,e,f,g,h,i,j,k,l,m;
 
 select b,c,a,sum(d) from t033t1 group by rollup(b,c,a) order by 1,2,3;
 
@@ -135,9 +152,12 @@ create table t033t2 (z int not null primary key,
 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) from t033t2 
+explain options 'f' select a,b,c,sum(d), 
+        grouping(a), grouping(b), grouping(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 
         group by rollup(a,b,c) order by 1,2,3;
-select a,b,c,sum(d) from t033t2 group by rollup(a,b,c) order by 1,2,3;
 control query shape cut;
 
 -- do not eliminate sort groupby rollup on primary keys
@@ -151,11 +171,33 @@ execute s;
 prepare s from select a, sum(b) from t033t3 group by (a);
 explain options 'f' s;
 
+-- group by rollup and grouping on hive tables
+sh echo "drop table t033hive1;" > TEST033_junk;
+sh regrhive.ksh -f TEST033_junk;
+sh echo "create table t033hive1(a int, b int);" > TEST033_junk;
+sh regrhive.ksh -f TEST033_junk;
+sh echo "drop table t033hive2;" > TEST033_junk;
+sh regrhive.ksh -f TEST033_junk;
+sh echo "create table t033hive2(a int, b int);" > TEST033_junk;
+sh regrhive.ksh -f TEST033_junk;
+insert into hive.hive.t033hive1 values (1,2), (null, null);
+insert into hive.hive.t033hive2 values (1,2), (null, null);
+prepare s from select t1.a, t2.b , grouping(t1.a), grouping(t2.b),
+       grouping(t1.a)+grouping(t2.b) aa  
+   from hive.hive.t033hive1 t1, hive.hive.t033hive2 t2 
+   group by rollup (t1.a, t2.b) order by aa;
+explain options 'f' s;
+execute s;
 
 -- error cases. Not allowed or currently not supported
 select a,a,sum(b) from t033t1 group by rollup (a,a);
 select d, sum(b) from t033t1 group by rollup (d);
 
+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);
+
 log;
 
 


Mime
View raw message