You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by sa...@apache.org on 2026/09/22 04:19:14 UTC
(spark) branch master updated: [SPARK-57925][SQL] Use DecimalType.DEFAULT_SCALE for Oracle bare NUMBER columns
This is an automated email from the ASF dual-hosted git repository.
sarutak 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 f50d4dbc193e [SPARK-57925][SQL] Use DecimalType.DEFAULT_SCALE for Oracle bare NUMBER columns
f50d4dbc193e is described below
commit f50d4dbc193edddec8a4f73ade09068afcaaf0d3
Author: Shrirang Mhalgi <sh...@gmail.com>
AuthorDate: Tue Sep 22 13:19:00 2026 +0900
[SPARK-57925][SQL] Use DecimalType.DEFAULT_SCALE for Oracle bare NUMBER columns
### What changes were proposed in this pull request?
Replace the hardcoded scale of 10 with `DecimalType.DEFAULT_SCALE` (18) in `OracleDialect.getCatalystType` for bare `NUMBER` columns (precision=0, scale=-127), gated behind `spark.sql.legacy.oracle.numberMapping.enabled` (default false = new scale 18, true = restore old scale 10).
### Why are the changes needed?
Oracle's bare `NUMBER` type (declared without explicit precision or scale) reports precision=0 and scale=-127 via JDBC `ResultSetMetaData`. Spark's `OracleDialect` maps these to `DecimalType(38, 10)` with a hardcoded scale of 10, causing **silent precision loss** for values with more than 10 decimal places.
The hardcoded 10 was an arbitrary pick from 2015 (PR #8780), explicitly labeled "sub-optimal" and "works in our domain for now" by the original author. `DecimalType.DEFAULT_SCALE` (18) is Spark's standard fallback for unknown scale.
**Trade-off:** `Decimal(38,18)` has 20 integer digits vs `Decimal(38,10)`'s 28. Bare `NUMBER` values with more than 20 integer digits that previously read correctly will raise `NUMERIC_VALUE_OUT_OF_RANGE`. The legacy config restores the old mapping for affected users.
### Does this PR introduce _any_ user-facing change?
Yes. Bare `NUMBER` columns now map to `DecimalType(38, 18)` instead of `DecimalType(38, 10)`. Columns with explicit `NUMBER(p,s)` are not affected. Set `spark.sql.legacy.oracle.numberMapping.enabled` to `true` to restore the previous behavior.
### How was this patch tested?
Updated existing `OracleDialect jdbc type mapping` test, added legacy config test, added boundary test exercising `Decimal.set()` overflow (23-digit integer overflows at scale=18, 18-digit fraction preserved), and updated `OracleIntegrationSuite` error message assertion.
### Was this patch authored or co-authored using generative AI tooling?
Yes. Co-Authored using Claude Opus 4.8
Closes #58786 from shrirangmhalgi/SPARK-57925-oracle-number-default-scale.
Authored-by: Shrirang Mhalgi <sh...@gmail.com>
Signed-off-by: Kousuke Saruta <sa...@apache.org>
---
.../spark/sql/jdbc/OracleIntegrationSuite.scala | 4 +-
docs/sql-migration-guide.md | 1 +
.../org/apache/spark/sql/internal/SQLConf.scala | 18 ++++++
.../org/apache/spark/sql/jdbc/OracleDialect.scala | 6 +-
.../org/apache/spark/sql/jdbc/JDBCSuite.scala | 64 ++++++++++++++++++++--
5 files changed, 85 insertions(+), 8 deletions(-)
diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/OracleIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/OracleIntegrationSuite.scala
index ae8817dd2f23..010b231f727e 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/OracleIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/OracleIntegrationSuite.scala
@@ -396,8 +396,8 @@ class OracleIntegrationSuite extends SharedJDBCIntegrationSuite
spark.read.jdbc(jdbcUrl, "tableWithCustomSchema", new Properties()).collect()
}
assert(e.getMessage.contains(
- "The 12312321321321312312312312123.0000000000 rounded half up from" +
- " 12312321321321312312312312123 cannot be represented as Decimal(38, 10)"))
+ "The 12312321321321312312312312123.000000000000000000 rounded half up from" +
+ " 12312321321321312312312312123 cannot be represented as Decimal(38, 18)"))
// custom schema can read data
val props = new Properties()
diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 2675bcd61195..fbb38f8d4c4b 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -26,6 +26,7 @@ license: |
- Since Spark 4.4, when an already-analyzed Data Source V2 query is refreshed after a compatible schema change, connectors can return more data columns from the current table schema in `Scan.readSchema()` than requested by `SupportsPushDownRequiredColumns.pruneColumns`. Previously, this partial pruning could fail planning because the scan reported columns absent from the analyzed relation output.
- Since Spark 4.4, when an already-analyzed Data Source V2 query is refreshed, if rebinding an expanded struct used as a map key causes distinct current keys to collide under the analyzed schema, the query fails with `DUPLICATED_MAP_KEY` by default instead of returning duplicate keys. With `spark.sql.mapKeyDedupPolicy=LAST_WIN`, the last value is kept.
+- Since Spark 4.4, the Oracle JDBC datasource maps bare `NUMBER` columns (declared without explicit precision or scale) to `DecimalType(38, 18)` instead of `DecimalType(38, 10)`. This uses `DecimalType.DEFAULT_SCALE` and preserves up to 18 fractional digits instead of 10, reducing silent precision loss. However, the integer range shrinks from 28 to 20 digits; bare `NUMBER` values with more than 20 integer digits that previously read correctly will raise `NUMERIC_VALUE_OUT_OF_RANGE`. The [...]
- Since Spark 4.4, the optimizer rule `SimplifyCaseConversionExpressions` no longer simplifies mixed case conversions `LOWER(UPPER(str))` to `LOWER(str)` or `UPPER(LOWER(str))` to `UPPER(str)`. This preserves standard Unicode case mapping semantics for non-ASCII characters such as Latin small letter dotless i (`ı` U+0131) and micro sign (`µ` U+00B5).
- Since Spark 4.4, the Oracle JDBC datasource maps Oracle DATE and TIMESTAMP to TimestampNTZType unconditionally, ignoring the JDBC read option `preferTimestampNTZ` (DATE only when the driver default `oracle.jdbc.mapDateToTimestamp=true` surfaces it as TIMESTAMP; otherwise DATE stays DateType). In 4.3 and earlier these were read as TimestampType, or as TimestampNTZType only when `preferTimestampNTZ=true`. This faithfully represents these zoneless Oracle types. Unaffected: Oracle DATE rea [...]
- Since Spark 4.4, a nondeterministic common expression that a conditional branch reads more than once is evaluated once, and every read sees that one value. `BETWEEN` and `NULLIF` read their input twice, so an expression such as `CASE WHEN c THEN monotonically_increasing_id() BETWEEN 3 AND 5 END` previously gave each read its own value and could return a different result. Queries whose results depended on the earlier behaviour change, and an optimized or physical plan may now show a `wi [...]
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 de12d585ee6b..110b030c09ba 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
@@ -7008,6 +7008,21 @@ object SQLConf {
.booleanConf
.createWithDefault(false)
+ val LEGACY_ORACLE_NUMBER_MAPPING_ENABLED =
+ buildConf("spark.sql.legacy.oracle.numberMapping.enabled")
+ .internal()
+ .doc("When true, Oracle bare NUMBER columns (no explicit precision/scale) are mapped " +
+ "to DecimalType(38, 10), preserving the pre-Spark-4.4 behavior. When false (default), " +
+ "they are mapped to DecimalType(38, 18) using DecimalType.DEFAULT_SCALE. The new " +
+ "default preserves more fractional digits (18 vs 10) but reduces the integer range " +
+ "from 28 to 20 digits; bare NUMBER values with more than 20 integer digits that " +
+ "previously read correctly will raise NUMERIC_VALUE_OUT_OF_RANGE. Set to true to " +
+ "restore the old mapping.")
+ .version("4.4.0")
+ .withBindingPolicy(ConfigBindingPolicy.SESSION)
+ .booleanConf
+ .createWithDefault(false)
+
val LEGACY_DB2_TIMESTAMP_MAPPING_ENABLED =
buildConf("spark.sql.legacy.db2.numericMapping.enabled")
.internal()
@@ -9224,6 +9239,9 @@ class SQLConf extends Serializable with Logging with SqlApiConf {
def legacyOracleTimestampNTZMappingEnabled: Boolean =
getConf(LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED)
+ def legacyOracleNumberMappingEnabled: Boolean =
+ getConf(LEGACY_ORACLE_NUMBER_MAPPING_ENABLED)
+
def legacyDB2numericMappingEnabled: Boolean =
getConf(LEGACY_DB2_TIMESTAMP_MAPPING_ENABLED)
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala
index ae28c6b60c27..0dab4c0e9652 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala
@@ -154,6 +154,8 @@ private case class OracleDialect() extends JdbcDialect with SQLConfHelper with N
sqlType match {
case Types.NUMERIC =>
val scale = if (null != md) md.build().getLong("scale") else 0L
+ val defaultScale = if (conf.legacyOracleNumberMappingEnabled) 10
+ else DecimalType.DEFAULT_SCALE
size match {
// Handle NUMBER fields that have no precision/scale in special way
// because JDBC ResultSetMetaData converts this to 0 precision and -127 scale
@@ -161,12 +163,12 @@ private case class OracleDialect() extends JdbcDialect with SQLConfHelper with N
// https://github.com/apache/spark/pull/8780#issuecomment-145598968
// and
// https://github.com/apache/spark/pull/8780#issuecomment-144541760
- case 0 => Option(DecimalType(DecimalType.MAX_PRECISION, 10))
+ case 0 => Option(DecimalType(DecimalType.MAX_PRECISION, defaultScale))
// Handle FLOAT fields in a special way because JDBC ResultSetMetaData converts
// this to NUMERIC with -127 scale
// Not sure if there is a more robust way to identify the field as a float (or other
// numeric types that do not specify a scale.
- case _ if scale == -127L => Option(DecimalType(DecimalType.MAX_PRECISION, 10))
+ case _ if scale == -127L => Option(DecimalType(DecimalType.MAX_PRECISION, defaultScale))
case _ => None
}
case TIMESTAMP_TZ | TIMESTAMP_LTZ =>
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
index 566a34bcf814..4f731ea84f9c 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
@@ -31,7 +31,10 @@ import org.mockito.ArgumentCaptor
import org.mockito.ArgumentMatchers._
import org.mockito.Mockito._
-import org.apache.spark.{SparkException, SparkIllegalArgumentException, SparkSQLException}
+import org.apache.spark.{
+ SparkArithmeticException, SparkException,
+ SparkIllegalArgumentException, SparkSQLException
+}
import org.apache.spark.executor.InputMetrics
import org.apache.spark.sql.{AnalysisException, DataFrame, Observation, Row}
import org.apache.spark.sql.catalyst.{analysis, TableIdentifier}
@@ -1675,10 +1678,14 @@ class JDBCSuite extends SharedSparkSession {
test("OracleDialect jdbc type mapping") {
val oracleDialect = JdbcDialects.get("jdbc:oracle")
val metadata = new MetadataBuilder().putString("name", "test_column").putLong("scale", -127)
- assert(oracleDialect.getCatalystType(java.sql.Types.NUMERIC, "float", 1, metadata) ==
- Some(DecimalType(DecimalType.MAX_PRECISION, 10)))
+ // Bare NUMBER (precision=0) uses DEFAULT_SCALE (18) by default
assert(oracleDialect.getCatalystType(java.sql.Types.NUMERIC, "numeric", 0, null) ==
- Some(DecimalType(DecimalType.MAX_PRECISION, 10)))
+ Some(DecimalType(DecimalType.MAX_PRECISION, DecimalType.DEFAULT_SCALE)))
+ // FLOAT (scale=-127) also uses DEFAULT_SCALE
+ assert(oracleDialect.getCatalystType(java.sql.Types.NUMERIC, "float", 1, metadata) ==
+ Some(DecimalType(DecimalType.MAX_PRECISION, DecimalType.DEFAULT_SCALE)))
+ // Explicit precision/scale columns are NOT affected (falls through to None)
+ assert(oracleDialect.getCatalystType(java.sql.Types.NUMERIC, "numeric", 10, null) == None)
assert(oracleDialect.getCatalystType(OracleDialect.BINARY_FLOAT, "BINARY_FLOAT", 0, null) ==
Some(FloatType))
assert(oracleDialect.getCatalystType(OracleDialect.BINARY_DOUBLE, "BINARY_DOUBLE", 0, null) ==
@@ -1689,6 +1696,55 @@ class JDBCSuite extends SharedSparkSession {
Some(TimestampType))
}
+ test("SPARK-57925: Oracle bare NUMBER legacy config restores scale=10") {
+ val oracleDialect = JdbcDialects.get("jdbc:oracle")
+ val metadata = new MetadataBuilder().putString("name", "test_column").putLong("scale", -127)
+ withSQLConf(SQLConf.LEGACY_ORACLE_NUMBER_MAPPING_ENABLED.key -> "true") {
+ assert(oracleDialect.getCatalystType(java.sql.Types.NUMERIC, "numeric", 0, null) ==
+ Some(DecimalType(DecimalType.MAX_PRECISION, 10)))
+ assert(oracleDialect.getCatalystType(java.sql.Types.NUMERIC, "float", 1, metadata) ==
+ Some(DecimalType(DecimalType.MAX_PRECISION, 10)))
+ }
+ }
+
+ test("SPARK-57925: Oracle bare NUMBER boundary - fraction preserved, large int overflows") {
+ val oracleDialect = JdbcDialects.get("jdbc:oracle")
+ val result = oracleDialect.getCatalystType(java.sql.Types.NUMERIC, "numeric", 0, null)
+ assert(result == Some(DecimalType(DecimalType.MAX_PRECISION, DecimalType.DEFAULT_SCALE)))
+ val p = DecimalType.MAX_PRECISION
+ val newScale = DecimalType.DEFAULT_SCALE // 18
+ val oldScale = 10
+
+ // High-precision fraction: 18 fractional digits (14 significant).
+ // New Decimal(38,18) preserves it; old Decimal(38,10) would truncate to 10 digits.
+ val highPrecFraction = new java.math.BigDecimal("0.000012345678912345")
+ val preserved = Decimal(highPrecFraction, p, newScale)
+ assert(preserved.toBigDecimal.scale == newScale)
+ assert(preserved.toString == "0.000012345678912345")
+
+ // Large integer: 23 digits.
+ // Old Decimal(38,10) fits (28 integer digits);
+ // new Decimal(38,18) overflows (20 integer digits).
+ val largeInt = new java.math.BigDecimal("12345678901234567890123")
+ // Old mapping: fits
+ val legacyDecimal = Decimal(largeInt, p, oldScale)
+ assert(legacyDecimal.toString.startsWith("12345678901234567890123"))
+ // New mapping: overflows
+ val ex = intercept[SparkArithmeticException] {
+ Decimal(largeInt, p, newScale)
+ }
+ assert(ex.getCondition == "NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION")
+
+ // Legacy config restores 28 integer digits via type mapping
+ withSQLConf(SQLConf.LEGACY_ORACLE_NUMBER_MAPPING_ENABLED.key -> "true") {
+ val legacyResult = oracleDialect.getCatalystType(
+ java.sql.Types.NUMERIC, "numeric", 0, null)
+ val legacyDt = legacyResult.get.asInstanceOf[DecimalType]
+ assert(legacyDt.precision - legacyDt.scale == 28,
+ "legacy integer range should be 28 digits")
+ }
+ }
+
test("SPARK-58876: Oracle stamps the NTZ wall-clock write marker, legacy-gated, even wrapped") {
val custom = new JdbcDialect {
override def canHandle(url: String): Boolean = url.startsWith("jdbc:oracle")
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org