Repository: calcite Updated Branches: refs/heads/master 316a05872 -> abfcc79bc
[CALCITE-1652] Allow GROUPING to have multiple arguments, like GROUPING_ID Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/abfcc79b Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/abfcc79b Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/abfcc79b Branch: refs/heads/master Commit: abfcc79bcd635ee73791c2d647c3f843d047ee5d Parents: 316a058 Author: Julian Hyde <[email protected]> Authored: Tue Feb 21 13:13:37 2017 -0800 Committer: Julian Hyde <[email protected]> Committed: Wed Feb 22 10:26:06 2017 -0800 ---------------------------------------------------------------------- .../java/org/apache/calcite/sql/SqlKind.java | 7 +++--- .../calcite/sql/fun/SqlGroupingFunction.java | 7 +++--- .../calcite/sql/fun/SqlGroupingIdFunction.java | 5 ++++ .../calcite/sql/fun/SqlStdOperatorTable.java | 17 +++++++++---- .../calcite/sql2rel/SqlToRelConverter.java | 3 --- .../apache/calcite/test/SqlValidatorTest.java | 11 +++++++-- core/src/test/resources/sql/agg.iq | 25 ++++++++++++++++++++ site/_docs/reference.md | 4 ++-- 8 files changed, 62 insertions(+), 17 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/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 e9ecd7d..ec7053a 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -681,13 +681,14 @@ public enum SqlKind { * {@code GROUP BY} clause. */ GROUPING_SETS, - /** The internal {@code GROUPING(e)} function. */ + /** The {@code GROUPING(e, ...)} function. */ GROUPING, - /** The internal {@code GROUPING_ID(e, ...)} function. */ + /** @deprecated Use {@link #GROUPING}. */ + @Deprecated // to be removed before 2.0 GROUPING_ID, - /** The internal {@code GROUP_ID()} function. */ + /** The {@code GROUP_ID()} function. */ GROUP_ID, // Aggregate functions http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/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 index 16e75e0..2559558 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java @@ -25,13 +25,14 @@ import org.apache.calcite.sql.type.ReturnTypes; * The {@code GROUPING} function. * * <p>This function is defined in the SQL standard. + * {@code GROUPING_ID} is a non-standard synonym. * * <p>Some examples are in {@code agg.iq}. */ class SqlGroupingFunction extends SqlAbstractGroupFunction { - public SqlGroupingFunction() { - super("GROUPING", SqlKind.GROUPING, ReturnTypes.INTEGER, null, - OperandTypes.ANY, SqlFunctionCategory.SYSTEM); + public SqlGroupingFunction(String name) { + super(name, SqlKind.GROUPING, ReturnTypes.BIGINT, null, + OperandTypes.ONE_OR_MORE, SqlFunctionCategory.SYSTEM); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java index a4276b4..ef5fe1d 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java @@ -28,9 +28,14 @@ import org.apache.calcite.sql.type.ReturnTypes; * consistent with Oracle. * * <p>Some examples are in {@code agg.iq}. + * + * @deprecated Now that {@code GROUPING} has the same functionality, + * this function is deprecated. */ +@Deprecated // to be removed before 2.0 class SqlGroupingIdFunction extends SqlAbstractGroupFunction { public SqlGroupingIdFunction() { + //noinspection deprecation super("GROUPING_ID", SqlKind.GROUPING_ID, ReturnTypes.BIGINT, null, OperandTypes.ONE_OR_MORE, SqlFunctionCategory.SYSTEM); } http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/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 8e1983e..8326f58 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 @@ -189,15 +189,24 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { * function ({@code SELECT}, {@code HAVING} clause, etc. of an aggregate * query), but not technically an aggregate function. */ public static final SqlGroupingFunction GROUPING = - new SqlGroupingFunction(); + new SqlGroupingFunction("GROUPING"); /** {@code GROUP_ID} function. */ public static final SqlGroupIdFunction GROUP_ID = new SqlGroupIdFunction(); - /** {@code GROUPING_ID} function. */ - public static final SqlGroupingIdFunction GROUPING_ID = - new SqlGroupingIdFunction(); + /** {@code GROUP_ID} function is a synonym for {@code GROUPING}. + * + * <p>Some history. The {@code GROUPING} function is in the SQL standard, + * and originally supported only one argument. The {@code GROUP_ID} is not + * standard (though supported in Oracle and SQL Server) and supports zero or + * more arguments. + * + * <p>The SQL standard has changed to allow {@code GROUPING} to have multiple + * arguments. It is now equivalent to {@code GROUP_ID}, so we made + * {@code GROUP_ID} a synonym for {@code GROUPING}. */ + public static final SqlGroupingFunction GROUPING_ID = + new SqlGroupingFunction("GROUPING_ID"); /** {@code EXTEND} operator. */ public static final SqlInternalOperator EXTEND = new SqlExtendOperator(); http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/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 a93ddd6..f6e20e1 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -4674,7 +4674,6 @@ public class SqlToRelConverter { switch (call.getKind()) { case GROUPING: - case GROUPING_ID: case GROUP_ID: final RelDataType type = validator.getValidatedNodeType(call); if (!aggregatingSelectScope.resolved.get().indicator) { @@ -4708,8 +4707,6 @@ public class SqlToRelConverter { private int effectiveArgCount(SqlCall call) { switch (call.getKind()) { case GROUPING: - return 1; - case GROUPING_ID: return call.operandCount(); case GROUP_ID: return groupExprs.size(); http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/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 5ff4f52..26bde91 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -5002,13 +5002,18 @@ public class SqlValidatorTest extends SqlValidatorTestCase { @Test public void testGrouping() { sql("select deptno, grouping(deptno) from emp group by deptno").ok(); + sql("select deptno, grouping(deptno, 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"); + .ok(); sql("select deptno, grouping(^empno^) from emp group by deptno") .fails("Argument to GROUPING operator must be a grouped expression"); + sql("select deptno, grouping(deptno, ^empno^) from emp group by deptno") + .fails("Argument to GROUPING operator must be a grouped expression"); + sql("select deptno, grouping(^empno^, deptno) from emp group by deptno") + .fails("Argument to GROUPING operator must be a grouped expression"); 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") @@ -5059,6 +5064,8 @@ public class SqlValidatorTest extends SqlValidatorTestCase { @Test public void testGroupingId() { sql("select deptno, grouping_id(deptno) from emp group by deptno").ok(); + sql("select deptno, grouping_id(deptno, deptno) from emp group by deptno") + .ok(); sql("select deptno / 2, grouping_id(deptno / 2),\n" + " ^grouping_id(deptno / 2, empno)^\n" + "from emp group by deptno / 2, empno") http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/core/src/test/resources/sql/agg.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq index ec08dcb..464c79a 100755 --- a/core/src/test/resources/sql/agg.iq +++ b/core/src/test/resources/sql/agg.iq @@ -508,6 +508,31 @@ group by deptno, gender; !ok +# GROUPING accepts multiple arguments, gives same result as GROUPING_ID +select count(*) as c, + grouping(deptno) as gd, + grouping_id(deptno) as gid, + grouping(deptno, gender, deptno) as gdgd, + grouping_id(deptno, gender, deptno) as gidgd +from emp +group by deptno, gender +having grouping(deptno) <= grouping_id(deptno, gender, deptno); ++---+----+-----+------+-------+ +| C | GD | GID | GDGD | GIDGD | ++---+----+-----+------+-------+ +| 1 | 1 | 1 | 7 | 7 | +| 1 | 1 | 1 | 7 | 7 | +| 1 | 1 | 1 | 7 | 7 | +| 1 | 1 | 1 | 7 | 7 | +| 1 | 1 | 1 | 7 | 7 | +| 1 | 1 | 1 | 7 | 7 | +| 1 | 1 | 1 | 7 | 7 | +| 2 | 1 | 1 | 7 | 7 | ++---+----+-----+------+-------+ +(8 rows) + +!ok + # GROUPING in ORDER BY clause select count(*) as c from emp http://git-wip-us.apache.org/repos/asf/calcite/blob/abfcc79b/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index fcf465c..2127a40 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1275,9 +1275,9 @@ Not implemented: | Operator syntax | Description |:-------------------- |:----------- -| GROUPING(expression) | Returns 1 if expression is rolled up in the current row's grouping set, 0 otherwise +| GROUPING(expression [, expression ] * ) | Returns a bit vector of the given grouping expressions | GROUP_ID() | Returns an integer that uniquely identifies the combination of grouping keys -| GROUPING_ID(expression [, expression ] * ) | Returns a bit vector of the given grouping expressions +| GROUPING_ID(expression [, expression ] * ) | Synonym for `GROUPING` ### User-defined functions
