Nick Young created SPARK-60873:
----------------------------------
Summary: 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
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]
