Re: Aggregate function error in 7.4 - Mailing list pgsql-sql

j knight <enabled@myrealbox.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
listof 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
listof the acceptable GROUP BY
 
!      * expressions for use by check_ungrouped_columns().      */     foreach(lst, qry->groupClause)     {
***************
*** 144,151 ****                     (errcode(ERRCODE_GROUPING_ERROR),                    errmsg("aggregates not
allowedin 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.