[jira] [Created] (SPARK-59175) MISSING_AGGREGATION when a select item combines a window function with the GROUP BY expression

["Lino Rosa (Jira)" <ji...@apache.org>]

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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org