You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by do...@apache.org on 2024/03/21 14:48:54 UTC

(spark) branch master updated: [SPARK-47501][SQL] Add convertDateToDate like the existing convertTimestampToTimestamp for JdbcDialect

This is an automated email from the ASF dual-hosted git repository.

dongjoon 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 32f3d4dc0389 [SPARK-47501][SQL] Add convertDateToDate like the existing convertTimestampToTimestamp for JdbcDialect
32f3d4dc0389 is described below

commit 32f3d4dc03892f23bc073205335ad0d4174c213a
Author: Kent Yao <ya...@apache.org>
AuthorDate: Thu Mar 21 07:48:41 2024 -0700

    [SPARK-47501][SQL] Add convertDateToDate like the existing convertTimestampToTimestamp for JdbcDialect
    
    ### What changes were proposed in this pull request?
    
    Add convertDateToDate like the existing convertTimestampToTimestamp for JdbcDialect
    
    ### Why are the changes needed?
    
    The date '±infinity' values cause overflows like timestamp '±infinity' in #41843
    
    ### Does this PR introduce _any_ user-facing change?
    
    fix expected overflow for dates to align with the timestamps of PostgreSQL
    
    ### How was this patch tested?
    new tests
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    no
    
    Closes #45638 from yaooqinn/SPARK-47501.
    
    Authored-by: Kent Yao <ya...@apache.org>
    Signed-off-by: Dongjoon Hyun <dh...@apple.com>
---
 .../spark/sql/jdbc/PostgresIntegrationSuite.scala  | 24 ++++++++++++++
 .../sql/execution/datasources/jdbc/JdbcUtils.scala |  6 ++--
 .../org/apache/spark/sql/jdbc/JdbcDialects.scala   |  8 +++++
 .../apache/spark/sql/jdbc/PostgresDialect.scala    | 37 +++++++++++-----------
 4 files changed, 54 insertions(+), 21 deletions(-)

diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala
index 8d137ba88cb1..a47e834a4b3c 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala
@@ -155,6 +155,14 @@ class PostgresIntegrationSuite extends DockerJDBCIntegrationSuite {
         "('-infinity', ARRAY[TIMESTAMP '-infinity'])")
       .executeUpdate()
 
+    conn.prepareStatement("CREATE TABLE infinity_dates" +
+        "(id SERIAL PRIMARY KEY, date_column DATE, date_array DATE[])")
+      .executeUpdate()
+    conn.prepareStatement("INSERT INTO infinity_dates (date_column, date_array)" +
+        " VALUES ('infinity', ARRAY[DATE 'infinity']), " +
+        "('-infinity', ARRAY[DATE '-infinity'])")
+      .executeUpdate()
+
     conn.prepareStatement("CREATE DOMAIN not_null_text AS TEXT DEFAULT ''").executeUpdate()
     conn.prepareStatement("create table custom_type(type_array not_null_text[]," +
       "type not_null_text)").executeUpdate()
@@ -462,6 +470,22 @@ class PostgresIntegrationSuite extends DockerJDBCIntegrationSuite {
     assert(negativeInfinitySeq.head.getTime == minTimeStamp)
   }
 
