Skip to content

Commit d7ec737

Browse files
committed
test: move the JOIN ON alias tests out of the end of subquery.slt
1 parent 5666afe commit d7ec737

1 file changed

Lines changed: 50 additions & 50 deletions

File tree

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 50 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -3544,6 +3544,56 @@ DROP TABLE correlated_inner;
35443544
statement ok
35453545
DROP TABLE multi_in_right_empty;
35463546

3547+
# A subquery in a JOIN ON correlated on an aliased table
3548+
statement ok
3549+
CREATE TABLE on_alias_t (a INT) AS VALUES (1), (2);
3550+
3551+
statement ok
3552+
CREATE TABLE on_alias_s (b INT) AS VALUES (1);
3553+
3554+
query II rowsort
3555+
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON x.a = ANY (SELECT b FROM on_alias_s);
3556+
----
3557+
1 1
3558+
1 2
3559+
3560+
query II rowsort
3561+
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON x.a > ALL (SELECT b FROM on_alias_s);
3562+
----
3563+
2 1
3564+
2 2
3565+
3566+
query II rowsort
3567+
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON EXISTS (SELECT 1 FROM on_alias_s WHERE on_alias_s.b = x.a);
3568+
----
3569+
1 1
3570+
1 2
3571+
3572+
query II rowsort
3573+
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON NOT EXISTS (SELECT 1 FROM on_alias_s WHERE on_alias_s.b = x.a);
3574+
----
3575+
2 1
3576+
2 2
3577+
3578+
query II rowsort
3579+
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON x.a IN (SELECT b FROM on_alias_s WHERE on_alias_s.b = x.a);
3580+
----
3581+
1 1
3582+
1 2
3583+
3584+
# The subquery is nested in an OR, so it is not a top-level conjunct
3585+
query II rowsort
3586+
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON (EXISTS (SELECT 1 FROM on_alias_s WHERE on_alias_s.b = x.a) OR x.a = 99);
3587+
----
3588+
1 1
3589+
1 2
3590+
3591+
statement ok
3592+
DROP TABLE on_alias_t;
3593+
3594+
statement ok
3595+
DROP TABLE on_alias_s;
3596+
35473597
# Regression test for #25507: a correlated filter below the side of an outer
35483598
# join that the join fills with NULLs must not be pulled above the join. The
35493599
# filter decides which rows of the other side are unmatched, so once it is
@@ -4215,53 +4265,3 @@ SELECT * FROM (VALUES (1)) AS x(d)
42154265
WHERE x.d IN (SELECT t.c FROM (SELECT * FROM (VALUES (1)) AS y(c) WHERE y.c = x.d) AS t WHERE x.d > 0);
42164266
----
42174267
1
4218-
4219-
# A subquery in a JOIN ON correlated on an aliased table
4220-
statement ok
4221-
CREATE TABLE on_alias_t (a INT) AS VALUES (1), (2);
4222-
4223-
statement ok
4224-
CREATE TABLE on_alias_s (b INT) AS VALUES (1);
4225-
4226-
query II rowsort
4227-
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON x.a = ANY (SELECT b FROM on_alias_s);
4228-
----
4229-
1 1
4230-
1 2
4231-
4232-
query II rowsort
4233-
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON x.a > ALL (SELECT b FROM on_alias_s);
4234-
----
4235-
2 1
4236-
2 2
4237-
4238-
query II rowsort
4239-
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON EXISTS (SELECT 1 FROM on_alias_s WHERE on_alias_s.b = x.a);
4240-
----
4241-
1 1
4242-
1 2
4243-
4244-
query II rowsort
4245-
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON NOT EXISTS (SELECT 1 FROM on_alias_s WHERE on_alias_s.b = x.a);
4246-
----
4247-
2 1
4248-
2 2
4249-
4250-
query II rowsort
4251-
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON x.a IN (SELECT b FROM on_alias_s WHERE on_alias_s.b = x.a);
4252-
----
4253-
1 1
4254-
1 2
4255-
4256-
# The subquery is nested in an OR, so it is not a top-level conjunct
4257-
query II rowsort
4258-
SELECT * FROM on_alias_t AS x JOIN on_alias_t AS y ON (EXISTS (SELECT 1 FROM on_alias_s WHERE on_alias_s.b = x.a) OR x.a = 99);
4259-
----
4260-
1 1
4261-
1 2
4262-
4263-
statement ok
4264-
DROP TABLE on_alias_t;
4265-
4266-
statement ok
4267-
DROP TABLE on_alias_s;

0 commit comments

Comments
 (0)