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 ea005353cecf [SPARK-58502][SQL] Make in-limit SQL test deterministic
ea005353cecf is described below
commit ea005353cecf60e6bd0f3693bf54763df701d834
Author: Vladimir Golubev <[email protected]>
AuthorDate: Tue Aug 4 13:46:40 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.
This is the branch-4.x-only version of #57696, requested in
https://github.com/apache/spark/pull/57696#issuecomment-5164048752 for Spark
4.3.0.
### 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"
The suite passed both while regenerating the golden files and in a
verification rerun (2/2 tests each).
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: OpenAI Codex (GPT-5)
Closes #57713 from vladimirg-db/make-in-limit-test-deterministic-4x.
Authored-by: Vladimir Golubev <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
(cherry picked from commit 881e5a94a15aa6c782f70b0b47a04a7e4c6fee5a)
Signed-off-by: Uros Bojanic <[email protected]>
---
.../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: [email protected]
For additional commands, e-mail: [email protected]
