Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit216977a

Browse files
committed
Fix an oversight in checking whether a join with LATERAL refs is legal.
In many cases, we can implement a semijoin as a plain innerjoin by firstpassing the righthand-side relation through a unique-ification step.However, one of the cases where this does NOT work is where the RHS hasa LATERAL reference to the LHS; that makes the RHS dependent on the LHSso that unique-ification is meaningless. joinpath.c understood this,and so would not generate any join paths of this kind ... but join_is_legalneglected to check for the case, so it would think that we could do it.The upshot would be a "could not devise a query plan for the given query"failure once we had failed to generate any join paths at all for the bogusjoin pair.Back-patch to 9.3 where LATERAL was added.
1 parent0efa0f6 commit216977a

File tree

3 files changed

+54
-2
lines changed

3 files changed

+54
-2
lines changed

‎src/backend/optimizer/path/joinrels.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -536,7 +536,9 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
536536
if (!bms_is_subset(ljinfo->lateral_lhs,rel1->relids))
537537
return false;/* rel1 can't compute the required parameter */
538538
if (match_sjinfo&&
539-
(reversed||match_sjinfo->jointype==JOIN_FULL))
539+
(reversed||
540+
unique_ified||
541+
match_sjinfo->jointype==JOIN_FULL))
540542
return false;/* not implementable as nestloop */
541543
}
542544
if (bms_is_subset(ljinfo->lateral_rhs,rel1->relids)&&
@@ -549,7 +551,9 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
549551
if (!bms_is_subset(ljinfo->lateral_lhs,rel2->relids))
550552
return false;/* rel2 can't compute the required parameter */
551553
if (match_sjinfo&&
552-
(!reversed||match_sjinfo->jointype==JOIN_FULL))
554+
(!reversed||
555+
unique_ified||
556+
match_sjinfo->jointype==JOIN_FULL))
553557
return false;/* not implementable as nestloop */
554558
}
555559
}

‎src/test/regress/expected/join.out

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4356,6 +4356,41 @@ select * from
43564356
Output: 3
43574357
(11 rows)
43584358

4359+
-- check we don't try to do a unique-ified semijoin with LATERAL
4360+
explain (verbose, costs off)
4361+
select * from
4362+
(values (0,9998), (1,1000)) v(id,x),
4363+
lateral (select f1 from int4_tbl
4364+
where f1 = any (select unique1 from tenk1
4365+
where unique2 = v.x offset 0)) ss;
4366+
QUERY PLAN
4367+
----------------------------------------------------------------------
4368+
Nested Loop
4369+
Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1
4370+
-> Values Scan on "*VALUES*"
4371+
Output: "*VALUES*".column1, "*VALUES*".column2
4372+
-> Hash Semi Join
4373+
Output: int4_tbl.f1
4374+
Hash Cond: (int4_tbl.f1 = tenk1.unique1)
4375+
-> Seq Scan on public.int4_tbl
4376+
Output: int4_tbl.f1
4377+
-> Hash
4378+
Output: tenk1.unique1
4379+
-> Index Scan using tenk1_unique2 on public.tenk1
4380+
Output: tenk1.unique1
4381+
Index Cond: (tenk1.unique2 = "*VALUES*".column2)
4382+
(14 rows)
4383+
4384+
select * from
4385+
(values (0,9998), (1,1000)) v(id,x),
4386+
lateral (select f1 from int4_tbl
4387+
where f1 = any (select unique1 from tenk1
4388+
where unique2 = v.x offset 0)) ss;
4389+
id | x | f1
4390+
----+------+----
4391+
0 | 9998 | 0
4392+
(1 row)
4393+
43594394
-- test some error cases where LATERAL should have been used but wasn't
43604395
select f1,g from int4_tbl a, (select f1 as g) ss;
43614396
ERROR: column "f1" does not exist

‎src/test/regress/sql/join.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1286,6 +1286,19 @@ select * from
12861286
select*from (select3as z) zwherez.z=x.x
12871287
) zzonzz.z=y.y;
12881288

1289+
-- check we don't try to do a unique-ified semijoin with LATERAL
1290+
explain (verbose, costs off)
1291+
select*from
1292+
(values (0,9998), (1,1000)) v(id,x),
1293+
lateral (select f1from int4_tbl
1294+
where f1= any (select unique1from tenk1
1295+
where unique2=v.x offset0)) ss;
1296+
select*from
1297+
(values (0,9998), (1,1000)) v(id,x),
1298+
lateral (select f1from int4_tbl
1299+
where f1= any (select unique1from tenk1
1300+
where unique2=v.x offset0)) ss;
1301+
12891302
-- test some error cases where LATERAL should have been used but wasn't
12901303
select f1,gfrom int4_tbl a, (select f1as g) ss;
12911304
select f1,gfrom int4_tbl a, (selecta.f1as g) ss;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp