To: j knight <enabled@myrealbox.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregate function error in 7.4 
In-reply-to: <40175887.2080108@myrealbox.com> 
References: <40175887.2080108@myrealbox.com>
Comments: In-reply-to j knight <enabled@myrealbox.com>
	message dated "Tue, 27 Jan 2004 23:36:55 -0700"
Date: Wed, 28 Jan 2004 02:50:01 -0500
Message-ID: <10762.1075276201@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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 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.