Обсуждение: Aggregate function error in 7.4

Hi all.

I'm trying to migrate from 7.3.4 to 7.4.1. I've performed a pg_dumpall 
using the pg_dumpall from 7.4.1. Once 7.4 is running and I try to import 
the dump, I get the following error:

psql:pgsql.dump:301: ERROR:  column "r.day" must appear in the GROUP BY 
clause or be used in an aggregate function


The SQL it's choking on is:

CREATE VIEW maillog_day_tally AS
SELECT    day,    COALESCE(sum(r.tally), 0) AS received,    COALESCE(sum(s.tally), 0) AS sent
FROM    maillog_recv r FULL JOIN maillog_sent s USING (day, address)
GROUP BY day;


The tables:

CREATE TABLE maillog_sent (    day date NOT NULL,    tally integer NOT NULL,    address text NOT NULL,    CONSTRAINT
maillog_sent_pkPRIMARY KEY (day, address)
 
);

CREATE TABLE maillog_recv (    day date NOT NULL,    tally integer NOT NULL,    address text NOT NULL,    CONSTRAINT
maillog_recv_pkPRIMARY KEY (day, address)
 
);


Of course if I change the query to read "SELECT r.day ..." it will 
parse, however, I need to be able to select the day column from the 
result of the JOIN, not just from one of the two tables.

I'm at a loss to explain why 7.4 is treating that SELECT query 
differently than previous versions. I can't see anything in the 7.4 
release notes that would help explain this. Can anyone provide any insight?


maillog=> select version();
version
---------------------------------------------------------------------
PostgreSQL 7.4.1 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4



.joel

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.

Tom Lane wrote:
> 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...

Thank you Tom. This does indeed solve the problem.



.joel