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 cea229f6a5dc [SPARK-60943][SQL] Fix wrong results when a cast from 
`DECIMAL(19, 0)` to `BIGINT` overflows
cea229f6a5dc is described below

commit cea229f6a5dcc130e80d31385c797270d1675419
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Thu Nov 5 16:46:03 2026 -0800

    [SPARK-60943][SQL] Fix wrong results when a cast from `DECIMAL(19, 0)` to 
`BIGINT` overflows
    
    ### What changes were proposed in this pull request?
    
    This PR fixes a correctness issue where `Cast.canUpCast` treats a cast from 
`DECIMAL(19, 0)` to `BIGINT` as an up-cast, although a 19-digit value such as 
`9999999999999999999` exceeds `Long.MaxValue` (`9223372036854775807`). This PR 
makes `DecimalType.isTighterThan` accept at most `Decimal.MAX_LONG_DIGITS` (18) 
integral digits for `LongType`.
    
    `isTighterThan` takes the bound of an integral type from 
`DecimalType.forType` and rejects a decimal type whose precision reaches it. 
The bound is right for `TINYINT`, `SMALLINT` and `INT` because their decimal 
types have as many digits as their largest values. However, `forType(LongType)` 
is `DECIMAL(20, 0)`, one digit more than `Long.MaxValue` has, so `DECIMAL(19, 
0)` passed the check. #37832 (SPARK-40389) fixed the same issue for 
`DECIMAL(10, 0)` to `INT`, but its test only checked [...]
    
    | Integral type | Largest value | Widest decimal type that `canUpCast` 
accepts (before) | (after) |
    |---|---|---|---|
    | `TINYINT` | 127 | `DECIMAL(2, 0)` | `DECIMAL(2, 0)` |
    | `SMALLINT` | 32767 | `DECIMAL(4, 0)` | `DECIMAL(4, 0)` |
    | `INT` | 2147483647 | `DECIMAL(9, 0)` | `DECIMAL(9, 0)` |
    | `BIGINT` | 9223372036854775807 | `DECIMAL(19, 0)` | `DECIMAL(18, 0)` |
    
    ### Why are the changes needed?
    
    This is a correctness issue. The optimizer uses `canUpCast` to decide which 
casts it can remove or move, so it returns wrong results for a `DECIMAL(19, 0)` 
value out of the range of `BIGINT`.
    
    ```sql
    CREATE TABLE t (d DECIMAL(19, 0)) USING parquet;
    INSERT INTO t VALUES (9999999999999999999);
    
    -- Issue 1: It should return NULL and false, but returns NULL and true.
    SELECT try_cast(d AS BIGINT), try_cast(d AS BIGINT) IS NOT NULL FROM t;
    
    -- Issue 2: It should fail with CAST_OVERFLOW, but returns 1.0E19.
    -- With spark.sql.ansi.enabled=false, it should return 
-8.446744073709552E18, but returns 1.0E19.
    SELECT CAST(CAST(d AS BIGINT) AS DOUBLE) FROM t;
    
    -- Issue 3: It should fail with CAST_OVERFLOW, but returns 
9999999999999999999.
    -- With spark.sql.ansi.enabled=false, it should return no row, but returns 
9999999999999999999.
    SELECT d FROM t WHERE CAST(d AS BIGINT) > 0;
    ```
    
    `SimplifyCasts` rewrites `IsNotNull(Cast(d, BIGINT))` to `IsNotNull(d)` 
(Issue 1) and `CAST(CAST(d AS BIGINT) AS DOUBLE)` to `CAST(d AS DOUBLE)` (Issue 
2). `UnwrapCastInBinaryComparison` rewrites `CAST(d AS BIGINT) > 0` to `d > 0` 
(Issue 3).
    
    With `spark.sql.ansi.enabled=false`, other users of `canUpCast` also 
