Examples Re: Bug Fix: COLLATE with multiple ORDER BYs in aggregates

On Tue, Apr 23, 2013 at 09:57:27AM -0700, David Fetter wrote:
> Folks,
> 
> While testing the upcoming FILTER clause for aggregates, Erik Rijkers
> uncovered a long-standing bug in $subject, namely that this case
> wasn't handled.  Please find attached a patch by Andrew Gierth and
> myself which fixes this issue and adds a regression test to ensure it
> remains fixed.

Please see below results when I run the regression test query on git
master, REL_9_2_STABLE, and REL9_1_STABLE, respectively:

$ psql postgres
psql (9.3devel)
Type "help" for help.

shackle@postgres:5493=# SELECT array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX") FROM (VALUES ('foo','bar')) v(a,b);
ERROR:  collation mismatch between explicit collations "C" and "POSIX"
LINE 1: SELECT array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX") F...
^

$ psql postgres
psql (9.2.4)
Type "help" for help.

shackle@postgres:5492=# SELECT array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX") FROM (VALUES ('foo','bar')) v(a,b);
ERROR:  collation mismatch between explicit collations "C" and "POSIX"
LINE 1: SELECT array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX") F...
^

$ psql postgres
psql (9.1.9)
Type "help" for help.

shackle@postgres:5491=# SELECT array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX") FROM (VALUES ('foo','bar')) v(a,b);
ERROR:  collation mismatch between explicit collations "C" and "POSIX"
LINE 1: SELECT array_agg(a COLLATE "C" ORDER BY b COLLATE "POSIX") F...
^
Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate