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 5d419fe0f489 [SPARK-60420][SQL][FOLLOWUP] Preserve JDBC timestamp
defaults in V2 builders
5d419fe0f489 is described below
commit 5d419fe0f489c5274c0293c4c92874e20bd9f310
Author: Uros Stankovic <[email protected]>
AuthorDate: Mon Nov 2 12:57:28 2026 +0000
[SPARK-60420][SQL][FOLLOWUP] Preserve JDBC timestamp defaults in V2 builders
### What changes were proposed in this pull request?
Preserve the table's resolved `timestampNTZAsWallClock` and
`zonelessTimestampAsNTZ` defaults when creating JDBC V2 scan and write options.
Keep type mapping bound to the table's resolved `zonelessTimestampAsNTZ`; allow
case-insensitive per-query `timestampNTZAsWallClock` conversion overrides.
Preserve the existing raw merge for case-sensitive JDBC driver properties. Keep
`spark.sql.legacy.oracle.timestampNTZMapping.enabled`.
### Why are the changes needed?
A V2 table can resolve its schema with the Oracle legacy flag disabled,
then construct a scan after the flag is enabled. Reconstructing JDBC options
from only the raw parameters re-evaluates the conversion default while
retaining the previously resolved schema. In Los Angeles, a stored `2022-05-19
00:00:00` can consequently become `07:00:00` in an NTZ column.
Carry the already-resolved defaults into both scan and write options.
Pushed aggregates must also retain the table's type mapping: a per-query
`zonelessTimestampAsNTZ` override can otherwise re-infer a different output
type and shift results during the cast back to the table's type.
### Does this PR introduce _any_ user-facing change?
Yes, in unreleased code only: fixes inconsistent Oracle timestamp
conversion and aggregate results after table resolution.
### How was this patch tested?
- New builder regression failed before the fix and passes afterward. It
varies the two options independently on the catalog and query, covering
single-key inputs, both flag-flip directions, mixed-case keys, and both
builders.
- Regression tests verify same-case replacement and preservation of
distinct case-sensitive driver property keys in both builders.
- Full `JDBCTableCatalogSuite`: 28 tests passed.
- Existing `JDBCSuite` tests matching `SPARK-19318`: two tests passed.
- `JDBCSuite` tests matching `SPARK-58876`: six tests passed.
- Docker integration-test sources compiled, including Oracle MIN/MAX and
GROUP BY comparisons with pushdown on/off, opposite query/catalog mapping
values, and different JVM/session time zones. Live Oracle tests were not run
because no local Oracle Docker image is available.
- SQL production/test and Docker integration-test Scala style checks
passed; `git diff --check` passed.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: OpenAI Codex (GPT-6)
Closes #59990 from urosstan-db/remove-oracle-ntz-legacy-conf.
Authored-by: Uros Stankovic <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
---
.../spark/sql/jdbc/OracleIntegrationSuite.scala | 20 ++++++
.../spark/sql/jdbc/v2/OracleIntegrationSuite.scala | 50 ++++++++++++++
.../execution/datasources/v2/jdbc/JDBCTable.scala | 18 +++--
.../v2/jdbc/JDBCTableCatalogSuite.scala | 78 +++++++++++++++++++++-
.../org/apache/spark/sql/jdbc/JDBCSuite.scala | 1 +
5 files changed, 162 insertions(+), 5 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 428331183b88..aab920a54bd9 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
@@ -628,12 +628,32 @@ class OracleIntegrationSuite extends
SharedJDBCIntegrationSuite
assert(optOutDf.schema("D").dataType === TimestampType)
assert(optOutDf.schema("T").dataType === TimestampType)
+ withTable("datetime_ltz") {
+ sql(s"CREATE TABLE datetime_ltz USING jdbc OPTIONS (url '$jdbcUrl', " +
+ "dbtable 'datetime', zonelessTimestampAsNTZ 'false',
timestampNTZAsWallClock 'false')")
+ val tableDf = sql("SELECT D, T FROM datetime_ltz")
+ assert(tableDf.schema("D").dataType === TimestampType)
+ assert(tableDf.schema("T").dataType === TimestampType)
+ checkAnswer(tableDf, Row(
+ Timestamp.valueOf("1991-11-09 00:00:00"),
Timestamp.valueOf("1996-01-01 01:23:45")))
+ }
+
// 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)))
+
+ withTable("datetime_no_wall_clock") {
+ sql(s"CREATE TABLE datetime_no_wall_clock USING jdbc OPTIONS (url
'$jdbcUrl', " +
+ "dbtable 'datetime', zonelessTimestampAsNTZ 'true',
timestampNTZAsWallClock 'false')")
+ val tableDf = sql("SELECT D, T FROM datetime_no_wall_clock")
+ assert(tableDf.schema("D").dataType === TimestampNTZType)
+ assert(tableDf.schema("T").dataType === TimestampNTZType)
+ checkAnswer(tableDf, Row(
+ LocalDateTime.of(1991, 11, 9, 8, 0, 0), LocalDateTime.of(1996, 1, 1,
9, 23, 45)))
+ }
}
// Legacy flag on: both fall back to TimestampType.
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 6e88d9e44616..5601f39ffdba 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
@@ -30,6 +30,7 @@ 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,6 +188,21 @@ class OracleIntegrationSuite extends
DockerJDBCIntegrationV2Suite with V2JDBCTes
override def caseConvert(tableName: String): String =
tableName.toUpperCase(Locale.ROOT)
+ test("SPARK-60420: V2 NTZ reads retain wall-clock conversion after a legacy
flag flip") {
+ val table = s"$catalogName.${namespaceOpt.get}.${caseConvert("datetime")}"
+ val legacyKey = SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key
+ withDefaultTimeZone(LA) {
+ val df = withSQLConf(legacyKey -> "false") {
+ val planned = sql(s"SELECT time1 FROM $table WHERE name = 'amy'")
+ assert(planned.schema.head.dataType === TimestampNTZType)
+ planned
+ }
+ withSQLConf(legacyKey -> "true") {
+ checkAnswer(df, Row(LocalDateTime.of(2022, 5, 19, 0, 0, 0)))
+ }
+ }
+ }
+
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)
@@ -228,6 +244,40 @@ class OracleIntegrationSuite extends
DockerJDBCIntegrationV2Suite with V2JDBCTes
}
}
+ gridTest("SPARK-60420: V2 aggregates retain catalog timestamp mapping")(
+ Seq(false, true)) { zoneless =>
+ val catalog = s"oracle_aggregate_$zoneless"
+ withDefaultTimeZone(LA) {
+ withSQLConf(
+ SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key -> "false",
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> "UTC",
+ SQLConf.TIMESTAMP_TYPE.key -> "TIMESTAMP_LTZ",
+ s"spark.sql.catalog.$catalog" -> classOf[JDBCTableCatalog].getName,
+ s"spark.sql.catalog.$catalog.url" -> db.getJdbcUrl(dockerIp,
externalPort),
+ s"spark.sql.catalog.$catalog.zonelessTimestampAsNTZ" ->
zoneless.toString) {
+ val table = s"$catalog.${namespaceOpt.get}.${caseConvert("datetime")}"
+ val expectedType = if (zoneless) TimestampNTZType else TimestampType
+ val schema = spark.table(table).schema
+ Seq("date1", "time1").foreach { column =>
+ assert(schema(caseConvert(column)).dataType === expectedType)
+ Seq(
+ (s"MIN($column), MAX($column)", ""),
+ (s"$column, COUNT(*)", s" GROUP BY $column")
+ ).foreach { case (projection, groupBy) =>
+ val options = s"'zonelessTimestampAsNTZ' = '${!zoneless}'"
+ val pushed = sql(s"SELECT $projection FROM $table WITH (" +
+ s"$options, 'pushDownAggregate' = 'true')$groupBy")
+ val unpushed = sql(s"SELECT $projection FROM $table WITH (" +
+ s"$options, 'pushDownAggregate' = 'false')$groupBy")
+ checkAggregateRemoved(pushed)
+ checkAggregateRemoved(unpushed, pushed = false)
+ checkAnswer(pushed, unpushed)
+ }
+ }
+ }
+ }
+ }
+
test("SPARK-46478: Revert SPARK-43049 to use varchar(255) for string") {
val tableName = catalogName + ".t1"
withTable(tableName) {
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTable.scala
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTable.scala
index 02197ba3d9df..e7bd9c11f71d 100644
---
a/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTable.scala
+++
b/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTable.scala
@@ -21,6 +21,7 @@ import java.util
import scala.jdk.CollectionConverters._
import org.apache.spark.sql.SparkSession
+import org.apache.spark.sql.catalyst.util.CaseInsensitiveMap
import org.apache.spark.sql.connector.catalog._
import org.apache.spark.sql.connector.catalog.TableCapability._
import org.apache.spark.sql.connector.catalog.index.{SupportsIndex, TableIndex}
@@ -59,15 +60,24 @@ case class JDBCTable(
util.EnumSet.of(BATCH_READ, V1_BATCH_WRITE, TRUNCATE)
}
- override def newScanBuilder(options: CaseInsensitiveStringMap):
JDBCScanBuilder = {
- val mergedOptions = new JDBCOptions(
+ private def mergeOptions(options: CaseInsensitiveStringMap):
CaseInsensitiveMap[String] = {
+ // Preserve case-sensitive driver properties when merging query options.
+ val merged = CaseInsensitiveMap(
jdbcOptions.parameters.originalMap ++
options.asCaseSensitiveMap().asScala)
+ // Keep the table's type mapping; query options can override its
conversion default.
+ merged +
+ (JDBCOptions.JDBC_TIMESTAMP_NTZ_WALL_CLOCK -> options.getOrDefault(
+ JDBCOptions.JDBC_TIMESTAMP_NTZ_WALL_CLOCK,
jdbcOptions.timestampNTZAsWallClock.toString)) +
+ (JDBCOptions.JDBC_ZONELESS_TIMESTAMP_AS_NTZ ->
jdbcOptions.zonelessTimestampAsNTZ.toString)
+ }
+
+ override def newScanBuilder(options: CaseInsensitiveStringMap):
JDBCScanBuilder = {
+ val mergedOptions = new JDBCOptions(mergeOptions(options))
JDBCScanBuilder(SparkSession.active, schema, mergedOptions,
additionalMetrics, databaseMetadata)
}
override def newWriteBuilder(info: LogicalWriteInfo): WriteBuilder = {
- val mergedOptions = new JdbcOptionsInWrite(
- jdbcOptions.parameters.originalMap ++
info.options.asCaseSensitiveMap().asScala)
+ val mergedOptions = new JdbcOptionsInWrite(mergeOptions(info.options))
JDBCWriteBuilder(schema, mergedOptions)
}
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala
index 7ba627ebde5a..a87192ba08f2 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/jdbc/JDBCTableCatalogSuite.scala
@@ -17,7 +17,7 @@
package org.apache.spark.sql.execution.datasources.v2.jdbc
import java.sql.{Connection, DriverManager}
-import java.util.Properties
+import java.util.{Locale, Properties}
import scala.jdk.CollectionConverters._
@@ -29,8 +29,10 @@ import
org.apache.spark.sql.catalyst.analysis.{NoSuchNamespaceException, NoSuchT
import org.apache.spark.sql.catalyst.parser.ParseException
import org.apache.spark.sql.catalyst.util.CharVarcharUtils
import org.apache.spark.sql.connector.catalog.{Identifier, TableChange,
TableSummary}
+import org.apache.spark.sql.connector.write.LogicalWriteInfoImpl
import org.apache.spark.sql.errors.DataTypeErrors.{toSQLConf, toSQLStmt}
import org.apache.spark.sql.execution.columnar.InMemoryTableScanExec
+import org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.test.SharedSparkSession
import org.apache.spark.sql.types._
@@ -106,6 +108,80 @@ class JDBCTableCatalogSuite extends SharedSparkSession {
checkAnswer(sql("SHOW TABLES IN h2.bad_test"), Seq())
}
+ test("SPARK-60420: JDBC builders preserve timestamp defaults and honor
conversion overrides") {
+ val wallClockKey = JDBCOptions.JDBC_TIMESTAMP_NTZ_WALL_CLOCK
+ val zonelessKey = JDBCOptions.JDBC_ZONELESS_TIMESTAMP_AS_NTZ
+ val legacyKey = SQLConf.LEGACY_ORACLE_TIMESTAMP_NTZ_MAPPING_ENABLED.key
+ val schema = new StructType().add("t", TimestampNTZType)
+
+ def timestampOptions(
+ wallClock: Option[Boolean],
+ zoneless: Option[Boolean],
+ upperCase: Boolean): Map[String, String] = {
+ (wallClock.toSeq.map(wallClockKey -> _.toString) ++
+ zoneless.toSeq.map(zonelessKey -> _.toString)).map { case (key, value)
=>
+ (if (upperCase) key.toUpperCase(Locale.ROOT) else key) -> value
+ }.toMap
+ }
+
+ for {
+ legacy <- Seq(false, true)
+ catalogWallClock <- Seq(None, Some(false), Some(true))
+ catalogZoneless <- Seq(None, Some(false), Some(true))
+ catalogUpperCase <- Seq(false, true)
+ } {
+ val table = withSQLConf(legacyKey -> legacy.toString) {
+ val parameters = Map(
+ "url" -> "jdbc:oracle:thin:@//host:1521/db",
+ "dbtable" -> "t",
+ "driver" -> "org.h2.Driver") ++
+ timestampOptions(catalogWallClock, catalogZoneless, catalogUpperCase)
+ JDBCTable(Identifier.of(Array.empty, "t"), schema, new
JDBCOptions(parameters))
+ }
+ withSQLConf(legacyKey -> (!legacy).toString) {
+ for {
+ queryWallClock <- Seq(None, Some(false), Some(true))
+ queryZoneless <- Seq(None, Some(false), Some(true))
+ queryUpperCase <- Seq(false, true)
+ } {
+ val parameters = timestampOptions(queryWallClock, queryZoneless,
queryUpperCase)
+ val queryOptions = new CaseInsensitiveStringMap(parameters.asJava)
+ val readOptions = table.newScanBuilder(queryOptions).jdbcOptions
+ val writeInfo = LogicalWriteInfoImpl("query-id", schema,
queryOptions)
+ val writeOptions =
table.newWriteBuilder(writeInfo).asInstanceOf[JDBCWriteBuilder].options
+ val clue = s"legacy=$legacy catalogWallClock=$catalogWallClock " +
+ s"catalogZoneless=$catalogZoneless
catalogUpperCase=$catalogUpperCase " +
+ s"queryWallClock=$queryWallClock queryZoneless=$queryZoneless " +
+ s"queryUpperCase=$queryUpperCase"
+ withClue(clue) {
+ Seq(readOptions, writeOptions).foreach { options =>
+ assert(options.timestampNTZAsWallClock ===
+ queryWallClock.orElse(catalogWallClock).getOrElse(!legacy))
+ assert(options.zonelessTimestampAsNTZ ===
catalogZoneless.getOrElse(!legacy))
+ }
+ }
+ }
+ }
+ }
+ }
+
+ test("SPARK-60420: JDBC builders preserve case-sensitive driver properties")
{
+ val schema = new StructType().add("id", IntegerType)
+ val properties = Map("customKey" -> "catalog-value", "untouchedKey" ->
"keep-value")
+ val parameters = Map("url" -> url, "dbtable" -> "t") ++ properties
+ val table = JDBCTable(Identifier.of(Array.empty, "t"), schema, new
JDBCOptions(parameters))
+ Seq("customKey", "CUSTOMKEY").foreach { key =>
+ val overrides = Map(key -> "query-value")
+ val queryOptions = new CaseInsensitiveStringMap(overrides.asJava)
+ val readOptions = table.newScanBuilder(queryOptions).jdbcOptions
+ val writeInfo = LogicalWriteInfoImpl("query-id", schema, queryOptions)
+ val writeOptions =
table.newWriteBuilder(writeInfo).asInstanceOf[JDBCWriteBuilder].options
+ Seq(readOptions, writeOptions).foreach { options =>
+ assert(options.asConnectionProperties.asScala.toMap === properties ++
overrides)
+ }
+ }
+ }
+
test("drop a table and test whether the table exists") {
withConnection { conn =>
conn.prepareStatement("""CREATE TABLE "test"."to_drop" (id
INTEGER)""").executeUpdate()
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 c06d9586e7ce..3b1487fcb793 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
@@ -1790,6 +1790,7 @@ class JDBCSuite extends SharedSparkSession {
}
// Dialects that don't opt in keep the java.sql.Timestamp conversion.
assert(!NoopDialect.timestampNTZAsWallClock)
+ assert(!new AggregatedDialect(List(custom,
NoopDialect)).timestampNTZAsWallClock)
}
test("SPARK-58876: Oracle NTZ mapping, preferTimestampNTZ and
zonelessTimestampAsNTZ") {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]