[
https://issues.apache.org/jira/browse/CALCITE-7594?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tisya Bhatia updated CALCITE-7594:
----------------------------------
Description:
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.
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.
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].
was:
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].
> 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
> Priority: Major
>
> 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.
> 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.
> 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)
