Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id DC0E7B5DBC9 for ; Wed, 22 Jun 2011 07:15:26 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 15433-05 for ; Wed, 22 Jun 2011 10:15:20 +0000 (UTC) X-Greylist: delayed 00:35:45.009182 by SQLgrey-1.7.6 Received: from mail-pw0-f46.google.com (mail-pw0-f46.google.com [209.85.160.46]) by mail.postgresql.org (Postfix) with ESMTP id 298F7B5DBC5 for ; Wed, 22 Jun 2011 07:15:19 -0300 (ADT) Received: by pwj7 with SMTP id 7so499699pwj.19 for ; Wed, 22 Jun 2011 03:15:19 -0700 (PDT) MIME-Version: 1.0 Received: by 10.68.43.228 with SMTP id z4mr203561pbl.297.1308737719556; Wed, 22 Jun 2011 03:15:19 -0700 (PDT) Received: by 10.68.55.167 with HTTP; Wed, 22 Jun 2011 03:15:19 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: References: Date: Wed, 22 Jun 2011 03:15:19 -0700 Message-ID: Subject: Re: a strange order by behavior From: Samuel Gendler To: Pavel Stehule Cc: Eyal Wilde , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=bcaec5396038e0db4404a64a3e19 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201106/79 X-Sequence-Number: 35545 --bcaec5396038e0db4404a64a3e19 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On Wed, Jun 22, 2011 at 2:39 AM, Samuel Gendler wrote: > > I was able to create the db with --lc_collate=3DC and get case-sensitive > sorting that treats spaces 'correctly,' but I have no idea how reliable t= hat > is with multibyte characters and it almost certainly doesn't handle accen= ted > characters correctly in languages that have a mix of ascii and non-ascii > characters, since the non-ascii chars will all sort as greater than the > ascii chars, which is probably not how things are alphabetized in those > languages. > > 'locale -a' at a command prompt will list all locale's available on a host. I guess there is no universal utf-8 collation, which isn't that surprising, given how utf-8 works, though I had hoped that there might be one that at least attempts to handle all languages that share character codes in some kind of sensible manner. Perhaps the details of UTF-8 encoding make that effectively impossible. But it looks as though if you want to handle accented characters, you would need to set collation to something like fr_FR.UTF-8, though it looks like all of the UTF-8 collations ignore spaces and are case-insensitive. I don't know of a way to determine what the sorting behaviour of an arbitrary collation might be other than experimentation. You can set LC_ALL to the locale in question and pipe a test file through the sort command to do a quick test: LC_ALL=3Den_US.UTF-8 sort < test.txt I checked variants like .UTF-8, .iso88591, .iso885915@euro and they all ignore spaces in all of the languages I checked. It appears that the only way to get a space-sensitive sort order is to use C collation. Same goes for capitalization, I think. Which is pretty ugly, if you ask me. It looks like it is theoretically possible to modify a locale's collation behaviour. On my linux system, all locale's with the space-ignoring, capital-ignoring collation have this entry in the locale definitions in /usr/share/i18n/locales: LC_COLLATE % Copy the template from ISO/IEC 14651 copy "iso14651_t1" END LC_COLLATE Looking at the iso14651_t1 file, I see this: # Any character not precisely specified will be considered as a special # character and considered only at the last level. # ...... IGNORE;IGNORE;IGNORE;...... # # SYMB. N=B0 GLY # IGNORE;IGNORE;IGNORE; # 32 IGNORE;IGNORE;IGNORE; # 33 _ IGNORE;IGNORE;IGNORE; # 34 <"_> IGNORE;IGNORE;IGNORE; # 35 - (MACRON) IGNORE;IGNORE;IGNORE; # 36 IGNORE;IGNORE;IGNORE; # 37 - IGNORE;IGNORE;IGNORE; # 38 , IGNORE;IGNORE;IGNORE; # 39 ; IGNORE;IGNORE;IGNORE; # 40 : There doesn't seem to be any obvious discussion of how to structure the LC_COLLATE section of a locale definition, but it is clear that telling it to ignore spaces and punctuation is the problem we are seeing. That seems like a very strange choice to make as the default for all UTF-8 locales. I can't find the locale definitions for OS X, which might reveal a more sensible LC_COLLATE or may just be defaulting back to C collation for everything. --bcaec5396038e0db4404a64a3e19 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Jun 22, 2011 at 2:39 AM, Samuel = Gendler <= sgendler@ideasculptor.com> wrote:

