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 849583140439 [SPARK-57323][SQL] Support casting between DATE and
nanosecond-precision timestamp types TIMESTAMP_NTZ(p) and TIMESTAMP_LTZ(p)
849583140439 is described below
commit 849583140439b106c37940f629064f01025ea6a8
Author: Maxim Gekk <[email protected]>
AuthorDate: Mon Jun 15 13:55:40 2026 +0200
[SPARK-57323][SQL] Support casting between DATE and nanosecond-precision
timestamp types TIMESTAMP_NTZ(p) and TIMESTAMP_LTZ(p)
### What changes were proposed in this pull request?
This PR adds explicit `CAST` support in both directions between `DATE` and
the nanosecond-precision timestamp types `TIMESTAMP_NTZ(p)` /
`TIMESTAMP_LTZ(p)` (precision `p` in `[7, 9]`, represented by
`TimestampNTZNanosType` / `TimestampLTZNanosType`):
- `DATE -> TIMESTAMP_NTZ(p)`
- `DATE -> TIMESTAMP_LTZ(p)`
- `TIMESTAMP_NTZ(p) -> DATE`
- `TIMESTAMP_LTZ(p) -> DATE`
Semantics are kept at parity with the existing microsecond `DATE <->
TIMESTAMP` casts:
- `DATE -> TIMESTAMP_LTZ(p)`: midnight of the date in the session time
zone, sub-microsecond part = 0.
- `DATE -> TIMESTAMP_NTZ(p)`: midnight UTC, sub-microsecond part = 0.
- `TIMESTAMP_LTZ(p) -> DATE`: calendar date in the session time zone; the
time-of-day and sub-microsecond digits are dropped.
- `TIMESTAMP_NTZ(p) -> DATE`: calendar date on the UTC wall-clock grid; the
time-of-day and sub-microsecond digits are dropped.
All changes are confined to `Cast.scala`:
- `canCast` / `canAnsiCast` allow all four conversions.
- `needsTimeZone` marks only the LTZ directions as zone-sensitive; the NTZ
directions use a fixed UTC grid (mirroring micro `TIMESTAMP_NTZ <-> DATE`,
which is intentionally zone-independent).
- `canANSIStoreAssign` returns `false` for all four `DATE <-> nanos`
combinations, so an explicit `CAST` is required in both directions. For `nanos
-> DATE` this avoids silently dropping time-of-day and sub-microsecond digits,
consistent with the nanos -> micros narrowing rule from SPARK-57293. The
lossless `DATE -> nanos` direction stays explicit-only as a deliberate
conservative policy while the nanos types are unreleased (allowing it later is
a compatible change, revoking it is not [...]
- `forceNullable` returns `false` for all four (parity with micro
`TIMESTAMP <-> DATE`; this is more precise than micro `TIMESTAMP_NTZ <-> DATE`,
which conservatively forces nullability via catch-all arms even though the
conversion never produces NULL).
- The interpreted and codegen cast methods (`castToDate`,
`castToTimestampLTZNanos`, `castToTimestampNTZNanos`, and their `*Code`
counterparts) implement the conversions.
No implicit up-cast / type-coercion changes (`UpCastRule` and
`findWiderDateTimeType` are unchanged).
Closes #56409
### Why are the changes needed?
The nanosecond-precision timestamp types `TIMESTAMP_NTZ(p)` and
`TIMESTAMP_LTZ(p)` currently have no `CAST` support to or from `DATE`: the
Catalyst `Cast` expression only implements `DATE <-> TIMESTAMP` and `DATE <->
TIMESTAMP_NTZ` for the microsecond GA types, plus the micros <-> nanos casts
added in SPARK-57293. This is a gap in the nanosecond timestamp feature
(SPARK-56822); users should be able to convert between `DATE` and the
nanosecond timestamp types just as they can with the [...]
### Does this PR introduce _any_ user-facing change?
Yes. Casting between `DATE` and `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)`
previously failed type checking; it is now a supported explicit `CAST` in both
directions. There is no change to released Spark versions, since the nanosecond
timestamp types are unreleased (under SPARK-56822 on master).
### How was this patch tested?
Added tests in `CastSuiteBase` (run under ANSI-off, ANSI-on, and `try_cast`
via the `CastWithAnsiOffSuite`, `CastWithAnsiOnSuite`, and `TryCastSuite`
subclasses):
- Null casts for both directions at MIN/MAX precision.
- `canANSIStoreAssign` assertions that all four `DATE <-> nanos`
combinations are blocked.
- "cast between date and timestamp_ntz with nanosecond precision": `DATE ->
NTZ(p)` yields midnight UTC with nanos = 0; `NTZ(p) -> DATE` drops the
time-of-day and sub-microsecond digits; round trip; nulls both ways.
- "cast between date and timestamp_ltz with nanosecond precision": same in
the session zone, plus a zone-sensitivity case (a western zone maps the date to
a later midnight instant) to exercise `needsTimeZone`.
```
build/sbt 'catalyst/testOnly
org.apache.spark.sql.catalyst.expressions.CastWithAnsiOffSuite
org.apache.spark.sql.catalyst.expressions.CastWithAnsiOnSuite
org.apache.spark.sql.catalyst.expressions.TryCastSuite'
```
`catalyst/scalastyle` and `catalyst/Test/scalastyle` both pass.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Cursor (Claude Opus 4.8)
Closes #56375 from MaxGekk/nanos-cast-date.
Authored-by: Maxim Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
---
.../spark/sql/catalyst/expressions/Cast.scala | 57 ++++++++++
.../sql/catalyst/expressions/CastSuiteBase.scala | 116 +++++++++++++++++++++
.../analyzer-results/timestamp-ltz-nanos.sql.out | 85 +++++++++++++++
.../analyzer-results/timestamp-ntz-nanos.sql.out | 78 ++++++++++++++
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 21 ++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 18 ++++
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 104 ++++++++++++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 96 +++++++++++++++++
8 files changed, 575 insertions(+)
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 29f0f5181a18..fdb5ea110588 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
@@ -121,6 +121,11 @@ object Cast extends QueryErrorsBase {
case (TimestampType, _: TimestampLTZNanosType) => true
case (_: TimestampLTZNanosType, TimestampType) => true
+ case (DateType, _: TimestampLTZNanosType) => true
+ case (_: TimestampLTZNanosType, DateType) => true
+ case (DateType, _: TimestampNTZNanosType) => true
+ case (_: TimestampNTZNanosType, DateType) => true
+
case (_: StringType, _: CalendarIntervalType) => true
case (_: StringType, _: AnsiIntervalType) => true
@@ -264,6 +269,11 @@ object Cast extends QueryErrorsBase {
case (TimestampType, _: TimestampLTZNanosType) => true
case (_: TimestampLTZNanosType, TimestampType) => true
+ case (DateType, _: TimestampLTZNanosType) => true
+ case (_: TimestampLTZNanosType, DateType) => true
+ case (DateType, _: TimestampNTZNanosType) => true
+ case (_: TimestampNTZNanosType, DateType) => true
+
case (_: StringType, DateType) => true
case (_: StringType, _: TimeType) => true
case (TimestampType, DateType) => true
@@ -355,6 +365,10 @@ object Cast extends QueryErrorsBase {
// the LTZ string parse/render depends on the session time zone.
case (_: StringType, _: TimestampLTZNanosType) => true
case (_: TimestampLTZNanosType, _: StringType) => true
+ // Only the LTZ directions are zone-sensitive; the NTZ DATE casts use a
fixed UTC grid,
+ // mirroring micro TIMESTAMP_NTZ<->DATE which is intentionally absent here.
+ case (DateType, _: TimestampLTZNanosType) => true
+ case (_: TimestampLTZNanosType, DateType) => true
case (ArrayType(fromType, _), ArrayType(toType, _)) =>
needsTimeZone(fromType, toType)
case (MapType(fromKey, fromValue, _), MapType(toKey, toValue, _)) =>
needsTimeZone(fromKey, toKey) || needsTimeZone(fromValue, toValue)
@@ -415,6 +429,16 @@ object Cast extends QueryErrorsBase {
// 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
case (_: DatetimeType, _: DatetimeType) => true
case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
@@ -462,8 +486,12 @@ object Cast extends QueryErrorsBase {
case (_: TimeType, ByteType | ShortType) => true
case (FloatType | DoubleType, TimestampType) => true
case (TimestampType, DateType) => false
+ case (_: TimestampLTZNanosType, DateType) => false
+ case (_: TimestampNTZNanosType, DateType) => false
case (_, DateType) => true
case (DateType, TimestampType) => false
+ case (DateType, _: TimestampLTZNanosType) => false
+ case (DateType, _: TimestampNTZNanosType) => false
case (DateType, _) => true
case (_, CalendarIntervalType) => true
@@ -827,6 +855,8 @@ case class Cast(
})
case TimestampType =>
buildCast[Long](_, m => TimestampNanosVal.fromParts(m, 0.toShort))
+ case DateType =>
+ buildCast[Int](_, d => TimestampNanosVal.fromParts(daysToMicros(d,
zoneId), 0.toShort))
}
private[this] def castToTimestampNTZNanos(
@@ -841,6 +871,9 @@ case class Cast(
})
case TimestampNTZType =>
buildCast[Long](_, m => TimestampNanosVal.fromParts(m, 0.toShort))
+ case DateType =>
+ buildCast[Int](_, d =>
+ TimestampNanosVal.fromParts(daysToMicros(d, ZoneOffset.UTC),
0.toShort))
}
private[this] def decimalToTimestamp(d: Decimal): Long = {
@@ -878,6 +911,10 @@ case class Cast(
buildCast[Long](_, t => microsToDays(t, zoneId))
case TimestampNTZType =>
buildCast[Long](_, t => microsToDays(t, ZoneOffset.UTC))
+ case _: TimestampLTZNanosType =>
+ buildCast[TimestampNanosVal](_, v => microsToDays(v.epochMicros, zoneId))
+ case _: TimestampNTZNanosType =>
+ buildCast[TimestampNanosVal](_, v => microsToDays(v.epochMicros,
ZoneOffset.UTC))
}
private[this] def castToTime(from: DataType, to: TimeType): Any => Any =
from match {
@@ -1558,6 +1595,14 @@ case class Cast(
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)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.microsToDays($c.epochMicros, $zid);"
+ case _: TimestampNTZNanosType =>
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.microsToDays($c.epochMicros,
java.time.ZoneOffset.UTC);"
case _ =>
(c, evPrim, evNull) => code"$evNull = true;"
}
@@ -1868,6 +1913,14 @@ case class Cast(
case TimestampType =>
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts($c, (short) 0);"
+ case DateType =>
+ val zoneIdClass = classOf[ZoneId]
+ val zid = JavaCode.global(
+ ctx.addReferenceObj("zoneId", zoneId, zoneIdClass.getName),
+ zoneIdClass)
+ (c, evPrim, evNull) =>
+ code"$evPrim = TimestampNanosVal.fromParts(" +
+ code"$dateTimeUtilsCls.daysToMicros($c, $zid), (short) 0);"
}
private[this] def castToTimestampNTZNanosCode(
@@ -1897,6 +1950,10 @@ case class Cast(
case TimestampNTZType =>
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts($c, (short) 0);"
+ case DateType =>
+ (c, evPrim, evNull) =>
+ code"$evPrim = TimestampNanosVal.fromParts(" +
+ code"$dateTimeUtilsCls.daysToMicros($c, java.time.ZoneOffset.UTC),
(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 de8862f5bc6c..bb5484b4771d 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
@@ -92,10 +92,14 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
Seq(TimestampNTZNanosType.MIN_PRECISION,
TimestampNTZNanosType.MAX_PRECISION).foreach { p =>
checkNullCast(TimestampNTZType, TimestampNTZNanosType(p))
checkNullCast(TimestampNTZNanosType(p), TimestampNTZType)
+ checkNullCast(DateType, TimestampNTZNanosType(p))
+ checkNullCast(TimestampNTZNanosType(p), DateType)
}
Seq(TimestampLTZNanosType.MIN_PRECISION,
TimestampLTZNanosType.MAX_PRECISION).foreach { p =>
checkNullCast(TimestampType, TimestampLTZNanosType(p))
checkNullCast(TimestampLTZNanosType(p), TimestampType)
+ checkNullCast(DateType, TimestampLTZNanosType(p))
+ checkNullCast(TimestampLTZNanosType(p), DateType)
}
checkNullCast(StringType, BinaryType)
checkNullCast(StringType, BooleanType)
@@ -745,6 +749,19 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
// ... but blocks the lossy narrowing nanos(p) -> micros to avoid silent
truncation.
assert(!Cast.canANSIStoreAssign(TimestampNTZNanosType(p),
TimestampNTZType))
assert(!Cast.canANSIStoreAssign(TimestampLTZNanosType(p), TimestampType))
+
+ // SPARK-57323: DATE <-> nanos requires an explicit CAST in both
directions, so STRICT
+ // store assignment and ANSI store assignment both reject it. STRICT
goes through
+ // Cast.canUpCast, so the assertions below also guard against a future
blanket datetime arm
+ // in UpCastRule silently turning this into a safe store assignment.
+ assert(!Cast.canUpCast(DateType, TimestampNTZNanosType(p)))
+ assert(!Cast.canUpCast(TimestampNTZNanosType(p), DateType))
+ assert(!Cast.canUpCast(DateType, TimestampLTZNanosType(p)))
+ assert(!Cast.canUpCast(TimestampLTZNanosType(p), DateType))
+ assert(!Cast.canANSIStoreAssign(DateType, TimestampNTZNanosType(p)))
+ assert(!Cast.canANSIStoreAssign(TimestampNTZNanosType(p), DateType))
+ assert(!Cast.canANSIStoreAssign(DateType, TimestampLTZNanosType(p)))
+ assert(!Cast.canANSIStoreAssign(TimestampLTZNanosType(p), DateType))
}
}
@@ -1289,6 +1306,105 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ 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)
+ val days = localDateToDays(date)
+ val midnightUtcMicros = daysToMicros(days, UTC)
+ foreachNanosPrecision { precision =>
+ // DATE -> TIMESTAMP_NTZ(p): midnight UTC with a zero sub-microsecond
part.
+ checkEvaluation(
+ cast(Literal.create(date, DateType), TimestampNTZNanosType(precision)),
+ TimestampNanosVal.fromParts(midnightUtcMicros, 0.toShort))
+ // TIMESTAMP_NTZ(p) -> DATE: drops the time-of-day and the
sub-microsecond digits.
+ val noon = localDateTimeToNanosVal(LocalDateTime.of(2020, 1, 1, 12, 30,
15, 123456789))
+ checkEvaluation(
+ cast(Literal.create(noon, TimestampNTZNanosType(precision)), DateType),
+ days)
+ // Round-trip DATE -> NTZ(p) -> DATE is the identity (NTZ(p) starts the
day at midnight).
+ checkEvaluation(
+ cast(cast(Literal.create(date, DateType),
TimestampNTZNanosType(precision)), DateType),
+ days)
+ // Null input in both directions.
+ checkEvaluation(
+ cast(Literal.create(null, DateType),
TimestampNTZNanosType(precision)), null)
+ checkEvaluation(
+ cast(Literal.create(null, TimestampNTZNanosType(precision)),
DateType), null)
+ }
+ }
+
+ test("SPARK-57323: cast between date and timestamp_ltz with nanosecond
precision") {
+ // LTZ casts resolve the calendar date / midnight instant in the session
time zone.
+ val date = LocalDate.of(2020, 1, 1)
+ val days = localDateToDays(date)
+ foreachNanosPrecision { precision =>
+ // DATE -> TIMESTAMP_LTZ(p): midnight of the date in the session zone,
sub-micro part = 0.
+ checkEvaluation(
+ cast(Literal.create(date, DateType), TimestampLTZNanosType(precision),
UTC_OPT),
+ TimestampNanosVal.fromParts(daysToMicros(days, UTC), 0.toShort))
+ // TIMESTAMP_LTZ(p) -> DATE: calendar date in the session zone; the
time-of-day and the
+ // sub-microsecond digits are dropped.
+ val noon = instantToNanosVal(timestampLTZ(2020, 1, 1, 12, 30, 15,
123456789))
+ checkEvaluation(
+ cast(Literal.create(noon, TimestampLTZNanosType(precision)), DateType,
UTC_OPT),
+ days)
+ // Round-trip DATE -> LTZ(p) -> DATE in the same zone is the identity.
+ checkEvaluation(
+ cast(
+ cast(Literal.create(date, DateType),
TimestampLTZNanosType(precision), UTC_OPT),
+ DateType, UTC_OPT),
+ days)
+ // Zone sensitivity (exercises needsTimeZone): the same date maps to a
later UTC instant in
+ // a western zone, since local midnight in LA occurs after midnight UTC.
+ assert(daysToMicros(days, LA) != daysToMicros(days, UTC))
+ checkEvaluation(
+ cast(Literal.create(date, DateType), TimestampLTZNanosType(precision),
Option(LA.getId)),
+ TimestampNanosVal.fromParts(daysToMicros(days, LA), 0.toShort))
+ // Null input in both directions.
+ checkEvaluation(
+ cast(Literal.create(null, DateType), TimestampLTZNanosType(precision),
UTC_OPT), null)
+ checkEvaluation(
+ cast(Literal.create(null, TimestampLTZNanosType(precision)), DateType,
UTC_OPT), null)
+ }
+ }
+
+ test("SPARK-57323: cast date <-> nanos timestamp nested in complex types") {
+ // DATE <-> nanos casts recurse element-wise through array / map / struct,
exactly like the
+ // microsecond DATE <-> TIMESTAMP casts. NTZ uses the fixed UTC grid
(zone-independent), so the
+ // nested literals are built from their external java.time forms
(LocalDate / LocalDateTime).
+ val date = LocalDate.of(2020, 1, 1) // external form of a DATE value
+ val midnight = LocalDateTime.of(2020, 1, 1, 0, 0) // DATE -> NTZ(p):
midnight, sub-micro = 0
+ val noon = LocalDateTime.of(2020, 1, 1, 12, 30, 15, 123456789) // NTZ(p)
-> DATE drops these
+ foreachNanosPrecision { p =>
+ val ntz = TimestampNTZNanosType(p)
+ // ARRAY<DATE> -> ARRAY<nanos(p)> and ARRAY<nanos(p)> -> ARRAY<DATE>.
+ checkEvaluation(
+ cast(Literal.create(Seq(date), ArrayType(DateType)), ArrayType(ntz)),
+ Literal.create(Seq(midnight), ArrayType(ntz)).value)
+ checkEvaluation(
+ cast(Literal.create(Seq(noon), ArrayType(ntz)), ArrayType(DateType)),
+ Literal.create(Seq(date), ArrayType(DateType)).value)
+ // MAP<STRING, nanos(p)> -> MAP<STRING, DATE> exercises the value side.
+ checkEvaluation(
+ cast(
+ Literal.create(Map("k" -> noon), MapType(StringType, ntz)),
+ MapType(StringType, DateType)),
+ Literal.create(Map("k" -> date), MapType(StringType, DateType)).value)
+ // MAP<nanos(p), STRING> -> MAP<DATE, STRING> exercises the key side.
+ checkEvaluation(
+ cast(
+ Literal.create(Map(noon -> "v"), MapType(ntz, StringType)),
+ MapType(DateType, StringType)),
+ Literal.create(Map(date -> "v"), MapType(DateType, StringType)).value)
+ // STRUCT<f: DATE> -> STRUCT<f: nanos(p)> exercises a struct field.
+ checkEvaluation(
+ cast(
+ Literal.create(Row(date), new StructType().add("f", DateType)),
+ new StructType().add("f", ntz)),
+ Literal.create(Row(midnight), new StructType().add("f", ntz)).value)
+ }
+ }
+
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/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
index 087efaf0c32f..8b5351dd8067 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
@@ -305,3 +305,88 @@ SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01
13:24:35.123456789 Asia/Kol
-- !query analysis
Project [extract(SECOND, 2019-12-31 23:54:35.123456789) AS extract(SECOND FROM
TIMESTAMP_LTZ '2019-12-31 23:54:35.123456789')#x]
+- OneRowRelation
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ltz(9)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ltz(7)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789'::date
+-- !query analysis
+Project [cast(2020-01-01 12:30:15.123456789 as date) AS CAST(TIMESTAMP_LTZ
'2020-01-01 12:30:15.123456789' AS DATE)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '1960-01-01 00:00:00.000000001'::date
+-- !query analysis
+Project [cast(1960-01-01 00:00:00.000000001 as date) AS CAST(TIMESTAMP_LTZ
'1960-01-01 00:00:00.000000001' AS DATE)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC'::date
+-- !query analysis
+Project [cast(2019-12-31 20:00:00.123456789 as date) AS CAST(TIMESTAMP_LTZ
'2019-12-31 20:00:00.123456789' AS DATE)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ltz(9)::date
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT (NULL :: date) :: timestamp_ltz(9)
+-- !query analysis
+Project [cast(cast(null as date) as timestamp_ltz(9)) AS CAST(CAST(NULL AS
DATE) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT (NULL :: timestamp_ltz(9)) :: date
+-- !query analysis
+Project [cast(cast(null as timestamp_ltz(9)) as date) AS CAST(CAST(NULL AS
TIMESTAMP_LTZ(9)) AS DATE)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT array(TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789') :: array<date>
+-- !query analysis
+Project [cast(array(2020-01-01 12:30:15.123456789) as array<date>) AS
array(TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT array(DATE '2020-01-01') :: array<timestamp_ltz(9)>
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT map('k', TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789') :: map<string,
date>
+-- !query analysis
+Project [cast(map(k, 2020-01-01 12:30:15.123456789) as map<string,date>) AS
map(k, TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ltz(9), string>
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ltz(9)>
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
index 567f131badf0..9e31702d3093 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
@@ -249,3 +249,81 @@ SELECT extract(SECOND FROM TIMESTAMP_NTZ '1960-01-01
13:24:35.123456789')
-- !query analysis
Project [extract(SECOND, 1960-01-01 13:24:35.123456789) AS extract(SECOND FROM
TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')#x]
+- OneRowRelation
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ntz(9)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ntz(7)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789'::date
+-- !query analysis
+Project [cast(2020-01-01 12:30:15.123456789 as date) AS CAST(TIMESTAMP_NTZ
'2020-01-01 12:30:15.123456789' AS DATE)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '1960-01-01 00:00:00.000000001'::date
+-- !query analysis
+Project [cast(1960-01-01 00:00:00.000000001 as date) AS CAST(TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000001' AS DATE)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ntz(9)::date
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT (NULL :: date) :: timestamp_ntz(9)
+-- !query analysis
+Project [cast(cast(null as date) as timestamp_ntz(9)) AS CAST(CAST(NULL AS
DATE) AS TIMESTAMP_NTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT (NULL :: timestamp_ntz(9)) :: date
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz(9)) as date) AS CAST(CAST(NULL AS
TIMESTAMP_NTZ(9)) AS DATE)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT array(TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789') :: array<date>
+-- !query analysis
+Project [cast(array(2020-01-01 12:30:15.123456789) as array<date>) AS
array(TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT array(DATE '2020-01-01') :: array<timestamp_ntz(9)>
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT map('k', TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789') :: map<string,
date>
+-- !query analysis
+Project [cast(map(k, 2020-01-01 12:30:15.123456789) as map<string,date>) AS
map(k, TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ntz(9), string>
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ntz(9)>
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
index 37fef0416b8a..f2cff02b94d5 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
@@ -79,3 +79,24 @@ SELECT extract(SECOND FROM TIMESTAMP_LTZ '1960-01-01
13:24:35.123456789');
-- while the second field (including the nanosecond fraction) is
offset-invariant.
SELECT extract(MINUTE FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata');
SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata');
+
+-- DATE <-> TIMESTAMP_LTZ(p) casts (SPARK-57323): midnight in the session zone
/ date extraction.
+-- Nanosecond typed literals derive precision from the fractional digits
(SPARK-57250).
+SELECT DATE '2020-01-01'::timestamp_ltz(9);
+SELECT DATE '2020-01-01'::timestamp_ltz(7);
+SELECT TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789'::date;
+SELECT TIMESTAMP_LTZ '1960-01-01 00:00:00.000000001'::date;
+-- Zone-dependence: a UTC instant in the early hours of Jan 1 falls on Dec 31
in the session
+-- zone (America/Los_Angeles, UTC-08:00), so LTZ -> DATE yields the previous
calendar day.
+SELECT TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC'::date;
+-- Round trip date -> ltz(p) -> date.
+SELECT DATE '2020-01-01'::timestamp_ltz(9)::date;
+-- NULLs in both directions.
+SELECT (NULL :: date) :: timestamp_ltz(9);
+SELECT (NULL :: timestamp_ltz(9)) :: date;
+-- DATE <-> nanos nested in complex types (array / map value / map key /
struct field).
+SELECT array(TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789') :: array<date>;
+SELECT array(DATE '2020-01-01') :: array<timestamp_ltz(9)>;
+SELECT map('k', TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789') :: map<string,
date>;
+SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ltz(9), string>;
+SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ltz(9)>;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
index e95530669277..a6d55ca3fc80 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
@@ -65,3 +65,21 @@ SELECT extract(SECOND FROM NULL :: timestamp_ntz(9));
-- Pre-epoch nanosecond values exercise the negative-epoch path.
SELECT extract(SECOND FROM TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789');
+
+-- DATE <-> TIMESTAMP_NTZ(p) casts (SPARK-57323): midnight UTC / date
extraction (zone-independent).
+-- Nanosecond typed literals derive precision from the fractional digits
(SPARK-57250).
+SELECT DATE '2020-01-01'::timestamp_ntz(9);
+SELECT DATE '2020-01-01'::timestamp_ntz(7);
+SELECT TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789'::date;
+SELECT TIMESTAMP_NTZ '1960-01-01 00:00:00.000000001'::date;
+-- Round trip date -> ntz(p) -> date.
+SELECT DATE '2020-01-01'::timestamp_ntz(9)::date;
+-- NULLs in both directions.
+SELECT (NULL :: date) :: timestamp_ntz(9);
+SELECT (NULL :: timestamp_ntz(9)) :: date;
+-- DATE <-> nanos nested in complex types (array / map value / map key /
struct field).
+SELECT array(TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789') :: array<date>;
+SELECT array(DATE '2020-01-01') :: array<timestamp_ntz(9)>;
+SELECT map('k', TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789') :: map<string,
date>;
+SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ntz(9), string>;
+SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ntz(9)>;
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
index 5909ed3b9cbf..0a216023966b 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
@@ -349,3 +349,107 @@ SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01
13:24:35.123456789 Asia/Kol
struct<extract(SECOND FROM TIMESTAMP_LTZ '2019-12-31
23:54:35.123456789'):decimal(11,9)>
-- !query output
35.123456789
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ltz(9)
+-- !query schema
+struct<CAST(DATE '2020-01-01' AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+2020-01-01 00:00:00
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ltz(7)
+-- !query schema
+struct<CAST(DATE '2020-01-01' AS TIMESTAMP_LTZ(7)):timestamp_ltz(7)>
+-- !query output
+2020-01-01 00:00:00
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789'::date
+-- !query schema
+struct<CAST(TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789' AS DATE):date>
+-- !query output
+2020-01-01
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '1960-01-01 00:00:00.000000001'::date
+-- !query schema
+struct<CAST(TIMESTAMP_LTZ '1960-01-01 00:00:00.000000001' AS DATE):date>
+-- !query output
+1960-01-01
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC'::date
+-- !query schema
+struct<CAST(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789' AS DATE):date>
+-- !query output
+2019-12-31
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ltz(9)::date
+-- !query schema
+struct<CAST(CAST(DATE '2020-01-01' AS TIMESTAMP_LTZ(9)) AS DATE):date>
+-- !query output
+2020-01-01
+
+
+-- !query
+SELECT (NULL :: date) :: timestamp_ltz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS DATE) AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT (NULL :: timestamp_ltz(9)) :: date
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS DATE):date>
+-- !query output
+NULL
+
+
+-- !query
+SELECT array(TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789') :: array<date>
+-- !query schema
+struct<array(TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789'):array<date>>
+-- !query output
+[2020-01-01]
+
+
+-- !query
+SELECT array(DATE '2020-01-01') :: array<timestamp_ltz(9)>
+-- !query schema
+struct<array(DATE '2020-01-01'):array<timestamp_ltz(9)>>
+-- !query output
+[2020-01-01 00:00:00]
+
+
+-- !query
+SELECT map('k', TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789') :: map<string,
date>
+-- !query schema
+struct<map(k, TIMESTAMP_LTZ '2020-01-01 12:30:15.123456789'):map<string,date>>
+-- !query output
+{"k":2020-01-01}
+
+
+-- !query
+SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ltz(9), string>
+-- !query schema
+struct<map(DATE '2020-01-01', v):map<timestamp_ltz(9),string>>
+-- !query output
+{2020-01-01 00:00:00:"v"}
+
+
+-- !query
+SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ltz(9)>
+-- !query schema
+struct<named_struct(f, DATE '2020-01-01'):struct<f:timestamp_ltz(9)>>
+-- !query output
+{"f":2020-01-01 00:00:00}
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
index 236f260e3160..551786284e81 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
@@ -285,3 +285,99 @@ SELECT extract(SECOND FROM TIMESTAMP_NTZ '1960-01-01
13:24:35.123456789')
struct<extract(SECOND FROM TIMESTAMP_NTZ '1960-01-01
13:24:35.123456789'):decimal(11,9)>
-- !query output
35.123456789
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ntz(9)
+-- !query schema
+struct<CAST(DATE '2020-01-01' AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ntz(7)
+-- !query schema
+struct<CAST(DATE '2020-01-01' AS TIMESTAMP_NTZ(7)):timestamp_ntz(7)>
+-- !query output
+2020-01-01 00:00:00
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789'::date
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789' AS DATE):date>
+-- !query output
+2020-01-01
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '1960-01-01 00:00:00.000000001'::date
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '1960-01-01 00:00:00.000000001' AS DATE):date>
+-- !query output
+1960-01-01
+
+
+-- !query
+SELECT DATE '2020-01-01'::timestamp_ntz(9)::date
+-- !query schema
+struct<CAST(CAST(DATE '2020-01-01' AS TIMESTAMP_NTZ(9)) AS DATE):date>
+-- !query output
+2020-01-01
+
+
+-- !query
+SELECT (NULL :: date) :: timestamp_ntz(9)
+-- !query schema
+struct<CAST(CAST(NULL AS DATE) AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT (NULL :: timestamp_ntz(9)) :: date
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS DATE):date>
+-- !query output
+NULL
+
+
+-- !query
+SELECT array(TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789') :: array<date>
+-- !query schema
+struct<array(TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789'):array<date>>
+-- !query output
+[2020-01-01]
+
+
+-- !query
+SELECT array(DATE '2020-01-01') :: array<timestamp_ntz(9)>
+-- !query schema
+struct<array(DATE '2020-01-01'):array<timestamp_ntz(9)>>
+-- !query output
+[2020-01-01 00:00:00]
+
+
+-- !query
+SELECT map('k', TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789') :: map<string,
date>
+-- !query schema
+struct<map(k, TIMESTAMP_NTZ '2020-01-01 12:30:15.123456789'):map<string,date>>
+-- !query output
+{"k":2020-01-01}
+
+
+-- !query
+SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ntz(9), string>
+-- !query schema
+struct<map(DATE '2020-01-01', v):map<timestamp_ntz(9),string>>
+-- !query output
+{2020-01-01 00:00:00:"v"}
+
+
+-- !query
+SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ntz(9)>
+-- !query schema
+struct<named_struct(f, DATE '2020-01-01'):struct<f:timestamp_ntz(9)>>
+-- !query output
+{"f":2020-01-01 00:00:00}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
