This is an automated email from the ASF dual-hosted git repository.

dongjoon-hyun pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/branch-4.x by this push:
     new 77f0554d381c [SPARK-60985][SQL] Fix query failures when a filter 
inferred from data constraints can fail
77f0554d381c is described below

commit 77f0554d381c8995e96e15c056f99d4b9fac4537
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Fri Nov 6 18:36:16 2026 -0800

    [SPARK-60985][SQL] Fix query failures when a filter inferred from data 
constraints can fail
    
    ### What changes were proposed in this pull request?
    
    `InferFiltersFromConstraints` turns data constraints into filters. This PR 
makes it skip a constraint when evaluating that constraint as a filter can 
fail. A constraint becomes a filter only if 
`ExprUtils.canEvaluateUnconditionally` accepts it, with these parts treated as 
safe so that the useful inference stays:
    
    - Constant parts count as the value they evaluate to, because 
`ConstantFolding` folds them right after this rule. For example, `b.d >= 
date_sub(a.dt, 7)` with `a.dt = DATE'2026-06-10'` (SPARK-57437) still becomes 
`b.d >= DATE'2026-06-03'`. A constant that fails to evaluate does not become a 
filter.
    - Widening numeric casts, which type coercion adds to join keys.
    - An integral remainder by a non-zero constant, such as `x % 2 = 0`, which 
cannot fail. It keeps a filter such as the one of a stream-stream join on both 
sides, which keeps the join state small.
    - Subqueries. An uncorrelated one runs once whichever rows use it, and a 
correlated one counts by its values and its correlation condition. This keeps 
the semi-joins inferred for TPC-H q18 and q20 and TPC-DS q14 and q23b.
    
    `Filter.constraints` does not change. Constraints are still facts about the 
output rows, and `PruneFilters` and `EliminateOuterJoin` rely on them. Only the 
two places that turn constraints into filters change, and `IsNotNull` is still 
inferred from a predicate that can fail.
    
    `spark.sql.legacy.inferFailableFiltersFromConstraints` restores the 
previous behavior, and the migration guide describes the change.
    
    Two alternatives were measured and rejected:
    
    - Moving the inferred filters after the original condition changes 343 plan 
stability golden files and does not help joins, because there the filter goes 
to a relation that has no such condition.
    - Keeping only the filters that can fail after the original condition, on 
the `Filter` path, still fails the query on a partition column, and still 
evaluates a UDF twice per row.
    
    ### Why are the changes needed?
    
    A constraint only says what holds on the rows an operator outputs. As a 
filter, it also runs on rows and values that the query does not evaluate it on. 
So this rule can add an expression that is not in the query, and fail a query 
that does not fail in any evaluation order of its own conjuncts.
    
    ```sql
    -- t has the rows (0, 6) and (6, 6).
    SELECT * FROM t WHERE x = y AND 10 / y > 0;
    ```
    
    On `(0, 6)`, `x = y` is false and `10 / y` is about `1.67`, whichever is 
evaluated first. The optimizer adds `10 / x > 0` ahead of both, and the query 
fails with `DIVIDE_BY_ZERO`.
    
    A join is worse, because the new filter goes to the other relation, where 
nothing guards it.
    
    ```sql
    -- 'abc' exists only in l.s.
    SELECT * FROM l JOIN r ON l.s = r.s WHERE CAST(r.s AS INT) > 0;
    ```
    
    This query fails with `CAST_INVALID_INPUT`, although no row of `r` has a 
value that fails.
    
    | Query | Data | Before | After |
    |---|---|---|---|
    | `x = y AND 10 / y > 0` | `(0, 6), (6, 6)` | `DIVIDE_BY_ZERO` | `(6, 6)` |
    | `id > 5 AND x = y AND 10 / (id - y) > 0` | `(0, 0, 0), (6, 0, 0)` | 
`DIVIDE_BY_ZERO` | `(6, 0, 0)` |
    | `p = y AND 10 / y > 0`, where `p` is a partition column | `(0, 6), (6, 
6)` | `DIVIDE_BY_ZERO` | `(6, 6)` |
    | `s1 = s2 AND CAST(s2 AS INT) > 0` | `('abc', '1'), ('1', '1')` | 
