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

Commit 1cf269c

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 97f29c8 commit 1cf269c

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/08 21:41:55 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/util/var.c,v 1.54.4.1 2010/07/08 00:14:41 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,
@@ -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
return flatten_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-
return flatten_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+
return newvar;
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+
bool save_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
select count(distinct ss.ten) from
9494
(select ten from tenk1 a
9595
where unique1 IN (select distinct hundred from 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(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
105+
from int8_tbl) sq0
106+
join
107+
int4_tbl i4 on dummy = i4.f1;

0 commit comments

Comments
 (0)
0