diff options
| -rw-r--r-- | src/backend/optimizer/prep/prepjointree.c | 12 | ||||
| -rw-r--r-- | src/backend/optimizer/util/var.c | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 52 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 31 |
4 files changed, 97 insertions, 6 deletions
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index e874064fcdd..3c492c0199b 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -730,6 +730,18 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, } /* + * We must flatten any join alias Vars in the subquery's targetlist, + * because pulling up the subquery's subqueries might have changed their + * expansions into arbitrary expressions, which could affect + * pullup_replace_vars' decisions about whether PlaceHolderVar wrappers + * are needed for tlist entries. (Likely it'd be better to do + * flatten_join_alias_vars on the whole query tree at some earlier stage, + * maybe even in the rewriter; but for now let's just fix this case here.) + */ + subquery->targetList = (List *) + flatten_join_alias_vars(subroot, (Node *) subquery->targetList); + + /* * Adjust level-0 varnos in subquery so that we can append its rangetable * to upper query's. We have to fix the subquery's append_rel_list as * well. diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c index 89450cbb426..360a358c4fd 100644 --- a/src/backend/optimizer/util/var.c +++ b/src/backend/optimizer/util/var.c @@ -750,16 +750,14 @@ flatten_join_alias_vars_mutator(Node *node, /* Ignore dropped columns */ if (newvar == NULL) continue; + newvar = copyObject(newvar); /* * If we are expanding an alias carried down from an upper * query, must adjust its varlevelsup fields. */ if (context->sublevels_up != 0) - { - newvar = copyObject(newvar); IncrementVarSublevelsUp(newvar, context->sublevels_up, 0); - } /* Recurse in case join input is itself a join */ /* (also takes care of setting inserted_sublink if needed) */ newvar = flatten_join_alias_vars_mutator(newvar, context); @@ -779,16 +777,14 @@ flatten_join_alias_vars_mutator(Node *node, Assert(var->varattno > 0); newvar = (Node *) list_nth(rte->joinaliasvars, var->varattno - 1); Assert(newvar != NULL); + newvar = copyObject(newvar); /* * If we are expanding an alias carried down from an upper query, must * adjust its varlevelsup fields. */ if (context->sublevels_up != 0) - { - newvar = copyObject(newvar); IncrementVarSublevelsUp(newvar, context->sublevels_up, 0); - } /* Recurse in case join input is itself a join */ newvar = flatten_join_alias_vars_mutator(newvar, context); diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 4463985cf28..be3df5e4735 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2674,6 +2674,58 @@ select f1, unique2, case when unique2 is null then f1 else 0 end (1 row) -- +-- check handling of join aliases when flattening multiple levels of subquery +-- +explain (verbose, costs off) +select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from + (values (0),(1)) foo1(join_key) +left join + (select join_key, bug_field from + (select ss1.join_key, ss1.bug_field from + (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 + ) foo2 + left join + (select unique2 as join_key from tenk1 i2) ss2 + using (join_key) + ) foo3 +using (join_key); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Nested Loop Left Join + Output: "*VALUES*".column1, i1.f1, (666) + Join Filter: ("*VALUES*".column1 = i1.f1) + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 + -> Materialize + Output: i1.f1, (666) + -> Nested Loop Left Join + Output: i1.f1, 666 + -> Seq Scan on public.int4_tbl i1 + Output: i1.f1 + -> Index Scan using tenk1_unique2 on public.tenk1 i2 + Output: i2.unique1, i2.unique2, i2.two, i2.four, i2.ten, i2.twenty, i2.hundred, i2.thousand, i2.twothousand, i2.fivethous, i2.tenthous, i2.odd, i2.even, i2.stringu1, i2.stringu2, i2.string4 + Index Cond: (i1.f1 = i2.unique2) +(14 rows) + +select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from + (values (0),(1)) foo1(join_key) +left join + (select join_key, bug_field from + (select ss1.join_key, ss1.bug_field from + (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 + ) foo2 + left join + (select unique2 as join_key from tenk1 i2) ss2 + using (join_key) + ) foo3 +using (join_key); + foo1_id | foo3_id | bug_field +---------+---------+----------- + 0 | 0 | 666 + 1 | | +(2 rows) + +-- -- test ability to push constants through outer join clauses -- explain (costs off) diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index b8e69e10185..9328c8c2503 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -691,6 +691,37 @@ select f1, unique2, case when unique2 is null then f1 else 0 end where (case when unique2 is null then f1 else 0 end) = 0; -- +-- check handling of join aliases when flattening multiple levels of subquery +-- + +explain (verbose, costs off) +select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from + (values (0),(1)) foo1(join_key) +left join + (select join_key, bug_field from + (select ss1.join_key, ss1.bug_field from + (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 + ) foo2 + left join + (select unique2 as join_key from tenk1 i2) ss2 + using (join_key) + ) foo3 +using (join_key); + +select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from + (values (0),(1)) foo1(join_key) +left join + (select join_key, bug_field from + (select ss1.join_key, ss1.bug_field from + (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 + ) foo2 + left join + (select unique2 as join_key from tenk1 i2) ss2 + using (join_key) + ) foo3 +using (join_key); + +-- -- test ability to push constants through outer join clauses -- |