Skip to content

Commit 1dac863

Browse files
committed
[SPARK-60873][SQL] Evaluate HAVING before nested window functions
1 parent f4eef98 commit 1dac863

5 files changed

Lines changed: 65 additions & 6 deletions

File tree

‎docs/sql-migration-guide.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@ license: |
2424

2525
## Upgrading from Spark SQL 4.4 to 5.0
2626

27+
- Since Spark 5.0, `HAVING` is evaluated before every nested window expression in the `SELECT` list. Previously, an inner window expression could include groups removed by `HAVING` and produce incorrect results.
2728
- Since Spark 5.0, the Spark Thrift Server refuses to start when `hive.server2.enable.doAs` is `true` and `hive.server2.authentication` verifies user identities (any recognized type other than `NONE`/`NOSASL`). It impersonates the connecting user for Hive metastore calls and driver-side file system access, but executor-side data access still runs as the server's own service identity, so storage-level permissions are checked against the service principal rather than the connecting user (SPARK-5159). Spark 4.4 logs a warning for the same configuration. To restore the previous behavior, set `spark.sql.hive.thriftServer.allowIneffectiveDoAs` to `true`. Setting `hive.server2.enable.doAs` to `false` also starts the server, but note that it stops impersonating metastore calls as well, so it is not a no-op.
2829

2930
## Upgrading from Spark SQL 4.3 to 4.4

‎sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala‎

Lines changed: 19 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3860,6 +3860,24 @@ class Analyzer(
38603860
Project(windowOps.output ++ newExpressionsWithWindowFunctions, windowOps)
38613861
} // end of addWindow
38623862

3863+
/**
3864+
* Extracts all nested window expressions and places the HAVING filter below every Window.
3865+
*/
3866+
private def addWindowWithHaving(
3867+
aggregateExpressions: Seq[NamedExpression],
3868+
groupingExpressions: Seq[Expression],
3869+
child: LogicalPlan,
3870+
condition: Expression): LogicalPlan = {
3871+
val (windowExpressions, regularExpressions) =
3872+
extract(aggregateExpressions, groupingExpressions)
3873+
val windowChild = if (hasWindowFunction(regularExpressions)) {
3874+
addWindowWithHaving(regularExpressions, groupingExpressions, child, condition)
3875+
} else {
3876+
Filter(condition, Aggregate(groupingExpressions, regularExpressions, child))
3877+
}
3878+
addWindow(windowExpressions, windowChild)
3879+
}
3880+
38633881
// We have to use transformDown at here to make sure the rule of
38643882
// "Aggregate with Having clause" will be triggered.
38653883
def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperatorsDownWithPruning(
@@ -3883,12 +3901,7 @@ class Analyzer(
38833901
throw QueryCompilationErrors.lateralColumnAliasInAggWithWindowAndHavingUnsupportedError(
38843902
lcaRef.nameParts)
38853903
})
3886-
val (windowExpressions, aggregateExpressions) = extract(aggregateExprs, groupingExprs)
3887-
// Create an Aggregate operator to evaluate aggregation functions.
3888-
val withAggregate = Aggregate(groupingExprs, aggregateExpressions, child)
3889-
// Add a Filter operator for conditions in the Having clause.
3890-
val withFilter = Filter(condition, withAggregate)
3891-
val withWindow = addWindow(windowExpressions, withFilter)
3904+
val withWindow = addWindowWithHaving(aggregateExprs, groupingExprs, child, condition)
38923905

38933906
// Finally, generate output columns according to the original projectList.
38943907
val finalProjectList = aggregateExprs.map(_.toAttribute)

‎sql/core/src/test/resources/sql-tests/analyzer-results/window-resolution-edge-cases.sql.out‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -343,6 +343,27 @@ Project [col1#x, m#x]
343343
+- Relation spark_catalog.default.t1[col1#x,col2#x] parquet
344344

345345

346+
-- !query
347+
SELECT
348+
k,
349+
SUM(RANK() OVER (ORDER BY SUM(v) DESC)) OVER () AS rank_sum
350+
FROM VALUES ('drop', 2), ('keep', 1) AS input(k, v)
351+
GROUP BY k
352+
HAVING k = 'keep'
353+
ORDER BY k
354+
-- !query analysis
355+
Sort [k#x ASC NULLS FIRST], true
356+
+- Project [k#x, rank_sum#xL]
357+
+- Project [k#x, _w0#xL, _w0#x, _w0#x, rank_sum#xL, rank_sum#xL]
358+
+- Window [sum(_w0#x) windowspecdefinition(specifiedwindowframe(RowFrame, unboundedpreceding$(), unboundedfollowing$())) AS rank_sum#xL]
359+
+- Project [k#x, _w0#xL, _w0#x, _w0#x]
360+
+- Window [rank(_w0#xL) windowspecdefinition(_w0#xL DESC NULLS LAST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS _w0#x], [_w0#xL DESC NULLS LAST]
361+
+- Filter (k#x = keep)
362+
+- Aggregate [k#x], [k#x, sum(v#x) AS _w0#xL]
363+
+- SubqueryAlias input
364+
+- LocalRelation [k#x, v#x]
365+
366+
346367
-- !query
347368
SELECT col1 FROM t1 GROUP BY col1, col2 HAVING col2 > 0
348369
QUALIFY ROW_NUMBER() OVER (ORDER BY SUM(col2) OVER (PARTITION BY col1) DESC, col1) = 1

‎sql/core/src/test/resources/sql-tests/inputs/window-resolution-edge-cases.sql‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -99,6 +99,16 @@ SELECT col1, MAX(ROW_NUMBER() OVER w) OVER () AS max_rn FROM t1 WINDOW w AS (ORD
9999

100100
-- Nested windows over an aggregate, and in QUALIFY together with HAVING.
101101
SELECT col1, MAX(RANK() OVER (ORDER BY SUM(col2))) OVER () AS m FROM t1 GROUP BY col1;
102+
103+
-- HAVING must be evaluated before every nested window layer.
104+
SELECT
105+
k,
106+
SUM(RANK() OVER (ORDER BY SUM(v) DESC)) OVER () AS rank_sum
107+
FROM VALUES ('drop', 2), ('keep', 1) AS input(k, v)
108+
GROUP BY k
109+
HAVING k = 'keep'
110+
ORDER BY k;
111+
102112
SELECT col1 FROM t1 GROUP BY col1, col2 HAVING col2 > 0
103113
QUALIFY ROW_NUMBER() OVER (ORDER BY SUM(col2) OVER (PARTITION BY col1) DESC, col1) = 1;
104114

‎sql/core/src/test/resources/sql-tests/results/window-resolution-edge-cases.sql.out‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -223,6 +223,20 @@ struct<col1:int,m:int>
223223
3 3
224224

225225

226+
-- !query
227+
SELECT
228+
k,
229+
SUM(RANK() OVER (ORDER BY SUM(v) DESC)) OVER () AS rank_sum
230+
FROM VALUES ('drop', 2), ('keep', 1) AS input(k, v)
231+
GROUP BY k
232+
HAVING k = 'keep'
233+
ORDER BY k
234+
-- !query schema
235+
struct<k:string,rank_sum:bigint>
236+
-- !query output
237+
keep 1
238+
239+
226240
-- !query
227241
SELECT col1 FROM t1 GROUP BY col1, col2 HAVING col2 > 0
228242
QUALIFY ROW_NUMBER() OVER (ORDER BY SUM(col2) OVER (PARTITION BY col1) DESC, col1) = 1

0 commit comments

Comments
 (0)