Postgres Bug - Aggregate with order by
Hello my name is Andrew,
I believe that I've found a bug within postgres where an aggregate function
is not accepted when an order by clause is specified, here is a short
example:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
postgres=# create table test_table(i int);
CREATE TABLE
postgres=# insert into test_table values (1);
INSERT 0 1
postgres=# select COUNT(i) from test_table;
count
-------
1
(1 row)
postgres=# select COUNT(i) from test_table order by i;
ERROR: column "test_table.i" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 1: select COUNT(i) from test_table order by i;
^
Andrew D <andrew.do1613@gmail.com> writes:
I believe that I've found a bug within postgres where an aggregate function
is not accepted when an order by clause is specified, here is a short
example:
postgres=# select COUNT(i) from test_table order by i;
ERROR: column "test_table.i" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 1: select COUNT(i) from test_table order by i;
^
This looks perfectly normal to me. The ORDER BY clause is
supposed to be performed after the aggregation step. But
there's no meaningful value of i to associate with the
aggregated rows (well, row, in this case).
It's not very clear what you were hoping to accomplish, but
if you intended to sort before the aggregation, the correct
way is to write
select COUNT(i order by i) from test_table;
(Ordering the input to COUNT() is pretty pointless, of course,
but there are other aggregates for which it's useful to do this.)
regards, tom lane