This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch branch-4.3
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.3 by this push:
new cba8f05f12bc [SPARK-58823][SQL][TEST] Add nanos-timestamp tests for
scalar subquery / EXISTS / NOT IN (SELECT)
cba8f05f12bc is described below
commit cba8f05f12bc777991527bed036c3a62831cbdb9
Author: Stevo Mitric <[email protected]>
AuthorDate: Tue Aug 18 09:52:29 2026 +0000
[SPARK-58823][SQL][TEST] Add nanos-timestamp tests for scalar subquery /
EXISTS / NOT IN (SELECT)
### What changes were proposed in this pull request?
Adds end-to-end test coverage for scalar subquery, EXISTS / NOT EXISTS, and
NOT IN (SELECT) over nanosecond-precision `TIMESTAMP_NTZ(p)` values, which were
previously exercised only for `IN (SELECT)`.
Golden cases in `timestamp-ntz-nanos.sql` (after the existing `IN (SELECT)`
case):
- scalar subquery in projection returns the nanos value and carries
`timestamp_ntz(9)` (value and NULL variants, via `typeof`);
- scalar subquery in a `WHERE` comparison, decided by the sub-microsecond
digit;
- correlated `EXISTS` / `NOT EXISTS` on a nanos equality;
- `NOT IN (SELECT ...)` anti-semi-join, including a mixed-precision (p=7 vs
p=9) probe and the three-valued-logic case where the subquery set contains NULL.
Also adds a matching DataFrame-level test in
`TimestampNanosWideningSuiteBase` (runs under ANSI on and off).
### Why are the changes needed?
These operators ride on generic nanos equality / type widening that is
already proven for joins, GROUP BY, and `IN (SELECT)`, but the scalar-subquery
/ EXISTS / NOT IN patterns had no explicit nanos coverage. The tests
regression-lock sub-microsecond correctness so a future change to subquery
result typing or semi-join comparison cannot silently truncate nanos to micros.
### Does this PR introduce _any_ user-facing change?
No. Test-only.
### How was this patch tested?
`SQLQueryTestSuite -z nanos` (golden regeneration, all pass) and
`TimestampNanosWideningAnsiOnSuite` / `TimestampNanosWideningAnsiOffSuite`
(14/14 pass).
### Was this patch authored or co-authored using generative AI tooling?
Co-Authored-By: Claude Opus 4.8
Closes #58049 from stevomitric/stevomitric/nanos-subquery-tests.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
(cherry picked from commit c67e787eb7579fb0ed0bd162e52748f79828a3c2)
Signed-off-by: Uros Bojanic <[email protected]>
---
.../analyzer-results/timestamp-ntz-nanos.sql.out | 129 +++++++++++++++++++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 55 +++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 94 +++++++++++++++
.../sql/TimestampNanosWideningSuiteBase.scala | 34 ++++++
4 files changed, 312 insertions(+)
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 6c6bbf4bf6c6..5eb9a2878ce3 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
@@ -1359,6 +1359,135 @@ Sort [k#x ASC NULLS FIRST], true
+- LocalRelation [k#x]
+-- !query
+SELECT (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+-- !query analysis
+Project [scalar-subquery#x [] AS scalarsubquery()#x]
+: +- Project [2020-01-01 00:00:00.000000999 AS TIMESTAMP_NTZ '2020-01-01
00:00:00.000000999'#x]
+: +- OneRowRelation
++- OneRowRelation
+
+
+-- !query
+SELECT typeof((SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'))
+-- !query analysis
+Project [typeof(scalar-subquery#x []) AS typeof(scalarsubquery())#x]
+: +- Project [2020-01-01 00:00:00.000000999 AS TIMESTAMP_NTZ '2020-01-01
00:00:00.000000999'#x]
+: +- OneRowRelation
++- OneRowRelation
+
+
+-- !query
+SELECT typeof((SELECT CAST(NULL AS timestamp_ntz(9))))
+-- !query analysis
+Project [typeof(scalar-subquery#x []) AS typeof(scalarsubquery())#x]
+: +- Project [cast(null as timestamp_ntz(9)) AS CAST(NULL AS
TIMESTAMP_NTZ(9))#x]
+: +- OneRowRelation
++- 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 = (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 = scalar-subquery#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 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 EXISTS (SELECT 1 FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS s(v)
+ WHERE s.v = t.k) ORDER BY k
+-- !query analysis
+Sort [k#x ASC NULLS FIRST], true
++- Project [k#x]
+ +- Filter exists#x [k#x]
+ : +- Project [1 AS 1#x]
+ : +- Filter (v#x = outer(k#x))
+ : +- SubqueryAlias s
+ : +- LocalRelation [v#x]
+ +- SubqueryAlias t
+ +- LocalRelation [k#x]
+
+
+-- !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 NOT EXISTS (SELECT 1 FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001') AS s(v)
+ WHERE s.v = t.k) ORDER BY k
+-- !query analysis
+Sort [k#x ASC NULLS FIRST], true
++- Project [k#x]
+ +- Filter NOT exists#x [k#x]
+ : +- Project [1 AS 1#x]
+ : +- Filter (v#x = outer(k#x))
+ : +- SubqueryAlias s
+ : +- LocalRelation [v#x]
+ +- SubqueryAlias t
+ +- LocalRelation [k#x]
+
+
+-- !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 NOT 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 NOT 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 k FROM VALUES
+ ('2020-01-01 00:00:00.0000009' :: timestamp_ntz(7)) AS t(k)
+ WHERE k NOT 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 NOT cast(k#x as timestamp_ntz(9)) IN (list#x [])
+ : +- Project [TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'#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 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 NOT IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'
+ UNION ALL SELECT CAST(NULL AS timestamp_ntz(9))) ORDER BY k
+-- !query analysis
+Sort [k#x ASC NULLS FIRST], true
++- Project [k#x]
+ +- Filter NOT k#x IN (list#x [])
+ : +- Union false, false
+ : :- Project [2020-01-01 00:00:00.000000999 AS TIMESTAMP_NTZ
'2020-01-01 00:00:00.000000999'#x]
+ : : +- OneRowRelation
+ : +- Project [cast(null as timestamp_ntz(9)) AS CAST(NULL AS
TIMESTAMP_NTZ(9))#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',
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 44aca6e23ad6..43c83810c750 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
@@ -461,6 +461,61 @@ SELECT k FROM VALUES
(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;
+-- Scalar subquery in projection returns the nanos value and carries the nanos
type;
+-- the sub-microsecond precision survives scalar-subquery result boxing.
+SELECT (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999');
+SELECT typeof((SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'));
+-- A NULL scalar subquery still carries the nanos type.
+SELECT typeof((SELECT CAST(NULL AS timestamp_ntz(9))));
+-- Scalar subquery in a WHERE comparison: the sub-microsecond value decides
the match, so only
+-- the .000000999 row qualifies (not the .000000001 row).
+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 = (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') ORDER BY k;
+
+-- EXISTS (correlated on a nanos equality): the outer row is kept iff a
matching nanos key exists
+-- in the subquery relation. Only the .000000999 row correlates to s.v.
+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 EXISTS (SELECT 1 FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS s(v)
+ WHERE s.v = t.k) ORDER BY k;
+
+-- NOT EXISTS (correlated on a nanos equality): the opposite; the outer row is
kept iff no matching
+-- nanos key exists. The subquery holds only .000000001, so the .000000999 row
survives.
+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 NOT EXISTS (SELECT 1 FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001') AS s(v)
+ WHERE s.v = t.k) ORDER BY k;
+
+-- NOT IN (subquery): anti-semi-join on the full nanos key. The .000000999 row
is in the subquery
+-- set (excluded); the .000000001 row is not (kept). Sub-microsecond precision
decides membership --
+-- the two values differ in the nanosecond digit, not by rounding error.
+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 NOT IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') ORDER
BY k;
+
+-- Mixed-precision NOT IN widens the probe to p=9 before the anti-join. The
p=7 value .0000009
+-- becomes .000000900 at p=9, which is not .000000999, so the row is not in
the set and is kept.
+SELECT k FROM VALUES
+ ('2020-01-01 00:00:00.0000009' :: timestamp_ntz(7)) AS t(k)
+ WHERE k NOT IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') ORDER
BY k;
+
+-- NOT IN with a NULL in the subquery set: three-valued logic. For the row
that does not equal the
+-- non-null member, the comparison against NULL is UNKNOWN, so NOT IN is
UNKNOWN and the row is
+-- filtered out; the row that equals the non-null member is a definite match
and also excluded.
+-- The result is therefore empty.
+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 NOT IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'
+ UNION ALL SELECT CAST(NULL AS timestamp_ntz(9))) 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',
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 ac132ba8d771..26ce0e0fd8b2 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
@@ -1436,6 +1436,100 @@ struct<k:timestamp_ntz(9)>
2020-01-01 00:00:00.000000999
+-- !query
+SELECT (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')
+-- !query schema
+struct<scalarsubquery():timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000999
+
+
+-- !query
+SELECT typeof((SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'))
+-- !query schema
+struct<typeof(scalarsubquery()):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !query
+SELECT typeof((SELECT CAST(NULL AS timestamp_ntz(9))))
+-- !query schema
+struct<typeof(scalarsubquery()):string>
+-- !query output
+timestamp_ntz(9)
+
+
+-- !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 = (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 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 EXISTS (SELECT 1 FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') AS s(v)
+ WHERE s.v = t.k) ORDER BY k
+-- !query schema
+struct<k:timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000999
+
+
+-- !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 NOT EXISTS (SELECT 1 FROM VALUES
+ (TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001') AS s(v)
+ WHERE s.v = t.k) ORDER BY k
+-- !query schema
+struct<k:timestamp_ntz(9)>
+-- !query output
+2020-01-01 00:00:00.000000999
+
+
+-- !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 NOT 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.000000001
+
+
+-- !query
+SELECT k FROM VALUES
+ ('2020-01-01 00:00:00.0000009' :: timestamp_ntz(7)) AS t(k)
+ WHERE k NOT IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999') ORDER
BY k
+-- !query schema
+struct<k:timestamp_ntz(7)>
+-- !query output
+2020-01-01 00:00:00.0000009
+
+
+-- !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 NOT IN (SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999'
+ UNION ALL SELECT CAST(NULL AS timestamp_ntz(9))) ORDER BY k
+-- !query schema
+struct<k:timestamp_ntz(9)>
+-- !query output
+
+
+
-- !query
SELECT typeof(col), col FROM (SELECT explode(array(
TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001',
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala
index 523b980adcee..91bfcb16f9d6 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosWideningSuiteBase.scala
@@ -120,6 +120,40 @@ abstract class TimestampNanosWideningSuiteBase extends
SharedSparkSession {
checkAnswer(ntz.selectExpr("a IN (b)"), Row(false))
}
+ test("SPARK-56822: NOT IN / NOT EXISTS / scalar subquery over nanosecond
timestamps") {
+ val ntzA = LocalDateTime.parse("2020-01-01T00:00:00.000000001")
+ val ntzB = LocalDateTime.parse("2020-01-01T00:00:00.000000999")
+ // Two sub-microsecond-distinct nanos values in the outer relation.
+ val outer = spark.createDataFrame(
+ spark.sparkContext.parallelize(Seq(Row(ntzA), Row(ntzB))),
+ new StructType().add("c", TimestampNTZNanosType(9)))
+
+ withTempView("outer_t") {
+ outer.createOrReplaceTempView("outer_t")
+
+ // NOT IN over the nanos key: only the value absent from the subquery
set survives, and the
+ // nanosecond digit -- not just the microsecond -- decides membership.
+ checkAnswer(
+ spark.sql(
+ "SELECT c FROM outer_t WHERE c NOT IN " +
+ "(SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000999')"),
+ Row(ntzA))
+
+ // NOT EXISTS correlated on the nanos key: the outer row survives iff no
matching key exists.
+ checkAnswer(
+ spark.sql(
+ "SELECT o.c FROM outer_t o WHERE NOT EXISTS " +
+ "(SELECT 1 FROM outer_t i WHERE i.c = o.c AND " +
+ "i.c = TIMESTAMP_NTZ '2020-01-01 00:00:00.000000001')"),
+ Row(ntzB))
+ }
+
+ // A scalar subquery carries the nanos type into its result column.
+ val scalar = spark.sql("SELECT (SELECT TIMESTAMP_NTZ '2020-01-01
00:00:00.000000999') AS c")
+ assert(scalar.schema("c").dataType === TimestampNTZNanosType(9))
+ checkAnswer(scalar, Row(ntzB))
+ }
+
test("SPARK-57454: binary comparison widens nanosecond timestamps") {
// Equal absolute instants stored at different precisions compare equal.
val ltzEq = twoCols(TimestampType, instantA, TimestampLTZNanosType(9),
instantA)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
