8000 Fix "cannot handle unplanned sub-select" error that can occur when a · commandprompt/postgres@8bed5e2 · GitHub
[go: up one dir, main page]
Skip to content

Commit 8bed5e2

Browse files
tglsfdctglsfdc
committed
Fix "cannot handle unplanned sub-select" error that can occur when a
sub-select contains a join alias reference that expands into an expression containing another sub-select. Per yesterday's report from Merlin Moncure and subsequent off-list investigation. Back-patch to 7.4. Older versions didn't attempt to flatten sub-selects in ways that would trigger this problem.
1 parent c778f89 commit 8bed5e2

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/08 00:14:33 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -51,6 +51,8 @@ typedef struct
5151
{
5252
Query *root;
5353
int sublevels_up;
54+
bool possible_sublink; /* could aliases include a SubLink? */
55+
bool inserted_sublink; /* have we inserted a SubLink? */
5456
} flatten_join_alias_vars_context;
5557

5658
static bool pull_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
return flatten_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-
return flatten_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+
return newvar;
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+
bool save_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
DROP TABLE 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(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
203+
from int8_tbl) sq0
204+
join
205+
int4_tbl i4 on dummy = i4.f1;

0 commit comments

Comments
 (0)
0