From: Samuel Gendler <sgendler@ideasculptor.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Eyal Wilde <eyal@impactsoft.co.il>
Cc: pgsql-sql@postgresql.org
Subject: Re: a strange order by behavior
Date: Wed, 22 Jun 2011 02:39:34 -0700
Message-ID: <BANLkTi=7Lqyj4LbRPHp1mLo_0-mFN5XPmA@mail.gmail.com> (raw)
In-Reply-To: <BANLkTim9sFLt3e2+auF_oTr68UETiW83VA@mail.gmail.com>
References: <BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com>
<BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
<BANLkTim9sFLt3e2+auF_oTr68UETiW83VA@mail.gmail.com>
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.
Then I just ran the exact same test on a Centos 5 linux host (postgresql 8.4
as well) and I am seeing the 'erroneous' sort order that prompted both this
thread and the other. So you can't even assume the same behaviour for the
same collation on different platforms.
On OS X:
# \l
Name | Owner | Encoding | Collation | Ctype |
Access privileges
-------------------+------------+----------+-------------+-------------+-----------------------
col_test | u1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
# show lc_collate;
lc_collate
-------------
en_US.UTF-8
# select * from t1 order by f1;
id | f1
----+-------------------
1 | CX Hatchback
2 | CX Minivan
2 | CX Plus Minivan
2 | CX Sedan
2 | CX Sport Utility
2 | CXL Minivan
2 | CXL Premium Sedan
2 | CXL Sedan
2 | CXL Sport Utility
2 | CXL Turbo Sedan
2 | CXS Sedan
On CentOS 5:
# \l
Name | Owner | Encoding | Collation | Ctype |
Access privileges
-------------------+------------+----------+-------------+-------------+-----------------------
col_test | u1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
# show lc_collate
ecorithm-# ;
lc_collate
-------------
en_US.UTF-8
# select * from t1 order by f1;
id | f1
----+-------------------
1 | CX Hatchback
2 | CXL Minivan
2 | CXL Premium Sedan
2 | CXL Sedan
2 | CXL Sport Utility
2 | CXL Turbo Sedan
2 | CX Minivan
2 | CX Plus Minivan
2 | CX Sedan
2 | CX Sport Utility
2 | CXS Sedan
Further testing would seem to reveal that OS X is using something resembling
C collation order, despite the fact that it says en_US.UTF-8. I say this
because it is also case sensitive. CentOS, on the other hand, is ignoring
spaces, but is also case-insensitive.
# select * from t1 order by f1 asc;
id | f1
----+-------------------
1 | CX Hatchback
2 | CXL Minivan
2 | CXL Premium Sedan
2 | CXL Sedan
2 | CXL Sport Utility
2 | CXL Turbo Sedan
2 | CX Minivan
2 | CX Plus Minivan
2 | cx Sedan
2 | CX Sedan
2 | CX Sport Utility
2 | CXS Sedan
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
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
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.
I happen to have my computer set so I can type cyrillic and it appears to
work correctly with non-ascii chars when the entire charset is non-ascii,
but it'd be a lot of effort to construct a test that utilizes a mix of
1,2,3, and 4 byte characters, since I'm not familiar with enough alphabets.
test=# select * from t1 order by f1;
id | f1
----+-------------------
1 | CX Hatchback
2 | CX Minivan
2 | CX Plus Minivan
2 | CX Sedan
2 | CX Sport Utility
2 | CXL Minivan
2 | CXL Premium Sedan
2 | CXL Sedan
2 | CXL Sport Utility
2 | CXL Turbo Sedan
2 | CXS Sedan
2 | cx Sedan
2 | АИВЕТ
2 | П АИВЕТ
2 | П РИВЕТ
2 | П СИВЕТ
2 | ПРИВЕТ
2 | РИВЕТ
2 | СИВЕТ
2 | привет
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sgendler@ideasculptor.com, pavel.stehule@gmail.com, eyal@impactsoft.co.il
Subject: Re: a strange order by behavior
In-Reply-To: <BANLkTi=7Lqyj4LbRPHp1mLo_0-mFN5XPmA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox