This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 e62637b323 [CALCITE-7594] Support GROUP BY ALL
e62637b323 is described below
commit e62637b323dc17d202546d9e5ac86d229690567e
Author: Tisya Bhatia <[email protected]>
AuthorDate: Mon Jun 8 15:34:35 2026 -0500
[CALCITE-7594] Support GROUP BY ALL
When GROUP BY ALL appears with no grouping items, group by every expression
in the SELECT clause that does not contain an aggregate or window function and
is not a measure. Parser forks the ALL branch on LOOKAHEAD(2); a trailing
grouping list keeps the existing CALCITE-5089 ALL/DISTINCT set-quantifier
behaviour; nothing trailing emits a GROUP_BY_ALL marker that the validator
expands before base group validation. Constants are kept as grouping keys
(grouping only by constants over empt [...]
---
core/src/main/codegen/templates/Parser.jj | 41 +++++++++++++++-------
.../apache/calcite/runtime/CalciteResource.java | 3 ++
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 ++
.../org/apache/calcite/sql/SqlSelectOperator.java | 4 +++
.../calcite/sql/fun/SqlInternalOperators.java | 5 +++
.../calcite/sql/validate/SqlValidatorImpl.java | 27 ++++++++++++++
.../calcite/runtime/CalciteResource.properties | 1 +
.../java/org/apache/calcite/test/JdbcTest.java | 11 ++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 30 ++++++++++++++++
site/_docs/reference.md | 9 +++--
.../apache/calcite/sql/parser/SqlParserTest.java | 16 +++++++++
11 files changed, 136 insertions(+), 14 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index b340415b95..4bbf49840c 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -2773,25 +2773,42 @@ SqlNode Where() :
SqlNodeList GroupBy() :
{
final List<SqlNode> list;
- final boolean distinct;
final Span s;
+ SqlParserPos pos;
}
{
<GROUP> { s = span(); }
<BY>
(
- <DISTINCT> { distinct = true; }
- | <ALL> { distinct = false; }
- | { distinct = false; }
+ <DISTINCT>
+ list = GroupingElementList() {
+ pos = s.end(this);
+ return new SqlNodeList(
+ ImmutableList.of(
+ SqlInternalOperators.GROUP_BY_DISTINCT.createCall(pos,
list)), pos
+ );
+ }
+ |
+ <ALL>
+ (
+ LOOKAHEAD(2)
+ list = GroupingElementList() {
+ return new SqlNodeList(list, s.end(this));
+ }
+ |
+ {
+ pos = s.end(this);
+ return new SqlNodeList(
+ ImmutableList.of(
+ SqlInternalOperators.GROUP_BY_ALL.createCall(pos)), pos
+ );
+ }
+ )
+ |
+ list = GroupingElementList() {
+ return new SqlNodeList(list, s.end(this));
+ }
)
- list = GroupingElementList() {
- final SqlParserPos pos = s.end(this);
- final List<SqlNode> list2 = distinct
- ? ImmutableList.of(
- SqlInternalOperators.GROUP_BY_DISTINCT.createCall(pos, list))
- : list;
- return new SqlNodeList(list2, pos);
- }
}
List<SqlNode> GroupingElementList() :
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 ea60315ae6..13745bf0df 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -383,6 +383,9 @@ ExInst<SqlValidatorException>
naturalOrUsingColumnNotCompatible(String a0,
@BaseMessage("Windowed aggregate expression is illegal in {0} clause")
ExInst<SqlValidatorException> windowedAggregateIllegalInClause(String a0);
+ @BaseMessage("GROUP BY ALL requires an explicit SELECT list; ''*'' is not
supported")
+ ExInst<SqlValidatorException> groupByAllRequiresExplicitSelectList();
+
@BaseMessage("Aggregate expressions cannot be nested")
ExInst<SqlValidatorException> nestedAggIllegal();
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 40111fe0af..680c833c17 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -183,6 +183,9 @@ public enum SqlKind {
/** The DISTINCT keyword of the GROUP BY clause. */
GROUP_BY_DISTINCT,
+ /** The ALL keyword of the GROUP BY clause. */
+ GROUP_BY_ALL,
+
/**
* 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 8605eef7b3..fea1c2235f 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
@@ -186,6 +186,10 @@ public SqlSelect createCall(
writer.sep("GROUP BY DISTINCT");
List<SqlNode> operandList = ((SqlCall)
groupBy.get(0)).getOperandList();
groupBy = new SqlNodeList(operandList, groupBy.getParserPosition());
+ } else if (groupBy.size() == 1 && groupBy.get(0) != null
+ && groupBy.get(0).getKind() == SqlKind.GROUP_BY_ALL) {
+ writer.sep("GROUP BY ALL");
+ groupBy = new SqlNodeList(groupBy.getParserPosition());
} else {
writer.sep("GROUP BY");
}
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 dd1098fe4f..8757e1e177 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
@@ -203,6 +203,11 @@ private SqlInternalOperators() {
public static final SqlInternalOperator GROUP_BY_DISTINCT =
new SqlRollupOperator("GROUP BY DISTINCT", SqlKind.GROUP_BY_DISTINCT);
+ /** {@code GROUP BY ALL}, a placeholder expanded during validation into
+ * a standard {@code GROUP BY}. */
+ public static final SqlInternalOperator GROUP_BY_ALL =
+ new SqlRollupOperator("GROUP BY ALL", SqlKind.GROUP_BY_ALL);
+
/** Fetch operator is ONLY used for its precedence during unparsing. */
public static final SqlOperator FETCH =
SqlBasicOperator.create("FETCH")
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 12b1b27335..7989b7e7d8 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
@@ -5274,6 +5274,7 @@ private static SqlNode measureToValue(SqlNode e) {
* called even if no GROUP BY clause is present.
*/
protected void validateGroupClause(SqlSelect select) {
+ rewriteGroupByAll(select);
SqlNodeList groupList = select.getGroup();
if (groupList == null) {
return;
@@ -5345,6 +5346,32 @@ protected void validateGroupClause(SqlSelect select) {
}
}
+ /** If GROUP BY clause is the {@code GROUP BY ALL} placeholder, replaces it
+ * with every non-aggregated expression from the SELECT clause. */
+ private void rewriteGroupByAll(SqlSelect select) {
+ final SqlNodeList groupList = select.getGroup();
+ if (groupList == null
+ || groupList.size() != 1
+ || groupList.get(0).getKind() != SqlKind.GROUP_BY_ALL) {
+ return;
+ }
+ final List<SqlNode> keys = new ArrayList<>();
+ for (SqlNode selectItem : select.getSelectList()) {
+ if (SqlValidatorUtil.isMeasure(selectItem)) {
+ continue;
+ }
+ final SqlNode expr = SqlUtil.stripAs(selectItem);
+ if (expr instanceof SqlIdentifier && ((SqlIdentifier) expr).isStar()) {
+ throw newValidationError(expr,
+ RESOURCE.groupByAllRequiresExplicitSelectList());
+ }
+ if (aggOrOverFinder.findAgg(expr) == null) {
+ keys.add(expr);
+ }
+ }
+ select.setGroupBy(new SqlNodeList(keys, groupList.getParserPosition()));
+ }
+
private void validateGroupItem(SqlValidatorScope groupScope,
@Nullable AggregatingSelectScope aggregatingScope,
SqlNode groupItem) {
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 8906e45121..325b69d872 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -131,6 +131,7 @@ GroupingInWrongClause={0} operator may only occur in
SELECT, HAVING or ORDER BY
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
+GroupByAllRequiresExplicitSelectList=GROUP BY ALL requires an explicit SELECT
list; ''*'' is not supported
NestedAggIllegal=Aggregate expressions cannot be nested
MeasureIllegal=Measure expressions can only occur within AGGREGATE function
MeasureMustBeInAggregateQuery=Measure expressions can only occur within a
GROUP BY query
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 9732b10c8f..ee1afe05cb 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -1297,6 +1297,17 @@ private void checkResultSetMetaData(Connection
connection, String sql)
+ "c0=1998\n");
}
+ /** Test case for [CALCITE-7594] GROUP BY ALL: grouping only by a constant
+ * over empty input returns 0 rows. */
+ @Test void testGroupByAllOverEmptyInput() {
+ CalciteAssert.hr()
+ .query("select 'x', count(*)\n"
+ + "from \"hr\".\"emps\"\n"
+ + "where false\n"
+ + "group by all")
+ .returnsCount(0);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-2894";>[CALCITE-2894]
* NullPointerException thrown by RelMdPercentageOriginalRows when explaining
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 0e9c533dbd..283ea802aa 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7615,6 +7615,36 @@ public boolean isBangEqualAllowed() {
.withConformance(lenient).ok();
}
+ @Test void testGroupByAll() {
+ // expands to every non-aggregate SELECT item - so listing a column besides
+ // an aggregate validates WITHOUT a "not being grouped" error
+ sql("select deptno, count(*) from emp group by all").ok();
+
+ // only aggregates -> global aggregation (one group), still valid
+ sql("select count(*) from emp group by all").ok();
+
+ // SELECT * cannot be expanded at group-validation time -> clear error
+ sql("select ^*^ from emp group by all")
+ .fails("(?s).*GROUP BY ALL requires an explicit SELECT list.*");
+
+ // contains-an-aggregate
+ sql("select deptno, substring(job, 1), count(*) + 1 as c, 'x' as x\n"
+ + "from emp group by all")
+ .ok();
+
+ // GROUP BY ALL collects the non-aggregate exprs (sal, x + 1) as
+ // group keys and "x" still resolves, so the two features coexist.
+ sql("select sal as x, x + 1 as y, count(*) from emp group by all")
+ .withValidatorIdentifierExpansion(true)
+ .withConformance(SqlConformanceEnum.BABEL)
+ .ok();
+
+ // GROUP BY ALL is unaffected by isGroupByAlias
+ sql("select deptno as d, count(*) from emp group by all")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .ok();
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5507";>[CALCITE-5507]
* HAVING alias failed when aggregate function in condition</a>. */
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index e546757d0b..454d13456a 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -210,7 +210,7 @@ ## Grammar
[ BY expression [, expression ]* ]
FROM tableExpression
[ WHERE booleanExpression ]
- [ GROUP BY [ ALL | DISTINCT ] { groupItem [, groupItem ]* } ]
+ [ GROUP BY { ALL | [ALL | DISTINCT ] groupItem [, groupiTEM ]* } ]
[ HAVING booleanExpression ]
[ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
[ QUALIFY booleanExpression ]
@@ -429,7 +429,12 @@ ## Grammar
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.
+GROUP BY ALL followed by grouping items is equivalent to GROUP BY
+(ALL is the default set quantifier).
+GROUP BY ALL on its own groups by every expression in the SELECT clause
+that is not an aggregate function; for example,
+"SELECT deptno, SUM(sal) FROM emp GROUP BY ALL" is equivalent to
+"SELECT deptno, SUM(sal) FROM emp GROUP BY deptno".
*selectWithoutFrom* is equivalent to VALUES,
but is not standard SQL and is only allowed in certain
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 a9e3c6b959..5c24c0a806 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
@@ -2637,6 +2637,22 @@ void checkPeriodPredicate(Checker checker) {
sql(sql2).ok(expected2);
}
+ @Test void testGroupByAll() {
+ final String sql = "select x, sum(y) from t\n"
+ + "group by all";
+ final String expected = "SELECT `X`, SUM(`Y`)\n"
+ + "FROM `T`\n"
+ + "GROUP BY ALL";
+ sql(sql).ok(expected);
+
+ final String sql1 = "select deptno from emp\n"
+ + "group by all deptno, gender";
+ final String expected1 = "SELECT `DEPTNO`\n"
+ + "FROM `EMP`\n"
+ + "GROUP BY `DEPTNO`, `GENDER`";
+ sql(sql1).ok(expected1);
+ }
+
@Test void testGroupByCube2() {
final String sql = "select deptno from emp\n"
+ "group by cube ((a, b), (c, d)) order by a";
