Re: Aggregate function error in 7.4

j knight <enabled(at)myrealbox(dot)com> writes:
> [ query with GROUP BY on a FULL JOIN USING column ]
> psql:pgsql.dump:301: ERROR: column "r.day" must appear in the GROUP BY
> clause or be used in an aggregate function

Argh. This is the result of a thinko in an optimization added in 7.4.
The patch is attached...

regards, tom lane

*** src/backend/parser/parse_agg.c.orig Sat Nov 29 14:51:51 2003
--- src/backend/parser/parse_agg.c Wed Jan 28 02:25:53 2004
***************
*** 98,104 ****
parseCheckAggregates(ParseState *pstate, Query *qry)
{
List *groupClauses = NIL;
! bool have_non_var_grouping = false;
List *lst;
bool hasJoinRTEs;
Node *clause;
--- 98,104 ----
parseCheckAggregates(ParseState *pstate, Query *qry)
{
List *groupClauses = NIL;
! bool have_non_var_grouping;
List *lst;
bool hasJoinRTEs;
Node *clause;
***************
*** 127,135 ****
* No aggregates allowed in GROUP BY clauses, either.
*
* While we are at it, build a list of the acceptable GROUP BY
! * expressions for use by check_ungrouped_columns() (this avoids
! * repeated scans of the targetlist within the recursive routine...).
! * And detect whether any of the expressions aren't simple Vars.
*/
foreach(lst, qry->groupClause)
{
--- 127,133 ----
* No aggregates allowed in GROUP BY clauses, either.
*
* While we are at it, build a list of the acceptable GROUP BY
! * expressions for use by check_ungrouped_columns().
*/
foreach(lst, qry->groupClause)
{
***************
*** 144,151 ****
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("aggregates not allowed in GROUP BY clause")));
groupClauses = lcons(expr, groupClauses);
- if (!IsA(expr, Var))
- have_non_var_grouping = true;
}

/*
--- 142,147 ----
***************
*** 169,174 ****
--- 165,185 ----
if (hasJoinRTEs)
groupClauses = (List *) flatten_join_alias_vars(qry,
(Node *) groupClauses);
+
+ /*
+ * Detect whether any of the grouping expressions aren't simple Vars;
+ * if they're all Vars then we don't have to work so hard in the
+ * recursive scans. (Note we have to flatten aliases before this.)
+ */
+ have_non_var_grouping = false;
+ foreach(lst, groupClauses)
+ {
+ if (!IsA((Node *) lfirst(lst), Var))
+ {
+ have_non_var_grouping = true;
+ break;
+ }
+ }

/*
* Check the targetlist and HAVING clause for ungrouped variables.