I was able to create the db with --lc_collat= e=3DC and get case-sensitive sorting that treats spaces 'correctly,'= ; but I have no idea how reliable that is with multibyte characters and it = almost certainly doesn't handle accented characters correctly in langua= ges that have a mix of ascii and non-ascii characters, since the non-ascii = chars will all sort as greater than the ascii chars, which is probably not = how things are alphabetized in those languages.


'locale -a' at a co= mmand prompt will list all locale's available on a host. I guess there = is no universal utf-8 collation, which isn't that surprising, given how= utf-8 works, though I had hoped that there might be one that at least atte= mpts to handle all languages that share character codes in some kind of sen= sible manner. =A0Perhaps the details of UTF-8 encoding make that effectivel= y impossible. =A0But it looks as though if you want to handle accented char= acters, you would need to set collation to something like fr_FR.UTF-8, thou= gh it looks like all of the UTF-8 collations ignore spaces and are case-ins= ensitive. =A0I don't know of a way to determine what the sorting behavi= our of an arbitrary collation might be other than experimentation. =A0You c= an set LC_ALL to the locale in question and pipe a test file through the so= rt command to do a quick test:

LC_A=
LL=3Den_US.UTF-8 sort < test.txt



I checked=
 variants like .UTF-8, .iso88591, .iso885915@euro and they all ignore space=
s in all of the languages I checked.  It appears that the only way to get a=
 space-sensitive sort order is to use C collation.  Same goes for capitaliz=
ation, I think.  Which is pretty ugly, if you ask me.
It looks like it is theoretically poss=
ible to modify a locale's collation behaviour. =A0On my linux system, a=
ll locale's with the space-ignoring, capital-ignoring collation have th=
is entry in the locale definitions in=A0/usr/share/i18n/locales:
LC_COLLATE=A0
=A0% Copy the templa=
te from ISO/IEC 14651=A0
copy "i=
so14651_t1"=A0
=A0END LC_COLLATE

Looking at the iso14651_t1 file, I s=
ee this:
# Any character not precisely specified will be considered as a =
special


# character and considered only at t=
he last level.
# <U0000>.=
.....<U7FFFFFFF> IGNORE;IGNORE;IGNORE;<U0000>......<U7FFFFFF=
F>


#
# SYMB. =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0N=B0 GLY
#
<U0020> IGNORE;IGNORE;IGNORE;<U0020=
> # 32 <SP>


<U005F> IGNORE;IGNORE;IGNORE;&=
lt;U005F> # 33 _
<U0332&g=
t; IGNORE;IGNORE;IGNORE;<U0332> # 34 <"_>
<U00AF> IGNORE;IGNORE;IGNORE;<U00AF> # 35 - (MACRON)
<U00AD> IGNORE;IGNORE;IGNORE;<U0=
0AD> # 36 <SHY>
<U0=
02D> IGNORE;IGNORE;IGNORE;<U002D> # 37 -


<U002C> IGNORE;IGNORE;IGNORE;&=
lt;U002C> # 38 ,
<U003B&g=
t; IGNORE;IGNORE;IGNORE;<U003B> # 39 ;
<U003A> IGNORE;IGNORE;IGNORE;<U003A> # 40 :
=A0

There doesn't seem t=
o be any obvious discussion of how to structure the LC_COLLATE section of a=
 locale definition, but it is clear that telling it to ignore spaces and pu=
nctuation is the problem we are seeing. =A0That seems like a very strange c=
hoice to make as the default for all UTF-8 locales. =A0I can't find the=
 locale definitions for OS X, which might reveal a more sensible LC_COLLATE=
 or may just be defaulting back to C collation for everything.

--bcaec5396038e0db4404a64a3e19--