@@ -3544,6 +3544,56 @@ DROP TABLE correlated_inner;
35443544statement ok
35453545DROP 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)
42154265WHERE 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----
421742671
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