You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ma...@apache.org on 2026/06/08 05:21:32 UTC

(spark) branch master updated: [SPARK-57293][SQL] Cast between nanosecond-precision and microsecond-precision timestamp types

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

MaxGekk 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 2660f4d13a78 [SPARK-57293][SQL] Cast between nanosecond-precision and microsecond-precision timestamp types
2660f4d13a78 is described below

commit 2660f4d13a787404bd728fbf47a597df205d0743
Author: Maxim Gekk <ma...@gmail.com>
AuthorDate: Mon Jun 8 07:21:19 2026 +0200

    [SPARK-57293][SQL] Cast between nanosecond-precision and microsecond-precision timestamp types
    
    ### What changes were proposed in this pull request?
    
    This PR adds explicit casts between nanosecond-precision timestamp types and their microsecond-precision counterparts, in both the interpreted and codegen paths:
    
    - `TIMESTAMP_NTZ` <-> `TIMESTAMP_NTZ(p)`
    - `TIMESTAMP_LTZ` <-> `TIMESTAMP_LTZ(p)` (`p` in `[7, 9]`)
    
    Both directions stay within a single zone family, so they are pure representation conversions with no timezone involvement:
    
    - **Widening** (micros -> nanos): `TimestampNanosVal.fromParts(micros, 0)`. Lossless and independent of the target precision `p` (the sub-microsecond part is always 0).
    - **Narrowing** (nanos -> micros): takes `epochMicros`, dropping the sub-microsecond digits. Truncation toward the past (floor), consistent with how microsecond timestamps are already produced. Silent in both ANSI and non-ANSI modes, matching Spark's existing silent fractional-second truncation for timestamp casts.
    
    Implementation:
    - Registered the four pairs in `Cast.canCast` / `Cast.canAnsiCast`.
    - Added interpreted cases in `castToTimestamp` / `castToTimestampNTZ` (narrowing) and `castToTimestampLTZNanos` / `castToTimestampNTZNanos` (widening), and mirrored them in the corresponding codegen helpers.
    - No new `Cast.needsTimeZone` entries are required. The preview flag `spark.sql.timestampNanosTypes.enabled` continues to gate the nanosecond-typed side.
    
    Out of scope: precision-to-precision casts within the nanosecond family (`TIMESTAMP_NTZ(p1)` -> `TIMESTAMP_NTZ(p2)`), cross-family casts (`TIMESTAMP_LTZ(p)` <-> `TIMESTAMP_NTZ(p)`), and implicit/up-cast/store-assignment coercion. These casts remain explicit-only, consistent with the existing string<->nanos casts.
    
    This is a subtask of [SPARK-56822](https://issues.apache.org/jira/browse/SPARK-56822) (SPIP: Timestamps with nanosecond precision).
    
    ### Why are the changes needed?
    
    Nanosecond-precision timestamp types currently support parsing from strings (SPARK-57211) and rendering to strings (SPARK-57256), but there is no cast between a nanosecond-precision type and its microsecond-precision counterpart. As a result, values cannot move between `TIMESTAMP_NTZ(p)` and `TIMESTAMP_NTZ`, or between `TIMESTAMP_LTZ(p)` and `TIMESTAMP_LTZ`. This PR fills that gap.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes, but only behind the preview flag `spark.sql.timestampNanosTypes.enabled`. When enabled, users can now explicitly cast between the four new pairs, e.g.:
    
    ```sql
    SELECT cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ntz(9)) as timestamp_ntz);
    -- 2020-01-01 00:00:00.123456
    ```
    
    The nanosecond-precision types are an unreleased, preview feature, so this is not a change compared to any released Spark version.
    
    ### How was this patch tested?
    
    - Added unit tests in `CastSuiteBase` covering widening, narrowing/truncation (with a non-zero sub-microsecond part to prove flooring), round-trip, and null inputs for both NTZ and LTZ families. These run under `CastWithAnsiOnSuite` / `CastWithAnsiOffSuite` (ANSI on/off) and exercise both the interpreted and codegen paths.
    - Extended the existing `null cast` test to cover the four new pairs.
    - Added end-to-end golden coverage in `cast.sql` and regenerated the four `cast.sql.out` golden files.
    - Verified style with scalastyle.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Cursor 2.0 (Claude Opus 4.8)
    
    Closes #56354 from MaxGekk/nanos-cast-micros.
    
    Authored-by: Maxim Gekk <ma...@gmail.com>
    Signed-off-by: Max Gekk <ma...@gmail.com>
---
 .../spark/sql/catalyst/expressions/Cast.scala      | 33 ++++++++
 .../sql/catalyst/expressions/CastSuiteBase.scala   | 96 +++++++++++++++++++++-
 .../sql-tests/analyzer-results/cast.sql.out        | 42 ++++++++++
 .../analyzer-results/nonansi/cast.sql.out          | 42 ++++++++++
 .../src/test/resources/sql-tests/inputs/cast.sql   | 15 ++++
 .../test/resources/sql-tests/results/cast.sql.out  | 48 +++++++++++
 .../sql-tests/results/nonansi/cast.sql.out         | 48 +++++++++++
 7 files changed, 323 insertions(+), 1 deletion(-)

diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
index 4a57ac4eeeed..29f0f5181a18 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
@@ -116,6 +116,11 @@ object Cast extends QueryErrorsBase {
     case (_: StringType, _: TimestampNTZNanosType) => true
     case (_: StringType, _: TimestampLTZNanosType) => true
 
+    case (TimestampNTZType, _: TimestampNTZNanosType) => true
+    case (_: TimestampNTZNanosType, TimestampNTZType) => true
+    case (TimestampType, _: TimestampLTZNanosType) => true
+    case (_: TimestampLTZNanosType, TimestampType) => true
+
     case (_: StringType, _: CalendarIntervalType) => true
     case (_: StringType, _: AnsiIntervalType) => true
 
@@ -254,6 +259,11 @@ object Cast extends QueryErrorsBase {
     case (_: StringType, _: TimestampNTZNanosType) => true
     case (_: StringType, _: TimestampLTZNanosType) => true
 
+    case (TimestampNTZType, _: TimestampNTZNanosType) => true
+    case (_: TimestampNTZNanosType, TimestampNTZType) => true
+    case (TimestampType, _: TimestampLTZNanosType) => true
+    case (_: TimestampLTZNanosType, TimestampType) => true
+
     case (_: StringType, DateType) => true
     case (_: StringType, _: TimeType) => true
     case (TimestampType, DateType) => true
@@ -400,6 +410,11 @@ object Cast extends QueryErrorsBase {
     case (_: NumericType, _: NumericType) => true
     case (_: AtomicType, _: StringType) => true
     case (_: CalendarIntervalType, _: StringType) => true
+    // SPARK-57293: narrowing a nanosecond-precision timestamp to its microsecond counterpart
+    // drops the sub-microsecond digits, so it is not allowed as a (silent) store assignment.
+    // This conversion stays explicit-only.
+    case (_: TimestampNTZNanosType, TimestampNTZType) => false
+    case (_: TimestampLTZNanosType, TimestampType) => false
     case (_: DatetimeType, _: DatetimeType) => true
 
     case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
@@ -762,6 +777,8 @@ case class Cast(
       buildCast[Int](_, d => daysToMicros(d, zoneId))
     case TimestampNTZType =>
       buildCast[Long](_, ts => convertTz(ts, zoneId, ZoneOffset.UTC))
+    case _: TimestampLTZNanosType =>
+      buildCast[TimestampNanosVal](_, v => v.epochMicros)
     // TimestampWritable.decimalToTimestamp
     case DecimalType() =>
       buildCast[Decimal](_, d => decimalToTimestamp(d))
@@ -794,6 +811,8 @@ case class Cast(
       buildCast[Int](_, d => daysToMicros(d, ZoneOffset.UTC))
     case TimestampType =>
       buildCast[Long](_, ts => convertTz(ts, ZoneOffset.UTC, zoneId))
+    case _: TimestampNTZNanosType =>
+      buildCast[TimestampNanosVal](_, v => v.epochMicros)
   }
 
   private[this] def castToTimestampLTZNanos(
@@ -806,6 +825,8 @@ case class Cast(
         } else {
           DateTimeUtils.stringToTimestampLTZNanos(utfs, precision, zoneId).orNull
         })
+    case TimestampType =>
+      buildCast[Long](_, m => TimestampNanosVal.fromParts(m, 0.toShort))
   }
 
   private[this] def castToTimestampNTZNanos(
@@ -818,6 +839,8 @@ case class Cast(
         } else {
           DateTimeUtils.stringToTimestampNTZNanos(utfs, precision, allowTimeZone = true).orNull
         })
+    case TimestampNTZType =>
+      buildCast[Long](_, m => TimestampNanosVal.fromParts(m, 0.toShort))
   }
 
   private[this] def decimalToTimestamp(d: Decimal): Long = {
@@ -1745,6 +1768,8 @@ case class Cast(
         zoneIdClass)
       (c, evPrim, evNull) =>
         code"$evPrim = $dateTimeUtilsCls.convertTz($c, $zid, java.time.ZoneOffset.UTC);"
+    case _: TimestampLTZNanosType =>
+      (c, evPrim, evNull) => code"$evPrim = $c.epochMicros;"
     case DecimalType() =>
       (c, evPrim, evNull) => code"$evPrim = ${decimalToTimestampCode(c)};"
     case DoubleType =>
@@ -1808,6 +1833,8 @@ case class Cast(
         zoneIdClass)
       (c, evPrim, evNull) =>
         code"$evPrim = $dateTimeUtilsCls.convertTz($c, java.time.ZoneOffset.UTC, $zid);"
+    case _: TimestampNTZNanosType =>
+      (c, evPrim, evNull) => code"$evPrim = $c.epochMicros;"
   }
 
   private[this] def castToTimestampLTZNanosCode(
@@ -1838,6 +1865,9 @@ case class Cast(
             }
            """
         }
+    case TimestampType =>
+      (c, evPrim, evNull) =>
+        code"$evPrim = TimestampNanosVal.fromParts($c, (short) 0);"
   }
 
   private[this] def castToTimestampNTZNanosCode(
@@ -1864,6 +1894,9 @@ case class Cast(
             }
            """
         }
+    case TimestampNTZType =>
+      (c, evPrim, evNull) =>
+        code"$evPrim = TimestampNanosVal.fromParts($c, (short) 0);"
   }
 
   private[this] def castToIntervalCode(from: DataType): CastFunction = from match {
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
index 5c346a980d58..de8862f5bc6c 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
@@ -40,7 +40,7 @@ import org.apache.spark.sql.types.DataTypeTestUtils.{dayTimeIntervalTypes, yearM
 import org.apache.spark.sql.types.DayTimeIntervalType.{DAY, HOUR, MINUTE, SECOND}
 import org.apache.spark.sql.types.UpCastRule.numericPrecedence
 import org.apache.spark.sql.types.YearMonthIntervalType.{MONTH, YEAR}
-import org.apache.spark.unsafe.types.UTF8String
+import org.apache.spark.unsafe.types.{TimestampNanosVal, UTF8String}
 import org.apache.spark.util.ThreadUtils
 
 /**
@@ -89,6 +89,14 @@ abstract class CastSuiteBase extends SparkFunSuite with ExpressionEvalHelper {
       TimestampNTZNanosType(TimestampNTZNanosType.MIN_PRECISION),
       TimestampNTZNanosType(TimestampNTZNanosType.MAX_PRECISION))
     (atomicTypes ++ timeTypes ++ timestampNanosTypes).foreach(dt => checkNullCast(dt, StringType))
+    Seq(TimestampNTZNanosType.MIN_PRECISION, TimestampNTZNanosType.MAX_PRECISION).foreach { p =>
+      checkNullCast(TimestampNTZType, TimestampNTZNanosType(p))
+      checkNullCast(TimestampNTZNanosType(p), TimestampNTZType)
+    }
+    Seq(TimestampLTZNanosType.MIN_PRECISION, TimestampLTZNanosType.MAX_PRECISION).foreach { p =>
+      checkNullCast(TimestampType, TimestampLTZNanosType(p))
+      checkNullCast(TimestampLTZNanosType(p), TimestampType)
+    }
     checkNullCast(StringType, BinaryType)
     checkNullCast(StringType, BooleanType)
     numericTypes.foreach(dt => checkNullCast(dt, BooleanType))
@@ -723,6 +731,23 @@ abstract class CastSuiteBase extends SparkFunSuite with ExpressionEvalHelper {
     }
   }
 
+  test("SPARK-57293: nanos<->micros store-assignment and up-cast contract") {
+    foreachNanosPrecision { p =>
+      // Explicit-only: neither direction is an up-cast, so STRICT store assignment rejects both.
+      assert(!Cast.canUpCast(TimestampNTZType, TimestampNTZNanosType(p)))
+      assert(!Cast.canUpCast(TimestampNTZNanosType(p), TimestampNTZType))
+      assert(!Cast.canUpCast(TimestampType, TimestampLTZNanosType(p)))
+      assert(!Cast.canUpCast(TimestampLTZNanosType(p), TimestampType))
+
+      // ANSI store assignment allows the lossless widening micros -> nanos(p) ...
+      assert(Cast.canANSIStoreAssign(TimestampNTZType, TimestampNTZNanosType(p)))
+      assert(Cast.canANSIStoreAssign(TimestampType, TimestampLTZNanosType(p)))
+      // ... but blocks the lossy narrowing nanos(p) -> micros to avoid silent truncation.
+      assert(!Cast.canANSIStoreAssign(TimestampNTZNanosType(p), TimestampNTZType))
+      assert(!Cast.canANSIStoreAssign(TimestampLTZNanosType(p), TimestampType))
+    }
+  }
+
   test("SPARK-40389: canUpCast: return false if casting decimal to integral types can cause" +
     " overflow") {
     Seq(ByteType, ShortType, IntegerType, LongType).foreach { integralType =>
@@ -1195,6 +1220,75 @@ abstract class CastSuiteBase extends SparkFunSuite with ExpressionEvalHelper {
       "{2020-01-01 00:00:00.123456789, 2020-01-01 00:00:00.123456789}")
   }
 
+  test("SPARK-57293: cast between timestamp_ntz and timestamp_ntz with nanosecond precision") {
+    // Sub-microsecond part is 0 and independent of the target precision p.
+    val micros = localDateTimeToNanosVal(LocalDateTime.of(2020, 1, 1, 12, 30, 15, 123456000))
+      .epochMicros
+    foreachNanosPrecision { precision =>
+      // Widening: micros -> nanos(p) sets nanosWithinMicro to 0 (lossless).
+      checkEvaluation(
+        cast(Literal.create(micros, TimestampNTZType), TimestampNTZNanosType(precision)),
+        TimestampNanosVal.fromParts(micros, 0.toShort))
+      // Narrowing: nanos(p) -> micros drops the sub-microsecond digits (floor toward the past).
+      // A non-zero nanosWithinMicro proves the truncation.
+      checkEvaluation(
+        cast(Literal.create(nanosVal(micros, 789), TimestampNTZNanosType(precision)),
+          TimestampNTZType),
+        micros)
+      // Narrowing also floors toward the past for negative epochMicros (pre-1970): the stored
+      // epochMicros is already the floor, and the sub-microsecond part is dropped, not rounded.
+      val ntzPreEpoch =
+        localDateTimeToNanosVal(LocalDateTime.of(1969, 12, 31, 23, 59, 59, 999999789))
+      checkEvaluation(
+        cast(Literal.create(ntzPreEpoch, TimestampNTZNanosType(precision)), TimestampNTZType),
+        ntzPreEpoch.epochMicros)
+      // Round-trip micros -> nanos(p) -> micros is the identity.
+      checkEvaluation(
+        cast(cast(Literal.create(micros, TimestampNTZType), TimestampNTZNanosType(precision)),
+          TimestampNTZType),
+        micros)
+      // Null input in both directions.
+      checkEvaluation(
+        cast(Literal.create(null, TimestampNTZType), TimestampNTZNanosType(precision)), null)
+      checkEvaluation(
+        cast(Literal.create(null, TimestampNTZNanosType(precision)), TimestampNTZType), null)
+    }
+  }
+
+  test("SPARK-57293: cast between timestamp_ltz and timestamp_ltz with nanosecond precision") {
+    // LTZ(p) and LTZ share the same epoch-micros instant; no timezone conversion is involved.
+    val micros = instantToMicros(timestampLTZ(2020, 1, 1, 12, 30, 15, 123456000))
+    foreachNanosPrecision { precision =>
+      // Widening: micros -> nanos(p) sets nanosWithinMicro to 0 (lossless).
+      checkEvaluation(
+        cast(Literal.create(micros, TimestampType), TimestampLTZNanosType(precision), UTC_OPT),
+        TimestampNanosVal.fromParts(micros, 0.toShort))
+      // Narrowing: nanos(p) -> micros drops the sub-microsecond digits (floor toward the past).
+      checkEvaluation(
+        cast(Literal.create(nanosVal(micros, 789), TimestampLTZNanosType(precision)),
+          TimestampType, UTC_OPT),
+        micros)
+      // Narrowing also floors toward the past for negative epochMicros (pre-1970): the stored
+      // epochMicros is already the floor, and the sub-microsecond part is dropped, not rounded.
+      val ltzPreEpoch = instantToNanosVal(timestampLTZ(1969, 12, 31, 23, 59, 59, 999999789))
+      checkEvaluation(
+        cast(Literal.create(ltzPreEpoch, TimestampLTZNanosType(precision)), TimestampType,
+          UTC_OPT),
+        ltzPreEpoch.epochMicros)
+      // Round-trip micros -> nanos(p) -> micros is the identity.
+      checkEvaluation(
+        cast(
+          cast(Literal.create(micros, TimestampType), TimestampLTZNanosType(precision), UTC_OPT),
+          TimestampType, UTC_OPT),
+        micros)
+      // Null input in both directions.
+      checkEvaluation(
+        cast(Literal.create(null, TimestampType), TimestampLTZNanosType(precision), UTC_OPT), null)
+      checkEvaluation(
+        cast(Literal.create(null, TimestampLTZNanosType(precision)), TimestampType, UTC_OPT), null)
+    }
+  }
+
   test("SPARK-35112: Cast string to day-time interval") {
     checkEvaluation(cast(Literal.create("0 0:0:0"), DayTimeIntervalType()), 0L)
     checkEvaluation(cast(Literal.create(" interval '0 0:0:0' Day TO second   "),
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/cast.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/cast.sql.out
index cc296a57abc5..ea9c634bbd89 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/cast.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/cast.sql.out
@@ -774,6 +774,48 @@ Project [concat(ts=, cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9)
 +- OneRowRelation
 
 
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as string)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp_ntz) as timestamp_ntz(9)) as string) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS STRING)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ntz(9)) as timestamp_ntz)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9)) as timestamp_ntz) AS CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as timestamp_ntz)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp_ntz) as timestamp_ntz(9)) as timestamp_ntz) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as string)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp) as timestamp_ltz(9)) as string) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS STRING)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ltz(9)) as timestamp)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(9)) as timestamp) AS CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_LTZ(9)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as timestamp)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp) as timestamp_ltz(9)) as timestamp) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
 -- !query
 select cast(cast('inf' as double) as timestamp)
 -- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/cast.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/cast.sql.out
index bcc410b322ae..a04d020e13cc 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/cast.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/cast.sql.out
@@ -638,6 +638,48 @@ Project [concat(ts=, cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9)
 +- OneRowRelation
 
 
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as string)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp_ntz) as timestamp_ntz(9)) as string) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS STRING)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ntz(9)) as timestamp_ntz)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9)) as timestamp_ntz) AS CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as timestamp_ntz)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp_ntz) as timestamp_ntz(9)) as timestamp_ntz) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as string)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp) as timestamp_ltz(9)) as string) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS STRING)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ltz(9)) as timestamp)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(9)) as timestamp) AS CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_LTZ(9)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as timestamp)
+-- !query analysis
+Project [cast(cast(cast(2020-01-01 00:00:00.123456 as timestamp) as timestamp_ltz(9)) as timestamp) AS CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
 -- !query
 select cast(cast('inf' as double) as timestamp)
 -- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/cast.sql b/sql/core/src/test/resources/sql-tests/inputs/cast.sql
index 5aead54b2722..78373951c565 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/cast.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/cast.sql
@@ -136,6 +136,21 @@ select cast(named_struct('f', cast('2020-01-01 00:00:00.123456789' as timestamp_
 select cast(cast(null as timestamp_ntz(9)) as string);
 select concat('ts=', cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ntz(9)) as string));
 
+-- SPARK-57293: cast between nanosecond-precision timestamps and their microsecond counterparts.
+-- Both directions stay within one zone family (pure representation conversions, no timezone
+-- involvement). Widening (micros -> nanos(p)) is wrapped in cast(... as string) because a bare
+-- nanos result column is not yet serializable by JDBC/thrift; narrowing/round-trip yield micros.
+-- TIMESTAMP_NTZ <-> TIMESTAMP_NTZ(p): widening sets the sub-microsecond part to 0.
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as string);
+-- Narrowing TIMESTAMP_NTZ(p) -> TIMESTAMP_NTZ drops the sub-microsecond digits (floor).
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ntz(9)) as timestamp_ntz);
+-- Round-trip micros -> nanos(p) -> micros returns the original microseconds.
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as timestamp_ntz);
+-- TIMESTAMP_LTZ <-> TIMESTAMP_LTZ(p): same conversions on the epoch-micros instant.
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as string);
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ltz(9)) as timestamp);
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as timestamp);
+
 select cast(cast('inf' as double) as timestamp);
 select cast(cast('inf' as float) as timestamp);
 
diff --git a/sql/core/src/test/resources/sql-tests/results/cast.sql.out b/sql/core/src/test/resources/sql-tests/results/cast.sql.out
index 2cff542eb98d..ba18d1e10168 100644
--- a/sql/core/src/test/resources/sql-tests/results/cast.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/cast.sql.out
@@ -1474,6 +1474,54 @@ struct<concat(ts=, CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_NTZ(9))
 ts=2020-01-01 00:00:00.123456789
 
 
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as string)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS STRING):string>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ntz(9)) as timestamp_ntz)
+-- !query schema
+struct<CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as timestamp_ntz)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as string)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS STRING):string>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ltz(9)) as timestamp)
+-- !query schema
+struct<CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_LTZ(9)) AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as timestamp)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
 -- !query
 select cast(cast('inf' as double) as timestamp)
 -- !query schema
diff --git a/sql/core/src/test/resources/sql-tests/results/nonansi/cast.sql.out b/sql/core/src/test/resources/sql-tests/results/nonansi/cast.sql.out
index d215cf7cee3c..442daf553aee 100644
--- a/sql/core/src/test/resources/sql-tests/results/nonansi/cast.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/nonansi/cast.sql.out
@@ -736,6 +736,54 @@ struct<concat(ts=, CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_NTZ(9))
 ts=2020-01-01 00:00:00.123456789
 
 
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as string)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS STRING):string>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ntz(9)) as timestamp_ntz)
+-- !query schema
+struct<CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp_ntz) as timestamp_ntz(9)) as timestamp_ntz)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP_NTZ) AS TIMESTAMP_NTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as string)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS STRING):string>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast('2020-01-01 00:00:00.123456789' as timestamp_ltz(9)) as timestamp)
+-- !query schema
+struct<CAST(CAST(2020-01-01 00:00:00.123456789 AS TIMESTAMP_LTZ(9)) AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(cast(cast('2020-01-01 00:00:00.123456' as timestamp) as timestamp_ltz(9)) as timestamp)
+-- !query schema
+struct<CAST(CAST(CAST(2020-01-01 00:00:00.123456 AS TIMESTAMP) AS TIMESTAMP_LTZ(9)) AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
 -- !query
 select cast(cast('inf' as double) as timestamp)
 -- !query schema


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