[
https://issues.apache.org/jira/browse/CALCITE-7647?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tisya Bhatia updated CALCITE-7647:
----------------------------------
Description:
CALCITE-7594 (GROUP BY ALL) and CALCITE-7597 (ORDER BY ALL) currently reject a
bare * or qualified t.* in the SELECT list.
Both bare * and qualified t.* are supported, reusing the same logic real SELECT
* expansion already uses. A query may reference multiple stars (e.g. SELECT
{*}, t2.{*}); each is expanded independently and the resulting keys are
combined, consistent with how Calcite already handles duplicate GROUP BY
expressions from explicit SQL.
Other SQL dialects that support {{GROUP BY ALL}} accept a star in the SELECT
list and expand it to its underlying columns, which then participate in the ALL
grouping / ordering:
- DuckDB (which introduced {{{}GROUP BY ALL{}}}): groups by all SELECT columns
not wrapped in aggregates; {{*}} expands to those columns.
- Snowflake: all non-aggregate items in the {{SELECT}} list are used for
grouping.
- Databricks / Spark SQL: all SELECT-list expressions not containing aggregates
become group expressions.
This issue makes Calcite consistent with those dialects. The star expands to
its columns, which become the grouping / sort keys (aggregates are still
excluded from GROUP BY ALL). Verified against DuckDB:
Testing: SqlValidatorTest cases cover a bare star, a star combined with an
aggregate and a HAVING clause, NATURAL JOIN, and a comma join across two tables.
Relates to: CALCITE-7594, CALCITE-7597
was:
CALCITE-7594 (GROUP BY ALL) and CALCITE-7597 (ORDER BY ALL) currently reject a
bare * or qualified t.* in the SELECT list.
This adds a new private helper in SqlValidatorImpl, expandStarForAllRewrite,
which resolves a star select-item into its underlying column identifiers by
calling the existing private expandStar overload with an isolated,
freshly-constructed selectItems/fields state scoped to that one star item.
rewriteGroupByAll and rewriteOrderByAll call this helper for each star item
encountered while building the grouping/ordering key list, then continue
processing the remaining select-list items exactly as before.
Both bare * and qualified t.* are supported, reusing the same logic real SELECT
* expansion already uses. A query may reference multiple stars (e.g. SELECT *,
t2.*); each is expanded independently and the resulting keys are combined,
consistent with how Calcite already handles duplicate GROUP BY expressions from
explicit SQL.
Testing: SqlValidatorTest cases cover a bare star, a star combined with an
aggregate and a HAVING clause, NATURAL JOIN, and a comma join across two tables.
Relates to: CALCITE-7594, CALCITE-7597
> Support SELECT * in GROUP BY ALL and ORDER BY ALL
> -------------------------------------------------
>
> Key: CALCITE-7647
> URL: https://issues.apache.org/jira/browse/CALCITE-7647
> Project: Calcite
> Issue Type: Improvement
> Reporter: Tisya Bhatia
> Assignee: Tisya Bhatia
> Priority: Major
> Labels: pull-request-available
>
> CALCITE-7594 (GROUP BY ALL) and CALCITE-7597 (ORDER BY ALL) currently reject
> a bare * or qualified t.* in the SELECT list.
> Both bare * and qualified t.* are supported, reusing the same logic real
> SELECT * expansion already uses. A query may reference multiple stars (e.g.
> SELECT {*}, t2.{*}); each is expanded independently and the resulting keys
> are combined, consistent with how Calcite already handles duplicate GROUP BY
> expressions from explicit SQL.
> Other SQL dialects that support {{GROUP BY ALL}} accept a star in the SELECT
> list and expand it to its underlying columns, which then participate in the
> ALL grouping / ordering:
> - DuckDB (which introduced {{{}GROUP BY ALL{}}}): groups by all SELECT
> columns not wrapped in aggregates; {{*}} expands to those columns.
> - Snowflake: all non-aggregate items in the {{SELECT}} list are used for
> grouping.
> - Databricks / Spark SQL: all SELECT-list expressions not containing
> aggregates
> become group expressions.
> This issue makes Calcite consistent with those dialects. The star expands to
> its columns, which become the grouping / sort keys (aggregates are still
> excluded from GROUP BY ALL). Verified against DuckDB:
>
> Testing: SqlValidatorTest cases cover a bare star, a star combined with an
> aggregate and a HAVING clause, NATURAL JOIN, and a comma join across two
> tables.
> Relates to: CALCITE-7594, CALCITE-7597
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
