calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject incubator-calcite git commit: Add GROUPING function
Date Fri, 21 Nov 2014 06:10:40 GMT
Repository: incubator-calcite
Updated Branches:
  refs/heads/master 911840a75 -> 66867b995


Add GROUPING function


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

Branch: refs/heads/master
Commit: 66867b9953e0065113ee3f8ba5c08819e68e76a5
Parents: 911840a
Author: Julian Hyde <jhyde@apache.org>
Authored: Thu Nov 20 20:26:49 2014 -0800
Committer: Julian Hyde <jhyde@apache.org>
Committed: Thu Nov 20 20:26:49 2014 -0800

----------------------------------------------------------------------
 core/src/main/codegen/templates/Parser.jj       |  1 +
 .../org/apache/calcite/rel/core/Aggregate.java  |  2 +-
 .../apache/calcite/runtime/CalciteResource.java |  9 ++
 .../java/org/apache/calcite/sql/SqlKind.java    |  5 +-
 .../org/apache/calcite/sql/SqlRankFunction.java | 15 ----
 .../calcite/sql/fun/SqlGroupingFunction.java    | 75 ++++++++++++++++
 .../calcite/sql/fun/SqlStdOperatorTable.java    |  7 ++
 .../sql/validate/AggregatingSelectScope.java    | 15 ++++
 .../calcite/sql/validate/SqlValidatorImpl.java  |  1 +
 .../calcite/sql/validate/SqlValidatorUtil.java  | 11 +++
 .../calcite/sql2rel/SqlToRelConverter.java      | 15 ++++
 .../calcite/runtime/CalciteResource.properties  |  3 +
 .../calcite/sql/parser/SqlParserTest.java       |  8 ++
 .../apache/calcite/sql/test/SqlAdvisorTest.java |  1 +
 .../calcite/test/SqlToRelConverterTest.java     | 26 ++++++
 .../apache/calcite/test/SqlValidatorTest.java   | 94 ++++++++++++++++++--
 .../calcite/test/SqlToRelConverterTest.xml      | 66 ++++++++++++--
 core/src/test/resources/sql/agg.oq              | 56 ++++++++++++
 18 files changed, 378 insertions(+), 32 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/codegen/templates/Parser.jj
----------------------------------------------------------------------
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 4776cdf..e636e87 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -4197,6 +4197,7 @@ SqlIdentifier ReservedFunctionName() :
         | <EXP>
         | <FIRST_VALUE>
         | <FUSION>
