Received: from ciderbox.stanaway.net (ciderbox.stanaway.net [170.76.50.98]) by postgresql.org (Postfix) with ESMTP id 78A8A475AC9 for ; Mon, 22 Apr 2002 13:36:16 -0400 (EDT) Received: by ciderbox.stanaway.net (Postfix, from userid 1000) id E41A89354C; Mon, 22 Apr 2002 12:36:03 -0500 (CDT) Subject: Re: Is this a BUG? Is there anyone has the same problem? From: David Stanaway To: Tom Lane Cc: jack , pgsql-sql@postgresql.org In-Reply-To: <16398.1019493311@sss.pgh.pa.us> References: <001401c1e93a$00565f40$1400a8c0@jac> <5841.1019402026@sss.pgh.pa.us> <001001c1e9f1$ee085c40$1400a8c0@jac> <16398.1019493311@sss.pgh.pa.us> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-TA5PGphTl80cHISWDptP" X-Mailer: Ximian Evolution 1.0.3 Date: 22 Apr 2002 12:36:02 -0500 Message-Id: <1019496962.24241.36.camel@ciderbox> Mime-Version: 1.0 X-Archive-Number: 200204/268 X-Sequence-Number: 7532 --=-TA5PGphTl80cHISWDptP Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hi Tom. This seems to be a bit of a FAQ at the moment... Do you think it should be added to the release notes? Maybe the people that build the packages for different distributions of Linux need to have some kind of warning in the installation scripts which let the users pic the locale used for Postgres by default. Cheers... -- David Stanaway On Mon, 2002-04-22 at 11:35, Tom Lane wrote: > "jack" writes: > >> What locale is your database running in? >=20 > > My locale is en_AU (on Redhat 7.2) >=20 > Hmph. It seems to be a peculiarity of the locale sorting rules for > English. Using RedHat 7.2, I made a file containing 3 lines, the last > of which has one trailing blank: >=20 > [tgl@rh1 tgl]$ cat test > AAB > AA B > AAB >=20 > -- hmm, can't see the spaces very well, so do this: >=20 > [tgl@rh1 tgl]$ sed 's/ /_/g' test > AAB > AA_B > AAB_ >=20 > -- Now sort under Aussie rules: >=20 > [tgl@rh1 tgl]$ LANG=3Den_AU sort test > AAB > AA B > AAB >=20 > -- uh, let's try looking to see where the spaces are: >=20 > [tgl@rh1 tgl]$ LANG=3Den_AU sort test | sed 's/ /_/g' > AAB > AA_B > AAB_ >=20 > -- Not too consistent, eh? I get the same results with en_US though: >=20 > [tgl@rh1 tgl]$ LANG=3Den_US sort test | sed 's/ /_/g' > AAB > AA_B > AAB_ >=20 > -- but traditional "C" locale does this: >=20 > [tgl@rh1 tgl]$ LANG=3DC sort test | sed 's/ /_/g' > AA_B > AAB > AAB_ >=20 >=20 > The reason that your SQL tests reflect this is that comparisons for type > CHAR(n) remove any trailing blanks before comparing; but the result of > substr() is of type TEXT, so it assumes trailing blanks are significant. > So the data you were sorting were in the one case effectively >=20 > 'AA B' > 'AAB' > 'BB 123' > 'BB123' >=20 > and in the other case >=20 > 'AA B' > 'AAB ' > 'BB 1' > 'BB12' >=20 > and the locale sort rules treat 'AAB' differently from 'AAB '. >=20 > If you think that's a bug, you can take it up with whoever maintains > Linux's locale rules. It ain't our bug though. (You might prefer > to initdb under C locale if you'd rather sort according to C rules.) >=20 > regards, tom lane >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >=20 --=-TA5PGphTl80cHISWDptP Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iQEXAwUAPMRKAr6yIDFGeSAyFAIxKwQApfG9kCmcHdS3k5qu4H8K5QdiHVlJzevX b2Ofoo2cN1PhEQNlvIE9NjPqzgtA7HnvNXvqZKOI+25Ekrlf6qKjW00ZA2XHywij jtmTMjdE5O9mO29jpnN5G/iXEW1L2RsGaUO3zi5x9xBQh64ggLUt0PyjK1NVSOwL ZxqJpHCxDLUD/1AibslnSDQqYEHMtasi+vifnl/sutuyUIzZ0qsLYDXosdGMWnlK Ha1V883jlkm92cyjasPXZUG3xg166UY/Y6J9AHBU/zoaXftFJHA0rfFhsygs6V7q ASdmpbOawvuxQCt/IOU7xo04eeQpfDuib4EyF06pxlfFhzzy5CwNEsX4 =A0F0 -----END PGP SIGNATURE----- --=-TA5PGphTl80cHISWDptP--