Tisya Bhatia created CALCITE-7594:
-------------------------------------
Summary: Support GROUP BY ALL
Key: CALCITE-7594
URL: https://issues.apache.org/jira/browse/CALCITE-7594
Project: Calcite
Issue Type: New Feature
Components: core
Reporter: Tisya Bhatia
when GROUP BY ALL appears with no grouping items, the query groups by every
expression in the SELECT clause that is not an aggregate or window function.
Example:
SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY ALL;
^ equivalent to GROUP BY deptno, job
Motivation: keeps the GROUP BY in sync with the SELECT list automatically and
removes a common class of "column must appear in the GROUP BY clause" errors.
PostgreSQL, DuckDB, Trino and Spark all offer a form of this.
Disambiguation from CALCITE-5089: Calcite already supports ALL/DISTINCT as a
set quantifier on grouping sets (e.g. GROUP BY ALL CUBE(a, b), where ALL is the
default). The new behavior applies ONLY when no grouping items follow ALL, so
the two are unambiguous to parse and existing behavior is unchanged. This
matches DuckDB/Spark, which spell it "GROUP BY ALL"; Trino uses "GROUP BY AUTO"
to avoid the overload.
Scope:
- GROUP BY ALL only. Positional GROUP BY (GROUP BY <ordinal>) is already
supported via SqlConformance.isGroupByOrdinal and is out of scope.
- ORDER BY ALL will be proposed as a separate issue.
- SELECT * with GROUP BY ALL is rejected with a clear error (star is not
expanded at group-validation time).
A working prototype (parser + validator + tests + docs) is ready; a PR will
follow. A discussion will also be raised on [email protected].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
