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

Commit8bed5e2

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 parentc778f89 commit8bed5e2

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
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/util/var.c,v 1.63 2004/12/31 22:00:23 pgsql Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/util/var.c,v 1.63.4.1 2010/07/0800:14:33 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,
@@ -478,6 +480,14 @@ pull_var_clause_walker(Node *node, pull_var_clause_context *context)
478480
* is necessary since we will not scan the JOIN as a base relation, which
479481
* is the only way that the executor can directly handle whole-row Vars.
480482
*
483+
* If a JOIN contains sub-selects that have been flattened, its join alias
484+
* entries might now be arbitrary expressions, not just Vars. This affects
485+
* this function in one important way: we might find ourselves inserting
486+
* SubLink expressions into subqueries, and we must make sure that their
487+
* Query.hasSubLinks fields get set to TRUE if so. If there are any
488+
* SubLinks in the join alias lists, the outer Query should already have
489+
* hasSubLinks = TRUE, so this is only relevant to un-flattened subqueries.
490+
*
481491
* NOTE: this is used on not-yet-planned expressions. We do not expect it
482492
* to be applied directly to a Query node.
483493
*/
@@ -488,6 +498,10 @@ flatten_join_alias_vars(Query *root, Node *node)
488498

489499
context.root=root;
490500
context.sublevels_up=0;
501+
/* flag whether join aliases could possibly contain SubLinks */
502+
context.possible_sublink=root->hasSubLinks;
503+
/* if hasSubLinks is already true, no need to work hard */
504+
context.inserted_sublink=root->hasSubLinks;
491505

492506
returnflatten_join_alias_vars_mutator(node,&context);
493507
}
@@ -539,6 +553,7 @@ flatten_join_alias_vars_mutator(Node *node,
539553
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
540554
}
541555
/* Recurse in case join input is itself a join */
556+
/* (also takes care of setting inserted_sublink if needed) */
542557
newvar=flatten_join_alias_vars_mutator(newvar,context);
543558
fields=lappend(fields,newvar);
544559
}
@@ -563,8 +578,15 @@ flatten_join_alias_vars_mutator(Node *node,
563578
newvar=copyObject(newvar);
564579
IncrementVarSublevelsUp(newvar,context->sublevels_up,0);
565580
}
581+
566582
/* Recurse in case join input is itself a join */
567-
returnflatten_join_alias_vars_mutator(newvar,context);
583+
newvar=flatten_join_alias_vars_mutator(newvar,context);
584+
585+
/* Detect if we are adding a sublink to query */
586+
if (context->possible_sublink&& !context->inserted_sublink)
587+
context->inserted_sublink=checkExprHasSubLink(newvar);
588+
589+
returnnewvar;
568590
}
569591
if (IsA(node,InClauseInfo))
570592
{
@@ -589,12 +611,17 @@ flatten_join_alias_vars_mutator(Node *node,
589611
{
590612
/* Recurse into RTE subquery or not-yet-planned sublink subquery */
591613
Query*newnode;
614+
boolsave_inserted_sublink;
592615

593616
context->sublevels_up++;
617+
save_inserted_sublink=context->inserted_sublink;
618+
context->inserted_sublink= ((Query*)node)->hasSubLinks;
594619
newnode=query_tree_mutator((Query*)node,
595620
flatten_join_alias_vars_mutator,
596621
(void*)context,
597622
QTW_IGNORE_JOINALIASES);
623+
newnode->hasSubLinks |=context->inserted_sublink;
624+
context->inserted_sublink=save_inserted_sublink;
598625
context->sublevels_up--;
599626
return (Node*)newnode;
600627
}

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -334,3 +334,17 @@ SELECT * FROM orders_view;
334334
DROP TABLE orderstest cascade;
335335
NOTICE: drop cascades to rule _RETURN on view orders_view
336336
NOTICE: drop cascades to view orders_view
337+
--
338+
-- Test case for sublinks pushed down into subselects via join alias expansion
339+
--
340+
select
341+
(select sq1) as qq1
342+
from
343+
(select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
344+
from int8_tbl) sq0
345+
join
346+
int4_tbl i4 on dummy = i4.f1;
347+
qq1
348+
-----
349+
(0 rows)
350+

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -191,3 +191,15 @@ FROM orderstest ord;
191191
SELECT*FROM orders_view;
192192

193193
DROPTABLE orderstest cascade;
194+
195+
--
196+
-- Test case for sublinks pushed down into subselects via join alias expansion
197+
--
198+
199+
select
200+
(select sq1)as qq1
201+
from
202+
(select exists(select1from int4_tblwhere f1= q2)as sq1,42as dummy
203+
from int8_tbl) sq0
204+
join
205+
int4_tbl i4on dummy=i4.f1;

0 commit comments

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