This is an automated email from the ASF dual-hosted git repository.
uros-b 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 a15101a7c8d2 [SPARK-60420][SQL] Add JDBC options to control Oracle
TimestampNTZ mapping and wall-clock conversion separately
a15101a7c8d2 is described below
commit a15101a7c8d21ca25f106a1f3460bfc337495711
Author: aleksandar-trajkovic-db <[email protected]>
AuthorDate: Fri Oct 30 23:58:14 2026 +0000
[SPARK-60420][SQL] Add JDBC options to control Oracle TimestampNTZ mapping
and wall-clock conversion separately
### What changes were proposed in this pull request?
Adds two JDBC options that override
`spark.sql.legacy.oracle.timestampNTZMapping.enabled`. When unset, both default
to `JdbcDialect.timestampNTZAsWallClock` (Oracle: the legacy conf; other
dialects: false):
- `zonelessTimestampAsNTZ`: infers zoneless timestamp types as
TimestampNTZType; only the Oracle dialect honors it. `JdbcUtils.getSchema`
passes it to the dialect through the column metadata. On JDBC catalog tables it
applies only as a catalog option.
- `timestampNTZAsWallClock`: the formerly internal
`__timestampNTZAsWallClock`; reads and writes TimestampNTZType values as
`java.time.LocalDateTime`.
### Why are the changes needed?
1. Goal: Let users choose the Oracle TimestampNTZ type mapping and the
value conversion per read or write.
2. Problem: Only an internal session conf switches both together. For an
Oracle `TIMESTAMP` column read with `preferTimestampNTZ=false`:
`Before: TimestampNTZType (option ignored)` -> `Target with
zonelessTimestampAsNTZ=false: TimestampType`
### Does this PR introduce _any_ user-facing change?
Yes, relative to unreleased master only: the new JDBC options
`zonelessTimestampAsNTZ` and `timestampNTZAsWallClock` (renamed from the
internal `__timestampNTZAsWallClock`). Default behavior is unchanged.
### How was this patch tested?
Existing tests in `JDBCSuite` and `OracleIntegrationSuite`, extended with
the new options.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (Anthropic Claude Opus 5.5)
This pull request and its description were written by Isaac.
---
This is my original work and I license it to the project under the
project's open source license.
Closes #59671 from aleksandar-trajkovic-db/getCatalystType-options.
Authored-by: aleksandar-trajkovic-db <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
---
.../spark/sql/jdbc/OracleIntegrationSuite.scala | 54 ++++++++++++++++++----
.../spark/sql/jdbc/v2/OracleIntegrationSuite.scala | 44 ++++++++++++++----
docs/sql-data-sources-jdbc.md | 16 +++++++
docs/sql-migration-guide.md | 2 +-
.../org/apache/spark/sql/internal/SQLConf.scala | 7 ++-
.../execution/datasources/jdbc/JDBCOptions.scala | 16 ++++++-
.../sql/execution/datasources/jdbc/JdbcUtils.scala | 4 ++
.../datasources/v2/jdbc/JDBCTableCatalog.scala | 3 +-
.../org/apache/spark/sql/jdbc/JdbcDialects.scala | 4 +-
.../org/apache/spark/sql/jdbc/OracleDialect.scala | 15 +++++-
.../org/apache/spark/sql/jdbc/JDBCSuite.scala | 37 ++++++++-------
11 files changed, 157 insertions(+), 45 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 d83a77313ddc..428331183b88 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
@@ -594,15 +594,18 @@ class OracleIntegrationSuite extends
SharedJDBCIntegrationSuite
}
test("Oracle DATE and TIMESTAMP read as TimestampNTZType by default; " +
- "TimestampType under the legacy flag") {
+ "TimestampType under the legacy flag; the NTZ options override the
flag") {
// Read the `datetime` table's DATE column D (1991-11-09, no time zone)
and TIMESTAMP column T
// (1996-01-01 01:23:45) in the driver's default mode
(oracle.jdbc.mapDateToTimestamp=true), so
// both arrive under JDBC Types.TIMESTAMP.
- def readDatetime(): DataFrame =
+ def readDatetime(options: Map[String, String] = Map.empty): DataFrame =
spark.read.format("jdbc")
.option("url", jdbcUrl)
.option("dbtable", "datetime")
+ .options(options)
.load()
+ val ntzRow =
+ Row(LocalDateTime.of(1991, 11, 9, 0, 0, 0), LocalDateTime.of(1996, 1, 1,
1, 23, 45))
// Default: both Oracle DATE and TIMESTAMP -> TimestampNTZType.
val df = readDatetime()
@@ -621,6 +624,18 @@ class OracleIntegrationSuite extends
SharedJDBCIntegrationSuite
assert(fracDf.select("T").collect().head.get(0)
=== LocalDateTime.of(1996, 1, 1, 1, 23, 45, 123456000))
+ val optOutDf = readDatetime(Map("zonelessTimestampAsNTZ" -> "false"))
+ assert(optOutDf.schema("D").dataType === TimestampType)
+ assert(optOutDf.schema("T").dataType === TimestampType)
+
+ // Conversion off keeps the NTZ mapping but reads through a JVM-zone
java.sql.Timestamp;
+ // LA is UTC-08:00 on this date, so 01:23:45 reads back as 09:23:45.
+ withDefaultTimeZone(LA) {
+ val noWallClockDf = readDatetime(Map("timestampNTZAsWallClock" ->
"false"))
+ assert(noWallClockDf.schema("T").dataType === TimestampNTZType)
+ checkAnswer(noWallClockDf.select("T"), Row(LocalDateTime.of(1996, 1, 1,
9, 23, 45)))
+ }
+
// Legacy flag on: both fall back to TimestampType.
withSQLConf(SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key ->
"true") {
val legacyDf = readDatetime()
@@ -629,6 +644,19 @@ class OracleIntegrationSuite extends
SharedJDBCIntegrationSuite
val legacyRow = legacyDf.select("D", "T").collect().head
assert(legacyRow.getTimestamp(0) === Timestamp.valueOf("1991-11-09
00:00:00"))
assert(legacyRow.getTimestamp(1) === Timestamp.valueOf("1996-01-01
01:23:45"))
+
+ withDefaultTimeZone(LA) {
+ val optInDf = readDatetime(
+ Map("zonelessTimestampAsNTZ" -> "true", "timestampNTZAsWallClock" ->
"true"))
+ assert(optInDf.schema("T").dataType === TimestampNTZType)
+ checkAnswer(optInDf.select("D", "T"), ntzRow)
+ withTable("datetime_ntz") {
+ sql(s"CREATE TABLE datetime_ntz USING org.apache.spark.sql.jdbc
OPTIONS (url " +
+ s"'$jdbcUrl', dbtable 'datetime', zonelessTimestampAsNTZ 'true', "
+
+ "timestampNTZAsWallClock 'true')")
+ checkAnswer(sql("SELECT D, T FROM datetime_ntz"), ntzRow)
+ }
+ }
}
}
@@ -776,24 +804,30 @@ class OracleIntegrationSuite extends
SharedJDBCIntegrationSuite
}
}
- test("legacy flag writes TimestampNTZ using the base conversion") {
+ test("legacy flag writes TimestampNTZ using the base conversion; " +
+ "timestampNTZAsWallClock overrides the flag") {
// Legacy write uses the base conversion, so in a non-UTC zone the default
wall-clock reader
// sees base(ldt).toLocalDateTime, not ldt (a wall-clock write would
round-trip to ldt here).
val ldt = LocalDateTime.of(1996, 1, 1, 1, 23, 45, 123456000)
val schema = StructType(Seq(StructField("T", TimestampNTZType)))
- withDefaultTimeZone(LA) {
- withSQLConf(SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key ->
"true") {
+ def writeAndRead(legacy: String, options: Map[String, String]): DataFrame
= {
+ withSQLConf(SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key ->
legacy) {
spark.createDataFrame(spark.sparkContext.parallelize(Seq(Row(ldt))),
schema)
- .write.format("jdbc").mode(SaveMode.Overwrite)
+ .write.format("jdbc").mode(SaveMode.Overwrite).options(options)
.option("url", jdbcUrl).option("dbtable", "ntz_legacy_write").save()
}
- val expected = DateTimeUtils.toJavaTimestampNoRebase(
- DateTimeUtils.localDateTimeToMicros(ldt)).toLocalDateTime
- assert(expected != ldt) // guard: LA must shift base vs wall-clock for
this to discriminate
val df = spark.read.format("jdbc")
.option("url", jdbcUrl).option("dbtable", "ntz_legacy_write").load()
assert(df.schema.fields.head.dataType === TimestampNTZType)
- assert(df.collect().head.get(0) === expected)
+ df
+ }
+ withDefaultTimeZone(LA) {
+ val expected = DateTimeUtils.toJavaTimestampNoRebase(
+ DateTimeUtils.localDateTimeToMicros(ldt)).toLocalDateTime
+ assert(expected != ldt) // guard: LA must shift base vs wall-clock for
this to discriminate
+ checkAnswer(writeAndRead("true", Map.empty), Row(expected))
+ checkAnswer(writeAndRead("true", Map("timestampNTZAsWallClock" ->
"true")), Row(ldt))
+ checkAnswer(writeAndRead("false", Map("timestampNTZAsWallClock" ->
"false")), Row(expected))
}
}
diff --git
a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
index 7920084527e3..6e88d9e44616 100644
---
a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
+++
b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
@@ -17,7 +17,7 @@
package org.apache.spark.sql.jdbc.v2
-import java.sql.Connection
+import java.sql.{Connection, Timestamp}
import java.time.LocalDateTime
import java.util.Locale
@@ -27,9 +27,9 @@ import org.apache.spark.{SparkConf, SparkRuntimeException}
import org.apache.spark.sql.{AnalysisException, Row}
import
org.apache.spark.sql.catalyst.util.CharVarcharUtils.CHAR_VARCHAR_TYPE_STRING_METADATA_KEY
import org.apache.spark.sql.catalyst.util.DateTimeTestUtils._
+import org.apache.spark.sql.catalyst.util.DateTimeUtils
import org.apache.spark.sql.connector.catalog.{Identifier, TableCatalog}
import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog
-import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.jdbc.OracleDatabaseOnDocker
import org.apache.spark.sql.types._
import org.apache.spark.tags.DockerTest
@@ -187,15 +187,43 @@ class OracleIntegrationSuite extends
DockerJDBCIntegrationV2Suite with V2JDBCTes
override def caseConvert(tableName: String): String =
tableName.toUpperCase(Locale.ROOT)
- test("SPARK-58876: a V2 NTZ read planned before a legacy flag flip still
reads wall-clock") {
+ test("SPARK-60420: V2 NTZ reads and writes honor per-query
timestampNTZAsWallClock") {
val table = s"$catalogName.${namespaceOpt.get}.${caseConvert("datetime")}"
+ val ldt = LocalDateTime.of(2022, 5, 19, 0, 0, 0)
+ val noWallClock = "'timestampNTZAsWallClock' = 'false'"
withDefaultTimeZone(LA) {
- val df =
withSQLConf(SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key -> "false")
{
- sql(s"SELECT time1 FROM $table WHERE name = 'amy'")
+ // With the conversion off, values go through a JVM-default-zone
java.sql.Timestamp.
+ val readShifted = DateTimeUtils.microsToLocalDateTime(
+ DateTimeUtils.fromJavaTimestampNoRebase(Timestamp.valueOf(ldt)))
+ val perRead = sql(s"SELECT time1 FROM $table WITH ($noWallClock) WHERE
name = 'amy'")
+ assert(perRead.schema.head.dataType === TimestampNTZType)
+ checkAnswer(perRead, Row(readShifted))
+
+ // Reading back with the defaults shows the stored wall-clock exactly.
+ val target =
s"$catalogName.${namespaceOpt.get}.${caseConvert("ntz_write")}"
+ withTable(target) {
+ sql(s"CREATE TABLE $target (t TIMESTAMP_NTZ)")
+ sql(s"INSERT INTO $target SELECT TIMESTAMP_NTZ'2022-05-19 00:00:00'")
+ sql(s"INSERT INTO $target WITH ($noWallClock) SELECT
TIMESTAMP_NTZ'2022-05-20 00:00:00'")
+ val writeShifted = DateTimeUtils.toJavaTimestampNoRebase(
+ DateTimeUtils.localDateTimeToMicros(LocalDateTime.of(2022, 5, 20, 0,
0, 0)))
+ .toLocalDateTime
+ checkAnswer(sql(s"SELECT t FROM $target"), Seq(ldt,
writeShifted).map(Row(_)))
}
- assert(df.schema.head.dataType === TimestampNTZType)
- withSQLConf(SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key ->
"true") {
- assert(df.collect().head.get(0) === LocalDateTime.of(2022, 5, 19, 0,
0, 0))
+ }
+ }
+
+ test("SPARK-60420: zonelessTimestampAsNTZ set on the catalog decides the V2
table schema") {
+ val table = s"${namespaceOpt.get}.${caseConvert("datetime")}"
+ withSQLConf(
+ "spark.sql.catalog.oracle_ltz" -> classOf[JDBCTableCatalog].getName,
+ "spark.sql.catalog.oracle_ltz.url" -> db.getJdbcUrl(dockerIp,
externalPort),
+ "spark.sql.catalog.oracle_ltz.zonelessTimestampAsNTZ" -> "false") {
+ Seq(catalogName -> TimestampNTZType, "oracle_ltz" ->
TimestampType).foreach {
+ case (catalog, expected) =>
+ val schema = spark.table(s"$catalog.$table").schema
+ assert(schema("DATE1").dataType === expected)
+ assert(schema("TIME1").dataType === expected)
}
}
}
diff --git a/docs/sql-data-sources-jdbc.md b/docs/sql-data-sources-jdbc.md
index 6240abe67f0e..4a7609c5cd88 100644
--- a/docs/sql-data-sources-jdbc.md
+++ b/docs/sql-data-sources-jdbc.md
@@ -382,6 +382,22 @@ logging into the data sources.
</td>
<td>read</td>
</tr>
+ <tr>
+ <td><code>zonelessTimestampAsNTZ</code></td>
+ <td><code>true</code> for Oracle, <code>false</code> for other
dialects</td>
+ <td>
+ When the option is set to <code>true</code>, timestamp types without a
time zone are inferred as Spark's TimestampNTZ type regardless of
<code>preferTimestampNTZ</code>. Otherwise, they follow
<code>preferTimestampNTZ</code>. This option is currently only supported by
Oracle. Its default follows the dialect's legacy TimestampNTZ mapping
configuration: it is <code>false</code> when that configuration is enabled.
Depending on the driver, values read as TimestampNTZ may not keep their [...]
+ </td>
+ <td>read/write</td>
+ </tr>
+ <tr>
+ <td><code>timestampNTZAsWallClock</code></td>
+ <td><code>true</code> for Oracle, <code>false</code> for other
dialects</td>
+ <td>
+ When the option is set to <code>true</code>, TimestampNTZ values are
read and written as <code>java.time.LocalDateTime</code>, preserving the
wall-clock value without a time zone conversion. This requires a JDBC driver
that supports <code>java.time.LocalDateTime</code>. Otherwise, they are
converted through <code>java.sql.Timestamp</code> in the JVM default time zone.
Its default follows the dialect's legacy TimestampNTZ mapping configuration: it
is <code>false</code> when that con [...]
+ </td>
+ <td>read/write</td>
+ </tr>
<tr>
<td><code>hint</code></td>
<td>(none)</td>
diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index d664602e6fe5..99798a3e5001 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -47,7 +47,7 @@ license: |
- Since Spark 4.4, when `spark.sql.charVarchar.standardSemantics.enabled` is
false and `spark.sql.readSideCharPadding` is true (the default), JDBC Data
Source V2 catalog tables on H2, PostgreSQL, SQL Server, and Oracle push CHAR
equality, inequality, null-safe equality, and `IN`/`NOT IN`. In 4.3 the default
read-side padding wrap kept those filters in Spark. Pushed predicates use the
remote collation, so a case-insensitive SQL Server collation can match a stored
`'ABC'` with `flag = 'abc [...]
- Since Spark 4.4, when `spark.sql.charVarchar.standardSemantics.enabled` is
false and `spark.sql.readSideCharPadding` is true (the default), `IS [NOT]
NULL` over a CHAR scan column is unwrapped to the raw column for every Data
Source V2 source, including every JDBC dialect. Previously the padding wrap
kept that null check in Spark.
- 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, the Oracle JDBC datasource maps Oracle DATE and TIMESTAMP
to TimestampNTZType by default, 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 read wit [...]
- 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 [...]
- Since Spark 4.4, `format_string` and `printf` pass a decimal argument to
`java.util.Formatter` as a `java.math.BigDecimal`, so the floating-point
conversions `%f`, `%e`, and `%g` format decimals instead of failing with
`IllegalFormatConversionException`. `%a` still rejects a decimal, because
`Formatter` supports it only for `float` and `double`. As a side effect, `%h`
on a decimal now hashes the `java.math.BigDecimal` (unscaled value and scale)
rather than Spark's internal `Decimal` (w [...]
- Since Spark 4.4, for storage-partitioned joins,
`spark.sql.requireAllClusterKeysForCoPartition` requires every join key to be
covered by some partition key instead of matching the partition keys
positionally. As a result, a join-key column partitioned by more than one
transform no longer prevents shuffle elimination, and
`spark.sql.sources.v2.bucketing.allowKeysSubsetOfPartitionKeys.enabled` no
longer additionally requires `spark.sql.requireAllClusterKeysForCoPartition` to
be `false` w [...]
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 4253cf72cd24..b29ee4579ecd 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
@@ -7372,8 +7372,11 @@ object SQLConf {
"behavior. When false (default), it is read as TimestampNTZType, which
faithfully " +
"represents these zoneless Oracle types. The same flag governs the
write path: when " +
"false a TimestampNTZType column is written to Oracle zoneless via
setObject, and when " +
- "true via a JVM-default-zone java.sql.Timestamp; these can differ for
wall-clocks in a " +
- "DST gap. The flag is read at schema-resolution and write time, so a
JDBC relation " +
+ "true via a JVM-default-zone java.sql.Timestamp; the stored values may
differ " +
+ "depending on the JVM default time zone. The JDBC " +
+ "options zonelessTimestampAsNTZ (type mapping) and " +
+ "timestampNTZAsWallClock (read and write conversion) take precedence
over this flag " +
+ "when set. The flag is read at schema-resolution and write time, so a
JDBC relation " +
"whose schema was already resolved (e.g. a cached or
metastore-registered table) must " +
"be re-resolved for a change to take effect. Oracle DATE read as JDBC
DATE " +
"(oracle.jdbc.mapDateToTimestamp=false, mapped to DateType), TIMESTAMP
WITH TIME ZONE, " +
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCOptions.scala
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCOptions.scala
index a5c866cbfa38..b28a3c98dc20 100644
---
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCOptions.scala
+++
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCOptions.scala
@@ -266,11 +266,22 @@ class JDBCOptions(
.map(_.toBoolean)
.getOrElse(false)
+ private lazy val dialectTimestampNTZAsWallClock: Boolean =
+ JdbcDialects.get(url).timestampNTZAsWallClock
+
+ // Reads and writes TimestampNTZType values as java.time.LocalDateTime.
val timestampNTZAsWallClock: Boolean =
parameters
.get(JDBC_TIMESTAMP_NTZ_WALL_CLOCK)
.map(_.toBoolean)
- .getOrElse(JdbcDialects.get(url).timestampNTZAsWallClock)
+ .getOrElse(dialectTimestampNTZAsWallClock)
+
+ // Infers zoneless timestamp types as TimestampNTZType. Only honored by
OracleDialect for now.
+ val zonelessTimestampAsNTZ: Boolean =
+ parameters
+ .get(JDBC_ZONELESS_TIMESTAMP_AS_NTZ)
+ .map(_.toBoolean)
+ .getOrElse(dialectTimestampNTZAsWallClock)
val hint = parameters.get(JDBC_HINT_STRING).map(value => {
require(value.matches("(?s)^/\\*\\+ .* \\*/$"),
@@ -386,5 +397,6 @@ object JDBCOptions {
val JDBC_PREFER_TIMESTAMP_NTZ = newOption("preferTimestampNTZ")
val JDBC_PREFER_TIMESTAMP_NANOS = newOption("preferTimestampNanos")
val JDBC_HINT_STRING = newOption("hint")
- val JDBC_TIMESTAMP_NTZ_WALL_CLOCK = newOption("__timestampNTZAsWallClock")
+ val JDBC_TIMESTAMP_NTZ_WALL_CLOCK = newOption("timestampNTZAsWallClock")
+ val JDBC_ZONELESS_TIMESTAMP_AS_NTZ = newOption("zonelessTimestampAsNTZ")
}
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala
index 87080108b690..bdb865f2f6c4 100644
---
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala
+++
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala
@@ -352,6 +352,8 @@ object JdbcUtils extends Logging with SQLConfHelper {
metadata.putBoolean("isSigned", isSigned)
metadata.putBoolean("isTimestampNTZ", options.preferTimestampNTZ)
metadata.putBoolean("preferTimestampNanos", options.preferTimestampNanos)
+ metadata.putBoolean(
+ JDBCOptions.JDBC_ZONELESS_TIMESTAMP_AS_NTZ,
options.zonelessTimestampAsNTZ)
metadata.putLong("scale", fieldScale)
metadata.putString("jdbcClientType", typeName)
dialect.updateExtraColumnMeta(conn, rsmd, i + 1, metadata)
@@ -359,6 +361,8 @@ object JdbcUtils extends Logging with SQLConfHelper {
val columnType =
dialect.getCatalystType(dataType, typeName, fieldSize,
metadata).getOrElse(
getCatalystType(dataType, typeName, fieldSize, fieldScale, isSigned,
options))
+ // Only an input to the dialect's type mapping, so keep it out of the
schema.
+ metadata.remove(JDBCOptions.JDBC_ZONELESS_TIMESTAMP_AS_NTZ)
fields(i) = StructField(columnName, columnType, nullable,
metadata.build())
i = i + 1
}
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalog.scala
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalog.scala
index ee1794b63c7f..ae29184a5824 100644
---
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalog.scala
+++
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalog.scala
@@ -154,8 +154,7 @@ class JDBCTableCatalog extends TableCatalog
override def loadTable(ident: Identifier): Table = {
JdbcUtils.withConnection(options) { conn =>
val tableOptions = new JDBCOptions(
- options.parameters + (JDBCOptions.JDBC_TABLE_NAME ->
getTableName(ident)) +
- (JDBCOptions.JDBC_TIMESTAMP_NTZ_WALL_CLOCK ->
dialect.timestampNTZAsWallClock.toString))
+ options.parameters + (JDBCOptions.JDBC_TABLE_NAME ->
getTableName(ident)))
JdbcUtils.classifyException(
condition = "FAILED_JDBC.LOAD_TABLE",
messageParameters = Map(
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/JdbcDialects.scala
b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/JdbcDialects.scala
index c2ae86ea9d4f..17cf7009d5e1 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/JdbcDialects.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/JdbcDialects.scala
@@ -220,8 +220,8 @@ abstract class JdbcDialect extends Serializable with
Logging {
}
/**
- * Whether TimestampNTZType values are read and written as zoneless
`LocalDateTime` rather than
- * through `java.sql.Timestamp`. Snapshotted into `JDBCOptions` when the
options are built.
+ * Default of the `timestampNTZAsWallClock` and `zonelessTimestampAsNTZ`
JDBC options.
+ * Snapshotted into `JDBCOptions` when the options are built.
*/
@Since("4.4.0")
def timestampNTZAsWallClock: Boolean = false
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 d9147f7a7c95..db4b475d0f01 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
@@ -213,8 +213,9 @@ private case class OracleDialect() extends JdbcDialect with
SQLConfHelper with N
case BINARY_DOUBLE => Some(DoubleType) // Value for
OracleTypes.BINARY_DOUBLE
case INTERVAL_YM => Some(YearMonthIntervalType())
case INTERVAL_DS => Some(DayTimeIntervalType())
- case TIMESTAMP if !conf.legacyOracleTimestampNTZMappingEnabled &&
typeName != null &&
- typeName.toUpperCase(Locale.ROOT).matches("DATE|TIMESTAMP") =>
+ case TIMESTAMP if typeName != null &&
+ typeName.toUpperCase(Locale.ROOT).matches("DATE|TIMESTAMP") &&
+ zonelessTimestampAsNTZ(md) =>
val metadata = if (md != null) md.build() else Metadata.empty
// Absent scale metadata: Oracle TIMESTAMP defaults to TIMESTAMP(6).
val scale = if (metadata.contains("scale"))
metadata.getLong("scale").toInt else 6
@@ -227,6 +228,16 @@ private case class OracleDialect() extends JdbcDialect
with SQLConfHelper with N
}
}
+ // JdbcUtils.getSchema passes the zonelessTimestampAsNTZ option through the
column metadata.
+ private def zonelessTimestampAsNTZ(md: MetadataBuilder): Boolean = {
+ val metadata = if (md != null) md.build() else Metadata.empty
+ if (metadata.contains(JDBCOptions.JDBC_ZONELESS_TIMESTAMP_AS_NTZ)) {
+ metadata.getBoolean(JDBCOptions.JDBC_ZONELESS_TIMESTAMP_AS_NTZ)
+ } else {
+ timestampNTZAsWallClock
+ }
+ }
+
override def timestampNTZAsWallClock: Boolean =
!conf.legacyOracleTimestampNTZMappingEnabled
override def getJDBCType(dt: DataType): Option[JdbcType] = dt match {
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 5efe9f10ad0b..c06d9586e7ce 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
@@ -1792,10 +1792,8 @@ class JDBCSuite extends SharedSparkSession {
assert(!NoopDialect.timestampNTZAsWallClock)
}
- test("SPARK-58876: Oracle NTZ mapping and the preferTimestampNTZ read
option") {
- // preferTimestampNTZ reaches the dialect only through the options passed
to getSchema, so
- // resolve a mocked Oracle TIMESTAMP column via getSchema under each
(prefer, flag) combination.
- def resolve(preferTimestampNTZ: Boolean): StructField = {
+ test("SPARK-58876: Oracle NTZ mapping, preferTimestampNTZ and
zonelessTimestampAsNTZ") {
+ def resolve(preferTimestampNTZ: Boolean, zoneless: Option[Boolean]):
StructField = {
val rsmd = mock(classOf[java.sql.ResultSetMetaData])
when(rsmd.getColumnCount).thenReturn(1)
when(rsmd.getColumnLabel(anyInt())).thenReturn("T")
@@ -1808,20 +1806,23 @@ class JDBCSuite extends SharedSparkSession {
val rs = mock(classOf[ResultSet])
when(rs.getMetaData).thenReturn(rsmd)
val options = new JDBCOptions("jdbc:oracle:thin:@//host:1521/db", "t",
- Map("preferTimestampNTZ" -> preferTimestampNTZ.toString))
+ Map("preferTimestampNTZ" -> preferTimestampNTZ.toString) ++
+ zoneless.map("zonelessTimestampAsNTZ" -> _.toString))
JdbcUtils.getSchema(mock(classOf[Connection]), rs, OracleDialect(),
options).fields.head
}
- // Non-legacy always maps to NTZ regardless of preferTimestampNTZ; the
legacy flag defers to the
- // shared mapping, which honors preferTimestampNTZ.
+ // zonelessTimestampAsNTZ, defaulting to !legacy, maps to NTZ regardless
of preferTimestampNTZ;
+ // otherwise the shared mapping honors preferTimestampNTZ.
for {
legacy <- Seq(true, false)
prefer <- Seq(true, false)
+ zoneless <- Seq(None, Some(true), Some(false))
} {
withSQLConf(SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key ->
legacy.toString) {
- val field = resolve(prefer)
- val expectedType = if (legacy && !prefer) TimestampType else
TimestampNTZType
- val hint = s"legacy=$legacy prefer=$prefer"
+ val field = resolve(prefer, zoneless)
+ val expectedType =
+ if (zoneless.getOrElse(!legacy) || prefer) TimestampNTZType else
TimestampType
+ val hint = s"legacy=$legacy prefer=$prefer zoneless=$zoneless"
assert(field.dataType === expectedType, hint)
}
}
@@ -1848,10 +1849,14 @@ class JDBCSuite extends SharedSparkSession {
test("SPARK-58876: Oracle NTZ writes pick the setter from the snapshotted
options") {
val ldt = LocalDateTime.of(1991, 11, 9, 0, 0, 0)
val schema = new StructType().add("t", TimestampNTZType, nullable = true)
- for (legacy <- Seq(true, false)) {
+ for {
+ legacy <- Seq(true, false)
+ wallClock <- Seq(None, Some(true), Some(false))
+ } {
val options = withSQLConf(
SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key ->
legacy.toString) {
- new JDBCOptions("jdbc:oracle:thin:@//host:1521/db", "t",
Map.empty[String, String])
+ new JDBCOptions("jdbc:oracle:thin:@//host:1521/db", "t",
+ wallClock.map("timestampNTZAsWallClock" -> _.toString).toMap)
}
val stmt = mock(classOf[java.sql.PreparedStatement])
val conn = mock(classOf[Connection])
@@ -1867,12 +1872,12 @@ class JDBCSuite extends SharedSparkSession {
} finally {
TaskContext.unset()
}
- if (legacy) {
- verify(stmt).setTimestamp(1,
dialect.convertTimestampNTZToJavaTimestamp(ldt))
- verify(stmt, never()).setObject(anyInt(), any())
- } else {
+ if (wallClock.getOrElse(!legacy)) {
verify(stmt).setObject(1, ldt)
verify(stmt, never()).setTimestamp(anyInt(), any())
+ } else {
+ verify(stmt).setTimestamp(1,
dialect.convertTimestampNTZToJavaTimestamp(ldt))
+ verify(stmt, never()).setObject(anyInt(), any())
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]