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 920c92890192 [SPARK-57841][SQL][TEST] Add cross-precision and 
nested-type test coverage for nanosecond-precision timestamps
920c92890192 is described below

commit 920c92890192fe3831c02ddb6fcf321caa053f06
Author: Stevo Mitric <[email protected]>
AuthorDate: Sun Aug 2 17:25:10 2026 +0000

    [SPARK-57841][SQL][TEST] Add cross-precision and nested-type test coverage 
for nanosecond-precision timestamps
    
    ### What changes were proposed in this pull request?
    
    This is a test-only change under the nanosecond-timestamp SPIP 
(SPARK-56822). It adds end-to-end coverage for operations over the 
nanosecond-precision timestamp types `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` 
(`p` in `[7, 9]`) that ride on the cross-precision widening (SPARK-57454) and 
string/nanos coercion (SPARK-57811) but had no dedicated tests:
    
    - Map-key lookup fast path: nanos-key sections added to the 
threshold-parameterized `GetMapValue` (`ComplexTypeSuite`) and `ElementAt` 
(`CollectionExpressionsSuite`) blocks, exercising both the hash (threshold 0) 
and linear (threshold Int.MaxValue) paths in interpreted and codegen modes.
    - Mixed-precision joins: `TimestampNanosJoinSuiteBase` gains `nanos(p1) 
JOIN nanos(p2)` and `micro JOIN nanos` cases across every forced join strategy 
and codegen mode, asserting the join key widens to the common nanos type and 
still distinguishes the sub-microsecond remainder.
    - New `TimestampNanosRenderingSuiteBase` for `show()` string rendering and 
`collect()` external round-trip at `p = 7/8/9`.
    - Golden SQL sections appended to `timestamp-{ntz,ltz}-nanos.sql` for 
INTERSECT/EXCEPT, BETWEEN, if/nvl/ifnull, IN (subquery), explode, element_at, 
struct-field extraction, and map lookup by string key and by nanosecond key.
    
    The map-key hash fast path was flagged as a possible latent issue (its hash 
arm lists only microsecond timestamps). It is in fact already correct: nanos 
keys are physical `TimestampNanosVal` objects that hash and compare through the 
object `hashCode()`/ `equals()` fall-through arm, consistently on the driver 
and codegen sides. The new tests lock this in; no production change was 
required.
    
    ### Why are the changes needed?
    
    To establish and guard the behavior of nanosecond-precision timestamps 
across joins, set operations, conditionals, subqueries, complex-type access, 
and rendering.
    
    ### Does this PR introduce any user-facing change?
    
    No. Test-only.
    
    ### How was this patch tested?
    
    New and updated tests:
    - `catalyst/testOnly *.ComplexTypeSuite *.CollectionExpressionsSuite`
    - `sql/testOnly *.TimestampNanosJoin*Suite *.TimestampNanosRendering*Suite`
    - `sql/testOnly *.SQLQueryTestSuite -- -z nanos`
    
    ### Was this patch authored or co-authored using generative AI tooling?
    Generated-by: Claude Code (Claude Opus 4.8)
    
    Closes #57634 from stevomitric/stevomitric/timestamp-nanos-e2e-coverage.
    
    Authored-by: Stevo Mitric <[email protected]>
    Signed-off-by: Uros Bojanic <[email protected]>
---
 .../expressions/CollectionExpressionsSuite.scala   |  43 ++++-
 .../catalyst/expressions/ComplexTypeSuite.scala    |  64 +++++++
 .../analyzer-results/timestamp-ltz-nanos.sql.out   | 176 ++++++++++++++++++
 .../analyzer-results/timestamp-ntz-nanos.sql.out   | 177 ++++++++++++++++++
 .../sql-tests/inputs/timestamp-ltz-nanos.sql       |  67 +++++++
 .../sql-tests/inputs/timestamp-ntz-nanos.sql       |  73 ++++++++
 .../sql-tests/results/timestamp-ltz-nanos.sql.out  | 156 ++++++++++++++++
 .../sql-tests/results/timestamp-ntz-nanos.sql.out  | 156 ++++++++++++++++
 .../spark/sql/TimestampNanosJoinSuiteBase.scala    | 199 +++++++++++++++++++--
 .../sql/TimestampNanosRenderingSuiteBase.scala     | 177 ++++++++++++++++++
 10 files changed, 1273 insertions(+), 15 deletions(-)

diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CollectionExpressionsSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CollectionExpressionsSuite.scala
index 35c632ec5ad7..ec4ee75d8acd 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CollectionExpressionsSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CollectionExpressionsSuite.scala
@@ -29,7 +29,7 @@ import org.apache.spark.sql.Row
 import org.apache.spark.sql.catalyst.InternalRow
 import org.apache.spark.sql.catalyst.analysis.TypeCheckResult
 import org.apache.spark.sql.catalyst.analysis.TypeCheckResult.DataTypeMismatch
-import org.apache.spark.sql.catalyst.util.{ArrayBasedMapData, 
DateTimeTestUtils, DateTimeUtils, GenericArrayData}
+import org.apache.spark.sql.catalyst.util.{ArrayBasedMapData, 
DateTimeTestUtils, DateTimeUtils, GenericArrayData, TimestampNanosTestUtils}
 import 
org.apache.spark.sql.catalyst.util.DateTimeTestUtils.{outstandingZoneIds, LA, 
UTC, UTC_OPT}
 import org.apache.spark.sql.catalyst.util.IntervalUtils._
 import org.apache.spark.sql.catalyst.util.TypeUtils.ordinalNumber
@@ -2164,6 +2164,47 @@ class CollectionExpressionsSuite
         checkEvaluation(ElementAt(dupTimeMap, Literal(t1, timeType)), 10)
         checkEvaluation(ElementAt(dupTimeMap, Literal(t2, timeType)), 20)
 
