From accabacce905371f94d9d9a00e308931b56b7d67 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 8 Jul 2010 00:14:28 +0000 Subject: [PATCH] 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. --- src/backend/optimizer/util/var.c | 31 +++++++++++++++++++++++-- src/test/regress/expected/subselect.out | 14 +++++++++++ src/test/regress/sql/subselect.sql | 12 ++++++++++ 3 files changed, 55 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c index dc1004cbd0e..a76f1677e49 100644 --- a/src/backend/optimizer/util/var.c +++ b/src/backend/optimizer/util/var.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/var.c,v 1.66 2005/10/15 02:49:21 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/var.c,v 1.66.2.1 2010/07/08 00:14:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -51,6 +51,8 @@ typedef struct { PlannerInfo *root; int sublevels_up; + bool possible_sublink; /* could aliases include a SubLink? */ + bool inserted_sublink; /* have we inserted a SubLink? */ } flatten_join_alias_vars_context; static bool pull_varnos_walker(Node *node, @@ -476,6 +478,14 @@ pull_var_clause_walker(Node *node, pull_var_clause_context *context) * is necessary since we will not scan the JOIN as a base relation, which * is the only way that the executor can directly handle whole-row Vars. * + * If a JOIN contains sub-selects that have been flattened, its join alias + * entries might now be arbitrary expressions, not just Vars. This affects + * this function in one important way: we might find ourselves inserting + * SubLink expressions into subqueries, and we must make sure that their + * Query.hasSubLinks fields get set to TRUE if so. If there are any + * SubLinks in the join alias lists, the outer Query should already have + * hasSubLinks = TRUE, so this is only relevant to un-flattened subqueries. + * * NOTE: this is used on not-yet-planned expressions. We do not expect it * to be applied directly to a Query node. */ @@ -486,6 +496,10 @@ flatten_join_alias_vars(PlannerInfo *root, Node *node) context.root = root; context.sublevels_up = 0; + /* flag whether join aliases could possibly contain SubLinks */ + context.possible_sublink = root->parse->hasSubLinks; + /* if hasSubLinks is already true, no need to work hard */ + context.inserted_sublink = root->parse->hasSubLinks; return flatten_join_alias_vars_mutator(node, &context); } @@ -535,6 +549,7 @@ flatten_join_alias_vars_mutator(Node *node, IncrementVarSublevelsUp(newvar, context->sublevels_up, 0); } /* Recurse in case join input is itself a join */ + /* (also takes care of setting inserted_sublink if needed) */ newvar = flatten_join_alias_vars_mutator(newvar, context); fields = lappend(fields, newvar); } @@ -559,8 +574,15 @@ flatten_join_alias_vars_mutator(Node *node, newvar = copyObject(newvar); IncrementVarSublevelsUp(newvar, context->sublevels_up, 0); } + /* Recurse in case join input is itself a join */ - return flatten_join_alias_vars_mutator(newvar, context); + newvar = flatten_join_alias_vars_mutator(newvar, context); + + /* Detect if we are adding a sublink to query */ + if (context->possible_sublink && !context->inserted_sublink) + context->inserted_sublink = checkExprHasSubLink(newvar); + + return newvar; } if (IsA(node, InClauseInfo)) { @@ -585,12 +607,17 @@ flatten_join_alias_vars_mutator(Node *node, { /* Recurse into RTE subquery or not-yet-planned sublink subquery */ Query *newnode; + bool save_inserted_sublink; context->sublevels_up++; + save_inserted_sublink = context->inserted_sublink; + context->inserted_sublink = ((Query *) node)->hasSubLinks; newnode = query_tree_mutator((Query *) node, flatten_join_alias_vars_mutator, (void *) context, QTW_IGNORE_JOINALIASES); + newnode->hasSubLinks |= context->inserted_sublink; + context->inserted_sublink = save_inserted_sublink; context->sublevels_up--; return (Node *) newnode; } diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index bfddea582f0..339b46f2972 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -435,3 +435,17 @@ from tc; 3 (2 rows) +-- +-- Test case for sublinks pushed down into subselects via join alias expansion +-- +select + (select sq1) as qq1 +from + (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy + from int8_tbl) sq0 + join + int4_tbl i4 on dummy = i4.f1; + qq1 +----- +(0 rows) + diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 2fc947d8542..46b46b4c45e 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -277,3 +277,15 @@ select ( select min(tb.id) from tb where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id from tc; + +-- +-- Test case for sublinks pushed down into subselects via join alias expansion +-- + +select + (select sq1) as qq1 +from + (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy + from int8_tbl) sq0 + join + int4_tbl i4 on dummy = i4.f1; -- 2.39.5