BUG #1082: Order by doesn't sort correctly.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:

Description: Order by doesn't sort correctly.

It almost certainly is the correct sort order according to the locale
you're using. Use pg_controldata to check the database locale. You'll
probably want to re-initdb in C locale. Most non-C locales have weird
rules that try to approximate dictionary sort order.

regards, tom lane

Tom Lane wrote:

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:

Description: Order by doesn't sort correctly.

It almost certainly is the correct sort order according to the locale
you're using. Use pg_controldata to check the database locale. You'll
probably want to re-initdb in C locale. Most non-C locales have weird
rules that try to approximate dictionary sort order.

regards, tom lane

Dear Tom,

Thanks for your email. I did check pg_controldata and found:
LC_COLLATE: en_GB
LC_CTYPE: en_GB

The bug isn't the particular ascii-betical (or other) order.
But what I am getting as a supposedly ordered list includes:

Cymbal #1
Cymbal - 18 inch
Cymbal #2

This ordering is perverse! No matter what the priority is of the
different characters, I cannot understand how the above can arise.
Whether '#' comes before or after '-', '#1' and '#2' should be adjacent.

Richard

Richard Neill <rn214@hermes.cam.ac.uk> writes:

This ordering is perverse!

No kidding.

No matter what the priority is of the
different characters, I cannot understand how the above can arise.

You are assuming that it's a byte-at-a-time process. It's not. I
believe the first pass considers only letters and digits.

You can easily prove to yourself that it's not just Postgres. Here's
an example on my Linux laptop:

[tgl@g3 tgl]$ cat zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[tgl@g3 tgl]$ LC_ALL=C sort zzz
Cymbal #1
Cymbal #2
Cymbal - 18 inch
[tgl@g3 tgl]$ LC_ALL=en_GB sort zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[tgl@g3 tgl]$

regards, tom lane

Dear Tom,

Thank you for your explanation. It's very helpful, although I was
extremely surprised! I agree, it's not a postgresql bug.

Can I suggest it might be worth a mention on the "Order By" part of the
documentation.

i.e. this page:
http://www.postgresql.org/docs/7.3/static/sql-select.html#SQL-ORDERBY

could possibly use a little more emphasis of this last paragraph:
Data of character types is sorted according to the locale-specific
collation order that was established when the database cluster was
initialized.

or perhaps a link to here:
http://www.postgresql.org/docs/7.3/static/charset.html#AEN21582

I did realise that the sort would be locale dependent, but failed to
realise it wasn't byte-at-a-time.

Best wishes

Richard

Tom Lane wrote:

Richard Neill <rn214@hermes.cam.ac.uk> writes:

This ordering is perverse!

No kidding.

No matter what the priority is of the
different characters, I cannot understand how the above can arise.

You are assuming that it's a byte-at-a-time process. It's not. I
believe the first pass considers only letters and digits.

You can easily prove to yourself that it's not just Postgres. Here's
an example on my Linux laptop:

[tgl@g3 tgl]$ cat zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[tgl@g3 tgl]$ LC_ALL=C sort zzz
Cymbal #1
Cymbal #2
Cymbal - 18 inch
[tgl@g3 tgl]$ LC_ALL=en_GB sort zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[tgl@g3 tgl]$

regards, tom lane

--
rn214@hermes.cam.ac.uk ** http://www.richardneill.org
Richard Neill, Trinity College, Cambridge, CB21TQ, U.K.

Tom Lane wrote:

Richard Neill <rn214@hermes.cam.ac.uk> writes:

This ordering is perverse!

No kidding.

No matter what the priority is of the
different characters, I cannot understand how the above can arise.

You are assuming that it's a byte-at-a-time process. It's not. I
believe the first pass considers only letters and digits.

You can easily prove to yourself that it's not just Postgres. Here's
an example on my Linux laptop:

[tgl@g3 tgl]$ cat zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[tgl@g3 tgl]$ LC_ALL=C sort zzz
Cymbal #1
Cymbal #2
Cymbal - 18 inch
[tgl@g3 tgl]$ LC_ALL=en_GB sort zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[tgl@g3 tgl]$

I verified this, and it's not GB specific as one might suggest... Same
with en_US, de_DE, fr_FR, af_ZA. Does this behaviour really make sense
to anybody?

Regards,
Andreas

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Tue, 24 Feb 2004, Andreas Pflug wrote:

<snip>

I verified this, and it's not GB specific as one might suggest... Same
with en_US, de_DE, fr_FR, af_ZA. Does this behaviour really make sense
to anybody?

Ummm, same with tr_TR (for a long time).

We digged glibc and found out that tr_TR locale is broken there. Trying to
fix it. Maybe in the next release...

Regards,
- --
Devrim GUNDUZ
devrim@gunduz.org devrim.gunduz@linux.org.tr
http://www.TDMSoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFAO1Dctl86P3SPfQ4RAotmAJwJTUGRtYezYQDByGJ4f04+bJv3wgCgpz08
TxjQCMoYTYogCBSPYkjppLs=
=YyRq
-----END PGP SIGNATURE-----

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I verified this, and it's not GB specific as one might suggest... Same
with en_US, de_DE, fr_FR, af_ZA.

Yeah, most of the locales use dictionary ordering rules.

Does this behaviour really make sense to anybody?

You'd have to argue about it with the people who work on locales.
AFAIK it is a standard of some kind.

regards, tom lane