Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 98104B5DBC5 for ; Wed, 22 Jun 2011 05:43:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 73916-01 for ; Wed, 22 Jun 2011 08:43:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com [74.125.83.174]) by mail.postgresql.org (Postfix) with ESMTP id 904B4B5D94C for ; Wed, 22 Jun 2011 05:43:21 -0300 (ADT) Received: by pvg12 with SMTP id 12so368073pvg.19 for ; Wed, 22 Jun 2011 01:43:20 -0700 (PDT) MIME-Version: 1.0 Received: by 10.68.8.136 with SMTP id r8mr163340pba.498.1308732200771; Wed, 22 Jun 2011 01:43:20 -0700 (PDT) Received: by 10.68.55.167 with HTTP; Wed, 22 Jun 2011 01:43:20 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: References: Date: Wed, 22 Jun 2011 01:43:20 -0700 Message-ID: Subject: Re: a strange order by behavior From: Samuel Gendler To: Eyal Wilde Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=bcaec5215aafeee0d704a648f5b9 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/75 X-Sequence-Number: 35541 --bcaec5215aafeee0d704a648f5b9 Content-Type: text/plain; charset=ISO-8859-1 On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde wrote: > the database collation is: en_US.UTF-8 > > drop table t1; > create table t1 (recid int ,f1 varchar(20)); > insert into t1 values (1,'a'); > insert into t1 values (2,' '); > insert into t1 values (3,'aa'); > insert into t1 values (4,' a'); > select * from t1 order by f1 > > result: > > recid f1 > 2 " " > 1 "a" -- 2 comes before 1 because space is smaller then 'a'. > fine. > 4 " a" -- now is see that 1 comes before 4 because space is > greater then 'a' !? > 3 "aa" -- now again, 4 comes before 3 because space is smaller > the 'a' !?! > > I seem to recall a thread here about it ignoring spaces entirely in that collation (and maybe ignoring capitalization, too?). I went to go test that assertion by initializing a database with C collation and got some complaint about it being incompatible with my template1 template database. I initialized a db off of template0 and then got side tracked and you've only just reminded me of it. I was planning to test whether it is safe to use UTF-8 for encoding but use C collation, and then maybe investigate other collations. This worked: createdb -E UTF-8 --lc-collate=C some_db so it should be easy enough to play around with it some. I'm not sure how to get a list of valid collations for any given charset, and it seems like C collation would generate somewhat strange results with non-ascii characters (french accented characters are supposed to alphabetize in some unexpected manner, I believe), so there must be something better - closer to UTF-8 collation but without ignoring whitespace and such. A quick google search reveals that there is some kind of standard for unicode collation ( http://www.unicode.org/reports/tr10/ ) and I have no idea if that is what is represented by the en_US.UTF-8 collation or not. I've got no real experience with this stuff. It appears that there are differences regarding collation in recent versions - the beta docs for 9.1 show that you can set collation on individual operations or differently for different columns ( http://www.postgresql.org/docs/9.1/static/collation.html ). I see nothing like that in 8.4 docs. It definitely looks like we both need to have a read of the localization chapter of the docs for our database version as there is a bunch of stuff in there that I was surprised to read when I just did a quick scan - like using anything but C or posix is much slower and can produce incorrect results in a 'like' query It looks like the docs prior to 9.1beta have no real reference to collation at all, so it's down to trial and error unless someone in the know speaks up. --sam --bcaec5215aafeee0d704a648f5b9 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wi= lde <eyal@imp= actsoft.co.il> wrote:
the database collation is: en_US.UTF-8

=
drop table t1;
create table t1 (recid int ,f1 varchar(= 20));
insert into t1 values (1,'a');
insert int= o t1 values (2,' ');
insert into t1 values (3,'aa');
insert into t1 value= s (4,' a');
select * from t1 order by f1

result:

recid =A0f1
2 =A0 =A0 = =A0" " =A0 =A0 =A0 =A0
1 =A0 =A0 =A0"a" =A0 =A0 =A0 =A0-- 2 comes before 1 because = space is smaller then 'a'. fine.
4 =A0 =A0 =A0" a&qu= ot; =A0 =A0 =A0 -- now is see that 1 comes before 4 because space is greate= r then 'a' !?
3 =A0 =A0 =A0"aa" =A0 =A0 =A0 -- now again, 4 comes before 3= because space is smaller the 'a' !?!


I seem to recall a thread here about it ignor= ing spaces entirely in that collation (and maybe ignoring capitalization, t= oo?). =A0I went to go test that assertion by initializing a database with C= collation and got some complaint about it being incompatible with my templ= ate1 template database. =A0I initialized a db off of template0 and then got= side tracked and you've only just reminded me of it. =A0I was planning= to test whether it is safe to use UTF-8 for encoding but use C collation, = and then maybe investigate other collations.

This worked:

createdb =A0-E UT= F-8 --lc-collate=3DC some_db

so it should be easy = enough to play around with it some. =A0I'm not sure how to get a list o= f valid collations for any given charset, and it seems like C collation wou= ld generate somewhat strange results with non-ascii characters (french acce= nted characters are supposed to alphabetize in some unexpected manner, I be= lieve), so there must be something better - closer to UTF-8 collation but w= ithout ignoring whitespace and such. =A0A quick google search reveals that = there is some kind of standard for unicode collation (http://www.unicode.org/reports/tr10/ ) and = I have no idea if that is what is represented by the en_US.UTF-8 collation = or not. =A0I've got no real experience with this stuff.

It appears that there are differences regarding collati= on in recent versions - the beta docs for 9.1 show that you can set collati= on on individual operations or differently for different columns (http://www.po= stgresql.org/docs/9.1/static/collation.html ). =A0I see nothing like th= at in 8.4 docs.

It definitely looks like we both need to have a read of= the localization chapter of the docs for our database version as there is = a bunch of stuff in there that I was surprised to read when I just did a qu= ick scan - like using anything but C or posix is much slower and can produc= e incorrect results in a 'like' query

It looks like the docs prior to 9.1beta have no real re= ference to collation at all, so it's down to trial and error unless som= eone in the know speaks up.

--sam


--bcaec5215aafeee0d704a648f5b9--