You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "vanekjar (via GitHub)" <gi...@apache.org> on 2025/10/23 01:14:50 UTC
[PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infers from complex join expressions [spark]
vanekjar opened a new pull request, #52699:
URL: https://github.com/apache/spark/pull/52699
## What changes were proposed in this pull request?
This PR improves the Spark SQL optimizer’s `InferFiltersFromConstraints` rule to infer filter conditions from join constraints that involve complex expressions, not just simple attribute equalities.
Currently, the optimizer can only infer additional constraints when the join condition is a simple equality (e.g., `a = b`). For more complex expressions, such as arithmetic operations, it does not infer the corresponding filter.
### Example (currently works as expected):
```sql
SELECT *
FROM t1
JOIN t2 ON t1.a = t2.b
WHERE t2.b = 1
```
In this case, the optimizer correctly infers the additional constraint `t1.a = 1`.
### Example (now handled by this PR):
```sql
SELECT *
FROM t1
JOIN t2 ON t1.a = t2.b + 2
WHERE t2.b = 1
```
Here, it is clear that `t1.a = 3` (since `t2.b = 1` and `t1.a = t2.b + 2`), but previously the optimizer did not infer this constraint. With this change, the optimizer can now deduce and push down `t1.a = 3`.
## How was this patch tested?
You can reproduce and verify the improvement with the following:
```scala
spark.sql("CREATE TABLE t1(a INT)")
spark.sql("CREATE TABLE t2(b INT)")
spark.sql("""
SELECT *
FROM t1
INNER JOIN t2 ON t2.b = t1.a + 2
WHERE t1.a = 1
""").explain
```
**Before this change, the physical plan does not include the inferred filter:**
```
== Physical Plan ==
AdaptiveSparkPlan
+- BroadcastHashJoin [(a#2 + 2)], [b#3], Inner, BuildRight, false
:- Filter (isnotnull(a#2) AND (a#2 = 1))
: +- FileScan spark_catalog.default.t1[a#2]
+- Filter isnotnull(b#3)
+- FileScan spark_catalog.default.t2[b#3]
```
**With this PR, the optimizer should infer and push down `t2.b = 3` as an additional filter.**
```
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- BroadcastHashJoin [(a#2 + 2)], [b#3], Inner, BuildRight, false
:- Filter (isnotnull(a#2) AND (a#2 = 1))
: +- FileScan spark_catalog.default.t1[a#2]
+- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=27]
+- Filter ((b#3 = 3) AND isnotnull(b#3))
+- FileScan spark_catalog.default.t2[b#3]
```
## Why are the changes needed?
Without this enhancement, the optimizer cannot push down filters or optimize query execution plans for queries with complex join conditions, which can lead to suboptimal performance.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #52699: [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions
URL: https://github.com/apache/spark/pull/52699
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "andylam-db (via GitHub)" <gi...@apache.org>.
andylam-db commented on code in PR #52699:
URL: https://github.com/apache/spark/pull/52699#discussion_r2479407383
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/QueryPlanConstraints.scala:
##########
@@ -77,6 +87,43 @@ trait ConstraintHelper {
inferredConstraints ++= replaceConstraints(predicates - eq - EqualNullSafe(l, r), l, r)
case _ => // No inference
}
+
+ // Step 2: Infer by constant substitution (e.g., a = 5, b = a + 3 => b = 5 + 3)
+ val equalityPredicates = predicates.toSeq.flatMap {
+ case e @ EqualTo(left: AttributeReference, right: Literal) => Some(((left, right), e))
+ case e @ EqualTo(left: Literal, right: AttributeReference) => Some(((right, left), e))
+ case _ => None
+ }
+ if (equalityPredicates.nonEmpty) {
+ val constantsMap = AttributeMap(equalityPredicates.map(_._1))
+ val predicateSet = equalityPredicates.map(_._2).toSet
+ def replaceConstantsInExpression(expression: Expression) = expression transform {
+ case a: AttributeReference =>
+ constantsMap.get(a) match {
+ case Some(literal) => literal
+ case None => a
+ }
+ }
+ predicates.foreach { cond =>
+ val replaced = cond transform {
+ // attribute equality is handled above, no need to replace
+ case e @ EqualTo(_: Attribute, _: Attribute) => e
+ case e @ EqualTo(_: Cast, _: Attribute) => e
+ case e @ EqualTo(_: Attribute, _: Cast) => e
+
+ case e @ EqualTo(_, _) if !predicateSet.contains(e) => replaceConstantsInExpression(e)
+ }
+ // Avoid inferring tautologies like 1 = 1
+ val isTautology = replaced match {
+ case EqualTo(left: Expression, right: Expression) if left.foldable && right.foldable =>
+ left.eval() == right.eval()
+ case _ => false
+ }
Review Comment:
This is a little complicated and potentially non-performant given that we have to do expression evaluation in the driver during compilation.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #52699: [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions
URL: https://github.com/apache/spark/pull/52699
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #52699:
URL: https://github.com/apache/spark/pull/52699#issuecomment-3900298464
We're closing this PR because it hasn't been updated in a while. This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable.
If you'd like to revive this PR, please reopen it and ask a committer to remove the Stale tag!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "vanekjar (via GitHub)" <gi...@apache.org>.
vanekjar commented on PR #52699:
URL: https://github.com/apache/spark/pull/52699#issuecomment-3488236942
@andylam-db Thanks for you suggestion, I simplified the code to reuse logic from `ConstantPropgation` rule. I introduced `ConstantPropagationHelper` to make the logic reusable across optimizer rules.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "andylam-db (via GitHub)" <gi...@apache.org>.
andylam-db commented on code in PR #52699:
URL: https://github.com/apache/spark/pull/52699#discussion_r2823597551
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala:
##########
@@ -162,31 +269,31 @@ object ConstantPropagation extends Rule[LogicalPlan] {
* 2. AttributeMap: propagated mapping of attribute => constant
*/
private def traverse(condition: Expression, replaceChildren: Boolean, nullIsFalse: Boolean)
- : (Option[Expression], AttributeMap[(Literal, BinaryComparison)]) =
+ : (Option[Expression], AttributeMap[(Literal, BinaryComparison)]) =
Review Comment:
Maybe we can write the following function and call it from InferFiltersFromConstraints?
```
/**
* Substitutes [[Attribute Attributes]] which can be statically evaluated with their corresponding
* value in conjunctive [[Expression Expressions]]. This opens up the main logic in this rule to
* be used externally.
*/
def replaceAttributesWithConstants(condition: Expression): Option[Expression] = {
val (newCondition, _) = traverse(condition, replaceChildren = true, nullIsFalse = true)
newCondition
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "andylam-db (via GitHub)" <gi...@apache.org>.
andylam-db commented on code in PR #52699:
URL: https://github.com/apache/spark/pull/52699#discussion_r2823597551
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala:
##########
@@ -162,31 +269,31 @@ object ConstantPropagation extends Rule[LogicalPlan] {
* 2. AttributeMap: propagated mapping of attribute => constant
*/
private def traverse(condition: Expression, replaceChildren: Boolean, nullIsFalse: Boolean)
- : (Option[Expression], AttributeMap[(Literal, BinaryComparison)]) =
+ : (Option[Expression], AttributeMap[(Literal, BinaryComparison)]) =
Review Comment:
Maybe we can write the following function and call it from InferFiltersFromConstraints, instead of the additional logic above?
```
/**
* Substitutes [[Attribute Attributes]] which can be statically evaluated with their corresponding
* value in conjunctive [[Expression Expressions]]. This opens up the main logic in this rule to
* be used externally.
*/
def replaceAttributesWithConstants(condition: Expression): Option[Expression] = {
val (newCondition, _) = traverse(condition, replaceChildren = true, nullIsFalse = true)
newCondition
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "andylam-db (via GitHub)" <gi...@apache.org>.
andylam-db commented on code in PR #52699:
URL: https://github.com/apache/spark/pull/52699#discussion_r2823597551
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala:
##########
@@ -162,31 +269,31 @@ object ConstantPropagation extends Rule[LogicalPlan] {
* 2. AttributeMap: propagated mapping of attribute => constant
*/
private def traverse(condition: Expression, replaceChildren: Boolean, nullIsFalse: Boolean)
- : (Option[Expression], AttributeMap[(Literal, BinaryComparison)]) =
+ : (Option[Expression], AttributeMap[(Literal, BinaryComparison)]) =
Review Comment:
Maybe we can just do the following and call it from InferFiltersFromConstraints?
```
/**
* Substitutes [[Attribute Attributes]] which can be statically evaluated with their corresponding
* value in conjunctive [[Expression Expressions]]. This opens up the main logic in this rule to
* be used externally.
*/
def replaceAttributesWithConstants(condition: Expression): Option[Expression] = {
val (newCondition, _) = traverse(condition, replaceChildren = true, nullIsFalse = true)
newCondition
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "vanekjar (via GitHub)" <gi...@apache.org>.
vanekjar commented on code in PR #52699:
URL: https://github.com/apache/spark/pull/52699#discussion_r2479843405
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/QueryPlanConstraints.scala:
##########
@@ -77,6 +87,43 @@ trait ConstraintHelper {
inferredConstraints ++= replaceConstraints(predicates - eq - EqualNullSafe(l, r), l, r)
case _ => // No inference
}
+
+ // Step 2: Infer by constant substitution (e.g., a = 5, b = a + 3 => b = 5 + 3)
+ val equalityPredicates = predicates.toSeq.flatMap {
+ case e @ EqualTo(left: AttributeReference, right: Literal) => Some(((left, right), e))
+ case e @ EqualTo(left: Literal, right: AttributeReference) => Some(((right, left), e))
+ case _ => None
+ }
+ if (equalityPredicates.nonEmpty) {
+ val constantsMap = AttributeMap(equalityPredicates.map(_._1))
+ val predicateSet = equalityPredicates.map(_._2).toSet
+ def replaceConstantsInExpression(expression: Expression) = expression transform {
+ case a: AttributeReference =>
+ constantsMap.get(a) match {
+ case Some(literal) => literal
+ case None => a
+ }
+ }
+ predicates.foreach { cond =>
+ val replaced = cond transform {
+ // attribute equality is handled above, no need to replace
+ case e @ EqualTo(_: Attribute, _: Attribute) => e
+ case e @ EqualTo(_: Cast, _: Attribute) => e
+ case e @ EqualTo(_: Attribute, _: Cast) => e
+
+ case e @ EqualTo(_, _) if !predicateSet.contains(e) => replaceConstantsInExpression(e)
+ }
+ // Avoid inferring tautologies like 1 = 1
+ val isTautology = replaced match {
+ case EqualTo(left: Expression, right: Expression) if left.foldable && right.foldable =>
+ left.eval() == right.eval()
+ case _ => false
+ }
Review Comment:
Thanks for the suggestion above, I'll try to reuse existing code as much as possible.
Regarding the performance. Expression evaluation already happens in the driver in `ConstantFolding` rule.
https://github.com/apache/spark/blob/7c5a9a32203874f274910a924ea812d23918cf02/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala#L49-L64
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-53996][SQL] Improve InferFiltersFromConstraints to infer filters from complex join expressions [spark]
Posted by "vanekjar (via GitHub)" <gi...@apache.org>.
vanekjar commented on code in PR #52699:
URL: https://github.com/apache/spark/pull/52699#discussion_r2479843405
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/QueryPlanConstraints.scala:
##########
@@ -77,6 +87,43 @@ trait ConstraintHelper {
inferredConstraints ++= replaceConstraints(predicates - eq - EqualNullSafe(l, r), l, r)
case _ => // No inference
}
+
+ // Step 2: Infer by constant substitution (e.g., a = 5, b = a + 3 => b = 5 + 3)
+ val equalityPredicates = predicates.toSeq.flatMap {
+ case e @ EqualTo(left: AttributeReference, right: Literal) => Some(((left, right), e))
+ case e @ EqualTo(left: Literal, right: AttributeReference) => Some(((right, left), e))
+ case _ => None
+ }
+ if (equalityPredicates.nonEmpty) {
+ val constantsMap = AttributeMap(equalityPredicates.map(_._1))
+ val predicateSet = equalityPredicates.map(_._2).toSet
+ def replaceConstantsInExpression(expression: Expression) = expression transform {
+ case a: AttributeReference =>
+ constantsMap.get(a) match {
+ case Some(literal) => literal
+ case None => a
+ }
+ }
+ predicates.foreach { cond =>
+ val replaced = cond transform {
+ // attribute equality is handled above, no need to replace
+ case e @ EqualTo(_: Attribute, _: Attribute) => e
+ case e @ EqualTo(_: Cast, _: Attribute) => e
+ case e @ EqualTo(_: Attribute, _: Cast) => e
+
+ case e @ EqualTo(_, _) if !predicateSet.contains(e) => replaceConstantsInExpression(e)
+ }
+ // Avoid inferring tautologies like 1 = 1
+ val isTautology = replaced match {
+ case EqualTo(left: Expression, right: Expression) if left.foldable && right.foldable =>
+ left.eval() == right.eval()
+ case _ => false
+ }
Review Comment:
Thanks for the suggestion above, I'll try to reuse existing code as much as possible.
Regarding the performance. Expression evaluation already happens in the driver in `ConstantFolding` rule.
https://github.com/apache/spark/blob/7c5a9a32203874f274910a924ea812d23918cf02/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala#L64
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org