BUG #4173: Illogical error message with aggregates + order by

The following bug has been logged online:

Bug reference: 4173
Logged by: Chris West (Faux)
Email address: pfx-psql(at)goeswhere(dot)com
PostgreSQL version: 8.3.1
Operating system: linux (debian lenny)
Description: Illogical error message with aggregates + order by
Details:

-- Prelim SQL:

create temporary table foo ( bar integer );
insert into foo (bar) values (3),(1),(2);

-- Simple example:

Consider the following SQL:

select count(bar) from foo order by bar;

For this input, both postgresql-8.2* and -8.3.* output:

ERROR: column "foo.bar" must appear in the GROUP BY clause or be used in an
aggregate function

This is misleading as COUNT() is clearly[0] an aggregate function, and I
believe the SQL to be valid (although I could not provide a standard
reference); MySQL and SQLite (I'm sorry) both accept it. The situation
probably does not arise in these servers, however.

-- Motivating example:

Using the aggregate array_accum from the documentation[1]:

CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

This creates an array of all the values accumulated into it; for instance:

select array_accum(bar) from foo

Produces:

{3,1,2}

The following is invalid, as above, but it is the code that I would expect
to run to recieve {1,2,3}:

select array_accum(bar) from foo order by bar;

The result of using group by, just in case it's not immediately obvious to
everyone (ha ha):

select array_accum(bar) from foo group by bar order by bar;

Is:

{1}
{2}
{3}

i.e. Not intended (but correct).

-- Workaround:

The only alternative implementation of this I can think of that works in the
general case (the array above, for example, could be externally sorted; this
would not work if the output array were to be sorted in relation to a
different column from "foo"), is something of the form:

select array_accum(bar) from
(select bar from foo order by bar) as pony

This is slow (~5 times slower on my real data) when the subquery will then
require a WHERE clause.

-- Summary

In summary, at least the error message is wrong, and I strongly believe that
the error condition itself is wrong.

--

gpg(at)goeswhere(dot)com: 0xA482EE24; fingerprint:
34F5 5032 D173 76AA 0412 6117 7835 5BD4 A482 EE24

[0] http://www.postgresql.org/docs/8.3/static/functions-aggregate.html

[1] http://www.postgresql.org/docs/8.3/static/xaggr.html