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 21b5b2fb2c [CALCITE-7647] Support SELECT * in GROUP BY ALL and ORDER
BY ALL
21b5b2fb2c is described below
commit 21b5b2fb2cf09a80fc1b7582c2e950b073046bda
Author: Tisya Bhatia <[email protected]>
AuthorDate: Mon Aug 3 11:56:27 2026 -0500
[CALCITE-7647] Support SELECT * in GROUP BY ALL and ORDER BY ALL
---
.../apache/calcite/runtime/CalciteResource.java | 6 ----
.../calcite/sql/validate/SqlValidatorImpl.java | 32 +++++++++++++++++++---
.../calcite/runtime/CalciteResource.properties | 2 --
.../org/apache/calcite/test/SqlValidatorTest.java | 28 +++++++++++++++----
core/src/test/resources/sql/agg.iq | 31 +++++++++++++++++++++
core/src/test/resources/sql/sort.iq | 15 ++++++++++
site/_docs/reference.md | 6 ++++
7 files changed, 102 insertions(+), 18 deletions(-)
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 2e5056da1d..cc27cb5c20 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -406,9 +406,6 @@ 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();
@@ -800,9 +797,6 @@ ExInst<CalciteException>
illegalArgumentForTableFunctionCall(String a0,
@BaseMessage("Streaming ORDER BY must start with monotonic expression")
ExInst<SqlValidatorException> streamMustOrderByMonotonic();
- @BaseMessage("ORDER BY ALL requires an explicit SELECT list; ''*'' is not
supported")
- ExInst<SqlValidatorException> orderByAllRequiresExplicitSelectList();
-
@BaseMessage("Set operator cannot combine streaming and non-streaming
inputs")
ExInst<SqlValidatorException> streamSetOpInconsistentInputs();
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 604c03d2d1..c2d12ffd18 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
@@ -5328,6 +5328,24 @@ protected void validateOrderList(SqlSelect select) {
}
}
+ /** Expands a single "*" or "t.*" select item into its underlying columns,
+ * for a GROUP BY ALL / ORDER BY ALL rewrite.
+ *
+ * <p>Calls the private {@code expandStar} core directly (not the public
+ * {@code expandStar(SqlNodeList, SqlSelect, boolean)} wrapper), with fresh
+ * collections: the wrapper would derive types over every select item and
+ * mark the expanded list, poisoning {@link AggregatingSelectScope}'s
+ * memoized grouping set with the not-yet-rewritten placeholder. The fresh
+ * {@code items}/{@code fields} must stay paired for NATURAL/USING index
+ * alignment. */
+ private List<SqlNode> expandStarForAllRewrite(SqlSelect select, SqlNode
starItem) {
+ final SelectScope scope = (SelectScope) getWhereScope(select);
+ final List<SqlNode> items = new ArrayList<>();
+ expandStar(items, catalogReader.nameMatcher().createSet(), PairList.of(),
+ false, scope, starItem, false);
+ return items;
+ }
+
protected void rewriteOrderByAll(SqlSelect select) {
final SqlNodeList orderList = select.getOrderList();
if (orderList == null || orderList.size() != 1) {
@@ -5360,8 +5378,10 @@ protected void rewriteOrderByAll(SqlSelect select) {
for (SqlNode selectItem : select.getSelectList()) {
final SqlNode expr = SqlUtil.stripAs(selectItem);
if (expr instanceof SqlIdentifier && ((SqlIdentifier) expr).isStar()) {
- throw newValidationError(expr,
- RESOURCE.orderByAllRequiresExplicitSelectList());
+ for (SqlNode column : expandStarForAllRewrite(select, expr)) {
+ keys.add(applyOrderByAllDirection(column, desc, nulls, pos));
+ }
+ continue;
}
keys.add(applyOrderByAllDirection(expr, desc, nulls, pos));
}
@@ -5561,8 +5581,12 @@ private void rewriteGroupByAll(SqlSelect select) {
}
final SqlNode expr = SqlUtil.stripAs(selectItem);
if (expr instanceof SqlIdentifier && ((SqlIdentifier) expr).isStar()) {
- throw newValidationError(expr,
- RESOURCE.groupByAllRequiresExplicitSelectList());
+ for (SqlNode column : expandStarForAllRewrite(select, expr)) {
+ if (aggOrOverFinder.findAgg(column) == null) {
+ keys.add(column);
+ }
+ }
+ continue;
}
if (aggOrOverFinder.findAgg(expr) == null) {
keys.add(expr);
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 636e117c7e..c90ac9c4ed 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -136,7 +136,6 @@ 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
@@ -261,7 +260,6 @@ CannotConvertToStream=Cannot convert table ''{0}'' to stream
CannotConvertToRelation=Cannot convert stream ''{0}'' to relation
StreamMustGroupByMonotonic=Streaming aggregation requires at least one
monotonic expression in GROUP BY clause
StreamMustOrderByMonotonic=Streaming ORDER BY must start with monotonic
expression
-OrderByAllRequiresExplicitSelectList=ORDER BY ALL requires an explicit SELECT
list; ''*'' is not supported
StreamSetOpInconsistentInputs=Set operator cannot combine streaming and
non-streaming inputs
CannotStreamValues=Cannot stream VALUES
CyclicDefinition=Cannot resolve ''{0}''; it references view ''{1}'', whose
definition is cyclic
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 892d68f117..38f883743e 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7366,9 +7366,17 @@ public boolean isBangEqualAllowed() {
sql("select deptno, sal from emp order by all").ok();
// direction applies to every expanded key
sql("select deptno, sal from emp order by all desc").ok();
- // SELECT * can't be expanded here
- sql("select ^*^ from emp order by all")
- .fails("(?s).*ORDER BY ALL requires an explicit SELECT list.*");
+ // SELECT * validates with ORDER BY ALL; what the star expands to as sort
+ // keys is asserted explicitly by rewritesTo below.
+ sql("select * from emp order by all").ok();
+ sql("select * from emp order by all desc").ok();
+ // Multiple qualified stars validate together (no cross-expansion error).
+ sql("select emp.*, dept.* from emp, dept order by all desc").ok();
+ // Show the expanded sort keys.
+ sql("select * from dept order by all")
+ .rewritesTo("SELECT *\n"
+ + "FROM `DEPT`\n"
+ + "ORDER BY `DEPT`.`DEPTNO`, `DEPT`.`NAME`");
// Aliases that shadow other column names must not confuse expansion
sql("select empno as deptno, deptno as empno from emp order by all").ok();
// verify "x" still resolves and the two features coexist
@@ -7822,9 +7830,17 @@ public boolean isBangEqualAllowed() {
// 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.*");
+ // SELECT * validates with GROUP BY ALL; what the star expands to as
+ // grouping keys is asserted explicitly by rewritesTo below.
+ sql("select * from emp group by all").ok();
+ sql("select *, count(*) from emp group by all").ok();
+ sql("select * from emp natural join dept group by all").ok();
+ // Show the expanded grouping keys: GROUP BY ALL is replaced by the
+ // star's underlying columns (behavior validated against DuckDB).
+ sql("select * from dept group by all")
+ .rewritesTo("SELECT *\n"
+ + "FROM `DEPT`\n"
+ + "GROUP BY `DEPT`.`DEPTNO`, `DEPT`.`NAME`");
// contains-an-aggregate
sql("select deptno, substring(job, 1), count(*) + 1 as c, 'x' as x\n"
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index d6557e34f1..bc24ba6014 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -4688,4 +4688,35 @@ FROM emp;
!use scott
+# [CALCITE-7647] Support SELECT * in GROUP BY ALL and ORDER BY ALL.
+# GROUP BY ALL expands SELECT * to every underlying column;
+# the star columns become grouping keys and the aggregate is excluded.
+select *, count(*) as c from (values (1, 'a'), (1, 'a'), (2, 'b')) as t(x, y)
+group by all
+order by x;
++---+---+---+
+| X | Y | C |
++---+---+---+
+| 1 | a | 2 |
+| 2 | b | 1 |
++---+---+---+
+(2 rows)
+
+!ok
+
+# [CALCITE-7647] GROUP BY ALL deduplicates a column that SELECT * and an
+# explicit reference both contribute to the grouping keys.
+select *, x from (values (1, 'a'), (1, 'a'), (2, 'b')) as t(x, y)
+group by all
+order by y;
++---+---+---+
+| X | Y | X |
++---+---+---+
+| 1 | a | 1 |
+| 2 | b | 2 |
++---+---+---+
+(2 rows)
+
+!ok
+
# End agg.iq
diff --git a/core/src/test/resources/sql/sort.iq
b/core/src/test/resources/sql/sort.iq
index 0e8d84cff9..a532047425 100644
--- a/core/src/test/resources/sql/sort.iq
+++ b/core/src/test/resources/sql/sort.iq
@@ -568,4 +568,19 @@ order by all;
!ok
+# [CALCITE-7647] Support SELECT * in GROUP BY ALL and ORDER BY ALL.
+# ORDER BY ALL expands SELECT * to every underlying column.
+select * from (values (2, 'b'), (1, 'a'), (1, 'c')) as t(x, y)
+order by all;
++---+---+
+| X | Y |
++---+---+
+| 1 | a |
+| 1 | c |
+| 2 | b |
++---+---+
+(3 rows)
+
+!ok
+
# End sort.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 71c067e27b..59ddce4af5 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -426,6 +426,9 @@ ## Grammar
"SELECT x, y FROM t ORDER BY ALL" is equivalent to
"SELECT x, y FROM t ORDER BY x, y"
An optional trailing ASC / DESC and NULLS FIRST / NULLS LAST applies to all
keys.
+A `*` in the SELECT clause is expanded to its underlying columns, each of which
+becomes a sort key; for example, "SELECT * FROM t ORDER BY ALL" sorts by every
+column of `t`.
In *query*, *start* may be either an unsigned numeric literal or a dynamic
parameter whose value is numeric. The *count* in a LIMIT clause may be either
@@ -468,6 +471,9 @@ ## Grammar
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".
+A `*` in the SELECT clause is expanded to its underlying columns, each of which
+becomes a grouping key; for example,
+"SELECT *, COUNT(*) FROM emp GROUP BY ALL" groups by every column of `emp`.
*selectWithoutFrom* is equivalent to VALUES,
but is not standard SQL and is only allowed in certain