+        | <GROUPING>
         | <LAST_VALUE>
         | <LN>
         | <LOCALTIME>

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java b/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
index 116569a..f6d7c8f 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
@@ -272,7 +272,7 @@ public abstract class Aggregate extends SingleRel {
         final RelDataType booleanType =
             typeFactory.createTypeWithNullability(
                 typeFactory.createSqlType(SqlTypeName.BOOLEAN), false);
-        builder.add("i$" + fieldList.get(groupKey), booleanType);
+        builder.add("i$" + fieldList.get(groupKey).getName(), booleanType);
       }
     }
     for (Ord<AggregateCall> aggCall : Ord.zip(aggCalls)) {

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 8b49f65..86de3b1 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -249,6 +249,15 @@ public interface CalciteResource {
   @BaseMessage("Expression ''{0}'' is not being grouped")
   ExInst<SqlValidatorException> notGroupExpr(String a0);
 
+  @BaseMessage("Argument to GROUPING operator must be a grouped expression")
+  ExInst<SqlValidatorException> groupingArgument();
+
+  @BaseMessage("GROUPING operator may only occur in an aggregate query")
+  ExInst<SqlValidatorException> groupingInAggregate();
+
+  @BaseMessage("GROUPING operator may only occur in SELECT, HAVING or ORDER BY clause")
+  ExInst<SqlValidatorException> groupingInWrongClause();
+
   @BaseMessage("Expression ''{0}'' is not in the select clause")
   ExInst<SqlValidatorException> notSelectDistinctExpr(String a0);
 

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql/SqlKind.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 6c6fd85..2011223 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -575,7 +575,10 @@ public enum SqlKind {
 
   /** The internal {@code GROUPING SETS} operator that occurs within a
    * {@code GROUP BY} clause. */
-  GROUPING_SETS;
+  GROUPING_SETS,
+
+  /** The internal {@code GROUPING} function. */
+  GROUPING;
 
   //~ Static fields/initializers ---------------------------------------------
 

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
index e157671..85712d3 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java
@@ -18,18 +18,13 @@ package org.apache.calcite.sql;
 
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeFactory;
-import org.apache.calcite.sql.parser.SqlParserPos;
 import org.apache.calcite.sql.type.OperandTypes;
 import org.apache.calcite.sql.type.ReturnTypes;
-import org.apache.calcite.sql.validate.SqlValidator;
-import org.apache.calcite.sql.validate.SqlValidatorScope;
 
 import com.google.common.collect.ImmutableList;
 
 import java.util.List;
 
-import static org.apache.calcite.util.Static.RESOURCE;
-
 /**
  * Operator which aggregates sets of values into a result.
  */
@@ -67,16 +62,6 @@ public class SqlRankFunction extends SqlAggFunction {
   public List<RelDataType> getParameterTypes(RelDataTypeFactory typeFactory) {
     return ImmutableList.of(type);
   }
-
-  public void validateCall(
-      SqlCall call,
-      SqlValidator validator,
-      SqlValidatorScope scope,
-      SqlValidatorScope operandScope) {
-    final SqlParserPos pos = call.getParserPosition();
-    throw SqlUtil.newContextException(pos,
-        RESOURCE.functionUndefined(call.toString()));
-  }
 }
 
 // End SqlRankFunction.java

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java
new file mode 100644
index 0000000..ce1591d
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java
@@ -0,0 +1,75 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.fun;
+
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlSelect;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.validate.AggregatingSelectScope;
+import org.apache.calcite.sql.validate.OrderByScope;
+import org.apache.calcite.sql.validate.SelectScope;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
+import org.apache.calcite.util.Static;
+
+/**
+ * The {@code GROUPING} function.
+ */
+class SqlGroupingFunction extends SqlFunction {
+  public SqlGroupingFunction() {
+    super("GROUPING", SqlKind.GROUPING, ReturnTypes.INTEGER, null,
+        OperandTypes.ANY, SqlFunctionCategory.SYSTEM);
+  }
+
+  @Override public void validateCall(SqlCall call, SqlValidator validator,
+      SqlValidatorScope scope, SqlValidatorScope operandScope) {
+    super.validateCall(call, validator, scope, operandScope);
+    final SelectScope selectScope =
+        SqlValidatorUtil.getEnclosingSelectScope(scope);
+    final SqlSelect select = selectScope.getNode();
+    if (!validator.isAggregate(select)) {
+      throw validator.newValidationError(call,
+          Static.RESOURCE.groupingInAggregate());
+    }
+    final AggregatingSelectScope aggregatingSelectScope =
+        SqlValidatorUtil.getEnclosingAggregateSelectScope(scope);
+    if (aggregatingSelectScope == null) {
+      // We're probably in the GROUP BY clause
+      throw validator.newValidationError(call,
+          Static.RESOURCE.groupingInWrongClause());
+    }
+    for (SqlNode operand : call.getOperandList()) {
+      if (scope instanceof OrderByScope) {
+        operand = validator.expandOrderExpr(select, operand);
+      } else {
+        operand = validator.expand(operand, scope);
+      }
+      if (!aggregatingSelectScope.isGroupingExpr(operand)) {
+        throw validator.newValidationError(operand,
+            Static.RESOURCE.groupingArgument());
+      }
+    }
+  }
+}
+
+// End SqlGroupingFunction.java

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 905b7ae..c27c876 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -163,6 +163,12 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
   public static final SqlInternalOperator GROUPING_SETS =
       new SqlInternalOperator("GROUPING_SETS", SqlKind.GROUPING_SETS);
 
+  /** {@code GROUPING} function. Occurs in similar places to an aggregate
+   * function ({@code SELECT}, {@code HAVING} clause, etc. of an aggregate
+   * query), but not technically an aggregate function. */
+  public static final SqlGroupingFunction GROUPING =
+      new SqlGroupingFunction();
+
   /**
    * String concatenation operator, '<code>||</code>'.
    */
@@ -1570,6 +1576,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable
{
     }
     return instance;
   }
+
 }
 
 // End SqlStdOperatorTable.java

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
b/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
index 94ee878..4584d6f 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AggregatingSelectScope.java
@@ -234,6 +234,21 @@ public class AggregatingSelectScope
   public void validateExpr(SqlNode expr) {
     checkAggregateExpr(expr, true);
   }
+
+  /** Returns whether a given expression is equal to one of the grouping
+   * expressions. Determines whether it is valid as an operand to GROUPING. */
+  public boolean isGroupingExpr(SqlNode operand) {
+    return lookupGroupingExpr(operand) >= 0;
+  }
+
+  public int lookupGroupingExpr(SqlNode operand) {
+    for (Ord<SqlNode> groupExpr : Ord.zip(groupExprList)) {
+      if (operand.equalsDeep(groupExpr.e, false)) {
+        return groupExpr.i;
+      }
+    }
+    return -1;
+  }
 }
 
 // End AggregatingSelectScope.java

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 55c1678..a02d86a 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -3729,6 +3729,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
     targetWindow.setWindowCall(call);
     targetWindow.validate(this, scope);
     targetWindow.setWindowCall(null);
