The following bug has been logged online:
Bug reference: 5564
Logged by: Daniel Grace
Email address: dgrace@wingsnw.com
PostgreSQL version: 9.0beta3
Operating system: Windows XP 32-bit
Description: Odd behavior with aggregate_func(DISTINCT foo ORDER BY
foo)
Details:
(Apologies if there's a duplicate, I may have accidentally submitted too
early. Tab+spacebar is a bad combination on browsers)
The manual states:
"If DISTINCT is specified in addition to an order_by_clause, then all the
ORDER BY expressions must match regular arguments of the aggregate; that is,
you cannot sort on an expression that is not included in the DISTINCT list.
"
However, in some circumstances Postgres will fail with "in an aggregate
with DISTINCT, ORDER BY expressions must appear in argument list" when the
same column is named in both places. It appears to be related to cases when
the aggregate function in question requires implicit typecasts:
This test case fails with the above error:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
t VARCHAR
);
INSERT INTO foo (t) VALUES ('a'), ('a'), ('b'), ('b'), ('c');
SELECT STRING_AGG(DISTINCT t ORDER BY t) FROM foo;
However, if t is cast to text in both halves of the aggregate function, it
works correctly:
SELECT STRING_AGG(DISTINCT t::TEXT ORDER BY t::TEXT) FROM foo;
It also works correctly if t is defined as TEXT instead of VARCHAR in the
table definition.
Note that if t is typecast in the ORDER BY but not the DISTINCT part, the
statement still fails (even though STRING_AGG implicitly casts t to text)
