You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2014/11/21 07:10:40 UTC
incubator-calcite git commit: Add GROUPING function
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 <jh...@apache.org>
Authored: Thu Nov 20 20:26:49 2014 -0800
Committer: Julian Hyde <jh...@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