diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/optimizer/prep/prepjointree.c | 12 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 52 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 31 |
3 files changed, 95 insertions, 0 deletions
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 485ac31bd37..1c6083bb3e1 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -867,6 +867,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/test/regress/expected/join.out b/src/test/regress/expected/join.out index b9559ea6075..c3598e2d252 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2930,6 +2930,58 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) (0 rows) -- +-- 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 Only Scan using tenk1_unique2 on public.tenk1 i2 + Output: i2.unique2 + Index Cond: (i2.unique2 = i1.f1) +(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 1edf9b3897b..6ae37f56381 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -804,6 +804,37 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) where a.unique2 = 5530 and coalesce(b.twothousand, a.twothousand) = 44; -- +-- 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 -- |