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 0346c594c44f [SPARK-57660][SQL] Support casting between TIME(p) and
TIMESTAMP_LTZ(q)
0346c594c44f is described below
commit 0346c594c44fea651835dd61fc9caa29f5072e27
Author: Maxim Gekk <[email protected]>
AuthorDate: Wed Jun 24 21:36:04 2026 +0200
[SPARK-57660][SQL] Support casting between TIME(p) and TIMESTAMP_LTZ(q)
### What changes were proposed in this pull request?
This PR adds bidirectional casts between the `TIME(p)` data type (`p` in
`[0, 9]`) and `TIMESTAMP_LTZ(q)` (`q` in `[6, 9]`, where `q=6` is the
microsecond `TimestampType` and `q` in `[7, 9]` is the nanosecond
`TimestampLTZNanosType`).
It is the `TIMESTAMP_LTZ` counterpart of #56677 / SPARK-57618 (`TIME` <->
`TIMESTAMP_NTZ`) and a sub-task of SPARK-56822.
Semantics follow the SQL standard (section 6.13 `<cast specification>`):
- `CAST(TIMESTAMP_LTZ(q) AS TIME(p))` (rule 15.d): the LTZ value is an
absolute instant, so its time-of-day is the local wall-clock time observed in
the session time zone, truncated to precision `p`. Unlike `TIMESTAMP_NTZ ->
TIME`, this direction depends on the session time zone.
- `CAST(TIME(p) AS TIMESTAMP_LTZ(q))` (rule 17.c): the date fields come
from `CURRENT_DATE` and the time fields from the value; the resulting local
date-time is interpreted in the session time zone to produce the instant. Since
`CURRENT_DATE` is stable within a query, the cast is stabilized via the
existing `ComputeCurrentTime` optimizer rule, so it shares the same date
literal as `current_date()`.
Both directions of `TIME` <-> `TIMESTAMP_LTZ` therefore depend on the
session time zone (whereas for `TIMESTAMP_NTZ` only `TIME -> TIMESTAMP_NTZ`
does). Fractional precision handling is pure truncation (floor toward the
precision step; no rounding). Both directions always succeed, so no new
nullability or error condition is introduced.
Implementation notes:
- New rule-table entries in `Cast.canCast` / `Cast.canAnsiCast` for the
four pairs. `canTryCast` inherits these for atomic types.
- All four pairs are marked `needsTimeZone` (both directions read the
session zone).
- Interpreted and codegen paths for both directions.
- `ComputeCurrentTime` scans `CAST` nodes and, applying the new
`Cast.isTimeToTimestampLTZ` predicate on the resolved plan, rewrites `TIME ->
TIMESTAMP_LTZ` into a zone-aware date+time builder (new internal
`MakeTimestampLTZ` / `MakeTimestampLTZNanos`) anchored on the query current
date. As with the NTZ feature, these casts are intentionally *not* tagged with
`CURRENT_LIKE` (inline-table validation treats `CURRENT_LIKE` as safe to
defer). The `Cast` eval/codegen fallback (using `curre [...]
- New helpers: `SparkDateTimeUtils.timestampToNanosOfDay` /
`timestampLTZNanosToNanosOfDay` and `DateTimeUtils.makeTimestampLTZNanos`.
Out of scope: Structured Streaming batch-timestamp parity for `TIME ->
TIMESTAMP_LTZ` (the cast uses the optimizer-instant current date rather than
the micro-batch timestamp).
### Why are the changes needed?
Spark supports `TIME` <-> `TIMESTAMP_NTZ` casts (SPARK-57618) but not
`TIME` <-> `TIMESTAMP_LTZ`. These conversions are required by the SQL standard
and are a common user need (attaching a time-of-day to a timestamp, or
extracting the time-of-day from a timestamp). This is a sub-task of SPARK-56822
(timestamps with nanosecond precision).
### Does this PR introduce _any_ user-facing change?
Yes. Casting between `TIME(p)` and `TIMESTAMP_LTZ(q)` is now allowed
(previously it failed analysis with a cast type-mismatch). Examples:
```sql
-- extract the time-of-day in the session time zone
SELECT CAST(TIMESTAMP'2020-05-17 12:34:56.789012' AS TIME(6));
-- 12:34:56.789012
-- attach the current date, interpreted in the session time zone
SELECT CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9));
-- <current_date> 12:34:56.789012345
```
This is a new feature on an unreleased branch; there is no behavior change
relative to a released version.
### How was this patch tested?
- New unit tests in `CastSuiteBase` (run under ANSI on and off):
allowed-pair / `needsTimeZone` matrix, `isTimeToTimestampLTZ` truth table,
`TIMESTAMP_LTZ(q) -> TIME(p)` values across all precisions (including pre-epoch
and sub-microsecond truncation), interpreted-vs-codegen consistency, and
zone-fixed round trips.
- New tests in `DateExpressionsSuite` for `MakeTimestampLTZ` /
`MakeTimestampLTZNanos` (including canonicalization on precision).
- New test in `ComputeCurrentTimeSuite` asserting the forward cast is
rewritten with a query-stable current-date literal consistent with
`current_date()`.
- New unit tests in `DateTimeUtilsSuite` for `makeTimestampLTZNanos` and
the time-of-day extraction helpers.
- New deterministic cases in `cast.sql` (and the imported
`nonansi/cast.sql`) with regenerated golden files.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Cursor (Claude Opus 4.8)
Closes #56733 from MaxGekk/time-cast-timestamp_ltz.
Authored-by: Maxim Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
---
.../sql/catalyst/util/SparkDateTimeUtils.scala | 20 ++
.../spark/sql/catalyst/expressions/Cast.scala | 89 ++++++-
.../catalyst/expressions/datetimeExpressions.scala | 86 +++++++
.../sql/catalyst/optimizer/finishAnalysis.scala | 30 ++-
.../spark/sql/catalyst/util/DateTimeUtils.scala | 36 +++
.../sql/catalyst/expressions/CastSuiteBase.scala | 152 ++++++++---
.../expressions/DateExpressionsSuite.scala | 85 +++++++
.../optimizer/ComputeCurrentTimeSuite.scala | 26 +-
.../sql/catalyst/util/DateTimeUtilsSuite.scala | 41 +++
.../sql-tests/analyzer-results/cast.sql.out | 247 ++++++++++++++++++
.../analyzer-results/nonansi/cast.sql.out | 247 ++++++++++++++++++
.../src/test/resources/sql-tests/inputs/cast.sql | 49 ++++
.../test/resources/sql-tests/results/cast.sql.out | 283 +++++++++++++++++++++
.../sql-tests/results/nonansi/cast.sql.out | 283 +++++++++++++++++++++
14 files changed, 1627 insertions(+), 47 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 ac93e3c1415a..be6c507f70ac 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
@@ -408,6 +408,26 @@ trait SparkDateTimeUtils {
Math.floorMod(v.epochMicros, MICROS_PER_DAY) * NANOS_PER_MICROS +
v.nanosWithinMicro
}
+ /**
+ * Extracts the time-of-day component (nanoseconds since midnight) from a
`TIMESTAMP_LTZ`
+ * microsecond value. `TIMESTAMP_LTZ` denotes an absolute instant, so its
time-of-day is the
+ * local wall-clock time observed in the session time zone `zoneId`. The
result stays in
+ * `[0, NANOS_PER_DAY)`.
+ */
+ def timestampToNanosOfDay(micros: Long, zoneId: ZoneId): Long = {
+ getLocalDateTime(micros, zoneId).toLocalTime.toNanoOfDay
+ }
+
+ /**
+ * Extracts the time-of-day component (nanoseconds since midnight) from a
nanosecond-precision
+ * `TIMESTAMP_LTZ` value. `TIMESTAMP_LTZ` denotes an absolute instant, so
its time-of-day is the
+ * local wall-clock time observed in the session time zone `zoneId`. The
sub-microsecond digits
+ * carried in `nanosWithinMicro` are preserved, and the result stays in `[0,
NANOS_PER_DAY)`.
+ */
+ def timestampLTZNanosToNanosOfDay(v: TimestampNanosVal, zoneId: ZoneId):
Long = {
+ timestampNanosToInstant(v).atZone(zoneId).toLocalTime.toNanoOfDay
+ }
+
/**
* Converts a local date at the default JVM time zone to the number of days
since 1970-01-01 in
* the hybrid calendar (Julian + Gregorian) by discarding the time part. The
resulted days are
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 406e18ea1f7b..9ae34f94424c 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
@@ -158,13 +158,16 @@ object Cast extends QueryErrorsBase {
case (_: TimeType, _: TimeType) => true
case (_: TimeType, _: IntegralType) => true
- // TIME(p) <-> TIMESTAMP_NTZ(q), q in [6, 9] (precision 6 is the micro
TimestampNTZType,
- // [7, 9] is TimestampNTZNanosType). Restricted to the NTZ family on
purpose: TIMESTAMP_LTZ
- // is not a valid counterpart for these casts.
+ // TIME(p) <-> TIMESTAMP_NTZ(q) / TIMESTAMP_LTZ(q), q in [6, 9] (precision
6 is the micro
+ // TimestampNTZType / TimestampType, [7, 9] is TimestampNTZNanosType /
TimestampLTZNanosType).
case (_: TimeType, TimestampNTZType) => true
case (TimestampNTZType, _: TimeType) => true
case (_: TimeType, _: TimestampNTZNanosType) => true
case (_: TimestampNTZNanosType, _: TimeType) => true
+ case (_: TimeType, TimestampType) => true
+ case (TimestampType, _: TimeType) => true
+ case (_: TimeType, _: TimestampLTZNanosType) => true
+ case (_: TimestampLTZNanosType, _: TimeType) => true
// non-null variants can generate nulls even in ANSI mode
case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
@@ -319,13 +322,16 @@ object Cast extends QueryErrorsBase {
case (_: TimeType, _: TimeType) => true
case (_: TimeType, _: IntegralType) => true
- // TIME(p) <-> TIMESTAMP_NTZ(q), q in [6, 9] (precision 6 is the micro
TimestampNTZType,
- // [7, 9] is TimestampNTZNanosType). Restricted to the NTZ family on
purpose: TIMESTAMP_LTZ
- // is not a valid counterpart for these casts.
+ // TIME(p) <-> TIMESTAMP_NTZ(q) / TIMESTAMP_LTZ(q), q in [6, 9] (precision
6 is the micro
+ // TimestampNTZType / TimestampType, [7, 9] is TimestampNTZNanosType /
TimestampLTZNanosType).
case (_: TimeType, TimestampNTZType) => true
case (TimestampNTZType, _: TimeType) => true
case (_: TimeType, _: TimestampNTZNanosType) => true
case (_: TimestampNTZNanosType, _: TimeType) => true
+ case (_: TimeType, TimestampType) => true
+ case (TimestampType, _: TimeType) => true
+ case (_: TimeType, _: TimestampLTZNanosType) => true
+ case (_: TimestampLTZNanosType, _: TimeType) => true
case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
canCast(fromType, toType) &&
@@ -371,7 +377,9 @@ object Cast extends QueryErrorsBase {
* with it: a conversion needs the session time zone exactly when its
`castTo*` / `castTo*Code`
* path reads `zoneId`. This is principally the string/date casts of the LTZ
timestamp families
* (TIMESTAMP and TIMESTAMP_LTZ(p)), the cross-family TIMESTAMP_LTZ <->
TIMESTAMP_NTZ conversions
- * (micro and nanosecond), and TIME -> TIMESTAMP_NTZ (whose date fields come
from CURRENT_DATE).
+ * (micro and nanosecond), TIME -> TIMESTAMP_NTZ (whose date fields come
from CURRENT_DATE), and
+ * both directions of TIME <-> TIMESTAMP_LTZ (the LTZ value is an absolute
instant, so extracting
+ * its time-of-day and attaching CURRENT_DATE to a TIME both depend on the
session time zone).
*/
def needsTimeZone(from: DataType, to: DataType): Boolean = (from, to) match {
case (VariantType, _) => true
@@ -404,6 +412,14 @@ object Cast extends QueryErrorsBase {
// time-of-day and is intentionally zone-independent, so it is absent here.
case (_: TimeType, TimestampNTZType) => true
case (_: TimeType, _: TimestampNTZNanosType) => true
+ // TIME <-> TIMESTAMP_LTZ depends on the session time zone in both
directions: the LTZ value is
+ // an absolute instant, so its time-of-day is the local wall clock
observed in the session zone,
+ // and TIME -> TIMESTAMP_LTZ attaches CURRENT_DATE (resolved in the
session zone) and converts
+ // the resulting local date-time to an instant in that zone.
+ case (_: TimeType, TimestampType) => true
+ case (TimestampType, _: TimeType) => true
+ case (_: TimeType, _: TimestampLTZNanosType) => true
+ case (_: TimestampLTZNanosType, _: TimeType) => true
case (ArrayType(fromType, _), ArrayType(toType, _)) =>
needsTimeZone(fromType, toType)
case (MapType(fromKey, fromValue, _), MapType(toKey, toValue, _)) =>
needsTimeZone(fromKey, toKey) || needsTimeZone(fromValue, toValue)
@@ -428,6 +444,19 @@ object Cast extends QueryErrorsBase {
case _ => false
}
+ /**
+ * Returns true for a cast from `TIME(p)` to `TIMESTAMP_LTZ(q)` (q in [6,
9]; q=6 is the micro
+ * `TimestampType`, [7, 9] is `TimestampLTZNanosType`). Like the NTZ
counterpart, such casts
+ * derive their date fields from `CURRENT_DATE`, so they are stabilized
within a query by
+ * [[org.apache.spark.sql.catalyst.optimizer.ComputeCurrentTime]], which
scans `CAST` nodes and
+ * uses this predicate on the resolved plan.
+ */
+ def isTimeToTimestampLTZ(from: DataType, to: DataType): Boolean = (from, to)
match {
+ case (_: TimeType, TimestampType) => true
+ case (_: TimeType, _: TimestampLTZNanosType) => true
+ case _ => false
+ }
+
/**
* Returns true iff we can safely up-cast the `from` type to `to` type
without any truncating or
* precision lose or possible runtime failures. For example, long -> int,
string -> int are not
@@ -882,6 +911,14 @@ case class Cast(
} else {
buildCast[Float](_, f => doubleToTimestamp(f.toDouble))
}
+ case _: TimeType =>
+ // Per ANSI, the date fields come from CURRENT_DATE (resolved in the
session time zone), and
+ // the resulting local date-time is converted to an instant in that
zone. In a real query
+ // plan ComputeCurrentTime rewrites this cast with a query-stable date
literal; this eval
+ // path is the fallback for direct expression evaluation and reads the
current date in the
+ // session time zone.
+ buildCast[Long](_, nanos =>
+ DateTimeUtils.makeTimestamp(currentDate(zoneId), nanos, zoneId))
}
private[this] def castToTimestampNTZ(from: DataType): Any => Any = from
match {
@@ -932,6 +969,12 @@ case class Cast(
DateTimeUtils.timestampNTZNanosToLTZNanos(v, zoneId, precision))
case DateType =>
buildCast[Int](_, d => TimestampNanosVal.fromParts(daysToMicros(d,
zoneId), 0.toShort))
+ case _: TimeType =>
+ // See castToTimestamp: the date fields come from CURRENT_DATE in the
session time zone, with
+ // the query-stable rewrite handled by ComputeCurrentTime. The
sub-microsecond digits of the
+ // TIME value are preserved up to the target precision.
+ buildCast[Long](_, nanos =>
+ DateTimeUtils.makeTimestampLTZNanos(currentDate(zoneId), nanos,
precision, zoneId))
}
private[this] def castToTimestampNTZNanos(
@@ -1024,6 +1067,14 @@ case class Cast(
case _: TimestampNTZNanosType =>
buildCast[TimestampNanosVal](_, v =>
DateTimeUtils.truncateTimeToPrecision(
DateTimeUtils.timestampNTZNanosToNanosOfDay(v), to.precision))
+ case TimestampType =>
+ // TIMESTAMP_LTZ is an absolute instant; its time-of-day is the local
wall clock observed in
+ // the session time zone.
+ buildCast[Long](_, micros => DateTimeUtils.truncateTimeToPrecision(
+ DateTimeUtils.timestampToNanosOfDay(micros, zoneId), to.precision))
+ case _: TimestampLTZNanosType =>
+ buildCast[TimestampNanosVal](_, v =>
DateTimeUtils.truncateTimeToPrecision(
+ DateTimeUtils.timestampLTZNanosToNanosOfDay(v, zoneId), to.precision))
// Unreachable for valid casts: `canCast(_, TimeType)` only allows the
source types handled
// above (and NullType is short-circuited in castInternal). Fail fast to
keep the interpreted
// and codegen (castToTimeCode) paths consistent if a future canCast arm
is added without a
@@ -1761,6 +1812,20 @@ case class Cast(
$evPrim = $dateTimeUtilsCls.truncateTimeToPrecision(
$dateTimeUtilsCls.timestampNTZNanosToNanosOfDay($c),
${to.precision});
"""
+ case TimestampType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, _) =>
+ code"""
+ $evPrim = $dateTimeUtilsCls.truncateTimeToPrecision(
+ $dateTimeUtilsCls.timestampToNanosOfDay($c, $zid),
${to.precision});
+ """
+ case _: TimestampLTZNanosType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, _) =>
+ code"""
+ $evPrim = $dateTimeUtilsCls.truncateTimeToPrecision(
+ $dateTimeUtilsCls.timestampLTZNanosToNanosOfDay($c, $zid),
${to.precision});
+ """
// Unreachable for valid casts (see castToTime). Fail fast at codegen
time instead of
// silently emitting a null, matching the interpreted path.
case _ =>
@@ -1967,6 +2032,11 @@ case class Cast(
}
"""
}
+ case _: TimeType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.makeTimestamp(" +
+ code"$dateTimeUtilsCls.currentDate($zid), $c, $zid);"
}
private[this] def castToTimestampNTZCode(
@@ -2054,6 +2124,11 @@ case class Cast(
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts(" +
code"$dateTimeUtilsCls.daysToMicros($c, $zid), (short) 0);"
+ case _: TimeType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.makeTimestampLTZNanos(" +
+ code"$dateTimeUtilsCls.currentDate($zid), $c, $precision, $zid);"
}
private[this] def castToTimestampNTZNanosCode(
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
index 3dfe12cc6108..667f79c37288 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
@@ -3066,6 +3066,92 @@ case class MakeTimestampNTZNanos(left: Expression,
right: Expression, precision:
}
}
+/**
+ * Creates a `TIMESTAMP_LTZ` (micro `TimestampType`) from a date and a local
time interpreted in the
+ * session time zone. This is the LTZ counterpart of [[MakeTimestampNTZ]];
because the result is an
+ * absolute instant it is time-zone aware. It is an internal expression used by
+ * [[org.apache.spark.sql.catalyst.optimizer.ComputeCurrentTime]] to rewrite
+ * `CAST(time AS TIMESTAMP_LTZ)` with a query-stable current date; it is not
registered as a SQL
+ * function.
+ */
+case class MakeTimestampLTZ(
+ left: Expression,
+ right: Expression,
+ timeZoneId: Option[String] = None)
+ extends BinaryExpression
+ with RuntimeReplaceable
+ with ExpectsInputTypes
+ with TimeZoneAwareExpression {
+
+ override lazy val replacement: Expression = StaticInvoke(
+ classOf[DateTimeUtils.type],
+ TimestampType,
+ "makeTimestamp",
+ Seq(left, right, Literal.create(zoneId.getId, StringType)),
+ Seq(left.dataType, right.dataType, StringType)
+ )
+
+ override def inputTypes: Seq[AbstractDataType] = Seq(DateType, AnyTimeType)
+
+ override def prettyName: String = "make_timestamp_ltz"
+
+ // TimeZoneAwareExpression's `final override val nodePatterns` wins in
linearization and would
+ // otherwise drop RUNTIME_REPLACEABLE; re-add it via this hook, mirroring
the other
+ // RuntimeReplaceable + TimeZoneAwareExpression siblings (e.g. ParseToDate,
ParseToTimestamp).
+ override def nodePatternsInternal(): Seq[TreePattern] =
Seq(RUNTIME_REPLACEABLE)
+
+ override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
+ copy(timeZoneId = Option(timeZoneId))
+
+ override protected def withNewChildrenInternal(
+ newLeft: Expression, newRight: Expression): Expression = {
+ copy(left = newLeft, right = newRight)
+ }
+}
+
+/**
+ * Creates a nanosecond-precision `TIMESTAMP_LTZ(precision)` (precision in [7,
9]) from a date and a
+ * local time interpreted in the session time zone, preserving the time's
sub-microsecond digits up
+ * to `precision`. This is the nanosecond, time-zone aware counterpart of
[[MakeTimestampLTZ]]. It
+ * is an internal expression used by
[[org.apache.spark.sql.catalyst.optimizer.ComputeCurrentTime]]
+ * to rewrite `CAST(time AS TIMESTAMP_LTZ(precision))` with a query-stable
current date; it is not
+ * registered as a SQL function.
+ */
+case class MakeTimestampLTZNanos(
+ left: Expression,
+ right: Expression,
+ precision: Int,
+ timeZoneId: Option[String] = None)
+ extends BinaryExpression
+ with RuntimeReplaceable
+ with ExpectsInputTypes
+ with TimeZoneAwareExpression {
+
+ override lazy val replacement: Expression = StaticInvoke(
+ classOf[DateTimeUtils.type],
+ TimestampLTZNanosType(precision),
+ "makeTimestampLTZNanos",
+ Seq(left, right, Literal(precision), Literal.create(zoneId.getId,
StringType)),
+ Seq(left.dataType, right.dataType, IntegerType, StringType)
+ )
+
+ override def inputTypes: Seq[AbstractDataType] = Seq(DateType, AnyTimeType)
+
+ override def prettyName: String = "make_timestamp_ltz_nanos"
+
+ // See MakeTimestampLTZ: re-add RUNTIME_REPLACEABLE that
TimeZoneAwareExpression's final
+ // nodePatterns would otherwise drop in linearization.
+ override def nodePatternsInternal(): Seq[TreePattern] =
Seq(RUNTIME_REPLACEABLE)
+
+ override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
+ copy(timeZoneId = Option(timeZoneId))
+
+ override protected def withNewChildrenInternal(
+ newLeft: Expression, newRight: Expression): Expression = {
+ copy(left = newLeft, right = newRight)
+ }
+}
+
// scalastyle:off line.size.limit
@ExpressionDescription(
usage = """
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/finishAnalysis.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/finishAnalysis.scala
index 74182f781890..437b67d0855d 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/finishAnalysis.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/finishAnalysis.scala
@@ -120,13 +120,14 @@ object ComputeCurrentTime extends Rule[LogicalPlan] {
val currentDates = collection.mutable.HashMap.empty[ZoneId, Literal]
val localTimestamps = collection.mutable.HashMap.empty[ZoneId, Literal]
- // The CAST bit is included so this rule can find TIME -> TIMESTAMP_NTZ
casts (which depend on
- // CURRENT_DATE) and stabilize them below. CAST is a broad pattern, so
this widens the rule's
- // traversal to most plans; the precise `Cast.isTimeToTimestampNTZ` guard
keeps the rewrite
+ // The CAST bit is included so this rule can find TIME -> TIMESTAMP_NTZ
and TIME ->
+ // TIMESTAMP_LTZ casts (which derive their date fields from CURRENT_DATE)
and stabilize them
+ // below. CAST is a broad pattern, so this widens the rule's traversal to
most plans; the
+ // precise `Cast.isTimeToTimestampNTZ` / `Cast.isTimeToTimestampLTZ`
guards keep the rewrite
// scoped. We intentionally do not tag these casts with CURRENT_LIKE
instead: inline-table
// validation treats CURRENT_LIKE as safe to defer, so tagging would let
unrelated non-foldable
- // NTZ-target casts (e.g. CAST(rand() AS TIMESTAMP_NTZ)) bypass that
validation (see SPARK-57618
- // and ResolveInlineTablesSuite).
+ // NTZ/LTZ-target casts (e.g. CAST(rand() AS TIMESTAMP_NTZ)) bypass that
validation (see
+ // SPARK-57618 and ResolveInlineTablesSuite).
def transformCondition(treePatternbits: TreePatternBits): Boolean = {
treePatternbits.containsPattern(CURRENT_LIKE) ||
treePatternbits.containsPattern(CAST)
}
@@ -160,6 +161,25 @@ object ComputeCurrentTime extends Rule[LogicalPlan] {
throw SparkException.internalError(
s"Unexpected target type in TIME -> TIMESTAMP_NTZ rewrite:
$other")
}
+ // CAST(time AS TIMESTAMP_LTZ(q)) likewise fills the date fields
from CURRENT_DATE.
+ // Rewrite it to a zone-aware date+time builder anchored on the same
query-stable current
+ // date literal, so all references agree within the query.
+ case c: Cast if Cast.isTimeToTimestampLTZ(c.child.dataType,
c.dataType) =>
+ val dateLit = currentDates.getOrElseUpdate(c.zoneId, {
+ Literal.create(
+ DateTimeUtils.microsToDays(currentTimestampMicros, c.zoneId),
DateType)
+ })
+ c.dataType match {
+ case l: TimestampLTZNanosType =>
+ MakeTimestampLTZNanos(dateLit, c.child, l.precision,
c.timeZoneId).replacement
+ case _: TimestampType =>
+ MakeTimestampLTZ(dateLit, c.child, c.timeZoneId).replacement
+ case other =>
+ // Unreachable: the outer guard `Cast.isTimeToTimestampLTZ`
only matches the micro
+ // TimestampType and the nanosecond TimestampLTZNanosType
targets.
+ throw SparkException.internalError(
+ s"Unexpected target type in TIME -> TIMESTAMP_LTZ rewrite:
$other")
+ }
case currentTimeType : CurrentTime =>
val truncatedTime = truncateTimeToPrecision(currentTimeOfDayNanos,
currentTimeType.precision)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
index 48ca74c29f25..481f99709858 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
@@ -1143,6 +1143,42 @@ object DateTimeUtils extends SparkDateTimeUtils {
makeTimestamp(days, nanos, zoneId)
}
+ /**
+ * Makes a nanosecond-precision `TIMESTAMP_LTZ(precision)` (precision in [7,
9]) from a date and
+ * a local time interpreted in the time zone `zoneId`, preserving the time's
sub-microsecond
+ * digits up to `precision`. This is the nanosecond counterpart of
+ * [[makeTimestamp(days:Int,nanos:Long,zoneId:java\.time\.ZoneId)*]].
+ *
+ * @param days The number of days since the epoch 1970-01-01.
+ * Negative numbers represent earlier days.
+ * @param nanos The number of nanoseconds within the day since midnight.
+ * @param precision The fractional-second precision of the target
`TIMESTAMP_LTZ(precision)`.
+ * @param zoneId The time zone ID at which the operation is performed.
+ * @return The composite `(epochMicros, nanosWithinMicro)` pair since the
epoch
+ * 1970-01-01 00:00:00Z.
+ */
+ def makeTimestampLTZNanos(
+ days: Int,
+ nanos: Long,
+ precision: Int,
+ zoneId: ZoneId): TimestampNanosVal = {
+ val ldt = LocalDateTime.of(daysToLocalDate(days), nanosToLocalTime(nanos))
+ instantToTimestampNanos(ldt.atZone(zoneId).toInstant, precision)
+ }
+
+ /**
+ * Makes a nanosecond-precision `TIMESTAMP_LTZ(precision)` from a date and a
local time with a
+ * time zone string. Used by the `CAST(time AS TIMESTAMP_LTZ(precision))`
rewrite, which embeds
+ * the resolved session time zone as a string literal.
+ */
+ def makeTimestampLTZNanos(
+ days: Int,
+ nanos: Long,
+ precision: Int,
+ timezone: UTF8String): TimestampNanosVal = {
+ makeTimestampLTZNanos(days, nanos, precision, getZoneId(timezone.toString))
+ }
+
/**
* Adds a day-time interval to a time.
*
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 73b7c872fc13..8cc38996d3f8 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
@@ -2504,50 +2504,40 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
}
}
- test("cast between TIME and TIMESTAMP_NTZ is allowed only for the NTZ
family") {
+ test("cast between TIME and TIMESTAMP_NTZ / TIMESTAMP_LTZ is allowed") {
for (p <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION) {
- assert(Cast.canCast(TimeType(p), TimestampNTZType))
- assert(Cast.canCast(TimestampNTZType, TimeType(p)))
- assert(Cast.canAnsiCast(TimeType(p), TimestampNTZType))
- assert(Cast.canAnsiCast(TimestampNTZType, TimeType(p)))
// try_cast inherits the allowed pairs (canTryCast delegates to
canAnsiCast for atomic types).
// These casts never fail, so try_cast behaves exactly like cast.
- assert(Cast.canTryCast(TimeType(p), TimestampNTZType))
- assert(Cast.canTryCast(TimestampNTZType, TimeType(p)))
- // TIMESTAMP_LTZ (the micro TimestampType) is not a valid counterpart.
- assert(!Cast.canCast(TimeType(p), TimestampType))
- assert(!Cast.canCast(TimestampType, TimeType(p)))
- assert(!Cast.canAnsiCast(TimeType(p), TimestampType))
- assert(!Cast.canAnsiCast(TimestampType, TimeType(p)))
- assert(!Cast.canTryCast(TimeType(p), TimestampType))
- assert(!Cast.canTryCast(TimestampType, TimeType(p)))
- // The disallowed pairs are rejected at analysis, not merely by the
canCast predicate. The
- // suite `cast` helper applies the active evalMode, so canAnsiCast /
canCast is exercised
- // under ANSI on / off respectively.
- assert(cast(Literal.create(0L, TimeType(p)),
TimestampType).checkInputDataTypes().isFailure)
- assert(cast(Literal.create(0L, TimestampType),
TimeType(p)).checkInputDataTypes().isFailure)
+ Seq(TimestampNTZType, TimestampType).foreach { micro =>
+ assert(Cast.canCast(TimeType(p), micro))
+ assert(Cast.canCast(micro, TimeType(p)))
+ assert(Cast.canAnsiCast(TimeType(p), micro))
+ assert(Cast.canAnsiCast(micro, TimeType(p)))
+ assert(Cast.canTryCast(TimeType(p), micro))
+ assert(Cast.canTryCast(micro, TimeType(p)))
+ }
foreachNanosPrecision { q =>
- assert(Cast.canCast(TimeType(p), TimestampNTZNanosType(q)))
- assert(Cast.canCast(TimestampNTZNanosType(q), TimeType(p)))
- assert(Cast.canAnsiCast(TimeType(p), TimestampNTZNanosType(q)))
- assert(Cast.canAnsiCast(TimestampNTZNanosType(q), TimeType(p)))
- assert(Cast.canTryCast(TimeType(p), TimestampNTZNanosType(q)))
- assert(Cast.canTryCast(TimestampNTZNanosType(q), TimeType(p)))
- // TIMESTAMP_LTZ nanos is not a valid counterpart either.
- assert(!Cast.canCast(TimeType(p), TimestampLTZNanosType(q)))
- assert(!Cast.canCast(TimestampLTZNanosType(q), TimeType(p)))
- assert(!Cast.canAnsiCast(TimeType(p), TimestampLTZNanosType(q)))
- assert(!Cast.canAnsiCast(TimestampLTZNanosType(q), TimeType(p)))
- assert(!Cast.canTryCast(TimeType(p), TimestampLTZNanosType(q)))
- assert(!Cast.canTryCast(TimestampLTZNanosType(q), TimeType(p)))
+ Seq(TimestampNTZNanosType(q), TimestampLTZNanosType(q)).foreach {
nanos =>
+ assert(Cast.canCast(TimeType(p), nanos))
+ assert(Cast.canCast(nanos, TimeType(p)))
+ assert(Cast.canAnsiCast(TimeType(p), nanos))
+ assert(Cast.canAnsiCast(nanos, TimeType(p)))
+ assert(Cast.canTryCast(TimeType(p), nanos))
+ assert(Cast.canTryCast(nanos, TimeType(p)))
+ }
}
}
- // Only TIME -> TIMESTAMP_NTZ depends on the session time zone
(CURRENT_DATE); the reverse
- // direction extracts the UTC wall-clock time-of-day and is
zone-independent.
+ // TIME -> TIMESTAMP_NTZ depends on the session time zone (CURRENT_DATE);
the reverse direction
+ // extracts the UTC wall-clock time-of-day and is zone-independent. Both
directions of
+ // TIME <-> TIMESTAMP_LTZ depend on the session zone (the LTZ value is an
absolute instant).
assert(Cast.needsTimeZone(TimeType(0), TimestampNTZType))
assert(Cast.needsTimeZone(TimeType(9), TimestampNTZNanosType(9)))
assert(!Cast.needsTimeZone(TimestampNTZType, TimeType(0)))
assert(!Cast.needsTimeZone(TimestampNTZNanosType(9), TimeType(9)))
+ assert(Cast.needsTimeZone(TimeType(0), TimestampType))
+ assert(Cast.needsTimeZone(TimestampType, TimeType(0)))
+ assert(Cast.needsTimeZone(TimeType(9), TimestampLTZNanosType(9)))
+ assert(Cast.needsTimeZone(TimestampLTZNanosType(9), TimeType(9)))
}
test("isTimeToTimestampNTZ identifies only TIME -> TIMESTAMP_NTZ") {
@@ -2570,6 +2560,28 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
assert(!Cast.isTimeToTimestampNTZ(TimestampNTZType, TimestampNTZType))
}
+ test("isTimeToTimestampLTZ identifies only TIME -> TIMESTAMP_LTZ") {
+ for (p <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION) {
+ // True only for TIME -> TIMESTAMP_LTZ (the current-date-dependent
direction). q = 6 is the
+ // micro TimestampType.
+ assert(Cast.isTimeToTimestampLTZ(TimeType(p), TimestampType))
+ // The reverse direction is handled by the eval/codegen path, and
TIMESTAMP_NTZ is not a
+ // counterpart of this predicate.
+ assert(!Cast.isTimeToTimestampLTZ(TimestampType, TimeType(p)))
+ assert(!Cast.isTimeToTimestampLTZ(TimeType(p), TimestampNTZType))
+ assert(!Cast.isTimeToTimestampLTZ(TimestampNTZType, TimeType(p)))
+ foreachNanosPrecision { q =>
+ assert(Cast.isTimeToTimestampLTZ(TimeType(p),
TimestampLTZNanosType(q)))
+ assert(!Cast.isTimeToTimestampLTZ(TimestampLTZNanosType(q),
TimeType(p)))
+ assert(!Cast.isTimeToTimestampLTZ(TimeType(p),
TimestampNTZNanosType(q)))
+ assert(!Cast.isTimeToTimestampLTZ(TimestampNTZNanosType(q),
TimeType(p)))
+ }
+ }
+ // Pairs that involve neither a TIME source nor a TIMESTAMP_LTZ target are
false.
+ assert(!Cast.isTimeToTimestampLTZ(DateType, TimestampType))
+ assert(!Cast.isTimeToTimestampLTZ(TimestampType, TimestampType))
+ }
+
test("cast timestamp_ntz to time") {
// Per ANSI rule 15.d the time-of-day fields are extracted and truncated
to the target
// precision; the operation is deterministic and time-zone independent.
@@ -2629,6 +2641,78 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("cast timestamp_ltz to time") {
+ // The LTZ value is an absolute instant; its time-of-day is the local wall
clock observed in the
+ // session time zone, truncated to the target precision. Use a fixed
non-UTC zone to exercise
+ // the zone-dependent path; the instants below avoid DST transitions in
that zone.
+ val zone = LA
+ val zid = Option(zone.getId)
+ val instants = Seq(
+ timestampLTZ(2020, 5, 17, 12, 34, 56, 789012345, zone),
+ timestampLTZ(1969, 12, 31, 23, 59, 59, 123456789, zone),
+ timestampLTZ(1, 1, 1, 0, 0, 0, 1, zone))
+ instants.foreach { inst =>
+ for (p <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION) {
+ // q = 6: the micro TimestampType. Its time-of-day has microsecond
resolution.
+ val micros = DateTimeUtils.instantToMicros(inst)
+ val expectedFromMicros = truncateTimeToPrecision(
+
DateTimeUtils.microsToInstant(micros).atZone(zone).toLocalTime.toNanoOfDay, p)
+ checkEvaluation(
+ cast(Literal.create(micros, TimestampType), TimeType(p), zid),
expectedFromMicros)
+
+ // q in [7, 9]: the nanosecond TimestampLTZNanosType.
+ foreachNanosPrecision { q =>
+ val truncatedInst =
+ inst.minusNanos((inst.getNano -
nanoOfSecTruncator(q)(inst.getNano)).toLong)
+ val v = instantToNanosVal(truncatedInst)
+ val expected =
+
truncateTimeToPrecision(truncatedInst.atZone(zone).toLocalTime.toNanoOfDay, p)
+ checkEvaluation(
+ cast(Literal.create(v, TimestampLTZNanosType(q)), TimeType(p),
zid), expected)
+ }
+ }
+ }
+
+ // Interpreted vs codegen consistency for the zone-dependent reverse
direction.
+ for (p <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION) {
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (child: Expression) => Cast(child, TimeType(p), zid), TimestampType)
+ foreachNanosPrecision { q =>
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (child: Expression) => Cast(child, TimeType(p), zid),
TimestampLTZNanosType(q))
+ }
+ }
+ }
+
+ test("cast time to timestamp_ltz and back") {
+ // TIME -> TIMESTAMP_LTZ takes the date from CURRENT_DATE, which cancels
out on the round trip
+ // back to TIME. Pin the zone to UTC so no DST transition can shift the
wall clock, making these
+ // assertions deterministic regardless of the current date.
+ val times = Seq(
+ localTime(0, 0, 0),
+ localTime(12, 34, 56, 789012),
+ localTime(12, 34, 56, 789012, 345),
+ localTime(23, 59, 59, 999999, 999))
+ times.foreach { nanos =>
+ // Both casts are pinned to UTC: unlike the NTZ reverse direction,
TIMESTAMP_LTZ -> TIME is
+ // zone-dependent, so the outer cast must read the time-of-day in the
same zone the inner cast
+ // used to build the instant.
+ // q = 6 keeps microsecond resolution.
+ checkEvaluation(
+ cast(cast(Literal(nanos, TimeType(9)), TimestampType, UTC_OPT),
TimeType(9), UTC_OPT),
+ truncateTimeToPrecision(nanos, 6))
+ // q in [7, 9] keeps the corresponding sub-microsecond digits.
+ foreachNanosPrecision { q =>
+ checkEvaluation(
+ cast(
+ cast(Literal(nanos, TimeType(9)), TimestampLTZNanosType(q),
UTC_OPT),
+ TimeType(9),
+ UTC_OPT),
+ truncateTimeToPrecision(nanos, q))
+ }
+ }
+ }
+
test("SPARK-52620: cast time to decimal with sufficient precision and
scale") {
// Test various TIME values converted to DecimalType(14, 9), which always
has sufficient
// precision and scale to represent the number of (nano)seconds since
midnight. Note that
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
index 474a8a04aa71..0d7a01fb2953 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
@@ -2625,6 +2625,91 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
MakeTimestampNTZNanos(dateLit(date), timeNanos, 7).canonicalized)
}
+ test("SPARK-57660: make timestamp_ltz from date and time") {
+ Seq(
+ ("2025-06-20", "15:20:30.123456", "America/Los_Angeles", LA),
+ ("2025-06-20", "15:20:30.123456", "+01:00", CET)
+ ).foreach { case (date, time, tz, zoneId) =>
+ // The local date-time is interpreted in the given zone to produce the
instant.
+ checkEvaluation(
+ MakeTimestampLTZ(dateLit(date), timeLit(time), Option(tz)),
+ timestampToMicros(s"${date}T${time}", zoneId))
+ }
+ // Null inputs propagate to a null result.
+ checkEvaluation(
+ MakeTimestampLTZ(Literal(null, DateType), timeLit("15:20:30"), UTC_OPT),
null)
+ checkEvaluation(
+ MakeTimestampLTZ(dateLit("2025-06-20"), Literal(null, TimeType()),
UTC_OPT), null)
+ // The result type is the micro TIMESTAMP_LTZ (TimestampType).
+ assert(MakeTimestampLTZ(dateLit("2025-06-20"), timeLit("15:20:30"),
UTC_OPT).dataType ===
+ TimestampType)
+ }
+
+ test("SPARK-57660: make nanosecond timestamp_ltz from date and time") {
+ val date = "2025-06-20"
+ val timeNanos = Literal.create(localTime(15, 20, 30, 123456, 789),
TimeType(9))
+ Seq("America/Los_Angeles" -> LA, "+01:00" -> CET).foreach { case (tz,
zoneId) =>
+ val micros = timestampToMicros(s"${date}T15:20:30.123456", zoneId)
+ // Precision 9 preserves all sub-microsecond digits; lower precisions
floor them.
+ checkEvaluation(MakeTimestampLTZNanos(dateLit(date), timeNanos, 9,
Option(tz)),
+ TimestampNanosVal.fromParts(micros, 789.toShort))
+ checkEvaluation(MakeTimestampLTZNanos(dateLit(date), timeNanos, 8,
Option(tz)),
+ TimestampNanosVal.fromParts(micros, 780.toShort))
+ checkEvaluation(MakeTimestampLTZNanos(dateLit(date), timeNanos, 7,
Option(tz)),
+ TimestampNanosVal.fromParts(micros, 700.toShort))
+ }
+ // Pre-epoch date.
+ val preEpochMicros = timestampToMicros("1969-12-31T23:59:59.123456", UTC)
+ checkEvaluation(
+ MakeTimestampLTZNanos(dateLit("1969-12-31"),
+ Literal.create(localTime(23, 59, 59, 123456, 789), TimeType(9)), 9,
UTC_OPT),
+ TimestampNanosVal.fromParts(preEpochMicros, 789.toShort))
+ // Null inputs propagate to a null result.
+ checkEvaluation(MakeTimestampLTZNanos(Literal(null, DateType), timeNanos,
9, UTC_OPT), null)
+ checkEvaluation(
+ MakeTimestampLTZNanos(dateLit(date), Literal(null, TimeType(9)), 9,
UTC_OPT), null)
+ // The result type carries the requested nanosecond precision.
+ assert(MakeTimestampLTZNanos(dateLit(date), timeNanos, 7,
UTC_OPT).dataType ===
+ TimestampLTZNanosType(7))
+ // The precision participates in equality and canonicalization: builders
that differ only in
+ // precision are distinct, so two casts to different TIMESTAMP_LTZ(q) are
never conflated.
+ assert(MakeTimestampLTZNanos(dateLit(date), timeNanos, 9, UTC_OPT) !=
+ MakeTimestampLTZNanos(dateLit(date), timeNanos, 7, UTC_OPT))
+ assert(MakeTimestampLTZNanos(dateLit(date), timeNanos, 9,
UTC_OPT).canonicalized ==
+ MakeTimestampLTZNanos(dateLit(date), timeNanos, 9,
UTC_OPT).canonicalized)
+ assert(MakeTimestampLTZNanos(dateLit(date), timeNanos, 9,
UTC_OPT).canonicalized !=
+ MakeTimestampLTZNanos(dateLit(date), timeNanos, 7,
UTC_OPT).canonicalized)
+ }
+
+ test("SPARK-57660: make timestamp_ltz across DST transitions") {
+ // This is the zone-conversion branch of the TIME -> TIMESTAMP_LTZ cast
(ComputeCurrentTime
+ // rewrites the cast into these builders). The Cast itself can't be
DST-tested deterministically
+ // because it takes the date from CURRENT_DATE, so the gap/overlap
behavior is asserted here on
+ // the builders with explicit transition dates.
+ val tz = "America/Los_Angeles"
+ // Spring-forward gap: 2020-03-08 02:30 does not exist in LA; java.time
shifts it forward to
+ // 03:30 PDT. The builder must resolve to that instant.
+ val gapInstant = LocalDateTime.of(2020, 3, 8, 3, 30,
0).atZone(LA).toInstant
+ checkEvaluation(
+ MakeTimestampLTZ(dateLit("2020-03-08"), timeLit("02:30:00"), Option(tz)),
+ DateTimeUtils.instantToMicros(gapInstant))
+ // Sub-microsecond digits survive the gap shift.
+ val gapNanoInstant = LocalDateTime.of(2020, 3, 8, 3, 30, 0,
789012000).atZone(LA).toInstant
+ checkEvaluation(
+ MakeTimestampLTZNanos(
+ dateLit("2020-03-08"),
+ Literal.create(localTime(2, 30, 0, 789012, 345), TimeType(9)), 9,
Option(tz)),
+
TimestampNanosVal.fromParts(DateTimeUtils.instantToMicros(gapNanoInstant),
345.toShort))
+ // Fall-back overlap: 2020-11-01 01:30 occurs twice in LA; java.time picks
the earlier offset
+ // (PDT, UTC-7). Pin the expected instant to that explicit offset so the
assertion does not just
+ // mirror the builder's own atZone resolution.
+ val overlapInstant =
+ LocalDateTime.of(2020, 11, 1, 1, 30,
0).atOffset(java.time.ZoneOffset.ofHours(-7)).toInstant
+ checkEvaluation(
+ MakeTimestampLTZ(dateLit("2020-11-01"), timeLit("01:30:00"), Option(tz)),
+ DateTimeUtils.instantToMicros(overlapInstant))
+ }
+
test("SPARK-53113: try to make timestamp from date, time, and timezone") {
Seq(
("2023-10-01", "12:34:56.123456", "America/Los_Angeles", LA),
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/ComputeCurrentTimeSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/ComputeCurrentTimeSuite.scala
index 34b38cbaed8f..a94bb6f6c5c1 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/ComputeCurrentTimeSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/ComputeCurrentTimeSuite.scala
@@ -30,7 +30,7 @@ import org.apache.spark.sql.catalyst.plans.logical.{Filter,
LocalRelation, Logic
import org.apache.spark.sql.catalyst.rules.RuleExecutor
import org.apache.spark.sql.catalyst.util.DateTimeUtils
import org.apache.spark.sql.internal.SQLConf
-import org.apache.spark.sql.types.{DateType, IntegerType,
TimestampNTZNanosType, TimestampNTZType, TimeType}
+import org.apache.spark.sql.types.{DateType, IntegerType,
TimestampLTZNanosType, TimestampNTZNanosType, TimestampNTZType, TimestampType,
TimeType}
import org.apache.spark.unsafe.types.UTF8String
class ComputeCurrentTimeSuite extends PlanTest {
@@ -297,6 +297,30 @@ class ComputeCurrentTimeSuite extends PlanTest {
assert(remainingCasts.isEmpty)
}
+ test("CAST(time AS TIMESTAMP_LTZ) is stabilized with the query current
date") {
+ val timeLit = Literal(0L, TimeType(6))
+ val in = Project(Seq(
+ Alias(Cast(timeLit, TimestampType), "a")(),
+ Alias(Cast(timeLit, TimestampLTZNanosType(9)), "b")(),
+ Alias(CurrentDate(), "c")()), LocalRelation())
+
+ val min = DateTimeUtils.currentDate(ZoneId.systemDefault())
+ val plan = Optimize.execute(in.analyze).asInstanceOf[Project]
+ val max = DateTimeUtils.currentDate(ZoneId.systemDefault())
+
+ // The two casts and current_date() must all be anchored to the same
current-date literal.
+ val dateLits = dateLiterals(plan)
+ assert(dateLits.size == 3)
+ assert(dateLits.toSet.size == 1)
+ assert(dateLits.forall(d => d >= min && d <= max))
+
+ // The TIME -> TIMESTAMP_LTZ casts must be rewritten away (replaced by a
date+time builder).
+ val remainingCasts = plan.flatMap(_.expressions.flatMap(_.collect {
+ case c: Cast if Cast.isTimeToTimestampLTZ(c.child.dataType, c.dataType)
=> c
+ }))
+ assert(remainingCasts.isEmpty)
+ }
+
private def dateLiterals(plan: LogicalPlan):
scala.collection.mutable.ArrayBuffer[Int] = {
val buf = new scala.collection.mutable.ArrayBuffer[Int]
plan.transformWithSubqueries { case subQuery =>
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
index e60ebf118207..6db96d8d366a 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
@@ -1673,6 +1673,47 @@ class DateTimeUtilsSuite extends SparkFunSuite with
Matchers with SQLHelper {
localTime(23, 59, 59, 123456, 789))
}
+ test("makeTimestampLTZNanos") {
+ // At UTC the local date-time coincides with the instant, so the result
matches the NTZ builder.
+ val nanos = localTime(23, 59, 59, 999999, 999)
+ val microsOfDay = localTime(23, 59, 59, 999999) / NANOS_PER_MICROS
+ assert(makeTimestampLTZNanos(0, nanos, 9, ZoneOffset.UTC) ===
+ TimestampNanosVal.fromParts(microsOfDay, 999.toShort))
+ assert(makeTimestampLTZNanos(0, nanos, 8, ZoneOffset.UTC) ===
+ TimestampNanosVal.fromParts(microsOfDay, 990.toShort))
+ assert(makeTimestampLTZNanos(0, nanos, 7, ZoneOffset.UTC) ===
+ TimestampNanosVal.fromParts(microsOfDay, 900.toShort))
+ // Pre-epoch date at UTC.
+ assert(makeTimestampLTZNanos(
+ days(1969, 12, 31), localTime(23, 59, 59, 123456, 789), 9,
ZoneOffset.UTC) ===
+ TimestampNanosVal.fromParts(date(1969, 12, 31, 23, 59, 59, 123456),
789.toShort))
+ // A non-UTC zone shifts the epoch-micros part by the zone offset;
sub-micro digits survive.
+ val zone = getZoneId("America/Los_Angeles")
+ val inst = LocalDateTime.of(2020, 5, 17, 12, 34, 56,
789012345).atZone(zone).toInstant
+ assert(makeTimestampLTZNanos(days(2020, 5, 17), localTime(12, 34, 56,
789012, 345), 9, zone) ===
+ TimestampNanosVal.fromParts(instantToMicros(inst), 345.toShort))
+ }
+
+ test("timestampLTZ time-of-day extraction") {
+ val zone = getZoneId("America/Los_Angeles")
+ // Micro TimestampLTZ: time-of-day is the local wall clock observed in the
zone.
+ val microInst = LocalDateTime.of(2020, 5, 17, 12, 34, 56,
789012000).atZone(zone).toInstant
+ assert(timestampToNanosOfDay(instantToMicros(microInst), zone) ===
+ localTime(12, 34, 56, 789012))
+ // At UTC the time-of-day equals the value modulo one day, including
pre-epoch values.
+ assert(timestampToNanosOfDay(0, ZoneOffset.UTC) === 0)
+ assert(timestampToNanosOfDay(date(1969, 12, 31, 23, 59, 59, 123456),
ZoneOffset.UTC) ===
+ localTime(23, 59, 59, 123456))
+
+ // Nanosecond TimestampLTZ preserves the sub-microsecond digits.
+ val nanoInst = LocalDateTime.of(2020, 5, 17, 12, 34, 56,
789012345).atZone(zone).toInstant
+ val v = TimestampNanosVal.fromParts(instantToMicros(nanoInst), 345.toShort)
+ assert(timestampLTZNanosToNanosOfDay(v, zone) === localTime(12, 34, 56,
789012, 345))
+ val vPre = TimestampNanosVal.fromParts(date(1969, 12, 31, 23, 59, 59,
123456), 789.toShort)
+ assert(timestampLTZNanosToNanosOfDay(vPre, ZoneOffset.UTC) ===
+ localTime(23, 59, 59, 123456, 789))
+ }
+
test("instant to nanos of day") {
assert(instantToNanosOfDay(Instant.parse("1970-01-01T00:00:01.001002003Z"),
"UTC") ==
1001002003)
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 91788c515310..fd23613e3ad4 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
@@ -1992,3 +1992,250 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'2020-05-17 12:34:56' END"
} ]
}
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(9)) as
time(9)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(7))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(9)) as
time(7)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(7))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(6))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(9)) as
time(6)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(7) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(7)) as
time(9)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT timestamp'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp)) AS typeof(CAST(TIME '12:34:56' AS
TIMESTAMP))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ(9)))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp_ltz(9))) AS typeof(CAST(TIME
'12:34:56' AS TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ) AS DATE) = current_date()
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS DATE) =
current_date()
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012' AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(12:34:56.789012 as timestamp) as time(6)) AS CAST(CAST(TIME
'12:34:56.789012' AS TIMESTAMP) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ltz(9)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ltz(7)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_LTZ(6)) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(cast(12:34:56.789012345 as time(9)) as timestamp) as
time(9)) AS CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS TIMESTAMP)
AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ)
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ(9))
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp_ltz(9)) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp_ltz(9)) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP_LTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(x AS DATE) = current_date() FROM VALUES (CAST(TIME'12:34:56' AS
TIMESTAMP_LTZ)) t(x)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(x AS TIME(6)) FROM VALUES (CAST(TIME'12:34:56.789012' AS
TIMESTAMP_LTZ)) t(x)
+-- !query analysis
+Project [cast(x#x as time(6)) AS x#x]
++- SubqueryAlias t
+ +- LocalRelation [x#x]
+
+
+-- !query
+SELECT CAST(x AS TIME(9)) FROM VALUES (CAST(TIME'12:34:56.789012345' AS
TIMESTAMP_LTZ(9))) t(x)
+-- !query analysis
+Project [cast(x#x as time(9)) AS x#x]
++- SubqueryAlias t
+ +- LocalRelation [x#x]
+
+
+-- !query
+SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17 12:34:56'
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "INCOMPATIBLE_COLUMN_TYPE",
+ "sqlState" : "42825",
+ "messageParameters" : {
+ "columnOrdinalNumber" : "first",
+ "dataType1" : "\"TIMESTAMP\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 69,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17
12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 63,
+ "fragment" : "coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP_LTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_LTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_LTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17 12:34:56'
END
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "[\"TIME(6)\", \"TIMESTAMP\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 81,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17
12:34:56' END"
+ } ]
+}
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 eba639340d26..b68104d9f03a 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
@@ -1839,3 +1839,250 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'2020-05-17 12:34:56' END"
} ]
}
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(9)) as
time(9)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(7))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(9)) as
time(7)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(7))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(6))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(9)) as
time(6)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(7) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ltz(7)) as
time(9)) AS CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT timestamp'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp)) AS typeof(CAST(TIME '12:34:56' AS
TIMESTAMP))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ(9)))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp_ltz(9))) AS typeof(CAST(TIME
'12:34:56' AS TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ) AS DATE) = current_date()
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS DATE) =
current_date()
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012' AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(12:34:56.789012 as timestamp) as time(6)) AS CAST(CAST(TIME
'12:34:56.789012' AS TIMESTAMP) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ltz(9)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ltz(7)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_LTZ(6)) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(cast(12:34:56.789012345 as time(9)) as timestamp) as
time(9)) AS CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS TIMESTAMP)
AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ)
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ(9))
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp_ltz(9)) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP_LTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp_ltz(9)) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP_LTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(x AS DATE) = current_date() FROM VALUES (CAST(TIME'12:34:56' AS
TIMESTAMP_LTZ)) t(x)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(x AS TIME(6)) FROM VALUES (CAST(TIME'12:34:56.789012' AS
TIMESTAMP_LTZ)) t(x)
+-- !query analysis
+Project [cast(x#x as time(6)) AS x#x]
++- SubqueryAlias t
+ +- LocalRelation [x#x]
+
+
+-- !query
+SELECT CAST(x AS TIME(9)) FROM VALUES (CAST(TIME'12:34:56.789012345' AS
TIMESTAMP_LTZ(9))) t(x)
+-- !query analysis
+Project [cast(x#x as time(9)) AS x#x]
++- SubqueryAlias t
+ +- LocalRelation [x#x]
+
+
+-- !query
+SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17 12:34:56'
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "INCOMPATIBLE_COLUMN_TYPE",
+ "sqlState" : "42825",
+ "messageParameters" : {
+ "columnOrdinalNumber" : "first",
+ "dataType1" : "\"TIMESTAMP\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 69,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17
12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 63,
+ "fragment" : "coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP_LTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_LTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_LTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17 12:34:56'
END
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "[\"TIME(6)\", \"TIMESTAMP\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 81,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17
12:34:56' END"
+ } ]
+}
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 e32dc528d1fd..2932658aa561 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/cast.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/cast.sql
@@ -374,3 +374,52 @@ SELECT time'12:34:56' UNION ALL SELECT
timestamp_ntz'2020-05-17 12:34:56';
SELECT coalesce(time'12:34:56', timestamp_ntz'2020-05-17 12:34:56');
SELECT coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(9));
SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp_ntz'2020-05-17
12:34:56' END;
+
+-- SPARK-57660: cast TIMESTAMP_LTZ(q) to TIME(p) extracts the local wall-clock
time-of-day in the
+-- session time zone and truncates to p. The LTZ literal is parsed in the
session zone and the
+-- time-of-day is read back in the same zone, so these values stay
zone-independent.
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(6));
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(3));
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(0));
+-- Nanosecond TIMESTAMP_LTZ(q) preserves the sub-microsecond digits up to p.
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(9));
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(7));
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(6));
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(7) AS
TIME(9));
+-- Double colon syntax.
+SELECT timestamp'2020-05-17 12:34:56.789012' :: TIME(6);
+
+-- SPARK-57660: cast TIME(p) to TIMESTAMP_LTZ(q) takes the date from
CURRENT_DATE and interprets the
+-- result in the session time zone. SQL-layer coverage stays deterministic:
type resolution, the date
+-- anchor equals current_date(), and the value round-trips back to TIME. The
current-date
+-- stabilization is covered by ComputeCurrentTimeSuite and the value semantics
by CastSuite* tests.
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ));
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ(9)));
+-- The date fields come from the query current date.
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ) AS DATE) = current_date();
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS DATE) =
current_date();
+-- Round-trips re-extract the original time-of-day (truncated to the
intermediate precision).
+SELECT CAST(CAST(TIME'12:34:56.789012' AS TIMESTAMP_LTZ) AS TIME(6));
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS TIME(9));
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS TIME(9));
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_LTZ(6)) AS
TIME(9));
+-- Null propagation in both directions.
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ);
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ(9));
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ) AS TIME(6));
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIME(6));
+
+-- SPARK-57660: inside an inline table the TIME -> TIMESTAMP_LTZ cast is
foldable and carries no
+-- CURRENT_LIKE pattern, so it is early-evaluated before ComputeCurrentTime.
Coverage stays
+-- deterministic: the date anchor still equals current_date() and the value
round-trips back to TIME.
+SELECT CAST(x AS DATE) = current_date() FROM VALUES (CAST(TIME'12:34:56' AS
TIMESTAMP_LTZ)) t(x);
+SELECT CAST(x AS TIME(6)) FROM VALUES (CAST(TIME'12:34:56.789012' AS
TIMESTAMP_LTZ)) t(x);
+SELECT CAST(x AS TIME(9)) FROM VALUES (CAST(TIME'12:34:56.789012345' AS
TIMESTAMP_LTZ(9))) t(x);
+
+-- SPARK-57660: TIME and TIMESTAMP_LTZ have no common type, so implicit
coercion is rejected; an
+-- explicit CAST (as above) is required. This guards `findWiderDateTimeType`
against ever widening
+-- TIME, which would silently inject CURRENT_DATE into UNION / coalesce / CASE.
+SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17 12:34:56';
+SELECT coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56');
+SELECT coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9));
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17 12:34:56'
END;
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 223504de7397..31d2b5fdcd02 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
@@ -3162,3 +3162,286 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'2020-05-17 12:34:56' END"
} ]
}
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(3)):time(3)>
+-- !query output
+12:34:56.789
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(0)):time(0)>
+-- !query output
+12:34:56
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(7))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(7)):time(7)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(6))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(7) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(7)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT timestamp'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP)):string>
+-- !query output
+timestamp
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ(9)))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP_LTZ(9))):string>
+-- !query output
+timestamp_ltz(9)
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ) AS DATE) = current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56' AS TIMESTAMP) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS DATE) =
current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012' AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012' AS TIMESTAMP) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_LTZ(6)) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS TIMESTAMP) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ)
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ(9))
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP) AS TIME(6)):time(6)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIME(6)):time(6)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(x AS DATE) = current_date() FROM VALUES (CAST(TIME'12:34:56' AS
TIMESTAMP_LTZ)) t(x)
+-- !query schema
+struct<(CAST(x AS DATE) = current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(x AS TIME(6)) FROM VALUES (CAST(TIME'12:34:56.789012' AS
TIMESTAMP_LTZ)) t(x)
+-- !query schema
+struct<x:time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(x AS TIME(9)) FROM VALUES (CAST(TIME'12:34:56.789012345' AS
TIMESTAMP_LTZ(9))) t(x)
+-- !query schema
+struct<x:time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17 12:34:56'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "INCOMPATIBLE_COLUMN_TYPE",
+ "sqlState" : "42825",
+ "messageParameters" : {
+ "columnOrdinalNumber" : "first",
+ "dataType1" : "\"TIMESTAMP\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 69,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17
12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 63,
+ "fragment" : "coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP_LTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_LTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_LTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17 12:34:56'
END
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "[\"TIME(6)\", \"TIMESTAMP\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 81,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17
12:34:56' END"
+ } ]
+}
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 18ce49c59bbf..3b7477beab01 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
@@ -2162,3 +2162,286 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'2020-05-17 12:34:56' END"
} ]
}
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(3)):time(3)>
+-- !query output
+12:34:56.789
+
+
+-- !query
+SELECT CAST(timestamp'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(0)):time(0)>
+-- !query output
+12:34:56
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(7))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(7)):time(7)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(9) AS
TIME(6))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(9)) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp_ltz'2020-05-17 12:34:56.789012345'::timestamp_ltz(7) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_LTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_LTZ(7)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT timestamp'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query schema
+struct<CAST(TIMESTAMP '2020-05-17 12:34:56.789012' AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP)):string>
+-- !query output
+timestamp
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ(9)))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP_LTZ(9))):string>
+-- !query output
+timestamp_ltz(9)
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_LTZ) AS DATE) = current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56' AS TIMESTAMP) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS DATE) =
current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012' AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012' AS TIMESTAMP) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(9)) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_LTZ(7)) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_LTZ(6)) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS TIMESTAMP) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ)
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_LTZ(9))
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP_LTZ(9)):timestamp_ltz(9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP) AS TIME(6)):time(6)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_LTZ(9)) AS TIME(6)):time(6)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(x AS DATE) = current_date() FROM VALUES (CAST(TIME'12:34:56' AS
TIMESTAMP_LTZ)) t(x)
+-- !query schema
+struct<(CAST(x AS DATE) = current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(x AS TIME(6)) FROM VALUES (CAST(TIME'12:34:56.789012' AS
TIMESTAMP_LTZ)) t(x)
+-- !query schema
+struct<x:time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(x AS TIME(9)) FROM VALUES (CAST(TIME'12:34:56.789012345' AS
TIMESTAMP_LTZ(9))) t(x)
+-- !query schema
+struct<x:time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17 12:34:56'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "INCOMPATIBLE_COLUMN_TYPE",
+ "sqlState" : "42825",
+ "messageParameters" : {
+ "columnOrdinalNumber" : "first",
+ "dataType1" : "\"TIMESTAMP\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 69,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT timestamp'2020-05-17
12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 63,
+ "fragment" : "coalesce(time'12:34:56', timestamp'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP_LTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_LTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_LTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ltz'2020-05-17
12:34:56.789012345'::timestamp_ltz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17 12:34:56'
END
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "[\"TIME(6)\", \"TIMESTAMP\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 81,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE timestamp'2020-05-17
12:34:56' END"
+ } ]
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
