1 /*-------------------------------------------------------------------------
4 * The query optimizer external interface.
6 * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
7 * Portions Copyright (c) 1994, Regents of the University of California
11 * $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.73 2000/01/26 05:56:37 momjian Exp $
13 *-------------------------------------------------------------------------
15 #include <sys/types.h>
19 #include "access/genam.h"
20 #include "access/heapam.h"
21 #include "catalog/pg_type.h"
22 #include "executor/executor.h"
23 #include "nodes/makefuncs.h"
24 #include "optimizer/clauses.h"
25 #include "optimizer/internal.h"
26 #include "optimizer/paths.h"
27 #include "optimizer/planmain.h"
28 #include "optimizer/planner.h"
29 #include "optimizer/prep.h"
30 #include "optimizer/subselect.h"
31 #include "optimizer/tlist.h"
32 #include "optimizer/var.h"
33 #include "parser/parse_expr.h"
34 #include "parser/parse_oper.h"
35 #include "utils/builtins.h"
36 #include "utils/lsyscache.h"
37 #include "utils/syscache.h"
39 static List *make_subplanTargetList(Query *parse, List *tlist,
40 AttrNumber **groupColIdx);
41 static Plan *make_groupplan(List *group_tlist, bool tuplePerGroup,
42 List *groupClause, AttrNumber *grpColIdx,
43 bool is_presorted, Plan *subplan);
44 static Plan *make_sortplan(List *tlist, List *sortcls, Plan *plannode);
46 /*****************************************************************************
48 * Query optimizer entry point
50 *****************************************************************************/
56 /* Initialize state for subselects */
57 PlannerQueryLevel = 1;
58 PlannerInitPlan = NULL;
59 PlannerParamVar = NULL;
62 transformKeySetQuery(parse);
64 result_plan = union_planner(parse);
66 Assert(PlannerQueryLevel == 1);
67 if (PlannerPlanId > 0)
69 result_plan->initPlan = PlannerInitPlan;
70 (void) SS_finalize_plan(result_plan);
72 result_plan->nParamExec = length(PlannerParamVar);
74 set_plan_references(result_plan);
82 * Invokes the planner on union queries if there are any left,
83 * recursing if necessary to get them all, then processes normal plans.
85 * Returns a query plan.
89 union_planner(Query *parse)
91 List *tlist = parse->targetList;
92 List *rangetable = parse->rtable;
93 Plan *result_plan = (Plan *) NULL;
94 AttrNumber *groupColIdx = NULL;
95 List *current_pathkeys = NIL;
99 * A HAVING clause without aggregates is equivalent to a WHERE clause
100 * (except it can only refer to grouped fields). If there are no
101 * aggs anywhere in the query, then we don't want to create an Agg
102 * plan node, so merge the HAVING condition into WHERE. (We used to
103 * consider this an error condition, but it seems to be legal SQL.)
105 if (parse->havingQual != NULL && ! parse->hasAggs)
107 if (parse->qual == NULL)
108 parse->qual = parse->havingQual;
110 parse->qual = (Node *) make_andclause(lappend(lcons(parse->qual,
113 parse->havingQual = NULL;
117 * Simplify constant expressions in targetlist and quals.
119 * Note that at this point the qual has not yet been converted to
120 * implicit-AND form, so we can apply eval_const_expressions directly.
121 * Also note that we need to do this before SS_process_sublinks,
122 * because that routine inserts bogus "Const" nodes.
124 tlist = (List *) eval_const_expressions((Node *) tlist);
125 parse->qual = eval_const_expressions(parse->qual);
126 parse->havingQual = eval_const_expressions(parse->havingQual);
129 if (parse->unionClause)
131 result_plan = (Plan *) plan_union_queries(parse);
132 /* XXX do we need to do this? bjm 12/19/97 */
133 tlist = preprocess_targetlist(tlist,
135 parse->resultRelation,
138 * We leave current_pathkeys NIL indicating we do not know sort order.
139 * Actually, for a normal UNION we have done an explicit sort; ought
140 * to change interface to plan_union_queries to pass that info back!
143 else if ((rt_index = first_inherit_rt_entry(rangetable)) != -1)
148 * Generate appropriate target list for subplan; may be different
149 * from tlist if grouping or aggregation is needed.
151 sub_tlist = make_subplanTargetList(parse, tlist, &groupColIdx);
154 * Recursively plan the subqueries needed for inheritance
156 result_plan = (Plan *) plan_inherit_queries(parse, sub_tlist,
160 * Fix up outer target list. NOTE: unlike the case for non-inherited
161 * query, we pass the unfixed tlist to subplans, which do their own
162 * fixing. But we still want to fix the outer target list afterwards.
163 * I *think* this is correct --- doing the fix before recursing is
164 * definitely wrong, because preprocess_targetlist() will do the
165 * wrong thing if invoked twice on the same list. Maybe that is a bug?
168 tlist = preprocess_targetlist(tlist,
170 parse->resultRelation,
173 if (parse->rowMark != NULL)
174 elog(ERROR, "SELECT FOR UPDATE is not supported for inherit queries");
176 * We leave current_pathkeys NIL indicating we do not know sort order
177 * of the Append-ed results.
184 /* Preprocess targetlist in case we are inside an INSERT/UPDATE. */
185 tlist = preprocess_targetlist(tlist,
187 parse->resultRelation,
191 * Add row-mark targets for UPDATE (should this be done in
192 * preprocess_targetlist?)
194 if (parse->rowMark != NULL)
198 foreach(l, parse->rowMark)
200 RowMark *rowmark = (RowMark *) lfirst(l);
206 if (!(rowmark->info & ROW_MARK_FOR_UPDATE))
209 resname = (char *) palloc(32);
210 sprintf(resname, "ctid%u", rowmark->rti);
211 resdom = makeResdom(length(tlist) + 1,
219 var = makeVar(rowmark->rti, -1, TIDOID, -1, 0);
221 ctid = makeTargetEntry(resdom, (Node *) var);
222 tlist = lappend(tlist, ctid);
227 * Generate appropriate target list for subplan; may be different
228 * from tlist if grouping or aggregation is needed.
230 sub_tlist = make_subplanTargetList(parse, tlist, &groupColIdx);
233 * Figure out whether we need a sorted result from query_planner.
235 * If we have a GROUP BY clause, then we want a result sorted
236 * properly for grouping. Otherwise, if there is an ORDER BY clause,
237 * we want to sort by the ORDER BY clause.
239 if (parse->groupClause)
241 parse->query_pathkeys =
242 make_pathkeys_for_sortclauses(parse->groupClause, tlist);
244 else if (parse->sortClause)
246 parse->query_pathkeys =
247 make_pathkeys_for_sortclauses(parse->sortClause, tlist);
251 parse->query_pathkeys = NIL;
254 /* Generate the (sub) plan */
255 result_plan = query_planner(parse,
257 (List *) parse->qual);
259 /* query_planner returns actual sort order (which is not
260 * necessarily what we requested) in query_pathkeys.
262 current_pathkeys = parse->query_pathkeys;
265 /* query_planner returns NULL if it thinks plan is bogus */
267 elog(ERROR, "union_planner: failed to create plan");
270 * If we have a GROUP BY clause, insert a group node (plus the
271 * appropriate sort node, if necessary).
273 if (parse->groupClause)
277 List *group_pathkeys;
281 * Decide whether how many tuples per group the Group node needs
282 * to return. (Needs only one tuple per group if no aggregate is
283 * present. Otherwise, need every tuple from the group to do the
284 * aggregation.) Note tuplePerGroup is named backwards :-(
286 tuplePerGroup = parse->hasAggs;
289 * If there are aggregates then the Group node should just return
290 * the same set of vars as the subplan did (but we can exclude
291 * any GROUP BY expressions). If there are no aggregates
292 * then the Group node had better compute the final tlist.
295 group_tlist = flatten_tlist(result_plan->targetlist);
300 * Figure out whether the path result is already ordered the way we
301 * need it --- if so, no need for an explicit sort step.
303 group_pathkeys = make_pathkeys_for_sortclauses(parse->groupClause,
305 if (pathkeys_contained_in(group_pathkeys, current_pathkeys))
307 is_sorted = true; /* no sort needed now */
308 /* current_pathkeys remains unchanged */
312 /* We will need to do an explicit sort by the GROUP BY clause.
313 * make_groupplan will do the work, but set current_pathkeys
314 * to indicate the resulting order.
317 current_pathkeys = group_pathkeys;
320 result_plan = make_groupplan(group_tlist,
329 * If we have a HAVING clause, do the necessary things with it.
330 * This code should parallel query_planner()'s initial processing
331 * of the WHERE clause.
333 if (parse->havingQual)
335 /* Convert the havingQual to implicit-AND normal form */
336 parse->havingQual = (Node *)
337 canonicalize_qual((Expr *) parse->havingQual, true);
339 /* Replace uplevel Vars with Params */
340 if (PlannerQueryLevel > 1)
341 parse->havingQual = SS_replace_correlation_vars(parse->havingQual);
343 if (parse->hasSubLinks)
345 /* Expand SubLinks to SubPlans */
346 parse->havingQual = SS_process_sublinks(parse->havingQual);
347 /* Check for ungrouped variables passed to subplans */
348 check_subplans_for_ungrouped_vars(parse->havingQual,
355 * If aggregate is present, insert the agg node
359 result_plan = (Plan *) make_agg(tlist, result_plan);
361 /* HAVING clause, if any, becomes qual of the Agg node */
362 result_plan->qual = (List *) parse->havingQual;
364 /* Note: Agg does not affect any existing sort order of the tuples */
368 * If we were not able to make the plan come out in the right order,
369 * add an explicit sort step.
371 if (parse->sortClause)
375 sort_pathkeys = make_pathkeys_for_sortclauses(parse->sortClause,
377 if (! pathkeys_contained_in(sort_pathkeys, current_pathkeys))
379 result_plan = make_sortplan(tlist, parse->sortClause, result_plan);
384 * Finally, if there is a UNIQUE clause, add the UNIQUE node.
386 if (parse->uniqueFlag)
388 result_plan = (Plan *) make_unique(tlist, result_plan,
396 * make_subplanTargetList
397 * Generate appropriate target list when grouping is required.
399 * When union_planner inserts Aggregate and/or Group plan nodes above
400 * the result of query_planner, we typically want to pass a different
401 * target list to query_planner than the outer plan nodes should have.
402 * This routine generates the correct target list for the subplan.
404 * The initial target list passed from the parser already contains entries
405 * for all ORDER BY and GROUP BY expressions, but it will not have entries
406 * for variables used only in HAVING clauses; so we need to add those
407 * variables to the subplan target list. Also, if we are doing either
408 * grouping or aggregation, we flatten all expressions except GROUP BY items
409 * into their component variables; the other expressions will be computed by
410 * the inserted nodes rather than by the subplan. For example,
412 * SELECT a+b,SUM(c+d) FROM table GROUP BY a+b;
413 * we want to pass this targetlist to the subplan:
415 * where the a+b target will be used by the Sort/Group steps, and the
416 * other targets will be used for computing the final results. (In the
417 * above example we could theoretically suppress the a and b targets and
418 * use only a+b, but it's not really worth the trouble.)
420 * 'parse' is the query being processed.
421 * 'tlist' is the query's target list.
422 * 'groupColIdx' receives an array of column numbers for the GROUP BY
423 * expressions (if there are any) in the subplan's target list.
425 * The result is the targetlist to be passed to the subplan.
429 make_subplanTargetList(Query *parse,
431 AttrNumber **groupColIdx)
440 * If we're not grouping or aggregating, nothing to do here;
441 * query_planner should receive the unmodified target list.
443 if (!parse->hasAggs && !parse->groupClause && !parse->havingQual)
447 * Otherwise, start with a "flattened" tlist (having just the vars
448 * mentioned in the targetlist and HAVING qual --- but not upper-
449 * level Vars; they will be replaced by Params later on).
451 sub_tlist = flatten_tlist(tlist);
452 extravars = pull_var_clause(parse->havingQual, false);
453 sub_tlist = add_to_flat_tlist(sub_tlist, extravars);
457 * If grouping, create sub_tlist entries for all GROUP BY expressions
458 * (GROUP BY items that are simple Vars should be in the list already),
459 * and make an array showing where the group columns are in the sub_tlist.
461 numCols = length(parse->groupClause);
465 AttrNumber *grpColIdx;
468 grpColIdx = (AttrNumber *) palloc(sizeof(AttrNumber) * numCols);
469 *groupColIdx = grpColIdx;
471 foreach(gl, parse->groupClause)
473 GroupClause *grpcl = (GroupClause *) lfirst(gl);
474 Node *groupexpr = get_sortgroupclause_expr(grpcl, tlist);
475 TargetEntry *te = NULL;
478 /* Find or make a matching sub_tlist entry */
479 foreach(sl, sub_tlist)
481 te = (TargetEntry *) lfirst(sl);
482 if (equal(groupexpr, te->expr))
487 te = makeTargetEntry(makeResdom(length(sub_tlist) + 1,
489 exprTypmod(groupexpr),
495 sub_tlist = lappend(sub_tlist, te);
498 /* and save its resno */
499 grpColIdx[keyno++] = te->resdom->resno;
508 * Add a Group node for GROUP BY processing.
509 * If we couldn't make the subplan produce presorted output for grouping,
510 * first add an explicit Sort node.
513 make_groupplan(List *group_tlist,
516 AttrNumber *grpColIdx,
520 int numCols = length(groupClause);
525 * The Sort node always just takes a copy of the subplan's tlist
526 * plus ordering information. (This might seem inefficient if the
527 * subplan contains complex GROUP BY expressions, but in fact Sort
528 * does not evaluate its targetlist --- it only outputs the same
529 * tuples in a new order. So the expressions we might be copying
530 * are just dummies with no extra execution cost.)
532 List *sort_tlist = new_unsorted_tlist(subplan->targetlist);
536 foreach(gl, groupClause)
538 GroupClause *grpcl = (GroupClause *) lfirst(gl);
539 TargetEntry *te = nth(grpColIdx[keyno]-1, sort_tlist);
540 Resdom *resdom = te->resdom;
543 * Check for the possibility of duplicate group-by clauses --- the
544 * parser should have removed 'em, but the Sort executor will get
545 * terribly confused if any get through!
547 if (resdom->reskey == 0)
549 /* OK, insert the ordering info needed by the executor. */
550 resdom->reskey = ++keyno;
551 resdom->reskeyop = get_opcode(grpcl->sortop);
555 subplan = (Plan *) make_sort(sort_tlist,
556 _NONAME_RELATION_ID_,
561 return (Plan *) make_group(group_tlist, tuplePerGroup, numCols,
567 * Add a Sort node to implement an explicit ORDER BY clause.
570 make_sortplan(List *tlist, List *sortcls, Plan *plannode)
577 * First make a copy of the tlist so that we don't corrupt the
581 temp_tlist = new_unsorted_tlist(tlist);
585 SortClause *sortcl = (SortClause *) lfirst(i);
586 Index refnumber = sortcl->tleSortGroupRef;
587 TargetEntry *tle = NULL;
591 foreach(l, temp_tlist)
593 tle = (TargetEntry *) lfirst(l);
594 if (tle->resdom->ressortgroupref == refnumber)
598 elog(ERROR, "make_sortplan: ORDER BY expression not found in targetlist");
599 resdom = tle->resdom;
602 * Check for the possibility of duplicate order-by clauses --- the
603 * parser should have removed 'em, but the executor will get terribly
604 * confused if any get through!
606 if (resdom->reskey == 0)
608 /* OK, insert the ordering info needed by the executor. */
609 resdom->reskey = ++keyno;
610 resdom->reskeyop = get_opcode(sortcl->sortop);
614 return (Plan *) make_sort(temp_tlist,
615 _NONAME_RELATION_ID_,
621 * pg_checkretval() -- check return value of a list of sql parse
624 * The return value of a sql function is the value returned by
625 * the final query in the function. We do some ad-hoc define-time
626 * type checking here to be sure that the user is returning the
629 * XXX Why is this function in this module?
632 pg_checkretval(Oid rettype, List *queryTreeList)
645 /* find the final query */
646 parse = (Query *) nth(length(queryTreeList) - 1, queryTreeList);
649 * test 1: if the last query is a utility invocation, then there had
650 * better not be a return value declared.
652 if (parse->commandType == CMD_UTILITY)
654 if (rettype == InvalidOid)
657 elog(ERROR, "return type mismatch in function decl: final query is a catalog utility");
660 /* okay, it's an ordinary query */
661 tlist = parse->targetList;
663 cmd = parse->commandType;
666 * test 2: if the function is declared to return no value, then the
667 * final query had better not be a retrieve.
669 if (rettype == InvalidOid)
671 if (cmd == CMD_SELECT)
673 "function declared with no return type, but final query is a retrieve");
678 /* by here, the function is declared to return some type */
679 if ((typ = typeidType(rettype)) == NULL)
680 elog(ERROR, "can't find return type %u for function\n", rettype);
683 * test 3: if the function is declared to return a value, then the
684 * final query had better be a retrieve.
686 if (cmd != CMD_SELECT)
687 elog(ERROR, "function declared to return type %s, but final query is not a retrieve", typeTypeName(typ));
690 * test 4: for base type returns, the target list should have exactly
691 * one entry, and its type should agree with what the user declared.
694 if (typeTypeRelid(typ) == InvalidOid)
696 if (ExecTargetListLength(tlist) > 1)
697 elog(ERROR, "function declared to return %s returns multiple values in final retrieve", typeTypeName(typ));
699 resnode = (Resdom *) ((TargetEntry *) lfirst(tlist))->resdom;
700 if (resnode->restype != rettype)
701 elog(ERROR, "return type mismatch in function: declared to return %s, returns %s", typeTypeName(typ), typeidTypeName(resnode->restype));
703 /* by here, base return types match */
708 * If the target list is of length 1, and the type of the varnode in
709 * the target list is the same as the declared return type, this is
710 * okay. This can happen, for example, where the body of the function
711 * is 'retrieve (x = func2())', where func2 has the same return type
712 * as the function that's calling it.
714 if (ExecTargetListLength(tlist) == 1)
716 resnode = (Resdom *) ((TargetEntry *) lfirst(tlist))->resdom;
717 if (resnode->restype == rettype)
722 * By here, the procedure returns a (set of) tuples. This part of the
723 * typechecking is a hack. We look up the relation that is the
724 * declared return type, and be sure that attributes 1 .. n in the
725 * target list match the declared types.
727 reln = heap_open(typeTypeRelid(typ), AccessShareLock);
729 relnatts = reln->rd_rel->relnatts;
731 if (ExecTargetListLength(tlist) != relnatts)
732 elog(ERROR, "function declared to return type %s does not retrieve (%s.*)", typeTypeName(typ), typeTypeName(typ));
734 /* expect attributes 1 .. n in order */
735 for (i = 1; i <= relnatts; i++)
737 TargetEntry *tle = lfirst(tlist);
738 Node *thenode = tle->expr;
739 Oid tletype = exprType(thenode);
741 if (tletype != reln->rd_att->attrs[i - 1]->atttypid)
742 elog(ERROR, "function declared to return type %s does not retrieve (%s.all)", typeTypeName(typ), typeTypeName(typ));
743 tlist = lnext(tlist);
746 heap_close(reln, AccessShareLock);