summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/join.out127
-rw-r--r--src/test/regress/sql/join.sql16
2 files changed, 130 insertions, 13 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6503dd1d2f8..6705706f02e 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2999,12 +2999,12 @@ from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
explain (costs off)
select unique2, x.*
from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+-------------------------------------------------
Nested Loop
- -> Seq Scan on tenk1 a
-> Seq Scan on int4_tbl b
- Filter: (f1 = a.unique1)
+ -> Index Scan using tenk1_unique1 on tenk1 a
+ Index Cond: (unique1 = b.f1)
(4 rows)
select unique2, x.*
@@ -3022,7 +3022,7 @@ explain (costs off)
Nested Loop
-> Seq Scan on int4_tbl x
-> Index Scan using tenk1_unique1 on tenk1
- Index Cond: (x.f1 = unique1)
+ Index Cond: (unique1 = x.f1)
(4 rows)
explain (costs off)
@@ -3033,7 +3033,7 @@ explain (costs off)
Nested Loop
-> Seq Scan on int4_tbl x
-> Index Scan using tenk1_unique1 on tenk1
- Index Cond: (x.f1 = unique1)
+ Index Cond: (unique1 = x.f1)
(4 rows)
select unique2, x.*
@@ -3050,15 +3050,13 @@ from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 =
explain (costs off)
select unique2, x.*
from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on f1 = unique1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop Left Join
-> Seq Scan on int4_tbl x
- -> Subquery Scan on ss
- Filter: (x.f1 = ss.unique1)
- -> Index Scan using tenk1_unique1 on tenk1
- Index Cond: (x.f1 = unique1)
-(6 rows)
+ -> Index Scan using tenk1_unique1 on tenk1
+ Index Cond: (x.f1 = unique1)
+(4 rows)
-- check scoping of lateral versus parent references
-- the first of these should return int8_tbl.q2, the second int8_tbl.q1
@@ -3135,6 +3133,109 @@ select * from generate_series(100,200) g,
123 | 4567890123456789 | 123
(3 rows)
+-- lateral with VALUES
+explain (costs off)
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Hash Join
+ Hash Cond: ("*VALUES*".column1 = b.unique2)
+ -> Nested Loop
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ -> Values Scan on "*VALUES*"
+ -> Hash
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+(8 rows)
+
+select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ count
+-------
+ 10000
+(1 row)
+
+-- lateral injecting a strange outer join condition
+explain (costs off)
+ select * from int8_tbl a,
+ int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+ on x.q2 = ss.z;
+ QUERY PLAN
+------------------------------------------
+ Nested Loop Left Join
+ Join Filter: (x.q2 = ($0))
+ -> Nested Loop
+ -> Seq Scan on int8_tbl a
+ -> Materialize
+ -> Seq Scan on int8_tbl x
+ -> Seq Scan on int4_tbl y
+(7 rows)
+
+select * from int8_tbl a,
+ int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+ on x.q2 = ss.z;
+ q1 | q2 | q1 | q2 | z
+------------------+-------------------+------------------+-------------------+------------------
+ 123 | 456 | 123 | 456 |
+ 123 | 456 | 123 | 4567890123456789 |
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 4567890123456789 |
+ 123 | 456 | 4567890123456789 | -4567890123456789 |
+ 123 | 4567890123456789 | 123 | 456 |
+ 123 | 4567890123456789 | 123 | 4567890123456789 |
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | 123 | 123 | 456 |
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 123 |
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | 4567890123456789 | 123 | 456 |
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | -4567890123456789 | 123 | 456 |
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |
+(57 rows)
+
-- test some error cases where LATERAL should have been used but wasn't
select f1,g from int4_tbl a, generate_series(0, f1) g;
ERROR: column "f1" does not exist
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 40db5602dd4..30ea48cb926 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -885,6 +885,22 @@ select * from generate_series(100,200) g,
lateral (select * from int8_tbl a where g = q1 union all
select * from int8_tbl b where g = q2) ss;
+-- lateral with VALUES
+explain (costs off)
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+
+-- lateral injecting a strange outer join condition
+explain (costs off)
+ select * from int8_tbl a,
+ int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+ on x.q2 = ss.z;
+select * from int8_tbl a,
+ int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
+ on x.q2 = ss.z;
+
-- test some error cases where LATERAL should have been used but wasn't
select f1,g from int4_tbl a, generate_series(0, f1) g;
select f1,g from int4_tbl a, generate_series(0, a.f1) g;
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-16 10:04:30 +0000