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 d45deda623eb [SPARK-57825][SQL] Add +/- ANSI year-month interval
support for TIMESTAMP_NTZ/LTZ(p)
d45deda623eb is described below
commit d45deda623eb9866a94d5dd84b63a7a9d7ad2071
Author: Stevo Mitric <[email protected]>
AuthorDate: Thu Jul 30 20:50:21 2026 +0000
[SPARK-57825][SQL] Add +/- ANSI year-month interval support for
TIMESTAMP_NTZ/LTZ(p)
### What changes were proposed in this pull request?
This PR adds support for `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` (`p in [7,
9]`) in `+/- ANSI year-month interval` arithmetic.
Concretely:
- Extends `TimestampAddYMInterval` input typing to accept nanos timestamp
types alongside the existing microsecond timestamp types.
- Adds nanos-aware execution/codegen paths that apply the month shift on
epoch micros while carrying the `nanosWithinMicro` remainder through unchanged.
- Routes nanos timestamps into `TimestampAddYMInterval` from
`BinaryArithmeticWithDatetimeResolver` for both `Add` and `Subtract` (the
year-month branches previously matched only `TimestampType | TimestampNTZType`,
so nanos timestamps fell through to an unresolved `Add` / `Subtract` and failed
analysis).
- Adds a `DateTimeUtils.timestampNanosAddMonths` helper.
- Adds catalyst and SQL test coverage for NTZ/LTZ nanos year-month interval
arithmetic parity, and regenerates the impacted nanos SQL golden files.
### Why are the changes needed?
Spark already supports timestamp +/- ANSI year-month interval for the
microsecond timestamp families, and SPARK-57501 added the day-time-interval
parity for the nanos families, but year-month intervals were still unsupported
for `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` (`p in [7, 9]`). This left valid
datetime arithmetic unsupported for nanos types.
These changes close that parity gap while preserving nanos precision
semantics and existing LTZ/NTZ timezone behavior.
### Does this PR introduce _any_ user-facing change?
Yes.
`TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` (`p in [7, 9]`) now support `+/-
ANSI year-month interval` arithmetic.
Examples:
- `TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' YEAR`
- `TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - INTERVAL '1-2' YEAR
TO MONTH`
### How was this patch tested?
- `build/sbt 'catalyst/testOnly
org.apache.spark.sql.catalyst.expressions.DateExpressionsSuite
org.apache.spark.sql.catalyst.util.DateTimeUtilsSuite'`
- `SPARK_GENERATE_GOLDEN_FILES=1 build/sbt 'sql/testOnly
org.apache.spark.sql.SQLQueryTestSuite -- -z "timestamp-ntz-nanos" -z
"timestamp-ltz-nanos" -z "interval"'`
- `build/sbt 'sql/testOnly org.apache.spark.sql.SQLQueryTestSuite -- -z
"timestamp-ntz-nanos" -z "timestamp-ltz-nanos"'`
- `build/sbt catalyst/scalastyle catalyst/Test/scalastyle`
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (Opus 4.8)
Closes #57636 from stevomitric/stevomitric/spark-57825-nanos.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
(cherry picked from commit 88697a3fc93dcbb2d0e557d22131b69e4cc3a554)
Signed-off-by: Uros Bojanic <[email protected]>
---
.../BinaryArithmeticWithDatetimeResolver.scala | 8 +++
.../catalyst/expressions/datetimeExpressions.scala | 22 ++++++--
.../spark/sql/catalyst/util/DateTimeUtils.scala | 12 ++++
.../expressions/DateExpressionsSuite.scala | 66 ++++++++++++++++++++++
.../sql/catalyst/util/DateTimeUtilsSuite.scala | 43 ++++++++++++++
.../analyzer-results/timestamp-ltz-nanos.sql.out | 57 +++++++++++--------
.../analyzer-results/timestamp-ntz-nanos.sql.out | 57 +++++++++++--------
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 16 ++++--
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 16 ++++--
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 64 +++++++++++++--------
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 64 +++++++++++++--------
11 files changed, 319 insertions(+), 106 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 cdfd942ca09a..72e11082d52f 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
@@ -47,6 +47,7 @@ import org.apache.spark.sql.catalyst.expressions.{
}
import org.apache.spark.sql.types.{
AnsiIntervalType,
+ AnyTimestampNanoType,
AnyTimestampTypeExpression,
CalendarIntervalType,
DatetimeType,
@@ -75,6 +76,10 @@ object BinaryArithmeticWithDatetimeResolver {
TimestampAddYMInterval(l, r)
case (_: YearMonthIntervalType, TimestampType | TimestampNTZType) =>
TimestampAddYMInterval(r, l)
+ case (_: AnyTimestampNanoType, _: YearMonthIntervalType) =>
+ TimestampAddYMInterval(l, r)
+ case (_: YearMonthIntervalType, _: AnyTimestampNanoType) =>
+ TimestampAddYMInterval(r, l)
case (CalendarIntervalType, CalendarIntervalType) |
(_: DayTimeIntervalType, _: DayTimeIntervalType) =>
a
@@ -113,6 +118,9 @@ object BinaryArithmeticWithDatetimeResolver {
case (TimestampType | TimestampNTZType, _: YearMonthIntervalType) =>
DatetimeSub(l, r, TimestampAddYMInterval(l,
UnaryMinus(r, context.evalMode == EvalMode.ANSI)))
+ case (_: AnyTimestampNanoType, _: YearMonthIntervalType) =>
+ DatetimeSub(l, r, TimestampAddYMInterval(l,
+ UnaryMinus(r, context.evalMode == EvalMode.ANSI)))
case (CalendarIntervalType, CalendarIntervalType) |
(_: DayTimeIntervalType, _: DayTimeIntervalType) =>
s
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 835f1cda947b..0b6dcb0cd875 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
@@ -2749,7 +2749,8 @@ case class TimestampAddYMInterval(
override def toString: String = s"$left + $right"
override def sql: String = s"${left.sql} + ${right.sql}"
- override def inputTypes: Seq[AbstractDataType] = Seq(AnyTimestampType,
YearMonthIntervalType)
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(TypeCollection(AnyTimestampType, AnyTimestampNanoType),
YearMonthIntervalType)
override def dataType: DataType = timestamp.dataType
@@ -2758,16 +2759,25 @@ case class TimestampAddYMInterval(
@transient private lazy val zoneIdInEval: ZoneId =
zoneIdForType(left.dataType)
- override def nullSafeEval(micros: Any, months: Any): Any = {
- timestampAddMonths(micros.asInstanceOf[Long], months.asInstanceOf[Int],
zoneIdInEval)
+ override def nullSafeEval(start: Any, months: Any): Any = left.dataType
match {
+ case _: AnyTimestampNanoType =>
+ timestampNanosAddMonths(
+ start.asInstanceOf[TimestampNanosVal], months.asInstanceOf[Int],
zoneIdInEval)
+ case _ =>
+ timestampAddMonths(start.asInstanceOf[Long], months.asInstanceOf[Int],
zoneIdInEval)
}
override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {
val zid = ctx.addReferenceObj("zoneId", zoneIdInEval,
classOf[ZoneId].getName)
val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
- defineCodeGen(ctx, ev, (micros, months) => {
- s"""$dtu.timestampAddMonths($micros, $months, $zid)"""
- })
+ left.dataType match {
+ case _: AnyTimestampNanoType =>
+ defineCodeGen(ctx, ev, (sd, months) =>
+ s"""$dtu.timestampNanosAddMonths($sd, $months, $zid)""")
+ case _ =>
+ defineCodeGen(ctx, ev, (micros, months) =>
+ s"""$dtu.timestampAddMonths($micros, $months, $zid)""")
+ }
}
override protected def withNewChildrenInternal(
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 24aabdc988fb..d605cbae7bb4 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
@@ -283,6 +283,18 @@ object DateTimeUtils extends SparkDateTimeUtils {
instantToMicros(microsToInstant(micros).atZone(zoneId).plusMonths(months).toInstant)
}
+ /**
+ * Adds a year-month interval expressed in months to a nanosecond-precision
timestamp value while
+ * preserving the `nanosWithinMicro` remainder.
+ */
+ def timestampNanosAddMonths(
+ start: TimestampNanosVal,
+ months: Int,
+ zoneId: ZoneId): TimestampNanosVal = {
+ val epochMicros = timestampAddMonths(start.epochMicros, months, zoneId)
+ TimestampNanosVal.fromParts(epochMicros, start.nanosWithinMicro)
+ }
+
/**
* Adds a day-time interval expressed in microseconds to a timestamp at the
given time zone.
* It converts the input timestamp to a local timestamp, and adds the
interval by:
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 226165b7b36f..5feecc86bdfc 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
@@ -2341,6 +2341,72 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
assert(ltzMismatch.errorSubClass == "UNEXPECTED_INPUT_TYPE")
}
+ test("SPARK-57825: add/subtract ANSI year-month interval on nanos
timestamps") {
+ val interval = Period.ofYears(1).plusMonths(2)
+ val minusInterval = Period.ofMonths(-1)
+
+ // A year-month shift moves only the month field: the whole fraction
(including the
+ // sub-microsecond `789`) and the time of day are carried through
unchanged.
+ val ntzType = TimestampNTZNanosType(9)
+ val ntzStart = DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2020-01-02T03:04:05.123456789"), precision = 9)
+ val ntzExpectedAdd = DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2021-03-02T03:04:05.123456789"), precision = 9)
+ val ntzExpectedSub = DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2019-12-02T03:04:05.123456789"), precision = 9)
+
+ checkEvaluation(
+ TimestampAddYMInterval(Literal.create(ntzStart, ntzType),
Literal(interval), Some("UTC")),
+ ntzExpectedAdd)
+ checkEvaluation(
+ TimestampAddYMInterval(
+ Literal.create(ntzStart, ntzType),
+ UnaryMinus(Literal(interval)),
+ Some("UTC")),
+ DateTimeUtils.localDateTimeToTimestampNanos(
+ LocalDateTime.parse("2018-11-02T03:04:05.123456789"), precision = 9))
+ checkEvaluation(
+ TimestampAddYMInterval(
+ Literal.create(ntzStart, ntzType), Literal(minusInterval),
Some("UTC")),
+ ntzExpectedSub)
+ assert(ntzExpectedAdd.nanosWithinMicro == ntzStart.nanosWithinMicro)
+ assert(ntzExpectedSub.nanosWithinMicro == ntzStart.nanosWithinMicro)
+
+ val ltzType = TimestampLTZNanosType(9)
+ val ltzStart = DateTimeUtils.instantToTimestampNanos(
+ Instant.parse("2020-01-02T03:04:05.123456789Z"), precision = 9)
+ val ltzExpectedAdd = DateTimeUtils.instantToTimestampNanos(
+ Instant.parse("2021-03-02T03:04:05.123456789Z"), precision = 9)
+ val ltzExpectedSub = DateTimeUtils.instantToTimestampNanos(
+ Instant.parse("2019-12-02T03:04:05.123456789Z"), precision = 9)
+
+ checkEvaluation(
+ TimestampAddYMInterval(Literal.create(ltzStart, ltzType),
Literal(interval), Some("UTC")),
+ ltzExpectedAdd)
+ checkEvaluation(
+ TimestampAddYMInterval(
+ Literal.create(ltzStart, ltzType),
+ UnaryMinus(Literal(interval)),
+ Some("UTC")),
+ DateTimeUtils.instantToTimestampNanos(
+ Instant.parse("2018-11-02T03:04:05.123456789Z"), precision = 9))
+ checkEvaluation(
+ TimestampAddYMInterval(
+ Literal.create(ltzStart, ltzType), Literal(minusInterval),
Some("UTC")),
+ ltzExpectedSub)
+ assert(ltzExpectedAdd.nanosWithinMicro == ltzStart.nanosWithinMicro)
+ assert(ltzExpectedSub.nanosWithinMicro == ltzStart.nanosWithinMicro)
+
+ yearMonthIntervalTypes.foreach { it =>
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (ts: Expression, ym: Expression) => TimestampAddYMInterval(ts, ym,
Some("UTC")),
+ ntzType, it)
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (ts: Expression, ym: Expression) => TimestampAddYMInterval(ts, ym,
Some("UTC")),
+ ltzType, it)
+ }
+ }
+
test("SPARK-37552: convert a timestamp_ntz to another time zone") {
checkEvaluation(
ConvertTimezone(
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 196d32e1a4f9..c46daf803598 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
@@ -1199,6 +1199,49 @@ class DateTimeUtilsSuite extends SparkFunSuite with
Matchers with SQLHelper {
}
}
+ test("SPARK-57825: timestamp nanos add year-month interval preserves
nanosWithinMicro") {
+ def nanos(epochMicros: Long, nanosWithinMicro: Int): TimestampNanosVal =
+ TimestampNanosVal.fromParts(epochMicros, nanosWithinMicro.toShort)
+
+ // The epoch-micros part follows the micro `timestampAddMonths` (including
the Jan-31 -> Feb-29
+ // day clamp in a leap year) while the sub-microsecond remainder is
carried through unchanged.
+ assert(timestampNanosAddMonths(
+ nanos(date(2020, 1, 31, 12, 0, 0, 123000, LA), 789), 1, LA) ===
+ nanos(date(2020, 2, 29, 12, 0, 0, 123000, LA), 789))
+
+ outstandingZoneIds.foreach { zid =>
+ // The sub-microsecond remainder is preserved for the boundary values 0,
1 and 999.
+ Seq(0, 1, 999).foreach { rem =>
+ // Zero interval is a no-op on both the epoch-micros and the remainder.
+ assert(timestampNanosAddMonths(
+ nanos(date(2021, 3, 18, 19, 44, 1, 123456, zid), rem), 0, zid) ===
+ nanos(date(2021, 3, 18, 19, 44, 1, 123456, zid), rem))
+ // Adding whole years/months shifts only the month field, never the
fraction.
+ assert(timestampNanosAddMonths(
+ nanos(date(2020, 1, 2, 3, 4, 5, 123456, zid), rem), 14, zid) ===
+ nanos(date(2021, 3, 2, 3, 4, 5, 123456, zid), rem))
+ // Subtracting months is symmetric.
+ assert(timestampNanosAddMonths(
+ nanos(date(2020, 1, 2, 3, 4, 5, 123456, zid), rem), -1, zid) ===
+ nanos(date(2019, 12, 2, 3, 4, 5, 123456, zid), rem))
+ // Pre-epoch (negative epochMicros) value.
+ assert(timestampNanosAddMonths(
+ nanos(date(1960, 1, 2, 3, 4, 5, 123456, zid), rem), 1, zid) ===
+ nanos(date(1960, 2, 2, 3, 4, 5, 123456, zid), rem))
+ }
+ }
+
+ // Consistency with the micro helper: epochMicros matches
`timestampAddMonths` exactly and the
+ // remainder is independent of the interval amount.
+ outstandingZoneIds.foreach { zid =>
+ val start = nanos(date(2020, 1, 2, 3, 4, 5, 123456, zid), 789)
+ val months = 15
+ val result = timestampNanosAddMonths(start, months, zid)
+ assert(result.epochMicros === timestampAddMonths(start.epochMicros,
months, zid))
+ assert(result.nanosWithinMicro === start.nanosWithinMicro)
+ }
+ }
+
test("SPARK-57159: timestampNanosToEpochNanos packs into int64
epoch-nanoseconds") {
def nanos(epochMicros: Long, nanosWithinMicro: Int): TimestampNanosVal =
TimestampNanosVal.fromParts(epochMicros, nanosWithinMicro.toShort)
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 3b3b3deaab8c..5e66dade1f29 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
@@ -603,6 +603,41 @@ Project [cast(1960-01-01 19:04:05.123456789 + INTERVAL '0
00:00:00.000001' DAY T
+- OneRowRelation
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' YEAR
+-- !query analysis
+Project [2020-01-01 19:04:05.123456789 + INTERVAL '1' YEAR AS TIMESTAMP_LTZ
'2020-01-01 19:04:05.123456789' + INTERVAL '1' YEAR#x]
++- OneRowRelation
+
+
+-- !query
+SELECT INTERVAL '1' YEAR + TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC'
+-- !query analysis
+Project [2020-01-01 19:04:05.123456789 + INTERVAL '1' YEAR AS TIMESTAMP_LTZ
'2020-01-01 19:04:05.123456789' + INTERVAL '1' YEAR#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' MONTH
+-- !query analysis
+Project [2020-01-01 19:04:05.123456789 + INTERVAL '1' MONTH AS TIMESTAMP_LTZ
'2020-01-01 19:04:05.123456789' + INTERVAL '1' MONTH#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - INTERVAL '1-2' YEAR
TO MONTH
+-- !query analysis
+Project [2020-01-01 19:04:05.123456789 - INTERVAL '1-2' YEAR TO MONTH AS
TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - INTERVAL '1-2' YEAR TO MONTH#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '1960-01-31 03:04:05.123456789 UTC' + INTERVAL '1' MONTH
+-- !query analysis
+Project [1960-01-30 19:04:05.123456789 + INTERVAL '1' MONTH AS TIMESTAMP_LTZ
'1960-01-30 19:04:05.123456789' + INTERVAL '1' MONTH#x]
++- OneRowRelation
+
+
-- !query
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + make_interval(0, 1,
0, 2, 0, 0, 0)
-- !query analysis
@@ -627,28 +662,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' MONTH
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.BINARY_OP_DIFF_TYPES",
- "sqlState" : "42K09",
- "messageParameters" : {
- "left" : "\"TIMESTAMP_LTZ(9)\"",
- "right" : "\"INTERVAL MONTH\"",
- "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' + INTERVAL
'1' MONTH)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 77,
- "fragment" : "TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL
'1' MONTH"
- } ]
-}
-
-
-- !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 bc9fc002ca05..0026a2707400 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
@@ -530,6 +530,41 @@ Project [cast(1960-01-02 03:04:05.123456789 + INTERVAL '0
00:00:00.000001' DAY T
+- OneRowRelation
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' YEAR
+-- !query analysis
+Project [2020-01-02 03:04:05.123456789 + INTERVAL '1' YEAR AS TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789' + INTERVAL '1' YEAR#x]
++- OneRowRelation
+
+
+-- !query
+SELECT INTERVAL '1' YEAR + TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789'
+-- !query analysis
+Project [2020-01-02 03:04:05.123456789 + INTERVAL '1' YEAR AS TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789' + INTERVAL '1' YEAR#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' MONTH
+-- !query analysis
+Project [2020-01-02 03:04:05.123456789 + INTERVAL '1' MONTH AS TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789' + INTERVAL '1' MONTH#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - INTERVAL '1-2' YEAR TO
MONTH
+-- !query analysis
+Project [2020-01-02 03:04:05.123456789 - INTERVAL '1-2' YEAR TO MONTH AS
TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - INTERVAL '1-2' YEAR TO MONTH#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '1960-01-31 03:04:05.123456789' + INTERVAL '1' MONTH
+-- !query analysis
+Project [1960-01-31 03:04:05.123456789 + INTERVAL '1' MONTH AS TIMESTAMP_NTZ
'1960-01-31 03:04:05.123456789' + INTERVAL '1' MONTH#x]
++- OneRowRelation
+
+
-- !query
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + make_interval(0, 1, 0,
2, 0, 0, 0)
-- !query analysis
@@ -554,28 +589,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' MONTH
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.BINARY_OP_DIFF_TYPES",
- "sqlState" : "42K09",
- "messageParameters" : {
- "left" : "\"TIMESTAMP_NTZ(9)\"",
- "right" : "\"INTERVAL MONTH\"",
- "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL
'1' MONTH)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 73,
- "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1'
MONTH"
- } ]
-}
-
-
-- !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/inputs/timestamp-ltz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
index e0e84fe0b3cb..62ae8ace49b8 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
@@ -168,11 +168,19 @@ SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' -
INTERVAL '1 00:04:00.000321' DAY TO SECOND;
SELECT TIMESTAMP_LTZ '1960-01-02 03:04:05.123456789 UTC' +
INTERVAL '0 00:00:00.000001' DAY TO SECOND;
--- SPARK-57501: nanos timestamps support only ANSI day-time intervals. A
(legacy) calendar interval
--- is rejected by TimestampAddInterval's type check, and a year-month interval
has no supported
--- operator overload.
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + make_interval(0, 1,
0, 2, 0, 0, 0);
+-- SPARK-57825: TIMESTAMP_LTZ(p) +/- ANSI year-month interval keeps the nanos
type/precision and
+-- carries the whole fraction (including the sub-microsecond digits) through
unchanged; the month
+-- shift is applied on the session-local wall clock.
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' YEAR;
+-- The interval-first operand order resolves to the same addition.
+SELECT INTERVAL '1' YEAR + TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC';
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' MONTH;
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - INTERVAL '1-2' YEAR
TO MONTH;
+-- Jan-31 -> Feb-29 day clamp on a pre-epoch (leap-year) value.
+SELECT TIMESTAMP_LTZ '1960-01-31 03:04:05.123456789 UTC' + INTERVAL '1' MONTH;
+-- SPARK-57501, SPARK-57825: nanos timestamps support ANSI day-time and
year-month intervals; the
+-- 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-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
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 3ef269f8eb1a..c80f0c236e59 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
@@ -144,11 +144,19 @@ SELECT named_struct('f', DATE '2020-01-01') :: struct<f:
timestamp_ntz(9)>;
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '2
00:03:00.000456' DAY TO SECOND;
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - INTERVAL '1
00:04:00.000321' DAY TO SECOND;
SELECT TIMESTAMP_NTZ '1960-01-02 03:04:05.123456789' + INTERVAL '0
00:00:00.000001' DAY TO SECOND;
--- SPARK-57501: nanos timestamps support only ANSI day-time intervals. A
(legacy) calendar interval
--- is rejected by TimestampAddInterval's type check, and a year-month interval
has no supported
--- operator overload.
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + make_interval(0, 1, 0,
2, 0, 0, 0);
+-- SPARK-57825: TIMESTAMP_NTZ(p) +/- ANSI year-month interval keeps the nanos
type/precision and
+-- carries the whole fraction (including the sub-microsecond digits) through
unchanged; a month
+-- shift never touches the time of day.
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' YEAR;
+-- The interval-first operand order resolves to the same addition.
+SELECT INTERVAL '1' YEAR + TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789';
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' MONTH;
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - INTERVAL '1-2' YEAR TO
MONTH;
+-- Jan-31 -> Feb-29 day clamp on a pre-epoch (leap-year) value.
+SELECT TIMESTAMP_NTZ '1960-01-31 03:04:05.123456789' + INTERVAL '1' MONTH;
+-- SPARK-57501, SPARK-57825: nanos timestamps support ANSI day-time and
year-month intervals; the
+-- 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-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
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 62a4b8448647..12aaa0cd1a51 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
@@ -678,6 +678,46 @@ struct<TIMESTAMP_LTZ '1960-01-01 19:04:05.123456789' +
INTERVAL '0 00:00:00.0000
1960-01-01 19:04:05.123457789
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' YEAR
+-- !query schema
+struct<TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' + INTERVAL '1'
YEAR:timestamp_ltz(9)>
+-- !query output
+2021-01-01 19:04:05.123456789
+
+
+-- !query
+SELECT INTERVAL '1' YEAR + TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC'
+-- !query schema
+struct<TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' + INTERVAL '1'
YEAR:timestamp_ltz(9)>
+-- !query output
+2021-01-01 19:04:05.123456789
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' MONTH
+-- !query schema
+struct<TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' + INTERVAL '1'
MONTH:timestamp_ltz(9)>
+-- !query output
+2020-02-01 19:04:05.123456789
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - INTERVAL '1-2' YEAR
TO MONTH
+-- !query schema
+struct<TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - INTERVAL '1-2' YEAR TO
MONTH:timestamp_ltz(9)>
+-- !query output
+2018-11-01 19:04:05.123456789
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '1960-01-31 03:04:05.123456789 UTC' + INTERVAL '1' MONTH
+-- !query schema
+struct<TIMESTAMP_LTZ '1960-01-30 19:04:05.123456789' + INTERVAL '1'
MONTH:timestamp_ltz(9)>
+-- !query output
+1960-02-29 19:04:05.123456789
+
+
-- !query
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + make_interval(0, 1,
0, 2, 0, 0, 0)
-- !query schema
@@ -704,30 +744,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL '1' MONTH
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.BINARY_OP_DIFF_TYPES",
- "sqlState" : "42K09",
- "messageParameters" : {
- "left" : "\"TIMESTAMP_LTZ(9)\"",
- "right" : "\"INTERVAL MONTH\"",
- "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' + INTERVAL
'1' MONTH)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 77,
- "fragment" : "TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + INTERVAL
'1' MONTH"
- } ]
-}
-
-
-- !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 a0d1aa9db7fd..dd74d5e0744e 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
@@ -596,6 +596,46 @@ struct<TIMESTAMP_NTZ '1960-01-02 03:04:05.123456789' +
INTERVAL '0 00:00:00.0000
1960-01-02 03:04:05.123457789
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' YEAR
+-- !query schema
+struct<TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1'
YEAR:timestamp_ntz(9)>
+-- !query output
+2021-01-02 03:04:05.123456789
+
+
+-- !query
+SELECT INTERVAL '1' YEAR + TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789'
+-- !query schema
+struct<TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1'
YEAR:timestamp_ntz(9)>
+-- !query output
+2021-01-02 03:04:05.123456789
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' MONTH
+-- !query schema
+struct<TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1'
MONTH:timestamp_ntz(9)>
+-- !query output
+2020-02-02 03:04:05.123456789
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - INTERVAL '1-2' YEAR TO
MONTH
+-- !query schema
+struct<TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - INTERVAL '1-2' YEAR TO
MONTH:timestamp_ntz(9)>
+-- !query output
+2018-11-02 03:04:05.123456789
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '1960-01-31 03:04:05.123456789' + INTERVAL '1' MONTH
+-- !query schema
+struct<TIMESTAMP_NTZ '1960-01-31 03:04:05.123456789' + INTERVAL '1'
MONTH:timestamp_ntz(9)>
+-- !query output
+1960-02-29 03:04:05.123456789
+
+
-- !query
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + make_interval(0, 1, 0,
2, 0, 0, 0)
-- !query schema
@@ -622,30 +662,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1' MONTH
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.BINARY_OP_DIFF_TYPES",
- "sqlState" : "42K09",
- "messageParameters" : {
- "left" : "\"TIMESTAMP_NTZ(9)\"",
- "right" : "\"INTERVAL MONTH\"",
- "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL
'1' MONTH)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 73,
- "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '1'
MONTH"
- } ]
-}
-
-
-- !query
SELECT max(c), min(c) FROM VALUES
(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'),
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]