From 4df8de7a682e98be2b9072458bb9e77513f53c47 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 24 Oct 2006 17:50:22 +0000 Subject: [PATCH] Fix check for whether a clauseless join has to be forced in the presence of outer joins. Originally it was only looking for overlap of the righthand side of a left join, but we have to do it on the lefthand side too. Per example from Jean-Pierre Pelletier. --- src/backend/optimizer/geqo/geqo_eval.c | 20 +++++++++++++------- src/backend/optimizer/path/joinrels.c | 16 ++++++++++++---- 2 files changed, 25 insertions(+), 11 deletions(-) diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c index 2d2f5e6826..240672edaf 100644 --- a/src/backend/optimizer/geqo/geqo_eval.c +++ b/src/backend/optimizer/geqo/geqo_eval.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.80 2006/03/05 15:58:28 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.81 2006/10/24 17:50:22 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -262,23 +262,29 @@ desirable_join(PlannerInfo *root, return true; /* - * Join if the rels are members of the same outer-join RHS. This is needed - * to improve the odds that we will find a valid solution in a case where - * an OJ RHS has a clauseless join. + * Join if the rels are members of the same outer-join side. This is + * needed to ensure that we can find a valid solution in a case where + * an OJ contains a clauseless join. */ foreach(l, root->oj_info_list) { OuterJoinInfo *ojinfo = (OuterJoinInfo *) lfirst(l); + /* ignore full joins --- other mechanisms preserve their ordering */ + if (ojinfo->is_full_join) + continue; if (bms_is_subset(outer_rel->relids, ojinfo->min_righthand) && bms_is_subset(inner_rel->relids, ojinfo->min_righthand)) return true; + if (bms_is_subset(outer_rel->relids, ojinfo->min_lefthand) && + bms_is_subset(inner_rel->relids, ojinfo->min_lefthand)) + return true; } /* - * Join if the rels are members of the same IN sub-select. This is needed - * to improve the odds that we will find a valid solution in a case where - * an IN sub-select has a clauseless join. + * Join if the rels are members of the same IN sub-select. This is needed + * to ensure that we can find a valid solution in a case where an IN + * sub-select has a clauseless join. */ foreach(l, root->in_info_list) { diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index bed9db5043..17b5f31915 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.80 2006/10/04 00:29:54 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.81 2006/10/24 17:50:22 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -87,9 +87,9 @@ make_rels_by_joins(PlannerInfo *root, int level, List **joinrels) /* * An exception occurs when there is a clauseless join inside a - * construct that restricts join order, i.e., an outer join RHS or + * construct that restricts join order, i.e., an outer join or * an IN (sub-SELECT) construct. Here, the rel may well have join - * clauses against stuff outside the OJ RHS or IN sub-SELECT, but + * clauses against stuff outside its OJ side or IN sub-SELECT, but * the clauseless join *must* be done before we can make use of * those join clauses. So do the clauseless join bit. * @@ -331,7 +331,7 @@ make_rels_by_clauseless_joins(PlannerInfo *root, /* * has_join_restriction * Detect whether the specified relation has join-order restrictions - * due to being inside an OJ RHS or an IN (sub-SELECT). + * due to being inside an outer join or an IN (sub-SELECT). */ static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel) @@ -342,8 +342,16 @@ has_join_restriction(PlannerInfo *root, RelOptInfo *rel) { OuterJoinInfo *ojinfo = (OuterJoinInfo *) lfirst(l); + /* ignore full joins --- other mechanisms preserve their ordering */ + if (ojinfo->is_full_join) + continue; + /* anything inside the RHS is definitely restricted */ if (bms_is_subset(rel->relids, ojinfo->min_righthand)) return true; + /* if it's a proper subset of the LHS, it's also restricted */ + if (bms_is_subset(rel->relids, ojinfo->min_lefthand) && + !bms_equal(rel->relids, ojinfo->min_lefthand)) + return true; } foreach(l, root->in_info_list) -- 2.11.0