This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 7c1e2746b9 [CALCITE-5089] Allow GROUP BY ALL or DISTINCT set
quantifier on GROUPING SETS
7c1e2746b9 is described below
commit 7c1e2746b94a50f0bb08571287acb5327fdc1b16
Author: hannerwang <[email protected]>
AuthorDate: Wed Apr 13 15:01:39 2022 +0800
[CALCITE-5089] Allow GROUP BY ALL or DISTINCT set quantifier on GROUPING
SETS
Close apache/calcite#2771
---
core/src/main/codegen/templates/Parser.jj | 12 +++-
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +
.../org/apache/calcite/sql/SqlSelectOperator.java | 12 +++-
.../calcite/sql/fun/SqlInternalOperators.java | 4 ++
.../apache/calcite/sql/fun/SqlRollupOperator.java | 5 ++
.../sql/validate/AggregatingSelectScope.java | 17 +++++-
.../calcite/sql/validate/SqlValidatorImpl.java | 14 ++++-
.../apache/calcite/test/SqlToRelConverterTest.java | 24 ++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 51 ++++++++++++++++
core/src/test/resources/sql/agg.iq | 69 ++++++++++++++++++++++
site/_docs/reference.md | 7 ++-
.../apache/calcite/sql/parser/SqlParserTest.java | 23 ++++++++
12 files changed, 235 insertions(+), 6 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index c18f195024..0652a25d48 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -126,6 +126,7 @@ import org.apache.calcite.util.SourceStringReader;
import org.apache.calcite.util.Util;
import org.apache.calcite.util.trace.CalciteTrace;
+import com.google.common.collect.ImmutableList;
import org.slf4j.Logger;
import java.io.Reader;
@@ -2499,11 +2500,20 @@ SqlNode WhereOpt() :
SqlNodeList GroupByOpt() :
{
List<SqlNode> list = new ArrayList<SqlNode>();
+ boolean distinct = false;
final Span s;
}
{
<GROUP> { s = span(); }
- <BY> list = GroupingElementList() {
+ <BY>
+ [ <DISTINCT> { distinct = true; } | <ALL> ]
+ list = GroupingElementList() {
+ if (distinct) {
+ SqlNode groupByDistinct =
+ SqlInternalOperators.GROUP_BY_DISTINCT.createCall(
+ s.add(getPos()).pos(), list);
+ list = ImmutableList.of(groupByDistinct);
+ }
return new SqlNodeList(list, s.addAll(list).pos());
}
|
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 e73214bce0..c6697489d2 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -157,6 +157,9 @@ public enum SqlKind {
/** A dynamic parameter. */
DYNAMIC_PARAM,
+ /** The DISTINCT keyword of the GROUP BY clause. */
+ GROUP_BY_DISTINCT,
+
/**
* ORDER BY clause.
*
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
index 59025647cb..70aa0b4cf9 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
@@ -202,10 +202,18 @@ public class SqlSelectOperator extends SqlOperator {
}
}
if (select.groupBy != null) {
- writer.sep("GROUP BY");
- final SqlNodeList groupBy =
+ SqlNodeList groupBy =
select.groupBy.size() == 0 ? SqlNodeList.SINGLETON_EMPTY
: select.groupBy;
+ // if the DISTINCT keyword of GROUP BY is present it can be the only item
+ if (groupBy.size() == 1 && groupBy.get(0) != null
+ && groupBy.get(0).getKind() == SqlKind.GROUP_BY_DISTINCT) {
+ writer.sep("GROUP BY DISTINCT");
+ List<SqlNode> operandList = ((SqlCall)
groupBy.get(0)).getOperandList();
+ groupBy = new SqlNodeList(operandList, groupBy.getParserPosition());
+ } else {
+ writer.sep("GROUP BY");
+ }
writer.list(SqlWriter.FrameTypeEnum.GROUP_BY_LIST, SqlWriter.COMMA,
groupBy);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
index ceea8fbb1f..c3f12806f9 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
@@ -115,4 +115,8 @@ public abstract class SqlInternalOperators {
new SqlInternalOperator("SEPARATOR", SqlKind.SEPARATOR, 20, false,
ReturnTypes.ARG0, InferTypes.RETURN_TYPE, OperandTypes.ANY);
+ /** {@code DISTINCT} operator, occurs within {@code GROUP BY} clause. */
+ public static final SqlInternalOperator GROUP_BY_DISTINCT =
+ new SqlRollupOperator("GROUP BY DISTINCT", SqlKind.GROUP_BY_DISTINCT);
+
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java
index 0b3f8b9d31..ed0007bcd7 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlRollupOperator.java
@@ -53,6 +53,11 @@ class SqlRollupOperator extends SqlInternalOperator {
return;
}
break;
+ case GROUP_BY_DISTINCT:
+ writer.keyword(call.getOperator().getName());
+ SqlNodeList groupBy = new SqlNodeList(call.getOperandList(),
call.getParserPosition());
+ writer.list(SqlWriter.FrameTypeEnum.GROUP_BY_LIST, SqlWriter.COMMA,
groupBy);
+ return;
default:
break;
}
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 53cc201f28..27b90195e8 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
@@ -20,6 +20,7 @@ import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlSelect;
@@ -30,6 +31,7 @@ import org.apache.calcite.util.Pair;
import com.google.common.base.Suppliers;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableSet;
import com.google.common.collect.ImmutableSortedMultiset;
import org.checkerframework.checker.nullness.qual.Nullable;
@@ -93,8 +95,15 @@ public class AggregatingSelectScope
try {
final ImmutableList.Builder<ImmutableList<ImmutableBitSet>> builder =
ImmutableList.builder();
+ boolean groupByDistinct = false;
if (select.getGroup() != null) {
- final SqlNodeList groupList = select.getGroup();
+ SqlNodeList groupList = select.getGroup();
+ // if the DISTINCT keyword of GROUP BY is present it can be the only
item
+ if (groupList.size() == 1 && groupList.get(0).getKind() ==
SqlKind.GROUP_BY_DISTINCT) {
+ groupList = new SqlNodeList(((SqlCall)
groupList.get(0)).getOperandList(),
+ groupList.getParserPosition());
+ groupByDistinct = true;
+ }
for (SqlNode groupExpr : groupList) {
SqlValidatorUtil.analyzeGroupItem(this, groupAnalyzer, builder,
groupExpr);
@@ -111,6 +120,12 @@ public class AggregatingSelectScope
flatGroupSets.add(ImmutableBitSet.of());
}
+ if (groupByDistinct) {
+ ImmutableSet<ImmutableBitSet> sets =
ImmutableSet.copyOf(flatGroupSets);
+ flatGroupSets.clear();
+ flatGroupSets.addAll(sets);
+ }
+
return new Resolved(groupAnalyzer.extraExprs, groupAnalyzer.groupExprs,
flatGroupSets, groupAnalyzer.groupExprProjection);
} finally {
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 2978f1a700..343a453bd0 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
@@ -4182,10 +4182,16 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
private void validateGroupByExpr(SqlNode groupByItem,
SqlValidatorScope groupByScope) {
switch (groupByItem.getKind()) {
+ case GROUP_BY_DISTINCT:
+ SqlCall call = (SqlCall) groupByItem;
+ for (SqlNode operand : call.getOperandList()) {
+ validateGroupByExpr(operand, groupByScope);
+ }
+ break;
case GROUPING_SETS:
case ROLLUP:
case CUBE:
- final SqlCall call = (SqlCall) groupByItem;
+ call = (SqlCall) groupByItem;
for (SqlNode operand : call.getOperandList()) {
validateExpr(operand, groupByScope);
}
@@ -4260,6 +4266,7 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
// expressions, because they do not have a type.
for (SqlNode node : groupList) {
switch (node.getKind()) {
+ case GROUP_BY_DISTINCT:
case GROUPING_SETS:
case ROLLUP:
case CUBE:
@@ -4296,6 +4303,11 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
@Nullable AggregatingSelectScope aggregatingScope,
SqlNode groupItem) {
switch (groupItem.getKind()) {
+ case GROUP_BY_DISTINCT:
+ for (SqlNode sqlNode : ((SqlCall) groupItem).getOperandList()) {
+ validateGroupItem(groupScope, aggregatingScope, sqlNode);
+ }
+ break;
case GROUPING_SETS:
case ROLLUP:
case CUBE:
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 880846127c..52893c4b94 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -4493,4 +4493,28 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
.withTrim(false)
.ok();
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5089";>[CALCITE-5089]
+ * Allow GROUP BY ALL or DISTINCT set quantifier on GROUPING SETS</a>. */
+ @Test void testGroupByDistinct() {
+ final String sql = "SELECT deptno, job, count(*)\n"
+ + "FROM emp\n"
+ + "GROUP BY DISTINCT\n"
+ + "CUBE (deptno, job),\n"
+ + "ROLLUP (deptno, job)";
+ sql(sql).ok();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5089";>[CALCITE-5089]
+ * Allow GROUP BY ALL or DISTINCT set quantifier on GROUPING SETS</a>. */
+ @Test void testGroupByAll() {
+ final String sql = "SELECT deptno, job, count(*)\n"
+ + "FROM emp\n"
+ + "GROUP BY ALL\n"
+ + "CUBE (deptno, job),\n"
+ + "ROLLUP (deptno, job)";
+ sql(sql).ok();
+ }
}
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 3cf56aa333..7d883fbaaa 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1839,6 +1839,41 @@ LogicalProject(D=[$0], EXPR$1=[+($0, $1)])
from emp group by d,mgr]]>
</Resource>
</TestCase>
+ <TestCase name="testGroupByAll">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, job, count(*)
+FROM emp
+GROUP BY ALL
+CUBE (deptno, job),
+ROLLUP (deptno, job)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]],
EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testGroupByCaseIn">
<Resource name="sql">
<![CDATA[select
@@ -1918,6 +1953,22 @@ group by
cube (coord_ne.sub.a, coord.x, coord."unit")]]>
</Resource>
</TestCase>
+ <TestCase name="testGroupByDistinct">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, job, count(*)
+FROM emp
+GROUP BY DISTINCT
+CUBE (deptno, job),
+ROLLUP (deptno, job)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]],
EXPR$2=[COUNT()])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testGroupByExpression">
<Resource name="sql">
<![CDATA[select count(*)
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index f7688e712e..f8b56a8325 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -581,6 +581,75 @@ select distinct count(*) from emp group by cube(deptno,
gender);
!ok
+# CUBE and ROLLUP cartesian product over same columns
+select deptno, gender, count(*) from emp where deptno = 20 group by
cube(deptno, gender), rollup(deptno, gender);
++--------+--------+--------+
+| DEPTNO | GENDER | EXPR$2 |
++--------+--------+--------+
+| 20 | M | 1 |
+| 20 | M | 1 |
+| 20 | M | 1 |
+| 20 | M | 1 |
+| 20 | M | 1 |
+| 20 | M | 1 |
+| 20 | M | 1 |
+| 20 | | 1 |
+| 20 | | 1 |
+| 20 | | 1 |
+| | M | 1 |
+| | | 1 |
++--------+--------+--------+
+(12 rows)
+
+!ok
+
+# GROUP BY DISTINCT CUBE and ROLLUP cartesian product over same columns
+select deptno, gender, count(*) from emp where deptno = 20 group by distinct
cube(deptno, gender), rollup(deptno, gender);
++--------+--------+--------+
+| DEPTNO | GENDER | EXPR$2 |
++--------+--------+--------+
+| 20 | M | 1 |
+| 20 | | 1 |
+| | M | 1 |
+| | | 1 |
++--------+--------+--------+
+(4 rows)
+
+!ok
+
+# GROUP BY over empty columns
+select count(*) from emp where deptno = 20 group by ();
++--------+
+| EXPR$0 |
++--------+
+| 1 |
++--------+
+(1 row)
+
+!ok
+
+# GROUP BY DISTINCT over empty columns
+select count(*) from emp where deptno = 20 group by distinct ();
++--------+
+| EXPR$0 |
++--------+
+| 1 |
++--------+
+(1 row)
+
+!ok
+
+# GROUP BY DISTINCT x + y
+select deptno + 1, count(*) from emp where deptno = 20 group by distinct
deptno + 1;
++--------+--------+
+| EXPR$0 | EXPR$1 |
++--------+--------+
+| 21 | 1 |
++--------+--------+
+(1 row)
+
+!ok
+
# CUBE and JOIN
select e.deptno, e.gender, min(e.ename) as min_name
from emp as e join dept as d using (deptno)
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 4f1de54526..2b6f52eda7 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -203,7 +203,7 @@ select:
{ * | projectItem [, projectItem ]* }
FROM tableExpression
[ WHERE booleanExpression ]
- [ GROUP BY { groupItem [, groupItem ]* } ]
+ [ GROUP BY [ ALL | DISTINCT ] { groupItem [, groupItem ]* } ]
[ HAVING booleanExpression ]
[ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
@@ -370,6 +370,11 @@ function).
An IN, EXISTS, UNIQUE or scalar sub-query may be correlated; that is, it
may refer to tables in the FROM clause of an enclosing query.
+GROUP BY DISTINCT removes duplicate grouping sets (for example,
+"GROUP BY DISTINCT GROUPING SETS ((a), (a, b), (a))" is equivalent to
+"GROUP BY GROUPING SETS ((a), (a, b))");
+GROUP BY ALL is equivalent to GROUP BY.
+
*selectWithoutFrom* is equivalent to VALUES,
but is not standard SQL and is only allowed in certain
[conformance levels]({{ site.apiRoot
}}/org/apache/calcite/sql/validate/SqlConformance.html#isFromRequired--).
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 0dd79138ea..54841c39f5 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -2122,6 +2122,29 @@ public class SqlParserTest {
sql(sql).ok(expected);
}
+ @Test void testGroupByAllOrDistinct() {
+ final String sql = "select deptno from emp\n"
+ + "group by all cube (a, b), rollup (a, b)";
+ final String expected = "SELECT `DEPTNO`\n"
+ + "FROM `EMP`\n"
+ + "GROUP BY CUBE(`A`, `B`), ROLLUP(`A`, `B`)";
+ sql(sql).ok(expected);
+
+ final String sql1 = "select deptno from emp\n"
+ + "group by distinct cube (a, b), rollup (a, b)";
+ final String expected1 = "SELECT `DEPTNO`\n"
+ + "FROM `EMP`\n"
+ + "GROUP BY DISTINCT CUBE(`A`, `B`), ROLLUP(`A`, `B`)";
+ sql(sql1).ok(expected1);
+
+ final String sql2 = "select deptno from emp\n"
+ + "group by cube (a, b), rollup (a, b)";
+ final String expected2 = "SELECT `DEPTNO`\n"
+ + "FROM `EMP`\n"
+ + "GROUP BY CUBE(`A`, `B`), ROLLUP(`A`, `B`)";
+ sql(sql2).ok(expected2);
+ }
+
@Test void testGroupByCube2() {
final String sql = "select deptno from emp\n"
+ "group by cube ((a, b), (c, d)) order by a";
