*
*
* IDENTIFICATION
- * $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.108 2001/06/05 05:26:04 tgl Exp $
+ * $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.117 2002/05/12 23:43:03 tgl Exp $
*
*-------------------------------------------------------------------------
*/
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
+#ifdef OPTIMIZER_DEBUG
+#include "nodes/print.h"
+#endif
#include "optimizer/clauses.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
#define EXPRKIND_HAVING 2
-static Node *pull_up_subqueries(Query *parse, Node *jtnode);
+static Node *pull_up_subqueries(Query *parse, Node *jtnode,
+ bool below_outer_join);
static bool is_simple_subquery(Query *subquery);
+static bool has_nullable_targetlist(Query *subquery);
static void resolvenew_in_jointree(Node *jtnode, int varno, List *subtlist);
static Node *preprocess_jointree(Query *parse, Node *jtnode);
static Node *preprocess_expression(Query *parse, Node *expr, int kind);
result_plan->nParamExec = length(PlannerParamVar);
/* final cleanup of the plan */
- set_plan_references(result_plan);
+ set_plan_references(parse, result_plan);
/* restore state for outer planner, if any */
PlannerQueryLevel = save_PlannerQueryLevel;
* this query.
*/
parse->jointree = (FromExpr *)
- pull_up_subqueries(parse, (Node *) parse->jointree);
+ pull_up_subqueries(parse, (Node *) parse->jointree, false);
/*
* If so, we may have created opportunities to simplify the jointree.
parse->havingQual = preprocess_expression(parse, parse->havingQual,
EXPRKIND_HAVING);
+ /*
+ * Check for ungrouped variables passed to subplans in targetlist and
+ * HAVING clause (but not in WHERE or JOIN/ON clauses, since those are
+ * evaluated before grouping). We can't do this any earlier because
+ * we must use the preprocessed targetlist for comparisons of grouped
+ * expressions.
+ */
+ if (parse->hasSubLinks &&
+ (parse->groupClause != NIL || parse->hasAggs))
+ check_subplans_for_ungrouped_vars(parse);
+
/*
* A HAVING clause without aggregates is equivalent to a WHERE clause
* (except it can only refer to grouped fields). Transfer any
* grouping_planner.
*/
if (parse->resultRelation &&
- (lst = expand_inherted_rtentry(parse, parse->resultRelation, false))
+ (lst = expand_inherted_rtentry(parse, parse->resultRelation, false))
!= NIL)
plan = inheritance_planner(parse, lst);
else
* the parent query. If the subquery has no special features like
* grouping/aggregation then we can merge it into the parent's jointree.
*
+ * below_outer_join is true if this jointree node is within the nullable
+ * side of an outer join. This restricts what we can do.
+ *
* A tricky aspect of this code is that if we pull up a subquery we have
* to replace Vars that reference the subquery's outputs throughout the
* parent query, including quals attached to jointree nodes above the one
* copy of the tree; we have to invoke it just on the quals, instead.
*/
static Node *
-pull_up_subqueries(Query *parse, Node *jtnode)
+pull_up_subqueries(Query *parse, Node *jtnode, bool below_outer_join)
{
if (jtnode == NULL)
return NULL;
* Is this a subquery RTE, and if so, is the subquery simple
* enough to pull up? (If not, do nothing at this node.)
*
- * Note: even if the subquery itself is simple enough, we can't
- * pull it up if there is a reference to its whole tuple result.
+ * If we are inside an outer join, only pull up subqueries whose
+ * targetlists are nullable --- otherwise substituting their tlist
+ * entries for upper Var references would do the wrong thing
+ * (the results wouldn't become NULL when they're supposed to).
+ * XXX This could be improved by generating pseudo-variables for
+ * such expressions; we'd have to figure out how to get the pseudo-
+ * variables evaluated at the right place in the modified plan tree.
+ * Fix it someday.
+ *
+ * Note: even if the subquery itself is simple enough, we can't pull
+ * it up if there is a reference to its whole tuple result. Perhaps
+ * a pseudo-variable is the answer here too.
*/
- if (subquery && is_simple_subquery(subquery) &&
+ if (rte->rtekind == RTE_SUBQUERY && is_simple_subquery(subquery) &&
+ (!below_outer_join || has_nullable_targetlist(subquery)) &&
!contain_whole_tuple_var((Node *) parse, varno, 0))
{
int rtoffset;
- Node *subjointree;
List *subtlist;
- List *l;
+ List *rt;
/*
* First, recursively pull up the subquery's subqueries, so
* having chunks of structure multiply linked.
*/
subquery->jointree = (FromExpr *)
- pull_up_subqueries(subquery, (Node *) subquery->jointree);
+ pull_up_subqueries(subquery, (Node *) subquery->jointree,
+ below_outer_join);
/*
- * Append the subquery's rangetable to mine (currently, no
- * adjustments will be needed in the subquery's rtable).
+ * Now make a modifiable copy of the subquery that we can
+ * run OffsetVarNodes on.
*/
- rtoffset = length(parse->rtable);
- parse->rtable = nconc(parse->rtable,
- copyObject(subquery->rtable));
+ subquery = copyObject(subquery);
/*
- * Make copies of the subquery's jointree and targetlist with
- * varnos adjusted to match the merged rangetable.
+ * Adjust varnos in subquery so that we can append its
+ * rangetable to upper query's.
*/
- subjointree = copyObject(subquery->jointree);
- OffsetVarNodes(subjointree, rtoffset, 0);
- subtlist = copyObject(subquery->targetList);
- OffsetVarNodes((Node *) subtlist, rtoffset, 0);
+ rtoffset = length(parse->rtable);
+ OffsetVarNodes((Node *) subquery, rtoffset, 0);
/*
* Replace all of the top query's references to the subquery's
* outputs with copies of the adjusted subtlist items, being
* careful not to replace any of the jointree structure.
+ * (This'd be a lot cleaner if we could use query_tree_mutator.)
*/
+ subtlist = subquery->targetList;
parse->targetList = (List *)
ResolveNew((Node *) parse->targetList,
varno, 0, subtlist, CMD_SELECT, 0);
resolvenew_in_jointree((Node *) parse->jointree, varno, subtlist);
+ Assert(parse->setOperations == NULL);
parse->havingQual =
ResolveNew(parse->havingQual,
varno, 0, subtlist, CMD_SELECT, 0);
- /*
- * Pull up any FOR UPDATE markers, too.
- */
- foreach(l, subquery->rowMarks)
+ foreach(rt, parse->rtable)
{
- int submark = lfirsti(l);
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
- parse->rowMarks = lappendi(parse->rowMarks,
- submark + rtoffset);
+ if (rte->rtekind == RTE_JOIN)
+ rte->joinaliasvars = (List *)
+ ResolveNew((Node *) rte->joinaliasvars,
+ varno, 0, subtlist, CMD_SELECT, 0);
}
+ /*
+ * Now append the adjusted rtable entries to upper query.
+ * (We hold off until after fixing the upper rtable entries;
+ * no point in running that code on the subquery ones too.)
+ */
+ parse->rtable = nconc(parse->rtable, subquery->rtable);
+
+ /*
+ * Pull up any FOR UPDATE markers, too. (OffsetVarNodes
+ * already adjusted the marker values, so just nconc the list.)
+ */
+ parse->rowMarks = nconc(parse->rowMarks, subquery->rowMarks);
+
/*
* Miscellaneous housekeeping.
*/
* Return the adjusted subquery jointree to replace the
* RangeTblRef entry in my jointree.
*/
- return subjointree;
+ return (Node *) subquery->jointree;
}
}
else if (IsA(jtnode, FromExpr))
List *l;
foreach(l, f->fromlist)
- lfirst(l) = pull_up_subqueries(parse, lfirst(l));
+ lfirst(l) = pull_up_subqueries(parse, lfirst(l),
+ below_outer_join);
}
else if (IsA(jtnode, JoinExpr))
{
JoinExpr *j = (JoinExpr *) jtnode;
- /*
- * At the moment, we can't pull up subqueries that are inside the
- * nullable side of an outer join, because substituting their target
- * list entries for upper Var references wouldn't do the right thing
- * (the entries wouldn't go to NULL when they're supposed to).
- * Suppressing the pullup is an ugly, performance-losing hack, but
- * I see no alternative for now. Find a better way to handle this
- * when we redesign query trees --- tgl 4/30/01.
- */
+ /* Recurse, being careful to tell myself when inside outer join */
switch (j->jointype)
{
case JOIN_INNER:
- j->larg = pull_up_subqueries(parse, j->larg);
- j->rarg = pull_up_subqueries(parse, j->rarg);
+ j->larg = pull_up_subqueries(parse, j->larg,
+ below_outer_join);
+ j->rarg = pull_up_subqueries(parse, j->rarg,
+ below_outer_join);
break;
case JOIN_LEFT:
- j->larg = pull_up_subqueries(parse, j->larg);
+ j->larg = pull_up_subqueries(parse, j->larg,
+ below_outer_join);
+ j->rarg = pull_up_subqueries(parse, j->rarg,
+ true);
break;
case JOIN_FULL:
+ j->larg = pull_up_subqueries(parse, j->larg,
+ true);
+ j->rarg = pull_up_subqueries(parse, j->rarg,
+ true);
break;
case JOIN_RIGHT:
- j->rarg = pull_up_subqueries(parse, j->rarg);
+ j->larg = pull_up_subqueries(parse, j->larg,
+ true);
+ j->rarg = pull_up_subqueries(parse, j->rarg,
+ below_outer_join);
break;
case JOIN_UNION:
+
/*
* This is where we fail if upper levels of planner
* haven't rewritten UNION JOIN as an Append ...
static bool
is_simple_subquery(Query *subquery)
{
-
/*
* Let's just make sure it's a valid subselect ...
*/
subquery->limitCount)
return false;
+ /*
+ * Don't pull up a subquery that has any set-returning functions in
+ * its targetlist. Otherwise we might well wind up inserting
+ * set-returning functions into places where they mustn't go,
+ * such as quals of higher queries.
+ */
+ if (expression_returns_set((Node *) subquery->targetList))
+ return false;
+
/*
* Hack: don't try to pull up a subquery with an empty jointree.
* query_planner() will correctly generate a Result plan for a
return true;
}
+/*
+ * has_nullable_targetlist
+ * Check a subquery in the range table to see if all the non-junk
+ * targetlist items are simple variables (and, hence, will correctly
+ * go to NULL when examined above the point of an outer join).
+ *
+ * A possible future extension is to accept strict functions of simple
+ * variables, eg, "x + 1".
+ */
+static bool
+has_nullable_targetlist(Query *subquery)
+{
+ List *l;
+
+ foreach(l, subquery->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+
+ /* ignore resjunk columns */
+ if (tle->resdom->resjunk)
+ continue;
+
+ /* Okay if tlist item is a simple Var */
+ if (tle->expr && IsA(tle->expr, Var))
+ continue;
+
+ return false;
+ }
+ return true;
+}
+
/*
* Helper routine for pull_up_subqueries: do ResolveNew on every expression
* in the jointree, without changing the jointree structure itself. Ugly,
/* Now, is it a FromExpr? */
if (child && IsA(child, FromExpr))
{
-
/*
* Yes, so do we want to merge it into parent? Always do
* so if child has just one element (since that doesn't
static Node *
preprocess_expression(Query *parse, Node *expr, int kind)
{
+ bool has_join_rtes;
+ List *rt;
/*
* Simplify constant expressions.
#endif
}
+ /* Expand SubLinks to SubPlans */
if (parse->hasSubLinks)
- {
- /* Expand SubLinks to SubPlans */
expr = SS_process_sublinks(expr);
- if (kind != EXPRKIND_WHERE &&
- (parse->groupClause != NIL || parse->hasAggs))
- {
-
- /*
- * Check for ungrouped variables passed to subplans. Note we
- * do NOT do this for subplans in WHERE (or JOIN/ON); it's
- * legal there because WHERE is evaluated pre-GROUP.
- */
- check_subplans_for_ungrouped_vars(expr, parse);
- }
- }
-
/* Replace uplevel vars with Param nodes */
if (PlannerQueryLevel > 1)
expr = SS_replace_correlation_vars(expr);
+ /*
+ * If the query has any join RTEs, try to replace join alias variables
+ * with base-relation variables, to allow quals to be pushed down.
+ * We must do this after sublink processing, since it does not recurse
+ * into sublinks.
+ *
+ * The flattening pass is expensive enough that it seems worthwhile to
+ * scan the rangetable to see if we can avoid it.
+ */
+ has_join_rtes = false;
+ foreach(rt, parse->rtable)
+ {
+ RangeTblEntry *rte = lfirst(rt);
+
+ if (rte->rtekind == RTE_JOIN)
+ {
+ has_join_rtes = true;
+ break;
+ }
+ }
+ if (has_join_rtes)
+ expr = flatten_join_alias_vars(expr, parse, false);
+
return expr;
}
if (parse->setOperations)
{
-
/*
* Construct the plan for set operations. The result will not
* need any work except perhaps a top-level sort and/or LIMIT.
if (parse->limitCount != NULL)
{
/*
- * A LIMIT clause limits the absolute number of tuples returned.
- * However, if it's not a constant LIMIT then we have to punt;
- * for lack of a better idea, assume 10% of the plan's result
- * is wanted.
+ * A LIMIT clause limits the absolute number of tuples
+ * returned. However, if it's not a constant LIMIT then we
+ * have to punt; for lack of a better idea, assume 10% of the
+ * plan's result is wanted.
*/
- double limit_fraction = 0.0;
+ double limit_fraction = 0.0;
if (IsA(parse->limitCount, Const))
{
int32 count = DatumGetInt32(limitc->constvalue);
/*
- * A NULL-constant LIMIT represents "LIMIT ALL", which
- * we treat the same as no limit (ie, expect to
- * retrieve all the tuples).
+ * A NULL-constant LIMIT represents "LIMIT ALL", which we
+ * treat the same as no limit (ie, expect to retrieve all
+ * the tuples).
*/
if (!limitc->constisnull && count > 0)
{
{
/*
* If we have absolute limits from both caller and LIMIT,
- * use the smaller value; if one is fractional and the other
- * absolute, treat the fraction as a fraction of the absolute
- * value; else we can multiply the two fractions together.
+ * use the smaller value; if one is fractional and the
+ * other absolute, treat the fraction as a fraction of the
+ * absolute value; else we can multiply the two fractions
+ * together.
*/
if (tuple_fraction >= 1.0)
{
if (parse->groupClause)
{
-
/*
* In GROUP BY mode, we have the little problem that we don't
* really know how many input tuples will be needed to make a
}
else if (parse->hasAggs)
{
-
/*
* Ungrouped aggregate will certainly want all the input
* tuples.
}
else if (parse->distinctClause)
{
-
/*
* SELECT DISTINCT, like GROUP, will absorb an unpredictable
* number of input tuples per output tuple. Handle the same
/*
* If there are aggregates then the Group node should just return
- * the same set of vars as the subplan did (but we can exclude any
- * GROUP BY expressions). If there are no aggregates then the
- * Group node had better compute the final tlist.
+ * the same set of vars as the subplan did. If there are no aggs
+ * then the Group node had better compute the final tlist.
*/
if (parse->hasAggs)
- group_tlist = flatten_tlist(result_plan->targetlist);
+ group_tlist = new_unsorted_tlist(result_plan->targetlist);
else
group_tlist = tlist;
}
else
{
-
/*
* We will need to do an explicit sort by the GROUP BY clause.
* make_groupplan will do the work, but set current_pathkeys
* where the a+b target will be used by the Sort/Group steps, and the
* other targets will be used for computing the final results. (In the
* above example we could theoretically suppress the a and b targets and
- * use only a+b, but it's not really worth the trouble.)
+ * pass down only c,d,a+b, but it's not really worth the trouble to
+ * eliminate simple var references from the subplan. We will avoid doing
+ * the extra computation to recompute a+b at the outer level; see
+ * replace_vars_with_subplan_refs() in setrefs.c.)
*
* 'parse' is the query being processed.
* 'tlist' is the query's target list.
if (!is_presorted)
{
-
/*
* The Sort node always just takes a copy of the subplan's tlist
* plus ordering information. (This might seem inefficient if the