tisyabhatia commented on code in PR #5089: URL: https://github.com/apache/calcite/pull/5089#discussion_r3571721249
########## core/src/test/resources/sql/agg.iq: ########## @@ -4301,4 +4301,19 @@ GROUP BY GROUPING SETS ((deptno), ()); !ok +# [CALCITE-7647] 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) Review Comment: Good question; yes, it's verified. `GROUP BY ALL` originates in DuckDB, and DuckDB, Snowflake, and Databricks/Spark SQL all group by the non-aggregate items in the `SELECT` list. DuckDB expands `*` to its underlying columns for this, so a star and an aggregate can coexist. I ran this exact test case in the DuckDB shell (https://shell.duckdb.org): ```sql SELECT *, count(*) AS c FROM (VALUES (1, 'a'), (1, 'a'), (2, 'b')) AS t(x, y) GROUP BY ALL ORDER BY ALL; ┌───────┬─────────┬───────┐ │ x │ y │ c │ ├───────┼─────────┼───────┤ │ 1 │ a │ 2 │ │ 2 │ b │ 1 │ └───────┴─────────┴───────┘ ``` Same result as test: * expands to (x, y), those become the grouping keys, and COUNT(*) is excluded -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
