uros-b commented on code in PR #59917: URL: https://github.com/apache/spark/pull/59917#discussion_r4423583482
########## sql/core/src/test/resources/sql-tests/inputs/window-with-aggregates.sql: ########## @@ -183,4 +183,120 @@ SELECT AVG(col1) as avg_col1, RANK() OVER (PARTITION BY col1 / 5, col2 % 2 ORDER BY AVG(col1)) as rank_in_bucket FROM VALUES (5, 1), (7, 3), (10, 2), (12, 4), (15, 1) AS t(col1, col2) -GROUP BY col1 / 5, col2 % 2, col1, col2; \ No newline at end of file +GROUP BY col1 / 5, col2 % 2, col1, col2; + +CREATE OR REPLACE TEMPORARY VIEW wwa_data AS SELECT * FROM VALUES +(1, 10), (1, 20), (2, 30), (3, 40) AS wwa_data(col1, col2); + +-- ORDER BY grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Sort). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +ORDER BY col1; + +-- Same as above but without a window function (Aggregate + Sort). +SELECT col1 AS a, count(*) AS cnt +FROM wwa_data +GROUP BY col1 +ORDER BY col1; + +-- HAVING on grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Filter). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +HAVING col1 > 0; + +-- QUALIFY on grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Filter). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY col1 > 0; + +-- QUALIFY with a window function in the condition on an aliased grouping key. +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY row_number() OVER (ORDER BY col1) = 1; + +-- QUALIFY + ORDER BY on grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Filter + Sort). +-- The Sort child is Filter(_, Window), not Window directly. +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY col1 > 0 +ORDER BY col1; + +-- QUALIFY with a window function in the condition + ORDER BY on aliased grouping key. +-- The Sort child is Project(_, Filter(_, Window(_, Window(_)))). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY row_number() OVER (ORDER BY col1) = 1 +ORDER BY col1; + +-- GROUPING SETS + window + ORDER BY: GROUPING_ID() aliased by user. +-- The GroupingAnalyticsTransformer creates a double alias +-- Alias(Alias(spark_grouping_id, "grouping_id()"), "gid") which must be split +-- so the Aggregate keeps the internal "grouping_id()" name and the Window uses "gid". Review Comment: +1 with the comment above ########## sql/core/src/test/resources/sql-tests/inputs/window-with-aggregates.sql: ########## @@ -183,4 +183,120 @@ SELECT AVG(col1) as avg_col1, RANK() OVER (PARTITION BY col1 / 5, col2 % 2 ORDER BY AVG(col1)) as rank_in_bucket FROM VALUES (5, 1), (7, 3), (10, 2), (12, 4), (15, 1) AS t(col1, col2) -GROUP BY col1 / 5, col2 % 2, col1, col2; \ No newline at end of file +GROUP BY col1 / 5, col2 % 2, col1, col2; + +CREATE OR REPLACE TEMPORARY VIEW wwa_data AS SELECT * FROM VALUES +(1, 10), (1, 20), (2, 30), (3, 40) AS wwa_data(col1, col2); + +-- ORDER BY grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Sort). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +ORDER BY col1; + +-- Same as above but without a window function (Aggregate + Sort). +SELECT col1 AS a, count(*) AS cnt +FROM wwa_data +GROUP BY col1 +ORDER BY col1; + +-- HAVING on grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Filter). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +HAVING col1 > 0; + +-- QUALIFY on grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Filter). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY col1 > 0; + +-- QUALIFY with a window function in the condition on an aliased grouping key. +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY row_number() OVER (ORDER BY col1) = 1; + +-- QUALIFY + ORDER BY on grouping key that is aliased with a different name in SELECT, +-- combined with a window function (Aggregate + Window + Filter + Sort). +-- The Sort child is Filter(_, Window), not Window directly. +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY col1 > 0 +ORDER BY col1; + +-- QUALIFY with a window function in the condition + ORDER BY on aliased grouping key. +-- The Sort child is Project(_, Filter(_, Window(_, Window(_)))). +SELECT col1 AS a, count(*) * 1.0 / sum(count(*)) OVER () AS ratio +FROM wwa_data +GROUP BY col1 +QUALIFY row_number() OVER (ORDER BY col1) = 1 +ORDER BY col1; + +-- GROUPING SETS + window + ORDER BY: GROUPING_ID() aliased by user. +-- The GroupingAnalyticsTransformer creates a double alias +-- Alias(Alias(spark_grouping_id, "grouping_id()"), "gid") which must be split +-- so the Aggregate keeps the internal "grouping_id()" name and the Window uses "gid". Review Comment: The GROUPING_ID() AS gid comment describes an alias split that the recorded plan does not have. Aggregate emits spark_grouping_id AS gid; the Window only reads sum(_w0); gid is passed through to ORDER BY. replaceGroupingFunction does wrap gid in a grouping_id() alias, but that inner alias is collapsed, not split onto the Window. -- 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
