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

Oleg Bartunov <oleg@sai.msu.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
columnof 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