Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id C916AB5DBF9
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 22 Jun 2011 06:47:07 -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 83933-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 22 Jun 2011 09:47:01 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com
	[209.85.210.46])
	by mail.postgresql.org (Postfix) with ESMTP id 1553FB5D91F
	for <pgsql-sql@postgresql.org>; Wed, 22 Jun 2011 06:47:00 -0300 (ADT)
Received: by pzk9 with SMTP id 9so405484pzk.19
	for <pgsql-sql@postgresql.org>; Wed, 22 Jun 2011 02:47:00 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.68.43.228 with SMTP id z4mr190461pbl.297.1308735574486; Wed,
	22 Jun 2011 02:39:34 -0700 (PDT)
Received: by 10.68.55.167 with HTTP; Wed, 22 Jun 2011 02:39:34 -0700 (PDT)
X-Originating-IP: [66.215.95.51]
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>
Date: Wed, 22 Jun 2011 02:39:34 -0700
Message-ID: <BANLkTi=7Lqyj4LbRPHp1mLo_0-mFN5XPmA@mail.gmail.com>
Subject: Re: a strange order by behavior
From: Samuel Gendler <sgendler@ideasculptor.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Eyal Wilde <eyal@impactsoft.co.il>, pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=bcaec539603805b03c04a649bfa1
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/77
X-Sequence-Number: 35543

--bcaec539603805b03c04a649bfa1
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 hi=
s
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 resemblin=
g
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=3Ducs_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=3DC and get case-sensitive
sorting that treats spaces 'correctly,' but I have no idea how reliable tha=
t
is with multibyte characters and it almost certainly doesn't handle accente=
d
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=3D# 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 | =D0=90=D0=98=D0=92=D0=95=D0=A2
  2 | =D0=9F =D0=90=D0=98=D0=92=D0=95=D0=A2
  2 | =D0=9F =D0=A0=D0=98=D0=92=D0=95=D0=A2
  2 | =D0=9F =D0=A1=D0=98=D0=92=D0=95=D0=A2
  2 | =D0=9F=D0=A0=D0=98=D0=92=D0=95=D0=A2
  2 | =D0=A0=D0=98=D0=92=D0=95=D0=A2
  2 | =D0=A1=D0=98=D0=92=D0=95=D0=A2
  2 | =D0=BF=D1=80=D0=B8=D0=B2=D0=B5=D1=82

--bcaec539603805b03c04a649bfa1
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Interesting. =C2=A0The original thread to which I was referring has a subje=
ct of &quot;Sorting Issue&quot; and the original request showed a list of v=
ehicle model names which were sorting as though there were no spaces. The u=
ser had collation set to en_US.UTF-8. =C2=A0However, 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.<div>
<br></div><div>Then I just ran the exact same test on a Centos 5 linux host=
 (postgresql 8.4 as well) and I am seeing the &#39;erroneous&#39; sort orde=
r that prompted both this thread and the other. =C2=A0So you can&#39;t even=
 assume the same behaviour for the same collation on different platforms.</=
div>
<div><br></div><div>On OS X:</div><div><br></div><div># \l</div><div><div>=
=C2=A0=C2=A0 =C2=A0 =C2=A0 Name =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 Owner =
=C2=A0 =C2=A0| Encoding | =C2=A0Collation =C2=A0| =C2=A0 =C2=A0Ctype =C2=A0=
 =C2=A0| =C2=A0 Access privileges =C2=A0=C2=A0</div><div>------------------=
-+------------+----------+-------------+-------------+---------------------=
--</div>
<div>=C2=A0col_test =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| u1 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 | UTF8 =C2=A0 =C2=A0 | en_US.UTF-8 | en_US.UTF-8 |=C2=A0</div=
><div>=C2=A0</div></div><div><div># show lc_collate;</div><div>=C2=A0lc_col=
late =C2=A0</div><div>-------------</div><div>=C2=A0en_US.UTF-8</div></div>=
<div><br>
</div><div><div># select * from t1 order by f1;</div><div>=C2=A0id | =C2=A0=
 =C2=A0 =C2=A0 =C2=A0f1 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0</div><div>----+--=
-----------------</div><div>=C2=A0=C2=A01 | CX Hatchback</div><div>=C2=A0=
=C2=A02 | CX Minivan</div><div>=C2=A0=C2=A02 | CX Plus Minivan</div><div>=
=C2=A0=C2=A02 | CX Sedan</div>
<div>=C2=A0=C2=A02 | CX Sport Utility</div><div>=C2=A0=C2=A02 | CXL Minivan=
</div><div>=C2=A0=C2=A02 | CXL Premium Sedan</div><div>=C2=A0=C2=A02 | CXL =
Sedan</div><div>=C2=A0=C2=A02 | CXL Sport Utility</div><div>=C2=A0=C2=A02 |=
 CXL Turbo Sedan</div><div>=C2=A0=C2=A02 | CXS Sedan</div><div><br>
</div>On CentOS 5:</div><div><br></div><div># \l</div><div><div>=C2=A0=C2=
=A0 =C2=A0 =C2=A0 Name =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 Owner =C2=A0 =C2=
=A0| Encoding | =C2=A0Collation =C2=A0| =C2=A0 =C2=A0Ctype =C2=A0 =C2=A0| =
=C2=A0 Access privileges =C2=A0=C2=A0</div><div>-------------------+-------=
-----+----------+-------------+-------------+-----------------------</div>
<div>=C2=A0col_test =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| u1 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 | UTF8 =C2=A0 =C2=A0 | en_US.UTF-8 | en_US.UTF-8 |=C2=A0</div=
></div><div><br></div><div><div><div># show lc_collate</div><div>ecorithm-#=
 ;</div><div>=C2=A0lc_collate =C2=A0</div><div>-------------</div><div>=C2=
