[
https://issues.apache.org/jira/browse/SPARK-59175?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-59175:
-----------------------------------
Labels: pull-request-available (was: )
> MISSING_AGGREGATION when a select item combines a window function with the
> GROUP BY expression
> ----------------------------------------------------------------------------------------------
>
> Key: SPARK-59175
> URL: https://issues.apache.org/jira/browse/SPARK-59175
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.0.1
> Reporter: Lino Rosa
> Priority: Major
> Labels: pull-request-available
>
> Reproduce it on a {{{}spark-shell{}}}:
>
>
> {code:java}
> spark.sql("""
> | CREATE OR REPLACE TEMP VIEW events AS
> | SELECT * FROM VALUES ('us'),('US'),('us'),('ca'),('CA'),('mx'),('br')
> | AS t(country)""".stripMargin)
> spark.sql("""
> | WITH ranked AS (
> | SELECT
> | CASE
> | WHEN ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC) <= 2 THEN
> UPPER(country)
> | ELSE 'Other'
> | END AS bucket,
> | COUNT(1) AS events
> | FROM events
> | GROUP BY UPPER(country)
> |)
> |SELECT bucket, SUM(events) AS events
> |FROM ranked
> |GROUP BY bucket""".stripMargin){code}
>
> The query crashes with:
> {code:java}
> [MISSING_AGGREGATION] The non-aggregating expression "country" is based on
> columns which are not participating in the GROUP BY clause.
> Add the columns or the expression to the GROUP BY, aggregate the expression,
> or use "any_value(country)" if you do not care which of the values within a
> group is returned. SQLSTATE: 42803;
> Aggregate [bucket#2], [bucket#2, sum(events#3L) AS events#1L]
> +- SubqueryAlias ranked
> +- SubqueryAlias ranked
> +- Project [bucket#2, events#3L]
> +- Project [events#3L, _w0#9L, country#5, _we0#10, CASE WHEN
> (_we0#10 <= 2) THEN upper(country#5) ELSE Other END AS bucket#2]
> +- Window [row_number() windowspecdefinition(_w0#9L DESC NULLS
> LAST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$()))
> AS _we0#10], [_w0#9L DESC NULLS LAST]
> +- Aggregate [upper(country#5)], [count(1) AS events#3L,
> count(1) AS _w0#9L, country#5]
> +- SubqueryAlias events
> +- View (`events`, [country#5])
> +- Project [cast(country#4 as string) AS country#5]
> +- Project [country#4]
> +- SubqueryAlias t
> +- LocalRelation [country#4] {code}
> You can see the Aggregate has {{country#5}} on its output list so that the
> Project above it does {{{}upper(country#5){}}}. I believe this is the bug.
> The output list of the aggregate should be {{upper(country#5)}} and the
> Project above it should just reference it.
> In case it helps fixing the issue, if we replace `THEN UPPER(country)` with
> `THEN ANY_VALUE(UPPER(country))`, then the query would work with the same
> semantics. Of course it's just a band-aid solution to circumvent the bug.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
