pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregate function error in 7.4
3+ messages / 2 participants
[nested] [flat]
* Aggregate function error in 7.4
@ 2004-01-28 06:36 j knight <enabled@myrealbox.com>
0 siblings, 1 reply; 3+ messages in thread
From: j knight @ 2004-01-28 06:36 UTC (permalink / raw)
To: pgsql-sql
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_pk PRIMARY KEY (day, address)
);
CREATE TABLE maillog_recv (
day date NOT NULL,
tally integer NOT NULL,
address text NOT NULL,
CONSTRAINT maillog_recv_pk PRIMARY 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggregate function error in 7.4
@ 2004-01-28 07:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: j knight <enabled@myrealbox.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-01-28 07:50 UTC (permalink / raw)
To: j knight <enabled@myrealbox.com>; +Cc: 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
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.
* Re: Aggregate function error in 7.4
@ 2004-01-28 19:18 j knight <enabled@myrealbox.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: j knight @ 2004-01-28 19:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-28 19:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-28 06:36 Aggregate function error in 7.4 j knight <enabled@myrealbox.com>
2004-01-28 07:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-28 19:18 ` j knight <enabled@myrealbox.com>
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