diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 76 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 9 |
2 files changed, 77 insertions, 8 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index fc3e1688068..c94ac614af8 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3161,7 +3161,7 @@ explain (costs off) Nested Loop Left Join -> Seq Scan on int4_tbl x -> Index Scan using tenk1_unique1 on tenk1 - Index Cond: (unique1 = x.f1) + Index Cond: (x.f1 = unique1) (4 rows) -- check scoping of lateral versus parent references @@ -3648,12 +3648,12 @@ select * from int4_tbl i left join lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; QUERY PLAN ------------------------------------------- - Nested Loop Left Join + Hash Left Join Output: i.f1, j.f1 - Filter: (i.f1 = j.f1) + Hash Cond: (i.f1 = j.f1) -> Seq Scan on public.int4_tbl i Output: i.f1 - -> Materialize + -> Hash Output: j.f1 -> Seq Scan on public.int2_tbl j Output: j.f1 @@ -3661,10 +3661,14 @@ select * from int4_tbl i left join select * from int4_tbl i left join lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; - f1 | f1 -----+---- - 0 | 0 -(1 row) + f1 | f1 +-------------+---- + 0 | 0 + 123456 | + -123456 | + 2147483647 | + -2147483647 | +(5 rows) explain (verbose, costs off) select * from int4_tbl i left join @@ -3691,6 +3695,62 @@ select * from int4_tbl i left join -2147483647 | (5 rows) +explain (verbose, costs off) +select * from int4_tbl a, + lateral ( + select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) + ) ss; + QUERY PLAN +------------------------------------------------- + Nested Loop + Output: a.f1, b.f1, c.q1, c.q2 + -> Seq Scan on public.int4_tbl a + Output: a.f1 + -> Hash Left Join + Output: b.f1, c.q1, c.q2 + Hash Cond: (b.f1 = c.q1) + -> Seq Scan on public.int4_tbl b + Output: b.f1 + -> Hash + Output: c.q1, c.q2 + -> Seq Scan on public.int8_tbl c + Output: c.q1, c.q2 + Filter: (a.f1 = c.q2) +(14 rows) + +select * from int4_tbl a, + lateral ( + select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) + ) ss; + f1 | f1 | q1 | q2 +-------------+-------------+----+---- + 0 | 0 | | + 0 | 123456 | | + 0 | -123456 | | + 0 | 2147483647 | | + 0 | -2147483647 | | + 123456 | 0 | | + 123456 | 123456 | | + 123456 | -123456 | | + 123456 | 2147483647 | | + 123456 | -2147483647 | | + -123456 | 0 | | + -123456 | 123456 | | + -123456 | -123456 | | + -123456 | 2147483647 | | + -123456 | -2147483647 | | + 2147483647 | 0 | | + 2147483647 | 123456 | | + 2147483647 | -123456 | | + 2147483647 | 2147483647 | | + 2147483647 | -2147483647 | | + -2147483647 | 0 | | + -2147483647 | 123456 | | + -2147483647 | -123456 | | + -2147483647 | 2147483647 | | + -2147483647 | -2147483647 | | +(25 rows) + -- lateral reference in a PlaceHolderVar evaluated at join level explain (verbose, costs off) select * from diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 36853ddce49..351400f2da2 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1022,6 +1022,15 @@ select * from int4_tbl i left join lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; select * from int4_tbl i left join lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; +explain (verbose, costs off) +select * from int4_tbl a, + lateral ( + select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) + ) ss; +select * from int4_tbl a, + lateral ( + select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) + ) ss; -- lateral reference in a PlaceHolderVar evaluated at join level explain (verbose, costs off) |