+        // Nanosecond timestamp keys (SPARK-57841). element_at routes through 
the same
+        // GetMapValueUtil map-lookup path as GetMapValue, so it must also 
distinguish keys that
+        // share epochMicros and differ only in nanosWithinMicro, on both the 
hash (threshold 0) and
+        // linear (threshold Int.MaxValue) paths. Physical TimestampNanosVal 
objects use the
+        // hashCode()/equals() fall-through arm (not the primitive-long arm). 
Built from internal
+        // values via ArrayBasedMapData (Literal.create of a Scala nanos-keyed 
Map would route
+        // through the schema-aware converter, which only accepts 
LocalDateTime / Instant keys).
+        val ntz9 = TimestampNTZNanosType(9)
+        val micros = 1577836800000000L // 2020-01-01T00:00:00Z
+        val n1 = TimestampNanosTestUtils.nanosVal(micros, 1)
+        val n2 = TimestampNanosTestUtils.nanosVal(micros, 999) // same micro 
as n1, must not alias
+        val n3 = TimestampNanosTestUtils.nanosVal(micros + 1, 0)
+        val ntzNanosMap = Literal.create(
+          new ArrayBasedMapData(
+            new GenericArrayData(Array[Any](n1, n2, n3)),
+            new GenericArrayData(Array[Any](10, 20, 30))),
+          MapType(ntz9, IntegerType))
+        checkEvaluation(ElementAt(ntzNanosMap, Literal(n1, ntz9)), 10)
+        checkEvaluation(ElementAt(ntzNanosMap, Literal(n2, ntz9)), 20)
+        checkEvaluation(ElementAt(ntzNanosMap, Literal(n3, ntz9)), 30)
+        checkEvaluation(ElementAt(ntzNanosMap,
+          Literal(TimestampNanosTestUtils.nanosVal(micros, 500), ntz9)), null)
+
+        // LTZ family + null value + duplicate-key first-wins.
+        val ltz9 = TimestampLTZNanosType(9)
+        val l1 = TimestampNanosTestUtils.nanosVal(micros, 100)
+        val l2 = TimestampNanosTestUtils.nanosVal(micros, 900)
+        val ltzNanosMap = Literal.create(
+          new ArrayBasedMapData(
+            new GenericArrayData(Array[Any](l1, l2)),
+            new GenericArrayData(Array[Any](10, null))),
+          MapType(ltz9, IntegerType))
+        checkEvaluation(ElementAt(ltzNanosMap, Literal(l1, ltz9)), 10)
+        checkEvaluation(ElementAt(ltzNanosMap, Literal(l2, ltz9)), null) // 
present, null value
+        val dupNanosMap = Literal.create(
+          new ArrayBasedMapData(
+            new GenericArrayData(Array[Any](n1, n2, n1)),
+            new GenericArrayData(Array[Any](10, 20, 30))),
+          MapType(ntz9, IntegerType))
+        checkEvaluation(ElementAt(dupNanosMap, Literal(n1, ntz9)), 10)
+
         // Array Keys
         val arrayType = ArrayType(IntegerType)
         val arrayMap = Literal.create(
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ComplexTypeSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ComplexTypeSuite.scala
index f2a8e5b64d21..abc28ff203f8 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ComplexTypeSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ComplexTypeSuite.scala
@@ -294,6 +294,53 @@ class ComplexTypeSuite extends SparkFunSuite with 
ExpressionEvalHelper {
         checkEvaluation(GetMapValue(dupTimeMap, Literal(t1, timeType)), 10)
         checkEvaluation(GetMapValue(dupTimeMap, Literal(t2, timeType)), 20)
 
+        // Nanosecond timestamp keys (SPARK-57841). Physically 
TimestampNanosVal objects, so they
+        // use the hashCode()/equals() fall-through arm of genHash / 
hashKeyOnDriver / genEqual --
+        // NOT the primitive-long arm that lists only microsecond timestamps. 
Keys share epochMicros
+        // and differ only in nanosWithinMicro, so a correct lookup must 
consult the full
+        // sub-microsecond value on both the hash (threshold 0) and linear 
(threshold Int.MaxValue)
+        // paths. Built from internal TimestampNanosVal via ArrayBasedMapData 
because Literal.create
+        // of a Scala Map with a nanos type routes through the schema-aware 
converter, which accepts
+        // only LocalDateTime / Instant keys (see the Decimal / Time 
duplicate-key cases above).
+        val ntz9 = TimestampNTZNanosType(9)
+        val micros = 1577836800000000L // 2020-01-01T00:00:00Z
+        val n1 = TimestampNanosTestUtils.nanosVal(micros, 1)
+        val n2 = TimestampNanosTestUtils.nanosVal(micros, 999) // same micro 
as n1, must not alias
+        val n3 = TimestampNanosTestUtils.nanosVal(micros + 1, 0)
+        val ntzNanosMap = Literal.create(
+          new ArrayBasedMapData(
+            new GenericArrayData(Array[Any](n1, n2, n3)),
+            new GenericArrayData(Array[Any](10, 20, 30))),
+          MapType(ntz9, IntegerType))
+        checkEvaluation(GetMapValue(ntzNanosMap, Literal(n1, ntz9)), 10)
+        checkEvaluation(GetMapValue(ntzNanosMap, Literal(n2, ntz9)), 20)
+        checkEvaluation(GetMapValue(ntzNanosMap, Literal(n3, ntz9)), 30)
+        checkEvaluation(GetMapValue(ntzNanosMap,
+          Literal(TimestampNanosTestUtils.nanosVal(micros, 500), ntz9)), null)
+
+        // LTZ family behaves the same; also cover a null map value.
+        val ltz9 = TimestampLTZNanosType(9)
+        val l1 = TimestampNanosTestUtils.nanosVal(micros, 100)
+        val l2 = TimestampNanosTestUtils.nanosVal(micros, 900)
+        val ltzNanosMap = Literal.create(
+          new ArrayBasedMapData(
+            new GenericArrayData(Array[Any](l1, l2)),
+            new GenericArrayData(Array[Any](10, null))),
+          MapType(ltz9, IntegerType))
+        checkEvaluation(GetMapValue(ltzNanosMap, Literal(l1, ltz9)), 10)
+        checkEvaluation(GetMapValue(ltzNanosMap, Literal(l2, ltz9)), null) // 
present, null value
+        checkEvaluation(GetMapValue(ltzNanosMap,
+          Literal(TimestampNanosTestUtils.nanosVal(micros, 500), ltz9)), null) 
// absent
+
+        // Nanosecond duplicate keys: first match wins (ArrayBasedMapData 
first-wins semantics).
+        val dupNanosMap = Literal.create(
+          new ArrayBasedMapData(
+            new GenericArrayData(Array[Any](n1, n2, n1)),
+            new GenericArrayData(Array[Any](10, 20, 30))),
+          MapType(ntz9, IntegerType))
+        checkEvaluation(GetMapValue(dupNanosMap, Literal(n1, ntz9)), 10)
+        checkEvaluation(GetMapValue(dupNanosMap, Literal(n2, ntz9)), 20)
+
         // 6. Binary Keys
         val binaryMap = Literal.create(Map(Array(1.toByte) -> 10, 
Array(2.toByte) -> 20),
           MapType(BinaryType, IntegerType))
@@ -368,6 +415,23 @@ class ComplexTypeSuite extends SparkFunSuite with 
ExpressionEvalHelper {
       assert(ElementAt(foldableLit, Literal(1)).usesFoldableHashLookup)
     }
 
