diff options
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 3 | ||||
| -rw-r--r-- | src/backend/optimizer/util/restrictinfo.c | 11 | ||||
| -rw-r--r-- | src/include/optimizer/restrictinfo.h | 1 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 27 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 11 |
5 files changed, 52 insertions, 1 deletions
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index b6ad01be6bb..280f21cd45c 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -3802,6 +3802,7 @@ create_nestloop_plan(PlannerInfo *root, if (IS_OUTER_JOIN(best_path->jointype)) { extract_actual_join_clauses(joinrestrictclauses, + best_path->path.parent->relids, &joinclauses, &otherclauses); } else @@ -3917,6 +3918,7 @@ create_mergejoin_plan(PlannerInfo *root, if (IS_OUTER_JOIN(best_path->jpath.jointype)) { extract_actual_join_clauses(joinclauses, + best_path->jpath.path.parent->relids, &joinclauses, &otherclauses); } else @@ -4213,6 +4215,7 @@ create_hashjoin_plan(PlannerInfo *root, if (IS_OUTER_JOIN(best_path->jpath.jointype)) { extract_actual_join_clauses(joinclauses, + best_path->jpath.path.parent->relids, &joinclauses, &otherclauses); } else diff --git a/src/backend/optimizer/util/restrictinfo.c b/src/backend/optimizer/util/restrictinfo.c index 1075dde40c8..65c1abcfe13 100644 --- a/src/backend/optimizer/util/restrictinfo.c +++ b/src/backend/optimizer/util/restrictinfo.c @@ -381,6 +381,7 @@ extract_actual_clauses(List *restrictinfo_list, */ void extract_actual_join_clauses(List *restrictinfo_list, + Relids joinrelids, List **joinquals, List **otherquals) { @@ -393,7 +394,15 @@ extract_actual_join_clauses(List *restrictinfo_list, { RestrictInfo *rinfo = lfirst_node(RestrictInfo, l); - if (rinfo->is_pushed_down) + /* + * We must check both is_pushed_down and required_relids, since an + * outer-join clause that's been pushed down to some lower join level + * via path parameterization will not be marked is_pushed_down; + * nonetheless, it must be treated as a filter clause not a join + * clause so far as the lower join level is concerned. + */ + if (rinfo->is_pushed_down || + !bms_is_subset(rinfo->required_relids, joinrelids)) { if (!rinfo->pseudoconstant) *otherquals = lappend(*otherquals, rinfo->clause); diff --git a/src/include/optimizer/restrictinfo.h b/src/include/optimizer/restrictinfo.h index 9cd874d07ed..a734d798c1e 100644 --- a/src/include/optimizer/restrictinfo.h +++ b/src/include/optimizer/restrictinfo.h @@ -36,6 +36,7 @@ extern List *get_actual_clauses(List *restrictinfo_list); extern List *extract_actual_clauses(List *restrictinfo_list, bool pseudoconstant); extern void extract_actual_join_clauses(List *restrictinfo_list, + Relids joinrelids, List **joinquals, List **otherquals); extern bool join_clause_is_movable_to(RestrictInfo *rinfo, RelOptInfo *baserel); diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 84c6e9b5a40..cbc882d47ba 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3371,6 +3371,33 @@ order by fault; | 123 | 122 (1 row) +explain (costs off) +select * from +(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) +left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x +left join unnest(v1ys) as u1(u1y) on u1y = v2y; + QUERY PLAN +------------------------------------------------------------- + Nested Loop Left Join + -> Values Scan on "*VALUES*" + -> Hash Right Join + Hash Cond: (u1.u1y = "*VALUES*_1".column2) + Filter: ("*VALUES*_1".column1 = "*VALUES*".column1) + -> Function Scan on unnest u1 + -> Hash + -> Values Scan on "*VALUES*_1" +(8 rows) + +select * from +(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) +left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x +left join unnest(v1ys) as u1(u1y) on u1y = v2y; + v1x | v1ys | v2x | v2y | u1y +-----+---------+-----+-----+----- + 1 | {10,20} | 1 | 10 | 10 + 2 | {20,30} | 2 | 20 | 20 +(2 rows) + -- -- test handling of potential equivalence clauses above outer joins -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index b1e05a33bd3..86c6d5be283 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1028,6 +1028,17 @@ select * from where fault = 122 order by fault; +explain (costs off) +select * from +(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) +left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x +left join unnest(v1ys) as u1(u1y) on u1y = v2y; + +select * from +(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) +left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x +left join unnest(v1ys) as u1(u1y) on u1y = v2y; + -- -- test handling of potential equivalence clauses above outer joins -- |