Thread: a strange order by behavior

Hello

a equalent of C collate for UTF8 is ucs_basic

Regards

Pavel Stehule

2011/6/22 Samuel Gendler <sgendler@ideasculptor.com>:
>
>
> On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde <eyal@impactsoft.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 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
>
>

Hello Peter


> Pavel suggested using a collation of ucs_basic, but I get an error when I
> try that on linux:
> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test
> createdb: database creation failed: ERROR:  invalid locale name ucs_basic

isn't this a bug in collations?

Regards

Pavel

> I was able to create the db with --lc_collate=C 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

> I'm actually surprised that european users aren't complaining about this all
> the time, but maybe european users are used to seeing things ordered in a
> manner which doesn't honour the 'correct' ordering of accented characters.
>  Actually, I wonder if the probable explanation of the lack of complaint is
> the fact that the kinds of fields most apps would tend to do alphabetical
> sorts on probably don't tend to have lots of punctuation other than spaces,
> so perhaps the language sensitive sorts are deemed sufficient because most
> people don't notice the funky behaviour with punctuation and whitespace
> while case-insensitive sort is probably desired most of the time.

I checked czech UTF8 collation and it is correct

postgres=# select * from x order by a collate ucs_basic;    a
-----------ChromečkaCrhaSemerádSynZárubaŠebíšek
(6 rows)

postgres=# select * from x order by a collate "cs_CZ";    a
-----------CrhaChromečkaSemerádSynŠebíšekZáruba
(6 rows)

Regards

Pavel Stehule

Samuel Gendler <sgendler@ideasculptor.com> writes:
> Interesting.  The original thread to which I was referring has a subject of
> "Sorting Issue" and the original request showed a list of vehicle model
> names which were sorting as though there were no spaces. The user had
> collation set to en_US.UTF-8.  However, my database (on OS X) sorts both his
> example and the example that started this thread correctly, despite my
> lc_collate being set to the same value.

OS X's support for comparisons in UTF-8 locales is widely understood
to be broken.  In general, as you've found out, there's little
compatibility in sort order across platforms; but OS X is just
completely unlike other modern Unixoid platforms.  I would not take it
as the definition of "correct".

AFAIK, if you're looking for strict character-by-character sorting
rather than those weird "dictionary" rules, C locale (a/k/a POSIX
locale) is your only choice, and you have to put up with the odd sort
order you will get for all non-ASCII characters.  In principle a locale
could be defined that does character-by-character but does something
reasonable with non-ASCII; but none of the major platforms seem to offer
one.

Also, just to be perfectly clear: this is not Postgres' fault, it's
just sorting the way strcoll() says to.  You'll get the same sort
order from the command-line sort(1) program, if you feed it the same
data in the same locale environment.
        regards, tom lane

On ons, 2011-06-22 at 01:43 -0700, Samuel Gendler wrote:
> I seem to recall a thread here about it ignoring spaces entirely in that
> collation (and maybe ignoring capitalization, too?).

The way it works is that every collating element (letter or other
character or character group that you sort as a unit) is assigned four
weights (primary, secondary, tertiary, and quaternary), and the sorting
then first compares the primary weights, then the secondary weights,
etc.  The primary weight typically indicates the overall sort order,
like A before B, the secondary weight has to do with diacritic marks,
the tertiary with letter case, and the fourth level is only used in
special cases.  So that's why it looks as though the capitalization is
"ignored" unless both the primary and secondary weights are the same.

> This worked:
> 
> createdb  -E UTF-8 --lc-collate=C some_db
> 
> 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.

At least the collate category of the en_US.UTF-8 locale on glibc is
unaltered from the ISO 14651 default ordering, which is equivalent to
the Unicode default ordering.  There several other locales for which
that is also the case.  Unfortunately, this is not exposed outside of
the glibc source code.  So you can't just select "give me a neutral
default ordering".

On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote:
> Pavel suggested using a collation of ucs_basic, but I get an error
> when I
> try that on linux:
> 
> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test
> createdb: database creation failed: ERROR:  invalid locale name ucs_basic 

ucs_basic is a collation name, which is an SQL object.  The argument of
createdb --lc-collate is an operating system locale name.  You can't mix
the two, even though they are similar.

2011/6/22 Peter Eisentraut <peter_e@gmx.net>:
> On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote:
>> Pavel suggested using a collation of ucs_basic, but I get an error
>> when I
>> try that on linux:
>>
>> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test
>> createdb: database creation failed: ERROR:  invalid locale name ucs_basic
>
> ucs_basic is a collation name, which is an SQL object.  The argument of
> createdb --lc-collate is an operating system locale name.  You can't mix
> the two, even though they are similar.
>

ok, what I can to select, when I would to use a C like default order?

Regards

Pavel

>
>

On tor, 2011-06-23 at 05:57 +0200, Pavel Stehule wrote:
> 2011/6/22 Peter Eisentraut <peter_e@gmx.net>:
> > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote:
> >> Pavel suggested using a collation of ucs_basic, but I get an error
> >> when I
> >> try that on linux:
> >>
> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test
> >> createdb: database creation failed: ERROR:  invalid locale name ucs_basic
> >
> > ucs_basic is a collation name, which is an SQL object.  The argument of
> > createdb --lc-collate is an operating system locale name.  You can't mix
> > the two, even though they are similar.
> >
> 
> ok, what I can to select, when I would to use a C like default order?

createdb --locale=C --encoding=UTF8

2011/6/23 Peter Eisentraut <peter_e@gmx.net>:
> On tor, 2011-06-23 at 05:57 +0200, Pavel Stehule wrote:
>> 2011/6/22 Peter Eisentraut <peter_e@gmx.net>:
>> > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote:
>> >> Pavel suggested using a collation of ucs_basic, but I get an error
>> >> when I
>> >> try that on linux:
>> >>
>> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test
>> >> createdb: database creation failed: ERROR:  invalid locale name ucs_basic
>> >
>> > ucs_basic is a collation name, which is an SQL object.  The argument of
>> > createdb --lc-collate is an operating system locale name.  You can't mix
>> > the two, even though they are similar.
>> >
>>
>> ok, what I can to select, when I would to use a C like default order?
>
> createdb --locale=C --encoding=UTF8
>

ok, thank you

Pavel

>
>