From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] select count(*) from hits group by count; |
Date: | 2000-01-29 16:24:58 |
Message-ID: | 14902.949163098@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
> discovery=> select count(*) from hits group by count;
> ERROR: Aggregates not allowed in GROUP BY clause
> There's easy workaround :
> discovery=> select count(*) as qq from hits group by count;
> but I'm curious is this a valid query ?
I believe this is probably a bug. We are treating GROUP BY the same
way we treat ORDER BY, namely that if an item is a simple name or
integer constant, we try first to interpret it as a result-column
name or number; only if it does not match any column name do we fall
back on treating it as a general expression. And the default result-
column name for "count(*)" is just "count".
This behavior is necessary to conform to the standard for ORDER BY ---
in fact, SQL92 doesn't actually allow anything *but* a result-column
name or number for ORDER BY. Accepting an expression is a Postgres
extension (I imagine other DBMSs do it too).
But I can't see anything in the spec that justifies treating a GROUP BY
item that way: a GROUP BY item is defined as a <column reference> which
is a plain expression constituent. We should probably change the code
behavior so that GROUP BY is always interpreted as a normal expression.
Question is, how many existing apps might be broken by such a change?
> Another question:
> discovery=> select count(*) as qqq,* from hits group by last_access;
> produces error:
> ERROR: Illegal use of aggregates or non-group column in target list
> Do I really need to have all fields in GROUP clause ?
Yes. See SQL92 7.9(7):
7) If T is a grouped table, then each <column reference> in each
<value expression> that references a column of T shall refer-
ence a grouping column or be specified within a <set function
specification>.
> Mysql seems allows this ?
Mysql is broken if it accepts this. There's no unique answer to give
for an ungrouped, non-aggregated column.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Samersoff | 2000-01-29 16:40:04 | RE: [HACKERS] select count(*) from hits group by count; |
Previous Message | Tom Lane | 2000-01-29 15:59:21 | Re: [HACKERS] END/ABORT |