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

Commitc171176

Browse files
committed
Repair bug in 8.2's new logic for planning outer joins: we have to allow joins
that overlap an outer join's min_righthand but aren't fully contained in it,to support joining within the RHS after having performed an outer join thatcan commute with this one. Aside from the direct fix in make_join_rel(),fix has_join_restriction() and GEQO's desirable_join() to consider thispossibility. Per report from Ian Harding.
1 parent849b070 commitc171176

File tree

4 files changed

+55
-23
lines changed

4 files changed

+55
-23
lines changed

‎src/backend/optimizer/README

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -223,8 +223,8 @@ rels to the OJ's syntactic rels may be legal. Per identities 1 and 2,
223223
non-FULL joins can be freely associated into the lefthand side of an
224224
OJ, but in general they can't be associated into the righthand side.
225225
So the restriction enforced by make_join_rel is that a proposed join
226-
can't joinacross a RHS boundary (ie, join anything inside theRHS
227-
to anything else) unless the join validly implements some outer join.
226+
can't joina rel within or partly within anRHS boundary to one outside
227+
the boundary, unless the join validly implements some outer join.
228228
(To support use of identity 3, we have to allow cases where an apparent
229229
violation of a lower OJ's RHS is committed while forming an upper OJ.
230230
If this wouldn't in fact be legal, the upper OJ's minimum LHS or RHS

‎src/backend/optimizer/geqo/geqo_eval.c

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.83 2007/01/05 22:19:30 momjian Exp $
9+
* $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.84 2007/02/13 02:31:02 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -182,7 +182,7 @@ gimme_tree(Gene *tour, int num_gene, GeqoEvalData *evaldata)
182182
* tour other than the one given. To the extent that the heuristics are
183183
* helpful, however, this will be a better plan than the raw tour.
184184
*
185-
* Also, when a join attempt fails (because ofIN-clause constraints), we
185+
* Also, when a join attempt fails (because ofOJ or IN constraints), we
186186
* may be able to recover and produce a workable plan, where the old code
187187
* just had to give up. This case acts the same as a false result from
188188
* desirable_join().
@@ -262,9 +262,9 @@ desirable_join(PlannerInfo *root,
262262
return true;
263263

264264
/*
265-
* Join if the relsare members ofthe same outer-join side. This is
266-
* needed to ensure that we can find a valid solution in a case where
267-
* an OJ contains a clauseless join.
265+
* Join if the relsoverlapthe same outer-join side and don't already
266+
*implement the outer join. This isneeded to ensure that we can find a
267+
*valid solution in a case wherean OJ contains a clauseless join.
268268
*/
269269
foreach(l,root->oj_info_list)
270270
{
@@ -273,11 +273,15 @@ desirable_join(PlannerInfo *root,
273273
/* ignore full joins --- other mechanisms preserve their ordering */
274274
if (ojinfo->is_full_join)
275275
continue;
276-
if (bms_is_subset(outer_rel->relids,ojinfo->min_righthand)&&
277-
bms_is_subset(inner_rel->relids,ojinfo->min_righthand))
276+
if (bms_overlap(outer_rel->relids,ojinfo->min_righthand)&&
277+
bms_overlap(inner_rel->relids,ojinfo->min_righthand)&&
278+
!bms_overlap(outer_rel->relids,ojinfo->min_lefthand)&&
279+
!bms_overlap(inner_rel->relids,ojinfo->min_lefthand))
278280
return true;
279-
if (bms_is_subset(outer_rel->relids,ojinfo->min_lefthand)&&
280-
bms_is_subset(inner_rel->relids,ojinfo->min_lefthand))
281+
if (bms_overlap(outer_rel->relids,ojinfo->min_lefthand)&&
282+
bms_overlap(inner_rel->relids,ojinfo->min_lefthand)&&
283+
!bms_overlap(outer_rel->relids,ojinfo->min_righthand)&&
284+
!bms_overlap(inner_rel->relids,ojinfo->min_righthand))
281285
return true;
282286
}
283287

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

Lines changed: 30 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.84 2007/01/20 20:45:39 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.85 2007/02/13 02:31:03 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -350,8 +350,9 @@ has_join_restriction(PlannerInfo *root, RelOptInfo *rel)
350350
/* ignore full joins --- other mechanisms preserve their ordering */
351351
if (ojinfo->is_full_join)
352352
continue;
353-
/* anything inside the RHS is definitely restricted */
354-
if (bms_is_subset(rel->relids,ojinfo->min_righthand))
353+
/* if it overlaps RHS and isn't yet joined to LHS, it's restricted */
354+
if (bms_overlap(rel->relids,ojinfo->min_righthand)&&
355+
!bms_overlap(rel->relids,ojinfo->min_lefthand))
355356
return true;
356357
/* if it's a proper subset of the LHS, it's also restricted */
357358
if (bms_is_subset(rel->relids,ojinfo->min_lefthand)&&
@@ -468,16 +469,36 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
468469
/*----------
469470
* Otherwise, the proposed join overlaps the RHS but isn't
470471
* a valid implementation of this OJ. It might still be
471-
* a valid implementation of some other OJ, however. We have
472-
* to allow this to support the associative identity
473-
*(a LJ b on Pab) LJ c ON Pbc = a LJ (b LJ c ON Pbc) on Pab
472+
* a legal join, however. If both inputs overlap the RHS,
473+
* assume that it's OK. Since the inputs presumably got past
474+
* this function's checks previously, they can't overlap the
475+
* LHS and their violations of the RHS boundary must represent
476+
* OJs that have been determined to commute with this one.
477+
* We have to allow this to work correctly in cases like
478+
*(a LEFT JOIN (b JOIN (c LEFT JOIN d)))
479+
* when the c/d join has been determined to commute with the join
480+
* to a, and hence d is not part of min_righthand for the upper
481+
* join. It should be legal to join b to c/d but this will appear
482+
* as a violation of the upper join's RHS.
483+
* Furthermore, if one input overlaps the RHS and the other does
484+
* not, we should still allow the join if it is a valid
485+
* implementation of some other OJ. We have to allow this to
486+
* support the associative identity
487+
*(a LJ b on Pab) LJ c ON Pbc = a LJ (b LJ c ON Pbc) on Pab
474488
* since joining B directly to C violates the lower OJ's RHS.
475489
* We assume that make_outerjoininfo() set things up correctly
476-
* so that we'll only match tothe upperOJ if thetransformation
477-
*is valid.Set flag here to check at bottom of loop.
490+
* so that we'll only match tosomeOJ if thejoin is valid.
491+
* Set flag here to check at bottom of loop.
478492
*----------
479493
*/
480-
is_valid_inner= false;
494+
if (bms_overlap(rel1->relids,ojinfo->min_righthand)&&
495+
bms_overlap(rel2->relids,ojinfo->min_righthand))
496+
{
497+
/* seems OK */
498+
Assert(!bms_overlap(joinrelids,ojinfo->min_lefthand));
499+
}
500+
else
501+
is_valid_inner= false;
481502
}
482503
}
483504

‎src/backend/optimizer/plan/initsplan.c

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.129 2007/02/01 19:10:26 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.130 2007/02/13 02:31:03 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -370,7 +370,9 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
370370

371371
/*
372372
* For an OJ, form the OuterJoinInfo now, because we need the OJ's
373-
* semantic scope (ojscope) to pass to distribute_qual_to_rels.
373+
* semantic scope (ojscope) to pass to distribute_qual_to_rels. But
374+
* we mustn't add it to oj_info_list just yet, because we don't want
375+
* distribute_qual_to_rels to think it is an outer join below us.
374376
*/
375377
if (j->jointype!=JOIN_INNER)
376378
{
@@ -451,8 +453,13 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
451453
* the caller, so that left_rels is always the nonnullable side. Hence
452454
* we need only distinguish the LEFT and FULL cases.
453455
*
454-
* The node should eventually beput into root->oj_info_list, but we
456+
* The node should eventually beappended to root->oj_info_list, but we
455457
* do not do that here.
458+
*
459+
* Note: we assume that this function is invoked bottom-up, so that
460+
* root->oj_info_list already contains entries for all outer joins that are
461+
* syntactically below this one; and indeed that oj_info_list is ordered
462+
* with syntactically lower joins listed first.
456463
*/
457464
staticOuterJoinInfo*
458465
make_outerjoininfo(PlannerInfo*root,

0 commit comments

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