[
https://issues.apache.org/jira/browse/SPARK-60873?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-60873:
-----------------------------------
Labels: pull-request-available (was: )
> HAVING is applied between nested window operations instead of before both
> -------------------------------------------------------------------------
>
> Key: SPARK-60873
> URL: https://issues.apache.org/jira/browse/SPARK-60873
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Nick Young
> Priority: Major
> Labels: pull-request-available
>
> Affects version: 5.0.0 (specified by the reporter; verification note below).
> Grouped queries containing nested window functions can evaluate the inner
> window before applying HAVING. This lets filtered-out groups affect the
> result.
> Minimal synthetic reproduction:
> {code:sql}
> SELECT
> k,
> sum(rank() OVER (ORDER BY sum(v) DESC)) OVER () AS total_rank
> FROM VALUES (1, 10), (2, 20), (3, 30), (4, 40) AS t(k, v)
> GROUP BY k
> HAVING k <> 4
> ORDER BY k;
> {code}
> Expected result:
> {code:text}
> k total_rank
> 1 6
> 2 6
> 3 6
> {code}
> HAVING should remove group 4 before either window executes. The remaining
> groups receive ranks 1, 2 and 3.
> Incorrect behavior:
> When HAVING is placed between the window layers, the inner window ranks all
> four groups first. Removing group 4 then leaves ranks 2, 3 and 4, producing a
> total of 9.
> Replacing the HAVING condition with {{HAVING sum(v) < 40}} also exercises
> aggregate resolution: aggregate HAVING expressions can remain unresolved or
> be rejected as invalid filter conditions.
> Workaround:
> Separate the window layers with a subquery:
> {code:sql}
> SELECT k, sum(r) OVER () AS total_rank
> FROM (
> SELECT k, rank() OVER (ORDER BY sum(v) DESC) AS r
> FROM VALUES (1, 10), (2, 20), (3, 30), (4, 40) AS t(k, v)
> GROUP BY k
> HAVING k <> 4
> ) s
> ORDER BY k;
> {code}
> Expected analyzer ordering:
> {code:text}
> Aggregate → HAVING Filter → inner Window → outer Window
> {code}
> The faulty ordering is:
> {code:text}
> Aggregate → inner Window → HAVING Filter → outer Window
> {code}
> Verification note: This exact synthetic reproduction and the affected
> upstream releases still need confirmation.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
