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

Commit612f953

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 parentb4f697f commit612f953

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
@@ -729,6 +729,18 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
729729
returnjtnode;
730730
}
731731

732+
/*
733+
* We must flatten any join alias Vars in the subquery's targetlist,
734+
* because pulling up the subquery's subqueries might have changed their
735+
* expansions into arbitrary expressions, which could affect
736+
* pullup_replace_vars' decisions about whether PlaceHolderVar wrappers
737+
* are needed for tlist entries. (Likely it'd be better to do
738+
* flatten_join_alias_vars on the whole query tree at some earlier stage,
739+
* maybe even in the rewriter; but for now let's just fix this case here.)
740+
*/
741+
subquery->targetList= (List*)
742+
flatten_join_alias_vars(subroot, (Node*)subquery->targetList);
743+
732744
/*
733745
* Adjust level-0 varnos in subquery so that we can append its rangetable
734746
* 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
@@ -750,16 +750,14 @@ flatten_join_alias_vars_mutator(Node *node,
750750
/* Ignore dropped columns */
751751
if (newvar==NULL)
752752
continue;
753+
newvar=copyObject(newvar);
753754

754755
/*
755756
* If we are expanding an alias carried down from an upper
756757
* query, must adjust its varlevelsup fields.
757758
*/
758759
if (context->sublevels_up!=0)
759-
{
760-
newvar=copyObject(newvar);
761760
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
762-
}
763761
/* Recurse in case join input is itself a join */
764762
/* (also takes care of setting inserted_sublink if needed) */
765763
newvar=flatten_join_alias_vars_mutator(newvar,context);
@@ -779,16 +777,14 @@ flatten_join_alias_vars_mutator(Node *node,
779777
Assert(var->varattno>0);
780778
newvar= (Node*)list_nth(rte->joinaliasvars,var->varattno-1);
781779
Assert(newvar!=NULL);
780+
newvar=copyObject(newvar);
782781

783782
/*
784783
* If we are expanding an alias carried down from an upper query, must
785784
* adjust its varlevelsup fields.
786785
*/
787786
if (context->sublevels_up!=0)
788-
{
789-
newvar=copyObject(newvar);
790787
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
791-
}
792788

793789
/* Recurse in case join input is itself a join */
794790
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
@@ -2673,6 +2673,58 @@ select f1, unique2, case when unique2 is null then f1 else 0 end
26732673
0 | 0 | 0
26742674
(1 row)
26752675

2676+
--
2677+
-- check handling of join aliases when flattening multiple levels of subquery
2678+
--
2679+
explain (verbose, costs off)
2680+
select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
2681+
(values (0),(1)) foo1(join_key)
2682+
left join
2683+
(select join_key, bug_field from
2684+
(select ss1.join_key, ss1.bug_field from
2685+
(select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
2686+
) foo2
2687+
left join
2688+
(select unique2 as join_key from tenk1 i2) ss2
2689+
using (join_key)
2690+
) foo3
2691+
using (join_key);
2692+
QUERY PLAN
2693+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2694+
Nested Loop Left Join
2695+
Output: "*VALUES*".column1, i1.f1, (666)
2696+
Join Filter: ("*VALUES*".column1 = i1.f1)
2697+
-> Values Scan on "*VALUES*"
2698+
Output: "*VALUES*".column1
2699+
-> Materialize
2700+
Output: i1.f1, (666)
2701+
-> Nested Loop Left Join
2702+
Output: i1.f1, 666
2703+
-> Seq Scan on public.int4_tbl i1
2704+
Output: i1.f1
2705+
-> Index Scan using tenk1_unique2 on public.tenk1 i2
2706+
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
2707+
Index Cond: (i1.f1 = i2.unique2)
2708+
(14 rows)
2709+
2710+
select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
2711+
(values (0),(1)) foo1(join_key)
2712+
left join
2713+
(select join_key, bug_field from
2714+
(select ss1.join_key, ss1.bug_field from
2715+
(select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1
2716+
) foo2
2717+
left join
2718+
(select unique2 as join_key from tenk1 i2) ss2
2719+
using (join_key)
2720+
) foo3
2721+
using (join_key);
2722+
foo1_id | foo3_id | bug_field
2723+
---------+---------+-----------
2724+
0 | 0 | 666
2725+
1 | |
2726+
(2 rows)
2727+
26762728
--
26772729
-- test ability to push constants through outer join clauses
26782730
--

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

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

693+
--
694+
-- check handling of join aliases when flattening multiple levels of subquery
695+
--
696+
697+
explain (verbose, costs off)
698+
selectfoo1.join_keyas foo1_id,foo3.join_keyAS foo3_id, bug_fieldfrom
699+
(values (0),(1)) foo1(join_key)
700+
left join
701+
(select join_key, bug_fieldfrom
702+
(selectss1.join_key,ss1.bug_fieldfrom
703+
(select f1as join_key,666as bug_fieldfrom int4_tbl i1) ss1
704+
) foo2
705+
left join
706+
(select unique2as join_keyfrom tenk1 i2) ss2
707+
using (join_key)
708+
) foo3
709+
using (join_key);
710+
711+
selectfoo1.join_keyas foo1_id,foo3.join_keyAS foo3_id, bug_fieldfrom
712+
(values (0),(1)) foo1(join_key)
713+
left join
714+
(select join_key, bug_fieldfrom
715+
(selectss1.join_key,ss1.bug_fieldfrom
716+
(select f1as join_key,666as bug_fieldfrom int4_tbl i1) ss1
717+
) foo2
718+
left join
719+
(select unique2as join_keyfrom tenk1 i2) ss2
720+
using (join_key)
721+
) foo3
722+
using (join_key);
723+
693724
--
694725
-- test ability to push constants through outer join clauses
695726
--

0 commit comments

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