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

Commitc0aa210

Browse files
committed
Flatten join alias Vars before pulling up targetlist items from a subquery.
pullup_replace_vars()'s decisions about whether a pulled-up replacementexpression needs to be wrapped in a PlaceHolderVar depend on the assumptionthat what looks like a Var behaves like a Var. However, if the Var is ajoin alias reference, later flattening of join aliases might replace theVar with something that's not a Var at all, and should have been wrapped.To fix, do a forcible pass of flatten_join_alias_vars() on the subquerytargetlist before we start to copy items out of it. We'll re-run thatprocessing on the pulled-up expressions later, but that's harmless.Per report from Ken Tanzer; the added regression test case is based on hisexample. This bug has been there since the PlaceHolderVar mechanism wasinvented, but has escaped detection because the circumstances that triggerit are fairly narrow. You need a flattenable query underneath an outerjoin, which contains another flattenable query inside a join of its own,with a dangerous expression (a constant or something else non-strict)in that one's targetlist.Having seen this, I'm wondering if it wouldn't be prudent to do allalias-variable flattening earlier, perhaps even in the rewriter.But that would probably not be a back-patchable change.
1 parent2583fa8 commitc0aa210

File tree

3 files changed

+95
-0
lines changed

3 files changed

+95
-0
lines changed

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -818,6 +818,18 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
818818
returnjtnode;
819819
}
820820

821+
/*
822+
* We must flatten any join alias Vars in the subquery's targetlist,
823+
* because pulling up the subquery's subqueries might have changed their
824+
* expansions into arbitrary expressions, which could affect
825+
* pullup_replace_vars' decisions about whether PlaceHolderVar wrappers
826+
* are needed for tlist entries. (Likely it'd be better to do
827+
* flatten_join_alias_vars on the whole query tree at some earlier stage,
828+
* maybe even in the rewriter; but for now let's just fix this case here.)
829+
*/
830+
subquery->targetList= (List*)
831+
flatten_join_alias_vars(subroot, (Node*)subquery->targetList);
832+
821833
/*
822834
* Adjust level-0 varnos in subquery so that we can append its rangetable
823835
* to upper query's. We have to fix the subquery's append_rel_list as

‎src/test/regress/expected/join.out

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2934,6 +2934,58 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
29342934
---------+---------+---------+----------
29352935
(0 rows)
29362936

2937+
--
2938+
-- check handling of join aliases when flattening multiple levels of subquery
2939+
--
2940+
explain (verbose, costs off)
2941+
select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
2942+
(values (0),(1)) foo1(join_key)
2943+
left join
2944+
(select join_key, bug_field from
2945+
(select ss1.join_key, ss1.bug_field from
2946+
(select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
2947+
) foo2
2948+
left join
2949+
(select unique2 as join_key from tenk1 i2) ss2
2950+
using (join_key)
2951+
) foo3
2952+
using (join_key);
2953+
QUERY PLAN
2954+
--------------------------------------------------------------------------
2955+
Nested Loop Left Join
2956+
Output: "*VALUES*".column1, i1.f1, (666)
2957+
Join Filter: ("*VALUES*".column1 = i1.f1)
2958+
-> Values Scan on "*VALUES*"
2959+
Output: "*VALUES*".column1
2960+
-> Materialize
2961+
Output: i1.f1, (666)
2962+
-> Nested Loop Left Join
2963+
Output: i1.f1, 666
2964+
-> Seq Scan on public.int4_tbl i1
2965+
Output: i1.f1
2966+
-> Index Only Scan using tenk1_unique2 on public.tenk1 i2
2967+
Output: i2.unique2
2968+
Index Cond: (i2.unique2 = i1.f1)
2969+
(14 rows)
2970+
2971+
select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
2972+
(values (0),(1)) foo1(join_key)
2973+
left join
2974+
(select join_key, bug_field from
2975+
(select ss1.join_key, ss1.bug_field from
2976+
(select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
2977+
) foo2
2978+
left join
2979+
(select unique2 as join_key from tenk1 i2) ss2
2980+
using (join_key)
2981+
) foo3
2982+
using (join_key);
2983+
foo1_id | foo3_id | bug_field
2984+
---------+---------+-----------
2985+
0 | 0 | 666
2986+
1 | |
2987+
(2 rows)
2988+
29372989
--
29382990
-- test ability to push constants through outer join clauses
29392991
--

‎src/test/regress/sql/join.sql

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -801,6 +801,37 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
801801
from tenk1 aleft join tenk1 bonb.thousand=a.unique1left join tenk1 conc.unique2= coalesce(b.twothousand,a.twothousand)
802802
wherea.unique2=5530and coalesce(b.twothousand,a.twothousand)=44;
803803

804+
--
805+
-- check handling of join aliases when flattening multiple levels of subquery
806+
--
807+
808+
explain (verbose, costs off)
809+
selectfoo1.join_keyas foo1_id,foo3.join_keyAS foo3_id, bug_fieldfrom
810+
(values (0),(1)) foo1(join_key)
811+
left join
812+
(select join_key, bug_fieldfrom
813+
(selectss1.join_key,ss1.bug_fieldfrom
814+
(select f1as join_key,666as bug_fieldfrom int4_tbl i1) ss1
815+
) foo2
816+
left join
817+
(select unique2as join_keyfrom tenk1 i2) ss2
818+
using (join_key)
819+
) foo3
820+
using (join_key);
821+
822+
selectfoo1.join_keyas foo1_id,foo3.join_keyAS foo3_id, bug_fieldfrom
823+
(values (0),(1)) foo1(join_key)
824+
left join
825+
(select join_key, bug_fieldfrom
826+
(selectss1.join_key,ss1.bug_fieldfrom
827+
(select f1as join_key,666as bug_fieldfrom int4_tbl i1) ss1
828+
) foo2
829+
left join
830+
(select unique2as join_keyfrom tenk1 i2) ss2
831+
using (join_key)
832+
) foo3
833+
using (join_key);
834+
804835
--
805836
-- test ability to push constants through outer join clauses
806837
--

0 commit comments

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