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

Commit8124215

Browse files
committed
Repair incorrect placement of WHERE clauses when there are multiple,
rearrangeable outer joins and the WHERE clause is non-strict and mentionsonly nullable-side relations. New bug in 8.2, caused by new logic to allowrearranging outer joins. Per bug #2807 from Ross Cohen; thanks to JeffDavis for producing a usable test case.
1 parentb307d7a commit8124215

File tree

1 file changed

+58
-30
lines changed

1 file changed

+58
-30
lines changed

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

Lines changed: 58 additions & 30 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.123 2006/10/04 00:29:54 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.124 2006/12/07 19:33:40 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -735,30 +735,69 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
735735
* For a non-outer-join qual, we can evaluate the qual as soon as (1)
736736
* we have all the rels it mentions, and (2) we are at or above any
737737
* outer joins that can null any of these rels and are below the
738-
* syntactic location of the given qual. To enforce the latter, scan
739-
* the oj_info_list and merge the required-relid sets of any such OJs
740-
* into the clause's own reference list. At the time we are called,
741-
* the oj_info_list contains only outer joins below this qual.
738+
* syntactic location of the given qual. We must enforce (2) because
739+
* pushing down such a clause below the OJ might cause the OJ to emit
740+
* null-extended rows that should not have been formed, or that should
741+
* have been rejected by the clause. (This is only an issue for
742+
* non-strict quals, since if we can prove a qual mentioning only
743+
* nullable rels is strict, we'd have reduced the outer join to an
744+
* inner join in reduce_outer_joins().)
745+
*
746+
* To enforce (2), scan the oj_info_list and merge the required-relid
747+
* sets of any such OJs into the clause's own reference list. At the
748+
* time we are called, the oj_info_list contains only outer joins
749+
* below this qual. We have to repeat the scan until no new relids
750+
* get added; this ensures that the qual is suitably delayed regardless
751+
* of the order in which OJs get executed. As an example, if we have
752+
* one OJ with LHS=A, RHS=B, and one with LHS=B, RHS=C, it is implied
753+
* that these can be done in either order; if the B/C join is done
754+
* first then the join to A can null C, so a qual actually mentioning
755+
* only C cannot be applied below the join to A.
742756
*/
743-
Relidsaddrelids=NULL;
744-
ListCell*l;
757+
boolfound_some;
745758

746759
outerjoin_delayed= false;
747-
foreach(l,root->oj_info_list)
748-
{
749-
OuterJoinInfo*ojinfo= (OuterJoinInfo*)lfirst(l);
760+
do {
761+
ListCell*l;
750762

751-
if (bms_overlap(relids,ojinfo->min_righthand)||
752-
(ojinfo->is_full_join&&
753-
bms_overlap(relids,ojinfo->min_lefthand)))
763+
found_some= false;
764+
foreach(l,root->oj_info_list)
754765
{
755-
addrelids=bms_add_members(addrelids,ojinfo->min_lefthand);
756-
addrelids=bms_add_members(addrelids,ojinfo->min_righthand);
757-
outerjoin_delayed= true;
766+
OuterJoinInfo*ojinfo= (OuterJoinInfo*)lfirst(l);
767+
768+
/* do we have any nullable rels of this OJ? */
769+
if (bms_overlap(relids,ojinfo->min_righthand)||
770+
(ojinfo->is_full_join&&
771+
bms_overlap(relids,ojinfo->min_lefthand)))
772+
{
773+
/* yes; do we have all its rels? */
774+
if (!bms_is_subset(ojinfo->min_lefthand,relids)||
775+
!bms_is_subset(ojinfo->min_righthand,relids))
776+
{
777+
/* no, so add them in */
778+
relids=bms_add_members(relids,
779+
ojinfo->min_lefthand);
780+
relids=bms_add_members(relids,
781+
ojinfo->min_righthand);
782+
outerjoin_delayed= true;
783+
/* we'll need another iteration */
784+
found_some= true;
785+
}
786+
}
758787
}
759-
}
788+
}while (found_some);
760789

761-
if (bms_is_subset(addrelids,relids))
790+
if (outerjoin_delayed)
791+
{
792+
/* Should still be a subset of current scope ... */
793+
Assert(bms_is_subset(relids,qualscope));
794+
/*
795+
* Because application of the qual will be delayed by outer join,
796+
* we mustn't assume its vars are equal everywhere.
797+
*/
798+
maybe_equijoin= false;
799+
}
800+
else
762801
{
763802
/*
764803
* Qual is not delayed by any lower outer-join restriction. If it
@@ -774,19 +813,8 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
774813
else
775814
maybe_equijoin= false;
776815
}
777-
else
778-
{
779-
relids=bms_union(relids,addrelids);
780-
/* Should still be a subset of current scope ... */
781-
Assert(bms_is_subset(relids,qualscope));
782816

783-
/*
784-
* Because application of the qual will be delayed by outer join,
785-
* we mustn't assume its vars are equal everywhere.
786-
*/
787-
maybe_equijoin= false;
788-
}
789-
bms_free(addrelids);
817+
/* Since it doesn't mention the LHS, it's certainly not an OJ clause */
790818
maybe_outer_join= false;
791819
}
792820

0 commit comments

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