Re: Error in sorting strings

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-bugs(at)postgresql(dot)org>,<edoardo(at)serra(dot)to(dot)it>
Subject: Re: Error in sorting strings
Date: 2010-09-20 01:19:05
Message-ID: 4C967039020000250003598F@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Edoardo Serra wrote:

> SELECT * FROM dummy ORDER BY string ASC;
> string
> --------
> 100%
> 10.1
> (2 rows)
>
> I would expect the order of the results to be '100%', '10.1'.

> Moreover, if I use the following dataset, I get the correct sorting
>
> SELECT string, ascii(substr(string, 3, 1)) FROM dummy ORDER BY
> string ASC;
> string | ascii
> --------+-------
> 10. | 46
> 100 | 48

You left out the most important piece of information -- what
collation have you configured? Many common collations ignore special
characters (except perhaps as tie-breakers), so your examples sort
based on:

'100'
'101'

versus

'10'
'100'

Both examples look right if you are using such a collation.

-Kevin

Browse pgsql-bugs by date

  From Date Subject
Next Message simon 2010-09-20 05:06:06 BUG #5664: index "idx000_mytable19" contains unexpected zero page
Previous Message Edoardo Serra 2010-09-20 00:35:35 Error in sorting strings