Lino Rosa created SPARK-59175:
---------------------------------
Summary: 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
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]
