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]

Reply via email to