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

Commita6492ff

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 parent16c4e6d commita6492ff

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
@@ -4527,6 +4527,41 @@ select * from
45274527
Output: 3
45284528
(11 rows)
45294529

4530+
-- check we don't try to do a unique-ified semijoin with LATERAL
4531+
explain (verbose, costs off)
4532+
select * from
4533+
(values (0,9998), (1,1000)) v(id,x),
4534+
lateral (select f1 from int4_tbl
4535+
where f1 = any (select unique1 from tenk1
4536+
where unique2 = v.x offset 0)) ss;
4537+
QUERY PLAN
4538+
----------------------------------------------------------------------
4539+
Nested Loop
4540+
Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1
4541+
-> Values Scan on "*VALUES*"
4542+
Output: "*VALUES*".column1, "*VALUES*".column2
4543+
-> Nested Loop Semi Join
4544+
Output: int4_tbl.f1
4545+
Join Filter: (int4_tbl.f1 = tenk1.unique1)
4546+
-> Seq Scan on public.int4_tbl
4547+
Output: int4_tbl.f1
4548+
-> Materialize
4549+
Output: tenk1.unique1
4550+
-> Index Scan using tenk1_unique2 on public.tenk1
4551+
Output: tenk1.unique1
4552+
Index Cond: (tenk1.unique2 = "*VALUES*".column2)
4553+
(14 rows)
4554+
4555+
select * from
4556+
(values (0,9998), (1,1000)) v(id,x),
4557+
lateral (select f1 from int4_tbl
4558+
where f1 = any (select unique1 from tenk1
4559+
where unique2 = v.x offset 0)) ss;
4560+
id | x | f1
4561+
----+------+----
4562+
0 | 9998 | 0
4563+
(1 row)
4564+
45304565
-- test some error cases where LATERAL should have been used but wasn't
45314566
select f1,g from int4_tbl a, (select f1 as g) ss;
45324567
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
@@ -1365,6 +1365,19 @@ select * from
13651365
select*from (select3as z offset0) zwherez.z=x.x
13661366
) zzonzz.z=y.y;
13671367

1368+
-- check we don't try to do a unique-ified semijoin with LATERAL
1369+
explain (verbose, costs off)
1370+
select*from
1371+
(values (0,9998), (1,1000)) v(id,x),
1372+
lateral (select f1from int4_tbl
1373+
where f1= any (select unique1from tenk1
1374+
where unique2=v.x offset0)) ss;
1375+
select*from
1376+
(values (0,9998), (1,1000)) v(id,x),
1377+
lateral (select f1from int4_tbl
1378+
where f1= any (select unique1from tenk1
1379+
where unique2=v.x offset0)) ss;
1380+
13681381
-- test some error cases where LATERAL should have been used but wasn't
13691382
select f1,gfrom int4_tbl a, (select f1as g) ss;
13701383
select f1,gfrom int4_tbl a, (selecta.f1as g) ss;

0 commit comments

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