diff options
| -rw-r--r-- | src/backend/optimizer/path/joinrels.c | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 35 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 13 |
3 files changed, 54 insertions, 2 deletions
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index fe9fd573174..b6c9494fed6 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -536,7 +536,9 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, if (!bms_is_subset(ljinfo->lateral_lhs, rel1->relids)) return false; /* rel1 can't compute the required parameter */ if (match_sjinfo && - (reversed || match_sjinfo->jointype == JOIN_FULL)) + (reversed || + unique_ified || + match_sjinfo->jointype == JOIN_FULL)) return false; /* not implementable as nestloop */ } if (bms_is_subset(ljinfo->lateral_rhs, rel1->relids) && @@ -549,7 +551,9 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, if (!bms_is_subset(ljinfo->lateral_lhs, rel2->relids)) return false; /* rel2 can't compute the required parameter */ if (match_sjinfo && - (!reversed || match_sjinfo->jointype == JOIN_FULL)) + (!reversed || + unique_ified || + match_sjinfo->jointype == JOIN_FULL)) return false; /* not implementable as nestloop */ } } diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 1afd0c328b5..10336d48a3a 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4527,6 +4527,41 @@ select * from Output: 3 (11 rows) +-- check we don't try to do a unique-ified semijoin with LATERAL +explain (verbose, costs off) +select * from + (values (0,9998), (1,1000)) v(id,x), + lateral (select f1 from int4_tbl + where f1 = any (select unique1 from tenk1 + where unique2 = v.x offset 0)) ss; + QUERY PLAN +---------------------------------------------------------------------- + Nested Loop + Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1 + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1, "*VALUES*".column2 + -> Nested Loop Semi Join + Output: int4_tbl.f1 + Join Filter: (int4_tbl.f1 = tenk1.unique1) + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 + -> Materialize + Output: tenk1.unique1 + -> Index Scan using tenk1_unique2 on public.tenk1 + Output: tenk1.unique1 + Index Cond: (tenk1.unique2 = "*VALUES*".column2) +(14 rows) + +select * from + (values (0,9998), (1,1000)) v(id,x), + lateral (select f1 from int4_tbl + where f1 = any (select unique1 from tenk1 + where unique2 = v.x offset 0)) ss; + id | x | f1 +----+------+---- + 0 | 9998 | 0 +(1 row) + -- test some error cases where LATERAL should have been used but wasn't select f1,g from int4_tbl a, (select f1 as g) ss; ERROR: column "f1" does not exist diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index d34cefac5a1..7553aefc6bd 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1365,6 +1365,19 @@ select * from select * from (select 3 as z offset 0) z where z.z = x.x ) zz on zz.z = y.y; +-- check we don't try to do a unique-ified semijoin with LATERAL +explain (verbose, costs off) +select * from + (values (0,9998), (1,1000)) v(id,x), + lateral (select f1 from int4_tbl + where f1 = any (select unique1 from tenk1 + where unique2 = v.x offset 0)) ss; +select * from + (values (0,9998), (1,1000)) v(id,x), + lateral (select f1 from int4_tbl + where f1 = any (select unique1 from tenk1 + where unique2 = v.x offset 0)) ss; + -- test some error cases where LATERAL should have been used but wasn't select f1,g from int4_tbl a, (select f1 as g) ss; select f1,g from int4_tbl a, (select a.f1 as g) ss; |