silently produce the wrapped-around value `-8446744073709551617` or a wrong 
count.
    
    ```sql
    SET spark.sql.ansi.enabled=false;
    
    -- Issue 4: It should fail with 
INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST,
    -- but stores -8446744073709551617.
    SET spark.sql.storeAssignmentPolicy=STRICT;
    CREATE TABLE s (b BIGINT) USING parquet;
    INSERT INTO s SELECT d FROM t;
    
    -- Issue 5: It should fail with INVALID_DEFAULT_VALUE.DATA_TYPE,
    -- but creates the table with the default value -8446744073709551617.
    CREATE TABLE u (id INT, b BIGINT DEFAULT 9999999999999999999BD) USING 
parquet;
    
    -- Issue 6: It should return one row (-8446744073709551617, 2),
    -- but returns (-8446744073709551617, 1) twice.
    CREATE TABLE i (d DECIMAL(19, 0)) USING parquet;
    INSERT INTO i VALUES (9999999999999999999), (-8446744073709551617);
    CREATE TABLE o (k BIGINT) USING parquet;
    INSERT INTO o VALUES (-8446744073709551617);
    SELECT k, (SELECT count(*) FROM i WHERE CAST(d AS BIGINT) = o.k) FROM o;
    ```
    
    `DecorrelateInnerQuery` pulls the correlated predicate of Issue 6 up over 
