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]