`CAST_INVALID_INPUT` | `('1', '1')` |
    | `l.x = r.x WHERE 10 / r.x > 0` | `l.x`: 0, 6; `r.x`: 6 | `DIVIDE_BY_ZERO` 
| `(6, 6)` |
    | `a LEFT JOIN b ON a.k = b.k AND 10 / (b.id - a.y) > 0 WHERE a.y = 0` | 
`a`: `(1, 0)`; `b`: `(1, 6), (99, 0)` | `DIVIDE_BY_ZERO` | `(1, 0, 1, 6)` |
    | `id > 5 AND x = y AND IF(id - y = 0, raise_error('boom'), true)` | `(0, 
0, 0), (6, 0, 0)` | `USER_RAISED_EXCEPTION` | `(6, 0, 0)` |
    
    The attribute equality path dates back to SPARK-13789 and SPARK-13871 in 
Spark 2.0. It became reachable with default settings when ANSI mode became the 
default in [v4.0.0](https://github.com/apache/spark/releases/tag/v4.0.0) 
(2025-05-19). Without ANSI mode, `raise_error`, `assert_true` and UDFs still 
reach it. SPARK-57437, in 
[v4.3.0](https://github.com/apache/spark/releases/tag/v4.3.0) (2026-10-27), 
extended the problem to literal bindings: the `LEFT JOIN` query in the table 
passes o [...]
    
    Other optimizer rules already guard against this:
    
    - `InferFiltersFromGenerate` generates predicates only over attributes 
(SPARK-37392), because the input "may fail to be evaluated under ANSI mode".
    - `PushPredicateThroughJoin` (SPARK-46707) and 
`PushPredicateThroughNonJoin` (SPARK-60602, SPARK-60633) hold back `throwable` 
conditions. An inferred copy goes around that: over a projection with `f(id) AS 
f`, `f > 5 AND x = y AND assert_true(id - y > 5) IS NULL` fails, because the 
copy is placed first and is pushed below the projection.
    
    Inferring a UDF also evaluates it twice per row: `x = y AND f(y) >= 0` 
calls `f` 20 times on 10 rows, and 10 times after this PR.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes, compared to the released versions.
    
    - The queries above return rows instead of failing.
    - A UDF in such a condition is no longer evaluated twice per row.
    - A filter that `ExprUtils.canEvaluateUnconditionally` does not accept no 
longer reaches the other side of a join, a data source, or partition pruning, 
so some queries can read more data. In `l JOIN r ON l.s = r.s WHERE upper(r.s) 
= 'A'`, `l` now gets only `l.s IS NOT NULL`. The same applies to `i + 1 > 5`, 
`abs(i) > 5`, and narrowing or string casts.
    - The check is conservative, so some of these filters cannot fail, such as 
`LIKE 'a%'`, `year(d) = 2024`, and arithmetic with ANSI mode off. Widening 
`ExprUtils.canEvaluateUnconditionally` brings them back.
    - Plain comparisons, `IN`, `BETWEEN`, widening numeric casts, integral 
remainders by a non-zero constant, constants, subqueries, and `IS NOT NULL` are 
still inferred. No TPC-DS or TPC-H plan changes.
    
    Set `spark.sql.legacy.inferFailableFiltersFromConstraints` to `true` to 
restore the previous behavior.
    
    ### How was this patch tested?
    
    Added five tests to `InferFiltersFromConstraintsSuite` and two to 
`DataFrameSuite`. They cover the `Filter` path, the join path, the legacy 
configuration, subqueries and their correlation condition, a narrowing cast 
over a widening one, a literal binding that makes the other side a failing 
constant, and an `Or` whose canonical form is `MultiCommutativeOp`, which is 
why the rule filters the original expressions rather than calling 
`ExpressionSet.filter`. The test "filter lower data typ [...]
    
    Constant folding and widening casts are covered by existing tests.
    
    The plan stability golden files do not change when regenerated with ANSI 
mode on and off:
    
    ```bash
    SPARK_GENERATE_GOLDEN_FILES=1 build/sbt "sql/testOnly *PlanStability*Suite"
    SPARK_GENERATE_GOLDEN_FILES=1 SPARK_ANSI_SQL_MODE=false build/sbt 
"sql/testOnly *PlanStability*Suite"
    ```
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #60162 from dongjoon-hyun/SPARK-60985.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
    (cherry picked from commit 3a6d5ee8e1c14cb26eef50a2e18ad718f171bae4)
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 docs/sql-migration-guide.md                        |  1 +
 .../spark/sql/catalyst/optimizer/Optimizer.scala   | 46 +++++++++++--
 .../org/apache/spark/sql/internal/SQLConf.scala    | 18 +++++
 .../InferFiltersFromConstraintsSuite.scala         | 77 +++++++++++++++++++---
 .../org/apache/spark/sql/DataFrameSuite.scala      | 43 ++++++++++++
 .../adaptive/AdaptiveQueryExecSuite.scala          |  4 ++
 .../spark/sql/hive/execution/HiveUDFSuite.scala    | 54 ++++++++-------
 7 files changed, 204 insertions(+), 39 deletions(-)

diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index c23f6f6d1444..1408dca054b3 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -72,6 +72,7 @@ license: |
 - Since Spark 4.4, when `spark.sql.parquet.fieldId.read.enabled` is true, a 
Parquet read fails with `PARQUET_FIELD_REQUESTED_MORE_THAN_ONCE` if two or more 
requested fields of a struct (or of the top-level schema) resolve to the same 
Parquet field. This happens when one requested field is matched by field ID and 
another by name, or when two requested fields have the same field ID. In Spark 
4.3 and earlier, such a read returned wrong values without an error: with the 
vectorized reader one [...]
 - Since Spark 4.4, when `spark.sql.groupByOrdinal` is true (the default), the 
pipe `AGGREGATE` operator rejects an aliased integer literal in its `GROUP BY` 
clause, such as `TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp`, with 
`UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE`, because a bare 
integer literal in the same position is an ordinal. Previously the aliased 
literal was grouped as a constant. Bare ordinals and aliases on other grouping 
expressions are not affecte [...]
 - Since Spark 4.4, with whole-stage codegen enabled, a filter condition, 
including the `FILTER` clause of an aggregate function, evaluates the remaining 
top-level conjuncts of the condition on a row after one of them evaluates to 
`NULL`, as the SQL standard requires, and does so in the order they appear in 
the condition, as Spark already did with whole-stage codegen disabled, which 
also evaluates a subexpression shared by several conjuncts before the 
condition. Only a `FALSE` conjunct st [...]
+- Since Spark 4.4, the optimizer no longer infers a filter from data 
constraints when evaluating the filter can fail, such as arithmetic or a 
narrowing cast in ANSI mode, or when it calls a user-defined function. The 
check is conservative, so a few filters that cannot fail are not inferred 
either, such as `LIKE 'a%'`, `year(d) = 2024`, and arithmetic with ANSI mode 
off. For example, `SELECT * FROM t WHERE x = y AND 10 / y > 0` no longer adds 
the filter `10 / x > 0`, which failed with `DI [...]
 - Since Spark 4.4, when a format is specified, `to_timestamp`, 
`try_to_timestamp`, and `to_timestamp_ltz` cast a numeric first argument to a 
string and parse it with the format, for example `to_timestamp(20201231, 
'yyyyMMdd')` returns `2020-12-31 00:00:00`. This matches the existing behavior 
when `spark.sql.timestampType` is `TIMESTAMP_NTZ`. Previously, with the default 
`TIMESTAMP_LTZ` timestamp type, such calls failed with `INTERNAL_ERROR`. Note 
that the string form of the number is par [...]
 - Since Spark 4.4, the Spark Thrift Server logs a warning at startup when 
`hive.server2.enable.doAs` is `true` and `hive.server2.authentication` verifies 
user identities (any recognized type other than `NONE`/`NOSASL`), because 
executor-side data access still runs as the server's own service identity 
rather than the connecting user (SPARK-5159). Set 
`spark.sql.hive.thriftServer.allowIneffectiveDoAs` to `true` to acknowledge the 
limitation and silence the warning. Spark 5.0 is expected to [...]
 - Since Spark 4.4, `UPDATE` and `MERGE` on a Data Source V2 table fail with 
`DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS` when they assign to 
a metadata column of the table. This includes `UPDATE SET *` and `INSERT *` in 
`MERGE WITH SCHEMA EVOLUTION` when the source has a column with the name of a 
metadata column of the target table. Previously, these assignments passed 
analysis and were usually ignored. To restore the previous behavior, set 
`spark.sql.legacy.ignoreMetadata [...]
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 1ac14446c08c..e9338c08822d 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
@@ -2256,8 +2256,8 @@ object InferFiltersFromConstraints extends 
Rule[LogicalPlan]
   private def inferFilters(plan: LogicalPlan): LogicalPlan = 
plan.transformWithPruning(
     _.containsAnyPattern(FILTER, JOIN)) {
     case filter @ Filter(condition, child) =>
-      val newFilters = filter.constraints --
-        (child.constraints ++ splitConjunctivePredicates(condition))
+      val newFilters = (filter.constraints --
+        (child.constraints ++ 
splitConjunctivePredicates(condition))).toSeq.filter(canBeFilter)
       if (newFilters.nonEmpty) {
         Filter(And(newFilters.reduce(And), condition), child)
       } else {
@@ -2302,17 +2302,55 @@ object InferFiltersFromConstraints extends 
Rule[LogicalPlan]
   }
 
   private def inferNewFilter(plan: LogicalPlan, constraints: ExpressionSet): 
LogicalPlan = {
-    val newPredicates = constraints
+    val newPredicates = (constraints
       .union(constructIsNotNullConstraints(constraints, plan.output))
       .filter { c =>
         c.references.nonEmpty && c.references.subsetOf(plan.outputSet) && 
c.deterministic
-      } -- plan.constraints
+      } -- plan.constraints).toSeq.filter(canBeFilter)
     if (newPredicates.isEmpty) {
       plan
     } else {
       Filter(newPredicates.reduce(And), plan)
     }
   }
+
+  /**
+   * A constraint only says what holds on the rows an operator outputs. As a 
filter, it also runs
+   * on rows and values the query may never evaluate it on, e.g. `10 / x > 0` 
from `x = y AND
+   * 10 / y > 0` on a row where `x` is 0 and `y` is not, so only a constraint 
that cannot fail
+   * becomes a filter. Constant parts count as what they evaluate to, as they 
are folded right
+   * after this rule. An uncorrelated subquery counts as a constant, as it 
runs once whichever
+   * rows use it, and a correlated one by its values and its correlation 
condition. Callers pass
+   * the original expressions, because [[ExpressionSet.filter]] tests the 
canonicalized ones.
+   */
+  private def canBeFilter(constraint: Expression): Boolean = {
+    conf.legacyInferFailableFiltersFromConstraints || 
ExprUtils.canEvaluateUnconditionally(
+      stripCannotFail(ConstantFolding.constantFolding(constraint, 
isConditionalBranch = true)))
+  }
+
+  // Top-down, so that each cast is judged by the type of its original input.
+  private def stripCannotFail(e: Expression): Expression = e match {
+    case c: Cast if isNumericUpCast(c.child.dataType, c.dataType) => 
stripCannotFail(c.child)
+    // An integral remainder by a non-zero constant, e.g. `x % 2 = 0`, never 
fails.
+    case r: Remainder if r.dataType.isInstanceOf[IntegralType] && 
isNonZeroIntegral(r.right) =>
+      stripCannotFail(r.left)
+    case i: InSubquery =>
+      (i.values.map(IsNotNull(_)) ++ 
i.query.joinCond).map(stripCannotFail).reduce[Expression](And)
+    case x: Exists =>
+      
x.joinCond.map(stripCannotFail).foldLeft[Expression](Literal.TrueLiteral)(And)
+    case s: ScalarSubquery if !s.isCorrelated => Literal.create(null, 
s.dataType)
+    case other => other.mapChildren(stripCannotFail)
+  }
+
+  private def isNonZeroIntegral(e: Expression): Boolean = e match {
+    case Literal(v, _: IntegralType) => v != null && v != 0
+    case _ => false
+  }
+
+  private def isNumericUpCast(from: DataType, to: DataType): Boolean = (from, 
to) match {
+    case (_: NumericType, _: NumericType) => Cast.canUpCast(from, to)
+    case _ => false
+  }
 }
 
 /**
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index cbc608b8e404..155b69a040b1 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -1851,6 +1851,21 @@ object SQLConf {
     .booleanConf
     .createWithDefault(true)
 
+  val LEGACY_INFER_FAILABLE_FILTERS_FROM_CONSTRAINTS =
+    buildConf("spark.sql.legacy.inferFailableFiltersFromConstraints")
+      .internal()
+      .doc("When true, the query optimizer also infers a filter from data 
constraints when " +
+        "evaluating it can fail or is otherwise observable, such as `10 / x > 
0` from " +
+        "`x = y AND 10 / y > 0`, or a UDF. Such a filter runs on rows that the 
query does not " +
+        "evaluate the expression on, so the query can raise an error that it 
does not raise " +
+        "without the filter. When false, only a filter that `ExprUtils." +
+        "canEvaluateUnconditionally` accepts is inferred, which also leaves 
out some " +
+        "expressions that cannot fail, such as `LIKE 'a%'`.")
+      .version("4.4.0")
+      .withBindingPolicy(ConfigBindingPolicy.NOT_APPLICABLE)
+      .booleanConf
+      .createWithDefault(false)
+
   val PROPAGATE_DISTINCT_KEYS_ENABLED =
     buildConf("spark.sql.optimizer.propagateDistinctKeys.enabled")
       .internal()
@@ -9696,6 +9711,9 @@ class SQLConf extends Serializable with Logging with 
SqlApiConf {
 
   def constraintPropagationEnabled: Boolean = 
getConf(CONSTRAINT_PROPAGATION_ENABLED)
 
+  def legacyInferFailableFiltersFromConstraints: Boolean =
+    getConf(LEGACY_INFER_FAILABLE_FILTERS_FROM_CONSTRAINTS)
+
   def escapedStringLiterals: Boolean = getConf(ESCAPED_STRING_LITERALS)
 
   def parserDfaCacheFlushRatio: Double = getConf(PARSER_DFA_CACHE_FLUSH_RATIO)
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/InferFiltersFromConstraintsSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/InferFiltersFromConstraintsSuite.scala
index 0640642489a6..af728c5974d7 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/InferFiltersFromConstraintsSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/InferFiltersFromConstraintsSuite.scala
@@ -311,19 +311,76 @@ class InferFiltersFromConstraintsSuite extends PlanTest {
       testConstraintsAfterJoin(originalLeft, originalRight, left, right, 
Inner, condition)
     }
 
-    Seq(Some("left.a".attr === "right.b".attr.cast(IntegerType)),
-      Some("right.b".attr.cast(IntegerType) === "left.a".attr)).foreach { 
condition =>
-      testConstraintsAfterJoin(
-        originalLeft,
-        originalRight,
-        left,
-        testRelation2.where(IsNotNull($"b") && $"b".attr.cast(IntegerType) === 
1)
-          .subquery("right"),
-        Inner,
-        condition)
+    // A narrowing cast can fail in ANSI mode, so it is not inferred.
+    withSQLConf(SQLConf.ANSI_ENABLED.key -> "true") {
+      Seq(Some("left.a".attr === "right.b".attr.cast(IntegerType)),
+        Some("right.b".attr.cast(IntegerType) === "left.a".attr)).foreach { 
condition =>
+        testConstraintsAfterJoin(originalLeft, originalRight, left, right, 
Inner, condition)
+      }
+    }
+  }
+
+  private def failable(e: Expression): Expression =
+    Divide(Literal(10.0), e, EvalMode.ANSI) > 0.0
+
+  test("SPARK-60985: a constraint that can fail is not inferred as a filter") {
+    val relation = LocalRelation($"x".double, $"y".double)
+    val query = relation.where($"x" === $"y" && failable($"y")).analyze
+    val notNullAndQuery = IsNotNull($"x") && IsNotNull($"y") && $"x" === $"y" 
&& failable($"y")
+
+    comparePlans(InferFiltersFromConstraints(query), 
relation.where(notNullAndQuery).analyze)
+    withSQLConf(SQLConf.LEGACY_INFER_FAILABLE_FILTERS_FROM_CONSTRAINTS.key -> 
"true") {
+      comparePlans(
+        InferFiltersFromConstraints(query),
+        relation.where(failable($"x") && notNullAndQuery).analyze)
     }
   }
 
+  test("SPARK-60985: a constraint that can fail is not inferred on the other 
side of a join") {
+    val left = LocalRelation($"x".double)
+    val right = LocalRelation($"y".double)
+    testConstraintsAfterJoin(
+      left.subquery("l"),
+      right.where(failable($"y")).subquery("r"),
+      left.where(IsNotNull($"x")).subquery("l"),
+      right.where(IsNotNull($"y") && failable($"y")).subquery("r"),
+      Inner,
+      Some("l.x".attr === "r.y".attr))
+  }
+
+  test("SPARK-60985: a constraint on an uncorrelated subquery is inferred as a 
filter") {
+    val relation = LocalRelation($"x".int, $"y".int)
+    val values = LocalRelation($"a".int).select($"a")
+    val scalar = ScalarSubquery(LocalRelation($"a".int).select(max($"a")))
+    def in(e: Expression): Expression = InSubquery(Seq(e), ListQuery(values))
+    val query = relation.where($"x" === $"y" && in($"y") && $"y" > 
scalar).analyze
+    val inferred = InferFiltersFromConstraints(query).collectFirst {
+      case Filter(condition, _) => splitConjunctivePredicates(condition)
+    }.get
+    assert(inferred.count(_.isInstanceOf[InSubquery]) === 2, inferred)
+    assert(inferred.count(_.isInstanceOf[GreaterThan]) === 2, inferred)
+  }
+
+  test("SPARK-60985: an integral remainder by a non-zero constant is 
inferred") {
+    val relation = LocalRelation($"x".int, $"y".int)
+    def even(e: Expression): Expression = Remainder(e, Literal(2), 
EvalMode.ANSI) === 0
+    val query = relation.where($"x" === $"y" && even($"y")).analyze
+    comparePlans(
+      InferFiltersFromConstraints(query),
+      relation.where(IsNotNull($"x") && IsNotNull($"y") && $"x" === $"y" &&
+        even($"y") && even($"x")).analyze)
+  }
+
+  test("SPARK-60985: a constraint whose canonical form is MultiCommutativeOp 
is inferred") {
+    val relation = LocalRelation($"x".int, $"y".int)
+    def anyOf(e: Expression): Expression = e === 1 || e === 2 || e === 3
+    val query = relation.where($"x" === $"y" && anyOf($"y")).analyze
+    comparePlans(
+      InferFiltersFromConstraints(query),
+      relation.where(IsNotNull($"x") && IsNotNull($"y") && $"x" === $"y" &&
+        anyOf($"y") && anyOf($"x")).analyze)
+  }
+
   test("SPARK-36978: IsNotNull constraints on structs should apply at the 
member field " +
     "instead of the root level nested type") {
     val structTestRelation = LocalRelation($"a".struct(StructType(
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/DataFrameSuite.scala 
b/sql/core/src/test/scala/org/apache/spark/sql/DataFrameSuite.scala
index db85393ef360..c9956317df46 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/DataFrameSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/DataFrameSuite.scala
@@ -3022,6 +3022,49 @@ class DataFrameSuite extends SharedSparkSession
     }
   }
 
+  test("SPARK-60985: an inferred filter does not raise an error the query does 
not raise") {
+    withTempPath { dir =>
+      // On (0, 6), neither `x = y` nor `10 / y` fails, whichever is evaluated 
first. On
+      // (3000000000, 1), neither does the cast of `y`, but the cast of `x` 
overflows.
+      Seq((0L, 6L), (6L, 6L), (3000000000L, 1L)).toDF("x", 
"y").write.parquet(dir.getCanonicalPath)
+      val df = spark.read.parquet(dir.getCanonicalPath)
+      withSQLConf(SQLConf.ANSI_ENABLED.key -> "true") {
+        checkAnswer(df.where($"x" === $"y" && lit(10L) / $"y" > 0), Row(6L, 
6L))
+        checkAnswer(
+          df.select($"x").distinct().join(df.select($"y").distinct(), $"x" === 
$"y")
+            .where(lit(10L) / $"y" > 0),
+          Row(6L, 6L))
+        checkAnswer(
+          df.where($"x" === $"y" && $"y".cast("float").cast("int") > 0), 
Row(6L, 6L))
+        // The binding `a.y = 0` turns `b.y = 10 / a.y` into `b.y = 10 / 0`, 
which no row reaches.
+        checkAnswer(
+          df.as("a").join(df.as("b"), $"a.x" === $"b.x" && $"b.y" === lit(10L) 
/ $"a.y")
+            .where($"a.y" === 0),
+          Seq.empty)
+      }
+    }
+  }
+
+  test("SPARK-60985: a correlated subquery is inferred with its correlation 
condition") {
+    withTempPath { dir =>
+      // `r.y` is 0 on one row, which the correlation condition `s.b = 10 / 
r.y` would divide by.
+      Seq(6L).toDF("x").write.parquet(s"${dir.getCanonicalPath}/l")
+      Seq(0L, 6L).toDF("y").write.parquet(s"${dir.getCanonicalPath}/r")
+      Seq((0L, 1L), (6L, 1L)).toDF("a", 
"b").write.parquet(s"${dir.getCanonicalPath}/s")
+      withTempView("l", "r", "s") {
+        Seq("l", "r", "s").foreach { t =>
+          
spark.read.parquet(s"${dir.getCanonicalPath}/$t").createOrReplaceTempView(t)
+        }
+        withSQLConf(SQLConf.ANSI_ENABLED.key -> "true") {
+          checkAnswer(
+            sql("SELECT * FROM l LEFT JOIN r ON l.x = r.y AND " +
+              "l.x IN (SELECT a FROM s WHERE s.b = 10 / l.x)"),
+            Row(6L, null))
+        }
+      }
+    }
+  }
+
   test("SPARK-60528: collect a nested empty struct") {
     val df = spark.range(1).selectExpr("id AS i", "named_struct('a', struct()) 
AS s")
     checkAnswer(df, Row(0L, Row(Row())))
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/adaptive/AdaptiveQueryExecSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/adaptive/AdaptiveQueryExecSuite.scala
index 32d9df8d839b..a7dbaf5d766f 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/adaptive/AdaptiveQueryExecSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/adaptive/AdaptiveQueryExecSuite.scala
@@ -1447,8 +1447,12 @@ class AdaptiveQueryExecSuite
   }
 
   test("Scalar subquery in later stages") {
+    // `(value + a) = (SELECT ...)` adds a string to a number, which can fail 
in ANSI mode, so its
+    // copy on the other side of the join, which makes it small enough to 
broadcast, is only
+    // inferred with the legacy configuration.
     withSQLConf(
         SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "true",
+        SQLConf.LEGACY_INFER_FAILABLE_FILTERS_FROM_CONSTRAINTS.key -> "true",
         SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "80") {
       val (plan, adaptivePlan) = runAdaptiveAndVerifyResult(
         "SELECT * FROM testData join testData2 ON key = a " +
diff --git 
a/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/HiveUDFSuite.scala
 
b/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/HiveUDFSuite.scala
index 54824bb479c3..9c917e9a5293 100644
--- 
a/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/HiveUDFSuite.scala
+++ 
b/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/HiveUDFSuite.scala
@@ -1245,31 +1245,35 @@ class HiveUDFSuite extends QueryTest with 
TestHiveSingleton {
     // Filter on the driver per-conjunct, which does not interleave the two 
copies.
     // With a shared GenericUDF instance, the second row threw 
ClassCastException:
     // TimestampWritable cannot be cast to java.sql.Timestamp.
-    withUserDefinedFunction("hive_gt" -> true) {
-      sql(s"CREATE TEMPORARY FUNCTION hive_gt AS 
'${classOf[GenericUDFOPGreaterThan].getName}'")
-      withTable("gt_table") {
-        sql("CREATE TABLE gt_table (id INT, pt DATE, created_at TIMESTAMP) 
STORED AS PARQUET")
-        sql("""
-          |INSERT INTO gt_table VALUES
-          |  (1, DATE '2024-09-10', TIMESTAMP '2024-09-01 00:00:00'),
-          |  (2, DATE '2024-09-10', TIMESTAMP '2024-09-05 00:00:00'),
-          |  (3, DATE '2024-09-10', TIMESTAMP '2024-09-15 00:00:00'),
-          |  (4, DATE '2024-09-11', TIMESTAMP '2024-09-01 00:00:00')
-          |""".stripMargin)
-        val df = sql("""
-          |SELECT id FROM gt_table
-          |WHERE pt = DATE '2024-09-10'
-          |  AND hive_gt(CAST(pt AS TIMESTAMP), created_at)
-          |ORDER BY id
-          |""".stripMargin)
-        // Guard against the test going silently vacuous: the optimized Filter 
must
-        // hold both copies, exactly one of them with a literal first argument.
-        val udfs = df.queryExecution.optimizedPlan.collect {
-          case f: Filter => f.condition.collect { case u: HiveGenericUDF => u }
-        }.flatten
-        assert(udfs.size == 2)
-        assert(udfs.count(_.children.head.isInstanceOf[Literal]) == 1)
-        checkAnswer(df, Row(1) :: Row(2) :: Nil)
+    // A Hive UDF can fail, so InferFiltersFromConstraints only copies it with 
the legacy
+    // configuration.
+    withSQLConf(SQLConf.LEGACY_INFER_FAILABLE_FILTERS_FROM_CONSTRAINTS.key -> 
"true") {
+      withUserDefinedFunction("hive_gt" -> true) {
+        sql(s"CREATE TEMPORARY FUNCTION hive_gt AS 
'${classOf[GenericUDFOPGreaterThan].getName}'")
+        withTable("gt_table") {
+          sql("CREATE TABLE gt_table (id INT, pt DATE, created_at TIMESTAMP) 
STORED AS PARQUET")
+          sql("""
+            |INSERT INTO gt_table VALUES
+            |  (1, DATE '2024-09-10', TIMESTAMP '2024-09-01 00:00:00'),
+            |  (2, DATE '2024-09-10', TIMESTAMP '2024-09-05 00:00:00'),
+            |  (3, DATE '2024-09-10', TIMESTAMP '2024-09-15 00:00:00'),
+            |  (4, DATE '2024-09-11', TIMESTAMP '2024-09-01 00:00:00')
+            |""".stripMargin)
+          val df = sql("""
+            |SELECT id FROM gt_table
+            |WHERE pt = DATE '2024-09-10'
+            |  AND hive_gt(CAST(pt AS TIMESTAMP), created_at)
+            |ORDER BY id
+            |""".stripMargin)
+          // Guard against the test going silently vacuous: the optimized 
Filter must
+          // hold both copies, exactly one of them with a literal first 
argument.
+          val udfs = df.queryExecution.optimizedPlan.collect {
+            case f: Filter => f.condition.collect { case u: HiveGenericUDF => 
u }
+          }.flatten
+          assert(udfs.size == 2)
+          assert(udfs.count(_.children.head.isInstanceOf[Literal]) == 1)
+          checkAnswer(df, Row(1) :: Row(2) :: Nil)
+        }
       }
     }
     hiveContext.reset()


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

Reply via email to