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 4bc916c99a46 [SPARK-60459][SQL] Push down filters through ASOF JOIN
4bc916c99a46 is described below

commit 4bc916c99a46cc525d6b05458bbadedcdc43a0f7
Author: Luka Zdravić <[email protected]>
AuthorDate: Fri Oct 23 12:52:57 2026 +0000

    [SPARK-60459][SQL] Push down filters through ASOF JOIN
    
    ### What changes were proposed in this pull request?
    `PushPredicateThroughJoin` now moves these predicates below an `AsOfJoin`:
    - A `WHERE` predicate on the left side only.
    - An `ON` predicate on the right side only.
    - An `ON` predicate on the left side only, for an inner join only.
    
    A right-side `WHERE` predicate runs after that match, so moving it below 
the join would change which right row is the closest. A left outer join keeps 
every left row, so a left-side `ON` predicate stays in the join.
    
    ### Why are the changes needed?
    No predicate moved below an `AsOfJoin`. So every row was shuffled and 
sorted before a filter ran, and the scans got no data filters for file skipping.
    
    ```sql
    SELECT * FROM trades t LEFT ASOF JOIN quotes q
      MATCH_CONDITION (t.ts >= q.ts) ON t.sym = q.sym AND q.price > 0
    WHERE t.qty > 100
    ```
    
    Before: both filters run at or after the join. After: `t.qty > 100` filters 
`trades` and `q.price > 0` filters `quotes` before the join, and both reach the 
scans.
    
    ### Does this PR introduce _any_ user-facing change?
    No. Query results stay the same. Only the plan changes.
    
    ### How was this patch tested?
    New `FilterPushdownSuite` tests check which `WHERE` and `ON` predicates 
move, for inner and left outer joins. They also check that non-deterministic 
and `raise_error` predicates stay. New `AsOfJoinSortMergeSQLSuite` tests on 
parquet tables check the results, that a left-side `WHERE` reaches the left 
scan, and that a right-side `WHERE` gives a different answer than the same 
predicate in `ON`.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    Generated-by: Claude Code
    
    Closes #59702 from LukaZdravic/asof-filter-pushdown.
    
    Authored-by: Luka Zdravić <[email protected]>
    Signed-off-by: Uros Bojanic <[email protected]>
---
 .../spark/sql/catalyst/optimizer/Optimizer.scala   |  38 ++++++-
 .../catalyst/optimizer/FilterPushdownSuite.scala   | 116 +++++++++++++++++++++
 .../spark/sql/AsOfJoinSortMergeSQLSuite.scala      |  88 ++++++++++++++++
 3 files changed, 241 insertions(+), 1 deletion(-)

diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
index 02e947bb58e6..3bb998dca253 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
@@ -2553,7 +2553,7 @@ object PruneFilters extends Rule[LogicalPlan] with 
PredicateHelper {
  */
 object PushDownPredicates extends Rule[LogicalPlan] {
   def apply(plan: LogicalPlan): LogicalPlan = plan.transformWithPruning(
-    _.containsAnyPattern(FILTER, JOIN)) {
+    _.containsAnyPattern(FILTER, JOIN, AS_OF_JOIN)) {
     CombineFilters.applyLocally
       .orElse(PushPredicateThroughNonJoin.applyLocally)
       .orElse(PushPredicateThroughJoin.applyLocally)
@@ -2975,6 +2975,8 @@ object PushPredicateThroughNonJoin extends 
Rule[LogicalPlan] with PredicateHelpe
  * And also pushes down the join filter, where the `condition` can be 
evaluated using only the
  * attributes of the left or right side of sub query when applicable.
  *
+ * Also pushes the safe predicates through an [[AsOfJoin]]
+ *
  * Check https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior 
for more details
  */
 object PushPredicateThroughJoin extends Rule[LogicalPlan] with PredicateHelper 
{
@@ -3091,6 +3093,40 @@ object PushPredicateThroughJoin extends 
Rule[LogicalPlan] with PredicateHelper {
         case other =>
           throw SparkException.internalError(s"Unexpected join type: $other")
       }
+    // Left-side WHERE predicate can run before the ASOF join, but a 
right-side one cannot
+    case f @ Filter(filterCondition, j: AsOfJoin) =>
+      val (leftFilterConditions, rightFilterConditions, commonFilterCondition) 
=
+        split(splitConjunctivePredicates(filterCondition), j.left, j.right)
+      if (leftFilterConditions.isEmpty) {
+        f
+      } else {
+        val newJoin = j.copy(left = 
Filter(leftFilterConditions.reduceLeft(And), j.left))
+        (rightFilterConditions ++ commonFilterCondition).
+          reduceLeftOption(And).map(Filter(_, newJoin)).getOrElse(newJoin)
+      }
+
+    // Right-side ON predicates can filter the right input first.
+    // Left-side ON predicates can filter the left input only for an inner join
+    // because a left outer join keeps every left row.
+    case j: AsOfJoin =>
+      val (leftJoinConditions, rightJoinConditions, commonJoinCondition) =
+        split(j.condition.map(splitConjunctivePredicates).getOrElse(Nil), 
j.left, j.right)
+      val (pushedLeftConditions, keptLeftConditions) = j.joinType match {
+        case Inner => (leftJoinConditions, Nil)
+        case LeftOuter => (Nil, leftJoinConditions)
+        case other =>
+          throw SparkException.internalError(s"Unexpected as-of join type: 
$other")
+      }
+      if (pushedLeftConditions.isEmpty && rightJoinConditions.isEmpty) {
+        j
+      } else {
+        val newLeft = pushedLeftConditions.
+          reduceLeftOption(And).map(Filter(_, j.left)).getOrElse(j.left)
+        val newRight = rightJoinConditions.
+          reduceLeftOption(And).map(Filter(_, j.right)).getOrElse(j.right)
+        j.copy(left = newLeft, right = newRight,
+          condition = (keptLeftConditions ++ 
commonJoinCondition).reduceLeftOption(And))
+      }
   }
 }
 
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
index 601f8e36f624..27ad4da6a917 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
@@ -1098,6 +1098,122 @@ class FilterPushdownSuite extends PlanTest {
     comparePlans(optimized, correctAnswer)
   }
 
+  private val asOfLeft = LocalRelation($"lt".int, $"lk".int, $"lv".int)
+  private val asOfRight = LocalRelation($"rt".int, $"rk".int, $"rv".int)
+  private val leftKey = asOfLeft.output(1)
+  private val leftValue = asOfLeft.output(2)
+  private val rightKey = asOfRight.output(1)
+  private val rightValue = asOfRight.output(2)
+
+  // A backward as-of join on lt >= rt. Only the inputs, the ON condition, and 
the type vary.
+  private def asOfJoin(
+      left: LogicalPlan,
+      right: LogicalPlan,
+      condition: Option[Expression],
+      joinType: JoinType): LogicalPlan = {
+    AsOfJoin(left, right, asOfLeft.output(0), asOfRight.output(0), condition, 
joinType,
+      tolerance = None, allowExactMatches = true, direction = 
AsOfJoinDirection("backward"))
+  }
+
+  test("as-of joins: push a left-side where condition into the left side") {
+    Seq(Inner, LeftOuter).foreach { joinType =>
+      val originalQuery = asOfJoin(asOfLeft, asOfRight, Some(leftKey === 
rightKey), joinType)
+        .where(leftValue > 1)
+        .analyze
+      val optimized = Optimize.execute(originalQuery)
+      val correctAnswer = asOfJoin(
+        asOfLeft.where(leftValue > 1), asOfRight, Some(leftKey === rightKey), 
joinType)
+        .analyze
+      comparePlans(optimized, correctAnswer)
+    }
+  }
+
+  test("as-of joins: keep right-side and two-side where conditions above the 
join") {
+    Seq(Inner, LeftOuter).foreach { joinType =>
+      val join = asOfJoin(asOfLeft, asOfRight, Some(leftKey === rightKey), 
joinType)
+      // A right-side filter must run after the match, as it changes which 
right row is closest.
+      val rightOnlyQuery = join.where(rightValue > 2).analyze
+      comparePlans(Optimize.execute(rightOnlyQuery), rightOnlyQuery)
+
+      val mixedQuery = join.where(leftValue > 1 && rightValue > 2 && leftValue 
> rightValue)
+        .analyze
+      val correctAnswer = asOfJoin(
+        asOfLeft.where(leftValue > 1), asOfRight, Some(leftKey === rightKey), 
joinType)
+        .where(rightValue > 2 && leftValue > rightValue)
+        .analyze
+      comparePlans(Optimize.execute(mixedQuery), correctAnswer)
+    }
+  }
+
+  test("as-of joins: do not push down non-deterministic where conditions") {
+    val originalQuery = asOfJoin(asOfLeft, asOfRight, Some(leftKey === 
rightKey), Inner)
+      .where(Rand(10) > 0.5)
+      .analyze
+    comparePlans(Optimize.execute(originalQuery), originalQuery)
+  }
+
+  test("as-of joins: do not push down predicates with raise_error") {
+    // raise_error over a literal references no columns, so it looks like a 
left-side predicate.
+    // Below the join it would fire on left rows that the join drops.
+    val raiseError = IsNull(RaiseError(Literal("boom")))
+    val whereQuery = asOfJoin(asOfLeft, asOfRight, Some(leftKey === rightKey), 
Inner)
+      .where(raiseError)
+      .analyze
+    comparePlans(Optimize.execute(whereQuery), whereQuery)
+
+    val onQuery = asOfJoin(asOfLeft, asOfRight, Some(leftKey === rightKey && 
raiseError), Inner)
+      .analyze
+    comparePlans(Optimize.execute(onQuery), onQuery)
+  }
+
+  test("as-of joins: push a right-side join condition into the right side") {
+    Seq(Inner, LeftOuter).foreach { joinType =>
+      val originalQuery = asOfJoin(
+        asOfLeft, asOfRight, Some(leftKey === rightKey && rightValue > 2), 
joinType)
+        .analyze
+      val optimized = Optimize.execute(originalQuery)
+      val correctAnswer = asOfJoin(
+        asOfLeft, asOfRight.where(rightValue > 2), Some(leftKey === rightKey), 
joinType)
+        .analyze
+      comparePlans(optimized, correctAnswer)
+    }
+  }
+
+  test("as-of joins: push a left-side join condition only for an inner join") {
+    val condition = Some(leftKey === rightKey && leftValue > 1)
+    val innerQuery = asOfJoin(asOfLeft, asOfRight, condition, Inner).analyze
+    val correctAnswer = asOfJoin(
+      asOfLeft.where(leftValue > 1), asOfRight, Some(leftKey === rightKey), 
Inner)
+      .analyze
+    comparePlans(Optimize.execute(innerQuery), correctAnswer)
+
+    // A left outer join keeps every left row, so the condition must stay in 
the join.
+    val leftOuterQuery = asOfJoin(asOfLeft, asOfRight, condition, 
LeftOuter).analyze
+    comparePlans(Optimize.execute(leftOuterQuery), leftOuterQuery)
+  }
+
+  test("as-of joins: push a where condition and a join condition together") {
+    val originalQuery = asOfJoin(
+      asOfLeft, asOfRight, Some(leftKey === rightKey && rightValue > 2), 
LeftOuter)
+      .where(leftValue > 1)
+      .analyze
+    val correctAnswer = asOfJoin(
+      asOfLeft.where(leftValue > 1), asOfRight.where(rightValue > 2),
+      Some(leftKey === rightKey), LeftOuter)
+      .analyze
+    comparePlans(Optimize.execute(originalQuery), correctAnswer)
+  }
+
+  test("as-of joins: PushDownPredicates visits an as-of join with no filter 
above it") {
+    val originalQuery = asOfJoin(
+      asOfLeft, asOfRight, Some(leftKey === rightKey && rightValue > 2), Inner)
+      .analyze
+    val correctAnswer = asOfJoin(
+      asOfLeft, asOfRight.where(rightValue > 2), Some(leftKey === rightKey), 
Inner)
+      .analyze
+    comparePlans(PushDownPredicates(originalQuery), correctAnswer)
+  }
+
   val testRelationWithArrayType = LocalRelation($"a".int, $"b".int, 
$"c_arr".array(IntegerType))
 
   test("generate: predicate referenced no generated column") {
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/AsOfJoinSortMergeSQLSuite.scala 
b/sql/core/src/test/scala/org/apache/spark/sql/AsOfJoinSortMergeSQLSuite.scala
index 42ec642e5d27..ba68e9b7cde6 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/AsOfJoinSortMergeSQLSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/AsOfJoinSortMergeSQLSuite.scala
@@ -20,6 +20,7 @@ package org.apache.spark.sql
 import java.sql.{Date, Timestamp}
 
 import org.apache.spark.sql.catalyst.plans.{AsOfJoinDirection, Backward, 
Forward}
+import org.apache.spark.sql.execution.FileSourceScanExec
 import org.apache.spark.sql.execution.adaptive.AdaptiveSparkPlanHelper
 import org.apache.spark.sql.execution.joins.SortMergeAsOfJoinExec
 import org.apache.spark.sql.internal.SQLConf
@@ -86,6 +87,26 @@ class AsOfJoinSortMergeSQLSuite extends QueryTest
         |""".stripMargin)
   }
 
+  /** Copies the trade and quote views into parquet tables, so a pushed filter 
reaches a scan. */
+  private def withTradeQuoteTables(f: => Unit): Unit = {
+    setupTradeQuoteViews()
+    withTable("trades_t", "quotes_t") {
+      sql("CREATE TABLE trades_t USING parquet AS SELECT * FROM trades")
+      sql("CREATE TABLE quotes_t USING parquet AS SELECT * FROM quotes")
+      f
+    }
+  }
+
+  /** Names of the columns that the scan of `table` filters on. */
+  private def scanFilterColumns(df: DataFrame, table: String): Set[String] = {
+    val plan = df.queryExecution.executedPlan
+    val scans = collect(plan) {
+      case scan: FileSourceScanExec if scan.tableIdentifier.exists(_.table == 
table) => scan
+    }
+    assert(scans.length == 1, s"Expected one scan of $table in plan:\n$plan")
+    scans.head.dataFilters.flatMap(_.references.map(_.name)).toSet
+  }
+
   /** Runs `f` with temp view `r` over `rows`, kept in input order so ties 
keep that order. */
   private def withOrderedRightView(rows: String, columns: String)(f: => Unit): 
Unit = {
     withTempView("r") {
@@ -953,6 +974,73 @@ class AsOfJoinSortMergeSQLSuite extends QueryTest
       Row(4L) :: Nil)
   }
 
+  test("WHERE on the left input is pushed into the left scan") {
+    withTradeQuoteTables {
+      val query = sql(
+        """
+          |SELECT t.trade_time, t.symbol, q.bid_price
+          |FROM trades_t t LEFT ASOF JOIN quotes_t q
+          |  MATCH_CONDITION (t.trade_time >= q.quote_time)
+          |  ON t.symbol = q.symbol
+          |WHERE t.quantity > 100
+          |""".stripMargin)
+      checkSortMergeAsOf(
+        query,
+        Row(Timestamp.valueOf("2026-06-29 10:00:11"), "AAPL", 180.20) :: Nil)
+      assert(scanFilterColumns(query, "trades_t").contains("quantity"))
+    }
+  }
+
+  test("WHERE on the right input runs after the closest match, unlike the same 
ON predicate") {
+    withTradeQuoteTables {
+      // The 10:00:11 trade's closest quote is 180.20. WHERE drops the trade, 
while ON skips
+      // that quote and matches the older 180.15 one.
+      val whereQuery = sql(
+        """
+          |SELECT t.trade_time, q.bid_price
+          |FROM trades_t t ASOF JOIN quotes_t q
+          |  MATCH_CONDITION (t.trade_time >= q.quote_time)
+          |  ON t.symbol = q.symbol
+          |WHERE q.bid_price < 180.18
+          |""".stripMargin)
+      checkSortMergeAsOf(
+        whereQuery,
+        Row(Timestamp.valueOf("2026-06-29 10:00:05"), 180.10) :: Nil)
+      assert(!scanFilterColumns(whereQuery, "quotes_t").contains("bid_price"))
+
+      val onQuery = sql(
+        """
+          |SELECT t.trade_time, q.bid_price
+          |FROM trades_t t ASOF JOIN quotes_t q
+          |  MATCH_CONDITION (t.trade_time >= q.quote_time)
+          |  ON t.symbol = q.symbol AND q.bid_price < 180.18
+          |""".stripMargin)
+      checkSortMergeAsOf(
+        onQuery,
+        Seq(
+          Row(Timestamp.valueOf("2026-06-29 10:00:05"), 180.10),
+          Row(Timestamp.valueOf("2026-06-29 10:00:11"), 180.15)))
+      assert(scanFilterColumns(onQuery, "quotes_t").contains("bid_price"))
+    }
+  }
+
+  test("ON predicate on the left input keeps every row of a LEFT ASOF JOIN") {
+    setupTradeQuoteViews()
+    checkSortMergeAsOf(
+      sql(
+        """
+          |SELECT t.trade_time, q.bid_price
+          |FROM trades t LEFT ASOF JOIN quotes q
+          |  MATCH_CONDITION (t.trade_time >= q.quote_time)
+          |  ON t.symbol = q.symbol AND t.quantity > 100
+          |""".stripMargin),
+      Seq(
+        Row(Timestamp.valueOf("2026-06-29 09:59:59"), null),
+        Row(Timestamp.valueOf("2026-06-29 10:00:05"), null),
+        Row(Timestamp.valueOf("2026-06-29 10:00:11"), 180.20),
+        Row(Timestamp.valueOf("2026-06-29 10:00:12"), null)))
+  }
+
   test("LEFT ASOF JOIN null-pads NOT NULL right catalog columns") {
     withTable("right_nn", "left_nn") {
       sql("CREATE TABLE right_nn (k INT NOT NULL, v STRING NOT NULL) USING 
parquet")


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to