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]