Thread: BUG #6495: mis-sorting on string field consisting of space-padded numbers

The following bug has been logged on the website:

Bug reference:      6495
Logged by:          Lou Duchez
Email address:      lou@paprikash.com
PostgreSQL version: 9.1.2
Operating system:   Fedora 16, CentOS 5
Description:=20=20=20=20=20=20=20=20

Download the following file and feed it into psql:

http://www.paprikash.com/lou/missorttest.sql

Then run the command:

select * from missorttest order by hiercode;

Each "hiercode" entry contains concatenations of numbers, where each number
is left-padded to take exactly four characters.  PostgreSQL is sorting as if
the padding were on the right.  As a result, "   2  10" is coming before "=
=20=20
2   2", even though a literal sorting of the string should put "   2   2"
first (because chr 32 comes before chr 49).

More info: this error does not occur on the Windows build of PostgreSQL.=20
Also, I compile my own versions of PostgreSQL on Fedora and CentOS, taking
all the defaults.

Hello

2012/2/28  <lou@paprikash.com>:
> The following bug has been logged on the website:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A06495
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Lou Duchez
> Email address: =C2=A0 =C2=A0 =C2=A0lou@paprikash.com
> PostgreSQL version: 9.1.2
> Operating system: =C2=A0 Fedora 16, CentOS 5
> Description:
>
> Download the following file and feed it into psql:
>
> http://www.paprikash.com/lou/missorttest.sql
>
> Then run the command:
>
> select * from missorttest order by hiercode;
>
> Each "hiercode" entry contains concatenations of numbers, where each numb=
er
> is left-padded to take exactly four characters. =C2=A0PostgreSQL is sorti=
ng as if
> the padding were on the right. =C2=A0As a result, " =C2=A0 2 =C2=A010" is=
 coming before "
> 2 =C2=A0 2", even though a literal sorting of the string should put " =C2=
=A0 2 =C2=A0 2"
> first (because chr 32 comes before chr 49).
>
> More info: this error does not occur on the Windows build of PostgreSQL.
> Also, I compile my own versions of PostgreSQL on Fedora and CentOS, taking
> all the defaults.
>

This should not be bug - this behave depends on system libraries and
locales setting.

Please, check value of lc_collate variable

postgres=3D> show lc_collate;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

different collations should have different order.

Regards

Pavel Stehule

>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs