Thread: BUG #5563: Odd behavior with aggregate_func(DISTINCT foo ORDER BY foo)

The following bug has been logged online:

Bug reference:      5563
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:

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

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::TEXT ORDER BY t::TEXT) FROM foo;

On Fri, Jul 16, 2010 at 18:04, Daniel Grace <dgrace@wingsnw.com> wrote:
> However, in some circumstances Postgres will fail

How exactly?

this is what I get:
=> SELECT STRING_AGG(DISTINCT t::text order by t::text) FROM foo;
 string_agg
------------
 abc
(1 row)

=> SELECT STRING_AGG(DISTINCT t::text order by t::text desc) FROM foo;
 string_agg
------------
 cba
(1 row)

Maybe its so obvious I missed it?

On Fri, Jul 23, 2010 at 10:42 AM, Alex Hunsaker <badalex@gmail.com> wrote:
> On Fri, Jul 16, 2010 at 18:04, Daniel Grace <dgrace@wingsnw.com> wrote:
>> However, in some circumstances Postgres will fail
>
> How exactly?
>
> Maybe its so obvious I missed it?
>

Please see BUG #5564 -- I accidentally submitted this one before I was
finished typing the details.

-- Daniel

On Fri, Jul 23, 2010 at 12:19, Daniel Grace <dgrace@wingsnw.com> wrote:
> Please see BUG #5564 -- I accidentally submitted this one before I was
> finished typing the details.

Ahh, sorry for the noise.  I was just trolling for bug reports that
had no replys yet :-)