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

Commitf19e92e

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 parentf29baf9 commitf19e92e

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
@@ -866,6 +866,18 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
866866
returnjtnode;
867867
}
868868

869+
/*
870+
* We must flatten any join alias Vars in the subquery's targetlist,
871+
* because pulling up the subquery's subqueries might have changed their
872+
* expansions into arbitrary expressions, which could affect
873+
* pullup_replace_vars' decisions about whether PlaceHolderVar wrappers
874+
* are needed for tlist entries. (Likely it'd be better to do
875+
* flatten_join_alias_vars on the whole query tree at some earlier stage,
876+
* maybe even in the rewriter; but for now let's just fix this case here.)
877+
*/
878+
subquery->targetList= (List*)
879+
flatten_join_alias_vars(subroot, (Node*)subquery->targetList);
880+
869881
/*
870882
* Adjust level-0 varnos in subquery so that we can append its rangetable
871883
* 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
@@ -2929,6 +2929,58 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
29292929
---------+---------+---------+----------
29302930
(0 rows)
29312931

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

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

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

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

0 commit comments

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