This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 22bffbbf0b7a [SPARK-58503][SQL] Evaluate HAVING before window
functions with generators
22bffbbf0b7a is described below
commit 22bffbbf0b7a1a15c3aec8b79dffce2f0afd87fa
Author: Vladimir Golubev <[email protected]>
AuthorDate: Sun Aug 2 22:02:27 2026 +0000
[SPARK-58503][SQL] Evaluate HAVING before window functions with generators
### What changes were proposed in this pull request?
Place a resolved HAVING filter below the complete Window chain when
generator extraction has
inserted Project and Generate operators above it. If the condition's
attributes are not available
below every Window, retain the existing fallback placement.
Add regression coverage for HAVING conditions on grouping columns and
aggregate aliases, including
multiple window specifications, and document the corrected behavior in the
SQL migration guide.
### Why are the changes needed?
Generator extraction can hide the Window chain from the analyzer's normal
HAVING handling. The
remaining fallback then places HAVING above Window, so window functions see
groups that HAVING
removes.
### Does this PR introduce _any_ user-facing change?
Yes. Window functions are now evaluated after HAVING for queries that also
select generators. For
example, the window count from this query changes from 4 to the correct
value 3:
```sql
SELECT explode(array(a)), count(*) OVER (), a
FROM VALUES (1), (2), (3), (NULL) AS t(a)
GROUP BY a
HAVING a IS NOT NULL;
```
### How was this patch tested?
Added SQL execution and analyzer golden coverage and ran:
```
build/sbt "sql/testOnly org.apache.spark.sql.SQLQueryTestSuite -- -z
generators-resolution-edge-cases.sql"
```
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: OpenAI Codex (GPT-5)
Closes #57704 from vladimirg-db/fix-having-before-window.
Authored-by: Vladimir Golubev <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
---
docs/sql-migration-guide.md | 1 +
.../spark/sql/catalyst/analysis/Analyzer.scala | 45 +++++++++++++++-
.../generators-resolution-edge-cases.sql.out | 60 ++++++++++++++++++++++
.../inputs/generators-resolution-edge-cases.sql | 26 ++++++++++
.../generators-resolution-edge-cases.sql.out | 46 +++++++++++++++++
5 files changed, 177 insertions(+), 1 deletion(-)
diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 5595cc09ab86..f25295ad5349 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -39,6 +39,7 @@ license: |
- Since Spark 4.3, `unix_seconds`, `unix_millis`, and `unix_micros` accept
`TIMESTAMP_NTZ` and the nanosecond-precision timestamp types directly, reading
them with no time-zone shift. Previously these functions accepted only
`TIMESTAMP_LTZ`; a `TIMESTAMP_NTZ` or nanosecond-timestamp argument was
rejected with a `DATATYPE_MISMATCH` error. This is a new capability and does
not change the result of any query that previously succeeded.
- Since Spark 4.3, `hash()` and `xxhash64()` include the `days` field of
`CalendarInterval` when computing the hash, so their output for interval values
differs from earlier releases. Previously the codegen path dropped `days`,
disagreeing with interpreted evaluation.
- Since Spark 4.3, when a `SELECT` or `INSERT` statement references the same
table more than once with different `WITH (...)` options (for example a
self-join, or `INSERT INTO t WITH (...) SELECT * FROM t WITH (...)`), each
reference now uses its own options instead of the second reference silently
inheriting the first reference's options via the analyzer's relation cache.
+- Since Spark 4.3, `HAVING` is evaluated before window functions when the
`SELECT` list also contains generator functions such as `explode`. Previously,
window functions could include groups removed by `HAVING` and produce incorrect
results.
## Upgrading from Spark SQL 4.1 to 4.2
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
index b7dcc7e4d296..8c02c92634ba 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
@@ -4706,7 +4706,50 @@ object ResolveUnresolvedHaving extends Rule[LogicalPlan]
{
plan.resolveOperatorsWithPruning(_.containsPattern(UNRESOLVED_HAVING),
ruleId) {
case u @ UnresolvedHaving(havingCondition, child)
if havingCondition.resolved && child.resolved =>
- Filter(condition = havingCondition, child = child)
+ val filter = Filter(condition = havingCondition, child = child)
+ insertFilterBeforeWindow(filter).getOrElse(filter)
+ }
+ }
+
+ /**
+ * Searches through Project and Generate nodes for a Window chain and places
HAVING below every
+ * Window in that chain. This restores SQL clause order for plans produced
by queries such as:
+ *
+ * {{{
+ * SELECT explode(array(a)), count(*) OVER ()
+ * FROM VALUES (1), (2), (NULL) AS t(a)
+ * GROUP BY a
+ * HAVING a IS NOT NULL
+ * }}}
+ *
+ * Returns None unless the condition can be evaluated below every Window in
the chain.
+ */
+ private def insertFilterBeforeWindow(filter: Filter): Option[LogicalPlan] =
filter.child match {
+ case project: Project =>
+ insertFilterBeforeWindow(filter.copy(child = project.child))
+ .map(child => project.withNewChildren(Seq(child)))
+ case generate: Generate =>
+ insertFilterBeforeWindow(filter.copy(child = generate.child))
+ .map(child => generate.withNewChildren(Seq(child)))
+ case window: Window =>
+ insertFilterBeforeWindowChain(filter, window)
+ case _ =>
+ None
+ }
+
+ private def insertFilterBeforeWindowChain(
+ filter: Filter,
+ window: Window): Option[LogicalPlan] = {
+ if (!filter.condition.references.subsetOf(window.child.outputSet)) {
+ None
+ } else {
+ val child = window.child match {
+ case childWindow: Window =>
+ insertFilterBeforeWindowChain(filter, childWindow)
+ case child =>
+ Some(filter.copy(child = child))
+ }
+ child.map(child => window.withNewChildren(Seq(child)))
}
}
}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/generators-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/generators-resolution-edge-cases.sql.out
index 0b21d6e6b85d..00930b19cd41 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/generators-resolution-edge-cases.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/generators-resolution-edge-cases.sql.out
@@ -781,3 +781,63 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "val"
} ]
}
+
+
+-- !query
+SELECT explode(array(a)) AS col, count(*) OVER () AS cnt, a
+FROM VALUES (1), (2), (3), (NULL) AS t(a)
+GROUP BY a
+HAVING a IS NOT NULL
+-- !query analysis
+Project [col#x, cnt#xL, a#x]
++- Generate explode(_gen_input_0#x), false, [col#x]
+ +- Project [_gen_input_0#x, cnt#xL, a#x]
+ +- Project [_gen_input_0#x, a#x, cnt#xL, cnt#xL]
+ +- Window [count(1)
windowspecdefinition(specifiedwindowframe(RowFrame, unboundedpreceding$(),
unboundedfollowing$())) AS cnt#xL]
+ +- Filter isnotnull(a#x)
+ +- Aggregate [a#x], [array(a#x) AS _gen_input_0#x, a#x]
+ +- SubqueryAlias t
+ +- LocalRelation [a#x]
+
+
+-- !query
+SELECT explode(array(a)) AS col,
+ count(*) OVER () AS group_count,
+ count(*) AS row_count
+FROM VALUES (1), (1), (2), (3), (3) AS t(a)
+GROUP BY a
+HAVING row_count > 1
+-- !query analysis
+Project [col#x, group_count#xL, row_count#xL]
++- Generate explode(_gen_input_0#x), false, [col#x]
+ +- Project [_gen_input_0#x, group_count#xL, row_count#xL]
+ +- Project [_gen_input_0#x, row_count#xL, group_count#xL, group_count#xL]
+ +- Window [count(1)
windowspecdefinition(specifiedwindowframe(RowFrame, unboundedpreceding$(),
unboundedfollowing$())) AS group_count#xL]
+ +- Filter (row_count#xL > cast(1 as bigint))
+ +- Aggregate [a#x], [array(a#x) AS _gen_input_0#x, count(1) AS
row_count#xL]
+ +- SubqueryAlias t
+ +- LocalRelation [a#x]
+
+
+-- !query
+SELECT explode(array(a)) AS col,
+ count(*) OVER () AS cnt,
+ count(*) OVER (
+ ORDER BY a
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ ) AS ordered_cnt,
+ a
+FROM VALUES (1), (2), (3), (NULL) AS t(a)
+GROUP BY a
+HAVING a IS NOT NULL
+-- !query analysis
+Project [col#x, cnt#xL, ordered_cnt#xL, a#x]
++- Generate explode(_gen_input_0#x), false, [col#x]
+ +- Project [_gen_input_0#x, cnt#xL, ordered_cnt#xL, a#x]
+ +- Project [_gen_input_0#x, a#x, cnt#xL, ordered_cnt#xL, cnt#xL,
ordered_cnt#xL]
+ +- Window [count(1) windowspecdefinition(a#x ASC NULLS FIRST,
specifiedwindowframe(RowFrame, unboundedpreceding$(), unboundedfollowing$()))
AS ordered_cnt#xL], [a#x ASC NULLS FIRST]
+ +- Window [count(1)
windowspecdefinition(specifiedwindowframe(RowFrame, unboundedpreceding$(),
unboundedfollowing$())) AS cnt#xL]
+ +- Filter isnotnull(a#x)
+ +- Aggregate [a#x], [array(a#x) AS _gen_input_0#x, a#x]
+ +- SubqueryAlias t
+ +- LocalRelation [a#x]
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/generators-resolution-edge-cases.sql
b/sql/core/src/test/resources/sql-tests/inputs/generators-resolution-edge-cases.sql
index 67b5ae77c336..5928ccea56e7 100644
---
a/sql/core/src/test/resources/sql-tests/inputs/generators-resolution-edge-cases.sql
+++
b/sql/core/src/test/resources/sql-tests/inputs/generators-resolution-edge-cases.sql
@@ -215,3 +215,29 @@ SELECT posexplode(array('x', 'y')) as (pos, val), pos, val
FROM (VALUES (42)) AS
-- generator's multi-alias does not shadow table column with aggregate
SELECT posexplode(array('x', 'y')) as (pos, val), pos, val, count(*) FROM
(VALUES (42)) AS t(pos) GROUP BY pos;
+
+-- HAVING on a grouping key should be evaluated before a window function with
a generator
+SELECT explode(array(a)) AS col, count(*) OVER () AS cnt, a
+FROM VALUES (1), (2), (3), (NULL) AS t(a)
+GROUP BY a
+HAVING a IS NOT NULL;
+
+-- HAVING on an aggregate should be evaluated before a window function with a
generator
+SELECT explode(array(a)) AS col,
+ count(*) OVER () AS group_count,
+ count(*) AS row_count
+FROM VALUES (1), (1), (2), (3), (3) AS t(a)
+GROUP BY a
+HAVING row_count > 1;
+
+-- HAVING should be evaluated before multiple window functions with a generator
+SELECT explode(array(a)) AS col,
+ count(*) OVER () AS cnt,
+ count(*) OVER (
+ ORDER BY a
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ ) AS ordered_cnt,
+ a
+FROM VALUES (1), (2), (3), (NULL) AS t(a)
+GROUP BY a
+HAVING a IS NOT NULL;
diff --git
a/sql/core/src/test/resources/sql-tests/results/generators-resolution-edge-cases.sql.out
b/sql/core/src/test/resources/sql-tests/results/generators-resolution-edge-cases.sql.out
index 7dc50cfb4aac..7876b772b720 100644
---
a/sql/core/src/test/resources/sql-tests/results/generators-resolution-edge-cases.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/generators-resolution-edge-cases.sql.out
@@ -781,3 +781,49 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "val"
} ]
}
+
+
+-- !query
+SELECT explode(array(a)) AS col, count(*) OVER () AS cnt, a
+FROM VALUES (1), (2), (3), (NULL) AS t(a)
+GROUP BY a
+HAVING a IS NOT NULL
+-- !query schema
+struct<col:int,cnt:bigint,a:int>
+-- !query output
+1 3 1
+2 3 2
+3 3 3
+
+
+-- !query
+SELECT explode(array(a)) AS col,
+ count(*) OVER () AS group_count,
+ count(*) AS row_count
+FROM VALUES (1), (1), (2), (3), (3) AS t(a)
+GROUP BY a
+HAVING row_count > 1
+-- !query schema
+struct<col:int,group_count:bigint,row_count:bigint>
+-- !query output
+1 2 2
+3 2 2
+
+
+-- !query
+SELECT explode(array(a)) AS col,
+ count(*) OVER () AS cnt,
+ count(*) OVER (
+ ORDER BY a
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ ) AS ordered_cnt,
+ a
+FROM VALUES (1), (2), (3), (NULL) AS t(a)
+GROUP BY a
+HAVING a IS NOT NULL
+-- !query schema
+struct<col:int,cnt:bigint,ordered_cnt:bigint,a:int>
+-- !query output
+1 3 3 1
+2 3 3 2
+3 3 3 3
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]