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

Commit92a7521

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 parent6730199 commit92a7521

File tree

4 files changed

+97
-6
lines changed

4 files changed

+97
-6
lines changed

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -731,6 +731,18 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
731731
returnjtnode;
732732
}
733733

734+
/*
735+
* We must flatten any join alias Vars in the subquery's targetlist,
736+
* because pulling up the subquery's subqueries might have changed their
737+
* expansions into arbitrary expressions, which could affect
738+
* pullup_replace_vars' decisions about whether PlaceHolderVar wrappers
739+
* are needed for tlist entries. (Likely it'd be better to do
740+
* flatten_join_alias_vars on the whole query tree at some earlier stage,
741+
* maybe even in the rewriter; but for now let's just fix this case here.)
742+
*/
743+
subquery->targetList= (List*)
744+
flatten_join_alias_vars(subroot, (Node*)subquery->targetList);
745+
734746
/*
735747
* Adjust level-0 varnos in subquery so that we can append its rangetable
736748
* to upper query's. We have to fix the subquery's append_rel_list as

‎src/backend/optimizer/util/var.c

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -779,16 +779,14 @@ flatten_join_alias_vars_mutator(Node *node,
779779
/* Ignore dropped columns */
780780
if (newvar==NULL)
781781
continue;
782+
newvar=copyObject(newvar);
782783

783784
/*
784785
* If we are expanding an alias carried down from an upper
785786
* query, must adjust its varlevelsup fields.
786787
*/
787788
if (context->sublevels_up!=0)
788-
{
789-
newvar=copyObject(newvar);
790789
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
791-
}
792790
/* Recurse in case join input is itself a join */
793791
/* (also takes care of setting inserted_sublink if needed) */
794792
newvar=flatten_join_alias_vars_mutator(newvar,context);
@@ -808,16 +806,14 @@ flatten_join_alias_vars_mutator(Node *node,
808806
Assert(var->varattno>0);
809807
newvar= (Node*)list_nth(rte->joinaliasvars,var->varattno-1);
810808
Assert(newvar!=NULL);
809+
newvar=copyObject(newvar);
811810

812811
/*
813812
* If we are expanding an alias carried down from an upper query, must
814813
* adjust its varlevelsup fields.
815814
*/
816815
if (context->sublevels_up!=0)
817-
{
818-
newvar=copyObject(newvar);
819816
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
820-
}
821817

822818
/* Recurse in case join input is itself a join */
823819
newvar=flatten_join_alias_vars_mutator(newvar,context);

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

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2710,6 +2710,58 @@ select f1, unique2, case when unique2 is null then f1 else 0 end
27102710
0 | 0 | 0
27112711
(1 row)
27122712

2713+
--
2714+
-- check handling of join aliases when flattening multiple levels of subquery
2715+
--
2716+
explain (verbose, costs off)
2717+
select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
2718+
(values (0),(1)) foo1(join_key)
2719+
left join
2720+
(select join_key, bug_field from
2721+
(select ss1.join_key, ss1.bug_field from
2722+
(select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
2723+
) foo2
2724+
left join
2725+
(select unique2 as join_key from tenk1 i2) ss2
2726+
using (join_key)
2727+
) foo3
2728+
using (join_key);
2729+
QUERY PLAN
2730+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2731+
Nested Loop Left Join
2732+
Output: "*VALUES*".column1, i1.f1, (666)
2733+
Join Filter: ("*VALUES*".column1 = i1.f1)
2734+
-> Values Scan on "*VALUES*"
2735+
Output: "*VALUES*".column1
2736+
-> Materialize
2737+
Output: i1.f1, (666)
2738+
-> Nested Loop Left Join
2739+
Output: i1.f1, 666
2740+
-> Seq Scan on public.int4_tbl i1
2741+
Output: i1.f1
2742+
-> Index Scan using tenk1_unique2 on public.tenk1 i2
2743+
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
2744+
Index Cond: (i1.f1 = i2.unique2)
2745+
(14 rows)
2746+
2747+
select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
2748+
(values (0),(1)) foo1(join_key)
2749+
left join
2750+
(select join_key, bug_field from
2751+
(select ss1.join_key, ss1.bug_field from
2752+
(select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
2753+
) foo2
2754+
left join
2755+
(select unique2 as join_key from tenk1 i2) ss2
2756+
using (join_key)
2757+
) foo3
2758+
using (join_key);
2759+
foo1_id | foo3_id | bug_field
2760+
---------+---------+-----------
2761+
0 | 0 | 666
2762+
1 | |
2763+
(2 rows)
2764+
27132765
--
27142766
-- test ability to push constants through outer join clauses
27152767
--

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

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -711,6 +711,37 @@ select f1, unique2, case when unique2 is null then f1 else 0 end
711711
from int4_tbl aleft join tenk1 bon f1= unique2
712712
where (case when unique2 isnull then f1 else0 end)=0;
713713

714+
--
715+
-- check handling of join aliases when flattening multiple levels of subquery
716+
--
717+
718+
explain (verbose, costs off)
719+
selectfoo1.join_keyas foo1_id,foo3.join_keyAS foo3_id, bug_fieldfrom
720+
(values (0),(1)) foo1(join_key)
721+
left join
722+
(select join_key, bug_fieldfrom
723+
(selectss1.join_key,ss1.bug_fieldfrom
724+
(select f1as join_key,666as bug_fieldfrom int4_tbl i1) ss1
725+
) foo2
726+
left join
727+
(select unique2as join_keyfrom tenk1 i2) ss2
728+
using (join_key)
729+
) foo3
730+
using (join_key);
731+
732+
selectfoo1.join_keyas foo1_id,foo3.join_keyAS foo3_id, bug_fieldfrom
733+
(values (0),(1)) foo1(join_key)
734+
left join
735+
(select join_key, bug_fieldfrom
736+
(selectss1.join_key,ss1.bug_fieldfrom
737+
(select f1as join_key,666as bug_fieldfrom int4_tbl i1) ss1
738+
) foo2
739+
left join
740+
(select unique2as join_keyfrom tenk1 i2) ss2
741+
using (join_key)
742+
) foo3
743+
using (join_key);
744+
714745
--
715746
-- test ability to push constants through outer join clauses
716747
--

0 commit comments

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