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

Commit1cf269c

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 parent97f29c8 commit1cf269c

File tree

3 files changed

+54
-2
lines changed

3 files changed

+54
-2
lines changed

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

Lines changed: 28 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/util/var.c,v 1.54 2003/08/0821:41:55 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/util/var.c,v 1.54.4.1 2010/07/0800:14:41 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -51,6 +51,8 @@ typedef struct
5151
{
5252
Query*root;
5353
intsublevels_up;
54+
boolpossible_sublink;/* could aliases include a SubLink? */
55+
boolinserted_sublink;/* have we inserted a SubLink? */
5456
}flatten_join_alias_vars_context;
5557

5658
staticboolpull_varnos_walker(Node*node,
@@ -488,6 +490,14 @@ pull_var_clause_walker(Node *node, pull_var_clause_context *context)
488490
* relation variables instead. This allows quals involving such vars to be
489491
* pushed down.
490492
*
493+
* If a JOIN contains sub-selects that have been flattened, its join alias
494+
* entries might now be arbitrary expressions, not just Vars. This affects
495+
* this function in one important way: we might find ourselves inserting
496+
* SubLink expressions into subqueries, and we must make sure that their
497+
* Query.hasSubLinks fields get set to TRUE if so. If there are any
498+
* SubLinks in the join alias lists, the outer Query should already have
499+
* hasSubLinks = TRUE, so this is only relevant to un-flattened subqueries.
500+
*
491501
* NOTE: this is used on not-yet-planned expressions. We do not expect it
492502
* to be applied directly to a Query node.
493503
*/
@@ -498,6 +508,10 @@ flatten_join_alias_vars(Query *root, Node *node)
498508

499509
context.root=root;
500510
context.sublevels_up=0;
511+
/* flag whether join aliases could possibly contain SubLinks */
512+
context.possible_sublink=root->hasSubLinks;
513+
/* if hasSubLinks is already true, no need to work hard */
514+
context.inserted_sublink=root->hasSubLinks;
501515

502516
returnflatten_join_alias_vars_mutator(node,&context);
503517
}
@@ -533,8 +547,15 @@ flatten_join_alias_vars_mutator(Node *node,
533547
newvar=copyObject(newvar);
534548
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
535549
}
550+
536551
/* Recurse in case join input is itself a join */
537-
returnflatten_join_alias_vars_mutator(newvar,context);
552+
newvar=flatten_join_alias_vars_mutator(newvar,context);
553+
554+
/* Detect if we are adding a sublink to query */
555+
if (context->possible_sublink&& !context->inserted_sublink)
556+
context->inserted_sublink=checkExprHasSubLink(newvar);
557+
558+
returnnewvar;
538559
}
539560
if (IsA(node,InClauseInfo))
540561
{
@@ -559,12 +580,17 @@ flatten_join_alias_vars_mutator(Node *node,
559580
{
560581
/* Recurse into RTE subquery or not-yet-planned sublink subquery */
561582
Query*newnode;
583+
boolsave_inserted_sublink;
562584

563585
context->sublevels_up++;
586+
save_inserted_sublink=context->inserted_sublink;
587+
context->inserted_sublink= ((Query*)node)->hasSubLinks;
564588
newnode=query_tree_mutator((Query*)node,
565589
flatten_join_alias_vars_mutator,
566590
(void*)context,
567591
QTW_IGNORE_JOINALIASES);
592+
newnode->hasSubLinks |=context->inserted_sublink;
593+
context->inserted_sublink=save_inserted_sublink;
568594
context->sublevels_up--;
569595
return (Node*)newnode;
570596
}

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,3 +201,17 @@ select count(distinct ss.ten) from
201201
10
202202
(1 row)
203203

204+
--
205+
-- Test case for sublinks pushed down into subselects via join alias expansion
206+
--
207+
select
208+
(select sq1) as qq1
209+
from
210+
(select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
211+
from int8_tbl) sq0
212+
join
213+
int4_tbl i4 on dummy = i4.f1;
214+
qq1
215+
-----
216+
(0 rows)
217+

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,3 +93,15 @@ select count(*) from
9393
selectcount(distinctss.ten)from
9494
(select tenfrom tenk1 a
9595
where unique1IN (select distinct hundredfrom tenk1 b)) ss;
96+
97+
--
98+
-- Test case for sublinks pushed down into subselects via join alias expansion
99+
--
100+
101+
select
102+
(select sq1)as qq1
103+
from
104+
(select exists(select1from int4_tblwhere f1= q2)as sq1,42as dummy
105+
from int8_tbl) sq0
106+
join
107+
int4_tbl i4on dummy=i4.f1;

0 commit comments

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