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

dongjoon-hyun 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 1c2f4a44fe4f [SPARK-60969][SQL] Keep pulling up correlated equalities 
on a cast from a scale-0 decimal to an integral type
1c2f4a44fe4f is described below

commit 1c2f4a44fe4f0ba900a98f8f74e3577e020eb949
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Thu Nov 5 19:50:58 2026 -0800

    [SPARK-60969][SQL] Keep pulling up correlated equalities on a cast from a 
scale-0 decimal to an integral type
    
    ### What changes were proposed in this pull request?
    
    This PR makes `DecorrelateInnerQuery.canPullUpOverAgg` aware of the cast's 
eval mode and the equality kind, so that a correlated equality on a cast from a 
scale-0 decimal attribute to an integral type is pulled up over an `Aggregate` 
when the cast is one-to-one on the rows that the equality can match:
    
    - ANSI cast: one-to-one under both `=` and `<=>` (the exact value, NULL 
only for NULL input, or `CAST_OVERFLOW`).
    - TRY cast: one-to-one under `=` only. Out-of-range values and NULL all 
become NULL, and `=` never matches NULL, but `<=>` would match all of them to a 
NULL outer key.
    - LEGACY casts (they wrap around), TRY casts under `<=>`, and casts from a 
decimal with a non-zero scale keep following `Cast.canUpCast`, so they keep 
using the domain join.
    
    The callers are unchanged because both pass the whole predicate, so the 
equality kind is available inside the function.
    
    ### Why are the changes needed?
    
    After SPARK-60943, `Cast.canUpCast(DECIMAL(19, 0), BIGINT)` is false, so 
`CAST(d AS BIGINT) = outer(k)` under an aggregate, e.g. `SELECT k, (SELECT 
count(*) FROM i WHERE CAST(d AS BIGINT) = o.k) FROM o`, takes the domain-join 
plan in every eval mode (an extra scan of `o`, a distinct aggregate, an 
exchange and a join). It is needed only where the cast can map two inner values 
to the same key (LEGACY wrap-around, and TRY under `<=>`). For ANSI and TRY 
with `=`, the cheap pull-up plan is [...]
    
    ### Does this PR introduce _any_ user-facing change?
    
    No. Query results are unchanged. Only the plan shape differs for the 
ANSI/TRY cases above, compared to master after SPARK-60943.
    
    ### How was this patch tested?
    
    Added a unit test in `DecorrelateInnerQuerySuite` for each branch of 
`canPullUpOverAgg`, and an end-to-end test in `SubquerySuite` that checks both 
the plan (the number of scans) and the result for ANSI, LEGACY, and TRY with 
`=` and `<=>`, a scale-1 decimal, and analysis with 
`spark.sql.optimizer.decorrelateInnerQuery.enabled=false`.
    
    ```
    build/sbt 'catalyst/testOnly *DecorrelateInnerQuerySuite'
    build/sbt 'sql/testOnly *SubquerySuite'
    ```
    
    The new end-to-end test fails without the change.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Sonnet 5.5
    
    Closes #60151 from dongjoon-hyun/SPARK-60969.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 .../catalyst/optimizer/DecorrelateInnerQuery.scala | 14 +++++-
 .../optimizer/DecorrelateInnerQuerySuite.scala     | 18 ++++++-
 .../scala/org/apache/spark/sql/SubquerySuite.scala | 56 ++++++++++++++++++++++
 3 files changed, 86 insertions(+), 2 deletions(-)

diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
index f71c4934945b..a6b12e0b424a 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
@@ -27,6 +27,7 @@ import org.apache.spark.sql.catalyst.plans.logical._
 import org.apache.spark.sql.catalyst.trees.TreePattern.OUTER_REFERENCE
 import org.apache.spark.sql.errors.{QueryCompilationErrors, 
QueryExecutionErrors}
 import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.types.{DecimalType, IntegralType}
 import org.apache.spark.util.collection.Utils
 
 /**
@@ -104,10 +105,21 @@ object DecorrelateInnerQuery extends PredicateHelper {
    *   (a = outer(c) - b) -> false
    */
   def canPullUpOverAgg(expression: Expression): Boolean = {
+    // Whether the equality ignores NULL keys (`=`) or matches them (`<=>`).
+    val nullSafe = expression.isInstanceOf[EqualNullSafe]
+
     def isSupported(e: Expression): Boolean = e match {
       case _: Attribute => true
       // Allow Cast expressions that guarantee 1:1 mapping.
-      case Cast(a: Attribute, dataType, _, _) => Cast.canUpCast(a.dataType, 
dataType)
+      case Cast(a: Attribute, dataType, _, evalMode) =>
+        Cast.canUpCast(a.dataType, dataType) || (a.dataType match {
+          // A cast from a scale-0 decimal to an integral type is exact or 
fails (ANSI), or
+          // also yields NULL (TRY). The NULLs never match under `=`, so the 
cast is still
+          // 1:1 on the rows that can match. LEGACY wraps around, so it is not 
1:1.
+          case DecimalType.Fixed(_, 0) if dataType.isInstanceOf[IntegralType] 
=>
+            evalMode == EvalMode.ANSI || (evalMode == EvalMode.TRY && 
!nullSafe)
+          case _ => false
+        })
       case _ => false
     }
 
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
index a451c6961ba8..bfbcb493352f 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
@@ -23,7 +23,7 @@ import org.apache.spark.sql.catalyst.expressions._
 import org.apache.spark.sql.catalyst.plans._
 import org.apache.spark.sql.catalyst.plans.logical._
 import org.apache.spark.sql.internal.SQLConf
-import org.apache.spark.sql.types.IntegerType
+import org.apache.spark.sql.types.{DecimalType, IntegerType, LongType}
 
 class DecorrelateInnerQuerySuite extends PlanTest {
 
@@ -166,6 +166,22 @@ class DecorrelateInnerQuerySuite extends PlanTest {
     check(innerPlan, outerPlan, correctAnswer, Seq(x <=> x, y <=> y))
   }
 
+  test("SPARK-60969: pull up a cast from a scale-0 decimal depending on the 
eval mode") {
+    val d = AttributeReference("d", DecimalType(19, 0))()
+    val d1 = AttributeReference("d1", DecimalType(19, 1))()
+    def cast(attr: Attribute, mode: EvalMode.Value): Expression =
+      Cast(attr, LongType, None, mode)
+    val k = OuterReference(AttributeReference("k", LongType)())
+    // ANSI is 1:1 under both equalities, TRY only under `=`, and LEGACY wraps 
around.
+    assert(DecorrelateInnerQuery.canPullUpOverAgg(cast(d, EvalMode.ANSI) === 
k))
+    assert(DecorrelateInnerQuery.canPullUpOverAgg(cast(d, EvalMode.ANSI) <=> 
k))
+    assert(DecorrelateInnerQuery.canPullUpOverAgg(cast(d, EvalMode.TRY) === k))
+    assert(!DecorrelateInnerQuery.canPullUpOverAgg(cast(d, EvalMode.TRY) <=> 
k))
+    assert(!DecorrelateInnerQuery.canPullUpOverAgg(cast(d, EvalMode.LEGACY) 
=== k))
+    // A non-zero scale truncates, so it is not 1:1.
+    assert(!DecorrelateInnerQuery.canPullUpOverAgg(cast(d1, EvalMode.ANSI) === 
k))
+  }
+
   test("aggregate with correlated equality predicates that has no attribute") {
     val outerPlan = testRelation2
     val minB = Alias(min(b), "min_b")()
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala 
b/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
index 8035595ec5ad..5e58dce3c647 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala
@@ -3333,4 +3333,60 @@ class SubquerySuite extends SharedSparkSession
       }
     }
   }
