From: Tom Lane <tgl@sss.pgh.pa.us>
To: j knight <enabled@myrealbox.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregate function error in 7.4
Date: Wed, 28 Jan 2004 02:50:01 -0500
Message-ID: <10762.1075276201@sss.pgh.pa.us> (raw)
In-Reply-To: <40175887.2080108@myrealbox.com>
References: <40175887.2080108@myrealbox.com>
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
inline patch 1: src/backend/parser/parse_agg.c (+17/-6 lines) download inline-1.patch*** 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, enabled@myrealbox.com
Subject: Re: Aggregate function error in 7.4
In-Reply-To: <10762.1075276201@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox