This is an automated email from the ASF dual-hosted git repository.

uros-b pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/master by this push:
     new 18b2feb67145 [SPARK-60543][SQL] Support nanosecond-precision 
timestamps in date_add/date_sub
18b2feb67145 is described below

commit 18b2feb67145ff8262fa4f6ad146cf4863687d50
Author: Stevo Mitric <[email protected]>
AuthorDate: Mon Oct 26 11:16:05 2026 +0000

    [SPARK-60543][SQL] Support nanosecond-precision timestamps in 
date_add/date_sub
    
    ### What changes were proposed in this pull request?
    
    `date_add` / `date_sub` (and the `dateadd` alias) take a DATE start and 
rely on the `DateTimeOperations` coercion rule to cast a timestamp start to 
DATE. That rule matched only the microsecond timestamp types, so 
`TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` (p in [7, 9]) failed with 
`DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE` in both ANSI modes.
    
    This PR makes the `DateAdd` / `DateSub` cases of 
`DateTimeOperationsTypeCoercion` and `AnsiDateTimeOperationsTypeCoercion` also 
match `AnyTimestampNanoTypeExpression`, the same pair of extractors 
`AnsiGetDateFieldOperationsTypeCoercion` uses for the date field functions 
(SPARK-57469). The nanosecond start is cast to DATE exactly like a microsecond 
one: the sub-day digits never reach a DATE result, and `TIMESTAMP_LTZ(p)` 
resolves the date in the session time zone.
    
    ### Why are the changes needed?
    
    Part of SPARK-56822. `date_add(TIMESTAMP_NTZ '2020-01-01 
00:00:00.123456789', 1)` failed while the microsecond equivalent returns 
`2020-01-02` and the sibling date-valued functions (`add_months`, `last_day`, 
`next_day`, `datediff`, `to_date`, `trunc`) already accept nanosecond 
timestamps. That inconsistency is what the SPIP's parity criterion rules out. 
With this change every DATE-input function agrees between microsecond and 
nanosecond timestamps in both ANSI modes.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes, only for the nanosecond timestamp types, which are behind the preview 
flag `spark.sql.timestampNanosTypes.enabled`: `date_add` / `date_sub` / 
`dateadd` now accept them instead of failing. Nothing changes for DATE, string 
or microsecond timestamp inputs.
    
    ### How was this patch tested?
    
    - `TypeCoercionSuite` / `AnsiTypeCoercionSuite` "Datetime operations": 
`DateAdd` / `DateSub` over NTZ and LTZ nanos get a `Cast(_, DateType)` start in 
both rule sets.
    - New `TimestampNanosFunctionsSuiteBase` tests (ANSI on and off): NTZ/LTZ 
at p=7..9 match the microsecond path, including LTZ in session zones behind, at 
and ahead of UTC, plus the Column API and NULLs; new golden cases in 
`timestamp-ntz-nanos.sql` / `timestamp-ltz-nanos.sql` for both ANSI modes.
    - Existing `DateExpressionsSuite` and the `date.sql` / `timestamp.sql` / 
`timestamp-ntz.sql` golden files pass unchanged.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Isaac
    
    Closes #59782 from stevomitric/stevomitric/nanos-date-add-sub.
    
    Authored-by: Stevo Mitric <[email protected]>
    Signed-off-by: Uros Bojanic <[email protected]>