+    call.validate(this, scope);
   }
 
   public void validateAggregateParams(

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
index 09c3e67..3f694e4 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
@@ -304,6 +304,17 @@ public class SqlValidatorUtil {
     return null;
   }
 
+  public static AggregatingSelectScope
+  getEnclosingAggregateSelectScope(SqlValidatorScope scope) {
+    while (scope instanceof DelegatingScope) {
+      if (scope instanceof AggregatingSelectScope) {
+        return (AggregatingSelectScope) scope;
+      }
+      scope = ((DelegatingScope) scope).getParent();
+    }
+    return null;
+  }
+
   /**
    * Derives the list of column names suitable for NATURAL JOIN. These are the
    * columns that occur exactly once on each side of the join.

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 9d6d5bb..36453e0 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -4607,6 +4607,21 @@ public class SqlToRelConverter {
       // assert call.getOperator().isAggregator();
       assert bb.agg == this;
 
+      switch (call.getKind()) {
+      case GROUPING:
+        if (aggregatingSelectScope.indicator) {
+          final int x = lookupGroupExpr(call.getOperandList().get(0));
+          if (x >= 0) {
+            return rexBuilder.makeCall(SqlStdOperatorTable.CASE,
+                rexBuilder.makeInputRef(bb.root,
+                    aggregatingSelectScope.groupExprList.size() + x),
+                rexBuilder.makeExactLiteral(BigDecimal.ONE),
+                rexBuilder.makeExactLiteral(BigDecimal.ZERO));
+          }
+        } else {
+          return rexBuilder.makeExactLiteral(BigDecimal.ONE);
+        }
+      }
       return aggMapping.get(call);
     }
 

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
----------------------------------------------------------------------
diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index b2c6b0a..4562792 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -86,6 +86,9 @@ ColumnInUsingNotUnique=Column name ''{0}'' in USING clause is not unique
on one
 NaturalOrUsingColumnNotCompatible=Column ''{0}'' matched using NATURAL keyword or USING clause
has incompatible types: cannot compare ''{1}'' to ''{2}''
 WindowNotFound=Window ''{0}'' not found
 NotGroupExpr=Expression ''{0}'' is not being grouped
+GroupingArgument=Argument to GROUPING operator must be a grouped expression
+GroupingInAggregate=GROUPING operator may only occur in an aggregate query
+GroupingInWrongClause=GROUPING operator may only occur in SELECT, HAVING or ORDER BY clause
 NotSelectDistinctExpr=Expression ''{0}'' is not in the select clause
 AggregateIllegalInClause=Aggregate expression is illegal in {0} clause
 WindowedAggregateIllegalInClause=Windowed aggregate expression is illegal in {0} clause

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 3f754ba..9cd6eef 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -831,6 +831,14 @@ public class SqlParserTest {
         .fails("(?s)Encountered \", rollup\" at .*");
   }
 
+  @Test public void testGrouping() {
+    sql("select deptno, grouping(deptno) from emp\n"
+        + "group by grouping sets (deptno, (deptno, gender), ())")
+        .ok("SELECT `DEPTNO`, (GROUPING(`DEPTNO`))\n"
+            + "FROM `EMP`\n"
+            + "GROUP BY (GROUPING_SETS(`DEPTNO`, (ROW(`DEPTNO`, `GENDER`)),))");
+  }
+
   @Test public void testWith() {
     check(
         "with femaleEmps as (select * from emps where gender = 'F')"

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index ad86773..f1bf067 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -144,6 +144,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
           "KEYWORD(FIRST_VALUE)",
           "KEYWORD(FLOOR)",
           "KEYWORD(FUSION)",
+          "KEYWORD(GROUPING)",
           "KEYWORD(INTERVAL)",
           "KEYWORD(LAST_VALUE)",
           "KEYWORD(LN)",

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 9e597c6..15f09ee 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -215,6 +215,32 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
         + "order by 2").ok();
   }
 
+  @Test public void testGroupingSetsProduct() {
+    // Example in SQL:2011:
+    //   GROUP BY GROUPING SETS ((A, B), (C)), GROUPING SETS ((X, Y), ())
+    // is transformed to
+    //   GROUP BY GROUPING SETS ((A, B, X, Y), (A, B), (C, X, Y), (C))
+    sql("select 1\n"
+        + "from (values (0, 1, 2, 3, 4)) as t(a, b, c, x, y)\n"
+        + "group by grouping sets ((a, b), c), grouping sets ((x, y), ())")
+        .ok();
+  }
+
+  /** When the GROUPING function occurs with GROUP BY (effectively just one
+   * grouping set), we can translate it directly to 1. */
+  @Test public void testGroupingFunctionWithGroupBy() {
+    sql("select deptno, grouping(deptno), count(*), grouping(empno)\n"
+        + "from emp\n"
+        + "group by empno, deptno\n"
+        + "order by 2").ok();
+  }
+
+  @Test public void testGroupingFunction() {
+    sql("select deptno, grouping(deptno), count(*), grouping(empno)\n"
+        + "from emp\n"
+        + "group by rollup(empno, deptno)").ok();
+  }
+
   /**
    * GROUP BY with duplicates
    *

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index fd20341..cc03a80 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -3836,7 +3836,9 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
   }
 
   @Test public void testWindowFunctions2() {
-    List<String> defined = Arrays.asList("RANK", "ROW_NUMBER");
+    List<String> defined =
+        Arrays.asList("CUME_DIST", "DENSE_RANK", "PERCENT_RANK", "RANK",
+            "ROW_NUMBER");
     if (Bug.TODO_FIXED) {
       checkColumnType(
           "select rank() over (order by deptno) from emp",
@@ -3889,9 +3891,18 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
         "select dense_rank() over w from emp window w as (order by empno ^rows^ 2 preceding)")
         .fails("ROW/RANGE not allowed with RANK or DENSE_RANK functions");
     if (defined.contains("PERCENT_RANK")) {
-      winSql(
-          "select percent_rank() over w from emp window w as (rows 2 preceding )")
+      winSql("select percent_rank() over w from emp\n"
+          + "window w as (order by empno)")
           .ok();
+      winSql(
+          "select percent_rank() over w from emp\n"
+          + "window w as (order by empno ^rows^ 2 preceding)")
+          .fails("ROW/RANGE not allowed with RANK or DENSE_RANK functions");
+      winSql(
+          "select percent_rank() over w from emp\n"
+          + "window w as ^(partition by empno)^")
+          .fails(
+              "RANK or DENSE_RANK functions require ORDER BY clause in window specification");
     } else {
       checkWinFuncExpWithWinClause(
           "^percent_rank()^",
@@ -3899,9 +3910,16 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
     }
     if (defined.contains("CUME_DIST")) {
       winSql(
-          "select cume_dist() over w from emp window w as (rows 2 preceding)")
+          "select cume_dist() over w from emp window w as ^(rows 2 preceding)^")
+          .fails(
+              "RANK or DENSE_RANK functions require ORDER BY clause in window specification");
+      winSql(
+          "select cume_dist() over w from emp window w as (order by empno ^rows^ 2 preceding)")
+          .fails("ROW/RANGE not allowed with RANK or DENSE_RANK functions");
+      winSql(
+          "select cume_dist() over w from emp window w as (order by empno)")
           .ok();
-      winSql("select cume_dist() over (rows 2 preceding ) from emp ").ok();
+      winSql("select cume_dist() over (order by empno) from emp ").ok();
     } else {
       checkWinFuncExpWithWinClause(
           "^cume_dist()^",
@@ -3914,7 +3932,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
         "select dense_rank() over (order by empno ^rows^ 2 preceding ) from emp ")
         .fails("ROW/RANGE not allowed with RANK or DENSE_RANK functions");
     if (defined.contains("PERCENT_RANK")) {
-      winSql("select percent_rank() over (rows 2 preceding ) from emp").ok();
+      winSql("select percent_rank() over (order by empno) from emp").ok();
     }
 
     // invalid column reference
@@ -4671,6 +4689,70 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
     return SqlValidatorUtil.cube(ImmutableList.copyOf(sets));
   }
 
+  @Test public void testGrouping() {
+    sql("select deptno, grouping(deptno) from emp group by deptno").ok();
+    sql("select deptno / 2, grouping(deptno / 2),\n"
+        + " ^grouping(deptno / 2, empno)^\n"
+        + "from emp group by deptno / 2, empno")
+        .fails(
+            "Invalid number of arguments to function 'GROUPING'. Was expecting 1 arguments");
+    sql("select deptno, grouping(^empno^) from emp group by deptno")
+        .fails("Expression 'EMPNO' is not being grouped");
+    sql("select deptno, grouping(^deptno + 1^) from emp group by deptno")
+        .fails("Argument to GROUPING operator must be a grouped expression");
+    sql("select deptno, grouping(emp.^xxx^) from emp")
+        .fails("Column 'XXX' not found in table 'EMP'");
+    sql("select deptno, ^grouping(deptno)^ from emp")
+        .fails("GROUPING operator may only occur in an aggregate query");
+    sql("select deptno, sum(^grouping(deptno)^) over () from emp")
+        .fails("GROUPING operator may only occur in an aggregate query");
+    sql("select deptno from emp group by deptno having grouping(deptno) < 5")
+        .ok();
+    sql("select deptno from emp group by deptno order by grouping(deptno)")
+        .ok();
+    sql("select deptno as xx from emp group by deptno order by grouping(xx)")
+        .ok();
+    sql("select deptno as empno from emp\n"
+        + "group by deptno order by grouping(empno)")
+        .ok();
+    sql("select 1 as deptno from emp\n"
+        + "group by deptno order by grouping(^deptno^)")
+        .fails("Argument to GROUPING operator must be a grouped expression");
+    sql("select deptno from emp group by deptno order by grouping(emp.deptno)")
+        .ok();
+    sql("select ^deptno^ from emp group by empno order by grouping(deptno)")
+        .fails("Expression 'DEPTNO' is not being grouped");
+    sql("select deptno from emp order by ^grouping(deptno)^")
+        .fails("GROUPING operator may only occur in an aggregate query");
+    sql("select deptno from emp where ^grouping(deptno)^ = 1")
+        .fails("GROUPING operator may only occur in an aggregate query");
+    sql("select deptno from emp where ^grouping(deptno)^ = 1 group by deptno")
+        .fails(
+             "GROUPING operator may only occur in SELECT, HAVING or ORDER BY clause");
+    sql("select deptno from emp group by deptno, ^grouping(deptno)^")
+        .fails(
+               "GROUPING operator may only occur in SELECT, HAVING or ORDER BY clause");
+    sql("select deptno from emp\n"
+        + "group by grouping sets(deptno, ^grouping(deptno)^)")
+        .fails(
+               "GROUPING operator may only occur in SELECT, HAVING or ORDER BY clause");
+    sql("select deptno from emp\n"
+        + "group by cube(empno, ^grouping(deptno)^)")
+        .fails(
+               "GROUPING operator may only occur in SELECT, HAVING or ORDER BY clause");
+    sql("select deptno from emp\n"
+        + "group by rollup(empno, ^grouping(deptno)^)")
+        .fails(
+               "GROUPING operator may only occur in SELECT, HAVING or ORDER BY clause");
+  }
+
+  @Test public void testCubeGrouping() {
+    sql("select deptno, grouping(deptno) from emp group by cube(deptno)").ok();
+    sql("select deptno, grouping(^deptno + 1^) from emp\n"
+        + "group by cube(deptno, empno)")
+        .fails("Argument to GROUPING operator must be a grouped expression");
+  }
+
   @Test public void testSumInvalidArgs() {
     checkFails(
         "select ^sum(ename)^, deptno from emp group by deptno",

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 513b94f..adefd68 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2024,7 +2024,7 @@ order by 2]]>
             <![CDATA[
 Sort(sort0=[$1], dir0=[ASC])
   LogicalProject(DEPTNO=[$0], ENAME=[$1], EXPR$2=[$4])
-    LogicalProject(DEPTNO=[$0], ENAME=[CASE($3, null, $1)], i$#0: DEPTNO INTEGER=[$2], i$#1:
ENAME VARCHAR(20)=[$3], EXPR$2=[$4])
+    LogicalProject(DEPTNO=[$0], ENAME=[CASE($3, null, $1)], i$DEPTNO=[$2], i$ENAME=[$3],
EXPR$2=[$4])
       LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], indicator=[true], EXPR$2=[SUM($2)])
         LogicalProject(DEPTNO=[$7], ENAME=[$1], SAL=[$5])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -2043,7 +2043,7 @@ group by sal,
         <Resource name="plan">
             <![CDATA[
 LogicalProject(EXPR$0=[$6])
-  LogicalProject(SAL=[$0], DEPTNO=[CASE($4, null, $1)], ENAME=[CASE($5, null, $2)], i$#0:
SAL INTEGER=[$3], i$#1: DEPTNO INTEGER=[$4], i$#2: ENAME VARCHAR(20)=[$5], EXPR$0=[$6])
+  LogicalProject(SAL=[$0], DEPTNO=[CASE($4, null, $1)], ENAME=[CASE($5, null, $2)], i$SAL=[$3],
i$DEPTNO=[$4], i$ENAME=[$5], EXPR$0=[$6])
     LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0, 2}]], indicator=[true],
EXPR$0=[SUM($0)])
       LogicalProject(SAL=[$5], DEPTNO=[$7], ENAME=[$1])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -2058,7 +2058,7 @@ group by grouping sets (a, b), grouping sets (c, d)]]>
         <Resource name="plan">
             <![CDATA[
 LogicalProject(EXPR$0=[1])
-  LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6,
null, $2)], EXPR$3=[CASE($7, null, $3)], i$#0: EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5],
i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+  LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6,
null, $2)], EXPR$3=[CASE($7, null, $3)], i$EXPR$0=[$4], i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7])
     LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 2}, {0, 3}, {1, 2}, {1, 3}]], indicator=[true])
       LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
 ]]>
@@ -2072,7 +2072,7 @@ group by grouping sets (a, (a, b)), grouping sets (c), d]]>
         <Resource name="plan">
             <![CDATA[
 LogicalProject(EXPR$0=[1])
-  LogicalProject(EXPR$0=[$0], EXPR$1=[CASE($5, null, $1)], EXPR$2=[$2], EXPR$3=[$3], i$#0:
EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5], i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+  LogicalProject(EXPR$0=[$0], EXPR$1=[CASE($5, null, $1)], EXPR$2=[$2], EXPR$3=[$3], i$EXPR$0=[$4],
i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7])
     LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 2, 3}]], indicator=[true])
       LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
 ]]>
@@ -2086,7 +2086,7 @@ group by rollup(a, b), rollup(c, d)]]>
         <Resource name="plan">
             <![CDATA[
 LogicalProject(EXPR$0=[1])
-  LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6,
null, $2)], EXPR$3=[CASE($7, null, $3)], i$#0: EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5],
i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+  LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6,
null, $2)], EXPR$3=[CASE($7, null, $3)], i$EXPR$0=[$4], i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7])
     LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0,
2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]], indicator=[true])
       LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
 ]]>
@@ -2100,7 +2100,7 @@ group by cube(a, b)]]>
         <Resource name="plan">
             <![CDATA[
 LogicalProject(EXPR$0=[1])
-  LogicalProject(EXPR$0=[CASE($2, null, $0)], EXPR$1=[CASE($3, null, $1)], i$#0: EXPR$0 INTEGER=[$2],
i$#1: EXPR$1 INTEGER=[$3])
+  LogicalProject(EXPR$0=[CASE($2, null, $0)], EXPR$1=[CASE($3, null, $1)], i$EXPR$0=[$2],
i$EXPR$1=[$3])
     LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]], indicator=[true])
       LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
         LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
@@ -2115,7 +2115,7 @@ group by rollup(b, (a, d))]]>
         <Resource name="plan">
             <![CDATA[
 LogicalProject(EXPR$0=[1])
-  LogicalProject(EXPR$1=[CASE($3, null, $0)], EXPR$0=[CASE($4, null, $1)], EXPR$3=[CASE($5,
null, $2)], i$#0: EXPR$1 INTEGER=[$3], i$#1: EXPR$0 INTEGER=[$4], i$#2: EXPR$3 INTEGER=[$5])
+  LogicalProject(EXPR$1=[CASE($3, null, $0)], EXPR$0=[CASE($4, null, $1)], EXPR$3=[CASE($5,
null, $2)], i$EXPR$1=[$3], i$EXPR$0=[$4], i$EXPR$3=[$5])
     LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0}, {}]], indicator=[true])
       LogicalProject(EXPR$1=[$1], EXPR$0=[$0], EXPR$3=[$3])
         LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
@@ -2131,7 +2131,7 @@ group by rollup(a, b), rollup(c, d)]]>
         <Resource name="plan">
             <![CDATA[
 LogicalProject(EXPR$0=[1])
-  LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6,
null, $2)], EXPR$3=[CASE($7, null, $3)], i$#0: EXPR$0 INTEGER=[$4], i$#1: EXPR$1 INTEGER=[$5],
i$#2: EXPR$2 INTEGER=[$6], i$#3: EXPR$3 INTEGER=[$7])
+  LogicalProject(EXPR$0=[CASE($4, null, $0)], EXPR$1=[CASE($5, null, $1)], EXPR$2=[CASE($6,
null, $2)], EXPR$3=[CASE($7, null, $3)], i$EXPR$0=[$4], i$EXPR$1=[$5], i$EXPR$2=[$6], i$EXPR$3=[$7])
     LogicalAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0,
2, 3}, {0, 2}, {0}, {2, 3}, {2}, {}]], indicator=[true])
       LogicalValues(tuples=[[{ 1, 2, 3, 4 }]])
 ]]>
@@ -2146,7 +2146,7 @@ group by rollup(a, b)]]>
         <Resource name="plan">
             <![CDATA[
 LogicalProject(A=[$0], B=[$1], C=[$4])
-  LogicalProject(A=[CASE($2, null, $0)], B=[CASE($3, null, $1)], i$#0: A INTEGER=[$2], i$#1:
B INTEGER=[$3], C=[$4])
+  LogicalProject(A=[CASE($2, null, $0)], B=[CASE($3, null, $1)], i$A=[$2], i$B=[$3], C=[$4])
     LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], C=[COUNT()])
       LogicalProject(A=[$0], B=[$1])
         LogicalProject(EXPR$0=[null], EXPR$1=[2])
@@ -2167,4 +2167,52 @@ LogicalProject(EXPR$0=[$1])
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testGroupingSetsProduct">
+        <Resource name="sql">
+            <![CDATA[select 1
+from (values (0, 1, 2, 3, 4)) as t(a, b, c, x, y)
+group by grouping sets ((a, b), c), grouping sets ((x, y), ())]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EXPR$0=[1])
+  LogicalProject(EXPR$0=[CASE($5, null, $0)], EXPR$1=[CASE($6, null, $1)], EXPR$2=[CASE($7,
null, $2)], EXPR$3=[CASE($8, null, $3)], EXPR$4=[CASE($9, null, $4)], i$EXPR$0=[$5], i$EXPR$1=[$6],
i$EXPR$2=[$7], i$EXPR$3=[$8], i$EXPR$4=[$9])
+    LogicalAggregate(group=[{0, 1, 2, 3, 4}], groups=[[{0, 1, 3, 4}, {0, 1}, {2, 3, 4}, {2}]],
indicator=[true])
+      LogicalValues(tuples=[[{ 0, 1, 2, 3, 4 }]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testGroupingFunctionWithGroupBy">
+        <Resource name="sql">
+            <![CDATA[select deptno, grouping(deptno), count(*), grouping(empno)
+from emp
+group by empno, deptno
+order by 2]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+Sort(sort0=[$1], dir0=[ASC])
+  LogicalProject(DEPTNO=[$1], EXPR$1=[1], EXPR$2=[$2], EXPR$3=[1])
+    LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
+      LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testGroupingFunction">
+        <Resource name="sql">
+            <![CDATA[select deptno, grouping(deptno), count(*), grouping(empno)
+from emp
+group by rollup(empno, deptno)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$1], EXPR$1=[CASE($3, 1, 0)], EXPR$2=[$4], EXPR$3=[CASE($2, 1, 0)])
+  LogicalProject(EMPNO=[CASE($2, null, $0)], DEPTNO=[CASE($3, null, $1)], i$EMPNO=[$2], i$DEPTNO=[$3],
EXPR$2=[$4])
+    LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], EXPR$2=[COUNT()])
+      LogicalProject(EMPNO=[$0], DEPTNO=[$7])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
 </Root>

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/66867b99/core/src/test/resources/sql/agg.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/agg.oq b/core/src/test/resources/sql/agg.oq
index 0f97176..86a2a5e 100644
--- a/core/src/test/resources/sql/agg.oq
+++ b/core/src/test/resources/sql/agg.oq
@@ -423,4 +423,60 @@ having count(*) > 2 or gender = 'M' and e.deptno = 10;
 
 !ok
 
+# GROUPING in SELECT clause of GROUP BY query
+select count(*) as c, grouping(deptno) as g
+from emp
+group by deptno;
++---+---+
+| C | G |
++---+---+
+| 1 | 1 |
+| 1 | 1 |
+| 1 | 1 |
+| 2 | 1 |
+| 2 | 1 |
+| 2 | 1 |
++---+---+
+(6 rows)
+
+!ok
+
+# GROUPING in ORDER BY clause
+select count(*) as c
+from emp
+group by deptno
+order by grouping(deptno);
++---+
+| C |
++---+
+| 1 |
+| 2 |
+| 1 |
+| 2 |
+| 1 |
+| 2 |
++---+
+(6 rows)
+
+!ok
+
+# GROUPING in SELECT clause of ROLLUP query
+select count(*) as c, deptno, grouping(deptno) as g
+from emp
+group by rollup(deptno);
++---+--------+---+
+| C | DEPTNO | G |
++---+--------+---+
+| 1 |     20 | 0 |
+| 1 |     60 | 0 |
+| 1 |        | 0 |
+| 2 |     10 | 0 |
+| 2 |     30 | 0 |
+| 2 |     50 | 0 |
+| 9 |        | 1 |
++---+--------+---+
+(7 rows)
+
+!ok
+
 # End agg.oq


Mime
View raw message