You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ur...@apache.org on 2026/08/03 08:27:39 UTC
(spark) branch master updated: [SPARK-58502][SQL] Make in-limit SQL test deterministic
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 f0e2b19b82df [SPARK-58502][SQL] Make in-limit SQL test deterministic
f0e2b19b82df is described below
commit f0e2b19b82df8a5c1d6a4eeabf33e241105481f4
Author: Vladimir Golubev <vl...@databricks.com>
AuthorDate: Mon Aug 3 08:27:26 2026 +0000
[SPARK-58502][SQL] Make in-limit SQL test deterministic
### What changes were proposed in this pull request?
Add deterministic ordering to result-sensitive subqueries in `in-limit.sql` that combine
`IN` or `NOT IN` with `LIMIT` or `OFFSET`. Regenerate the execution and analyzer golden files.
### Why are the changes needed?
Without an ordering, `LIMIT` and `OFFSET` may select different membership values under different
physical row orders, making the golden results flaky. Ordering by the projected membership column
makes tied rows semantically equivalent.
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
`build/sbt "sql/testOnly org.apache.spark.sql.SQLQueryTestSuite -- -z in-limit.sql"`
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: OpenAI Codex (GPT-5)
Closes #57696 from vladimirg-db/make-in-limit-test-deterministic.
Authored-by: Vladimir Golubev <vl...@databricks.com>
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
---
.../subquery/in-subquery/in-limit.sql.out | 180 ++++++++++++---------
.../inputs/subquery/in-subquery/in-limit.sql | 10 ++
.../results/subquery/in-subquery/in-limit.sql.out | 22 ++-
3 files changed, 128 insertions(+), 84 deletions(-)
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out
index 1820f23cfc79..8179080bba1b 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out
@@ -201,6 +201,7 @@ FROM t1
WHERE t1a IN (SELECT t2a
FROM t2
WHERE t1d = t2d
+ ORDER BY t2a
LIMIT 10
OFFSET 2)
LIMIT 2
@@ -214,14 +215,15 @@ GlobalLimit 2
: +- GlobalLimit 10
: +- LocalLimit 10
: +- Offset 2
- : +- Project [t2a#x]
- : +- Filter (outer(t1d#xL) = t2d#xL)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2a#x ASC NULLS FIRST], true
+ : +- Project [t2a#x]
+ : +- Filter (outer(t1d#xL) = t2d#xL)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -263,6 +265,7 @@ FROM t1
WHERE t1a IN (SELECT t2a
FROM t2
WHERE t1d = t2d
+ ORDER BY t2a
OFFSET 2)
OFFSET 1
-- !query analysis
@@ -270,14 +273,15 @@ Offset 1
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Filter t1a#x IN (list#x [t1d#xL])
: +- Offset 2
- : +- Project [t2a#x]
- : +- Filter (outer(t1d#xL) = t2d#xL)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2a#x ASC NULLS FIRST], true
+ : +- Project [t2a#x]
+ : +- Filter (outer(t1d#xL) = t2d#xL)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -292,6 +296,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c NULLS LAST
LIMIT 2)
LIMIT 4
-- !query analysis
@@ -301,14 +306,15 @@ GlobalLimit 4
+- Filter t1c#x IN (list#x [])
: +- GlobalLimit 2
: +- LocalLimit 2
- : +- Project [t2c#x]
- : +- Filter (cast(t2b#x as int) >= 8)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2c#x ASC NULLS LAST], true
+ : +- Project [t2c#x]
+ : +- Filter (cast(t2b#x as int) >= 8)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -359,6 +365,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b <= t1d
+ ORDER BY t2c NULLS LAST
LIMIT 2)
LIMIT 4
-- !query analysis
@@ -368,14 +375,15 @@ GlobalLimit 4
+- Filter t1c#x IN (list#x [t1d#xL])
: +- GlobalLimit 2
: +- LocalLimit 2
- : +- Project [t2c#x]
- : +- Filter (cast(t2b#x as bigint) <= outer(t1d#xL))
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2c#x ASC NULLS LAST], true
+ : +- Project [t2c#x]
+ : +- Filter (cast(t2b#x as bigint) <= outer(t1d#xL))
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -390,20 +398,22 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
LIMIT 2)
-- !query analysis
Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Filter NOT t1b#x IN (list#x [])
: +- GlobalLimit 2
: +- LocalLimit 2
- : +- Project [t2b#x]
- : +- Filter (cast(t2b#x as int) > 6)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2b#x ASC NULLS FIRST], true
+ : +- Project [t2b#x]
+ : +- Filter (cast(t2b#x as int) > 6)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -557,6 +567,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c NULLS LAST
LIMIT 2
OFFSET 2)
LIMIT 4
@@ -570,14 +581,15 @@ GlobalLimit 4
: +- GlobalLimit 2
: +- LocalLimit 2
: +- Offset 2
- : +- Project [t2c#x]
- : +- Filter (cast(t2b#x as int) >= 8)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2c#x ASC NULLS LAST], true
+ : +- Project [t2c#x]
+ : +- Filter (cast(t2b#x as int) >= 8)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -630,6 +642,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
LIMIT 2
OFFSET 2)
-- !query analysis
@@ -638,14 +651,15 @@ Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- GlobalLimit 2
: +- LocalLimit 2
: +- Offset 2
- : +- Project [t2b#x]
- : +- Filter (cast(t2b#x as int) > 6)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2b#x ASC NULLS FIRST], true
+ : +- Project [t2b#x]
+ : +- Filter (cast(t2b#x as int) > 6)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -660,20 +674,22 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b <= t1d
+ ORDER BY t2b
LIMIT 2)
-- !query analysis
Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Filter NOT t1b#x IN (list#x [t1d#xL])
: +- GlobalLimit 2
: +- LocalLimit 2
- : +- Project [t2b#x]
- : +- Filter (cast(t2b#x as bigint) <= outer(t1d#xL))
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2b#x ASC NULLS FIRST], true
+ : +- Project [t2b#x]
+ : +- Filter (cast(t2b#x as bigint) <= outer(t1d#xL))
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -858,6 +874,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c DESC NULLS LAST
OFFSET 2)
OFFSET 4
-- !query analysis
@@ -865,14 +882,15 @@ Offset 4
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Filter t1c#x IN (list#x [])
: +- Offset 2
- : +- Project [t2c#x]
- : +- Filter (cast(t2b#x as int) >= 8)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2c#x DESC NULLS LAST], true
+ : +- Project [t2c#x]
+ : +- Filter (cast(t2b#x as int) >= 8)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
@@ -952,19 +970,21 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
OFFSET 2)
-- !query analysis
Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Filter NOT t1b#x IN (list#x [])
: +- Offset 2
- : +- Project [t2b#x]
- : +- Filter (cast(t2b#x as int) > 6)
- : +- SubqueryAlias t2
- : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- Sort [t2b#x ASC NULLS FIRST], true
+ : +- Project [t2b#x]
+ : +- Filter (cast(t2b#x as int) > 6)
+ : +- SubqueryAlias t2
+ : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+ : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+ : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+ : +- SubqueryAlias t2
+ : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-limit.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-limit.sql
index 7c816d8a4167..a094dcb81157 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-limit.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-limit.sql
@@ -88,6 +88,7 @@ FROM t1
WHERE t1a IN (SELECT t2a
FROM t2
WHERE t1d = t2d
+ ORDER BY t2a
LIMIT 10
OFFSET 2)
LIMIT 2
@@ -109,6 +110,7 @@ FROM t1
WHERE t1a IN (SELECT t2a
FROM t2
WHERE t1d = t2d
+ ORDER BY t2a
OFFSET 2)
OFFSET 1;
@@ -118,6 +120,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c NULLS LAST
LIMIT 2)
LIMIT 4;
@@ -138,6 +141,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b <= t1d
+ ORDER BY t2c NULLS LAST
LIMIT 2)
LIMIT 4;
@@ -148,6 +152,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
LIMIT 2);
-- TC 01.05
@@ -200,6 +205,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c NULLS LAST
LIMIT 2
OFFSET 2)
LIMIT 4
@@ -225,6 +231,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
LIMIT 2
OFFSET 2);
@@ -233,6 +240,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b <= t1d
+ ORDER BY t2b
LIMIT 2);
SELECT *
@@ -296,6 +304,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c DESC NULLS LAST
OFFSET 2)
OFFSET 4;
@@ -327,6 +336,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
OFFSET 2);
-- OFFSET with NOT IN correlated
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out
index d501c93973a3..67aa738ee36b 100644
--- a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out
@@ -113,6 +113,7 @@ FROM t1
WHERE t1a IN (SELECT t2a
FROM t2
WHERE t1d = t2d
+ ORDER BY t2a
LIMIT 10
OFFSET 2)
LIMIT 2
@@ -145,6 +146,7 @@ FROM t1
WHERE t1a IN (SELECT t2a
FROM t2
WHERE t1d = t2d
+ ORDER BY t2a
OFFSET 2)
OFFSET 1
-- !query schema
@@ -161,6 +163,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c NULLS LAST
LIMIT 2)
LIMIT 4
-- !query schema
@@ -168,8 +171,6 @@ struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decim
-- !query output
val1a 16 12 10 15.0 20.0 2000 2014-07-04 01:01:00 2014-07-04
val1a 16 12 21 15.0 20.0 2000 2014-06-04 01:02:00.001 2014-06-04
-val1b 8 16 19 17.0 25.0 2600 2014-05-04 01:01:00 2014-05-04
-val1c 8 16 19 17.0 25.0 2600 2014-05-04 01:02:00.001 2014-05-05
-- !query
@@ -195,6 +196,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b <= t1d
+ ORDER BY t2c NULLS LAST
LIMIT 2)
LIMIT 4
-- !query schema
@@ -210,6 +212,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
LIMIT 2)
-- !query schema
struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decimal(4,0),t1h:timestamp,t1i:date>
@@ -218,6 +221,10 @@ val1a 16 12 10 15.0 20.0 2000 2014-07-04 01:01:00 2014-07-04
val1a 16 12 21 15.0 20.0 2000 2014-06-04 01:02:00.001 2014-06-04
val1a 6 8 10 15.0 20.0 2000 2014-04-04 01:00:00 2014-04-04
val1a 6 8 10 15.0 20.0 2000 2014-04-04 01:02:00.001 2014-04-04
+val1d 10 NULL 12 17.0 25.0 2600 2015-05-04 01:01:00 2015-05-04
+val1e 10 NULL 19 17.0 25.0 2600 2014-05-04 01:01:00 2014-05-04
+val1e 10 NULL 19 17.0 25.0 2600 2014-09-04 01:02:00.001 2014-09-04
+val1e 10 NULL 25 17.0 25.0 2600 2014-08-04 01:01:00 2014-08-04
-- !query
@@ -292,6 +299,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c NULLS LAST
LIMIT 2
OFFSET 2)
LIMIT 4
@@ -327,6 +335,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
LIMIT 2
OFFSET 2)
-- !query schema
@@ -348,14 +357,17 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b <= t1d
+ ORDER BY t2b
LIMIT 2)
-- !query schema
struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decimal(4,0),t1h:timestamp,t1i:date>
-- !query output
val1a 16 12 10 15.0 20.0 2000 2014-07-04 01:01:00 2014-07-04
val1a 16 12 21 15.0 20.0 2000 2014-06-04 01:02:00.001 2014-06-04
-val1b 8 16 19 17.0 25.0 2600 2014-05-04 01:01:00 2014-05-04
-val1c 8 16 19 17.0 25.0 2600 2014-05-04 01:02:00.001 2014-05-05
+val1d 10 NULL 12 17.0 25.0 2600 2015-05-04 01:01:00 2015-05-04
+val1e 10 NULL 19 17.0 25.0 2600 2014-05-04 01:01:00 2014-05-04
+val1e 10 NULL 19 17.0 25.0 2600 2014-09-04 01:02:00.001 2014-09-04
+val1e 10 NULL 25 17.0 25.0 2600 2014-08-04 01:01:00 2014-08-04
-- !query
@@ -456,6 +468,7 @@ FROM t1
WHERE t1c IN (SELECT t2c
FROM t2
WHERE t2b >= 8
+ ORDER BY t2c DESC NULLS LAST
OFFSET 2)
OFFSET 4
-- !query schema
@@ -504,6 +517,7 @@ FROM t1
WHERE t1b NOT IN (SELECT t2b
FROM t2
WHERE t2b > 6
+ ORDER BY t2b
OFFSET 2)
-- !query schema
struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decimal(4,0),t1h:timestamp,t1i:date>
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org