Skip site navigation (1) Skip section navigation (2)

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

From: Richard Neill <rn214(at)hermes(dot)cam(dot)ac(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: rn214(at)cam(dot)ac(dot)uk, PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #1082: Order by doesn't sort correctly.
Date: 2004-02-23 01:25:06
Message-ID: 40395672.2070103@hermes.cam.ac.uk (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)hermes(dot)cam(dot)ac(dot)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(at)g3 tgl]$ cat zzz
> Cymbal #1
> Cymbal - 18 inch
> Cymbal #2
> [tgl(at)g3 tgl]$ LC_ALL=C sort zzz
> Cymbal #1
> Cymbal #2
> Cymbal - 18 inch
> [tgl(at)g3 tgl]$ LC_ALL=en_GB sort zzz
> Cymbal #1
> Cymbal - 18 inch
> Cymbal #2
> [tgl(at)g3 tgl]$
> 
> 			regards, tom lane
> 

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

In response to

pgsql-bugs by date

Next:From: Michael MeskesDate: 2004-02-23 18:15:29
Subject: Re: ecpg mapping struct members for indicators incorrectly?
Previous:From: Tom LaneDate: 2004-02-22 23:41:40
Subject: Re: [HACKERS] Mac OS X, PostgreSQL, PL/Tcl

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group