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 405e9846ff9a [SPARK-57618][SQL] Support casting between TIME(p) and
TIMESTAMP_NTZ(q)
405e9846ff9a is described below
commit 405e9846ff9a719a3151eb7824622834e6ea087d
Author: Maxim Gekk <[email protected]>
AuthorDate: Wed Jun 24 10:40:55 2026 +0200
[SPARK-57618][SQL] Support casting between TIME(p) and TIMESTAMP_NTZ(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_NTZ(q)` (`q` in `[6, 9]`, where `q=6` is the
microsecond `TimestampNTZType` and `q` in `[7, 9]` is the nanosecond
`TimestampNTZNanosType`).
Semantics follow the SQL standard (section 6.13 `<cast specification>`):
- `CAST(TIMESTAMP_NTZ(q) AS TIME(p))` (rule 15.d): extracts the
hour/minute/second and fractional fields and truncates to precision `p`.
Deterministic and time-zone independent.
- `CAST(TIME(p) AS TIMESTAMP_NTZ(q))` (rule 17.c): the date fields come
from `CURRENT_DATE` and the time fields from the value. 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()`.
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, restricted to the NTZ family (`TIMESTAMP_LTZ` is intentionally not
a valid counterpart). `canTryCast` inherits these for atomic types.
- Interpreted and codegen paths for both directions.
- `TIME -> TIMESTAMP_NTZ` is marked `needsTimeZone` (its date fields come
from `CURRENT_DATE`). `ComputeCurrentTime` scans `CAST` nodes directly and,
applying the precise `Cast.isTimeToTimestampNTZ` predicate on the resolved
plan, rewrites it into a date+time builder (`makeTimestampNTZ` / new internal
`MakeTimestampNTZNanos`) anchored on the query current date. These casts are
intentionally *not* tagged with the `CURRENT_LIKE` tree pattern: inline-table
validation treats `CURRENT_LIKE [...]
- New helpers: `SparkDateTimeUtils.timestampNTZToNanosOfDay` /
`timestampNTZNanosToNanosOfDay` and `DateTimeUtils.makeTimestampNTZNanos`.
Out of scope: Structured Streaming batch-timestamp parity for `TIME ->
TIMESTAMP_NTZ` (the cast uses the optimizer-instant current date rather than
the micro-batch timestamp).
### Why are the changes needed?
Spark already supports `TIME` casts to/from `STRING`, integral, and
`DECIMAL` types, but there was no way to convert between `TIME` and
`TIMESTAMP_NTZ`. 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_NTZ(q)` is now allowed
(previously it failed analysis with a cast type-mismatch). Examples:
```sql
-- extract the time-of-day
SELECT CAST(TIMESTAMP_NTZ'2020-05-17 12:34:56.789012' AS TIME(6));
-- 12:34:56.789012
-- attach the current date
SELECT CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(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, `TIMESTAMP_NTZ(q) -> TIME(p)` values
across all precisions (including pre-epoch and sub-microsecond truncation),
interpreted-vs-codegen consistency, and date-independent round trips.
- 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 `makeTimestampNTZNanos` 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 #56677 from MaxGekk/time-cast-timestamp_ntz.
Authored-by: Maxim Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
---
.../sql/catalyst/util/SparkDateTimeUtils.scala | 19 ++
.../spark/sql/catalyst/expressions/Cast.scala | 92 ++++++-
.../catalyst/expressions/datetimeExpressions.scala | 31 +++
.../sql/catalyst/optimizer/finishAnalysis.scala | 31 ++-
.../spark/sql/catalyst/util/DateTimeUtils.scala | 17 ++
.../analysis/ResolveInlineTablesSuite.scala | 6 +-
.../sql/catalyst/expressions/CastSuiteBase.scala | 126 +++++++++
.../expressions/DateExpressionsSuite.scala | 32 +++
.../optimizer/ComputeCurrentTimeSuite.scala | 37 ++-
.../sql/catalyst/util/DateTimeUtilsSuite.scala | 37 +++
.../connect/planner/SparkConnectProtoSuite.scala | 17 ++
.../sql-tests/analyzer-results/cast.sql.out | 258 ++++++++++++++++++
.../analyzer-results/nonansi/cast.sql.out | 258 ++++++++++++++++++
.../src/test/resources/sql-tests/inputs/cast.sql | 50 ++++
.../test/resources/sql-tests/results/cast.sql.out | 291 +++++++++++++++++++++
.../sql-tests/results/nonansi/cast.sql.out | 291 +++++++++++++++++++++
16 files changed, 1584 insertions(+), 9 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 5abc80e7c6a1..ac93e3c1415a 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
@@ -389,6 +389,25 @@ trait SparkDateTimeUtils {
*/
def nanosToLocalTime(nanos: Long): LocalTime = LocalTime.ofNanoOfDay(nanos)
+ /**
+ * Extracts the time-of-day component (nanoseconds since midnight) from a
`TIMESTAMP_NTZ`
+ * microsecond value. `TIMESTAMP_NTZ` is a UTC wall-clock value, so its
time-of-day is the value
+ * taken modulo one day. `floorMod` keeps the result in `[0, NANOS_PER_DAY)`
even for pre-epoch
+ * (negative) timestamps.
+ */
+ def timestampNTZToNanosOfDay(micros: Long): Long = {
+ Math.floorMod(micros, MICROS_PER_DAY) * NANOS_PER_MICROS
+ }
+
+ /**
+ * Extracts the time-of-day component (nanoseconds since midnight) from a
nanosecond-precision
+ * `TIMESTAMP_NTZ` value, preserving the sub-microsecond digits carried in
`nanosWithinMicro`.
+ * Since `nanosWithinMicro` is in `[0, 999]`, the result stays in `[0,
NANOS_PER_DAY)`.
+ */
+ def timestampNTZNanosToNanosOfDay(v: TimestampNanosVal): Long = {
+ Math.floorMod(v.epochMicros, MICROS_PER_DAY) * NANOS_PER_MICROS +
v.nanosWithinMicro
+ }
+
/**
* 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 d12f46ecee9f..406e18ea1f7b 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,6 +158,14 @@ 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.
+ case (_: TimeType, TimestampNTZType) => true
+ case (TimestampNTZType, _: TimeType) => true
+ case (_: TimeType, _: TimestampNTZNanosType) => true
+ case (_: TimestampNTZNanosType, _: TimeType) => true
+
// non-null variants can generate nulls even in ANSI mode
case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
canAnsiCast(fromType, toType) && resolvableNullability(fn || (fromType
== VariantType), tn)
@@ -311,6 +319,14 @@ 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.
+ case (_: TimeType, TimestampNTZType) => true
+ case (TimestampNTZType, _: TimeType) => true
+ case (_: TimeType, _: TimestampNTZNanosType) => true
+ case (_: TimestampNTZNanosType, _: TimeType) => true
+
case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
canCast(fromType, toType) &&
resolvableNullability(fn || forceNullable(fromType, toType), tn)
@@ -351,11 +367,11 @@ object Cast extends QueryErrorsBase {
/**
* Return true if we need to use the `timeZone` information casting `from`
type to `to` type.
- * The patterns matched reflect the current implementation in the Cast node.
- * c.f. usage of `timeZone` in:
- * * Cast.castToString
- * * Cast.castToDate
- * * Cast.castToTimestamp
+ * The patterns matched reflect the current implementation in the Cast node
and must stay in sync
+ * 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).
*/
def needsTimeZone(from: DataType, to: DataType): Boolean = (from, to) match {
case (VariantType, _) => true
@@ -383,6 +399,11 @@ object Cast extends QueryErrorsBase {
// mirroring micro TIMESTAMP_NTZ<->DATE which is intentionally absent here.
case (DateType, _: TimestampLTZNanosType) => true
case (_: TimestampLTZNanosType, DateType) => true
+ // TIME -> TIMESTAMP_NTZ fills the date fields from CURRENT_DATE, which is
resolved in the
+ // session time zone. The reverse (TIMESTAMP_NTZ -> TIME) extracts the UTC
wall-clock
+ // time-of-day and is intentionally zone-independent, so it is absent here.
+ case (_: TimeType, TimestampNTZType) => true
+ case (_: TimeType, _: TimestampNTZNanosType) => true
case (ArrayType(fromType, _), ArrayType(toType, _)) =>
needsTimeZone(fromType, toType)
case (MapType(fromKey, fromValue, _), MapType(toKey, toValue, _)) =>
needsTimeZone(fromKey, toKey) || needsTimeZone(fromValue, toValue)
@@ -395,6 +416,18 @@ object Cast extends QueryErrorsBase {
case _ => false
}
+ /**
+ * Returns true for a cast from `TIME(p)` to `TIMESTAMP_NTZ(q)` (q in [6,
9]). 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 isTimeToTimestampNTZ(from: DataType, to: DataType): Boolean = (from, to)
match {
+ case (_: TimeType, _: TimestampNTZType) => true
+ case (_: TimeType, _: TimestampNTZNanosType) => 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
@@ -868,6 +901,12 @@ case class Cast(
buildCast[TimestampNanosVal](_, v => v.epochMicros)
case _: TimestampLTZNanosType =>
buildCast[TimestampNanosVal](_, v => convertTz(v.epochMicros,
ZoneOffset.UTC, zoneId))
+ case _: TimeType =>
+ // Per ANSI, the date fields come from CURRENT_DATE (resolved in the
session time 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.makeTimestampNTZ(currentDate(zoneId), nanos))
}
private[this] def castToTimestampLTZNanos(
@@ -919,6 +958,12 @@ case class Cast(
case DateType =>
buildCast[Int](_, d =>
TimestampNanosVal.fromParts(daysToMicros(d, ZoneOffset.UTC),
0.toShort))
+ case _: TimeType =>
+ // See castToTimestampNTZ: 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.makeTimestampNTZNanos(currentDate(zoneId), nanos,
precision))
}
private[this] def decimalToTimestamp(d: Decimal): Long = {
@@ -973,6 +1018,18 @@ case class Cast(
}
case _: TimeType =>
buildCast[Long](_, nanos => DateTimeUtils.truncateTimeToPrecision(nanos,
to.precision))
+ case TimestampNTZType =>
+ buildCast[Long](_, micros => DateTimeUtils.truncateTimeToPrecision(
+ DateTimeUtils.timestampNTZToNanosOfDay(micros), to.precision))
+ case _: TimestampNTZNanosType =>
+ buildCast[TimestampNanosVal](_, v =>
DateTimeUtils.truncateTimeToPrecision(
+ DateTimeUtils.timestampNTZNanosToNanosOfDay(v), 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
+ // matching converter here.
+ case _ =>
+ throw SparkException.internalError(s"Cannot cast $from to
${to.typeName}.")
}
// IntervalConverter
@@ -1692,8 +1749,22 @@ case class Cast(
code"""
$evPrim = $dateTimeUtilsCls.truncateTimeToPrecision($nanos,
${to.precision});
"""
+ case TimestampNTZType =>
+ (c, evPrim, _) =>
+ code"""
+ $evPrim = $dateTimeUtilsCls.truncateTimeToPrecision(
+ $dateTimeUtilsCls.timestampNTZToNanosOfDay($c), ${to.precision});
+ """
+ case _: TimestampNTZNanosType =>
+ (c, evPrim, _) =>
+ code"""
+ $evPrim = $dateTimeUtilsCls.truncateTimeToPrecision(
+ $dateTimeUtilsCls.timestampNTZNanosToNanosOfDay($c),
${to.precision});
+ """
+ // Unreachable for valid casts (see castToTime). Fail fast at codegen
time instead of
+ // silently emitting a null, matching the interpreted path.
case _ =>
- (_, _, evNull) => code"$evNull = true;"
+ throw SparkException.internalError(s"Cannot cast $from to
${to.typeName}.")
}
}
@@ -1932,6 +2003,10 @@ case class Cast(
val zid = zoneIdValue(ctx)
(c, evPrim, evNull) =>
code"$evPrim = $dateTimeUtilsCls.convertTz($c.epochMicros,
java.time.ZoneOffset.UTC, $zid);"
+ case _: TimeType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim =
$dateTimeUtilsCls.makeTimestampNTZ($dateTimeUtilsCls.currentDate($zid), $c);"
}
private[this] def castToTimestampLTZNanosCode(
@@ -2024,6 +2099,11 @@ case class Cast(
(c, evPrim, evNull) =>
code"$evPrim = TimestampNanosVal.fromParts(" +
code"$dateTimeUtilsCls.daysToMicros($c, java.time.ZoneOffset.UTC),
(short) 0);"
+ case _: TimeType =>
+ val zid = zoneIdValue(ctx)
+ (c, evPrim, evNull) =>
+ code"$evPrim = $dateTimeUtilsCls.makeTimestampNTZNanos(" +
+ code"$dateTimeUtilsCls.currentDate($zid), $c, $precision);"
}
private[this] def castToIntervalCode(from: DataType): CastFunction = from
match {
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 268d86852c3c..3dfe12cc6108 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
@@ -3035,6 +3035,37 @@ case class MakeTimestampNTZ(left: Expression, right:
Expression)
}
}
+/**
+ * Creates a nanosecond-precision `TIMESTAMP_NTZ(precision)` (precision in [7,
9]) from a date and
+ * a local time, preserving the time's sub-microsecond digits up to
`precision`. This is the
+ * nanosecond counterpart of [[MakeTimestampNTZ]]. It is an internal
expression used by
+ * [[org.apache.spark.sql.catalyst.optimizer.ComputeCurrentTime]] to rewrite
+ * `CAST(time AS TIMESTAMP_NTZ(precision))` with a query-stable current date;
it is not registered
+ * as a SQL function.
+ */
+case class MakeTimestampNTZNanos(left: Expression, right: Expression,
precision: Int)
+ extends BinaryExpression
+ with RuntimeReplaceable
+ with ExpectsInputTypes {
+
+ override def replacement: Expression = StaticInvoke(
+ classOf[DateTimeUtils.type],
+ TimestampNTZNanosType(precision),
+ "makeTimestampNTZNanos",
+ Seq(left, right, Literal(precision)),
+ Seq(left.dataType, right.dataType, IntegerType)
+ )
+
+ override def inputTypes: Seq[AbstractDataType] = Seq(DateType, AnyTimeType)
+
+ override def prettyName: String = "make_timestamp_ntz_nanos"
+
+ 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 160c4147dae6..74182f781890 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
@@ -21,6 +21,7 @@ import java.time.{Instant, LocalDateTime, ZoneId}
import scala.util.control.NonFatal
+import org.apache.spark.SparkException
import org.apache.spark.sql.catalyst.{CurrentUserContext, InternalRow}
import org.apache.spark.sql.catalyst.analysis.{CastSupport,
ResolvedInlineTable}
import
org.apache.spark.sql.catalyst.analysis.ResolveInlineTables.prepareForEval
@@ -119,8 +120,15 @@ 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
+ // 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).
def transformCondition(treePatternbits: TreePatternBits): Boolean = {
- treePatternbits.containsPattern(CURRENT_LIKE)
+ treePatternbits.containsPattern(CURRENT_LIKE) ||
treePatternbits.containsPattern(CAST)
}
plan.transformDownWithSubqueriesAndPruning(transformCondition) {
@@ -131,6 +139,27 @@ object ComputeCurrentTime extends Rule[LogicalPlan] {
Literal.create(
DateTimeUtils.microsToDays(currentTimestampMicros, cd.zoneId),
DateType)
})
+ // CAST(time AS TIMESTAMP_NTZ(q)) fills the date fields from
CURRENT_DATE. Rewrite it to
+ // a date+time builder anchored on the same query-stable current
date literal that
+ // current_date() resolves to, so all references agree within the
query. The builder's
+ // `replacement` (a StaticInvoke) is emitted directly because
ReplaceExpressions has
+ // already run earlier in this batch and will not expand a fresh
RuntimeReplaceable.
+ case c: Cast if Cast.isTimeToTimestampNTZ(c.child.dataType,
c.dataType) =>
+ val dateLit = currentDates.getOrElseUpdate(c.zoneId, {
+ Literal.create(
+ DateTimeUtils.microsToDays(currentTimestampMicros, c.zoneId),
DateType)
+ })
+ c.dataType match {
+ case n: TimestampNTZNanosType =>
+ MakeTimestampNTZNanos(dateLit, c.child,
n.precision).replacement
+ case _: TimestampNTZType =>
+ MakeTimestampNTZ(dateLit, c.child).replacement
+ case other =>
+ // Unreachable: the outer guard `Cast.isTimeToTimestampNTZ`
only matches the micro
+ // TimestampNTZType and the nanosecond TimestampNTZNanosType
targets.
+ throw SparkException.internalError(
+ s"Unexpected target type in TIME -> TIMESTAMP_NTZ 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 b37fbf224963..48ca74c29f25 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
@@ -1098,6 +1098,23 @@ object DateTimeUtils extends SparkDateTimeUtils {
localDateTimeToMicros(LocalDateTime.of(daysToLocalDate(days),
nanosToLocalTime(nanos)))
}
+ /**
+ * Makes a nanosecond-precision timestamp without time zone from a date and
a local time,
+ * flooring the combined value to the target `precision` in [7, 9] (see
+ * [[localDateTimeToTimestampNanos]]).
+ *
+ * @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_NTZ(precision)`.
+ * @return The composite `(epochMicros, nanosWithinMicro)` pair since the
epoch
+ * 1970-01-01 00:00:00Z.
+ */
+ def makeTimestampNTZNanos(days: Int, nanos: Long, precision: Int):
TimestampNanosVal = {
+ localDateTimeToTimestampNanos(
+ LocalDateTime.of(daysToLocalDate(days), nanosToLocalTime(nanos)),
precision)
+ }
+
/**
* Makes a timestamp from a date and a local time.
*
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/ResolveInlineTablesSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/ResolveInlineTablesSuite.scala
index db22c9e48a39..50d88e90987c 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/ResolveInlineTablesSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/ResolveInlineTablesSuite.scala
@@ -25,7 +25,7 @@ import
org.apache.spark.sql.catalyst.expressions.aggregate.Count
import org.apache.spark.sql.catalyst.optimizer.{ComputeCurrentTime,
EvalInlineTables}
import org.apache.spark.sql.catalyst.plans.logical.LocalRelation
import org.apache.spark.sql.catalyst.util.EvaluateUnresolvedInlineTable
-import org.apache.spark.sql.types.{LongType, NullType, TimestampType, TimeType}
+import org.apache.spark.sql.types.{LongType, NullType, TimestampNTZType,
TimestampType, TimeType}
/**
* Unit tests for [[ResolveInlineTables]]. Note that there are also test cases
defined in
@@ -49,6 +49,10 @@ class ResolveInlineTablesSuite extends AnalysisTest with
BeforeAndAfter {
EvaluateUnresolvedInlineTable.validateInputEvaluable(
UnresolvedInlineTable(Seq("c1"), Seq(Seq(Rand(1)))))
}
+ intercept[AnalysisException] {
+ EvaluateUnresolvedInlineTable.validateInputEvaluable(
+ UnresolvedInlineTable(Seq("c1"), Seq(Seq(Cast(Rand(1),
TimestampNTZType)))))
+ }
// aggregate should not work
intercept[AnalysisException] {
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 122554a18886..73b7c872fc13 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
@@ -30,6 +30,7 @@ import org.apache.spark.sql.catalyst.expressions.Cast._
import org.apache.spark.sql.catalyst.expressions.codegen.CodegenContext
import org.apache.spark.sql.catalyst.util.DateTimeConstants._
import org.apache.spark.sql.catalyst.util.DateTimeTestUtils._
+import org.apache.spark.sql.catalyst.util.DateTimeUtils
import org.apache.spark.sql.catalyst.util.DateTimeUtils._
import org.apache.spark.sql.catalyst.util.IntervalUtils
import org.apache.spark.sql.catalyst.util.IntervalUtils.microsToDuration
@@ -2503,6 +2504,131 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("cast between TIME and TIMESTAMP_NTZ is allowed only for the NTZ
family") {
+ 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)
+ 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)))
+ }
+ }
+ // 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.
+ 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)))
+ }
+
+ test("isTimeToTimestampNTZ identifies only TIME -> TIMESTAMP_NTZ") {
+ for (p <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION) {
+ // True only for TIME -> TIMESTAMP_NTZ (the current-date-dependent
direction).
+ assert(Cast.isTimeToTimestampNTZ(TimeType(p), TimestampNTZType))
+ // The reverse direction is zone-independent, and TIMESTAMP_LTZ is not a
counterpart.
+ assert(!Cast.isTimeToTimestampNTZ(TimestampNTZType, TimeType(p)))
+ assert(!Cast.isTimeToTimestampNTZ(TimeType(p), TimestampType))
+ assert(!Cast.isTimeToTimestampNTZ(TimestampType, TimeType(p)))
+ foreachNanosPrecision { q =>
+ assert(Cast.isTimeToTimestampNTZ(TimeType(p),
TimestampNTZNanosType(q)))
+ assert(!Cast.isTimeToTimestampNTZ(TimestampNTZNanosType(q),
TimeType(p)))
+ assert(!Cast.isTimeToTimestampNTZ(TimeType(p),
TimestampLTZNanosType(q)))
+ assert(!Cast.isTimeToTimestampNTZ(TimestampLTZNanosType(q),
TimeType(p)))
+ }
+ }
+ // Pairs that involve neither a TIME source nor a TIMESTAMP_NTZ target are
false.
+ assert(!Cast.isTimeToTimestampNTZ(DateType, TimestampNTZType))
+ assert(!Cast.isTimeToTimestampNTZ(TimestampNTZType, TimestampNTZType))
+ }
+
+ 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.
+ val ldts = Seq(
+ LocalDateTime.of(2020, 5, 17, 12, 34, 56, 789012345),
+ LocalDateTime.of(1969, 12, 31, 23, 59, 59, 123456789),
+ LocalDateTime.of(1, 1, 1, 0, 0, 0, 1))
+ ldts.foreach { ldt =>
+ for (p <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION) {
+ // q = 6: the micro TimestampNTZType. Its time-of-day has microsecond
resolution.
+ val micros = DateTimeUtils.localDateTimeToMicros(ldt)
+ val expectedFromMicros =
+
truncateTimeToPrecision(localTimeToNanos(microsToLocalDateTime(micros).toLocalTime),
p)
+ checkEvaluation(
+ cast(Literal.create(micros, TimestampNTZType), TimeType(p)),
expectedFromMicros)
+
+ // q in [7, 9]: the nanosecond TimestampNTZNanosType.
+ foreachNanosPrecision { q =>
+ val truncatedLdt = ldt.withNano(nanoOfSecTruncator(q)(ldt.getNano))
+ val v = localDateTimeToNanosVal(truncatedLdt)
+ val expected =
truncateTimeToPrecision(localTimeToNanos(truncatedLdt.toLocalTime), p)
+ checkEvaluation(cast(Literal.create(v, TimestampNTZNanosType(q)),
TimeType(p)), expected)
+ }
+ }
+ }
+
+ // Interpreted vs codegen consistency (zone-independent reverse direction).
+ for (p <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION) {
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (child: Expression) => Cast(child, TimeType(p)), TimestampNTZType)
+ foreachNanosPrecision { q =>
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (child: Expression) => Cast(child, TimeType(p)),
TimestampNTZNanosType(q))
+ }
+ }
+ }
+
+ test("cast time to timestamp_ntz and back") {
+ // TIME -> TIMESTAMP_NTZ takes the date from CURRENT_DATE, which cancels
out on the round trip
+ // back to TIME, so these assertions are 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 =>
+ // q = 6 keeps microsecond resolution.
+ checkEvaluation(
+ cast(cast(Literal(nanos, TimeType(9)), TimestampNTZType, UTC_OPT),
TimeType(9)),
+ truncateTimeToPrecision(nanos, 6))
+ // q in [7, 9] keeps the corresponding sub-microsecond digits.
+ foreachNanosPrecision { q =>
+ checkEvaluation(
+ cast(cast(Literal(nanos, TimeType(9)), TimestampNTZNanosType(q),
UTC_OPT), TimeType(9)),
+ 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 4a016ee186bd..474a8a04aa71 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
@@ -2593,6 +2593,38 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
null)
}
+ test("SPARK-57618: make nanosecond timestamp_ntz from date and time") {
+ val date = "2025-06-20"
+ val micros = timestampToMicros("2025-06-20T15:20:30.123456", UTC)
+ val timeNanos = Literal.create(localTime(15, 20, 30, 123456, 789),
TimeType(9))
+ // Precision 9 preserves all sub-microsecond digits; lower precisions
floor them.
+ checkEvaluation(MakeTimestampNTZNanos(dateLit(date), timeNanos, 9),
+ TimestampNanosVal.fromParts(micros, 789.toShort))
+ checkEvaluation(MakeTimestampNTZNanos(dateLit(date), timeNanos, 8),
+ TimestampNanosVal.fromParts(micros, 780.toShort))
+ checkEvaluation(MakeTimestampNTZNanos(dateLit(date), timeNanos, 7),
+ TimestampNanosVal.fromParts(micros, 700.toShort))
+ // Pre-epoch date.
+ val preEpochMicros = timestampToMicros("1969-12-31T23:59:59.123456", UTC)
+ checkEvaluation(
+ MakeTimestampNTZNanos(dateLit("1969-12-31"),
+ Literal.create(localTime(23, 59, 59, 123456, 789), TimeType(9)), 9),
+ TimestampNanosVal.fromParts(preEpochMicros, 789.toShort))
+ // Null inputs propagate to a null result.
+ checkEvaluation(MakeTimestampNTZNanos(Literal(null, DateType), timeNanos,
9), null)
+ checkEvaluation(MakeTimestampNTZNanos(dateLit(date), Literal(null,
TimeType(9)), 9), null)
+ // The result type carries the requested nanosecond precision.
+ assert(MakeTimestampNTZNanos(dateLit(date), timeNanos, 7).dataType ===
TimestampNTZNanosType(7))
+ // The precision participates in equality and canonicalization: builders
that differ only in
+ // precision are distinct, so two casts to different TIMESTAMP_NTZ(q) are
never conflated.
+ assert(MakeTimestampNTZNanos(dateLit(date), timeNanos, 9) !=
+ MakeTimestampNTZNanos(dateLit(date), timeNanos, 7))
+ assert(MakeTimestampNTZNanos(dateLit(date), timeNanos, 9).canonicalized ==
+ MakeTimestampNTZNanos(dateLit(date), timeNanos, 9).canonicalized)
+ assert(MakeTimestampNTZNanos(dateLit(date), timeNanos, 9).canonicalized !=
+ MakeTimestampNTZNanos(dateLit(date), timeNanos, 7).canonicalized)
+ }
+
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 c4f71ec62957..34b38cbaed8f 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.IntegerType
+import org.apache.spark.sql.types.{DateType, IntegerType,
TimestampNTZNanosType, TimestampNTZType, TimeType}
import org.apache.spark.unsafe.types.UTF8String
class ComputeCurrentTimeSuite extends PlanTest {
@@ -273,6 +273,41 @@ class ComputeCurrentTimeSuite extends PlanTest {
checkLiterals({ zoneId: String => CurrentDate(Some(zoneId)) },
numUniqueZoneIds)
}
+ test("CAST(time AS TIMESTAMP_NTZ) is stabilized with the query current
date") {
+ val timeLit = Literal(0L, TimeType(6))
+ val in = Project(Seq(
+ Alias(Cast(timeLit, TimestampNTZType), "a")(),
+ Alias(Cast(timeLit, TimestampNTZNanosType(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_NTZ casts must be rewritten away (replaced by a
date+time builder).
+ val remainingCasts = plan.flatMap(_.expressions.flatMap(_.collect {
+ case c: Cast if Cast.isTimeToTimestampNTZ(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 =>
+ subQuery.transformAllExpressions { case lit: Literal if lit.dataType ==
DateType =>
+ buf += lit.value.asInstanceOf[Int]
+ lit
+ }
+ }
+ buf
+ }
+
private def literals[T](plan: LogicalPlan):
scala.collection.mutable.ArrayBuffer[T] = {
val literals = new scala.collection.mutable.ArrayBuffer[T]
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 82617b6b4cf6..e60ebf118207 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
@@ -1636,6 +1636,43 @@ class DateTimeUtilsSuite extends SparkFunSuite with
Matchers with SQLHelper {
assert(msg.contains("Invalid value"))
}
+ test("makeTimestampNTZNanos") {
+ // The combined date + time is floored to the target nanosecond precision
in [7, 9].
+ val nanos = localTime(23, 59, 59, 999999, 999)
+ val microsOfDay = localTime(23, 59, 59, 999999) / NANOS_PER_MICROS
+ assert(makeTimestampNTZNanos(0, nanos, 9) ===
+ TimestampNanosVal.fromParts(microsOfDay, 999.toShort))
+ assert(makeTimestampNTZNanos(0, nanos, 8) ===
+ TimestampNanosVal.fromParts(microsOfDay, 990.toShort))
+ assert(makeTimestampNTZNanos(0, nanos, 7) ===
+ TimestampNanosVal.fromParts(microsOfDay, 900.toShort))
+ // A non-zero date shifts the microsecond component while preserving the
sub-micro digits.
+ assert(makeTimestampNTZNanos(days(2020, 5, 17), localTime(12, 34, 56,
789012, 345), 9) ===
+ TimestampNanosVal.fromParts(date(2020, 5, 17, 12, 34, 56, 789012),
345.toShort))
+ // Pre-epoch date.
+ assert(makeTimestampNTZNanos(days(1969, 12, 31), localTime(23, 59, 59,
123456, 789), 9) ===
+ TimestampNanosVal.fromParts(date(1969, 12, 31, 23, 59, 59, 123456),
789.toShort))
+ }
+
+ test("timestampNTZ time-of-day extraction") {
+ // Micro TimestampNTZ: time-of-day is the value modulo one day.
+ assert(timestampNTZToNanosOfDay(0) === 0)
+ assert(timestampNTZToNanosOfDay(date(2020, 5, 17, 12, 34, 56, 789012)) ===
+ localTime(12, 34, 56, 789012))
+ // Pre-epoch timestamps wrap correctly via floorMod.
+ assert(timestampNTZToNanosOfDay(date(1969, 12, 31, 23, 59, 59, 123456)) ===
+ localTime(23, 59, 59, 123456))
+ assert(timestampNTZToNanosOfDay(-1) === localTime(23, 59, 59, 999999))
+
+ // Nanosecond TimestampNTZ preserves the sub-microsecond digits.
+ assert(timestampNTZNanosToNanosOfDay(
+ TimestampNanosVal.fromParts(date(2020, 5, 17, 12, 34, 56, 789012),
345.toShort)) ===
+ localTime(12, 34, 56, 789012, 345))
+ assert(timestampNTZNanosToNanosOfDay(
+ TimestampNanosVal.fromParts(date(1969, 12, 31, 23, 59, 59, 123456),
789.toShort)) ===
+ 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/connect/server/src/test/scala/org/apache/spark/sql/connect/planner/SparkConnectProtoSuite.scala
b/sql/connect/server/src/test/scala/org/apache/spark/sql/connect/planner/SparkConnectProtoSuite.scala
index aa0345f089fc..1a5189e24304 100644
---
a/sql/connect/server/src/test/scala/org/apache/spark/sql/connect/planner/SparkConnectProtoSuite.scala
+++
b/sql/connect/server/src/test/scala/org/apache/spark/sql/connect/planner/SparkConnectProtoSuite.scala
@@ -986,6 +986,23 @@ class SparkConnectProtoSuite extends PlanTest with
SparkConnectPlanTest {
sparkTestRelation.select(col("id").cast("string")))
}
+ test("SPARK-57618: cast TIMESTAMP_NTZ to TIME") {
+ // The cast logic itself lives in Catalyst (covered by CastSuite*); this
confirms the
+ // TIMESTAMP_NTZ -> TIME pair round-trips through the Connect planner. The
target TIME type is
+ // carried as a type string and parsed server-side, so this does not
depend on TIME being
+ // serializable in the Connect schema proto (only the NTZ column is). The
reverse direction
+ // (TIME -> TIMESTAMP_NTZ) is not exercisable here until the TIME type is
supported by the
+ // Connect DataType proto converter; it is covered by the Catalyst suites.
+ val connectRel =
+ createLocalRelationProto(Seq(AttributeReference("ts",
TimestampNTZType)()), Seq.empty)
+ val sparkRel = spark.createDataFrame(
+ new java.util.ArrayList[Row](),
+ StructType(Seq(StructField("ts", TimestampNTZType))))
+ comparePlans(
+ connectRel.select("ts".protoAttr.cast("time")),
+ sparkRel.select(col("ts").cast("time")))
+ }
+
test("Test colRegex") {
comparePlans(
connectTestRelation.select("id".colRegex),
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 2ad6e040585b..91788c515310 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
@@ -1734,3 +1734,261 @@ SELECT CAST(time '23:59:59.999999999' AS decimal(14, 8))
-- !query analysis
Project [cast(23:59:59.999999999 as decimal(14,8)) AS CAST(TIME
'23:59:59.999999999' AS DECIMAL(14,8))#x]
+- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(6)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(3)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(3))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(0)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(0))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'1969-12-31 23:59:59.123456' AS TIME(6))
+-- !query analysis
+Project [cast(1969-12-31 23:59:59.123456 as time(6)) AS CAST(TIMESTAMP_NTZ
'1969-12-31 23:59:59.123456' AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(9)) as
time(9)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(7))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(9)) as
time(7)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(7))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(6))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(9)) as
time(6)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(7) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(7)) as
time(9)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT timestamp_ntz'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(6)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp_ntz)) AS typeof(CAST(TIME
'12:34:56' AS TIMESTAMP_NTZ))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ(9)))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp_ntz(9))) AS typeof(CAST(TIME
'12:34:56' AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ) AS DATE) = current_date()
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(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_NTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(12:34:56.789012 as timestamp_ntz) as time(6)) AS
CAST(CAST(TIME '12:34:56.789012' AS TIMESTAMP_NTZ) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ntz(9)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(7)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ntz(7)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_NTZ(6)) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(cast(12:34:56.789012345 as time(9)) as timestamp_ntz) as
time(9)) AS CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS
TIMESTAMP_NTZ) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ)
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp_ntz) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ(9))
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp_ntz(9)) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP_NTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP_NTZ) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz(9)) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP_NTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(x AS DATE) = current_date() FROM VALUES (CAST(TIME'12:34:56' AS
TIMESTAMP_NTZ)) 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_NTZ)) 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_NTZ(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_ntz'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_NTZ\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 73,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT
timestamp_ntz'2020-05-17 12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'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_NTZ\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP_NTZ '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 67,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(9))
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP_NTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_NTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_NTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp_ntz'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_NTZ\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP_NTZ
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 85,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'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 c48776a1de31..eba639340d26 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
@@ -1581,3 +1581,261 @@ SELECT CAST(time '23:59:59.999999999' AS decimal(14, 8))
-- !query analysis
Project [cast(23:59:59.999999999 as decimal(14,8)) AS CAST(TIME
'23:59:59.999999999' AS DECIMAL(14,8))#x]
+- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(6)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(3)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(3))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(0)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(0))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'1969-12-31 23:59:59.123456' AS TIME(6))
+-- !query analysis
+Project [cast(1969-12-31 23:59:59.123456 as time(6)) AS CAST(TIMESTAMP_NTZ
'1969-12-31 23:59:59.123456' AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(9)) as
time(9)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(7))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(9)) as
time(7)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(7))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(6))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(9)) as
time(6)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(7) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(2020-05-17 12:34:56.789012345 as timestamp_ntz(7)) as
time(9)) AS CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT timestamp_ntz'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query analysis
+Project [cast(2020-05-17 12:34:56.789012 as time(6)) AS CAST(TIMESTAMP_NTZ
'2020-05-17 12:34:56.789012' AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp_ntz)) AS typeof(CAST(TIME
'12:34:56' AS TIMESTAMP_NTZ))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ(9)))
+-- !query analysis
+Project [typeof(cast(12:34:56 as timestamp_ntz(9))) AS typeof(CAST(TIME
'12:34:56' AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ) AS DATE) = current_date()
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(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_NTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(12:34:56.789012 as timestamp_ntz) as time(6)) AS
CAST(CAST(TIME '12:34:56.789012' AS TIMESTAMP_NTZ) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ntz(9)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(7)) AS TIME(9))
+-- !query analysis
+Project [cast(cast(12:34:56.789012345 as timestamp_ntz(7)) as time(9)) AS
CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(7)) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_NTZ(6)) AS
TIME(9))
+-- !query analysis
+Project [cast(cast(cast(12:34:56.789012345 as time(9)) as timestamp_ntz) as
time(9)) AS CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS
TIMESTAMP_NTZ) AS TIME(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ)
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp_ntz) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP_NTZ)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ(9))
+-- !query analysis
+Project [cast(cast(null as time(6)) as timestamp_ntz(9)) AS CAST(CAST(NULL AS
TIME(6)) AS TIMESTAMP_NTZ(9))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP_NTZ) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS TIME(6))
+-- !query analysis
+Project [cast(cast(null as timestamp_ntz(9)) as time(6)) AS CAST(CAST(NULL AS
TIMESTAMP_NTZ(9)) AS TIME(6))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT CAST(x AS DATE) = current_date() FROM VALUES (CAST(TIME'12:34:56' AS
TIMESTAMP_NTZ)) 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_NTZ)) 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_NTZ(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_ntz'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_NTZ\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 73,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT
timestamp_ntz'2020-05-17 12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'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_NTZ\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP_NTZ '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 67,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(9))
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.DATA_DIFF_TYPES",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "dataType" : "(\"TIME(6)\" or \"TIMESTAMP_NTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_NTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_NTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp_ntz'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_NTZ\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP_NTZ
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 85,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'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 87621d4413d3..e32dc528d1fd 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/cast.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/cast.sql
@@ -324,3 +324,53 @@ SELECT CAST(time '23:59:59.9' AS decimal(6, 0));
SELECT CAST(time '23:59:59.999' AS decimal(8, 2));
SELECT CAST(time '23:59:59.999999' AS decimal(11, 5));
SELECT CAST(time '23:59:59.999999999' AS decimal(14, 8));
+
+-- SPARK-57618: cast TIMESTAMP_NTZ(q) to TIME(p) extracts the time-of-day and
truncates to p.
+-- This direction is deterministic and time-zone independent.
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(6));
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(3));
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(0));
+-- Pre-epoch wall-clock time-of-day is preserved.
+SELECT CAST(timestamp_ntz'1969-12-31 23:59:59.123456' AS TIME(6));
+-- Nanosecond TIMESTAMP_NTZ(q) preserves the sub-microsecond digits up to p.
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(9));
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(7));
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(6));
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(7) AS
TIME(9));
+-- Double colon syntax.
+SELECT timestamp_ntz'2020-05-17 12:34:56.789012' :: TIME(6);
+
+-- SPARK-57618: cast TIME(p) to TIMESTAMP_NTZ(q) takes the date from
CURRENT_DATE. 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* unit tests.
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ));
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ(9)));
+-- The date fields come from the query current date.
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ) AS DATE) = current_date();
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(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_NTZ) AS TIME(6));
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS TIME(9));
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(7)) AS TIME(9));
+SELECT CAST(CAST(TIME'12:34:56.789012345'::time(9) AS TIMESTAMP_NTZ(6)) AS
TIME(9));
+-- Null propagation in both directions.
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ);
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ(9));
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIME(6));
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS TIME(6));
+
+-- SPARK-57618: inside an inline table the TIME -> TIMESTAMP_NTZ 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_NTZ)) t(x);
+SELECT CAST(x AS TIME(6)) FROM VALUES (CAST(TIME'12:34:56.789012' AS
TIMESTAMP_NTZ)) t(x);
+SELECT CAST(x AS TIME(9)) FROM VALUES (CAST(TIME'12:34:56.789012345' AS
TIMESTAMP_NTZ(9))) t(x);
+
+-- SPARK-57618: TIME and TIMESTAMP_NTZ 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_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;
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 a6d3cee116aa..223504de7397 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
@@ -2871,3 +2871,294 @@ SELECT CAST(time '23:59:59.999999999' AS decimal(14, 8))
struct<CAST(TIME '23:59:59.999999999' AS DECIMAL(14,8)):decimal(14,8)>
-- !query output
86400.00000000
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012' AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012' AS TIME(3)):time(3)>
+-- !query output
+12:34:56.789
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012' AS TIME(0)):time(0)>
+-- !query output
+12:34:56
+
+
+-- !query
+SELECT CAST(timestamp_ntz'1969-12-31 23:59:59.123456' AS TIME(6))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '1969-12-31 23:59:59.123456' AS TIME(6)):time(6)>
+-- !query output
+23:59:59.123456
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(7))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(7)):time(7)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(6))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(7) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(7)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT timestamp_ntz'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '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_NTZ))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP_NTZ)):string>
+-- !query output
+timestamp_ntz
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ(9)))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP_NTZ(9))):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ) AS DATE) = current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56' AS TIMESTAMP_NTZ) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS DATE) =
current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012' AS TIMESTAMP_NTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012' AS TIMESTAMP_NTZ) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(7)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(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_NTZ(6)) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS TIMESTAMP_NTZ)
AS TIME(9)):time(9)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ)
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ(9))
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIME(6)):time(6)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ(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_NTZ)) 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_NTZ)) 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_NTZ(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_ntz'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_NTZ\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 73,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT
timestamp_ntz'2020-05-17 12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'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_NTZ\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP_NTZ '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 67,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(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_NTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_NTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_NTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp_ntz'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_NTZ\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP_NTZ
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 85,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'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 1bc9c12b0421..18ce49c59bbf 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
@@ -1871,3 +1871,294 @@ SELECT CAST(time '23:59:59.999999999' AS decimal(14, 8))
struct<CAST(TIME '23:59:59.999999999' AS DECIMAL(14,8)):decimal(14,8)>
-- !query output
86400.00000000
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(6))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012' AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(3))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012' AS TIME(3)):time(3)>
+-- !query output
+12:34:56.789
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012' AS TIME(0))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012' AS TIME(0)):time(0)>
+-- !query output
+12:34:56
+
+
+-- !query
+SELECT CAST(timestamp_ntz'1969-12-31 23:59:59.123456' AS TIME(6))
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '1969-12-31 23:59:59.123456' AS TIME(6)):time(6)>
+-- !query output
+23:59:59.123456
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(7))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(7)):time(7)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(9) AS
TIME(6))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(9)) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(timestamp_ntz'2020-05-17 12:34:56.789012345'::timestamp_ntz(7) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(TIMESTAMP_NTZ '2020-05-17 12:34:56.789012345' AS
TIMESTAMP_NTZ(7)) AS TIME(9)):time(9)>
+-- !query output
+12:34:56.7890123
+
+
+-- !query
+SELECT timestamp_ntz'2020-05-17 12:34:56.789012' :: TIME(6)
+-- !query schema
+struct<CAST(TIMESTAMP_NTZ '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_NTZ))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP_NTZ)):string>
+-- !query output
+timestamp_ntz
+
+
+-- !query
+SELECT typeof(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ(9)))
+-- !query schema
+struct<typeof(CAST(TIME '12:34:56' AS TIMESTAMP_NTZ(9))):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56' AS TIMESTAMP_NTZ) AS DATE) = current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56' AS TIMESTAMP_NTZ) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS DATE) =
current_date()
+-- !query schema
+struct<(CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS DATE) =
current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012' AS TIMESTAMP_NTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012' AS TIMESTAMP_NTZ) AS TIME(6)):time(6)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(9)) AS
TIME(9)):time(9)>
+-- !query output
+12:34:56.789012345
+
+
+-- !query
+SELECT CAST(CAST(TIME'12:34:56.789012345' AS TIMESTAMP_NTZ(7)) AS TIME(9))
+-- !query schema
+struct<CAST(CAST(TIME '12:34:56.789012345' AS TIMESTAMP_NTZ(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_NTZ(6)) AS
TIME(9))
+-- !query schema
+struct<CAST(CAST(CAST(TIME '12:34:56.789012345' AS TIME(9)) AS TIMESTAMP_NTZ)
AS TIME(9)):time(9)>
+-- !query output
+12:34:56.789012
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ)
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP_NTZ):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIME) AS TIMESTAMP_NTZ(9))
+-- !query schema
+struct<CAST(CAST(NULL AS TIME(6)) AS TIMESTAMP_NTZ(9)):timestamp_ntz(9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ) AS TIME(6)):time(6)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT CAST(CAST(NULL AS TIMESTAMP_NTZ(9)) AS TIME(6))
+-- !query schema
+struct<CAST(CAST(NULL AS TIMESTAMP_NTZ(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_NTZ)) 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_NTZ)) 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_NTZ(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_ntz'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_NTZ\"",
+ "dataType2" : "\"TIME(6)\"",
+ "hint" : "",
+ "operator" : "UNION",
+ "tableOrdinalNumber" : "second"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 1,
+ "stopIndex" : 73,
+ "fragment" : "SELECT time'12:34:56' UNION ALL SELECT
timestamp_ntz'2020-05-17 12:34:56'"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'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_NTZ\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', TIMESTAMP_NTZ '2020-05-17
12:34:56')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 67,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17 12:34:56')"
+ } ]
+}
+
+
+-- !query
+SELECT coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(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_NTZ(9)\")",
+ "functionName" : "`coalesce`",
+ "sqlExpr" : "\"coalesce(TIME '12:34:56', CAST(TIMESTAMP_NTZ '2020-05-17
12:34:56.789012345' AS TIMESTAMP_NTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 95,
+ "fragment" : "coalesce(time'12:34:56', timestamp_ntz'2020-05-17
12:34:56.789012345'::timestamp_ntz(9))"
+ } ]
+}
+
+
+-- !query
+SELECT CASE WHEN true THEN time'12:34:56' ELSE timestamp_ntz'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_NTZ\"]",
+ "functionName" : "`casewhen`",
+ "sqlExpr" : "\"CASE WHEN true THEN TIME '12:34:56' ELSE TIMESTAMP_NTZ
'2020-05-17 12:34:56' END\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 85,
+ "fragment" : "CASE WHEN true THEN time'12:34:56' ELSE
timestamp_ntz'2020-05-17 12:34:56' END"
+ } ]
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