+
+  test("SPARK-60969: correlated equality on a cast from a scale-0 decimal 
under an aggregate") {
+    withTable("o", "i", "j", "s") {
+      sql("CREATE TABLE o USING parquet AS SELECT * FROM VALUES " +
+        "(-8446744073709551617), (1), (CAST(NULL AS BIGINT)) AS t(k)")
+      // 9999999999999999999 and -8446744073709551617 are the same BIGINT when 
wrapped around.
+      // 9999999999999999999 and -9999999999999999999 are out of the BIGINT 
range.
+      sql("CREATE TABLE i USING parquet AS SELECT * FROM VALUES " +
+        "(9999999999999999999BD), (-8446744073709551617BD), 
(-9999999999999999999BD), (1BD) " +
+        "AS t(d)")
+      sql("CREATE TABLE j USING parquet AS SELECT * FROM VALUES (1BD), (2BD), 
(2BD) AS t(d)")
+      sql("CREATE TABLE s USING parquet AS SELECT * FROM VALUES (1.0BD), 
(2.5BD) AS t(d)")
+
+      def check(
+          ansi: Boolean,
+          table: String,
+          cond: String,
+          domainJoin: Boolean,
+          expected: Seq[Row]): Unit = {
+        withSQLConf(SQLConf.ANSI_ENABLED.key -> ansi.toString) {
+          val df = sql(s"SELECT k, (SELECT count(*) FROM $table WHERE $cond) 
FROM o")
+          // The domain join scans the outer table once more.
+          assert(df.queryExecution.optimizedPlan.collectLeaves().size ==
+            (if (domainJoin) 3 else 2), cond)
+          checkAnswer(df, expected)
+        }
+      }
+      val min = new java.math.BigDecimal("-8446744073709551617")
+
+      // LEGACY wraps around, so two inner values are the same key.
+      check(ansi = false, "i", "CAST(d AS BIGINT) = o.k", domainJoin = true,
+        Seq(Row(min, 2), Row(1, 1), Row(null, 0)))
+      // ANSI is one-to-one under both equalities, unless the scale is not 
zero.
+      check(ansi = true, "j", "CAST(d AS BIGINT) = o.k", domainJoin = false,
+        Seq(Row(min, 0), Row(1, 1), Row(null, 0)))
+      check(ansi = true, "j", "CAST(d AS BIGINT) <=> o.k", domainJoin = false,
+        Seq(Row(min, 0), Row(1, 1), Row(null, 0)))
+      check(ansi = true, "s", "CAST(d AS BIGINT) = o.k", domainJoin = true,
+        Seq(Row(min, 0), Row(1, 1), Row(null, 0)))
+      // TRY is one-to-one under `=` which never matches NULL, but not under 
`<=>`.
+      check(ansi = false, "i", "try_cast(d AS BIGINT) = o.k", domainJoin = 
false,
+        Seq(Row(min, 1), Row(1, 1), Row(null, 0)))
+      check(ansi = false, "i", "try_cast(d AS BIGINT) <=> o.k", domainJoin = 
true,
+        Seq(Row(min, 1), Row(1, 1), Row(null, 2)))
+
+      // The correlated predicates are validated without the decorrelation 
framework.
+      withSQLConf(SQLConf.DECORRELATE_INNER_QUERY_ENABLED.key -> "false") {
+        Seq("CAST(d AS BIGINT) = o.k" -> "j", "try_cast(d AS BIGINT) = o.k" -> 
"i").foreach {
+          case (cond, table) =>
+            withSQLConf(SQLConf.ANSI_ENABLED.key -> "true") {
+              sql(s"SELECT k, (SELECT count(*) FROM $table WHERE $cond) FROM 
o").collect()
+            }
+        }
+      }
+    }
+  }
 }


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

Reply via email to