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]

Reply via email to