Re: [HACKERS] select count(*) from hits group by count;

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