the aggregate because it assumes that the cast is one-to-one. In the same way, 
`Dataset.as[Long]` returns `-8446744073709551617` for the value.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. This PR fixes the wrong results above. A cast from `DECIMAL(19, 0)` to 
`BIGINT` now behaves as a cast from `DECIMAL(20, 0)` to `BIGINT` already does. 
This is a long-standing issue in the released versions, including 
[v4.3.0](https://github.com/apache/spark/releases/tag/v4.3.0) (2026-10-27).
    
    Since `DECIMAL(19, 0)` is not an up-cast to `BIGINT` anymore, the checks 
that allow only up-casts reject it, as they do for `DECIMAL(20, 0)`. For 
example:
    
    - `Dataset.as[Long]` on a `DECIMAL(19, 0)` column fails with 
`CANNOT_UP_CAST_DATATYPE`, even if all values are in the range of `BIGINT`. 
This includes an Oracle `NUMBER(19)` column, which the Oracle dialect creates 
for `BIGINT` and the JDBC data source reads as `DECIMAL(19, 0)`, and the result 
of `sum` over a `DECIMAL(9, 0)` column. With Spark Connect, the Scala client 
fails instead when it reads the result: `Reading 'LongType' values from a class 
org.apache.arrow.vector.DecimalVector [...]
    - The `STRICT` store assignment policy fails with 
`INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST`.
    - A generated column of `BIGINT` with a `DECIMAL(19, 0)` expression fails 
with `UNSUPPORTED_EXPRESSION_GENERATED_COLUMN`.
    - `ALTER TABLE ... ALTER COLUMN ... TYPE BIGINT` on a `DECIMAL(19, 0)` 
column fails with `NOT_SUPPORTED_CHANGE_COLUMN`.
    
    In addition, under the default `ANSI` store assignment policy, an overflow 
fails with `CAST_OVERFLOW_IN_TABLE_INSERT` instead of `CAST_OVERFLOW`. With 
`spark.sql.ansi.enabled=false`, a cast from `DECIMAL(19, 0)` to `BIGINT` is not 
pushed down to Data Source V2 sources anymore. The optimizer does not unwrap a 
comparison such as `CAST(d AS BIGINT) > 5` to `d > 5` anymore, so file sources 
such as Parquet and JDBC sources do not receive that filter. For example, a 
view that exposes an Ora [...]
    
    To keep converting such values, users can cast them to `BIGINT` explicitly. 
This PR adds a migration guide entry.
    
    ### How was this patch tested?
    
    Pass the CIs with the newly added test cases.
    
    - `CastSuiteBase`: for every precision `p`, `canUpCast` from `DECIMAL(p, 
0)` to `TINYINT`, `SMALLINT`, `INT` and `BIGINT` is true if and only if every 
value fits in the type.
    - `SQLQuerySuite`: `CAST(CAST(d AS BIGINT) AS DOUBLE)` fails with 
`CAST_OVERFLOW` for `9999999999999999999BD`, as #37832 tests for `INT`.
    - `SimplifyCastsSuite` and `SQLQuerySuite`: `try_cast(d AS BIGINT) IS NOT 
NULL` is not simplified, and `CAST(d AS BIGINT) > 0` is not unwrapped, for 
`DECIMAL(19, 0)`, with ANSI mode on and off.
    - `ArrowEncoderSuite`: the up-cast case to `bigint` starts from 
`decimal(18,0)` instead of `decimal(19,0)`, the widest decimal type that is an 
up-cast to `bigint`, like the cases of the other integral types.
    
    I also ran Issues 1 to 6 and the other users of `canUpCast` before and 
after this PR with a temporary suite (not included). After this PR, 
`DECIMAL(19, 0)` gives the same result as `DECIMAL(20, 0)` in every case.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #60145 from dongjoon-hyun/SPARK-60943.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
    (cherry picked from commit 241dd224767ce626b074da49a0e7e8bd3d670528)
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 docs/sql-migration-guide.md                        |  1 +
 .../org/apache/spark/sql/types/DecimalType.scala   |  4 +++
 .../spark/sql/catalyst/expressions/Cast.scala      |  6 ++---
 .../sql/catalyst/expressions/CastSuiteBase.scala   | 11 ++++++++
 .../catalyst/optimizer/SimplifyCastsSuite.scala    |  6 ++++-
 .../connect/client/arrow/ArrowEncoderSuite.scala   |  2 +-
 .../scala/org/apache/spark/sql/SQLQuerySuite.scala | 29 ++++++++++++++++++++++
 7 files changed, 54 insertions(+), 5 deletions(-)

diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 3fe7636b75c0..066aeb55c91c 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -26,6 +26,7 @@ license: |
 
 - Since Spark 4.4, when a file source reads a partition column with a 
user-specified `DECIMAL` type from directory names, it converts each value to 
the precision and scale of that type, rounding half up as `CAST` does. 
Previously, the vectorized Parquet and ORC readers applied the declared scale 
to the digits in the directory name without an error, for example `d=1.5` read 
as `DECIMAL(10,2)` returned `0.15`. Data derived from affected query results 
may need to be recomputed. A value that [...]
 - Since Spark 4.4, when a file source reads a partition column with a 
user-specified `TINYINT` or `SMALLINT` type from directory names, a value out 
of the range of that type fails with `INVALID_PARTITION_VALUE` regardless of 
`spark.sql.ansi.enabled`, as it does for `INT`. Previously, the value wrapped 
around without an error, for example `b=300` read as `TINYINT` returned `44`. 
Data derived from affected query results may need to be recomputed. To get 
`NULL` for such a value, set `spark. [...]
+- Since Spark 4.4, a cast from `DECIMAL(19, 0)` to `BIGINT` is no longer 
treated as an up-cast because it can overflow. Previously, the optimizer could 
remove such a cast or unwrap it in a comparison, so a query returned a wrong 
result for a value out of the range of `BIGINT` instead of failing with 
`CAST_OVERFLOW`, or instead of using the wrapped-around value when 
`spark.sql.ansi.enabled` is `false`. For example, for a `DECIMAL(19, 0)` column 
`d` with the value `9999999999999999999`, `t [...]
 - Since Spark 4.4, `size` and `cardinality` of a map built by `map(...)` 
return the number of entries of the built map. Previously, the optimizer could 
return the number of key-value pairs instead, for example `size(map(1, 'a', 1, 
'b'))` returned `2` instead of `1` when `spark.sql.mapKeyDedupPolicy` is 
`LAST_WIN`. Data derived from affected query results may need to be recomputed. 
Under the default `EXCEPTION` policy, such a query now fails with 
`DUPLICATED_MAP_KEY`, and a NULL key fails [...]
 - Since Spark 4.4, when `spark.sql.ansi.enabled` is `false`, `size` and 
`cardinality` of an array built by `array(...)` evaluate the LEGACY casts in it 
that can fail, such as a cast from an interval to `TINYINT`, from a string to 
an interval, or, when `spark.sql.preserveCharVarcharTypeInfo` or 
`spark.sql.charVarchar.standardSemantics.enabled` is `true`, to `VARCHAR(n)`. 
Previously, the optimizer could return the number of elements without 
evaluating such a cast. For example, for an `INTE [...]
 - Since Spark 4.4, Data Source V2 file scans correctly evaluate deterministic 
Python UDF filter predicates that reference only partition columns. Previously, 
these predicates could be dropped, returning rows that did not satisfy the 
filter (SPARK-60486). Data derived from affected query results may need to be 
recomputed.
diff --git 
a/sql/api/src/main/scala/org/apache/spark/sql/types/DecimalType.scala 
b/sql/api/src/main/scala/org/apache/spark/sql/types/DecimalType.scala
index bff483cefda9..84ee134d018c 100644
--- a/sql/api/src/main/scala/org/apache/spark/sql/types/DecimalType.scala
+++ b/sql/api/src/main/scala/org/apache/spark/sql/types/DecimalType.scala
@@ -84,6 +84,10 @@ case class DecimalType(precision: Int, scale: Int) extends 
FractionalType {
   private[sql] def isTighterThan(other: DataType): Boolean = other match {
     case dt: DecimalType =>
       (precision - scale) <= (dt.precision - dt.scale) && scale <= dt.scale
+    // `DecimalType.forType(LongType)` is DECIMAL(20, 0), but a DECIMAL(19, 0) 
value can overflow a
+    // long as well, e.g. 9999999999999999999 > Long.MaxValue.
+    case LongType =>
+      precision <= Decimal.MAX_LONG_DIGITS && scale == 0
     case dt: IntegralType =>
       val integerAsDecimal = DecimalType.forType(dt)
       assert(integerAsDecimal.scale == 0)
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
index ed8fa99074c4..4de9d57818ec 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
@@ -816,9 +816,9 @@ case class Cast(
   } else {
     (child.dataType, dataType) match {
       case (_: StringType, BinaryType) => child.nullable
-      // TODO: Implement a more accurate method for checking whether a decimal 
value can be cast
-      //       as integral types without overflow. Currently, the cast can 
overflow even if
-      //       "Cast.canUpCast" method returns true.
+      // TODO: Follow `Cast.canUpCast` as for the other types. It no longer 
allows a decimal to
+      //       integral cast that can overflow (SPARK-60943), but following it 
here would change
+      //       the nullability of `try_cast` from a decimal to an integral 
type.
       case (_: DecimalType, _: IntegralType) => true
       case _ => child.nullable || !Cast.canUpCast(child.dataType, dataType)
     }
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
index c02acc157b32..297340e6b65f 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
@@ -958,6 +958,17 @@ abstract class CastSuiteBase extends SparkFunSuite with 
ExpressionEvalHelper {
     }
   }
 
+  test("SPARK-60943: canUpCast from DECIMAL(p, 0) to an integral type iff 
every value fits") {
+    Seq(ByteType -> BigDecimal(Byte.MaxValue), ShortType -> 
BigDecimal(Short.MaxValue),
+      IntegerType -> BigDecimal(Int.MaxValue), LongType -> 
BigDecimal(Long.MaxValue)).foreach {
+      case (t, max) =>
+        (1 to DecimalType.MAX_PRECISION).foreach { p =>
+          val fits = BigDecimal(10).pow(p) - 1 <= max
+          assert(Cast.canUpCast(DecimalType(p, 0), t) == fits, s"DECIMAL($p, 
0) to $t")
+        }
+    }
+  }
+
   test("SPARK-27671: cast from nested null type in struct") {
     import DataTypeTestUtils._
 
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/SimplifyCastsSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/SimplifyCastsSuite.scala
index 881755001c03..599055d18805 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/SimplifyCastsSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/SimplifyCastsSuite.scala
@@ -148,7 +148,7 @@ class SimplifyCastsSuite extends PlanTest {
   }
 
   test("SPARK-45909: Remove the cast if it can safely up-cast in IsNotNull") {
-    val input = LocalRelation($"a".int, $"b".decimal(18, 0))
+    val input = LocalRelation($"a".int, $"b".decimal(18, 0), $"d".decimal(19, 
0))
     // Remove cast
     comparePlans(
       Optimize.execute(
@@ -166,5 +166,9 @@ class SimplifyCastsSuite extends PlanTest {
       Optimize.execute(
         input.select($"a".cast(DecimalType(2, 1)).as("v")).analyze),
       input.select($"a".cast(DecimalType(2, 1)).as("v")).analyze)
+    // SPARK-60943: DECIMAL(19, 0) values can be out of the range of BIGINT
+    comparePlans(
+      
Optimize.execute(input.select($"d".cast(LongType).isNotNull.as("v")).analyze),
+      input.select($"d".cast(LongType).isNotNull.as("v")).analyze)
   }
 }
diff --git 
a/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/connect/client/arrow/ArrowEncoderSuite.scala
 
b/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/connect/client/arrow/ArrowEncoderSuite.scala
index 6cd9731851f2..20dfe9f4a252 100644
--- 
a/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/connect/client/arrow/ArrowEncoderSuite.scala
+++ 
b/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/connect/client/arrow/ArrowEncoderSuite.scala
@@ -1190,7 +1190,7 @@ class ArrowEncoderSuite extends ConnectFunSuite {
     .test(PrimitiveLongEncoder, _.toLong)
     .test(scalaDecimalEncoder(13, 4), identity)
     .test(StringEncoder, _.toString())
-  UpCastTestCase(scalaDecimalEncoder(19), BigDecimal(_))
+  UpCastTestCase(scalaDecimalEncoder(18), BigDecimal(_))
     .test(PrimitiveLongEncoder, _.toLong)
     .test(scalaDecimalEncoder(23, 1), identity)
     .test(StringEncoder, _.toString())
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala 
b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala
index a83059664162..b37877b0a6a3 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala
@@ -4718,6 +4718,35 @@ class SQLQuerySuite extends SharedSparkSession with 
AdaptiveSparkPlanHelper
     }
   }
 
+  test("SPARK-60943: Don't eliminate a cast from DECIMAL(19, 0) to BIGINT 
which can overflow") {
+    withSQLConf(SQLConf.ANSI_ENABLED.key -> "true") {
+      withTable("dt") {
+        sql("create table dt using parquet as select 9999999999999999999BD as 
d")
+        val msg = intercept[SparkArithmeticException] {
+          sql("select cast(cast(d as bigint) as double) from dt").collect()
+        }.getMessage
+        assert(msg.contains("[CAST_OVERFLOW]"))
+      }
+    }
+  }
+
+  test("SPARK-60943: Don't unwrap a comparison on a cast from DECIMAL(19, 0) 
to BIGINT") {
+    withTable("dt") {
+      sql("create table dt using parquet as select 9999999999999999999BD as d")
+      Seq("true", "false").foreach { ansi =>
+        withSQLConf(SQLConf.ANSI_ENABLED.key -> ansi) {
+          checkAnswer(sql("select try_cast(d as bigint) is not null from dt"), 
Row(false))
+          val query = sql("select d from dt where cast(d as bigint) > 0")
+          if (ansi == "true") {
+            intercept[SparkArithmeticException](query.collect())
+          } else {
+            checkAnswer(query, Seq.empty)
+          }
+        }
+      }
+    }
+  }
+
   test("SPARK-40903: Don't reorder Add for canonicalize if it is decimal 
type") {
     val tableName = "decimalTable"
     withTable(tableName) {


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

Reply via email to