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 6bac1dbd7cf7 [SPARK-57841][SQL][TEST] Add cross-precision and
nested-type test coverage for nanosecond-precision timestamps
6bac1dbd7cf7 is described below
commit 6bac1dbd7cf7ad0ba5852f5ad40263918e22cbda
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]>
(cherry picked from commit 920c92890192fe3831c02ddb6fcf321caa053f06)
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]