---
 .../sql/catalyst/analysis/TypeCoercionHelper.scala |   9 +-
 .../sql/catalyst/analysis/TypeCoercionSuite.scala  |  10 ++
 .../analyzer-results/timestamp-ltz-nanos.sql.out   | 122 +++++++++++++++++
 .../analyzer-results/timestamp-ntz-nanos.sql.out   |  84 ++++++++++++
 .../sql-tests/inputs/timestamp-ltz-nanos.sql       |  26 ++++
 .../sql-tests/inputs/timestamp-ntz-nanos.sql       |  19 +++
 .../sql-tests/results/timestamp-ltz-nanos.sql.out  | 146 +++++++++++++++++++++
 .../sql-tests/results/timestamp-ntz-nanos.sql.out  |  98 ++++++++++++++
 .../sql/TimestampNanosFunctionsSuiteBase.scala     |  94 ++++++++++++-
 9 files changed, 601 insertions(+), 7 deletions(-)

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 973c3788a4e1..1939e191c92e 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
@@ -71,6 +71,7 @@ import org.apache.spark.sql.types.{
   AbstractDataType,
   AnyDataType,
   AnyTimestampNanoType,
+  AnyTimestampNanoTypeExpression,
   AnyTimestampTypeExpression,
   ArrayType,
   BinaryType,
@@ -897,10 +898,10 @@ abstract class TypeCoercionHelper {
    */
   object DateTimeOperationsTypeCoercion {
     def apply(expression: Expression): Expression = expression match {
-      case d @ DateAdd(AnyTimestampTypeExpression(), _) =>
+      case d @ DateAdd(AnyTimestampTypeExpression() | 
AnyTimestampNanoTypeExpression(), _) =>
         d.copy(startDate = Cast(d.startDate, DateType))
       case d @ DateAdd(StringTypeExpression(), _) => d.copy(startDate = 
Cast(d.startDate, DateType))
-      case d @ DateSub(AnyTimestampTypeExpression(), _) =>
+      case d @ DateSub(AnyTimestampTypeExpression() | 
AnyTimestampNanoTypeExpression(), _) =>
         d.copy(startDate = Cast(d.startDate, DateType))
       case d @ DateSub(StringTypeExpression(), _) => d.copy(startDate = 
Cast(d.startDate, DateType))
 
@@ -931,9 +932,9 @@ abstract class TypeCoercionHelper {
    */
   object AnsiDateTimeOperationsTypeCoercion {
     def apply(expression: Expression): Expression = expression match {
-      case d @ DateAdd(AnyTimestampTypeExpression(), _) =>
+      case d @ DateAdd(AnyTimestampTypeExpression() | 
AnyTimestampNanoTypeExpression(), _) =>
         d.copy(startDate = Cast(d.startDate, DateType))
-      case d @ DateSub(AnyTimestampTypeExpression(), _) =>
+      case d @ DateSub(AnyTimestampTypeExpression() | 
AnyTimestampNanoTypeExpression(), _) =>
         d.copy(startDate = Cast(d.startDate, DateType))
 
       case s @ SubtractTimestamps(DateTypeExpression(), r, _, _)
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 7aecd76c0faf..fb9cc87257e6 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
@@ -656,6 +656,16 @@ abstract class TypeCoercionSuiteBase extends AnalysisTest {
       SubtractTimestamps(
         Cast(timestampNTZLiteral, TimestampNTZNanosType(9)),
         Cast(ltzNanos9, TimestampNTZNanosType(9))))
+
+    // SPARK-60543: date_add/date_sub cast a nanos start to DATE, like the 
micro types.
+    Seq(ntzNanos9, ltzNanos9, ntzNanos7).foreach { nanosLiteral =>
+      ruleTest(rule,
+        DateAdd(nanosLiteral, intLiteral),
+        DateAdd(Cast(nanosLiteral, DateType), intLiteral))
+      ruleTest(rule,
+        DateSub(nanosLiteral, intLiteral),
+        DateSub(Cast(nanosLiteral, DateType), intLiteral))
+    }
   }
 
   test("datetime comparison") {
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 6bbd4ff1ee0a..686bba8e71ed 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
@@ -579,6 +579,128 @@ SELECT named_struct('f', DATE '2020-01-01') :: struct<f: 
timestamp_ltz(9)>
 [Analyzer test output redacted due to nondeterminism]
 
 
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-02-28 23:59:59.999999999', 1)
+-- !query analysis
+Project [date_add(cast(2020-02-28 23:59:59.999999999 as date), 1) AS 
date_add(TIMESTAMP_LTZ '2020-02-28 23:59:59.999999999', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_sub(TIMESTAMP_LTZ '2020-03-01 00:00:00.000000001', 1)
+-- !query analysis
+Project [date_sub(cast(2020-03-01 00:00:00.000000001 as date), 1) AS 
date_sub(TIMESTAMP_LTZ '2020-03-01 00:00:00.000000001', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT dateadd('2020-01-31 12:00:00.1234567' :: timestamp_ltz(7), 30)
+-- !query analysis
+Project [date_add(cast(cast(2020-01-31 12:00:00.1234567 as timestamp_ltz(7)) 
as date), 30) AS date_add(CAST(2020-01-31 12:00:00.1234567 AS 
TIMESTAMP_LTZ(7)), 30)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_sub('2020-01-01 12:00:00.12345678' :: timestamp_ltz(8), -1)
+-- !query analysis
+Project [date_sub(cast(cast(2020-01-01 12:00:00.12345678 as timestamp_ltz(8)) 
as date), -1) AS date_sub(CAST(2020-01-01 12:00:00.12345678 AS 
TIMESTAMP_LTZ(8)), -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query analysis
+Project [date_add(cast(2020-01-01 00:00:00.123456789 as date), cast(2 as int)) 
AS date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', 2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query analysis
+Project [date_add(cast(2019-12-31 20:00:00.123456789 as date), 1) AS 
date_add(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query analysis
+Project [date_sub(cast(2019-12-31 20:00:00.123456789 as date), 1) AS 
date_sub(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1),
+       date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SET spark.sql.session.timeZone=UTC
+-- !query analysis
+SetCommand (spark.sql.session.timeZone,Some(UTC))
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query analysis
+Project [date_add(cast(2019-12-31 20:00:00.123456789 as date), 1) AS 
date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SET spark.sql.session.timeZone=America/Los_Angeles
+-- !query analysis
+SetCommand (spark.sql.session.timeZone,Some(America/Los_Angeles))
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '1969-12-31 23:59:59.999999999 UTC', 1)
+-- !query analysis
+Project [date_add(cast(1969-12-31 15:59:59.999999999 as date), 1) AS 
date_add(TIMESTAMP_LTZ '1969-12-31 15:59:59.999999999', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(NULL :: timestamp_ltz(9), 1), date_sub(NULL :: 
timestamp_ltz(9), 1)
+-- !query analysis
+Project [date_add(cast(cast(null as timestamp_ltz(9)) as date), 1) AS 
date_add(CAST(NULL AS TIMESTAMP_LTZ(9)), 1)#x, date_sub(cast(cast(null as 
timestamp_ltz(9)) as date), 1) AS date_sub(CAST(NULL AS TIMESTAMP_LTZ(9)), 1)#x]
++- OneRowRelation
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(false))
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1),
+       date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query analysis
+Project [date_add(cast(2019-12-31 20:00:00.123456789 as date), 1) AS 
date_add(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 1)#x, 
date_sub(cast(2019-12-31 20:00:00.123456789 as date), 1) AS 
date_sub(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query analysis
+Project [date_add(cast(2020-01-01 00:00:00.123456789 as date), 2) AS 
date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', 2)#x]
++- OneRowRelation
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(true))
+
+
 -- !query
 SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' +
   INTERVAL '2 00:03:00.000456' DAY TO SECOND
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 2b14e2b8a653..325ab70f3414 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
@@ -509,6 +509,90 @@ SELECT named_struct('f', DATE '2020-01-01') :: struct<f: 
timestamp_ntz(9)>
 [Analyzer test output redacted due to nondeterminism]
 
 
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1)
+-- !query analysis
+Project [date_add(cast(2020-02-28 23:59:59.999999999 as date), 1) AS 
date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1)
+-- !query analysis
+Project [date_sub(cast(2020-03-01 00:00:00.000000001 as date), 1) AS 
date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT dateadd('2020-01-31 12:00:00.1234567' :: timestamp_ntz(7), 30)
+-- !query analysis
+Project [date_add(cast(cast(2020-01-31 12:00:00.1234567 as timestamp_ntz(7)) 
as date), 30) AS date_add(CAST(2020-01-31 12:00:00.1234567 AS 
TIMESTAMP_NTZ(7)), 30)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_sub('2020-01-01 12:00:00.12345678' :: timestamp_ntz(8), -1)
+-- !query analysis
+Project [date_sub(cast(cast(2020-01-01 12:00:00.12345678 as timestamp_ntz(8)) 
as date), -1) AS date_sub(CAST(2020-01-01 12:00:00.12345678 AS 
TIMESTAMP_NTZ(8)), -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query analysis
+Project [date_add(cast(2020-01-01 00:00:00.123456789 as date), cast(2 as int)) 
AS date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '1969-12-31 23:59:59.999999999', 1)
+-- !query analysis
+Project [date_add(cast(1969-12-31 23:59:59.999999999 as date), 1) AS 
date_add(TIMESTAMP_NTZ '1969-12-31 23:59:59.999999999', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 1),
+       date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456', 1)
+-- !query analysis
+Project [date_add(cast(2020-01-01 00:00:00.123456789 as date), 1) AS 
date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 1)#x, 
date_add(cast(2020-01-01 00:00:00.123456 as date), 1) AS date_add(TIMESTAMP_NTZ 
'2020-01-01 00:00:00.123456', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(NULL :: timestamp_ntz(9), 1), date_sub(NULL :: 
timestamp_ntz(9), 1)
+-- !query analysis
+Project [date_add(cast(cast(null as timestamp_ntz(9)) as date), 1) AS 
date_add(CAST(NULL AS TIMESTAMP_NTZ(9)), 1)#x, date_sub(cast(cast(null as 
timestamp_ntz(9)) as date), 1) AS date_sub(CAST(NULL AS TIMESTAMP_NTZ(9)), 1)#x]
++- OneRowRelation
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(false))
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1),
+       date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1)
+-- !query analysis
+Project [date_add(cast(2020-02-28 23:59:59.999999999 as date), 1) AS 
date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1)#x, 
date_sub(cast(2020-03-01 00:00:00.000000001 as date), 1) AS 
date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query analysis
+Project [date_add(cast(2020-01-01 00:00:00.123456789 as date), 2) AS 
date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 2)#x]
++- OneRowRelation
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(true))
+
+
 -- !query
 SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '2 
00:03:00.000456' DAY TO SECOND
 -- !query analysis
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 f46528745b86..90162d505124 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
@@ -161,6 +161,32 @@ SELECT map('k', TIMESTAMP_LTZ '2020-01-01 
12:30:15.123456789') :: map<string, da
 SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ltz(9), string>;
 SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ltz(9)>;
 
+-- SPARK-60543: date_add / date_sub (and the dateadd alias) over 
TIMESTAMP_LTZ(p) cast the start to
+-- DATE in the session zone like the micro type, so the sub-microsecond digits 
never affect the
+-- result.
+SELECT date_add(TIMESTAMP_LTZ '2020-02-28 23:59:59.999999999', 1);
+SELECT date_sub(TIMESTAMP_LTZ '2020-03-01 00:00:00.000000001', 1);
+SELECT dateadd('2020-01-31 12:00:00.1234567' :: timestamp_ltz(7), 30);
+SELECT date_sub('2020-01-01 12:00:00.12345678' :: timestamp_ltz(8), -1);
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', '2');
+-- 04:00 UTC on Jan 1 is Dec 31 in the session zone (America/Los_Angeles), as 
for the micro type.
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1);
+SELECT date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1);
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1),
+       date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1);
+SET spark.sql.session.timeZone=UTC;
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1);
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1);
+SET spark.sql.session.timeZone=America/Los_Angeles;
+-- Pre-epoch value exercises the negative-epoch path.
+SELECT date_add(TIMESTAMP_LTZ '1969-12-31 23:59:59.999999999 UTC', 1);
+SELECT date_add(NULL :: timestamp_ltz(9), 1), date_sub(NULL :: 
timestamp_ltz(9), 1);
+SET spark.sql.ansi.enabled=false;
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1),
+       date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1);
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', '2');
+SET spark.sql.ansi.enabled=true;
+
 -- SPARK-57501: TIMESTAMP_LTZ(p) +/- ANSI day-time interval preserves nanos 
remainder.
 SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' +
   INTERVAL '2 00:03:00.000456' DAY TO SECOND;
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 3e5c2143d6a1..6aa4ec03a020 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
@@ -140,6 +140,25 @@ SELECT map('k', TIMESTAMP_NTZ '2020-01-01 
12:30:15.123456789') :: map<string, da
 SELECT map(DATE '2020-01-01', 'v') :: map<timestamp_ntz(9), string>;
 SELECT named_struct('f', DATE '2020-01-01') :: struct<f: timestamp_ntz(9)>;
 
+-- SPARK-60543: date_add / date_sub (and the dateadd alias) over 
TIMESTAMP_NTZ(p) cast the start to
+-- DATE like the micro types, so the time of day and sub-microsecond digits 
never affect the result.
+SELECT date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1);
+SELECT date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1);
+SELECT dateadd('2020-01-31 12:00:00.1234567' :: timestamp_ntz(7), 30);
+SELECT date_sub('2020-01-01 12:00:00.12345678' :: timestamp_ntz(8), -1);
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', '2');
+-- Pre-epoch value exercises the negative-epoch path.
+SELECT date_add(TIMESTAMP_NTZ '1969-12-31 23:59:59.999999999', 1);
+-- Same result as the micro type.
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 1),
+       date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456', 1);
+SELECT date_add(NULL :: timestamp_ntz(9), 1), date_sub(NULL :: 
timestamp_ntz(9), 1);
+SET spark.sql.ansi.enabled=false;
+SELECT date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1),
+       date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1);
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', '2');
+SET spark.sql.ansi.enabled=true;
+
 -- SPARK-57501: TIMESTAMP_NTZ(p) +/- ANSI day-time interval preserves nanos 
remainder.
 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;
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 148b9938e99c..45cc4763847f 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
@@ -651,6 +651,152 @@ struct<named_struct(f, DATE 
'2020-01-01'):struct<f:timestamp_ltz(9)>>
 {"f":2020-01-01 00:00:00}
 
 
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-02-28 23:59:59.999999999', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_LTZ '2020-02-28 23:59:59.999999999', 1):date>
+-- !query output
+2020-02-29
+
+
+-- !query
+SELECT date_sub(TIMESTAMP_LTZ '2020-03-01 00:00:00.000000001', 1)
+-- !query schema
+struct<date_sub(TIMESTAMP_LTZ '2020-03-01 00:00:00.000000001', 1):date>
+-- !query output
+2020-02-29
+
+
+-- !query
+SELECT dateadd('2020-01-31 12:00:00.1234567' :: timestamp_ltz(7), 30)
+-- !query schema
+struct<date_add(CAST(2020-01-31 12:00:00.1234567 AS TIMESTAMP_LTZ(7)), 
30):date>
+-- !query output
+2020-03-01
+
+
+-- !query
+SELECT date_sub('2020-01-01 12:00:00.12345678' :: timestamp_ltz(8), -1)
+-- !query schema
+struct<date_sub(CAST(2020-01-01 12:00:00.12345678 AS TIMESTAMP_LTZ(8)), 
-1):date>
+-- !query output
+2020-01-02
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query schema
+struct<date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', 2):date>
+-- !query output
+2020-01-03
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 1):date>
+-- !query output
+2020-01-01
+
+
+-- !query
+SELECT date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query schema
+struct<date_sub(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 1):date>
+-- !query output
+2019-12-30
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1),
+       date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1)
+-- !query schema
+struct<date_add(TIMESTAMP '2019-12-31 20:00:00.123456', 
1):date,date_sub(TIMESTAMP '2019-12-31 20:00:00.123456', 1):date>
+-- !query output
+2020-01-01     2019-12-30
+
+
+-- !query
+SET spark.sql.session.timeZone=UTC
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.session.timeZone     UTC
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789', 1):date>
+-- !query output
+2020-01-02
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456 UTC', 1)
+-- !query schema
+struct<date_add(TIMESTAMP '2020-01-01 04:00:00.123456', 1):date>
+-- !query output
+2020-01-02
+
+
+-- !query
+SET spark.sql.session.timeZone=America/Los_Angeles
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.session.timeZone     America/Los_Angeles
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '1969-12-31 23:59:59.999999999 UTC', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_LTZ '1969-12-31 15:59:59.999999999', 1):date>
+-- !query output
+1970-01-01
+
+
+-- !query
+SELECT date_add(NULL :: timestamp_ltz(9), 1), date_sub(NULL :: 
timestamp_ltz(9), 1)
+-- !query schema
+struct<date_add(CAST(NULL AS TIMESTAMP_LTZ(9)), 1):date,date_sub(CAST(NULL AS 
TIMESTAMP_LTZ(9)), 1):date>
+-- !query output
+NULL   NULL
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled false
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1),
+       date_sub(TIMESTAMP_LTZ '2020-01-01 04:00:00.123456789 UTC', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 
1):date,date_sub(TIMESTAMP_LTZ '2019-12-31 20:00:00.123456789', 1):date>
+-- !query output
+2020-01-01     2019-12-30
+
+
+-- !query
+SELECT date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query schema
+struct<date_add(TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789', 2):date>
+-- !query output
+2020-01-03
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled true
+
+
 -- !query
 SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' +
   INTERVAL '2 00:03:00.000456' DAY TO SECOND
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 b575aa965d67..a36f137d2b7f 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
@@ -572,6 +572,104 @@ struct<named_struct(f, DATE 
'2020-01-01'):struct<f:timestamp_ntz(9)>>
 {"f":2020-01-01 00:00:00}
 
 
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1):date>
+-- !query output
+2020-02-29
+
+
+-- !query
+SELECT date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1)
+-- !query schema
+struct<date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1):date>
+-- !query output
+2020-02-29
+
+
+-- !query
+SELECT dateadd('2020-01-31 12:00:00.1234567' :: timestamp_ntz(7), 30)
+-- !query schema
+struct<date_add(CAST(2020-01-31 12:00:00.1234567 AS TIMESTAMP_NTZ(7)), 
30):date>
+-- !query output
+2020-03-01
+
+
+-- !query
+SELECT date_sub('2020-01-01 12:00:00.12345678' :: timestamp_ntz(8), -1)
+-- !query schema
+struct<date_sub(CAST(2020-01-01 12:00:00.12345678 AS TIMESTAMP_NTZ(8)), 
-1):date>
+-- !query output
+2020-01-02
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query schema
+struct<date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 2):date>
+-- !query output
+2020-01-03
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '1969-12-31 23:59:59.999999999', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_NTZ '1969-12-31 23:59:59.999999999', 1):date>
+-- !query output
+1970-01-01
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 1),
+       date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 
1):date,date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456', 1):date>
+-- !query output
+2020-01-02     2020-01-02
+
+
+-- !query
+SELECT date_add(NULL :: timestamp_ntz(9), 1), date_sub(NULL :: 
timestamp_ntz(9), 1)
+-- !query schema
+struct<date_add(CAST(NULL AS TIMESTAMP_NTZ(9)), 1):date,date_sub(CAST(NULL AS 
TIMESTAMP_NTZ(9)), 1):date>
+-- !query output
+NULL   NULL
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled false
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 1),
+       date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1)
+-- !query schema
+struct<date_add(TIMESTAMP_NTZ '2020-02-28 23:59:59.999999999', 
1):date,date_sub(TIMESTAMP_NTZ '2020-03-01 00:00:00.000000001', 1):date>
+-- !query output
+2020-02-29     2020-02-29
+
+
+-- !query
+SELECT date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', '2')
+-- !query schema
+struct<date_add(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789', 2):date>
+-- !query output
+2020-01-03
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled true
+
+
 -- !query
 SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + INTERVAL '2 
00:03:00.000456' DAY TO SECOND
 -- !query schema
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
index bf3e19f7a604..565bdeb5e2ac 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
@@ -31,9 +31,9 @@ import org.apache.spark.sql.types._
  * End-to-end tests over the nanosecond-precision timestamp types 
`TIMESTAMP_NTZ(p)` /
  * `TIMESTAMP_LTZ(p)` (`p` in `[7, 9]`), part of the nanosecond timestamp 
preview (SPARK-56822).
  * Covers the datetime functions (`hour`/`minute`/`second`, 
`EXTRACT`/`date_part`, the date-field
- * functions) and the `MIN`/`MAX` aggregates (plus 
`min_by`/`max_by`/`greatest`/`least`). Most
- * tests use the SQL path (`selectExpr`); several also cross-check the Scala 
`Column` API. The two
- * subclasses run every test with ANSI mode on and off.
+ * functions, `date_add`/`date_sub`) and the `MIN`/`MAX` aggregates (plus 
`min_by`/`max_by`/
+ * `greatest`/`least`). Most tests use the SQL path (`selectExpr`); several 
also cross-check the
+ * Scala `Column` API. The two subclasses run every test with ANSI mode on and 
off.
  */
 abstract class TimestampNanosFunctionsSuiteBase extends SharedSparkSession {
 
@@ -244,6 +244,94 @@ abstract class TimestampNanosFunctionsSuiteBase extends 
SharedSparkSession {
     }
   }
 
