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

Commit45f8eaa

Browse files
committed
Fix improper interaction of FULL JOINs with lateral references.
join_is_legal() needs to reject forming certain outer joins in caseswhere that would lead the planner down a blind alley. However, itmistakenly supposed that the way to handle full joins was to treat themas applying the same constraints as for left joins, only to both sides.That doesn't work, as shown in bug #15741 from Anthony Skorski: givena lateral reference out of a join that's fully enclosed by a full join,the code would fail to believe that any join ordering is legal, resultingin errors like "failed to build any N-way joins".However, we don't really need to consider full joins at all for thispurpose, because we effectively force them to be evaluated in syntacticorder, and that order is always legal for lateral references. Hence,get rid of this broken logic for full joins and just ignore them instead.This seems to have been an oversight in commit7e19db0.Back-patch to all supported branches, as that was.Discussion:https://postgr.es/m/15741-276f1f464b3f40eb@postgresql.org
1 parentc660003 commit45f8eaa

File tree

3 files changed

+44
-9
lines changed

3 files changed

+44
-9
lines changed

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

Lines changed: 4 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -623,22 +623,17 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
623623
{
624624
SpecialJoinInfo*sjinfo= (SpecialJoinInfo*)lfirst(l);
625625

626+
/* ignore full joins --- their ordering is predetermined */
627+
if (sjinfo->jointype==JOIN_FULL)
628+
continue;
629+
626630
if (bms_overlap(sjinfo->min_lefthand,join_plus_rhs)&&
627631
!bms_is_subset(sjinfo->min_righthand,join_plus_rhs))
628632
{
629633
join_plus_rhs=bms_add_members(join_plus_rhs,
630634
sjinfo->min_righthand);
631635
more= true;
632636
}
633-
/* full joins constrain both sides symmetrically */
634-
if (sjinfo->jointype==JOIN_FULL&&
635-
bms_overlap(sjinfo->min_righthand,join_plus_rhs)&&
636-
!bms_is_subset(sjinfo->min_lefthand,join_plus_rhs))
637-
{
638-
join_plus_rhs=bms_add_members(join_plus_rhs,
639-
sjinfo->min_lefthand);
640-
more= true;
641-
}
642637
}
643638
}while (more);
644639
if (bms_overlap(join_plus_rhs,join_lateral_rels))

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

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1391,6 +1391,31 @@ SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
13911391
3 | 3 | 30 | 8
13921392
(45 rows)
13931393

1394+
-- check handling of FULL JOIN with multiple lateral references (bug #15741)
1395+
SELECT *
1396+
FROM (VALUES (1),(2)) v1(r1)
1397+
LEFT JOIN LATERAL (
1398+
SELECT *
1399+
FROM generate_series(1, v1.r1) AS gs1
1400+
LEFT JOIN LATERAL (
1401+
SELECT *
1402+
FROM generate_series(1, gs1) AS gs2
1403+
LEFT JOIN generate_series(1, gs2) AS gs3 ON TRUE
1404+
) AS ss1 ON TRUE
1405+
FULL JOIN generate_series(1, v1.r1) AS gs4 ON FALSE
1406+
) AS ss0 ON TRUE;
1407+
r1 | gs1 | gs2 | gs3 | gs4
1408+
----+-----+-----+-----+-----
1409+
1 | | | | 1
1410+
1 | 1 | 1 | 1 |
1411+
2 | | | | 1
1412+
2 | | | | 2
1413+
2 | 1 | 1 | 1 |
1414+
2 | 2 | 1 | 1 |
1415+
2 | 2 | 2 | 1 |
1416+
2 | 2 | 2 | 2 |
1417+
(8 rows)
1418+
13941419
DROP FUNCTION rngfunc_sql(int,int);
13951420
DROP FUNCTION rngfunc_mat(int,int);
13961421
DROP SEQUENCE rngfunc_rescan_seq1;

‎src/test/regress/sql/rangefuncs.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -319,6 +319,21 @@ SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
319319
LATERAL (SELECT r1,*FROM (VALUES (10),(20),(30)) v2(r2)
320320
LEFT JOIN generate_series(r1,2+r2/5) f(i)ON ((r2+i)<100) OFFSET0) s1;
321321

322+
-- check handling of FULL JOIN with multiple lateral references (bug #15741)
323+
324+
SELECT*
325+
FROM (VALUES (1),(2)) v1(r1)
326+
LEFT JOIN LATERAL (
327+
SELECT*
328+
FROM generate_series(1,v1.r1)AS gs1
329+
LEFT JOIN LATERAL (
330+
SELECT*
331+
FROM generate_series(1, gs1)AS gs2
332+
LEFT JOIN generate_series(1, gs2)AS gs3ON TRUE
333+
)AS ss1ON TRUE
334+
FULLJOIN generate_series(1,v1.r1)AS gs4ON FALSE
335+
)AS ss0ON TRUE;
336+
322337
DROPFUNCTION rngfunc_sql(int,int);
323338
DROPFUNCTION rngfunc_mat(int,int);
324339
DROPSEQUENCE rngfunc_rescan_seq1;

0 commit comments

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