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]

Reply via email to