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]