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 860195ac2120 [SPARK-57511][SQL] Support explicit CAST between
TIMESTAMP_LTZ(p) and TIMESTAMP_NTZ(q) for p, q in [6, 9]
860195ac2120 is described below
commit 860195ac2120b5e8c5a79d126be5e5a1a41b2674
Author: Maxim Gekk <[email protected]>
AuthorDate: Fri Jun 19 07:48:13 2026 +0200
[SPARK-57511][SQL] Support explicit CAST between TIMESTAMP_LTZ(p) and
TIMESTAMP_NTZ(q) for p, q in [6, 9]
### What changes were proposed in this pull request?
This PR adds explicit `CAST` support between the nanosecond-capable
`TIMESTAMP_LTZ(p)` and `TIMESTAMP_NTZ(q)` types for `p, q in [6, 9]`, i.e.:
- `CAST(<timestamp_ltz(p)> AS TIMESTAMP_NTZ(q))`
- `CAST(<timestamp_ntz(p)> AS TIMESTAMP_LTZ(q))`
Recall that the parser maps precision `6` to the microsecond family members
(`TIMESTAMP_LTZ(6)` = `TIMESTAMP`, `TIMESTAMP_NTZ(6)` = `TIMESTAMP_NTZ`), so
the full matrix is covered by two groups:
- nanos <-> nanos: `TimestampLTZNanosType(p) <-> TimestampNTZNanosType(q)`
for `p, q in [7, 9]`;
- the precision-6 boundary, which mixes a micro family member with the
other family's nanos member: `TIMESTAMP <-> TimestampNTZNanosType(q)` and
`TIMESTAMP_NTZ <-> TimestampLTZNanosType(p)`.
Concretely, in `Cast.scala`:
- `canCast` / `canAnsiCast`: allow the cross-family directions above.
- `needsTimeZone`: the conversion reinterprets an absolute instant (LTZ) as
a wall-clock local date-time (NTZ) and vice versa, so it is session-time-zone
dependent (mirroring micro `TIMESTAMP <-> TIMESTAMP_NTZ`).
- `canANSIStoreAssign`: the cross-family casts stay explicit-only (not
silent store assignments) while the nanosecond types are unreleased; the
all-micro `TIMESTAMP <-> TIMESTAMP_NTZ` pair remains store-assignable.
- Interpreted and codegen conversion paths for the new source/target
combinations, reusing the existing `convertTz` micro semantics plus precision
flooring for the sub-microsecond part.
`SparkDateTimeUtils.scala` gains two small helpers,
`timestampLTZNanosToNTZNanos` and `timestampNTZNanosToLTZNanos`, that compose
the existing public conversion utilities (`timestampNanosToInstant`,
`localDateTimeToTimestampNanos`, etc.).
Out of scope (unchanged): implicit type coercion / common-type resolution
(e.g. `CASE`, `UNION` wider-type inference).
### Why are the changes needed?
Spark already supports same-family cross-precision nanos casts
(`TIMESTAMP_NTZ(p) -> TIMESTAMP_NTZ(q)`, `TIMESTAMP_LTZ(p) ->
TIMESTAMP_LTZ(q)`) and the microsecond `TIMESTAMP <-> TIMESTAMP_NTZ` casts, but
direct cross-family casts between `TIMESTAMP_LTZ(p)` and `TIMESTAMP_NTZ(q)`
were not supported for `p, q in [6, 9]`. This closes that gap so explicit
`CAST(...)` has consistent timestamp parity. This is a sub-task of SPARK-56822
(SPIP: Timestamps with nanosecond precision).
### Does this PR introduce _any_ user-facing change?
Yes, within the unreleased nanosecond-timestamp feature (gated by
`spark.sql.timestampNanosTypes.enabled`). Explicit casts that previously failed
type checking, e.g.:
```sql
SELECT CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7));
SELECT CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9));
```
now succeed, reinterpreting the value against the session time zone and
flooring the fractional second to the target precision. There is no change to
already-released types.
### How was this patch tested?
- Added catalyst unit tests in `CastSuiteBase` (run under both ANSI
on/off): admissibility / store-assignment / up-cast / `needsTimeZone` /
null-safety (`!forceNullable`) contracts (nanos<->nanos and the micro
boundary), value tests across `p, q in [7, 9]` in UTC and `America/Los_Angeles`
(widening keeps a zero sub-microsecond part, narrowing floors, pre-epoch and
null cases), a round-trip test, and the micro family member (precision 6)
to/from nanos test.
- Added a DST resolution test exercising the `America/Los_Angeles`
spring-forward gap (`2020-03-08 02:30`, non-existent local time -> shifted
forward) and fall-back overlap (`2020-11-01 01:30`, ambiguous -> earlier
offset), asserting the nanos `NTZ <-> LTZ` cast matches the production micro
`TIMESTAMP_NTZ <-> TIMESTAMP` cast on the epoch-micros part (expected micro
values obtained via `evaluateWithoutCodegen`).
- Extended `cast.sql` golden coverage (type resolution, lossless same-zone
round-trips, narrowing truncation, null propagation, and the precision-6
boundary) and regenerated the result/analyzer goldens, including the non-ANSI
variants.
- `./dev/scalastyle` passes.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Cursor
Closes #56577 from MaxGekk/ltz-ntz-nanos-cast.
Authored-by: Maxim Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
---
.../sql/catalyst/util/SparkDateTimeUtils.scala | 31 +++
.../spark/sql/catalyst/expressions/Cast.scala | 159 ++++++++-----
.../sql/catalyst/expressions/CastSuiteBase.scala | 247 ++++++++++++++++++++-
.../sql-tests/analyzer-results/cast.sql.out | 152 +++++++++++++
.../analyzer-results/nonansi/cast.sql.out | 152 +++++++++++++
.../src/test/resources/sql-tests/inputs/cast.sql | 35 +++
.../test/resources/sql-tests/results/cast.sql.out | 176 +++++++++++++++
.../sql-tests/results/nonansi/cast.sql.out | 176 +++++++++++++++
8 files changed, 1069 insertions(+), 59 deletions(-)
diff --git
a/sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/SparkDateTimeUtils.scala
b/sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/SparkDateTimeUtils.scala
index ea20057278ce..2b992ba53fe8 100644
---
a/sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/SparkDateTimeUtils.scala
+++
b/sql/api/src/main/scala/org/apache/spark/sql/catalyst/util/SparkDateTimeUtils.scala
@@ -310,6 +310,37 @@ trait SparkDateTimeUtils {
microsToInstant(v.epochMicros).plusNanos(v.nanosWithinMicro.toLong)
}
+ /**
+ * Converts a `TIMESTAMP_LTZ(p)` nanosecond value into the
`TIMESTAMP_NTZ(precision)` wall-clock
+ * value observed in the time zone `zoneId`. The LTZ value denotes an
absolute instant;
+ * rendering it as a local date-time at `zoneId` yields the NTZ
representation. Time-zone
+ * offsets shift only whole seconds, so the sub-microsecond
`nanosWithinMicro` component is
+ * preserved before being floored to the target `precision` (same flooring
as same-family
+ * narrowing casts).
+ */
+ def timestampLTZNanosToNTZNanos(
+ v: TimestampNanosVal,
+ zoneId: ZoneId,
+ precision: Int): TimestampNanosVal = {
+ val localDateTime =
timestampNanosToInstant(v).atZone(zoneId).toLocalDateTime
+ localDateTimeToTimestampNanos(localDateTime, precision)
+ }
+
+ /**
+ * Converts a `TIMESTAMP_NTZ(q)` nanosecond value into the
`TIMESTAMP_LTZ(precision)` instant
+ * obtained by interpreting its wall-clock local date-time in the time zone
`zoneId`. This is
+ * the reverse of [[timestampLTZNanosToNTZNanos]]; the sub-microsecond
`nanosWithinMicro`
+ * component is preserved across the (whole-second) offset shift before
being floored to the
+ * target `precision`.
+ */
+ def timestampNTZNanosToLTZNanos(
+ v: TimestampNanosVal,
+ zoneId: ZoneId,
+ precision: Int): TimestampNanosVal = {
+ val instant = timestampNanosToLocalDateTime(v).atZone(zoneId).toInstant
+ instantToTimestampNanos(instant, precision)
+ }
+
/**
* Converts the local date to the number of days since 1970-01-01.
*/
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 b29e6f5d953b..506babb08f34 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
@@ -115,13 +115,13 @@ object Cast extends QueryErrorsBase {
case (_: StringType, _: AnyTimestampNanoType) => true
- case (TimestampNTZType, _: TimestampNTZNanosType) => true
- case (_: TimestampNTZNanosType, TimestampNTZType) => true
- case (TimestampType, _: TimestampLTZNanosType) => true
- case (_: TimestampLTZNanosType, TimestampType) => true
-
- case (_: TimestampNTZNanosType, _: TimestampNTZNanosType) => true
- case (_: TimestampLTZNanosType, _: TimestampLTZNanosType) => true
+ // Casts between timestamp types where at least one side is a nanosecond
type are allowed for
+ // every micro/nanos and nanos/nanos combination, across both time-zone
families. Precision 6 is
+ // the micro family member (TIMESTAMP_LTZ(6) = TIMESTAMP, TIMESTAMP_NTZ(6)
= TIMESTAMP_NTZ),
+ // matched here via AnyTimestampType; the all-micro TIMESTAMP <->
TIMESTAMP_NTZ pair is above.
+ case (_: AnyTimestampNanoType, _: AnyTimestampNanoType) => true
+ case (t, _: AnyTimestampNanoType) if AnyTimestampType.acceptsType(t) =>
true
+ case (_: AnyTimestampNanoType, t) if AnyTimestampType.acceptsType(t) =>
true
case (DateType, _: TimestampLTZNanosType) => true
case (_: TimestampLTZNanosType, DateType) => true
@@ -265,13 +265,13 @@ object Cast extends QueryErrorsBase {
case (_: StringType, _: AnyTimestampNanoType) => true
- case (TimestampNTZType, _: TimestampNTZNanosType) => true
- case (_: TimestampNTZNanosType, TimestampNTZType) => true
- case (TimestampType, _: TimestampLTZNanosType) => true
- case (_: TimestampLTZNanosType, TimestampType) => true
-
- case (_: TimestampNTZNanosType, _: TimestampNTZNanosType) => true
- case (_: TimestampLTZNanosType, _: TimestampLTZNanosType) => true
+ // Casts between timestamp types where at least one side is a nanosecond
type are allowed for
+ // every micro/nanos and nanos/nanos combination, across both time-zone
families. Precision 6 is
+ // the micro family member (TIMESTAMP_LTZ(6) = TIMESTAMP, TIMESTAMP_NTZ(6)
= TIMESTAMP_NTZ),
+ // matched here via AnyTimestampType; the all-micro TIMESTAMP <->
TIMESTAMP_NTZ pair is above.
+ case (_: AnyTimestampNanoType, _: AnyTimestampNanoType) => true
+ case (t, _: AnyTimestampNanoType) if AnyTimestampType.acceptsType(t) =>
true
+ case (_: AnyTimestampNanoType, t) if AnyTimestampType.acceptsType(t) =>
true
case (DateType, _: TimestampLTZNanosType) => true
case (_: TimestampLTZNanosType, DateType) => true
@@ -365,6 +365,16 @@ object Cast extends QueryErrorsBase {
case (TimestampType, DateType) => true
case (TimestampType, TimestampNTZType) => true
case (TimestampNTZType, TimestampType) => true
+ // Cross-family nanosecond casts convert between an absolute instant (LTZ)
and a wall-clock
+ // local date-time (NTZ), so they depend on the session time zone,
mirroring the micro
+ // TIMESTAMP <-> TIMESTAMP_NTZ pair above. This includes the mixed
micro/nanos cases where one
+ // side is the precision-6 micro family member (TIMESTAMP / TIMESTAMP_NTZ).
+ case (_: TimestampLTZNanosType, _: TimestampNTZNanosType) => true
+ case (_: TimestampNTZNanosType, _: TimestampLTZNanosType) => true
+ case (TimestampType, _: TimestampNTZNanosType) => true
+ case (_: TimestampNTZNanosType, TimestampType) => true
+ case (TimestampNTZType, _: TimestampLTZNanosType) => true
+ case (_: TimestampLTZNanosType, TimestampNTZType) => true
// NTZ string is zone-independent (mirroring micro TIMESTAMP_NTZ, which is
not listed); only
// the LTZ string parse/render depends on the session time zone.
case (_: StringType, _: TimestampLTZNanosType) => true
@@ -428,27 +438,30 @@ 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
- // SPARK-57323: DATE <-> nanosecond-precision timestamp requires an
explicit CAST in both
- // directions. nanos -> DATE silently drops time-of-day and
sub-microsecond digits (the same
- // rule as the narrowing above). DATE -> nanos is lossless (midnight, zero
sub-micro part),
- // but conversions involving the nanos types stay explicit-only while the
types are
- // unreleased: allowing the store assignment later is a compatible change,
revoking it is not.
- // Note this is stricter than micro DATE <-> TIMESTAMP[_NTZ], which the
catch-all below allows.
- case (DateType, _: TimestampLTZNanosType) => false
- case (DateType, _: TimestampNTZNanosType) => false
- case (_: TimestampLTZNanosType, DateType) => false
- case (_: TimestampNTZNanosType, DateType) => false
// SPARK-57490: same-family cross-precision nanosecond casts: widening
(e.g. TIMESTAMP_NTZ(7) ->
// TIMESTAMP_NTZ(9)) is lossless and allowed as a silent store assignment,
while narrowing
// (e.g. (9) -> (7)) drops sub-microsecond digits and stays explicit-only.
Equal precision is
- // already handled by the `from == to` short-circuit above.
+ // handled by the `from == to` short-circuit above; micros -> nanos
widening (e.g. TIMESTAMP_NTZ
+ // -> TIMESTAMP_NTZ(9)) is lossless and falls to the catch-all below.
case (f: TimestampNTZNanosType, t: TimestampNTZNanosType) => f.precision
<= t.precision
case (f: TimestampLTZNanosType, t: TimestampLTZNanosType) => f.precision
<= t.precision
+ // SPARK-57323: DATE <-> nanosecond-precision timestamp requires an
explicit CAST in both
+ // directions (nanos -> DATE drops fields; DATE -> nanos is lossless but
kept explicit-only
+ // while the nanos types are unreleased). Stricter than micro DATE <->
TIMESTAMP[_NTZ], which
+ // the catch-all below allows.
+ case (DateType, _: AnyTimestampNanoType) => false
+ case (_: AnyTimestampNanoType, DateType) => false
+ // SPARK-57293/57511: narrowing any nanosecond timestamp to a microsecond
timestamp drops the
+ // sub-microsecond digits, and cross-family casts additionally reinterpret
the value against the
+ // session time zone; both stay explicit-only rather than silent store
assignments while the
+ // nanos types are unreleased. This covers same-family narrowing (nanos ->
micro), cross-family
+ // nanos <-> nanos, and the mixed micro/nanos pairs at the precision-6
boundary; everything
+ // matched here is explicit-only. The all-micro TIMESTAMP <->
TIMESTAMP_NTZ pair and micros ->
+ // nanos same-family widening stay store-assignable via the catch-all
below.
+ case (_: AnyTimestampNanoType, t) if AnyTimestampType.acceptsType(t) =>
false
+ case (TimestampType, _: TimestampNTZNanosType) => false
+ case (TimestampNTZType, _: TimestampLTZNanosType) => false
+ case (_: AnyTimestampNanoType, _: AnyTimestampNanoType) => false
case (_: DatetimeType, _: DatetimeType) => true
case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
@@ -817,6 +830,8 @@ case class Cast(
buildCast[Long](_, ts => convertTz(ts, zoneId, ZoneOffset.UTC))
case _: TimestampLTZNanosType =>
buildCast[TimestampNanosVal](_, v => v.epochMicros)
+ case _: TimestampNTZNanosType =>
+ buildCast[TimestampNanosVal](_, v => convertTz(v.epochMicros, zoneId,
ZoneOffset.UTC))
// TimestampWritable.decimalToTimestamp
case DecimalType() =>
buildCast[Decimal](_, d => decimalToTimestamp(d))
@@ -851,6 +866,8 @@ case class Cast(
buildCast[Long](_, ts => convertTz(ts, ZoneOffset.UTC, zoneId))
case _: TimestampNTZNanosType =>
buildCast[TimestampNanosVal](_, v => v.epochMicros)
+ case _: TimestampLTZNanosType =>
+ buildCast[TimestampNanosVal](_, v => convertTz(v.epochMicros,
ZoneOffset.UTC, zoneId))
}
private[this] def castToTimestampLTZNanos(
@@ -865,9 +882,15 @@ case class Cast(
})
case TimestampType =>
buildCast[Long](_, m => TimestampNanosVal.fromParts(m, 0.toShort))
+ case TimestampNTZType =>
+ buildCast[Long](_, m =>
+ TimestampNanosVal.fromParts(convertTz(m, zoneId, ZoneOffset.UTC),
0.toShort))
case _: TimestampLTZNanosType =>
buildCast[TimestampNanosVal](_, v =>
DateTimeUtils.truncateTimestampNanosToPrecision(v, precision))
+ case _: TimestampNTZNanosType =>
+ buildCast[TimestampNanosVal](_, v =>
+ DateTimeUtils.timestampNTZNanosToLTZNanos(v, zoneId, precision))
case DateType =>
buildCast[Int](_, d => TimestampNanosVal.fromParts(daysToMicros(d,
zoneId), 0.toShort))
}
@@ -884,9 +907,15 @@ case class Cast(
})
case TimestampNTZType =>
buildCast[Long](_, m => TimestampNanosVal.fromParts(m, 0.toShort))
+ case TimestampType =>
+ buildCast[Long](_, m =>
+ TimestampNanosVal.fromParts(convertTz(m, ZoneOffset.UTC, zoneId),
0.toShort))
case _: TimestampNTZNanosType =>
buildCast[TimestampNanosVal](_, v =>
DateTimeUtils.truncateTimestampNanosToPrecision(v, precision))
+ case _: TimestampLTZNanosType =>
+ buildCast[TimestampNanosVal](_, v =>
+ DateTimeUtils.timestampLTZNanosToNTZNanos(v, zoneId, precision))
case DateType =>
buildCast[Int](_, d =>
TimestampNanosVal.fromParts(daysToMicros(d, ZoneOffset.UTC),
0.toShort))
@@ -1580,6 +1609,14 @@ case class Cast(
code"$evPrim =
${NumberConverter.getClass.getName.stripSuffix("$")}.toBinary($c);"
}
+ // Registers the session `zoneId` as a codegen reference object and returns
a handle to it.
+ // Kept as a method (rather than a pre-`match` val) so the reference is only
added for the arms
+ // that actually need the session time zone, not for zone-independent casts.
+ private[this] def zoneIdValue(ctx: CodegenContext): GlobalValue = {
+ val zoneIdClass = classOf[ZoneId]
+ JavaCode.global(ctx.addReferenceObj("zoneId", zoneId,
zoneIdClass.getName), zoneIdClass)
+ }
+
private[this] def castToDateCode(
from: DataType,
ctx: CodegenContext): CastFunction = {
@@ -1604,16 +1641,14 @@ case class Cast(
}
case TimestampType =>
- val zidClass = classOf[ZoneId]
- val zid = JavaCode.global(ctx.addReferenceObj("zoneId", zoneId,
zidClass.getName), zidClass)
+ val zid = zoneIdValue(ctx)
(c, evPrim, evNull) =>
code"""$evPrim = $dateTimeUtilsCls.microsToDays($c, $zid);"""
case TimestampNTZType =>
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.microsToDays($c,
java.time.ZoneOffset.UTC);"
case _: TimestampLTZNanosType =>
- val zidClass = classOf[ZoneId]
- val zid = JavaCode.global(ctx.addReferenceObj("zoneId", zoneId,
zidClass.getName), zidClass)
+ val zid = zoneIdValue(ctx)
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.microsToDays($c.epochMicros, $zid);"
case _: TimestampNTZNanosType =>
@@ -1790,10 +1825,7 @@ case class Cast(
from: DataType,
ctx: CodegenContext): CastFunction = from match {
case _: StringType =>
- val zoneIdClass = classOf[ZoneId]
- val zid = JavaCode.global(
- ctx.addReferenceObj("zoneId", zoneId, zoneIdClass.getName),
- zoneIdClass)
+ val zid = zoneIdValue(ctx)
val longOpt = ctx.freshVariable("longOpt", classOf[Option[Long]])
(c, evPrim, evNull) =>
if (ansiEnabled) {
@@ -1816,21 +1848,19 @@ case class Cast(
case _: IntegralType =>
(c, evPrim, evNull) => code"$evPrim = ${longToTimeStampCode(c)};"
case DateType =>
- val zoneIdClass = classOf[ZoneId]
- val zid = JavaCode.global(
- ctx.addReferenceObj("zoneId", zoneId, zoneIdClass.getName),
- zoneIdClass)
+ val zid = zoneIdValue(ctx)
(c, evPrim, evNull) =>
code"""$evPrim = $dateTimeUtilsCls.daysToMicros($c, $zid);"""
case TimestampNTZType =>
- val zoneIdClass = classOf[ZoneId]
- val zid = JavaCode.global(
- ctx.addReferenceObj("zoneId", zoneId, zoneIdClass.getName),
- zoneIdClass)
+ val zid = zoneIdValue(ctx)
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.convertTz($c, $zid,
java.time.ZoneOffset.UTC);"
case _: TimestampLTZNanosType =>
(c, evPrim, evNull) => code"$evPrim = $c.epochMicros;"
+ case _: TimestampNTZNanosType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.convertTz($c.epochMicros, $zid,
java.time.ZoneOffset.UTC);"
case DecimalType() =>
(c, evPrim, evNull) => code"$evPrim = ${decimalToTimestampCode(c)};"
case DoubleType =>
@@ -1888,14 +1918,15 @@ case class Cast(
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.daysToMicros($c,
java.time.ZoneOffset.UTC);"
case TimestampType =>
- val zoneIdClass = classOf[ZoneId]
- val zid = JavaCode.global(
- ctx.addReferenceObj("zoneId", zoneId, zoneIdClass.getName),
- zoneIdClass)
+ val zid = zoneIdValue(ctx)
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.convertTz($c,
java.time.ZoneOffset.UTC, $zid);"
case _: TimestampNTZNanosType =>
(c, evPrim, evNull) => code"$evPrim = $c.epochMicros;"
+ case _: TimestampLTZNanosType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.convertTz($c.epochMicros,
java.time.ZoneOffset.UTC, $zid);"
}
private[this] def castToTimestampLTZNanosCode(
@@ -1903,10 +1934,7 @@ case class Cast(
precision: Int,
ctx: CodegenContext): CastFunction = from match {
case _: StringType =>
- val zoneIdClass = classOf[ZoneId]
- val zid = JavaCode.global(
- ctx.addReferenceObj("zoneId", zoneId, zoneIdClass.getName),
- zoneIdClass)
+ val zid = zoneIdValue(ctx)
val tsOpt = ctx.freshVariable("tsOpt",
classOf[Option[TimestampNanosVal]])
(c, evPrim, evNull) =>
if (ansiEnabled) {
@@ -1929,14 +1957,20 @@ case class Cast(
case TimestampType =>
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts($c, (short) 0);"
+ case TimestampNTZType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = TimestampNanosVal.fromParts(" +
+ code"$dateTimeUtilsCls.convertTz($c, $zid,
java.time.ZoneOffset.UTC), (short) 0);"
case _: TimestampLTZNanosType =>
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.truncateTimestampNanosToPrecision($c,
$precision);"
+ case _: TimestampNTZNanosType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.timestampNTZNanosToLTZNanos($c, $zid,
$precision);"
case DateType =>
- val zoneIdClass = classOf[ZoneId]
- val zid = JavaCode.global(
- ctx.addReferenceObj("zoneId", zoneId, zoneIdClass.getName),
- zoneIdClass)
+ val zid = zoneIdValue(ctx)
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts(" +
code"$dateTimeUtilsCls.daysToMicros($c, $zid), (short) 0);"
@@ -1969,9 +2003,18 @@ case class Cast(
case TimestampNTZType =>
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts($c, (short) 0);"
+ case TimestampType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = TimestampNanosVal.fromParts(" +
+ code"$dateTimeUtilsCls.convertTz($c, java.time.ZoneOffset.UTC,
$zid), (short) 0);"
case _: TimestampNTZNanosType =>
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.truncateTimestampNanosToPrecision($c,
$precision);"
+ case _: TimestampLTZNanosType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.timestampLTZNanosToNTZNanos($c, $zid,
$precision);"
case DateType =>
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts(" +
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 896b2eddf3f6..980c68f65f20 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
@@ -101,13 +101,23 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
checkNullCast(DateType, TimestampLTZNanosType(p))
checkNullCast(TimestampLTZNanosType(p), DateType)
}
- // Same-family cross-precision nanos casts.
+ // Same-family and cross-family cross-precision nanos casts.
for {
p1 <- TimestampNTZNanosType.MIN_PRECISION to
TimestampNTZNanosType.MAX_PRECISION
p2 <- TimestampNTZNanosType.MIN_PRECISION to
TimestampNTZNanosType.MAX_PRECISION
} {
checkNullCast(TimestampNTZNanosType(p1), TimestampNTZNanosType(p2))
checkNullCast(TimestampLTZNanosType(p1), TimestampLTZNanosType(p2))
+ checkNullCast(TimestampLTZNanosType(p1), TimestampNTZNanosType(p2))
+ checkNullCast(TimestampNTZNanosType(p1), TimestampLTZNanosType(p2))
+ }
+ // Cross-family casts that involve the precision-6 micro family member
(TIMESTAMP_LTZ(6) =
+ // TIMESTAMP and TIMESTAMP_NTZ(6) = TIMESTAMP_NTZ) and the other family's
nanosecond member.
+ foreachNanosPrecision { p =>
+ checkNullCast(TimestampType, TimestampNTZNanosType(p))
+ checkNullCast(TimestampNTZNanosType(p), TimestampType)
+ checkNullCast(TimestampNTZType, TimestampLTZNanosType(p))
+ checkNullCast(TimestampLTZNanosType(p), TimestampNTZType)
}
checkNullCast(StringType, BinaryType)
checkNullCast(StringType, BooleanType)
@@ -791,6 +801,61 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("cross-family nanos cast: admissibility, store-assignment and up-cast
contract") {
+ for {
+ p <- TimestampLTZNanosType.MIN_PRECISION to
TimestampLTZNanosType.MAX_PRECISION
+ q <- TimestampNTZNanosType.MIN_PRECISION to
TimestampNTZNanosType.MAX_PRECISION
+ } {
+ val ltz = TimestampLTZNanosType(p)
+ val ntz = TimestampNTZNanosType(q)
+ // Explicit casts are allowed in both directions, both in ANSI and
non-ANSI modes.
+ assert(Cast.canCast(ltz, ntz))
+ assert(Cast.canCast(ntz, ltz))
+ assert(Cast.canAnsiCast(ltz, ntz))
+ assert(Cast.canAnsiCast(ntz, ltz))
+ // The cross-family reinterpretation against the session zone is never a
safe up-cast.
+ assert(!Cast.canUpCast(ltz, ntz))
+ assert(!Cast.canUpCast(ntz, ltz))
+ // They stay explicit-only: never silent store assignments (mirroring
the other nanos casts).
+ assert(!Cast.canANSIStoreAssign(ltz, ntz))
+ assert(!Cast.canANSIStoreAssign(ntz, ltz))
+ // The conversion depends on the session time zone in both directions.
+ assert(Cast.needsTimeZone(ltz, ntz))
+ assert(Cast.needsTimeZone(ntz, ltz))
+ // Reinterpreting a non-null instant/local date-time never yields null,
so the cast is
+ // null-safe in both directions (mirroring the micro TIMESTAMP <->
TIMESTAMP_NTZ pair).
+ assert(!Cast.forceNullable(ltz, ntz))
+ assert(!Cast.forceNullable(ntz, ltz))
+ }
+ }
+
+ test("cross-family nanos cast: micro boundary (precision 6) admissibility
and store contract") {
+ // TIMESTAMP_LTZ(6) = TIMESTAMP and TIMESTAMP_NTZ(6) = TIMESTAMP_NTZ, so
the precision-6
+ // cross-family casts are the mixed micro/nanos pairs covered here.
+ foreachNanosPrecision { p =>
+ val pairs = Seq(
+ (TimestampType: DataType, TimestampNTZNanosType(p): DataType), //
LTZ(6) -> NTZ(p)
+ (TimestampNTZNanosType(p): DataType, TimestampType: DataType), //
NTZ(p) -> LTZ(6)
+ (TimestampNTZType: DataType, TimestampLTZNanosType(p): DataType),//
NTZ(6) -> LTZ(p)
+ (TimestampLTZNanosType(p): DataType, TimestampNTZType: DataType))//
LTZ(p) -> NTZ(6)
+ pairs.foreach { case (from, to) =>
+ // Explicit casts are allowed (ANSI and non-ANSI), but are never safe
up-casts and never
+ // silent store assignments, and they depend on the session time zone.
+ assert(Cast.canCast(from, to))
+ assert(Cast.canAnsiCast(from, to))
+ assert(!Cast.canUpCast(from, to))
+ assert(!Cast.canANSIStoreAssign(from, to))
+ assert(Cast.needsTimeZone(from, to))
+ // Null-safe like the micro TIMESTAMP <-> TIMESTAMP_NTZ pair.
+ assert(!Cast.forceNullable(from, to))
+ }
+ }
+ // Sanity: the all-micro TIMESTAMP <-> TIMESTAMP_NTZ pair (precision 6 <->
6) stays a silent
+ // store assignment, unlike the mixed micro/nanos pairs above.
+ assert(Cast.canANSIStoreAssign(TimestampType, TimestampNTZType))
+ assert(Cast.canANSIStoreAssign(TimestampNTZType, TimestampType))
+ }
+
test("SPARK-40389: canUpCast: return false if casting decimal to integral
types can cause" +
" overflow") {
Seq(ByteType, ShortType, IntegerType, LongType).foreach { integralType =>
@@ -1398,6 +1463,186 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("cross-family nanos cast: timestamp_ltz to timestamp_ntz") {
+ // LTZ(p) denotes an absolute instant; LTZ(p) -> NTZ(q) renders it as the
wall-clock local
+ // date-time observed in the session zone (mirroring the micro TIMESTAMP
-> TIMESTAMP_NTZ
+ // conversion on the epoch-micros part) and re-floors the sub-microsecond
digits to q.
+ Seq(UTC, LA).foreach { zone =>
+ val zid = Option(zone.getId)
+ val instants = Seq(
+ timestampLTZ(2020, 1, 1, 12, 30, 15, 123456789),
+ // Pre-epoch: epochMicros is negative, the sub-microsecond part stays
in [0, 999].
+ timestampLTZ(1969, 12, 31, 23, 59, 59, 999999789))
+ instants.foreach { instant =>
+ val srcMicros = instantToNanosVal(instant).epochMicros
+ val ntzMicros = convertTz(srcMicros, UTC, zone)
+ for {
+ p <- TimestampLTZNanosType.MIN_PRECISION to
TimestampLTZNanosType.MAX_PRECISION
+ q <- TimestampNTZNanosType.MIN_PRECISION to
TimestampNTZNanosType.MAX_PRECISION
+ } {
+ // The source already floors to p; the cross-family cast then floors
to q, i.e. to
+ // min(p, q). Only the wall-clock epoch-micros part shifts with the
zone.
+ val srcNanos = floorNanosToPrecision(789, p)
+ val expectedNanos = floorNanosToPrecision(srcNanos, q)
+ checkEvaluation(
+ cast(Literal.create(nanosVal(srcMicros, srcNanos),
TimestampLTZNanosType(p)),
+ TimestampNTZNanosType(q), zid),
+ nanosVal(ntzMicros, expectedNanos))
+ checkEvaluation(
+ cast(Literal.create(null, TimestampLTZNanosType(p)),
TimestampNTZNanosType(q), zid),
+ null)
+ }
+ }
+ // Zone sensitivity (exercises needsTimeZone): in LA the wall-clock
micros differ from UTC.
+ if (zone == LA) {
+ assert(convertTz(instantToNanosVal(instants.head).epochMicros, UTC,
zone) !=
+ instantToNanosVal(instants.head).epochMicros)
+ }
+ }
+ }
+
+ test("cross-family nanos cast: timestamp_ntz to timestamp_ltz") {
+ // NTZ(q) denotes a wall-clock local date-time; NTZ(q) -> LTZ(p)
interprets it in the session
+ // zone to obtain the absolute instant (mirroring micro TIMESTAMP_NTZ ->
TIMESTAMP on the
+ // epoch-micros part) and re-floors the sub-microsecond digits to p.
+ Seq(UTC, LA).foreach { zone =>
+ val zid = Option(zone.getId)
+ val localDateTimes = Seq(
+ LocalDateTime.of(2020, 1, 1, 12, 30, 15, 123456789),
+ LocalDateTime.of(1969, 12, 31, 23, 59, 59, 999999789))
+ localDateTimes.foreach { localDateTime =>
+ val srcMicros = localDateTimeToNanosVal(localDateTime).epochMicros
+ val ltzMicros = convertTz(srcMicros, zone, UTC)
+ for {
+ p <- TimestampLTZNanosType.MIN_PRECISION to
TimestampLTZNanosType.MAX_PRECISION
+ q <- TimestampNTZNanosType.MIN_PRECISION to
TimestampNTZNanosType.MAX_PRECISION
+ } {
+ val srcNanos = floorNanosToPrecision(789, q)
+ val expectedNanos = floorNanosToPrecision(srcNanos, p)
+ checkEvaluation(
+ cast(Literal.create(nanosVal(srcMicros, srcNanos),
TimestampNTZNanosType(q)),
+ TimestampLTZNanosType(p), zid),
+ nanosVal(ltzMicros, expectedNanos))
+ checkEvaluation(
+ cast(Literal.create(null, TimestampNTZNanosType(q)),
TimestampLTZNanosType(p), zid),
+ null)
+ }
+ }
+ if (zone == LA) {
+
assert(convertTz(localDateTimeToNanosVal(localDateTimes.head).epochMicros,
zone, UTC) !=
+ localDateTimeToNanosVal(localDateTimes.head).epochMicros)
+ }
+ }
+ }
+
+ test("cross-family nanos cast: round-trip ltz -> ntz -> ltz preserves the
instant") {
+ // Round-tripping through the wall-clock NTZ form and back to LTZ in the
same zone is the
+ // identity at equal precision (no sub-microsecond digits are dropped).
+ Seq(UTC, LA).foreach { zone =>
+ val zid = Option(zone.getId)
+ foreachNanosPrecision { p =>
+ val src = nanosVal(
+ instantToNanosVal(timestampLTZ(2020, 7, 1, 6, 15, 30,
123456789)).epochMicros,
+ floorNanosToPrecision(789, p))
+ checkEvaluation(
+ cast(
+ cast(Literal.create(src, TimestampLTZNanosType(p)),
TimestampNTZNanosType(p), zid),
+ TimestampLTZNanosType(p), zid),
+ src)
+ }
+ }
+ }
+
+ test("cross-family nanos cast: DST gap and overlap resolve like the micro
cast") {
+ // The cross-family nanos casts reuse the same java.time resolver as the
micro
+ // TIMESTAMP <-> TIMESTAMP_NTZ casts (NTZ -> LTZ interprets the wall clock
via
+ // LocalDateTime.atZone; LTZ -> NTZ renders the instant via
Instant.atZone). Exercise the LA
+ // spring-forward gap (02:30 does not exist -> java.time shifts forward)
and the fall-back
+ // overlap (01:30 occurs twice -> earlier offset), asserting the nanos
result matches the
+ // corresponding micro cast on the epoch-micros part. The existing zone
tests above use
+ // non-transition instants, so this is the only place DST resolution is
observable.
+ val la = Option(LA.getId)
+ Seq(
+ LocalDateTime.of(2020, 3, 8, 2, 30, 0, 123456789), // inside the PST
-> PDT gap
+ LocalDateTime.of(2020, 11, 1, 1, 30, 0, 123456789)) // inside the PDT
-> PST overlap
+ .foreach { ldt =>
+ // NTZ stores the wall-clock fields as epoch-micros-at-UTC, exactly like
micro TIMESTAMP_NTZ.
+ val ntzMicros = localDateTimeToNanosVal(ldt).epochMicros
+ // The expected LTZ instant is whatever the micro TIMESTAMP_NTZ ->
TIMESTAMP cast produces;
+ // for the gap this is the forward-shifted instant, for the overlap the
earlier offset.
+ val microLtz = evaluateWithoutCodegen(
+ cast(Literal.create(ntzMicros, TimestampNTZType), TimestampType,
la)).asInstanceOf[Long]
+ // The reverse micro TIMESTAMP -> TIMESTAMP_NTZ of that instant (for the
gap, the wall clock
+ // is the shifted 03:30, not the original 02:30).
+ val microNtz = evaluateWithoutCodegen(
+ cast(Literal.create(microLtz, TimestampType), TimestampNTZType,
la)).asInstanceOf[Long]
+ for {
+ p <- TimestampLTZNanosType.MIN_PRECISION to
TimestampLTZNanosType.MAX_PRECISION
+ q <- TimestampNTZNanosType.MIN_PRECISION to
TimestampNTZNanosType.MAX_PRECISION
+ } {
+ // NTZ(q) -> LTZ(p): the epoch-micros part resolves DST exactly like
the micro cast.
+ val ntzToLtzSrc = floorNanosToPrecision(789, q)
+ checkEvaluation(
+ cast(Literal.create(nanosVal(ntzMicros, ntzToLtzSrc),
TimestampNTZNanosType(q)),
+ TimestampLTZNanosType(p), la),
+ nanosVal(microLtz, floorNanosToPrecision(ntzToLtzSrc, p)))
+ // LTZ(p) -> NTZ(q): rendering the resolved instant matches the micro
cast too.
+ val ltzToNtzSrc = floorNanosToPrecision(789, p)
+ checkEvaluation(
+ cast(Literal.create(nanosVal(microLtz, ltzToNtzSrc),
TimestampLTZNanosType(p)),
+ TimestampNTZNanosType(q), la),
+ nanosVal(microNtz, floorNanosToPrecision(ltzToNtzSrc, q)))
+ }
+ }
+ }
+
+ test("cross-family nanos cast: micro family member (precision 6) to/from
nanos") {
+ // The precision-6 micro family members are TIMESTAMP_LTZ(6) = TIMESTAMP
and
+ // TIMESTAMP_NTZ(6) = TIMESTAMP_NTZ. Casting them across families
reinterprets the value against
+ // the session zone (mirroring the all-micro TIMESTAMP <-> TIMESTAMP_NTZ
conversion); micro
+ // targets carry a zero sub-microsecond part and micro sources contribute
none.
+ Seq(UTC, LA).foreach { zone =>
+ val zid = Option(zone.getId)
+ // Micro LTZ instant (sub-micro digits are not representable in the
micro type).
+ val ltzMicros = instantToMicros(timestampLTZ(2020, 1, 1, 12, 30, 15,
123456000))
+ val ntzMicrosForLtz = convertTz(ltzMicros, UTC, zone)
+ // Micro NTZ wall-clock value.
+ val ntzMicros =
+ localDateTimeToNanosVal(LocalDateTime.of(2020, 1, 1, 12, 30, 15,
123456000)).epochMicros
+ val ltzMicrosForNtz = convertTz(ntzMicros, zone, UTC)
+ foreachNanosPrecision { p =>
+ // TIMESTAMP (LTZ(6)) -> TIMESTAMP_NTZ(p): wall clock in the session
zone, sub-micro = 0.
+ checkEvaluation(
+ cast(Literal.create(ltzMicros, TimestampType),
TimestampNTZNanosType(p), zid),
+ nanosVal(ntzMicrosForLtz, 0))
+ // TIMESTAMP_NTZ (NTZ(6)) -> TIMESTAMP_LTZ(p): instant of the wall
clock, sub-micro = 0.
+ checkEvaluation(
+ cast(Literal.create(ntzMicros, TimestampNTZType),
TimestampLTZNanosType(p), zid),
+ nanosVal(ltzMicrosForNtz, 0))
+ // TIMESTAMP_NTZ(p) -> TIMESTAMP (LTZ(6)): drops the sub-microsecond
digits before the zone
+ // reinterpretation. A non-zero nanosWithinMicro on the source proves
the truncation.
+ checkEvaluation(
+ cast(Literal.create(nanosVal(ntzMicros, floorNanosToPrecision(789,
p)),
+ TimestampNTZNanosType(p)), TimestampType, zid),
+ convertTz(ntzMicros, zone, UTC))
+ // TIMESTAMP_LTZ(p) -> TIMESTAMP_NTZ (NTZ(6)): drops the
sub-microsecond digits likewise.
+ checkEvaluation(
+ cast(Literal.create(nanosVal(ltzMicros, floorNanosToPrecision(789,
p)),
+ TimestampLTZNanosType(p)), TimestampNTZType, zid),
+ convertTz(ltzMicros, UTC, zone))
+ // Null input in all four directions.
+ checkEvaluation(
+ cast(Literal.create(null, TimestampType), TimestampNTZNanosType(p),
zid), null)
+ checkEvaluation(
+ cast(Literal.create(null, TimestampNTZType),
TimestampLTZNanosType(p), zid), null)
+ checkEvaluation(
+ cast(Literal.create(null, TimestampNTZNanosType(p)), TimestampType,
zid), null)
+ checkEvaluation(
+ cast(Literal.create(null, TimestampLTZNanosType(p)),
TimestampNTZType, zid), null)
+ }
+ }
+ }
+
test("SPARK-57323: cast between date and timestamp_ntz with nanosecond
precision") {
// NTZ casts use a fixed UTC wall-clock grid, independent of the session
time zone.
val date = LocalDate.of(2020, 1, 1)
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 bf344eb08e05..a3c8e894f8a9 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
@@ -872,6 +872,158 @@ Project [cast(cast(null as timestamp_ltz(8)) as
timestamp_ltz(9)) AS CAST(CAST(N
+- OneRowRelation
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(7))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(7))) AS
typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(9))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9))) AS
typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(8))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(8))) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(8)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(9))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(9))) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(9)::timestamp_ltz(9)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9)) as
timestamp_ltz(9)) AS CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9)) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(9)::timestamp_ntz(9)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(9)) as
timestamp_ntz(9)) AS CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(7)::timestamp_ltz(7)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(7)) as
timestamp_ltz(7)) AS CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7)) AS TIMESTAMP_LTZ(7))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ntz'1960-01-01
00:00:00.123456789'::timestamp_ltz(7)::timestamp_ntz(7)
+-- !query analysis
+Project [cast(cast(1960-01-01 00:00:00.123456789 as timestamp_ltz(7)) as
timestamp_ntz(7)) AS CAST(CAST(TIMESTAMP_NTZ '1960-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(7)) AS TIMESTAMP_NTZ(7))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz(7)
+-- !query analysis
+Project [cast(cast(null as timestamp_ltz(9)) as timestamp_ntz(7)) AS
CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ(7))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ntz(8))::timestamp_ltz(9)
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz(8)) as timestamp_ltz(9)) AS
CAST(CAST(NULL AS TIMESTAMP_NTZ(8)) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456'::timestamp_ltz(9))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456 as timestamp_ltz(9))) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(6))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz)) AS
typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS TIMESTAMP_NTZ))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(6))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp)) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS TIMESTAMP))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9)::timestamp
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456'::timestamp_ltz(9)::timestamp_ntz
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456 as timestamp_ltz(9)) as
timestamp_ntz) AS CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(6)::timestamp_ltz
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz) as
timestamp) AS CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(6)::timestamp_ntz
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp) as
timestamp_ntz) AS CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp)::timestamp_ntz(9)
+-- !query analysis
+Project [cast(cast(null as timestamp) as timestamp_ntz(9)) AS CAST(CAST(NULL
AS TIMESTAMP) AS TIMESTAMP_NTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ntz)::timestamp_ltz(9)
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz) as timestamp_ltz(9)) AS
CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ntz(9))::timestamp
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz(9)) as timestamp) AS CAST(CAST(NULL
AS TIMESTAMP_NTZ(9)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz
+-- !query analysis
+Project [cast(cast(null as timestamp_ltz(9)) as timestamp_ntz) AS
CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ)#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 c6ee17e8e387..b18f5739fd68 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
@@ -736,6 +736,158 @@ Project [cast(cast(null as timestamp_ltz(8)) as
timestamp_ltz(9)) AS CAST(CAST(N
+- OneRowRelation
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(7))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(7))) AS
typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(9))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9))) AS
typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(8))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(8))) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(8)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(9))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(9))) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(9)::timestamp_ltz(9)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(9)) as
timestamp_ltz(9)) AS CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9)) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(9)::timestamp_ntz(9)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ltz(9)) as
timestamp_ntz(9)) AS CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(7)::timestamp_ltz(7)
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz(7)) as
timestamp_ltz(7)) AS CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7)) AS TIMESTAMP_LTZ(7))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ntz'1960-01-01
00:00:00.123456789'::timestamp_ltz(7)::timestamp_ntz(7)
+-- !query analysis
+Project [cast(cast(1960-01-01 00:00:00.123456789 as timestamp_ltz(7)) as
timestamp_ntz(7)) AS CAST(CAST(TIMESTAMP_NTZ '1960-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(7)) AS TIMESTAMP_NTZ(7))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz(7)
+-- !query analysis
+Project [cast(cast(null as timestamp_ltz(9)) as timestamp_ntz(7)) AS
CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ(7))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ntz(8))::timestamp_ltz(9)
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz(8)) as timestamp_ltz(9)) AS
CAST(CAST(NULL AS TIMESTAMP_NTZ(8)) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456'::timestamp_ltz(9))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456 as timestamp_ltz(9))) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(6))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz)) AS
typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS TIMESTAMP_NTZ))#x]
++- OneRowRelation
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(6))
+-- !query analysis
+Project [typeof(cast(2020-01-01 00:00:00.123456789 as timestamp)) AS
typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS TIMESTAMP))#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9)::timestamp
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456'::timestamp_ltz(9)::timestamp_ntz
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456 as timestamp_ltz(9)) as
timestamp_ntz) AS CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(6)::timestamp_ltz
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp_ntz) as
timestamp) AS CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(6)::timestamp_ntz
+-- !query analysis
+Project [cast(cast(2020-01-01 00:00:00.123456789 as timestamp) as
timestamp_ntz) AS CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp)::timestamp_ntz(9)
+-- !query analysis
+Project [cast(cast(null as timestamp) as timestamp_ntz(9)) AS CAST(CAST(NULL
AS TIMESTAMP) AS TIMESTAMP_NTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ntz)::timestamp_ltz(9)
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz) as timestamp_ltz(9)) AS
CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ntz(9))::timestamp
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz(9)) as timestamp) AS CAST(CAST(NULL
AS TIMESTAMP_NTZ(9)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz
+-- !query analysis
+Project [cast(cast(null as timestamp_ltz(9)) as timestamp_ntz) AS
CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ)#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 a9e855106624..87621d4413d3 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/cast.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/cast.sql
@@ -164,6 +164,41 @@ select timestamp_ltz'1960-01-01
00:00:00.123456789'::timestamp_ltz(9)::timestamp
select cast(null as timestamp_ntz(9))::timestamp_ntz(7);
select cast(null as timestamp_ltz(8))::timestamp_ltz(9);
+-- Cross-family cast between nanosecond TIMESTAMP_LTZ(p) and TIMESTAMP_NTZ(q).
+-- The conversion reinterprets the value against the session time zone, so
SQL-layer coverage stays
+-- zone-independent (type resolution, lossless same-zone round-trips, null
propagation); the
+-- zone-aware value semantics (flooring, pre-epoch, zone shifts) are covered
in CastSuite* unit tests.
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(7));
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(9));
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(8));
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(9));
+-- Same-zone round-trips are lossless at equal precision and re-floored when
narrowing in between.
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(9)::timestamp_ltz(9);
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(9)::timestamp_ntz(9);
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(7)::timestamp_ltz(7);
+select timestamp_ntz'1960-01-01
00:00:00.123456789'::timestamp_ltz(7)::timestamp_ntz(7);
+-- Null propagation in both directions.
+select cast(null as timestamp_ltz(9))::timestamp_ntz(7);
+select cast(null as timestamp_ntz(8))::timestamp_ltz(9);
+
+-- Boundary at precision 6: TIMESTAMP_LTZ(6) is TIMESTAMP and TIMESTAMP_NTZ(6)
is TIMESTAMP_NTZ, so
+-- these cross-family casts mix the micro family member with the other
family's nanos member.
+select typeof(timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9));
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456'::timestamp_ltz(9));
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(6));
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(6));
+-- Same-zone round-trips: micro(6) -> nanos -> micro(6) is lossless; nanos ->
micro(6) -> nanos
+-- drops the sub-microsecond digits.
+select timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9)::timestamp;
+select timestamp_ntz'2020-01-01
00:00:00.123456'::timestamp_ltz(9)::timestamp_ntz;
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(6)::timestamp_ltz;
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(6)::timestamp_ntz;
+-- Null propagation across the micro boundary in all four directions.
+select cast(null as timestamp)::timestamp_ntz(9);
+select cast(null as timestamp_ntz)::timestamp_ltz(9);
+select cast(null as timestamp_ntz(9))::timestamp;
+select cast(null as timestamp_ltz(9))::timestamp_ntz;
+
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 b6da2f1c32e2..24f9668b3b17 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
@@ -1586,6 +1586,182 @@ struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(8)) AS
TIMESTAMP_LTZ(9)):timestamp_ltz(9)
NULL
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(7))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7))):string>
+-- !query output
+timestamp_ntz(7)
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9))):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(8))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(8))):string>
+-- !query output
+timestamp_ltz(8)
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9))):string>
+-- !query output
+timestamp_ltz(9)
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(9)::timestamp_ltz(9)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9)) AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+2020-01-01 00:00:00.123456789
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(9)::timestamp_ntz(9)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.123456789
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(7)::timestamp_ltz(7)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7)) AS TIMESTAMP_LTZ(7)):timestamp_ltz(7)>
+-- !query output
+2020-01-01 00:00:00.1234567
+
+
+-- !query
+select timestamp_ntz'1960-01-01
00:00:00.123456789'::timestamp_ltz(7)::timestamp_ntz(7)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '1960-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(7)) AS TIMESTAMP_NTZ(7)):timestamp_ntz(7)>
+-- !query output
+1960-01-01 00:00:00.1234567
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz(7)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS
TIMESTAMP_NTZ(7)):timestamp_ntz(7)>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ntz(8))::timestamp_ltz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ(8)) AS
TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+NULL
+
+
+-- !query
+select typeof(timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP '2020-01-01 00:00:00.123456' AS
TIMESTAMP_NTZ(9))):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456'::timestamp_ltz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS
TIMESTAMP_LTZ(9))):string>
+-- !query output
+timestamp_ltz(9)
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(6))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ)):string>
+-- !query output
+timestamp_ntz
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(6))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP)):string>
+-- !query output
+timestamp
+
+
+-- !query
+select timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9)::timestamp
+-- !query schema
+struct<CAST(CAST(TIMESTAMP '2020-01-01 00:00:00.123456' AS TIMESTAMP_NTZ(9))
AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456'::timestamp_ltz(9)::timestamp_ntz
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(6)::timestamp_ltz
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ) AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(6)::timestamp_ntz
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS TIMESTAMP)
AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(null as timestamp)::timestamp_ntz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP) AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ntz)::timestamp_ltz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ntz(9))::timestamp
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS TIMESTAMP):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+NULL
+
+
-- !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 408cb06ef36b..4d66796a577b 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
@@ -848,6 +848,182 @@ struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(8)) AS
TIMESTAMP_LTZ(9)):timestamp_ltz(9)
NULL
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(7))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7))):string>
+-- !query output
+timestamp_ntz(7)
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9))):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(8))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(8))):string>
+-- !query output
+timestamp_ltz(8)
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9))):string>
+-- !query output
+timestamp_ltz(9)
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(9)::timestamp_ltz(9)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(9)) AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+2020-01-01 00:00:00.123456789
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(9)::timestamp_ntz(9)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.123456789
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(7)::timestamp_ltz(7)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ(7)) AS TIMESTAMP_LTZ(7)):timestamp_ltz(7)>
+-- !query output
+2020-01-01 00:00:00.1234567
+
+
+-- !query
+select timestamp_ntz'1960-01-01
00:00:00.123456789'::timestamp_ltz(7)::timestamp_ntz(7)
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '1960-01-01 00:00:00.123456789' AS
TIMESTAMP_LTZ(7)) AS TIMESTAMP_NTZ(7)):timestamp_ntz(7)>
+-- !query output
+1960-01-01 00:00:00.1234567
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz(7)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS
TIMESTAMP_NTZ(7)):timestamp_ntz(7)>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ntz(8))::timestamp_ltz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ(8)) AS
TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+NULL
+
+
+-- !query
+select typeof(timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP '2020-01-01 00:00:00.123456' AS
TIMESTAMP_NTZ(9))):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456'::timestamp_ltz(9))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS
TIMESTAMP_LTZ(9))):string>
+-- !query output
+timestamp_ltz(9)
+
+
+-- !query
+select typeof(timestamp_ltz'2020-01-01 00:00:00.123456789'::timestamp_ntz(6))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ)):string>
+-- !query output
+timestamp_ntz
+
+
+-- !query
+select typeof(timestamp_ntz'2020-01-01 00:00:00.123456789'::timestamp_ltz(6))
+-- !query schema
+struct<typeof(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP)):string>
+-- !query output
+timestamp
+
+
+-- !query
+select timestamp'2020-01-01 00:00:00.123456'::timestamp_ntz(9)::timestamp
+-- !query schema
+struct<CAST(CAST(TIMESTAMP '2020-01-01 00:00:00.123456' AS TIMESTAMP_NTZ(9))
AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456'::timestamp_ltz(9)::timestamp_ntz
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456' AS
TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select timestamp_ltz'2020-01-01
00:00:00.123456789'::timestamp_ntz(6)::timestamp_ltz
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789' AS
TIMESTAMP_NTZ) AS TIMESTAMP):timestamp>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select timestamp_ntz'2020-01-01
00:00:00.123456789'::timestamp_ltz(6)::timestamp_ntz
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' AS TIMESTAMP)
AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+2020-01-01 00:00:00.123456
+
+
+-- !query
+select cast(null as timestamp)::timestamp_ntz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP) AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ntz)::timestamp_ltz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ntz(9))::timestamp
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS TIMESTAMP):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+select cast(null as timestamp_ltz(9))::timestamp_ntz
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+NULL
+
+
-- !query
select cast(cast('inf' as double) as timestamp)
-- !query schema
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
