Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

pgsql-hackers by date

Next:From: Dmitry SamersoffDate: 2000-01-29 16:40:04
Subject: RE: [HACKERS] select count(*) from hits group by count;
Previous:From: Tom LaneDate: 2000-01-29 15:59:21
Subject: Re: [HACKERS] END/ABORT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group