Re: unexpected (to me) sorting order

> From: Chris Mair <chris(at)1006(dot)org>

> To: Björn Lundin <b(dot)f(dot)lundin(at)gmail(dot)com>; pgsql-general(at)postgresql(dot)org
> Cc:
> Sent: Wednesday, 8 April 2015, 10:36
> Subject: Re: [GENERAL] unexpected (to me) sorting order
>
>
>> select * from T_SORT order by NAME ;
>>
>> rollback;
>> id | name
>> ----+--------------------
>> 1 | FINISH_110_150_1
>> 2 | FINISH_110_200_1
>> 3 | FINISH_1.10_20.0_3
>> 4 | FINISH_1.10_20.0_4
>> 5 | FINISH_1.10_30.0_3
>> 6 | FINISH_1.10_30.0_4
>> 7 | FINISH_120_150_1
>> 8 | FINISH_120_200_1
>> (8 rows)
>
> Hi,
>
> PostreSQL relies on the OS's C lib. So this kind
> of ordering problems depend on the OS' idea about
> collations.
>
> I get the exact same order on 9.4.1 running on Centos 7.1:
>
> chris=# select * from T_SORT order by NAME ;
> id | name
> ----+--------------------
> 1 | FINISH_110_150_1
> 2 | FINISH_110_200_1
> 3 | FINISH_1.10_20.0_3
> 4 | FINISH_1.10_20.0_4
> 5 | FINISH_1.10_30.0_3
> 6 | FINISH_1.10_30.0_4
> 7 | FINISH_120_150_1
> 8 | FINISH_120_200_1
> (8 rows)
>
> But I get this on 9.3.5 running on OS X 10.8
>
> chris=# select * from T_SORT order by NAME ;
> id | name
> ----+--------------------
> 3 | FINISH_1.10_20.0_3
> 4 | FINISH_1.10_20.0_4
> 5 | FINISH_1.10_30.0_3
> 6 | FINISH_1.10_30.0_4
> 1 | FINISH_110_150_1
> 2 | FINISH_110_200_1
> 7 | FINISH_120_150_1
> 8 | FINISH_120_200_1
>
> with both databases having Collate = en_US.UTF-8.
>
> If I put your data in a file and use the command sort
> from the shell I get the same effect (this is on
> the Centos 7.1 box):
>
> [chris(at)mercury ~]$ cat x
> FINISH_1.10_20.0_3
> FINISH_1.10_20.0_4
> FINISH_1.10_30.0_3
> FINISH_1.10_30.0_4
> FINISH_110_150_1
> FINISH_110_200_1
> FINISH_120_150_1
> FINISH_120_200_1
>
> [chris(at)mercury ~]$ sort x
>
> FINISH_110_150_1
> FINISH_110_200_1
> FINISH_1.10_20.0_3
> FINISH_1.10_20.0_4
> FINISH_1.10_30.0_3
> FINISH_1.10_30.0_4
> FINISH_120_150_1
> FINISH_120_200_1
> [chris(at)mercury ~]$
>
> I don't know what's the rationale behin this,
> but it looks like Linux ignores the . when doing the sort.
>
>

I think this is down to behaviour changes in glibc, there was a thread a while ago where somebody replicating via streaming rep between with different versions of glibc ended up in a bit of a pickle.

http://www.postgresql.org/message-id/BA6132ED-1F6B-4A0B-AC22-81278F5AB81E@tripadvisor.com