pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres Bug - Aggregate with order by
2+ messages / 2 participants
[nested] [flat]
* Postgres Bug - Aggregate with order by
@ 2022-03-31 20:04 Andrew D <andrew.do1613@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Andrew D @ 2022-03-31 20:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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;
^
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgres Bug - Aggregate with order by
@ 2022-03-31 20:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew D <andrew.do1613@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-03-31 20:34 UTC (permalink / raw)
To: Andrew D <andrew.do1613@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-03-31 20:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-31 20:04 Postgres Bug - Aggregate with order by Andrew D <andrew.do1613@gmail.com>
2022-03-31 20:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox