You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ur...@apache.org on 2026/07/30 08:08:13 UTC
(spark) branch branch-4.x updated: [SPARK-57811][SQL] Support string to nanosecond-precision timestamp coercion in comparisons and predicates
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 dc31dc41f3af [SPARK-57811][SQL] Support string to nanosecond-precision timestamp coercion in comparisons and predicates
dc31dc41f3af is described below
commit dc31dc41f3afe7243a7ab1966bb77c505f5de3aa
Author: Stevo Mitric <st...@gmail.com>
AuthorDate: Thu Jul 30 08:07:47 2026 +0000
[SPARK-57811][SQL] Support string to nanosecond-precision timestamp coercion in comparisons and predicates
### What changes were proposed in this pull request?
Add non-ANSI type-coercion arms so that a string compared against a nanosecond-precision timestamp column (`TIMESTAMP_NTZ(p)`/`TIMESTAMP_LTZ(p)`, p in [7, 9]) is cast to that nanos type, mirroring the existing microsecond `TimestampType` handling (`StringPromotionTypeCoercion` equality arms + `TypeCoercion.findCommonTypeForBinaryComparison`).
### Why are the changes needed?
Micros TimestampType has string-coercion arms that honor ...datetimeToString.enabled (legacy → promote to string; equality → cast to timestamp). Nanos had none, so it fell through to config-blind AtomicType promotion. This adds the arms so nanos matches TimestampType's legacy behavior.
### Does this PR introduce _any_ user-facing change?
Only under legacy datetimeToString=true + ANSI off: range comparisons (<, BETWEEN, …) now promote to string (matching micros); equality unchanged. All other configs identical.
### How was this patch tested?
Extended existing suites and added golden file tests.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code
Closes #57223 from stevomitric/stevomitric/spark-57811-nanos.
Authored-by: Stevo Mitric <st...@gmail.com>
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
(cherry picked from commit 54dce691f533cb558a7c8403e90d90b8c971dc50)
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
---
.../analysis/StringPromotionTypeCoercion.scala | 11 ++++
.../spark/sql/catalyst/analysis/TypeCoercion.scala | 8 +++
.../spark/sql/types/DataTypeExpression.scala | 5 ++
.../catalyst/analysis/AnsiTypeCoercionSuite.scala | 33 +++++++++++
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 60 ++++++++++++++++++++
.../analyzer-results/timestamp-ltz-nanos.sql.out | 57 +++++++++++++++++++
.../analyzer-results/timestamp-ntz-nanos.sql.out | 57 +++++++++++++++++++
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 30 ++++++++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 28 ++++++++++
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 64 ++++++++++++++++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 64 ++++++++++++++++++++++
.../sql/TimestampNanosWideningSuiteBase.scala | 23 ++++++++
12 files changed, 440 insertions(+)
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringPromotionTypeCoercion.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringPromotionTypeCoercion.scala
index 5ca3736d2352..d6e3df444245 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringPromotionTypeCoercion.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringPromotionTypeCoercion.scala
@@ -34,6 +34,7 @@ import org.apache.spark.sql.types.{
DoubleType,
NullType,
StringTypeExpression,
+ TimestampLTZNanosTypeExpression,
TimestampType,
TimestampTypeExpression
}
@@ -55,10 +56,20 @@ object StringPromotionTypeCoercion {
// For equality between string and timestamp we cast the string to a timestamp
// so that things like rounding of subsecond precision does not affect the comparison.
+ // Both micros and nanos are scoped to the LTZ family (TimestampType / TimestampLTZNanosType):
+ // the NTZ families need no arm here, because their equality reaches the general
+ // BinaryComparison arm below and findCommonTypeForBinaryComparison returns the config-blind
+ // nanos/micros common type for them -- the same Cast this arm would add. Only the LTZ arm is
+ // load-bearing, since without it LTZ equality would take the range path and be promoted to
+ // string under legacy castDatetimeToString.
case p @ Equality(left @ StringTypeExpression(), right @ TimestampTypeExpression()) =>
p.withNewChildren(Seq(Cast(left, TimestampType), right))
case p @ Equality(left @ TimestampTypeExpression(), right @ StringTypeExpression()) =>
p.withNewChildren(Seq(left, Cast(right, TimestampType)))
+ case p @ Equality(left @ StringTypeExpression(), right @ TimestampLTZNanosTypeExpression()) =>
+ p.withNewChildren(Seq(Cast(left, right.dataType), right))
+ case p @ Equality(left @ TimestampLTZNanosTypeExpression(), right @ StringTypeExpression()) =>
+ p.withNewChildren(Seq(left, Cast(right, left.dataType)))
case p @ BinaryComparison(left, right)
if findCommonTypeForBinaryComparison(left.dataType, right.dataType, conf).isDefined =>
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
index 53de166e69ed..7c76e692bb5b 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
@@ -145,6 +145,14 @@ object TypeCoercion extends TypeCoercionBase {
=> if (conf.castDatetimeToString) Some(st) else Some(TimestampType)
case (TimestampType, st: StringType)
=> if (conf.castDatetimeToString) Some(st) else Some(TimestampType)
+ // Mirror the micros TimestampType (LTZ) arms above: nanos TIMESTAMP_LTZ(p) honors
+ // castDatetimeToString. Nanos TIMESTAMP_NTZ(p) intentionally has no arm here and, exactly like
+ // micros TimestampNTZType, falls through to the config-blind canPromoteAsInBinaryComparison
+ // line below -- so the LTZ and NTZ families stay consistent across micros and nanos.
+ case (st: StringType, tsNanos: TimestampLTZNanosType)
+ => if (conf.castDatetimeToString) Some(st) else Some(tsNanos)
+ case (tsNanos: TimestampLTZNanosType, st: StringType)
+ => if (conf.castDatetimeToString) Some(st) else Some(tsNanos)
case (st: StringType, NullType) => Some(st)
case (NullType, st: StringType) => Some(st)
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/types/DataTypeExpression.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/types/DataTypeExpression.scala
index 6cb6b7d429d8..329af882391d 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/types/DataTypeExpression.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/types/DataTypeExpression.scala
@@ -87,6 +87,11 @@ private[sql] object AnyTimestampNanoTypeExpression {
e.dataType.isInstanceOf[AnyTimestampNanoType]
}
+private[sql] object TimestampLTZNanosTypeExpression {
+ def unapply(e: Expression): Boolean =
+ e.dataType.isInstanceOf[TimestampLTZNanosType]
+}
+
private[sql] object DecimalExpression {
def unapply(e: Expression): Option[(Int, Int)] = e.dataType match {
case t: DecimalType => Some((t.precision, t.scale))
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
index 80d94cc3760d..c222655525ec 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
@@ -1150,4 +1150,37 @@ class AnsiTypeCoercionSuite extends TypeCoercionSuiteBase {
shouldNotCast(ArrayType(ArrayType(IntegerType)),
AbstractArrayType(StringTypeWithCollation(supportsTrimCollation = true)))
}
+
+ test("SPARK-57811: ANSI string to nanosecond timestamp coercion in comparisons and predicates") {
+ // PromoteStrings has no standalone rule id (it only runs inside AnsiCombinedTypeCoercionRule in
+ // production), so wrap it the same way to get a runnable, id-registered rule for ruleTest.
+ val rule =
+ new AnsiTypeCoercion.AnsiCombinedTypeCoercionRule(Seq(AnsiTypeCoercion.PromoteStrings))
+ // In ANSI mode the string operand is coerced to the nanosecond timestamp type for both the LTZ
+ // and NTZ families, via AnsiStringPromotionTypeCoercion.findWiderTypeForString (the atomic-type
+ // fall-through). This is config-blind: ANSI coercion never reads castDatetimeToString, so
+ // unlike the non-ANSI range path there is no legacy string-promotion branch. Both families
+ // behave exactly like their micros counterparts (TimestampType / TimestampNTZType) in ANSI
+ // mode, and the concrete operand type (family + precision) is preserved. Assert under both
+ // flag values to lock in that ANSI ignores it.
+ Seq("false", "true").foreach { legacy =>
+ withSQLConf(SQLConf.LEGACY_CAST_DATETIME_TO_STRING.key -> legacy) {
+ Seq(7, 8, 9).foreach { p =>
+ Seq(TimestampLTZNanosType(p), TimestampNTZNanosType(p)).foreach { nt =>
+ val tsn = AttributeReference("tsn", nt)()
+ val strLit = Literal("2020-01-02 03:04:05.123456789")
+ // Equality (covers both 3VL EqualTo and null-safe EqualNullSafe).
+ ruleTest(rule, EqualTo(tsn, strLit), EqualTo(tsn, Cast(strLit, nt)))
+ ruleTest(rule, EqualTo(strLit, tsn), EqualTo(Cast(strLit, nt), tsn))
+ ruleTest(rule, EqualNullSafe(tsn, strLit), EqualNullSafe(tsn, Cast(strLit, nt)))
+ ruleTest(rule, EqualNullSafe(strLit, tsn), EqualNullSafe(Cast(strLit, nt), tsn))
+ // Range comparisons.
+ ruleTest(rule, LessThan(tsn, strLit), LessThan(tsn, Cast(strLit, nt)))
+ ruleTest(rule, GreaterThanOrEqual(strLit, tsn),
+ GreaterThanOrEqual(Cast(strLit, nt), tsn))
+ }
+ }
+ }
+ }
+ }
}
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 d990a393ff65..2fbea06b4d06 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
@@ -1701,6 +1701,66 @@ class TypeCoercionSuite extends TypeCoercionSuiteBase {
EqualTo(Cast(date0301, TimestampType), timestamp0301000000))
ruleTest(rule, LessThan(date0301, timestamp0301000001),
LessThan(Cast(date0301, TimestampType), timestamp0301000001))
+
+ // SPARK-57811: a string operand is coerced to the nanosecond timestamp type in comparisons
+ // and predicates, mirroring the microsecond timestamp handling above. The concrete operand
+ // type (family + precision) is preserved, so the string is cast to that exact nanos type.
+ Seq(7, 8, 9).foreach { p =>
+ Seq(TimestampLTZNanosType(p), TimestampNTZNanosType(p)).foreach { nt =>
+ val tsn = AttributeReference("tsn", nt)()
+ val strLit = Literal("2020-01-02 03:04:05.123456789")
+ // Equality path: the string is cast to the nanos operand's own type so subsecond rounding
+ // does not affect the comparison. LTZ takes the explicit StringPromotionTypeCoercion
+ // Equality arm; NTZ has no arm and reaches the same cast via the general BinaryComparison
+ // fall-through (findCommonTypeForBinaryComparison returns the config-blind nanos type),
+ // mirroring how micros TimestampType (arm) vs TimestampNTZType (fall-through) are handled.
+ // The `Equality` extractor matches both 3VL `EqualTo` and null-safe `EqualNullSafe`, so
+ // both are covered.
+ ruleTest(rule, EqualTo(tsn, strLit), EqualTo(tsn, Cast(strLit, nt)))
+ ruleTest(rule, EqualTo(strLit, tsn), EqualTo(Cast(strLit, nt), tsn))
+ ruleTest(rule, EqualNullSafe(tsn, strLit), EqualNullSafe(tsn, Cast(strLit, nt)))
+ ruleTest(rule, EqualNullSafe(strLit, tsn), EqualNullSafe(Cast(strLit, nt), tsn))
+ // Range path (findCommonTypeForBinaryComparison).
+ ruleTest(rule, LessThan(tsn, strLit), LessThan(tsn, Cast(strLit, nt)))
+ ruleTest(rule, GreaterThanOrEqual(strLit, tsn),
+ GreaterThanOrEqual(Cast(strLit, nt), tsn))
+ }
+ }
+
+ // SPARK-57811: under legacy `castDatetimeToString`, the two nanos families mirror their micros
+ // counterparts. LTZ has a range arm in findCommonTypeForBinaryComparison, so its range
+ // comparisons promote both operands to string (like micros TimestampType); NTZ has no arm and
+ // stays config-blind (like micros TimestampNTZType), casting the string to the nanos type. In
+ // both families equality still casts the string to nanos, because the Equality arm fires before
+ // the range arm and reads no config. This block is the assertion that distinguishes the new
+ // production arms: in the default config the generic string-promotion fall-through already
+ // yields the nanos common type, so only the legacy branch separates the LTZ arm's effect.
+ withSQLConf(SQLConf.LEGACY_CAST_DATETIME_TO_STRING.key -> "true") {
+ val ltzType = TimestampLTZNanosType(9)
+ val ltz = AttributeReference("ltz", ltzType)()
+ val ntzType = TimestampNTZNanosType(9)
+ val ntz = AttributeReference("ntz", ntzType)()
+ val strLit = Literal("2020-01-02 03:04:05.123456789")
+ // LTZ range: both operands become strings (matches micros TimestampType).
+ ruleTest(rule, LessThan(ltz, strLit), LessThan(Cast(ltz, StringType), strLit))
+ ruleTest(rule, GreaterThanOrEqual(strLit, ltz),
+ GreaterThanOrEqual(strLit, Cast(ltz, StringType)))
+ // NTZ range: config-blind, the string is cast to the nanos type (matches micros
+ // TimestampNTZType, which has no arm and falls through to canPromoteAsInBinaryComparison).
+ ruleTest(rule, LessThan(ntz, strLit), LessThan(ntz, Cast(strLit, ntzType)))
+ ruleTest(rule, GreaterThanOrEqual(strLit, ntz),
+ GreaterThanOrEqual(Cast(strLit, ntzType), ntz))
+ // Equality: for both families the string is still cast to nanos so subseconds are compared
+ // exactly -- LTZ via the explicit Equality arm (which fires before the range arm), NTZ via
+ // the config-blind BinaryComparison fall-through. Holds for both 3VL `EqualTo` and null-safe
+ // `EqualNullSafe`.
+ Seq(ltzType -> ltz, ntzType -> ntz).foreach { case (nt, tsn) =>
+ ruleTest(rule, EqualTo(tsn, strLit), EqualTo(tsn, Cast(strLit, nt)))
+ ruleTest(rule, EqualTo(strLit, tsn), EqualTo(Cast(strLit, nt), tsn))
+ ruleTest(rule, EqualNullSafe(tsn, strLit), EqualNullSafe(tsn, Cast(strLit, nt)))
+ ruleTest(rule, EqualNullSafe(strLit, tsn), EqualNullSafe(Cast(strLit, nt), tsn))
+ }
+ }
}
test("cast WindowFrame boundaries to the type they operate upon") {
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 cdfb8a78bb22..3b3b3deaab8c 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
@@ -1036,6 +1036,63 @@ Sort [v#x ASC NULLS FIRST], true
+- OneRowRelation
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c = '2020-01-02 03:04:05.123456788',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query analysis
+Project [(c#x = cast(2020-01-02 03:04:05.123456789 as timestamp_ltz(9))) AS (c = 2020-01-02 03:04:05.123456789)#x, (c#x = cast(2020-01-02 03:04:05.123456788 as timestamp_ltz(9))) AS (c = 2020-01-02 03:04:05.123456788)#x, (c#x < cast(2020-01-02 03:04:05.123456790 as timestamp_ltz(9))) AS (c < 2020-01-02 03:04:05.123456790)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT c FROM VALUES
+ (TIMESTAMP_LTZ '2020-01-02 03:04:05.000000001'),
+ (TIMESTAMP_LTZ '2020-01-02 03:04:05.000000009') AS t(c)
+ WHERE c BETWEEN '2020-01-02 03:04:05.000000001' AND '2020-01-02 03:04:05.000000005'
+-- !query analysis
+Project [c#x]
++- Filter between(c#x, 2020-01-02 03:04:05.000000001, 2020-01-02 03:04:05.000000005)
+ +- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(false))
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=true
+-- !query analysis
+SetCommand (spark.sql.legacy.typeCoercion.datetimeToString.enabled,Some(true))
+
+
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query analysis
+Project [(c#x = cast(2020-01-02 03:04:05.123456789 as timestamp_ltz(9))) AS (c = 2020-01-02 03:04:05.123456789)#x, (cast(c#x as string) < 2020-01-02 03:04:05.123456790) AS (c < 2020-01-02 03:04:05.123456790)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=false
+-- !query analysis
+SetCommand (spark.sql.legacy.typeCoercion.datetimeToString.enabled,Some(false))
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(true))
+
+
-- !query
SELECT unix_seconds(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
-- !query analysis
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 7b03461ea2ac..bc9fc002ca05 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
@@ -870,6 +870,63 @@ Sort [v#x ASC NULLS FIRST], true
+- OneRowRelation
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c = '2020-01-02 03:04:05.123456788',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query analysis
+Project [(c#x = cast(2020-01-02 03:04:05.123456789 as timestamp_ntz(9))) AS (c = 2020-01-02 03:04:05.123456789)#x, (c#x = cast(2020-01-02 03:04:05.123456788 as timestamp_ntz(9))) AS (c = 2020-01-02 03:04:05.123456788)#x, (c#x < cast(2020-01-02 03:04:05.123456790 as timestamp_ntz(9))) AS (c < 2020-01-02 03:04:05.123456790)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT c FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-02 03:04:05.000000001'),
+ (TIMESTAMP_NTZ '2020-01-02 03:04:05.000000009') AS t(c)
+ WHERE c BETWEEN '2020-01-02 03:04:05.000000001' AND '2020-01-02 03:04:05.000000005'
+-- !query analysis
+Project [c#x]
++- Filter between(c#x, 2020-01-02 03:04:05.000000001, 2020-01-02 03:04:05.000000005)
+ +- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(false))
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=true
+-- !query analysis
+SetCommand (spark.sql.legacy.typeCoercion.datetimeToString.enabled,Some(true))
+
+
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query analysis
+Project [(c#x = cast(2020-01-02 03:04:05.123456789 as timestamp_ntz(9))) AS (c = 2020-01-02 03:04:05.123456789)#x, (c#x < cast(2020-01-02 03:04:05.123456790 as timestamp_ntz(9))) AS (c < 2020-01-02 03:04:05.123456790)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=false
+-- !query analysis
+SetCommand (spark.sql.legacy.typeCoercion.datetimeToString.enabled,Some(false))
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(true))
+
+
-- !query
SELECT unix_seconds(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
-- !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 62e9b8eb93b5..e0e84fe0b3cb 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
@@ -329,6 +329,36 @@ SELECT v, lead(v) OVER (ORDER BY v) AS next_v FROM (
UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000100'
UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000500') ORDER BY v;
+-- SPARK-57811: a string operand is coerced to the nanosecond timestamp type in comparisons and
+-- predicates (not truncated to micros, not promoted to string). The 9th fractional digit is
+-- significant, so an off-by-one-nanosecond literal does not compare equal.
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c = '2020-01-02 03:04:05.123456788',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789') AS t(c);
+
+-- BETWEEN over nanosecond timestamps: only the value inside the sub-microsecond range qualifies.
+SELECT c FROM VALUES
+ (TIMESTAMP_LTZ '2020-01-02 03:04:05.000000001'),
+ (TIMESTAMP_LTZ '2020-01-02 03:04:05.000000009') AS t(c)
+ WHERE c BETWEEN '2020-01-02 03:04:05.000000001' AND '2020-01-02 03:04:05.000000005';
+
+-- SPARK-57811: this is the config that exercises the new non-ANSI production arms. The arms live
+-- in TypeCoercion (not AnsiTypeCoercion), so ANSI must be disabled; and only under legacy
+-- castDatetimeToString does the range path differ. With both flags set, the range comparison
+-- promotes BOTH operands to string (Cast(c AS STRING), matching the micro TIMESTAMP type), while
+-- equality still casts the string to the nanos type (the Equality arm fires before the range arm).
+-- In every other config (ANSI on, or ANSI off without the legacy flag) the string coerces to the
+-- nanos type, identical to the parent commit, which is why the default-config cases above do not
+-- distinguish this change.
+SET spark.sql.ansi.enabled=false;
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=true;
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789') AS t(c);
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=false;
+SET spark.sql.ansi.enabled=true;
+
-- SPARK-57814: unix_seconds / unix_millis / unix_micros over nanosecond-precision values. The result
-- is a whole BIGINT count of the unit; sub-unit digits are dropped. Explicit-zone literals fix the
-- instant directly, independent of the session time zone.
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 971a2f8e148f..3ef269f8eb1a 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
@@ -269,6 +269,34 @@ SELECT v, lead(v) OVER (ORDER BY v) AS next_v FROM (
UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000100'
UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000500') ORDER BY v;
+-- SPARK-57811: a string operand is coerced to the nanosecond timestamp type in comparisons and
+-- predicates (not truncated to micros, not promoted to string). The 9th fractional digit is
+-- significant, so an off-by-one-nanosecond literal does not compare equal.
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c = '2020-01-02 03:04:05.123456788',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') AS t(c);
+
+-- BETWEEN over nanosecond timestamps: only the value inside the sub-microsecond range qualifies.
+SELECT c FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-02 03:04:05.000000001'),
+ (TIMESTAMP_NTZ '2020-01-02 03:04:05.000000009') AS t(c)
+ WHERE c BETWEEN '2020-01-02 03:04:05.000000001' AND '2020-01-02 03:04:05.000000005';
+
+-- SPARK-57811: TIMESTAMP_NTZ(p) mirrors micros TimestampNTZType under legacy castDatetimeToString.
+-- Micros TimestampNTZType has no arm in findCommonTypeForBinaryComparison, so it stays config-blind
+-- and casts the string to the timestamp type even under the legacy flag; nanos NTZ has no arm
+-- either and does the same. (Only the LTZ family, like micros TimestampType, promotes the range
+-- comparison to string under this flag -- see timestamp-ltz-nanos.sql.) So with both flags set the
+-- NTZ range comparison still casts the string to the nanos type, identical to the default config.
+SET spark.sql.ansi.enabled=false;
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=true;
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') AS t(c);
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=false;
+SET spark.sql.ansi.enabled=true;
+
-- SPARK-57814: unix_seconds / unix_millis / unix_micros over nanosecond-precision values. The result
-- is a whole BIGINT count of the unit; sub-unit digits (incl. the sub-microsecond remainder) are
-- dropped and NTZ applies no zone shift, so the wall-clock value is read as the epoch instant.
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 388ce96f7396..62a4b8448647 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
@@ -1127,6 +1127,70 @@ struct<v:timestamp_ltz(9),next_v:timestamp_ltz(9)>
2020-01-01 00:00:00.0000009 NULL
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c = '2020-01-02 03:04:05.123456788',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query schema
+struct<(c = 2020-01-02 03:04:05.123456789):boolean,(c = 2020-01-02 03:04:05.123456788):boolean,(c < 2020-01-02 03:04:05.123456790):boolean>
+-- !query output
+true false true
+
+
+-- !query
+SELECT c FROM VALUES
+ (TIMESTAMP_LTZ '2020-01-02 03:04:05.000000001'),
+ (TIMESTAMP_LTZ '2020-01-02 03:04:05.000000009') AS t(c)
+ WHERE c BETWEEN '2020-01-02 03:04:05.000000001' AND '2020-01-02 03:04:05.000000005'
+-- !query schema
+struct<c:timestamp_ltz(9)>
+-- !query output
+2020-01-02 03:04:05.000000001
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled false
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.legacy.typeCoercion.datetimeToString.enabled true
+
+
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query schema
+struct<(c = 2020-01-02 03:04:05.123456789):boolean,(c < 2020-01-02 03:04:05.123456790):boolean>
+-- !query output
+true true
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.legacy.typeCoercion.datetimeToString.enabled false
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled true
+
+
-- !query
SELECT unix_seconds(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
-- !query schema
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 821aad995bc6..a0d1aa9db7fd 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
@@ -924,6 +924,70 @@ struct<v:timestamp_ntz(9),next_v:timestamp_ntz(9)>
2020-01-01 00:00:00.0000009 NULL
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c = '2020-01-02 03:04:05.123456788',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query schema
+struct<(c = 2020-01-02 03:04:05.123456789):boolean,(c = 2020-01-02 03:04:05.123456788):boolean,(c < 2020-01-02 03:04:05.123456790):boolean>
+-- !query output
+true false true
+
+
+-- !query
+SELECT c FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-02 03:04:05.000000001'),
+ (TIMESTAMP_NTZ '2020-01-02 03:04:05.000000009') AS t(c)
+ WHERE c BETWEEN '2020-01-02 03:04:05.000000001' AND '2020-01-02 03:04:05.000000005'
+-- !query schema
+struct<c:timestamp_ntz(9)>
+-- !query output
+2020-01-02 03:04:05.000000001
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled false
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.legacy.typeCoercion.datetimeToString.enabled true
+
+
+-- !query
+SELECT c = '2020-01-02 03:04:05.123456789',
+ c < '2020-01-02 03:04:05.123456790'
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') AS t(c)
+-- !query schema
+struct<(c = 2020-01-02 03:04:05.123456789):boolean,(c < 2020-01-02 03:04:05.123456790):boolean>
+-- !query output
+true true
+
+
+-- !query
+SET spark.sql.legacy.typeCoercion.datetimeToString.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.legacy.typeCoercion.datetimeToString.enabled false
+
+
+-- !query
+SET spark.sql.ansi.enabled=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled true
+
+
-- !query
SELECT unix_seconds(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
-- !query schema
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala
index 22bb69fdfc6b..523b980adcee 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala
@@ -132,6 +132,29 @@ abstract class TimestampNanosWideningSuiteBase extends SharedSparkSession {
val ntzEq = twoCols(TimestampNTZType, ldtA, TimestampNTZNanosType(9), ldtA)
checkAnswer(ntzEq.selectExpr("a = b", "a < b"), Row(true, false))
}
+
+ test("SPARK-57811: string operand coerces to the nanosecond timestamp type") {
+ // Equality on the exact sub-microsecond value: the string is parsed at nanos precision, so a
+ // string that differs only in the 9th digit does NOT compare equal. The paired
+ // `= '...789' => true` / `= '...788' => false` is the load-bearing pair: it rules out
+ // micro-truncation (truncating the operand to micros would make both strings equal the column).
+ // The nanos-vs-string-promotion distinction is locked separately by the analyzer goldens
+ // (explicit `cast as timestamp_ntz(9)`) and the unit tests in TypeCoercionSuite.
+ val ntz =
+ single(TimestampNTZNanosType(9), LocalDateTime.parse("2020-01-02T03:04:05.123456789"))
+ checkAnswer(
+ ntz.selectExpr(
+ "c = '2020-01-02 03:04:05.123456789'",
+ "c = '2020-01-02 03:04:05.123456788'",
+ "c < '2020-01-02 03:04:05.123456790'",
+ "c > '2020-01-02 03:04:05.123456788'"),
+ Row(true, false, true, true))
+
+ // LTZ operand: the string literal is parsed in the session zone (America/Los_Angeles), so the
+ // 2020-01-01T00:00:00Z instant equals the local wall-clock time eight hours earlier.
+ val ltz = single(TimestampLTZNanosType(9), Instant.parse("2020-01-01T00:00:00Z"))
+ checkAnswer(ltz.selectExpr("c = '2019-12-31 16:00:00'"), Row(true))
+ }
}
// Runs the nanosecond timestamp widening tests with ANSI mode enabled explicitly.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org