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

Commitd4d32ee

Browse files
committed
Fix "cannot handle unplanned sub-select" error that can occur when a
sub-select contains a join alias reference that expands into an expressioncontaining another sub-select. Per yesterday's report from Merlin Moncureand subsequent off-list investigation.Back-patch to 7.4. Older versions didn't attempt to flatten sub-selects inways that would trigger this problem.
1 parent5b1b3ef commitd4d32ee

File tree

3 files changed

+55
-2
lines changed

3 files changed

+55
-2
lines changed

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

Lines changed: 29 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/optimizer/util/var.c,v 1.87 2010/01/02 16:57:48 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/optimizer/util/var.c,v 1.88 2010/07/08 00:14:03 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -63,6 +63,8 @@ typedef struct
6363
{
6464
PlannerInfo*root;
6565
intsublevels_up;
66+
boolpossible_sublink;/* could aliases include a SubLink? */
67+
boolinserted_sublink;/* have we inserted a SubLink? */
6668
}flatten_join_alias_vars_context;
6769

6870
staticboolpull_varnos_walker(Node*node,
@@ -688,6 +690,14 @@ pull_var_clause_walker(Node *node, pull_var_clause_context *context)
688690
* This also adjusts relid sets found in some expression node types to
689691
* substitute the contained base rels for any join relid.
690692
*
693+
* If a JOIN contains sub-selects that have been flattened, its join alias
694+
* entries might now be arbitrary expressions, not just Vars. This affects
695+
* this function in one important way: we might find ourselves inserting
696+
* SubLink expressions into subqueries, and we must make sure that their
697+
* Query.hasSubLinks fields get set to TRUE if so. If there are any
698+
* SubLinks in the join alias lists, the outer Query should already have
699+
* hasSubLinks = TRUE, so this is only relevant to un-flattened subqueries.
700+
*
691701
* NOTE: this is used on not-yet-planned expressions. We do not expect it
692702
* to be applied directly to a Query node.
693703
*/
@@ -698,6 +708,10 @@ flatten_join_alias_vars(PlannerInfo *root, Node *node)
698708

699709
context.root=root;
700710
context.sublevels_up=0;
711+
/* flag whether join aliases could possibly contain SubLinks */
712+
context.possible_sublink=root->parse->hasSubLinks;
713+
/* if hasSubLinks is already true, no need to work hard */
714+
context.inserted_sublink=root->parse->hasSubLinks;
701715

702716
returnflatten_join_alias_vars_mutator(node,&context);
703717
}
@@ -747,6 +761,7 @@ flatten_join_alias_vars_mutator(Node *node,
747761
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
748762
}
749763
/* Recurse in case join input is itself a join */
764+
/* (also takes care of setting inserted_sublink if needed) */
750765
newvar=flatten_join_alias_vars_mutator(newvar,context);
751766
fields=lappend(fields,newvar);
752767
}
@@ -773,8 +788,15 @@ flatten_join_alias_vars_mutator(Node *node,
773788
newvar=copyObject(newvar);
774789
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
775790
}
791+
776792
/* Recurse in case join input is itself a join */
777-
returnflatten_join_alias_vars_mutator(newvar,context);
793+
newvar=flatten_join_alias_vars_mutator(newvar,context);
794+
795+
/* Detect if we are adding a sublink to query */
796+
if (context->possible_sublink&& !context->inserted_sublink)
797+
context->inserted_sublink=checkExprHasSubLink(newvar);
798+
799+
returnnewvar;
778800
}
779801
if (IsA(node,PlaceHolderVar))
780802
{
@@ -797,12 +819,17 @@ flatten_join_alias_vars_mutator(Node *node,
797819
{
798820
/* Recurse into RTE subquery or not-yet-planned sublink subquery */
799821
Query*newnode;
822+
boolsave_inserted_sublink;
800823

801824
context->sublevels_up++;
825+
save_inserted_sublink=context->inserted_sublink;
826+
context->inserted_sublink= ((Query*)node)->hasSubLinks;
802827
newnode=query_tree_mutator((Query*)node,
803828
flatten_join_alias_vars_mutator,
804829
(void*)context,
805830
QTW_IGNORE_JOINALIASES);
831+
newnode->hasSubLinks |=context->inserted_sublink;
832+
context->inserted_sublink=save_inserted_sublink;
806833
context->sublevels_up--;
807834
return (Node*)newnode;
808835
}

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -507,3 +507,17 @@ select (select (a.*)::text) from view_a a;
507507
(42)
508508
(1 row)
509509

510+
--
511+
-- Test case for sublinks pushed down into subselects via join alias expansion
512+
--
513+
select
514+
(select sq1) as qq1
515+
from
516+
(select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
517+
from int8_tbl) sq0
518+
join
519+
int4_tbl i4 on dummy = i4.f1;
520+
qq1
521+
-----
522+
(0 rows)
523+

‎src/test/regress/sql/subselect.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -323,3 +323,15 @@ select view_a from view_a;
323323
select (select view_a)from view_a;
324324
select (select (select view_a))from view_a;
325325
select (select (a.*)::text)from view_a a;
326+
327+
--
328+
-- Test case for sublinks pushed down into subselects via join alias expansion
329+
--
330+
331+
select
332+
(select sq1)as qq1
333+
from
334+
(select exists(select1from int4_tblwhere f1= q2)as sq1,42as dummy
335+
from int8_tbl) sq0
336+
join
337+
int4_tbl i4on dummy=i4.f1;

0 commit comments

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