+  test("SPARK-47501: infinity date test") {
+    val df = sqlContext.read.jdbc(jdbcUrl, "infinity_dates", new Properties)
+    val row = df.collect()
+
+    assert(row.length == 2)
+    val infinity = row(0).getDate(1)
+    val negativeInfinity = row(1).getDate(1)
+    val infinitySeq = row(0).getAs[scala.collection.Seq[Date]]("date_array")
+    val negativeInfinitySeq = row(1).getAs[scala.collection.Seq[Date]]("date_array")
+    val minDate = -62135654400000L
+    val maxDate = 253402156800000L
+    assert(infinity.getTime == maxDate)
+    assert(negativeInfinity.getTime == minDate)
+    assert(infinitySeq.head.getTime == maxDate)
+    assert(negativeInfinitySeq.head.getTime == minDate)
+  }
 
   test("SPARK-47407: Support java.sql.Types.NULL for NullType") {
     val df = spark.read.format("jdbc")
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 84d87f008217..70fd9bd071e9 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
@@ -404,7 +404,7 @@ object JdbcUtils extends Logging with SQLConfHelper {
         // DateTimeUtils.fromJavaDate does not handle null value, so we need to check it.
         val dateVal = rs.getDate(pos + 1)
         if (dateVal != null) {
-          row.setInt(pos, fromJavaDate(dateVal))
+          row.setInt(pos, fromJavaDate(dialect.convertDateToDate(dateVal)))
         } else {
           row.update(pos, null)
         }
@@ -526,7 +526,9 @@ object JdbcUtils extends Logging with SQLConfHelper {
         case StringType =>
           arrayConverter[Object]((obj: Object) => UTF8String.fromString(obj.toString))
 
-        case DateType => arrayConverter[Date](fromJavaDate)
+        case DateType => arrayConverter[Date] {
+          (d: Date) => fromJavaDate(dialect.convertDateToDate(d))
+        }
 
         case dt: DecimalType =>
             arrayConverter[java.math.BigDecimal](d => Decimal(d, dt.precision, dt.scale))
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 845161c81ea5..ff23afbc3125 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
@@ -156,6 +156,14 @@ abstract class JdbcDialect extends Serializable with Logging {
   @Since("3.5.0")
   def convertJavaTimestampToTimestamp(t: Timestamp): Timestamp = t
 
+  /**
+   * Converts an instance of `java.sql.Date` to a custom `java.sql.Date` value.
+   * @param d the date value returned from JDBC ResultSet getDate method.
+   * @return the date value after conversion
+   */
+  @Since("4.0.0")
+  def convertDateToDate(d: Date): Date = d
+
   /**
    * Convert java.sql.Timestamp to a LocalDateTime representing the same wall-clock time as the
    * value stored in a remote database.
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala
index 5c949b28ba7c..d8692a055ffe 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala
@@ -17,7 +17,7 @@
 
 package org.apache.spark.sql.jdbc
 
-import java.sql.{Connection, SQLException, Timestamp, Types}
+import java.sql.{Connection, Date, SQLException, Timestamp, Types}
 import java.time.{LocalDateTime, ZoneOffset}
 import java.util
 import java.util.Locale
@@ -307,24 +307,23 @@ private case class PostgresDialect() extends JdbcDialect with SQLConfHelper {
   override def convertJavaTimestampToTimestamp(t: Timestamp): Timestamp = {
     // Variable names come from PostgreSQL "constant field docs":
     // https://jdbc.postgresql.org/documentation/publicapi/index.html?constant-values.html
-    val POSTGRESQL_DATE_NEGATIVE_INFINITY = -9223372036832400000L
-    val POSTGRESQL_DATE_NEGATIVE_SMALLER_INFINITY = -185543533774800000L
-    val POSTGRESQL_DATE_POSITIVE_INFINITY = 9223372036825200000L
-    val POSTGRESQL_DATE_DATE_POSITIVE_SMALLER_INFINITY = 185543533774800000L
-
-    val minTimeStamp = LocalDateTime.of(1, 1, 1, 0, 0, 0).toInstant(ZoneOffset.UTC).toEpochMilli
-    val maxTimestamp =
-      LocalDateTime.of(9999, 12, 31, 23, 59, 59, 999999999).toInstant(ZoneOffset.UTC).toEpochMilli
-
-    val time = t.getTime
-    if (time == POSTGRESQL_DATE_POSITIVE_INFINITY ||
-      time == POSTGRESQL_DATE_DATE_POSITIVE_SMALLER_INFINITY) {
-      new Timestamp(maxTimestamp)
-    } else if (time == POSTGRESQL_DATE_NEGATIVE_INFINITY ||
-      time == POSTGRESQL_DATE_NEGATIVE_SMALLER_INFINITY) {
-      new Timestamp(minTimeStamp)
-    } else {
-      t
+   t.getTime match {
+     case 9223372036825200000L =>
+       new Timestamp(LocalDateTime.of(9999, 12, 31, 23, 59, 59, 999999999)
+         .toInstant(ZoneOffset.UTC).toEpochMilli)
+     case -9223372036832400000L =>
+       new Timestamp(LocalDateTime.of(1, 1, 1, 0, 0, 0).toInstant(ZoneOffset.UTC).toEpochMilli)
+     case _ => t
+   }
+  }
+
+  override def convertDateToDate(d: Date): Date = {
+    d.getTime match {
+      case 9223372036825200000L =>
+        new Date(LocalDateTime.of(9999, 12, 31, 0, 0, 0).toInstant(ZoneOffset.UTC).toEpochMilli)
+      case -9223372036832400000L =>
+        new Date(LocalDateTime.of(1, 1, 1, 0, 0, 0).toInstant(ZoneOffset.UTC).toEpochMilli)
+      case _ => d
     }
   }
 }


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org