+  // SPARK-60543: date_add / date_sub (and the dateadd alias) cast a nanos 
start to DATE like the
+  // micro types, so the precision, time-of-day and sub-microsecond digits 
never change the result.
+  private val dateAddSubExprs = Seq(
+    "date_add(c, 1)", "date_sub(c, 1)", "dateadd(c, -31)", "date_add(c, '2')", 
"date_sub(c, 366)")
+
+  test("SPARK-60543: date_add/date_sub over nanosecond TIMESTAMP_NTZ match the 
micro path") {
+    val ldts = Seq(
+      "2020-02-28T23:59:59.999999999",
+      "2021-01-01T00:00:00.000000001",
+      "1969-12-31T23:59:59.999999999",
+      "1960-07-15T06:07:08.123456789")
+    ldts.foreach { s =>
+      val micro = spark.createDataFrame(
+        spark.sparkContext.parallelize(Seq(Row(LocalDateTime.parse(s)))),
+        new StructType().add("c", 
TimestampNTZType)).selectExpr(dateAddSubExprs: _*).collect()
+      Seq(7, 8, 9).foreach { p =>
+        checkAnswer(ntzNanos(s, p).selectExpr(dateAddSubExprs: _*), 
micro.toSeq)
+      }
+    }
+    Seq(7, 8, 9).foreach { p =>
+      checkAnswer(
+        ntzNanos("2020-02-28T23:59:59.999999999", p).selectExpr("date_add(c, 
1)", "date_sub(c, 1)"),
+        Row(java.sql.Date.valueOf("2020-02-29"), 
java.sql.Date.valueOf("2020-02-27")))
+    }
+  }
+
+  test("SPARK-60543: date_add/date_sub over nanosecond TIMESTAMP_LTZ match the 
micro path") {
+    // In America/Los_Angeles the second and third instants fall on the 
previous calendar day; in
+    // Asia/Tokyo the last one falls on the next day.
+    val instants = Seq(
+      "2020-02-29T12:34:56.123456789Z",
+      "2020-01-01T04:00:00.123456789Z",
+      "2020-03-01T07:59:59.999999999Z",
+      "1960-07-15T06:07:08.123456789Z",
+      "2020-02-29T20:00:00.123456789Z")
+    Seq("America/Los_Angeles", "UTC", "Asia/Tokyo").foreach { zone =>
+      withSQLConf(SQLConf.SESSION_LOCAL_TIMEZONE.key -> zone) {
+        instants.foreach { s =>
+          val micro = spark.createDataFrame(
+            spark.sparkContext.parallelize(Seq(Row(Instant.parse(s)))),
+            new StructType().add("c", 
TimestampType)).selectExpr(dateAddSubExprs: _*).collect()
+          Seq(7, 8, 9).foreach { p =>
+            checkAnswer(ltzNanos(s, p).selectExpr(dateAddSubExprs: _*), 
micro.toSeq)
+          }
+        }
+      }
+    }
+    // 2020-01-01T04:00Z is 2019-12-31 in America/Los_Angeles but 2020-01-01 
in UTC, and
+    // 2020-02-29T20:00Z is 2020-03-01 in Asia/Tokyo.
+    checkAnswer(
+      ltzNanos("2020-01-01T04:00:00.123456789Z", 9).selectExpr("date_add(c, 
1)"),
+      Row(java.sql.Date.valueOf("2020-01-01")))
+    withSQLConf(SQLConf.SESSION_LOCAL_TIMEZONE.key -> "UTC") {
+      checkAnswer(
+        ltzNanos("2020-01-01T04:00:00.123456789Z", 9).selectExpr("date_add(c, 
1)"),
+        Row(java.sql.Date.valueOf("2020-01-02")))
+    }
+    withSQLConf(SQLConf.SESSION_LOCAL_TIMEZONE.key -> "Asia/Tokyo") {
+      checkAnswer(
+        ltzNanos("2020-02-29T20:00:00.123456789Z", 9).selectExpr("date_add(c, 
1)"),
+        Row(java.sql.Date.valueOf("2020-03-02")))
+    }
+  }
+
+  test("SPARK-60543: date_add/date_sub match the functions.* Column API over 
nanos") {
+    val df = ntzNanos("2020-02-29T12:34:56.123456789", 9)
+    val expected = Row(
+      java.sql.Date.valueOf("2020-03-01"),
+      java.sql.Date.valueOf("2020-02-28"),
+      java.sql.Date.valueOf("2020-03-01"))
+    checkAnswer(df.selectExpr("date_add(c, 1)", "date_sub(c, 1)", "dateadd(c, 
1)"), expected)
+    checkAnswer(
+      df.select(date_add(col("c"), 1), date_sub(col("c"), 1), 
dateadd(col("c"), lit(1))),
+      expected)
+  }
+
+  test("SPARK-60543: date_add/date_sub over NULL nanosecond timestamps") {
+    Seq(7, 8, 9).foreach { p =>
+      val df = spark.createDataFrame(
+        spark.sparkContext.parallelize(Seq(Row(null, null))),
+        new StructType().add("ntz", TimestampNTZNanosType(p)).add("ltz", 
TimestampLTZNanosType(p)))
+      checkAnswer(
+        df.selectExpr(
+          "date_add(ntz, 1)", "date_sub(ntz, 1)", "date_add(ltz, 1)", 
"date_sub(ltz, 1)"),
+        Row(null, null, null, null))
+    }
+  }
+
   // ===== MIN / MAX aggregates over nanosecond-precision timestamps 
(SPARK-56822) =====
   // `Min`/`Max` are type-agnostic `DeclarativeAggregate`s gated only on 
orderability
   // (`TypeUtils.checkForOrderingExpr`); the nanosecond timestamp types became 
orderable in


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to