+    // A nanosecond-timestamp key type is a hashable AtomicType, so a foldable 
nanos-keyed map above
+    // the threshold takes the hash path too (SPARK-57841). Keys are internal 
TimestampNanosVal.
+    val ntz9 = TimestampNTZNanosType(9)
+    val nanosEntries = (0 until 2000).map { i =>
+      TimestampNanosTestUtils.nanosVal(1577836800000000L + i, i % 1000) -> i
+    }
+    val nanosFoldableLit = Literal.create(
+      new ArrayBasedMapData(
+        new GenericArrayData(nanosEntries.map(_._1.asInstanceOf[Any]).toArray),
+        new 
GenericArrayData(nanosEntries.map(_._2.asInstanceOf[Any]).toArray)),
+      MapType(ntz9, IntegerType))
+    withSQLConf(SQLConf.MAP_LOOKUP_HASH_THRESHOLD.key -> "1000") {
+      assert(GetMapValue(nanosFoldableLit,
+        Literal(TimestampNanosTestUtils.nanosVal(1577836800000000L, 0), ntz9))
+        .usesFoldableHashLookup)
+    }
+
     // Foldable but below threshold --> LinearExecutor.
     withSQLConf(SQLConf.MAP_LOOKUP_HASH_THRESHOLD.key -> "10000") {
       assert(!GetMapValue(foldableLit, Literal(1)).usesFoldableHashLookup)
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 5e66dade1f29..7664ff3b273d 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
@@ -1244,3 +1244,179 @@ SELECT date_trunc('NANOSECOND', TIMESTAMP_LTZ 
'2020-01-01 12:34:56.123456789 UTC
 -- !query analysis
 Project [date_trunc(NANOSECOND, 2020-01-01 04:34:56.123456789, 
Some(America/Los_Angeles)) AS date_trunc(NANOSECOND, TIMESTAMP_LTZ '2020-01-01 
04:34:56.123456789')#x]
 +- OneRowRelation
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' AS c
+    UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC')
+  INTERSECT SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Intersect false
+   :- Project [c#x]
+   :  +- SubqueryAlias __auto_generated_subquery_name
+   :     +- Union false, false
+   :        :- Project [2019-12-31 16:00:00.000000001 AS c#x]
+   :        :  +- OneRowRelation
+   :        +- Project [2019-12-31 16:00:00.000000999 AS TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000999'#x]
+   :           +- OneRowRelation
+   +- Project [2019-12-31 16:00:00.000000001 AS TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000001'#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' AS c
+    UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC')
+  EXCEPT SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Except false
+   :- Project [c#x]
+   :  +- SubqueryAlias __auto_generated_subquery_name
+   :     +- Union false, false
+   :        :- Project [2019-12-31 16:00:00.000000001 AS c#x]
+   :        :  +- OneRowRelation
+   :        +- Project [2019-12-31 16:00:00.000000999 AS TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000999'#x]
+   :           +- OneRowRelation
+   +- Project [2019-12-31 16:00:00.000000001 AS TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000001'#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT typeof(c), c FROM (
+    (SELECT '2020-01-01 00:00:00.0000009 UTC' :: timestamp_ltz(7) AS c)
+     INTERSECT (SELECT '2020-01-01 00:00:00.000000900 UTC' :: 
timestamp_ltz(9))) ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Project [typeof(c#x) AS typeof(c)#x, c#x]
+   +- SubqueryAlias __auto_generated_subquery_name
+      +- Intersect false
+         :- Project [cast(c#x as timestamp_ltz(9)) AS c#x]
+         :  +- Project [cast(2020-01-01 00:00:00.0000009 UTC as 
timestamp_ltz(7)) AS c#x]
+         :     +- OneRowRelation
+         +- Project [cast(2020-01-01 00:00:00.000000900 UTC as 
timestamp_ltz(9)) AS CAST(2020-01-01 00:00:00.000000900 UTC AS 
TIMESTAMP_LTZ(9))#x]
+            +- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000500 UTC'
+    BETWEEN TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'
+-- !query analysis
+Project [between(2019-12-31 16:00:00.0000005, 2019-12-31 16:00:00.000000001, 
2019-12-31 16:00:00.000000999) AS between(TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000500', TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', 
TIMESTAMP_LTZ '2019-12-31 16:00:00.000000999')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000001000 UTC'
+    BETWEEN TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'
+-- !query analysis
+Project [between(2019-12-31 16:00:00.000001, 2019-12-31 16:00:00.000000001, 
2019-12-31 16:00:00.000000999) AS between(TIMESTAMP_LTZ '2019-12-31 
16:00:00.000001000', TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', 
TIMESTAMP_LTZ '2019-12-31 16:00:00.000000999')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT '2020-01-01 00:00:00.000000500 UTC' :: timestamp_ltz(9)
+    BETWEEN '2020-01-01 00:00:00.0000001 UTC' :: timestamp_ltz(7)
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000001 UTC'
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT if(true,
+    '2020-01-01 00:00:00.0000001 UTC' :: timestamp_ltz(7),
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC') AS v)
+-- !query analysis
+Project [typeof(v#x) AS typeof(v)#x, v#x]
++- SubqueryAlias __auto_generated_subquery_name
+   +- Project [if (true) cast(cast(2020-01-01 00:00:00.0000001 UTC as 
timestamp_ltz(7)) as timestamp_ltz(9)) else 2019-12-31 16:00:00.123456789 AS 
v#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT nvl(
+    CAST(NULL AS timestamp_ltz(9)),
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') AS v)
+-- !query analysis
+Project [typeof(v#x) AS typeof(v)#x, v#x]
++- SubqueryAlias __auto_generated_subquery_name
+   +- Project [nvl(cast(null as timestamp_ltz(9)), 2019-12-31 
16:00:00.000000999) AS v#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT ifnull(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', CAST(NULL AS 
timestamp_ltz(9)))
+-- !query analysis
+Project [ifnull(2019-12-31 16:00:00.000000001, cast(null as timestamp_ltz(9))) 
AS ifnull(TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', CAST(NULL AS 
TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT k FROM VALUES
+    (TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'),
+    (TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') AS t(k)
+  WHERE k IN (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') ORDER 
BY k
+-- !query analysis
+Sort [k#x ASC NULLS FIRST], true
++- Project [k#x]
+   +- Filter k#x IN (list#x [])
+      :  +- Project [2019-12-31 16:00:00.000000999 AS TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000999'#x]
+      :     +- OneRowRelation
+      +- SubqueryAlias t
+         +- LocalRelation [k#x]
+
+
+-- !query
+SELECT typeof(col), col FROM (SELECT explode(array(
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC',
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'))) ORDER BY col
+-- !query analysis
+Sort [col#x ASC NULLS FIRST], true
++- Project [typeof(col#x) AS typeof(col)#x, col#x]
+   +- SubqueryAlias __auto_generated_subquery_name
+      +- Project [col#x]
+         +- Generate explode(array(2019-12-31 16:00:00.000000001, 2019-12-31 
16:00:00.000000999)), false, [col#x]
+            +- OneRowRelation
+
+
+-- !query
+SELECT element_at(array(
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC',
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'), 2)
+-- !query analysis
+Project [element_at(array(2019-12-31 16:00:00.000000001, 2019-12-31 
16:00:00.000000999), 2, None, true) AS element_at(array(TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000001', TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000999'), 2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT (named_struct('f', TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC')).f
+-- !query analysis
+Project [named_struct(f, 2019-12-31 16:00:00.123456789).f AS named_struct(f, 
TIMESTAMP_LTZ '2019-12-31 16:00:00.123456789').f#x]
++- OneRowRelation
+
+
+-- !query
+SELECT map('k', TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC')['k']
+-- !query analysis
+Project [map(k, 2019-12-31 16:00:00.123456789)[k] AS map(k, TIMESTAMP_LTZ 
'2019-12-31 16:00:00.123456789')[k]#x]
++- OneRowRelation
+
+
+-- !query
+SELECT map(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', 'a',
+           TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC', 'b')[
+       TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC']
+-- !query analysis
+Project [map(2019-12-31 16:00:00.000000001, a, 2019-12-31 16:00:00.000000999, 
b)[2019-12-31 16:00:00.000000999] AS map(TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000001', a, TIMESTAMP_LTZ '2019-12-31 16:00:00.000000999', 
b)[TIMESTAMP_LTZ '2019-12-31 16:00:00.000000999']#x]
++- OneRowRelation
+
+
+-- !query
+SELECT element_at(map(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', 'a',
+           TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC', 'b'),
+       TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC')
+-- !query analysis
+Project [element_at(map(2019-12-31 16:00:00.000000001, a, 2019-12-31 
16:00:00.000000999, b), 2019-12-31 16:00:00.000000001, None, true) AS 
element_at(map(TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', a, TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000999', b), TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000001')#x]
++- OneRowRelation
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 0026a2707400..a610d8a3570a 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
@@ -1071,3 +1071,180 @@ SELECT date_trunc('NANOSECOND', TIMESTAMP_NTZ 
'2020-01-01 12:34:56.123456789')
 -- !query analysis
 Project [date_trunc(NANOSECOND, 2020-01-01 12:34:56.123456789, 
Some(America/Los_Angeles)) AS date_trunc(NANOSECOND, TIMESTAMP_NTZ '2020-01-01 
12:34:56.123456789')#x]
 +- OneRowRelation
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' AS c
+    UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+  INTERSECT SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Intersect false
+   :- Project [c#x]
+   :  +- SubqueryAlias __auto_generated_subquery_name
+   :     +- Union false, false
+   :        :- Project [2020-01-01 00:00:00.000000001 AS c#x]
+   :        :  +- OneRowRelation
+   :        +- Project [2020-01-01 00:00:00.000000999 AS TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000999'#x]
+   :           +- OneRowRelation
+   +- Project [2020-01-01 00:00:00.000000001 AS TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000001'#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' AS c
+    UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+  EXCEPT SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Except false
+   :- Project [c#x]
+   :  +- SubqueryAlias __auto_generated_subquery_name
+   :     +- Union false, false
+   :        :- Project [2020-01-01 00:00:00.000000001 AS c#x]
+   :        :  +- OneRowRelation
+   :        +- Project [2020-01-01 00:00:00.000000999 AS TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000999'#x]
+   :           +- OneRowRelation
+   +- Project [2020-01-01 00:00:00.000000001 AS TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000001'#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT typeof(c), c FROM (
+    (SELECT '2020-01-01 00:00:00.0000009' :: timestamp_ntz(7) AS c)
+     INTERSECT (SELECT '2020-01-01 00:00:00.000000900' :: timestamp_ntz(9))) 
ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Project [typeof(c#x) AS typeof(c)#x, c#x]
+   +- SubqueryAlias __auto_generated_subquery_name
+      +- Intersect false
+         :- Project [cast(c#x as timestamp_ntz(9)) AS c#x]
+         :  +- Project [cast(2020-01-01 00:00:00.0000009 as timestamp_ntz(7)) 
AS c#x]
+         :     +- OneRowRelation
+         +- Project [cast(2020-01-01 00:00:00.000000900 as timestamp_ntz(9)) 
AS CAST(2020-01-01 00:00:00.000000900 AS TIMESTAMP_NTZ(9))#x]
+            +- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000500'
+    BETWEEN TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'
+-- !query analysis
+Project [between(2020-01-01 00:00:00.0000005, 2020-01-01 00:00:00.000000001, 
2020-01-01 00:00:00.000000999) AS between(TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000500', TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 
TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000001000'
+    BETWEEN TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'
+-- !query analysis
+Project [between(2020-01-01 00:00:00.000001, 2020-01-01 00:00:00.000000001, 
2020-01-01 00:00:00.000000999) AS between(TIMESTAMP_NTZ '2020-01-01 
00:00:00.000001000', TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 
TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT '2020-01-01 00:00:00.000000500' :: timestamp_ntz(9)
+    BETWEEN '2020-01-01 00:00:00.0000001' :: timestamp_ntz(7)
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000001'
+-- !query analysis
+Project [between(cast(2020-01-01 00:00:00.000000500 as timestamp_ntz(9)), 
cast(2020-01-01 00:00:00.0000001 as timestamp_ntz(7)), 2020-01-01 
00:00:00.000001) AS between(CAST(2020-01-01 00:00:00.000000500 AS 
TIMESTAMP_NTZ(9)), CAST(2020-01-01 00:00:00.0000001 AS TIMESTAMP_NTZ(7)), 
TIMESTAMP_NTZ '2020-01-01 00:00:00.000001')#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT if(true,
+    '2020-01-01 00:00:00.0000001' :: timestamp_ntz(7),
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789') AS v)
+-- !query analysis
+Project [typeof(v#x) AS typeof(v)#x, v#x]
++- SubqueryAlias __auto_generated_subquery_name
+   +- Project [if (true) cast(cast(2020-01-01 00:00:00.0000001 as 
timestamp_ntz(7)) as timestamp_ntz(9)) else 2020-01-01 00:00:00.123456789 AS 
v#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT nvl(
+    CAST(NULL AS timestamp_ntz(9)),
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS v)
+-- !query analysis
+Project [typeof(v#x) AS typeof(v)#x, v#x]
++- SubqueryAlias __auto_generated_subquery_name
+   +- Project [nvl(cast(null as timestamp_ntz(9)), 2020-01-01 
00:00:00.000000999) AS v#x]
+      +- OneRowRelation
+
+
+-- !query
+SELECT ifnull(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', CAST(NULL AS 
timestamp_ntz(9)))
+-- !query analysis
+Project [ifnull(2020-01-01 00:00:00.000000001, cast(null as timestamp_ntz(9))) 
AS ifnull(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', CAST(NULL AS 
TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT k FROM VALUES
+    (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'),
+    (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS t(k)
+  WHERE k IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') ORDER BY k
+-- !query analysis
+Sort [k#x ASC NULLS FIRST], true
++- Project [k#x]
+   +- Filter k#x IN (list#x [])
+      :  +- Project [2020-01-01 00:00:00.000000999 AS TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000999'#x]
+      :     +- OneRowRelation
+      +- SubqueryAlias t
+         +- LocalRelation [k#x]
+
+
+-- !query
+SELECT typeof(col), col FROM (SELECT explode(array(
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001',
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'))) ORDER BY col
+-- !query analysis
+Sort [col#x ASC NULLS FIRST], true
++- Project [typeof(col#x) AS typeof(col)#x, col#x]
+   +- SubqueryAlias __auto_generated_subquery_name
+      +- Project [col#x]
+         +- Generate explode(array(2020-01-01 00:00:00.000000001, 2020-01-01 
00:00:00.000000999)), false, [col#x]
+            +- OneRowRelation
+
+
+-- !query
+SELECT element_at(array(
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001',
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'), 2)
+-- !query analysis
+Project [element_at(array(2020-01-01 00:00:00.000000001, 2020-01-01 
00:00:00.000000999), 2, None, true) AS element_at(array(TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000001', TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000999'), 2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT (named_struct('f', TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789')).f
+-- !query analysis
+Project [named_struct(f, 2020-01-01 00:00:00.123456789).f AS named_struct(f, 
TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789').f#x]
++- OneRowRelation
+
+
+-- !query
+SELECT map('k', TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789')['k']
+-- !query analysis
+Project [map(k, 2020-01-01 00:00:00.123456789)[k] AS map(k, TIMESTAMP_NTZ 
'2020-01-01 00:00:00.123456789')[k]#x]
++- OneRowRelation
+
+
+-- !query
+SELECT map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 'a',
+           TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', 'b')[
+       TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999']
+-- !query analysis
+Project [map(2020-01-01 00:00:00.000000001, a, 2020-01-01 00:00:00.000000999, 
b)[2020-01-01 00:00:00.000000999] AS map(TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000001', a, TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', 
b)[TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999']#x]
++- OneRowRelation
+
+
+-- !query
+SELECT element_at(map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 'a',
+           TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', 'b'),
+       TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001')
+-- !query analysis
+Project [element_at(map(2020-01-01 00:00:00.000000001, a, 2020-01-01 
00:00:00.000000999, b), 2020-01-01 00:00:00.000000001, None, true) AS 
element_at(map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', a, TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000999', b), TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000001')#x]
++- OneRowRelation
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 62ae8ace49b8..93ffae607f10 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
@@ -408,3 +408,70 @@ SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789 
UTC' :: timestamp_ltz(9
 SELECT date_trunc('DAY', '2020-01-01 04:00:00.000000123 UTC' :: 
timestamp_ltz(7));
 -- An unsupported (sub-microsecond) unit yields NULL; the result still carries 
the nanos type.
 SELECT date_trunc('NANOSECOND', TIMESTAMP_LTZ '2020-01-01 12:34:56.123456789 
UTC');
+
+-- SPARK-57841: end-to-end coverage for operators that ride on the resolved 
widening (SPARK-57454)
+-- and complex-type access over nanosecond values, mirroring 
timestamp-ntz-nanos.sql for the LTZ
+-- family. Every case turns on the SUB-MICROSECOND remainder or on 
cross-precision widening. LTZ is
+-- zone-dependent, so literals carry an explicit UTC zone and render in the 
session zone
+-- (America/Los_Angeles, UTC-08:00). Multi-row queries end in a top-level 
ORDER BY.
+
+-- INTERSECT / EXCEPT distinguish the sub-microsecond remainder.
+SELECT c FROM (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' AS c
+    UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC')
+  INTERSECT SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' ORDER BY 
c;
+SELECT c FROM (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' AS c
+    UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC')
+  EXCEPT SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' ORDER BY c;
+-- Mixed-precision set op widens to the wider precision.
+SELECT typeof(c), c FROM (
+    (SELECT '2020-01-01 00:00:00.0000009 UTC' :: timestamp_ltz(7) AS c)
+     INTERSECT (SELECT '2020-01-01 00:00:00.000000900 UTC' :: 
timestamp_ltz(9))) ORDER BY c;
+
+-- BETWEEN on a sub-microsecond boundary.
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000500 UTC'
+    BETWEEN TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC';
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000001000 UTC'
+    BETWEEN TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC';
+-- Mixed-precision BETWEEN widens the bounds to the probe's precision.
+SELECT '2020-01-01 00:00:00.000000500 UTC' :: timestamp_ltz(9)
+    BETWEEN '2020-01-01 00:00:00.0000001 UTC' :: timestamp_ltz(7)
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000001 UTC';
+
+-- if / nvl / ifnull preserve the nanos type and widen mixed-precision 
branches to the wider type.
+SELECT typeof(v), v FROM (SELECT if(true,
+    '2020-01-01 00:00:00.0000001 UTC' :: timestamp_ltz(7),
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC') AS v);
+SELECT typeof(v), v FROM (SELECT nvl(
+    CAST(NULL AS timestamp_ltz(9)),
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') AS v);
+SELECT ifnull(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', CAST(NULL AS 
timestamp_ltz(9)));
+
+-- IN (subquery): the semi-join matches on the full nanos key.
+SELECT k FROM VALUES
+    (TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'),
+    (TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') AS t(k)
+  WHERE k IN (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') ORDER 
BY k;
+
+-- explode(array<ts_nanos>) yields one row per element.
+SELECT typeof(col), col FROM (SELECT explode(array(
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC',
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'))) ORDER BY col;
+
+-- element_at over array<ts_nanos> (1-based).
+SELECT element_at(array(
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC',
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'), 2);
+
+-- struct-field extraction.
+SELECT (named_struct('f', TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 
UTC')).f;
+
+-- map lookup by string key and by nanosecond key.
+SELECT map('k', TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC')['k'];
+SELECT map(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', 'a',
+           TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC', 'b')[
+       TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'];
+SELECT element_at(map(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', 'a',
+           TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC', 'b'),
+       TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC');
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 c80f0c236e59..3896f9da4d3f 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
@@ -341,3 +341,76 @@ SELECT date_trunc('HOUR', '2020-01-01 12:34:56.123456789' 
:: timestamp_ntz(9));
 SELECT date_trunc('DAY', '2020-06-21 23:30:00.000000123' :: timestamp_ntz(7));
 -- An unsupported (sub-microsecond) unit yields NULL; the result still carries 
the nanos type.
 SELECT date_trunc('NANOSECOND', TIMESTAMP_NTZ '2020-01-01 12:34:56.123456789');
+
+-- SPARK-57841: end-to-end coverage for operators that ride on the resolved 
widening (SPARK-57454)
+-- and complex-type access over nanosecond values. Every case turns on the 
SUB-MICROSECOND remainder
+-- (.000000001 vs .000000999 share a microsecond; only the full nanos value 
tells them apart) or on
+-- cross-precision widening. Multi-row queries end in a top-level ORDER BY so 
the golden output order
+-- is meaningful (SQLQueryTestSuite re-sorts otherwise). NTZ is 
zone-independent; the LTZ file mirrors
+-- these in the session zone.
+
+-- INTERSECT / EXCEPT distinguish the sub-microsecond remainder. A micro-only 
set op would wrongly
+-- merge .000000001 and .000000999; here INTERSECT keeps only the common value 
and EXCEPT removes it.
+SELECT c FROM (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' AS c
+    UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+  INTERSECT SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' ORDER BY c;
+SELECT c FROM (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' AS c
+    UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+  EXCEPT SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' ORDER BY c;
+-- Mixed-precision set op widens to the wider precision; the equal instant 
matches after widening.
+SELECT typeof(c), c FROM (
+    (SELECT '2020-01-01 00:00:00.0000009' :: timestamp_ntz(7) AS c)
+     INTERSECT (SELECT '2020-01-01 00:00:00.000000900' :: timestamp_ntz(9))) 
ORDER BY c;
+
+-- BETWEEN on a sub-microsecond boundary: the bounds share the microsecond 
with the probe, so only
+-- the full nanos value decides inclusivity. .000000500 is inside [.000000001, 
.000000999];
+-- .000001000 (next microsecond) is outside.
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000500'
+    BETWEEN TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999';
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000001000'
+    BETWEEN TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999';
+-- Mixed-precision BETWEEN widens the bounds to the probe's precision.
+SELECT '2020-01-01 00:00:00.000000500' :: timestamp_ntz(9)
+    BETWEEN '2020-01-01 00:00:00.0000001' :: timestamp_ntz(7)
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000001';
+
+-- if / nvl / ifnull preserve the nanos type and widen mixed-precision 
branches to the wider type.
+SELECT typeof(v), v FROM (SELECT if(true,
+    '2020-01-01 00:00:00.0000001' :: timestamp_ntz(7),
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789') AS v);
+SELECT typeof(v), v FROM (SELECT nvl(
+    CAST(NULL AS timestamp_ntz(9)),
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS v);
+SELECT ifnull(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', CAST(NULL AS 
timestamp_ntz(9)));
+
+-- IN (subquery): the semi-join matches on the full nanos key, so only the 
.000000999 row qualifies.
+SELECT k FROM VALUES
+    (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'),
+    (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS t(k)
+  WHERE k IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') ORDER BY k;
+
+-- explode(array<ts_nanos>) yields one row per element, each keeping the nanos 
type and value.
+SELECT typeof(col), col FROM (SELECT explode(array(
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001',
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'))) ORDER BY col;
+
+-- element_at over array<ts_nanos> (1-based) returns the addressed element 
unchanged.
+SELECT element_at(array(
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001',
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'), 2);
+
+-- struct-field extraction reads the nanos value back out of a struct.
+SELECT (named_struct('f', TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789')).f;
+
+-- map lookup by string key and by nanosecond key (GetMapValue / element_at 
over a nanos-keyed map).
+-- The nanos-keyed lookup must consult the full sub-microsecond value: looking 
up .000000999 returns
+-- 'b', not 'a', even though both keys share the microsecond.
+SELECT map('k', TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789')['k'];
+SELECT map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 'a',
+           TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', 'b')[
+       TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'];
+SELECT element_at(map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 'a',
+           TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', 'b'),
+       TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001');
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 12aaa0cd1a51..f2bb95ff833e 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
@@ -1365,3 +1365,159 @@ SELECT date_trunc('NANOSECOND', TIMESTAMP_LTZ 
'2020-01-01 12:34:56.123456789 UTC
 struct<date_trunc(NANOSECOND, TIMESTAMP_LTZ '2020-01-01 
04:34:56.123456789'):timestamp_ltz(9)>
 -- !query output
 NULL
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' AS c
+    UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC')
+  INTERSECT SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' ORDER BY c
+-- !query schema
+struct<c:timestamp_ltz(9)>
+-- !query output
+2019-12-31 16:00:00.000000001
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' AS c
+    UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC')
+  EXCEPT SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC' ORDER BY c
+-- !query schema
+struct<c:timestamp_ltz(9)>
+-- !query output
+2019-12-31 16:00:00.000000999
+
+
+-- !query
+SELECT typeof(c), c FROM (
+    (SELECT '2020-01-01 00:00:00.0000009 UTC' :: timestamp_ltz(7) AS c)
+     INTERSECT (SELECT '2020-01-01 00:00:00.000000900 UTC' :: 
timestamp_ltz(9))) ORDER BY c
+-- !query schema
+struct<typeof(c):string,c:timestamp_ltz(9)>
+-- !query output
+timestamp_ltz(9)       2019-12-31 16:00:00.0000009
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000500 UTC'
+    BETWEEN TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'
+-- !query schema
+struct<between(TIMESTAMP_LTZ '2019-12-31 16:00:00.000000500', TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000001', TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000999'):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000001000 UTC'
+    BETWEEN TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'
+-- !query schema
+struct<between(TIMESTAMP_LTZ '2019-12-31 16:00:00.000001000', TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000001', TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000999'):boolean>
+-- !query output
+false
+
+
+-- !query
+SELECT '2020-01-01 00:00:00.000000500 UTC' :: timestamp_ltz(9)
+    BETWEEN '2020-01-01 00:00:00.0000001 UTC' :: timestamp_ltz(7)
+        AND TIMESTAMP_LTZ '2020-01-01 00:00:00.000001 UTC'
+-- !query schema
+struct<between(CAST(2020-01-01 00:00:00.000000500 UTC AS TIMESTAMP_LTZ(9)), 
CAST(2020-01-01 00:00:00.0000001 UTC AS TIMESTAMP_LTZ(7)), TIMESTAMP 
'2019-12-31 16:00:00.000001'):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT if(true,
+    '2020-01-01 00:00:00.0000001 UTC' :: timestamp_ltz(7),
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC') AS v)
+-- !query schema
+struct<typeof(v):string,v:timestamp_ltz(9)>
+-- !query output
+timestamp_ltz(9)       2019-12-31 16:00:00.0000001
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT nvl(
+    CAST(NULL AS timestamp_ltz(9)),
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') AS v)
+-- !query schema
+struct<typeof(v):string,v:timestamp_ltz(9)>
+-- !query output
+timestamp_ltz(9)       2019-12-31 16:00:00.000000999
+
+
+-- !query
+SELECT ifnull(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', CAST(NULL AS 
timestamp_ltz(9)))
+-- !query schema
+struct<ifnull(TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', CAST(NULL AS 
TIMESTAMP_LTZ(9))):timestamp_ltz(9)>
+-- !query output
+2019-12-31 16:00:00.000000001
+
+
+-- !query
+SELECT k FROM VALUES
+    (TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC'),
+    (TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') AS t(k)
+  WHERE k IN (SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC') ORDER 
BY k
+-- !query schema
+struct<k:timestamp_ltz(9)>
+-- !query output
+2019-12-31 16:00:00.000000999
+
+
+-- !query
+SELECT typeof(col), col FROM (SELECT explode(array(
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC',
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'))) ORDER BY col
+-- !query schema
+struct<typeof(col):string,col:timestamp_ltz(9)>
+-- !query output
+timestamp_ltz(9)       2019-12-31 16:00:00.000000001
+timestamp_ltz(9)       2019-12-31 16:00:00.000000999
+
+
+-- !query
+SELECT element_at(array(
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC',
+    TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC'), 2)
+-- !query schema
+struct<element_at(array(TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', 
TIMESTAMP_LTZ '2019-12-31 16:00:00.000000999'), 2):timestamp_ltz(9)>
+-- !query output
+2019-12-31 16:00:00.000000999
+
+
+-- !query
+SELECT (named_struct('f', TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC')).f
+-- !query schema
+struct<named_struct(f, TIMESTAMP_LTZ '2019-12-31 
16:00:00.123456789').f:timestamp_ltz(9)>
+-- !query output
+2019-12-31 16:00:00.123456789
+
+
+-- !query
+SELECT map('k', TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC')['k']
+-- !query schema
+struct<map(k, TIMESTAMP_LTZ '2019-12-31 
16:00:00.123456789')[k]:timestamp_ltz(9)>
+-- !query output
+2019-12-31 16:00:00.123456789
+
+
+-- !query
+SELECT map(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', 'a',
+           TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC', 'b')[
+       TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC']
+-- !query schema
+struct<map(TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', a, TIMESTAMP_LTZ 
'2019-12-31 16:00:00.000000999', b)[TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000999']:string>
+-- !query output
+b
+
+
+-- !query
+SELECT element_at(map(TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC', 'a',
+           TIMESTAMP_LTZ '2020-01-01 00:00:00.000000999 UTC', 'b'),
+       TIMESTAMP_LTZ '2020-01-01 00:00:00.000000001 UTC')
+-- !query schema
+struct<element_at(map(TIMESTAMP_LTZ '2019-12-31 16:00:00.000000001', a, 
TIMESTAMP_LTZ '2019-12-31 16:00:00.000000999', b), TIMESTAMP_LTZ '2019-12-31 
16:00:00.000000001'):string>
+-- !query output
+a
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 dd74d5e0744e..dd55757374bd 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
@@ -1154,3 +1154,159 @@ SELECT date_trunc('NANOSECOND', TIMESTAMP_NTZ 
'2020-01-01 12:34:56.123456789')
 struct<date_trunc(NANOSECOND, TIMESTAMP_NTZ '2020-01-01 
12:34:56.123456789'):timestamp_ntz(9)>
 -- !query output
 NULL
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' AS c
+    UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+  INTERSECT SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' ORDER BY c
+-- !query schema
+struct<c:timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000001
+
+
+-- !query
+SELECT c FROM (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' AS c
+    UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+  EXCEPT SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001' ORDER BY c
+-- !query schema
+struct<c:timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000999
+
+
+-- !query
+SELECT typeof(c), c FROM (
+    (SELECT '2020-01-01 00:00:00.0000009' :: timestamp_ntz(7) AS c)
+     INTERSECT (SELECT '2020-01-01 00:00:00.000000900' :: timestamp_ntz(9))) 
ORDER BY c
+-- !query schema
+struct<typeof(c):string,c:timestamp_ntz(9)>
+-- !query output
+timestamp_ntz(9)       2020-01-01 00:00:00.0000009
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000500'
+    BETWEEN TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'
+-- !query schema
+struct<between(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000500', TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000001', TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000999'):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000001000'
+    BETWEEN TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'
+-- !query schema
+struct<between(TIMESTAMP_NTZ '2020-01-01 00:00:00.000001000', TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000001', TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000999'):boolean>
+-- !query output
+false
+
+
+-- !query
+SELECT '2020-01-01 00:00:00.000000500' :: timestamp_ntz(9)
+    BETWEEN '2020-01-01 00:00:00.0000001' :: timestamp_ntz(7)
+        AND TIMESTAMP_NTZ '2020-01-01 00:00:00.000001'
+-- !query schema
+struct<between(CAST(2020-01-01 00:00:00.000000500 AS TIMESTAMP_NTZ(9)), 
CAST(2020-01-01 00:00:00.0000001 AS TIMESTAMP_NTZ(7)), TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000001'):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT if(true,
+    '2020-01-01 00:00:00.0000001' :: timestamp_ntz(7),
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789') AS v)
+-- !query schema
+struct<typeof(v):string,v:timestamp_ntz(9)>
+-- !query output
+timestamp_ntz(9)       2020-01-01 00:00:00.0000001
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT nvl(
+    CAST(NULL AS timestamp_ntz(9)),
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS v)
+-- !query schema
+struct<typeof(v):string,v:timestamp_ntz(9)>
+-- !query output
+timestamp_ntz(9)       2020-01-01 00:00:00.000000999
+
+
+-- !query
+SELECT ifnull(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', CAST(NULL AS 
timestamp_ntz(9)))
+-- !query schema
+struct<ifnull(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', CAST(NULL AS 
TIMESTAMP_NTZ(9))):timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000001
+
+
+-- !query
+SELECT k FROM VALUES
+    (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001'),
+    (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS t(k)
+  WHERE k IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') ORDER BY k
+-- !query schema
+struct<k:timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000999
+
+
+-- !query
+SELECT typeof(col), col FROM (SELECT explode(array(
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001',
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'))) ORDER BY col
+-- !query schema
+struct<typeof(col):string,col:timestamp_ntz(9)>
+-- !query output
+timestamp_ntz(9)       2020-01-01 00:00:00.000000001
+timestamp_ntz(9)       2020-01-01 00:00:00.000000999
+
+
+-- !query
+SELECT element_at(array(
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001',
+    TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'), 2)
+-- !query schema
+struct<element_at(array(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 
TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'), 2):timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000999
+
+
+-- !query
+SELECT (named_struct('f', TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789')).f
+-- !query schema
+struct<named_struct(f, TIMESTAMP_NTZ '2020-01-01 
00:00:00.123456789').f:timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.123456789
+
+
+-- !query
+SELECT map('k', TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789')['k']
+-- !query schema
+struct<map(k, TIMESTAMP_NTZ '2020-01-01 
00:00:00.123456789')[k]:timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.123456789
+
+
+-- !query
+SELECT map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 'a',
+           TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', 'b')[
+       TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999']
+-- !query schema
+struct<map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', a, TIMESTAMP_NTZ 
'2020-01-01 00:00:00.000000999', b)[TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000999']:string>
+-- !query output
+b
+
+
+-- !query
+SELECT element_at(map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', 'a',
+           TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', 'b'),
+       TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001')
+-- !query schema
+struct<element_at(map(TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001', a, 
TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999', b), TIMESTAMP_NTZ '2020-01-01 
00:00:00.000000001'):string>
+-- !query output
+a
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
index b6e7b9aef28c..79762085c727 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
@@ -20,6 +20,8 @@ package org.apache.spark.sql
 import java.time.{Instant, LocalDateTime}
 
 import org.apache.spark.SparkConf
+import org.apache.spark.sql.catalyst.expressions.EqualTo
+import org.apache.spark.sql.catalyst.plans.logical.Join
 import org.apache.spark.sql.execution.SparkPlan
 import org.apache.spark.sql.execution.adaptive.AdaptiveSparkPlanHelper
 import org.apache.spark.sql.execution.joins.{BroadcastHashJoinExec, 
ShuffledHashJoinExec, SortMergeJoinExec}
@@ -112,6 +114,27 @@ abstract class TimestampNanosJoinSuiteBase extends 
SharedSparkSession with Adapt
         s"Executed plan:\n$plan")
   }
 
+  /**
+   * Asserts that the equi-join key in `df`'s analyzed plan was coerced to 
`expected` on BOTH sides
+   * (type coercion inserts a `Cast` around the narrower side). This pins that 
a mixed-precision or
+   * micro-vs-nanos join actually widens the key via `findWiderDateTimeType` 
rather than, say,
+   * comparing at the narrower precision and dropping the distinguishing digit.
+   */
+  protected def assertJoinKeyType(df: DataFrame, expected: DataType): Unit = {
+    val equalTos = df.queryExecution.analyzed.collect { case j: Join => 
j.condition }.flatten
+      .flatMap(_.collect { case e: EqualTo => e })
+    assert(equalTos.nonEmpty,
+      s"No EqualTo join condition found in analyzed 
plan:\n${df.queryExecution.analyzed}")
+    equalTos.foreach { e =>
+      assert(e.left.dataType == expected && e.right.dataType == expected,
+        s"Join key not coerced to $expected: ${e.left.dataType} === 
${e.right.dataType}")
+    }
+  }
+
+  private def cgLabel(cgConf: Seq[(String, String)]): String =
+    if (cgConf.contains(SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> "true")) 
"codegen on"
+    else "codegen off"
+
   // ---- relation builders: key column "k" of the given nanos type + an Int 
id column ----
 
   private def ntzLeft(p: Int): DataFrame =
@@ -150,6 +173,45 @@ abstract class TimestampNanosJoinSuiteBase extends 
SharedSparkSession with Adapt
         Row(null, 40))),
       new StructType().add("k", TimestampLTZNanosType(p)).add("rid", 
IntegerType))
 
+  // Microsecond-typed sides (TIMESTAMP_NTZ / TIMESTAMP): whole-microsecond 
keys (nanosWithinMicro
+  // implicitly 0). Joining these against a nanos side widens the key to the 
nanos type, so only a
+  // nanos row whose sub-microsecond remainder is also 0 can match. lid=1 (a 
whole micro) matches
+  // the nanos .000000000 row; lid=2 is another whole micro with no match; 
lid=4 is a NULL key.
+  private def ntzMicroLeft(): DataFrame =
+    spark.createDataFrame(
+      spark.sparkContext.parallelize(Seq(
+        Row(LocalDateTime.parse("2020-01-01T00:00:00"), 1),
+        Row(LocalDateTime.parse("2020-01-01T00:00:01"), 2),
+        Row(null, 4))),
+      new StructType().add("k", TimestampNTZType).add("lid", IntegerType))
+
+  private def ltzMicroLeft(): DataFrame =
+    spark.createDataFrame(
+      spark.sparkContext.parallelize(Seq(
+        Row(Instant.parse("2020-01-01T00:00:00Z"), 1),
+        Row(Instant.parse("2020-01-01T00:00:01Z"), 2),
+        Row(null, 4))),
+      new StructType().add("k", TimestampType).add("lid", IntegerType))
+
+  // Nanos right side for the micro-vs-nanos join: rid=10 is exactly on a 
microsecond boundary
+  // (.000000000, matches the micro lid=1); rid=20 shares that microsecond but 
carries a 500ns
+  // remainder, so it must NOT match the micro key (the widened key keeps the 
sub-micro bit).
+  private def ntzNanosRightForMicro(p: Int): DataFrame =
+    spark.createDataFrame(
+      spark.sparkContext.parallelize(Seq(
+        Row(LocalDateTime.parse("2020-01-01T00:00:00.000000000"), 10),
+        Row(LocalDateTime.parse("2020-01-01T00:00:00.000000500"), 20),
+        Row(null, 40))),
+      new StructType().add("k", TimestampNTZNanosType(p)).add("rid", 
IntegerType))
+
+  private def ltzNanosRightForMicro(p: Int): DataFrame =
+    spark.createDataFrame(
+      spark.sparkContext.parallelize(Seq(
+        Row(Instant.parse("2020-01-01T00:00:00.000000000Z"), 10),
+        Row(Instant.parse("2020-01-01T00:00:00.000000500Z"), 20),
+        Row(null, 40))),
+      new StructType().add("k", TimestampLTZNanosType(p)).add("rid", 
IntegerType))
+
   // Expected join outputs (order-insensitive). Selected as (lid, rid) so each 
row is identifiable.
   // INNER: only the fully-equal sub-microsecond pair (500ns == 500ns).
   private val expectedInner: Seq[Row] = Seq(Row(1, 10))
@@ -159,6 +221,12 @@ abstract class TimestampNanosJoinSuiteBase extends 
SharedSparkSession with Adapt
   private val expectedLeftOuter: Seq[Row] =
     Seq(Row(1, 10), Row(2, null), Row(3, null), Row(4, null))
 
+  // micro-vs-nanos: only the whole-microsecond left row (lid=1) matches the 
nanos .000000000 row
+  // (rid=10). The nanos .000000500 row (rid=20) shares the microsecond but 
must NOT match, and the
+  // NULL key (lid=4) never matches.
+  private val expectedMicroInner: Seq[Row] = Seq(Row(1, 10))
+  private val expectedMicroLeftOuter: Seq[Row] = Seq(Row(1, 10), Row(2, null), 
Row(4, null))
+
   // 
==========================================================================================
   // NTZ: inner + left-outer over a sub-microsecond key, every strategy x 
codegen mode x p.
   // 
==========================================================================================
@@ -166,14 +234,8 @@ abstract class TimestampNanosJoinSuiteBase extends 
SharedSparkSession with Adapt
     (stratName, execClass, stratConf) <- joinStrategies
     cgConf <- codegenModes
   } {
-    val cgName = if (cgConf.exists(_ == 
(SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> "true"))) {
-      "codegen on"
-    } else {
-      "codegen off"
-    }
-
     test(s"NTZ nanos join distinguishes the sub-microsecond remainder - " +
-      s"$stratName - $cgName") {
+      s"$stratName - ${cgLabel(cgConf)}") {
       withSQLConf((stratConf ++ cgConf): _*) {
         Seq(7, 8, 9).foreach { p =>
           val left = ntzLeft(p)
@@ -200,14 +262,8 @@ abstract class TimestampNanosJoinSuiteBase extends 
SharedSparkSession with Adapt
     (stratName, execClass, stratConf) <- joinStrategies
     cgConf <- codegenModes
   } {
-    val cgName = if (cgConf.exists(_ == 
(SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> "true"))) {
-      "codegen on"
-    } else {
-      "codegen off"
-    }
-
     test(s"LTZ nanos join distinguishes the sub-microsecond remainder - " +
-      s"$stratName - $cgName") {
+      s"$stratName - ${cgLabel(cgConf)}") {
       withSQLConf((stratConf ++ cgConf): _*) {
         Seq(7, 8, 9).foreach { p =>
           val left = ltzLeft(p)
@@ -226,6 +282,121 @@ abstract class TimestampNanosJoinSuiteBase extends 
SharedSparkSession with Adapt
       }
     }
   }
+
+  // Mixed-precision pairs. Both sides carry a nanos type but at DIFFERENT 
precisions; the join key
+  // is widened to the higher precision (findWiderDateTimeType). Because every 
remainder is a
+  // multiple of 100ns (exact at every p in [7, 9]), the same inputs and 
expected rows hold for
+  // each pair, and the sub-microsecond equal/distinct relationship is 
preserved after widening.
+  private val mixedPrecisionPairs: Seq[(Int, Int)] = Seq((7, 9), (7, 8), (8, 
9))
+
+  // 
==========================================================================================
+  // NTZ mixed precision: p_left != p_right, key widens to max(p_left, 
p_right).
+  // 
==========================================================================================
+  for {
+    (stratName, execClass, stratConf) <- joinStrategies
+    cgConf <- codegenModes
+  } {
+    test(s"NTZ nanos join across mixed precisions widens key - $stratName - 
${cgLabel(cgConf)}") {
+      withSQLConf((stratConf ++ cgConf): _*) {
+        mixedPrecisionPairs.foreach { case (pl, pr) =>
+          val left = ntzLeft(pl)
+          val right = ntzRight(pr)
+          val wider = TimestampNTZNanosType(math.max(pl, pr))
+
+          val inner = left.join(right, left("k") === right("k"), "inner")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(inner, execClass)
+          assertJoinKeyType(inner, wider)
+          checkAnswer(inner, expectedInner)
+
+          val leftOuter = left.join(right, left("k") === right("k"), 
"left_outer")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(leftOuter, execClass)
+          checkAnswer(leftOuter, expectedLeftOuter)
+        }
+      }
+    }
+  }
+
+  // 
==========================================================================================
+  // LTZ mixed precision: p_left != p_right, key widens to max(p_left, 
p_right).
+  // 
==========================================================================================
+  for {
+    (stratName, execClass, stratConf) <- joinStrategies
+    cgConf <- codegenModes
+  } {
+    test(s"LTZ nanos join across mixed precisions widens key - $stratName - 
${cgLabel(cgConf)}") {
+      withSQLConf((stratConf ++ cgConf): _*) {
+        mixedPrecisionPairs.foreach { case (pl, pr) =>
+          val left = ltzLeft(pl)
+          val right = ltzRight(pr)
+          val wider = TimestampLTZNanosType(math.max(pl, pr))
+
+          val inner = left.join(right, left("k") === right("k"), "inner")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(inner, execClass)
+          assertJoinKeyType(inner, wider)
+          checkAnswer(inner, expectedInner)
+
+          val leftOuter = left.join(right, left("k") === right("k"), 
"left_outer")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(leftOuter, execClass)
+          checkAnswer(leftOuter, expectedLeftOuter)
+        }
+      }
+    }
+  }
+
+  // 
==========================================================================================
+  // Microsecond timestamp JOIN nanosecond timestamp: the key widens from the 
micro type to the
+  // nanos type, and only a whole-microsecond (zero-remainder) nanos row can 
match the micro key.
+  // 
==========================================================================================
+  for {
+    (stratName, execClass, stratConf) <- joinStrategies
+    cgConf <- codegenModes
+  } {
+    test(s"NTZ micro join nanos widens to the nanos key - $stratName - 
${cgLabel(cgConf)}") {
+      withSQLConf((stratConf ++ cgConf): _*) {
+        Seq(7, 8, 9).foreach { p =>
+          val left = ntzMicroLeft()
+          val right = ntzNanosRightForMicro(p)
+          val wider = TimestampNTZNanosType(p)
+
+          val inner = left.join(right, left("k") === right("k"), "inner")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(inner, execClass)
+          assertJoinKeyType(inner, wider)
+          checkAnswer(inner, expectedMicroInner)
+
+          val leftOuter = left.join(right, left("k") === right("k"), 
"left_outer")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(leftOuter, execClass)
+          checkAnswer(leftOuter, expectedMicroLeftOuter)
+        }
+      }
+    }
+
+    test(s"LTZ micro join nanos widens to the nanos key - $stratName - 
${cgLabel(cgConf)}") {
+      withSQLConf((stratConf ++ cgConf): _*) {
+        Seq(7, 8, 9).foreach { p =>
+          val left = ltzMicroLeft()
+          val right = ltzNanosRightForMicro(p)
+          val wider = TimestampLTZNanosType(p)
+
+          val inner = left.join(right, left("k") === right("k"), "inner")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(inner, execClass)
+          assertJoinKeyType(inner, wider)
+          checkAnswer(inner, expectedMicroInner)
+
+          val leftOuter = left.join(right, left("k") === right("k"), 
"left_outer")
+            .select(left("lid"), right("rid"))
+          assertJoinUsed(leftOuter, execClass)
+          checkAnswer(leftOuter, expectedMicroLeftOuter)
+        }
+      }
+    }
+  }
 }
 
 // Runs the nanosecond timestamp join tests with ANSI mode enabled explicitly.
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosRenderingSuiteBase.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosRenderingSuiteBase.scala
new file mode 100644
index 000000000000..f3525de4af0e
--- /dev/null
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosRenderingSuiteBase.scala
@@ -0,0 +1,177 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *    http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql
+
+import java.time.{Instant, LocalDateTime}
+
+import org.apache.spark.SparkConf
+// castToImpl: `spark.createDataFrame` is typed as the public sql.DataFrame, 
but `showString` is a
+// classic-only method; this implicit narrows the receiver to classic.Dataset 
for that call.
+import org.apache.spark.sql.classic.ClassicConversions.castToImpl
+import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.test.SharedSparkSession
+import org.apache.spark.sql.types._
+
+/**
+ * End-to-end `show()` / `collect()` rendering tests over the 
nanosecond-precision timestamp types
+ * `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` (`p` in `[7, 9]`). These ride on 
the nanos cast-to-string
+ * (`ToPrettyString` -> `ToStringBase`, which routes nanos through the Types 
Framework fraction
+ * formatter) and the nanos external encoders, so no production change is 
required.
+ *
+ * Two distinct surfaces are pinned, neither of which the golden SQL files can 
express:
+ *   - `Dataset.show()` string rendering: the fractional second is rendered to 
the type's precision
+ *     with sub-precision digits floored to zero and an all-zero fraction 
trimmed entirely. A value
+ *     that only differs below the type's precision therefore renders 
identically -- the display is
+ *     lossy at `p`, exactly as the stored value is. (The golden `.out` files 
render via
+ *     `hiveResultString`, a different entry point onto the same formatter; 
`HiveResultSuite` covers
+ *     that one. `show()` is what a user actually sees at the console.)
+ *   - `collect()` external round-trip: NTZ comes back as 
`java.time.LocalDateTime` and LTZ as
+ *     `java.time.Instant` (see `RowEncoder`), and `.getNano` preserves the 
sub-microsecond
+ *     remainder floored to the type's precision -- so two values that share 
`epochMicros` but
+ *     differ in `nanosWithinMicro` are distinguishable after a full driver 
round-trip.
+ *
+ * The nanosecond timestamp types are gated behind a preview flag enabled by 
default under tests
+ * (`Utils.isTesting`), so it is not set here. The session time zone is fixed 
to America/Los_Angeles
+ * (UTC-08:00, no DST on 2020-01-01) so the `TIMESTAMP_LTZ` (`Instant`) values 
render
+ * deterministically in wall-clock time. The two subclasses run every test 
with ANSI mode on/off.
+ */
+abstract class TimestampNanosRenderingSuiteBase extends QueryTest with 
SharedSparkSession {
+
+  override def sparkConf: SparkConf = super.sparkConf
+    .set(SQLConf.SESSION_LOCAL_TIMEZONE.key, "America/Los_Angeles")
+
+  // Single nanosecond TIMESTAMP_NTZ(p) column "c"; a null element becomes a 
NULL row.
+  private def ntzDF(values: Seq[String], precision: Int): DataFrame =
+    spark.createDataFrame(
+      spark.sparkContext.parallelize(
+        values.map(s => Row(if (s == null) null else LocalDateTime.parse(s)))),
+      new StructType().add("c", TimestampNTZNanosType(precision)))
+
+  // Single nanosecond TIMESTAMP_LTZ(p) column "c"; a null element becomes a 
NULL row.
+  private def ltzDF(values: Seq[String], precision: Int): DataFrame =
+    spark.createDataFrame(
+      spark.sparkContext.parallelize(
+        values.map(s => Row(if (s == null) null else Instant.parse(s)))),
+      new StructType().add("c", TimestampLTZNanosType(precision)))
+
+  /**
+   * The data cells of `Dataset.show(truncate = 0)`, one string per 
(single-column) row, in row
+   * order with the alignment padding stripped. `showString` frames the table 
as a top border,
+   * a header row, a separator, one line per data row, then a bottom border; 
dropping the first
+   * three lines and the last isolates the data rows, and `.trim` removes the 
right-alignment
+   * padding so the exact rendered value can be compared (a prefix `contains` 
check could not tell
+   * a precision-floored fraction from a longer one).
+   */
+  private def shownCells(df: DataFrame): Seq[String] = {
+    val lines = df.showString(100, truncate = 0, vertical = 
false).split("\n").toSeq
+    lines.drop(3).dropRight(1).map(_.stripPrefix("|").stripSuffix("|").trim)
+  }
+
+  // The per-precision floored rendering of ".123456789": p=7 -> 7 digits, p=8 
-> 8, p=9 -> 9.
+  private val frac: Map[Int, String] = Map(
+    7 -> ".1234567", 8 -> ".12345678", 9 -> ".123456789")
+
+  // 
==========================================================================================
+  // show() renders the fraction to the type's precision, flooring 
sub-precision digits and
+  // trimming an all-zero fraction. NTZ is zone-independent.
+  // 
==========================================================================================
+  test("show() renders nanosecond TIMESTAMP_NTZ to the type precision") {
+    Seq(7, 8, 9).foreach { p =>
+      val df = ntzDF(Seq(
+        "2020-01-01T00:00:00.123456789", // floored to p digits
+        "2020-01-01T00:00:00.000000001", // non-zero only at digit 9 -> 
survives only at p=9
+        "2020-01-01T00:00:00",           // all-zero fraction -> no fraction
+        null), p)
+      assert(shownCells(df) === Seq(
+        "2020-01-01 00:00:00" + frac(p),
+        if (p == 9) "2020-01-01 00:00:00.000000001" else "2020-01-01 00:00:00",
+        "2020-01-01 00:00:00",
+        "NULL"))
+    }
+  }
+
+  test("show() renders nanosecond TIMESTAMP_LTZ in the session zone to the 
type precision") {
+    Seq(7, 8, 9).foreach { p =>
+      // UTC instants; the session zone is UTC-08:00, so 08:00:00Z renders as 
00:00:00 wall-clock.
+      val df = ltzDF(Seq(
+        "2020-01-01T08:00:00.123456789Z",
+        "2020-01-01T08:00:00.000000001Z",
+        "2020-01-01T08:00:00Z",
+        null), p)
+      assert(shownCells(df) === Seq(
+        "2020-01-01 00:00:00" + frac(p),
+        if (p == 9) "2020-01-01 00:00:00.000000001" else "2020-01-01 00:00:00",
+        "2020-01-01 00:00:00",
+        "NULL"))
+    }
+  }
+
+  test("show() renders nanosecond timestamps nested in array / struct") {
+    // A 9-digit fraction inside a complex type still renders to the element 
precision (p=9 here).
+    val ntz = ntzDF(Seq("2020-01-01T00:00:00.123456789"), 9)
+    assert(shownCells(ntz.selectExpr("array(c)")) ===
+      Seq("[2020-01-01 00:00:00.123456789]"))
+    assert(shownCells(ntz.selectExpr("named_struct('f', c)")) ===
+      Seq("{2020-01-01 00:00:00.123456789}"))
+  }
+
+  // 
==========================================================================================
+  // collect() returns the external LocalDateTime / Instant, preserving the 
sub-microsecond
+  // remainder floored to the type precision. Two values sharing epochMicros 
are distinguishable.
+  // 
==========================================================================================
+  // .000000123 floors to 100ns at p=7, 120ns at p=8, 123ns at p=9; .000000999 
-> 900 / 990 / 999.
+  private def flooredNano(base: Int, p: Int): Int = p match {
+    case 7 => base / 100 * 100
+    case 8 => base / 10 * 10
+    case 9 => base
+  }
+
+  test("collect() over nanosecond TIMESTAMP_NTZ preserves the 
precision-floored remainder") {
+    Seq(7, 8, 9).foreach { p =>
+      val df = ntzDF(Seq(
+        "2020-01-01T00:00:00.000000123",
+        "2020-01-01T00:00:00.000000999",
+        null), p)
+      val got = df.collect().map(r => 
Option(r.getAs[LocalDateTime]("c")).map(_.getNano)).toSet
+      assert(got === Set(Some(flooredNano(123, p)), Some(flooredNano(999, p)), 
None))
+      // The two non-null values share epochMicros yet stay distinct at every 
supported precision.
+      assert(flooredNano(123, p) != flooredNano(999, p))
+    }
+  }
+
+  test("collect() over nanosecond TIMESTAMP_LTZ preserves the 
precision-floored remainder") {
+    Seq(7, 8, 9).foreach { p =>
+      val df = ltzDF(Seq(
+        "2020-01-01T00:00:00.000000123Z",
+        "2020-01-01T00:00:00.000000999Z",
+        null), p)
+      val got = df.collect().map(r => 
Option(r.getAs[Instant]("c")).map(_.getNano)).toSet
+      assert(got === Set(Some(flooredNano(123, p)), Some(flooredNano(999, p)), 
None))
+    }
+  }
+}
+
+// Runs the nanosecond timestamp rendering tests with ANSI mode enabled 
explicitly.
+class TimestampNanosRenderingAnsiOnSuite extends 
TimestampNanosRenderingSuiteBase {
+  override def sparkConf: SparkConf = 
super.sparkConf.set(SQLConf.ANSI_ENABLED.key, "true")
+}
+
+// Runs the nanosecond timestamp rendering tests with ANSI mode disabled 
explicitly.
+class TimestampNanosRenderingAnsiOffSuite extends 
TimestampNanosRenderingSuiteBase {
+  override def sparkConf: SparkConf = 
super.sparkConf.set(SQLConf.ANSI_ENABLED.key, "false")
+}


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

Reply via email to