This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new eb508f96c963 [SPARK-57832][SQL] Support timestamp - timestamp
subtraction for nanosecond-precision timestamps
eb508f96c963 is described below
commit eb508f96c96386ceeb94a63ea6912c1f2186b517
Author: Stevo Mitric <[email protected]>
AuthorDate: Tue Aug 11 10:37:33 2026 +0000
[SPARK-57832][SQL] Support timestamp - timestamp subtraction for
nanosecond-precision timestamps
### What changes were proposed in this pull request?
`SubtractTimestamps` (the `timestamp - timestamp` operator) previously only
accepted the microsecond timestamp types (`AnyTimestampType`). This PR extends
it to also accept the nanosecond-precision timestamp types (`TIMESTAMP_NTZ(p)`
/ `TIMESTAMP_LTZ(p)`, `p` in [7, 9]).
The difference is always reported on the microsecond grid, since the result
is a `DayTimeIntervalType` (which has microsecond resolution). Each operand
therefore contributes only its `epochMicros`; the sub-microsecond remainder is
truncated. This keeps `ts_nanos - ts_nanos` numerically identical to
`ts_nanos::timestamp - ts_nanos::timestamp`.
Concretely:
- `SubtractTimestamps.inputTypes` widens to
`TypeCollection(AnyTimestampType, AnyTimestampNanoType)` for both operands, and
`nullSafeEval` / `doGenCode` reduce a `TimestampNanosVal` operand to its
`epochMicros` before subtracting.
- `BinaryArithmeticWithDatetimeResolver` recognizes a nanos operand on
either side of a `Subtract` and routes it to `SubtractTimestamps`.
- The `DateTimeOperations` / `AnsiDateTimeOperations` type-coercion rules
unify mixed operands (DATE vs timestamp, or two timestamps differing in
precision or time-zone family). A cross-family pair unifies in the NTZ family
(mirroring the microsecond `TIMESTAMP - TIMESTAMP_NTZ` precedent); a
same-family pair keeps its family so an LTZ pair still subtracts in the session
time zone.
For pure-microsecond inputs the behavior is unchanged.
### Why are the changes needed?
Sub-task of SPARK-56822 (nanosecond-precision timestamp support). Without
this, subtracting two nanosecond timestamps fails analysis.
### Does this PR introduce _any_ user-facing change?
Yes. `timestamp_nanos - timestamp_nanos` (and mixed micro/nanos, and
nanos/DATE) now produces a microsecond-grid `DayTimeIntervalType` instead of
failing analysis.
### How was this patch tested?
- New unit test in `DateExpressionsSuite` covering NTZ/LTZ nanos operands,
sub-microsecond truncation, pre-epoch values, NULLs, and interpreted/codegen
consistency.
- New coercion cases in `TypeCoercionSuite` / `AnsiTypeCoercionSuite`.
- New golden SQL cases in `timestamp-ntz-nanos.sql` /
`timestamp-ltz-nanos.sql`.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (Claude Opus 4.8)
Closes #57680 from stevomitric/stevomitric/spark-57832-subtract-nanos.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
(cherry picked from commit e29825ff8de87ab32801876f7b34bf12d3469991)
Signed-off-by: Uros Bojanic <[email protected]>
---
.../BinaryArithmeticWithDatetimeResolver.scala | 4 +-
.../sql/catalyst/analysis/TypeCoercionHelper.scala | 68 +++++++++++++----
.../catalyst/expressions/datetimeExpressions.scala | 50 ++++++++++---
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 62 ++++++++++++++++
.../expressions/DateExpressionsSuite.scala | 86 ++++++++++++++++++++++
.../analyzer-results/nonansi/timestamp.sql.out | 8 +-
.../sql-tests/analyzer-results/time.sql.out | 2 +-
.../analyzer-results/timestamp-ltz-nanos.sql.out | 54 ++++++++++++++
.../analyzer-results/timestamp-ntz-nanos.sql.out | 55 ++++++++++++++
.../timestampNTZ/timestamp.sql.out | 8 +-
.../typeCoercion/native/decimalPrecision.sql.out | 16 ++--
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 23 ++++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 20 +++++
.../sql-tests/results/nonansi/timestamp.sql.out | 8 +-
.../test/resources/sql-tests/results/time.sql.out | 2 +-
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 64 ++++++++++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 64 ++++++++++++++++
.../results/timestampNTZ/timestamp.sql.out | 8 +-
.../typeCoercion/native/decimalPrecision.sql.out | 16 ++--
19 files changed, 560 insertions(+), 58 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/BinaryArithmeticWithDatetimeResolver.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/BinaryArithmeticWithDatetimeResolver.scala
index 72e11082d52f..3b254f297242 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/BinaryArithmeticWithDatetimeResolver.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/BinaryArithmeticWithDatetimeResolver.scala
@@ -145,7 +145,9 @@ object BinaryArithmeticWithDatetimeResolver {
TimestampAddInterval(l, UnaryMinus(r, context.evalMode ==
EvalMode.ANSI))), l.dataType)
case _
if AnyTimestampTypeExpression.unapply(l) ||
- AnyTimestampTypeExpression.unapply(r) =>
+ AnyTimestampTypeExpression.unapply(r) ||
+ AnyTimestampNanoType.acceptsType(l.dataType) ||
+ AnyTimestampNanoType.acceptsType(r.dataType) =>
SubtractTimestamps(l, r)
case (_, DateType) => SubtractDates(l, r)
case (DateType, dt) if dt != StringType => DateSub(l, r)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
index 942a6be948d8..93e1c602c072 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
@@ -244,6 +244,11 @@ abstract class TypeCoercionHelper {
}
}
+ // Fractional-seconds precision of the microsecond timestamp family. DATE
has no time component
+ // and is treated as this precision when widening (so DATE <-> micro widens
to a micro type and
+ // DATE <-> nanos to a nanos type). The nanos types carry their own
precision in [7, 9].
+ private final val MicrosPrecision = 6
+
protected def findWiderDateTimeType(d1: DatetimeType, d2: DatetimeType):
Option[DatetimeType] =
(d1, d2) match {
// Two TIME operands of differing fractional-seconds precision widen to
the larger precision
@@ -275,7 +280,6 @@ abstract class TypeCoercionHelper {
// Fractional-seconds precision of the timestamp family (micros: 6,
nanos: 7-9). DATE has no
// time component and is treated as the micro precision (getOrElse) so
that DATE <-> micro
// widens to the micro type and DATE <-> nanos to the nanos type.
- val MicrosPrecision = 6
def isLtz(d: DatetimeType): Boolean = TimestampFamily.isLtz(d)
def isNtz(d: DatetimeType): Boolean = TimestampFamily.isNtz(d)
def precisionOf(d: DatetimeType): Int =
@@ -302,6 +306,36 @@ abstract class TypeCoercionHelper {
}
}
+ /** Whether `dt` is on the LTZ/NTZ timestamp fractional-precision axis (a
micro or nanos type). */
+ private def isTimestampFamily(dt: DataType): Boolean =
+ TimestampFamily.fractionalPrecision(dt).isDefined
+
+ /**
+ * Common operand type for [[SubtractTimestamps]] over two differing
timestamp-family operands.
+ * The operands are widened to the larger of the two fractional-second
precisions (the micro types
+ * count as 6, the nanos types carry their own precision `p` in [7, 9]) and
unified in one
+ * time-zone family:
+ * - a cross-family pair unifies in the no-time-zone (NTZ) family,
mirroring the microsecond
+ * precedent where TIMESTAMP - TIMESTAMP_NTZ coerces both operands to
TIMESTAMP_NTZ;
+ * - a same-family pair keeps that family, so a TIMESTAMP -
TIMESTAMP_LTZ(p) style pair still
+ * subtracts in the session time zone (DST-aware) exactly as a pure LTZ
pair does.
+ * The subtraction reads only each operand's epochMicros and always reports
the difference on the
+ * microsecond grid (a DayTimeIntervalType in the default mode, a
CalendarIntervalType when
+ * spark.sql.legacy.interval.enabled is set), so widening the precision
never changes the numeric
+ * result -- it only keeps the two operands the same concrete type. For a
pure-micro cross-family
+ * pair this returns TimestampNTZType, identical to the pre-nanos behavior.
+ */
+ private def subtractTimestampsCommonType(dt1: DataType, dt2: DataType):
DataType = {
+ val p = math.max(
+ TimestampFamily.fractionalPrecision(dt1).getOrElse(MicrosPrecision),
+ TimestampFamily.fractionalPrecision(dt2).getOrElse(MicrosPrecision))
+ if (TimestampFamily.isLtz(dt1) && TimestampFamily.isLtz(dt2)) {
+ if (p <= MicrosPrecision) TimestampType else TimestampLTZNanosType(p)
+ } else {
+ if (p <= MicrosPrecision) TimestampNTZType else TimestampNTZNanosType(p)
+ }
+ }
+
/**
* Type coercion helper that matches agaist [[In]] and [[InSubquery]]
expressions in order to
* type coerce LHS and RHS to expected types.
@@ -738,14 +772,18 @@ abstract class TypeCoercionHelper {
d.copy(startDate = Cast(d.startDate, DateType))
case d @ DateSub(StringTypeExpression(), _) => d.copy(startDate =
Cast(d.startDate, DateType))
- case s @ SubtractTimestamps(DateTypeExpression(),
AnyTimestampTypeExpression(), _, _) =>
+ case s @ SubtractTimestamps(DateTypeExpression(), r, _, _)
+ if isTimestampFamily(r.dataType) =>
s.copy(left = Cast(s.left, s.right.dataType))
- case s @ SubtractTimestamps(AnyTimestampTypeExpression(),
DateTypeExpression(), _, _) =>
+ case s @ SubtractTimestamps(l, DateTypeExpression(), _, _)
+ if isTimestampFamily(l.dataType) =>
s.copy(right = Cast(s.right, s.left.dataType))
- case s @ SubtractTimestamps(AnyTimestampTypeExpression(),
AnyTimestampTypeExpression(), _, _)
- if s.left.dataType != s.right.dataType =>
- val newLeft = castIfNotSameType(s.left, TimestampNTZType)
- val newRight = castIfNotSameType(s.right, TimestampNTZType)
+ case s @ SubtractTimestamps(l, r, _, _)
+ if isTimestampFamily(l.dataType) && isTimestampFamily(r.dataType) &&
+ l.dataType != r.dataType =>
+ val commonType = subtractTimestampsCommonType(l.dataType, r.dataType)
+ val newLeft = castIfNotSameType(s.left, commonType)
+ val newRight = castIfNotSameType(s.right, commonType)
s.copy(left = newLeft, right = newRight)
case t @ TimestampAddInterval(StringTypeExpression(), _, _) =>
@@ -766,14 +804,18 @@ abstract class TypeCoercionHelper {
case d @ DateSub(AnyTimestampTypeExpression(), _) =>
d.copy(startDate = Cast(d.startDate, DateType))
- case s @ SubtractTimestamps(DateTypeExpression(),
AnyTimestampTypeExpression(), _, _) =>
+ case s @ SubtractTimestamps(DateTypeExpression(), r, _, _)
+ if isTimestampFamily(r.dataType) =>
s.copy(left = Cast(s.left, s.right.dataType))
- case s @ SubtractTimestamps(AnyTimestampTypeExpression(),
DateTypeExpression(), _, _) =>
+ case s @ SubtractTimestamps(l, DateTypeExpression(), _, _)
+ if isTimestampFamily(l.dataType) =>
s.copy(right = Cast(s.right, s.left.dataType))
- case s @ SubtractTimestamps(AnyTimestampTypeExpression(),
AnyTimestampTypeExpression(), _, _)
- if s.left.dataType != s.right.dataType =>
- val newLeft = castIfNotSameType(s.left, TimestampNTZType)
- val newRight = castIfNotSameType(s.right, TimestampNTZType)
+ case s @ SubtractTimestamps(l, r, _, _)
+ if isTimestampFamily(l.dataType) && isTimestampFamily(r.dataType) &&
+ l.dataType != r.dataType =>
+ val commonType = subtractTimestampsCommonType(l.dataType, r.dataType)
+ val newLeft = castIfNotSameType(s.left, commonType)
+ val newRight = castIfNotSameType(s.right, commonType)
s.copy(left = newLeft, right = newRight)
case other => other
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 f2ad3107ed6d..6eddd779d704 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
@@ -4704,6 +4704,10 @@ object Extract {
* the given timestamps.
* - Otherwise the expression returns `DayTimeIntervalType` with the
difference in microseconds
* between given timestamps.
+ *
+ * Both microsecond and nanosecond-precision timestamp types are accepted as
operands. Because the
+ * difference is reported on the microsecond grid in either mode, a nanosecond
operand contributes
+ * only its `epochMicros`; the sub-microsecond remainder is truncated.
*/
case class SubtractTimestamps(
left: Expression,
@@ -4718,7 +4722,14 @@ case class SubtractTimestamps(
def this(endTimestamp: Expression, startTimestamp: Expression) =
this(endTimestamp, startTimestamp, SQLConf.get.legacyIntervalEnabled)
- override def inputTypes: Seq[AbstractDataType] = Seq(AnyTimestampType,
AnyTimestampType)
+ // Nanosecond-precision timestamps are accepted alongside the microsecond
types. The difference is
+ // always reported on the microsecond grid (both result types --
DayTimeIntervalType and, in
+ // legacy mode, CalendarIntervalType -- carry microsecond resolution), so
each operand contributes
+ // only its epochMicros; the sub-microsecond remainder is truncated.
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(
+ TypeCollection(AnyTimestampType, AnyTimestampNanoType),
+ TypeCollection(AnyTimestampType, AnyTimestampNanoType))
override def dataType: DataType =
if (legacyInterval) CalendarIntervalType else DayTimeIntervalType()
@@ -4727,6 +4738,13 @@ case class SubtractTimestamps(
@transient private lazy val zoneIdInEval: ZoneId =
zoneIdForType(left.dataType)
+ // For the nanosecond carrier the child value is a boxed TimestampNanosVal,
so read its
+ // epochMicros; for the microsecond timestamp types it is already a boxed
Long.
+ private def toMicros(value: Any): Long = value match {
+ case v: TimestampNanosVal => v.epochMicros
+ case n => n.asInstanceOf[Long]
+ }
+
@transient
private lazy val evalFunc: (Long, Long) => Any = if (legacyInterval) {
(leftMicros, rightMicros) =>
@@ -4736,17 +4754,29 @@ case class SubtractTimestamps(
subtractTimestamps(leftMicros, rightMicros, zoneIdInEval)
}
- override def nullSafeEval(leftMicros: Any, rightMicros: Any): Any = {
- evalFunc(leftMicros.asInstanceOf[Long], rightMicros.asInstanceOf[Long])
+ override def nullSafeEval(leftTs: Any, rightTs: Any): Any = {
+ evalFunc(toMicros(leftTs), toMicros(rightTs))
}
- override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = if
(legacyInterval) {
- defineCodeGen(ctx, ev, (end, start) =>
- s"new org.apache.spark.unsafe.types.CalendarInterval(0, 0, $end -
$start)")
- } else {
- val zid = ctx.addReferenceObj("zoneId", zoneIdInEval,
classOf[ZoneId].getName)
- val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, (l, r) => s"""$dtu.subtractTimestamps($l, $r,
$zid)""")
+ override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {
+ // The nanosecond carrier exposes epochMicros as a public field; the
microsecond types are
+ // already primitive longs. Reduce each operand to microseconds before
subtracting.
+ def toMicrosCode(e: Expression): String => String = e.dataType match {
+ case _: AnyTimestampNanoType => c => s"$c.epochMicros"
+ case _ => c => c
+ }
+ val leftMicros = toMicrosCode(left)
+ val rightMicros = toMicrosCode(right)
+ if (legacyInterval) {
+ defineCodeGen(ctx, ev, (end, start) =>
+ s"new org.apache.spark.unsafe.types.CalendarInterval(0, 0, " +
+ s"${leftMicros(end)} - ${rightMicros(start)})")
+ } else {
+ val zid = ctx.addReferenceObj("zoneId", zoneIdInEval,
classOf[ZoneId].getName)
+ val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
+ defineCodeGen(ctx, ev, (l, r) =>
+ s"""$dtu.subtractTimestamps(${leftMicros(l)}, ${rightMicros(r)},
$zid)""")
+ }
}
override def toString: String = s"($left - $right)"
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
index 2fbea06b4d06..fc596766e189 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
@@ -29,6 +29,7 @@ import org.apache.spark.sql.catalyst.plans.ReferenceAllColumns
import org.apache.spark.sql.catalyst.plans.logical._
import org.apache.spark.sql.catalyst.rules.{Rule, RuleExecutor}
import org.apache.spark.sql.catalyst.types.DataTypeUtils
+import org.apache.spark.sql.catalyst.util.DateTimeUtils
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.types._
import org.apache.spark.unsafe.types.CalendarInterval
@@ -436,6 +437,67 @@ abstract class TypeCoercionSuiteBase extends AnalysisTest {
ruleTest(rule,
SubtractTimestamps(timestampNTZLiteral, timestampLiteral),
SubtractTimestamps(timestampNTZLiteral, Cast(timestampLiteral,
TimestampNTZType)))
+
+ // SPARK-57832: subtraction accepts nanosecond-precision timestamps. A
DATE operand takes the
+ // nanos type of the other side; a timestamp pair that differs only in
precision or family is
+ // widened to a common type before being handed to SubtractTimestamps.
+ val ntzNanos9 = Literal.create(
+ DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2021-01-01T00:00:00"), precision = 9),
+ TimestampNTZNanosType(9))
+ val ltzNanos9 = Literal.create(
+ DateTimeUtils.instantToTimestampNanos(
+ java.time.Instant.parse("2021-01-01T00:00:00Z"), precision = 9),
+ TimestampLTZNanosType(9))
+ val ntzNanos7 = Literal.create(
+ DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2021-01-01T00:00:00"), precision = 7),
+ TimestampNTZNanosType(7))
+
+ // DATE - nanos and nanos - DATE cast the DATE side to the nanos type.
Both the NTZ-nanos and
+ // the LTZ-nanos side are covered so the DATE-adopts-the-other-family arm
is exercised in both
+ // time-zone families and both operand orders.
+ ruleTest(rule,
+ SubtractTimestamps(dateLiteral, ntzNanos9),
+ SubtractTimestamps(Cast(dateLiteral, TimestampNTZNanosType(9)),
ntzNanos9))
+ ruleTest(rule,
+ SubtractTimestamps(ntzNanos9, dateLiteral),
+ SubtractTimestamps(ntzNanos9, Cast(dateLiteral,
TimestampNTZNanosType(9))))
+ ruleTest(rule,
+ SubtractTimestamps(dateLiteral, ltzNanos9),
+ SubtractTimestamps(Cast(dateLiteral, TimestampLTZNanosType(9)),
ltzNanos9))
+ ruleTest(rule,
+ SubtractTimestamps(ltzNanos9, dateLiteral),
+ SubtractTimestamps(ltzNanos9, Cast(dateLiteral,
TimestampLTZNanosType(9))))
+ // Same-precision same-family pair is already the same type -> left
untouched.
+ ruleTest(rule,
+ SubtractTimestamps(ntzNanos9, ntzNanos9),
+ SubtractTimestamps(ntzNanos9, ntzNanos9))
+ // Cross-family nanos pair (LTZ vs NTZ) unifies in the NTZ family at the
max precision.
+ ruleTest(rule,
+ SubtractTimestamps(ltzNanos9, ntzNanos7),
+ SubtractTimestamps(
+ Cast(ltzNanos9, TimestampNTZNanosType(9)), Cast(ntzNanos7,
TimestampNTZNanosType(9))))
+ // Micro NTZ vs nanos NTZ: same family, widen precision to the nanos type.
+ ruleTest(rule,
+ SubtractTimestamps(timestampNTZLiteral, ntzNanos9),
+ SubtractTimestamps(Cast(timestampNTZLiteral, TimestampNTZNanosType(9)),
ntzNanos9))
+ // Micro LTZ (TIMESTAMP) vs nanos LTZ: same LTZ family, widen precision to
the nanos LTZ type
+ // so the subtraction still runs in the session time zone.
+ ruleTest(rule,
+ SubtractTimestamps(timestampLiteral, ltzNanos9),
+ SubtractTimestamps(Cast(timestampLiteral, TimestampLTZNanosType(9)),
ltzNanos9))
+ // Cross-family micro/nanos pairs: a micro operand on one side and a nanos
operand of the other
+ // family on the other. Both unify in the NTZ family at the nanos
precision (the cross-family
+ // rule prefers NTZ), so the micro operand widens across both axes at once.
+ ruleTest(rule,
+ SubtractTimestamps(timestampLiteral, ntzNanos9),
+ SubtractTimestamps(Cast(timestampLiteral, TimestampNTZNanosType(9)),
ntzNanos9))
+ ruleTest(rule,
+ SubtractTimestamps(timestampNTZLiteral, ltzNanos9),
+ SubtractTimestamps(
+ Cast(timestampNTZLiteral, TimestampNTZNanosType(9)),
+ Cast(ltzNanos9, TimestampNTZNanosType(9))))
}
test("datetime comparison") {
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 071e1949ad96..691263e5b77d 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
@@ -2407,6 +2407,92 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("SPARK-57832: subtract nanosecond-precision timestamps") {
+ // The difference between two nanosecond timestamps is reported on the
microsecond grid: only
+ // each operand's epochMicros participates, so the sub-microsecond
remainder is truncated. The
+ // first pair below differs by a whole day plus 0.123456 s at the
microsecond level, and the
+ // 789/111 sub-microsecond digits drop out entirely. The zero-result case
(two values inside the
+ // same microsecond) is exercised by the second pair further down.
+ val ntzType = TimestampNTZNanosType(9)
+ val ntzLeft = DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2020-01-02T03:04:05.123456789"), precision = 9)
+ val ntzRight = DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2020-01-01T03:04:05.000000111"), precision = 9)
+ // 1 day + 0.123456 s; the 789/111 sub-microsecond digits drop out.
+ checkEvaluation(
+ SubtractTimestamps(
+ Literal.create(ntzLeft, ntzType),
+ Literal.create(ntzRight, ntzType),
+ legacyInterval = false,
+ timeZoneId = Some("UTC")),
+ Duration.ofDays(1).plus(123456, ChronoUnit.MICROS))
+ // Two values inside the same microsecond subtract to exactly zero
(remainder truncated).
+ checkEvaluation(
+ SubtractTimestamps(
+ Literal.create(ntzLeft, ntzType),
+ Literal.create(
+ DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2020-01-02T03:04:05.123456001"), precision =
9),
+ ntzType),
+ legacyInterval = false,
+ timeZoneId = Some("UTC")),
+ Duration.ZERO)
+ // Legacy calendar-interval result carries the same microsecond difference.
+ checkEvaluation(
+ SubtractTimestamps(
+ Literal.create(ntzLeft, ntzType),
+ Literal.create(ntzRight, ntzType),
+ legacyInterval = true,
+ timeZoneId = Some("UTC")),
+ new CalendarInterval(0, 0, MICROS_PER_DAY + 123456L))
+
+ // LTZ nanos: subtraction reads the local wall clock at the session zone.
Evaluated at UTC the
+ // instants and their local date-times coincide, so the difference matches
the NTZ case above.
+ val ltzType = TimestampLTZNanosType(9)
+ val ltzLeft = DateTimeUtils.instantToTimestampNanos(
+ Instant.parse("2020-01-02T03:04:05.123456789Z"), precision = 9)
+ val ltzRight = DateTimeUtils.instantToTimestampNanos(
+ Instant.parse("2020-01-01T03:04:05.000000111Z"), precision = 9)
+ checkEvaluation(
+ SubtractTimestamps(
+ Literal.create(ltzLeft, ltzType),
+ Literal.create(ltzRight, ltzType),
+ legacyInterval = false,
+ timeZoneId = Some("UTC")),
+ Duration.ofDays(1).plus(123456, ChronoUnit.MICROS))
+
+ // Pre-epoch operand exercises the negative-epoch path; the result stays
on the micros grid.
+ val ntzPreEpoch = DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("1960-01-01T00:00:00.000000999"), precision = 9)
+ checkEvaluation(
+ SubtractTimestamps(
+ Literal.create(ntzLeft, ntzType),
+ Literal.create(ntzPreEpoch, ntzType),
+ legacyInterval = false,
+ timeZoneId = Some("UTC")),
+ Duration.between(
+ LocalDateTime.parse("1960-01-01T00:00:00"),
+ LocalDateTime.parse("2020-01-02T03:04:05.123456")))
+
+ // NULL operands propagate.
+ checkEvaluation(
+ SubtractTimestamps(
+ Literal.create(null, ntzType),
+ Literal.create(ntzRight, ntzType),
+ legacyInterval = false,
+ timeZoneId = Some("UTC")),
+ null)
+
+ Seq(false, true).foreach { legacy =>
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (l: Expression, r: Expression) => SubtractTimestamps(l, r, legacy,
Some("UTC")),
+ ntzType, ntzType)
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (l: Expression, r: Expression) => SubtractTimestamps(l, r, legacy,
Some("UTC")),
+ ltzType, ltzType)
+ }
+ }
+
test("SPARK-37552: convert a timestamp_ntz to another time zone") {
checkEvaluation(
ConvertTimezone(
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/timestamp.sql.out
index a6ebe17e8b31..a1191af8f6c6 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/timestamp.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/timestamp.sql.out
@@ -875,7 +875,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:10\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP '2011-11-11 11:11:11' - 2011-11-11 11:11:10)\""
},
"queryContext" : [ {
@@ -899,7 +899,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:11\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(2011-11-11 11:11:11 - TIMESTAMP '2011-11-11 11:11:10')\""
},
"queryContext" : [ {
@@ -943,7 +943,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(str - TIMESTAMP '2011-11-11 11:11:11')\""
},
"queryContext" : [ {
@@ -967,7 +967,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP '2011-11-11 11:11:11' - str)\""
},
"queryContext" : [ {
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
index 3b0cece05ea6..3173aa5e2f93 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
@@ -1940,7 +1940,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"TIME '12:30:41.123'\"",
"inputType" : "\"TIME(6)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIME '12:30:41.123' - TIMESTAMP '2025-07-11 10:00:01')\""
},
"queryContext" : [ {
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
index 69b136fc0caa..cca0a1e7d836 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
@@ -662,6 +662,60 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-01 03:04:05.000000111 UTC'
+-- !query analysis
+Project [(2020-01-01 19:04:05.123456789 - 2019-12-31 19:04:05.000000111) AS
(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ '2019-12-31
19:04:05.000000111')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456001 UTC'
+-- !query analysis
+Project [(2020-01-01 19:04:05.123456789 - 2020-01-01 19:04:05.123456001) AS
(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ '2020-01-01
19:04:05.123456001')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456789 UTC'
+-- !query analysis
+Project [(2019-12-31 19:04:05.000000111 - 2020-01-01 19:04:05.123456789) AS
(TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111' - TIMESTAMP_LTZ '2020-01-01
19:04:05.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT ('2020-01-02 03:04:05.1234567 UTC' :: timestamp_ltz(7)) - ('2020-01-01
03:04:05.000000009 UTC' :: timestamp_ltz(9))
+-- !query analysis
+Project [(cast(cast(2020-01-02 03:04:05.1234567 UTC as timestamp_ltz(7)) as
timestamp_ltz(9)) - cast(2020-01-01 03:04:05.000000009 UTC as
timestamp_ltz(9))) AS (CAST(2020-01-02 03:04:05.1234567 UTC AS
TIMESTAMP_LTZ(7)) - CAST(2020-01-01 03:04:05.000000009 UTC AS
TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC'
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC' - TIMESTAMP_LTZ
'1960-01-01 00:00:00.000000999 UTC'
+-- !query analysis
+Project [(2019-12-31 16:00:00.123456789 - 1959-12-31 16:00:00.000000999) AS
(TIMESTAMP_LTZ '2019-12-31 16:00:00.123456789' - TIMESTAMP_LTZ '1959-12-31
16:00:00.000000999')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - CAST(NULL AS
timestamp_ltz(9))
+-- !query analysis
+Project [(2020-01-01 19:04:05.123456789 - cast(null as timestamp_ltz(9))) AS
(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - CAST(NULL AS
TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
-- !query
SELECT max(c), min(c) FROM VALUES
(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'),
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
index bfe32a1f2b9c..d8ff98fbe760 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
@@ -589,6 +589,61 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111'
+-- !query analysis
+Project [(2020-01-02 03:04:05.123456789 - 2020-01-01 03:04:05.000000111) AS
(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01
03:04:05.000000111')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001'
+-- !query analysis
+Project [(2020-01-02 03:04:05.123456789 - 2020-01-02 03:04:05.123456001) AS
(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-02
03:04:05.123456001')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789'
+-- !query analysis
+Project [(2020-01-01 03:04:05.000000111 - 2020-01-02 03:04:05.123456789) AS
(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ '2020-01-02
03:04:05.123456789')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9))
+-- !query analysis
+Project [(cast(cast(2020-01-02 03:04:05.1234567 as timestamp_ntz(7)) as
timestamp_ntz(9)) - cast(2020-01-01 03:04:05.000000009 as timestamp_ntz(9))) AS
(CAST(2020-01-02 03:04:05.1234567 AS TIMESTAMP_NTZ(7)) - CAST(2020-01-01
03:04:05.000000009 AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05'
+-- !query analysis
+Project [(2020-01-02 03:04:05.123456789 - cast(2020-01-02 03:04:05 as
timestamp_ntz(9))) AS (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-02 03:04:05')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999'
+-- !query analysis
+Project [(2020-01-01 00:00:00.123456789 - 1960-01-01 00:00:00.000000999) AS
(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ '1960-01-01
00:00:00.000000999')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
timestamp_ntz(9))
+-- !query analysis
+Project [(2020-01-02 03:04:05.123456789 - cast(null as timestamp_ntz(9))) AS
(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
-- !query
SELECT max(c), min(c) FROM VALUES
(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'),
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
index af47dffedcb1..f66c0b84e882 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
@@ -877,7 +877,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:10\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP_NTZ '2011-11-11 11:11:11' - 2011-11-11
11:11:10)\""
},
"queryContext" : [ {
@@ -901,7 +901,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:11\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(2011-11-11 11:11:11 - TIMESTAMP_NTZ '2011-11-11
11:11:10')\""
},
"queryContext" : [ {
@@ -947,7 +947,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(str - TIMESTAMP_NTZ '2011-11-11 11:11:11')\""
},
"queryContext" : [ {
@@ -971,7 +971,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP_NTZ '2011-11-11 11:11:11' - str)\""
},
"queryContext" : [ {
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/typeCoercion/native/decimalPrecision.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/typeCoercion/native/decimalPrecision.sql.out
index 4458e15e53cf..45e987583c9e 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/typeCoercion/native/decimalPrecision.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/typeCoercion/native/decimalPrecision.sql.out
@@ -1706,7 +1706,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(3,0))\"",
"inputType" : "\"DECIMAL(3,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(3,0)))\""
},
"queryContext" : [ {
@@ -1730,7 +1730,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(5,0))\"",
"inputType" : "\"DECIMAL(5,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(5,0)))\""
},
"queryContext" : [ {
@@ -1754,7 +1754,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(10,0))\"",
"inputType" : "\"DECIMAL(10,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(10,0)))\""
},
"queryContext" : [ {
@@ -1778,7 +1778,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(20,0))\"",
"inputType" : "\"DECIMAL(20,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(20,0)))\""
},
"queryContext" : [ {
@@ -2426,7 +2426,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(3,0))\"",
"inputType" : "\"DECIMAL(3,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(3,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
@@ -2450,7 +2450,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(5,0))\"",
"inputType" : "\"DECIMAL(5,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(5,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
@@ -2474,7 +2474,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(10,0))\"",
"inputType" : "\"DECIMAL(10,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(10,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
@@ -2498,7 +2498,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(20,0))\"",
"inputType" : "\"DECIMAL(20,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(20,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
index 385b242749e7..9133dda2e006 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
@@ -182,6 +182,29 @@ SELECT TIMESTAMP_LTZ '1960-01-31 03:04:05.123456789 UTC' +
INTERVAL '1' MONTH;
-- legacy calendar interval is still rejected by TimestampAddInterval's type
check.
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + make_interval(0, 1,
0, 2, 0, 0, 0);
+-- SPARK-57832: TIMESTAMP_LTZ(p) - TIMESTAMP_LTZ(p) yields a microsecond-grid
DayTimeIntervalType.
+-- Only each operand's epochMicros participates, so the sub-microsecond
remainder is truncated: the
+-- 789/111 sub-micro digits drop out and the difference is exactly 1 day +
0.123456 s. Like the
+-- micro TIMESTAMP_LTZ case, the subtraction runs on session-zone local
date-times, so a DST
+-- transition between the two instants could shift the interval; there is none
in the UTC span here.
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-01 03:04:05.000000111 UTC';
+-- Two values inside the same microsecond subtract to zero once the remainder
is truncated.
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456001 UTC';
+-- The subtraction is antisymmetric.
+SELECT TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456789 UTC';
+-- Mixed precision (7 vs 9) widens to the common nanos type before subtracting.
+SELECT ('2020-01-02 03:04:05.1234567 UTC' :: timestamp_ltz(7)) - ('2020-01-01
03:04:05.000000009 UTC' :: timestamp_ltz(9));
+-- Mixed with a micro TIMESTAMP_LTZ operand.
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC';
+-- A DATE operand is cast to the nanos LTZ type (midnight in the session time
zone); the
+-- fraction below the micro grid drops. The bare LTZ literal and the DATE both
read in the session
+-- zone (America/Los_Angeles), so the difference is exactly 1 day.
+SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01';
+-- Pre-epoch operand exercises the negative-epoch path.
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC' - TIMESTAMP_LTZ
'1960-01-01 00:00:00.000000999 UTC';
+-- NULL operand propagates.
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - CAST(NULL AS
timestamp_ltz(9));
+
-- SPARK-57103: MAX / MIN over nanosecond-precision TIMESTAMP_LTZ. The
aggregate preserves the
-- nanosecond type and orders by the sub-microsecond remainder; NULLs are
ignored. Values are
-- rendered in the session time zone (America/Los_Angeles).
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
index 5a458a6b265e..d145ecb7146a 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
@@ -158,6 +158,26 @@ SELECT TIMESTAMP_NTZ '1960-01-31 03:04:05.123456789' +
INTERVAL '1' MONTH;
-- legacy calendar interval is still rejected by TimestampAddInterval's type
check.
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + make_interval(0, 1, 0,
2, 0, 0, 0);
+-- SPARK-57832: TIMESTAMP_NTZ(p) - TIMESTAMP_NTZ(p) yields a microsecond-grid
DayTimeIntervalType.
+-- Only each operand's epochMicros participates, so the sub-microsecond
remainder is truncated: the
+-- 789/111 sub-micro digits drop out and the difference is exactly 1 day +
0.123456 s.
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111';
+-- Two values inside the same microsecond subtract to zero once the remainder
is truncated.
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001';
+-- The subtraction is antisymmetric.
+SELECT TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789';
+-- Mixed precision (7 vs 9) widens to the common nanos type before
subtracting; the result stays on
+-- the micros grid.
+SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9));
+-- Mixed with a micro TIMESTAMP_NTZ operand.
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05';
+-- A DATE operand is cast to the nanos type (midnight); the fraction below the
micro grid drops.
+SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01';
+-- Pre-epoch operand exercises the negative-epoch path.
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999';
+-- NULL operand propagates.
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
timestamp_ntz(9));
+
-- SPARK-57103: MAX / MIN over nanosecond-precision TIMESTAMP_NTZ. The
aggregate preserves the
-- nanosecond type and orders by the sub-microsecond remainder (two values
share the same
-- microsecond and differ only within it); NULLs are ignored.
diff --git
a/sql/core/src/test/resources/sql-tests/results/nonansi/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/nonansi/timestamp.sql.out
index 4d7786512e55..c0aaec386bf9 100644
--- a/sql/core/src/test/resources/sql-tests/results/nonansi/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/nonansi/timestamp.sql.out
@@ -1035,7 +1035,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:10\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP '2011-11-11 11:11:11' - 2011-11-11 11:11:10)\""
},
"queryContext" : [ {
@@ -1061,7 +1061,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:11\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(2011-11-11 11:11:11 - TIMESTAMP '2011-11-11 11:11:10')\""
},
"queryContext" : [ {
@@ -1111,7 +1111,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(str - TIMESTAMP '2011-11-11 11:11:11')\""
},
"queryContext" : [ {
@@ -1137,7 +1137,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP '2011-11-11 11:11:11' - str)\""
},
"queryContext" : [ {
diff --git a/sql/core/src/test/resources/sql-tests/results/time.sql.out
b/sql/core/src/test/resources/sql-tests/results/time.sql.out
index 58e72aec919e..5bb6fb64c748 100644
--- a/sql/core/src/test/resources/sql-tests/results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/time.sql.out
@@ -2358,7 +2358,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"TIME '12:30:41.123'\"",
"inputType" : "\"TIME(6)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIME '12:30:41.123' - TIMESTAMP '2025-07-11 10:00:01')\""
},
"queryContext" : [ {
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
index 3ae6f96251cc..31a40e0d80a0 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
@@ -744,6 +744,70 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-01 03:04:05.000000111 UTC'
+-- !query schema
+struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ
'2019-12-31 19:04:05.000000111'):interval day to second>
+-- !query output
+1 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456001 UTC'
+-- !query schema
+struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ
'2020-01-01 19:04:05.123456001'):interval day to second>
+-- !query output
+0 00:00:00.000000000
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456789 UTC'
+-- !query schema
+struct<(TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111' - TIMESTAMP_LTZ
'2020-01-01 19:04:05.123456789'):interval day to second>
+-- !query output
+-1 00:00:00.123456000
+
+
+-- !query
+SELECT ('2020-01-02 03:04:05.1234567 UTC' :: timestamp_ltz(7)) - ('2020-01-01
03:04:05.000000009 UTC' :: timestamp_ltz(9))
+-- !query schema
+struct<(CAST(2020-01-02 03:04:05.1234567 UTC AS TIMESTAMP_LTZ(7)) -
CAST(2020-01-01 03:04:05.000000009 UTC AS TIMESTAMP_LTZ(9))):interval day to
second>
+-- !query output
+1 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC'
+-- !query schema
+struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP '2020-01-01
19:04:05'):interval day to second>
+-- !query output
+0 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
+-- !query schema
+struct<(TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'):interval day to second>
+-- !query output
+1 00:00:00.000000000
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC' - TIMESTAMP_LTZ
'1960-01-01 00:00:00.000000999 UTC'
+-- !query schema
+struct<(TIMESTAMP_LTZ '2019-12-31 16:00:00.123456789' - TIMESTAMP_LTZ
'1959-12-31 16:00:00.000000999'):interval day to second>
+-- !query output
+21915 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - CAST(NULL AS
timestamp_ltz(9))
+-- !query schema
+struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - CAST(NULL AS
TIMESTAMP_LTZ(9))):interval day to second>
+-- !query output
+NULL
+
+
-- !query
SELECT max(c), min(c) FROM VALUES
(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'),
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
index dd16231ba635..a39b70a3d4de 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
@@ -662,6 +662,70 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111'):interval day to second>
+-- !query output
+1 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001'):interval day to second>
+-- !query output
+0 00:00:00.000000000
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789'):interval day to second>
+-- !query output
+-1 00:00:00.123456000
+
+
+-- !query
+SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9))
+-- !query schema
+struct<(CAST(2020-01-02 03:04:05.1234567 AS TIMESTAMP_NTZ(7)) -
CAST(2020-01-01 03:04:05.000000009 AS TIMESTAMP_NTZ(9))):interval day to second>
+-- !query output
+1 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05'):interval day to second>
+-- !query output
+0 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'):interval day to second>
+-- !query output
+1 00:00:00.000000000
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999'):interval day to second>
+-- !query output
+21915 00:00:00.123456000
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
timestamp_ntz(9))
+-- !query schema
+struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
TIMESTAMP_NTZ(9))):interval day to second>
+-- !query output
+NULL
+
+
-- !query
SELECT max(c), min(c) FROM VALUES
(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'),
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
index 904cffe03b44..c0a154facec2 100644
---
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
@@ -1035,7 +1035,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:10\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP_NTZ '2011-11-11 11:11:11' - 2011-11-11
11:11:10)\""
},
"queryContext" : [ {
@@ -1061,7 +1061,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"2011-11-11 11:11:11\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(2011-11-11 11:11:11 - TIMESTAMP_NTZ '2011-11-11
11:11:10')\""
},
"queryContext" : [ {
@@ -1111,7 +1111,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(str - TIMESTAMP_NTZ '2011-11-11 11:11:11')\""
},
"queryContext" : [ {
@@ -1137,7 +1137,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"str\"",
"inputType" : "\"STRING\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(TIMESTAMP_NTZ '2011-11-11 11:11:11' - str)\""
},
"queryContext" : [ {
diff --git
a/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalPrecision.sql.out
b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalPrecision.sql.out
index 54e26851ba57..d0a62b06e1c4 100644
---
a/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalPrecision.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/decimalPrecision.sql.out
@@ -1508,7 +1508,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(3,0))\"",
"inputType" : "\"DECIMAL(3,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(3,0)))\""
},
"queryContext" : [ {
@@ -1534,7 +1534,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(5,0))\"",
"inputType" : "\"DECIMAL(5,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(5,0)))\""
},
"queryContext" : [ {
@@ -1560,7 +1560,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(10,0))\"",
"inputType" : "\"DECIMAL(10,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(10,0)))\""
},
"queryContext" : [ {
@@ -1586,7 +1586,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(20,0))\"",
"inputType" : "\"DECIMAL(20,0)\"",
"paramIndex" : "second",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) - CAST(1 AS
DECIMAL(20,0)))\""
},
"queryContext" : [ {
@@ -2164,7 +2164,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(3,0))\"",
"inputType" : "\"DECIMAL(3,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(3,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
@@ -2190,7 +2190,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(5,0))\"",
"inputType" : "\"DECIMAL(5,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(5,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
@@ -2216,7 +2216,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(10,0))\"",
"inputType" : "\"DECIMAL(10,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(10,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
@@ -2242,7 +2242,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"inputSql" : "\"CAST(1 AS DECIMAL(20,0))\"",
"inputType" : "\"DECIMAL(20,0)\"",
"paramIndex" : "first",
- "requiredType" : "\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\"",
+ "requiredType" : "(\"(TIMESTAMP OR TIMESTAMP WITHOUT TIME ZONE)\" or
\"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7, 9])\")",
"sqlExpr" : "\"(CAST(1 AS DECIMAL(20,0)) - CAST(2017-12-11 09:30:00.0 AS
TIMESTAMP))\""
},
"queryContext" : [ {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