=A0en_US.UTF-8</div>
</div></div><div><br></div><div><div># select * from t1 order by f1;</div><=
div>=C2=A0id | =C2=A0 =C2=A0 =C2=A0 =C2=A0f1 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=
=A0</div><div>----+-------------------</div><div>=C2=A0=C2=A01 | CX Hatchba=
ck</div><div>=C2=A0=C2=A02 | CXL Minivan</div><div>=C2=A0=C2=A02 | CXL Prem=
ium Sedan</div>
<div>=C2=A0=C2=A02 | CXL Sedan</div><div>=C2=A0=C2=A02 | CXL Sport Utility<=
/div><div>=C2=A0=C2=A02 | CXL Turbo Sedan</div><div>=C2=A0=C2=A02 | CX Mini=
van</div><div>=C2=A0=C2=A02 | CX Plus Minivan</div><div>=C2=A0=C2=A02 | CX =
Sedan</div><div>=C2=A0=C2=A02 | CX Sport Utility</div><div>=C2=A0=C2=A02 | =
CXS Sedan</div>
<div><br></div><div>Further testing would seem to reveal that OS X is using=
 something resembling C collation order, despite the fact that it says en_U=
S.UTF-8. I say this because it is also case sensitive. =C2=A0CentOS, on the=
 other hand, is ignoring spaces, but is also case-insensitive.</div>
<div><br></div><div><div># select * from t1 order by f1 asc;</div><div>=C2=
=A0id | =C2=A0 =C2=A0 =C2=A0 =C2=A0f1 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0</di=
v><div>----+-------------------</div><div>=C2=A0=C2=A01 | CX Hatchback</div=
><div>=C2=A0=C2=A02 | CXL Minivan</div><div>=C2=A0=C2=A02 | CXL Premium Sed=
an</div>
<div>=C2=A0=C2=A02 | CXL Sedan</div><div>=C2=A0=C2=A02 | CXL Sport Utility<=
/div><div>=C2=A0=C2=A02 | CXL Turbo Sedan</div><div>=C2=A0=C2=A02 | CX Mini=
van</div><div>=C2=A0=C2=A02 | CX Plus Minivan</div><div>=C2=A0=C2=A02 | cx =
Sedan</div><div>=C2=A0=C2=A02 | CX Sedan</div><div>=C2=A0=C2=A02 | CX Sport=
 Utility</div>
<div>=C2=A0=C2=A02 | CXS Sedan</div></div><div><br></div><div><div>Pavel su=
ggested using a collation of ucs_basic, but I get an error when I try that =
on linux:</div></div><div><br></div><div><div>$ createdb -U u1 --lc-collate=
=3Ducs_basic -E UTF-8 test=C2=A0</div>
<div>createdb: database creation failed: ERROR: =C2=A0invalid locale name u=
cs_basic</div></div><div><br></div></div><div>I was able to create the db w=
ith --lc_collate=3DC and get case-sensitive sorting that treats spaces &#39=
;correctly,&#39; but I have no idea how reliable that is with multibyte cha=
racters and it almost certainly doesn&#39;t handle accented characters corr=
ectly 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 i=
s probably not how things are alphabetized in those languages.</div>
<div><br></div><div>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 char=
set is non-ascii, but it&#39;d be a lot of effort to construct a test that =
utilizes a mix of 1,2,3, and 4 byte characters, since I&#39;m not familiar =
with enough alphabets.</div>
<div><br></div><div><div>test=3D# select * from t1 order by f1;</div><div>=
=C2=A0id | =C2=A0 =C2=A0 =C2=A0 =C2=A0f1 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0<=
/div><div>----+-------------------</div><div>=C2=A0=C2=A01 | CX Hatchback</=
div><div>=C2=A0=C2=A02 | CX Minivan</div><div>=C2=A0=C2=A02 | CX Plus Miniv=
an</div>
<div>=C2=A0=C2=A02 | CX Sedan</div><div>=C2=A0=C2=A02 | CX Sport Utility</d=
iv><div>=C2=A0=C2=A02 | CXL Minivan</div><div>=C2=A0=C2=A02 | CXL Premium S=
edan</div><div>=C2=A0=C2=A02 | CXL Sedan</div><div>=C2=A0=C2=A02 | CXL Spor=
t Utility</div><div>=C2=A0=C2=A02 | CXL Turbo Sedan</div><div>=C2=A0=C2=A02=
 | CXS Sedan</div>
<div>=C2=A0=C2=A02 | cx Sedan</div><div>=C2=A0=C2=A02 | =D0=90=D0=98=D0=92=
=D0=95=D0=A2</div><div>=C2=A0=C2=A02 | =D0=9F =D0=90=D0=98=D0=92=D0=95=D0=
=A2</div><div>=C2=A0=C2=A02 | =D0=9F =D0=A0=D0=98=D0=92=D0=95=D0=A2</div><d=
iv>=C2=A0=C2=A02 | =D0=9F =D0=A1=D0=98=D0=92=D0=95=D0=A2</div><div>=C2=A0=
=C2=A02 | =D0=9F=D0=A0=D0=98=D0=92=D0=95=D0=A2</div><div>=C2=A0=C2=A02 | =
=D0=A0=D0=98=D0=92=D0=95=D0=A2</div><div>=C2=A0=C2=A02 | =D0=A1=D0=98=D0=92=
=D0=95=D0=A2</div><div>=C2=A0=C2=A02 | =D0=BF=D1=80=D0=B8=D0=B2=D0=B5=D1=82=
</div></div><div><br>
</div><div><br></div>

--bcaec539603805b03c04a649bfa1--