1 /*-------------------------------------------------------------------------
4 * handle aggregates and window functions in parser
6 * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994, Regents of the University of California
11 * $PostgreSQL: pgsql/src/backend/parser/parse_agg.c,v 1.86 2008/12/31 00:08:37 tgl Exp $
13 *-------------------------------------------------------------------------
17 #include "nodes/makefuncs.h"
18 #include "nodes/nodeFuncs.h"
19 #include "optimizer/tlist.h"
20 #include "optimizer/var.h"
21 #include "parser/parse_agg.h"
22 #include "parser/parsetree.h"
23 #include "rewrite/rewriteManip.h"
24 #include "utils/lsyscache.h"
31 bool have_non_var_grouping;
33 } check_ungrouped_columns_context;
35 static void check_ungrouped_columns(Node *node, ParseState *pstate,
36 List *groupClauses, bool have_non_var_grouping);
37 static bool check_ungrouped_columns_walker(Node *node,
38 check_ungrouped_columns_context *context);
42 * transformAggregateCall -
43 * Finish initial transformation of an aggregate call
45 * parse_func.c has recognized the function as an aggregate, and has set
46 * up all the fields of the Aggref except agglevelsup. Here we must
47 * determine which query level the aggregate actually belongs to, set
48 * agglevelsup accordingly, and mark p_hasAggs true in the corresponding
52 transformAggregateCall(ParseState *pstate, Aggref *agg)
57 * The aggregate's level is the same as the level of the lowest-level
58 * variable or aggregate in its arguments; or if it contains no variables
59 * at all, we presume it to be local.
61 min_varlevel = find_minimum_var_level((Node *) agg->args);
64 * An aggregate can't directly contain another aggregate call of the same
65 * level (though outer aggs are okay). We can skip this check if we
66 * didn't find any local vars or aggs.
68 if (min_varlevel == 0)
70 if (pstate->p_hasAggs &&
71 checkExprHasAggs((Node *) agg->args))
73 (errcode(ERRCODE_GROUPING_ERROR),
74 errmsg("aggregate function calls cannot be nested"),
75 parser_errposition(pstate,
76 locate_agg_of_level((Node *) agg->args, 0))));
79 /* It can't contain window functions either */
80 if (pstate->p_hasWindowFuncs &&
81 checkExprHasWindowFuncs((Node *) agg->args))
83 (errcode(ERRCODE_GROUPING_ERROR),
84 errmsg("aggregate function calls cannot contain window function calls"),
85 parser_errposition(pstate,
86 locate_windowfunc((Node *) agg->args))));
90 agg->agglevelsup = min_varlevel;
92 /* Mark the correct pstate as having aggregates */
93 while (min_varlevel-- > 0)
94 pstate = pstate->parentParseState;
95 pstate->p_hasAggs = true;
99 * transformWindowFuncCall -
100 * Finish initial transformation of a window function call
102 * parse_func.c has recognized the function as a window function, and has set
103 * up all the fields of the WindowFunc except winref. Here we must (1) add
104 * the WindowDef to the pstate (if not a duplicate of one already present) and
105 * set winref to link to it; and (2) mark p_hasWindowFuncs true in the pstate.
106 * Unlike aggregates, only the most closely nested pstate level need be
107 * considered --- there are no "outer window functions" per SQL spec.
110 transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
114 * A window function call can't contain another one (but aggs are OK).
115 * XXX is this required by spec, or just an unimplemented feature?
117 if (pstate->p_hasWindowFuncs &&
118 checkExprHasWindowFuncs((Node *) wfunc->args))
120 (errcode(ERRCODE_WINDOWING_ERROR),
121 errmsg("window function calls cannot be nested"),
122 parser_errposition(pstate,
123 locate_windowfunc((Node *) wfunc->args))));
126 * If the OVER clause just specifies a window name, find that
127 * WINDOW clause (which had better be present). Otherwise, try to
128 * match all the properties of the OVER clause, and make a new entry
129 * in the p_windowdefs list if no luck.
136 Assert(windef->refname == NULL &&
137 windef->partitionClause == NIL &&
138 windef->orderClause == NIL &&
139 windef->frameOptions == FRAMEOPTION_DEFAULTS);
141 foreach(lc, pstate->p_windowdefs)
143 WindowDef *refwin = (WindowDef *) lfirst(lc);
146 if (refwin->name && strcmp(refwin->name, windef->name) == 0)
148 wfunc->winref = winref;
152 if (lc == NULL) /* didn't find it? */
154 (errcode(ERRCODE_UNDEFINED_OBJECT),
155 errmsg("window \"%s\" does not exist", windef->name),
156 parser_errposition(pstate, windef->location)));
163 foreach(lc, pstate->p_windowdefs)
165 WindowDef *refwin = (WindowDef *) lfirst(lc);
168 if (refwin->refname && windef->refname &&
169 strcmp(refwin->refname, windef->refname) == 0)
170 /* matched on refname */ ;
171 else if (!refwin->refname && !windef->refname)
172 /* matched, no refname */ ;
175 if (equal(refwin->partitionClause, windef->partitionClause) &&
176 equal(refwin->orderClause, windef->orderClause) &&
177 refwin->frameOptions == windef->frameOptions)
179 /* found a duplicate window specification */
180 wfunc->winref = winref;
184 if (lc == NULL) /* didn't find it? */
186 pstate->p_windowdefs = lappend(pstate->p_windowdefs, windef);
187 wfunc->winref = list_length(pstate->p_windowdefs);
191 pstate->p_hasWindowFuncs = true;
195 * parseCheckAggregates
196 * Check for aggregates where they shouldn't be and improper grouping.
198 * Ideally this should be done earlier, but it's difficult to distinguish
199 * aggregates from plain functions at the grammar level. So instead we
200 * check here. This function should be called after the target list and
201 * qualifications are finalized.
204 parseCheckAggregates(ParseState *pstate, Query *qry)
206 List *groupClauses = NIL;
207 bool have_non_var_grouping;
214 /* This should only be called if we found aggregates or grouping */
215 Assert(pstate->p_hasAggs || qry->groupClause || qry->havingQual);
218 * Scan the range table to see if there are JOIN or self-reference CTE
219 * entries. We'll need this info below.
221 hasJoinRTEs = hasSelfRefRTEs = false;
222 foreach(l, pstate->p_rtable)
224 RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);
226 if (rte->rtekind == RTE_JOIN)
228 else if (rte->rtekind == RTE_CTE && rte->self_reference)
229 hasSelfRefRTEs = true;
233 * Aggregates must never appear in WHERE or JOIN/ON clauses.
235 * (Note this check should appear first to deliver an appropriate error
236 * message; otherwise we are likely to complain about some innocent
237 * variable in the target list, which is outright misleading if the
238 * problem is in WHERE.)
240 if (checkExprHasAggs(qry->jointree->quals))
242 (errcode(ERRCODE_GROUPING_ERROR),
243 errmsg("aggregates not allowed in WHERE clause"),
244 parser_errposition(pstate,
245 locate_agg_of_level(qry->jointree->quals, 0))));
246 if (checkExprHasAggs((Node *) qry->jointree->fromlist))
248 (errcode(ERRCODE_GROUPING_ERROR),
249 errmsg("aggregates not allowed in JOIN conditions"),
250 parser_errposition(pstate,
251 locate_agg_of_level((Node *) qry->jointree->fromlist, 0))));
254 * No aggregates allowed in GROUP BY clauses, either.
256 * While we are at it, build a list of the acceptable GROUP BY expressions
257 * for use by check_ungrouped_columns().
259 foreach(l, qry->groupClause)
261 SortGroupClause *grpcl = (SortGroupClause *) lfirst(l);
264 expr = get_sortgroupclause_expr(grpcl, qry->targetList);
266 continue; /* probably cannot happen */
267 if (checkExprHasAggs(expr))
269 (errcode(ERRCODE_GROUPING_ERROR),
270 errmsg("aggregates not allowed in GROUP BY clause"),
271 parser_errposition(pstate,
272 locate_agg_of_level(expr, 0))));
273 groupClauses = lcons(expr, groupClauses);
277 * If there are join alias vars involved, we have to flatten them to the
278 * underlying vars, so that aliased and unaliased vars will be correctly
279 * taken as equal. We can skip the expense of doing this if no rangetable
280 * entries are RTE_JOIN kind.
281 * We use the planner's flatten_join_alias_vars routine to do the
282 * flattening; it wants a PlannerInfo root node, which fortunately can be
287 root = makeNode(PlannerInfo);
289 root->planner_cxt = CurrentMemoryContext;
290 root->hasJoinRTEs = true;
292 groupClauses = (List *) flatten_join_alias_vars(root,
293 (Node *) groupClauses);
296 root = NULL; /* keep compiler quiet */
299 * Detect whether any of the grouping expressions aren't simple Vars; if
300 * they're all Vars then we don't have to work so hard in the recursive
301 * scans. (Note we have to flatten aliases before this.)
303 have_non_var_grouping = false;
304 foreach(l, groupClauses)
306 if (!IsA((Node *) lfirst(l), Var))
308 have_non_var_grouping = true;
314 * Check the targetlist and HAVING clause for ungrouped variables.
316 * Note: because we check resjunk tlist elements as well as regular ones,
317 * this will also find ungrouped variables that came from ORDER BY and
318 * WINDOW clauses. For that matter, it's also going to examine the
319 * grouping expressions themselves --- but they'll all pass the test ...
321 clause = (Node *) qry->targetList;
323 clause = flatten_join_alias_vars(root, clause);
324 check_ungrouped_columns(clause, pstate,
325 groupClauses, have_non_var_grouping);
327 clause = (Node *) qry->havingQual;
329 clause = flatten_join_alias_vars(root, clause);
330 check_ungrouped_columns(clause, pstate,
331 groupClauses, have_non_var_grouping);
334 * Per spec, aggregates can't appear in a recursive term.
336 if (pstate->p_hasAggs && hasSelfRefRTEs)
338 (errcode(ERRCODE_INVALID_RECURSION),
339 errmsg("aggregate functions not allowed in a recursive query's recursive term"),
340 parser_errposition(pstate,
341 locate_agg_of_level((Node *) qry, 0))));
345 * parseCheckWindowFuncs
346 * Check for window functions where they shouldn't be.
348 * We have to forbid window functions in WHERE, JOIN/ON, HAVING, GROUP BY,
349 * and window specifications. (Other clauses, such as RETURNING and LIMIT,
350 * have already been checked.) Transformation of all these clauses must
351 * be completed already.
354 parseCheckWindowFuncs(ParseState *pstate, Query *qry)
358 /* This should only be called if we found window functions */
359 Assert(pstate->p_hasWindowFuncs);
361 if (checkExprHasWindowFuncs(qry->jointree->quals))
363 (errcode(ERRCODE_WINDOWING_ERROR),
364 errmsg("window functions not allowed in WHERE clause"),
365 parser_errposition(pstate,
366 locate_windowfunc(qry->jointree->quals))));
367 if (checkExprHasWindowFuncs((Node *) qry->jointree->fromlist))
369 (errcode(ERRCODE_WINDOWING_ERROR),
370 errmsg("window functions not allowed in JOIN conditions"),
371 parser_errposition(pstate,
372 locate_windowfunc((Node *) qry->jointree->fromlist))));
373 if (checkExprHasWindowFuncs(qry->havingQual))
375 (errcode(ERRCODE_WINDOWING_ERROR),
376 errmsg("window functions not allowed in HAVING clause"),
377 parser_errposition(pstate,
378 locate_windowfunc(qry->havingQual))));
380 foreach(l, qry->groupClause)
382 SortGroupClause *grpcl = (SortGroupClause *) lfirst(l);
385 expr = get_sortgroupclause_expr(grpcl, qry->targetList);
386 if (checkExprHasWindowFuncs(expr))
388 (errcode(ERRCODE_WINDOWING_ERROR),
389 errmsg("window functions not allowed in GROUP BY clause"),
390 parser_errposition(pstate,
391 locate_windowfunc(expr))));
394 foreach(l, qry->windowClause)
396 WindowClause *wc = (WindowClause *) lfirst(l);
399 foreach(l2, wc->partitionClause)
401 SortGroupClause *grpcl = (SortGroupClause *) lfirst(l2);
404 expr = get_sortgroupclause_expr(grpcl, qry->targetList);
405 if (checkExprHasWindowFuncs(expr))
407 (errcode(ERRCODE_WINDOWING_ERROR),
408 errmsg("window functions not allowed in window definition"),
409 parser_errposition(pstate,
410 locate_windowfunc(expr))));
412 foreach(l2, wc->orderClause)
414 SortGroupClause *grpcl = (SortGroupClause *) lfirst(l2);
417 expr = get_sortgroupclause_expr(grpcl, qry->targetList);
418 if (checkExprHasWindowFuncs(expr))
420 (errcode(ERRCODE_WINDOWING_ERROR),
421 errmsg("window functions not allowed in window definition"),
422 parser_errposition(pstate,
423 locate_windowfunc(expr))));
429 * check_ungrouped_columns -
430 * Scan the given expression tree for ungrouped variables (variables
431 * that are not listed in the groupClauses list and are not within
432 * the arguments of aggregate functions). Emit a suitable error message
435 * NOTE: we assume that the given clause has been transformed suitably for
436 * parser output. This means we can use expression_tree_walker.
438 * NOTE: we recognize grouping expressions in the main query, but only
439 * grouping Vars in subqueries. For example, this will be rejected,
440 * although it could be allowed:
442 * (SELECT x FROM bar where y = (foo.a + foo.b))
445 * The difficulty is the need to account for different sublevels_up.
446 * This appears to require a whole custom version of equal(), which is
447 * way more pain than the feature seems worth.
450 check_ungrouped_columns(Node *node, ParseState *pstate,
451 List *groupClauses, bool have_non_var_grouping)
453 check_ungrouped_columns_context context;
455 context.pstate = pstate;
456 context.groupClauses = groupClauses;
457 context.have_non_var_grouping = have_non_var_grouping;
458 context.sublevels_up = 0;
459 check_ungrouped_columns_walker(node, &context);
463 check_ungrouped_columns_walker(Node *node,
464 check_ungrouped_columns_context *context)
470 if (IsA(node, Const) ||
472 return false; /* constants are always acceptable */
475 * If we find an aggregate call of the original level, do not recurse into
476 * its arguments; ungrouped vars in the arguments are not an error. We can
477 * also skip looking at the arguments of aggregates of higher levels,
478 * since they could not possibly contain Vars that are of concern to us
479 * (see transformAggregateCall). We do need to look into the arguments of
480 * aggregates of lower levels, however.
482 if (IsA(node, Aggref) &&
483 (int) ((Aggref *) node)->agglevelsup >= context->sublevels_up)
487 * If we have any GROUP BY items that are not simple Vars, check to see if
488 * subexpression as a whole matches any GROUP BY item. We need to do this
489 * at every recursion level so that we recognize GROUPed-BY expressions
490 * before reaching variables within them. But this only works at the outer
491 * query level, as noted above.
493 if (context->have_non_var_grouping && context->sublevels_up == 0)
495 foreach(gl, context->groupClauses)
497 if (equal(node, lfirst(gl)))
498 return false; /* acceptable, do not descend more */
503 * If we have an ungrouped Var of the original query level, we have a
504 * failure. Vars below the original query level are not a problem, and
505 * neither are Vars from above it. (If such Vars are ungrouped as far as
506 * their own query level is concerned, that's someone else's problem...)
510 Var *var = (Var *) node;
514 if (var->varlevelsup != context->sublevels_up)
515 return false; /* it's not local to my query, ignore */
518 * Check for a match, if we didn't do it above.
520 if (!context->have_non_var_grouping || context->sublevels_up != 0)
522 foreach(gl, context->groupClauses)
524 Var *gvar = (Var *) lfirst(gl);
526 if (IsA(gvar, Var) &&
527 gvar->varno == var->varno &&
528 gvar->varattno == var->varattno &&
529 gvar->varlevelsup == 0)
530 return false; /* acceptable, we're okay */
534 /* Found an ungrouped local variable; generate error message */
535 Assert(var->varno > 0 &&
536 (int) var->varno <= list_length(context->pstate->p_rtable));
537 rte = rt_fetch(var->varno, context->pstate->p_rtable);
538 attname = get_rte_attribute_name(rte, var->varattno);
539 if (context->sublevels_up == 0)
541 (errcode(ERRCODE_GROUPING_ERROR),
542 errmsg("column \"%s.%s\" must appear in the GROUP BY clause or be used in an aggregate function",
543 rte->eref->aliasname, attname),
544 parser_errposition(context->pstate, var->location)));
547 (errcode(ERRCODE_GROUPING_ERROR),
548 errmsg("subquery uses ungrouped column \"%s.%s\" from outer query",
549 rte->eref->aliasname, attname),
550 parser_errposition(context->pstate, var->location)));
553 if (IsA(node, Query))
555 /* Recurse into subselects */
558 context->sublevels_up++;
559 result = query_tree_walker((Query *) node,
560 check_ungrouped_columns_walker,
563 context->sublevels_up--;
566 return expression_tree_walker(node, check_ungrouped_columns_walker,
571 * Create expression trees for the transition and final functions
572 * of an aggregate. These are needed so that polymorphic functions
573 * can be used within an aggregate --- without the expression trees,
574 * such functions would not know the datatypes they are supposed to use.
575 * (The trees will never actually be executed, however, so we can skimp
576 * a bit on correctness.)
578 * agg_input_types, agg_state_type, agg_result_type identify the input,
579 * transition, and result types of the aggregate. These should all be
580 * resolved to actual types (ie, none should ever be ANYELEMENT etc).
582 * transfn_oid and finalfn_oid identify the funcs to be called; the latter
585 * Pointers to the constructed trees are returned into *transfnexpr and
586 * *finalfnexpr. The latter is set to NULL if there's no finalfn.
589 build_aggregate_fnexprs(Oid *agg_input_types,
603 * Build arg list to use in the transfn FuncExpr node. We really only care
604 * that transfn can discover the actual argument types at runtime using
605 * get_fn_expr_argtype(), so it's okay to use Param nodes that don't
606 * correspond to any real Param.
608 argp = makeNode(Param);
609 argp->paramkind = PARAM_EXEC;
611 argp->paramtype = agg_state_type;
612 argp->paramtypmod = -1;
615 args = list_make1(argp);
617 for (i = 0; i < agg_num_inputs; i++)
619 argp = makeNode(Param);
620 argp->paramkind = PARAM_EXEC;
622 argp->paramtype = agg_input_types[i];
623 argp->paramtypmod = -1;
625 args = lappend(args, argp);
628 *transfnexpr = (Expr *) makeFuncExpr(transfn_oid,
633 /* see if we have a final function */
634 if (!OidIsValid(finalfn_oid))
641 * Build expr tree for final function
643 argp = makeNode(Param);
644 argp->paramkind = PARAM_EXEC;
646 argp->paramtype = agg_state_type;
647 argp->paramtypmod = -1;
649 args = list_make1(argp);
651 *finalfnexpr = (Expr *) makeFuncExpr(finalfn_oid,