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]