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 2017/02/22 19:23:23 UTC
calcite git commit: [CALCITE-1652] Allow GROUPING to have multiple
arguments, like GROUPING_ID
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 <jh...@apache.org>
Authored: Tue Feb 21 13:13:37 2017 -0800
Committer: Julian Hyde <jh...@apache.org>
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