From: Tom Lane <tgl@sss.pgh.pa.us>
To: jack <datactrl@tpg.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Is this a BUG? Is there anyone has the same problem?
Date: Mon, 22 Apr 2002 12:35:11 -0400
Message-ID: <16398.1019493311@sss.pgh.pa.us> (raw)
In-Reply-To: <001001c1e9f1$ee085c40$1400a8c0@jac>
References: <001401c1e93a$00565f40$1400a8c0@jac>
<5841.1019402026@sss.pgh.pa.us>
<001001c1e9f1$ee085c40$1400a8c0@jac>
"jack" <datactrl@tpg.com.au> writes:
>> What locale is your database running in?
> My locale is en_AU (on Redhat 7.2)
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:
[tgl@rh1 tgl]$ cat test
AAB
AA B
AAB
-- hmm, can't see the spaces very well, so do this:
[tgl@rh1 tgl]$ sed 's/ /_/g' test
AAB
AA_B
AAB_
-- Now sort under Aussie rules:
[tgl@rh1 tgl]$ LANG=en_AU sort test
AAB
AA B
AAB
-- uh, let's try looking to see where the spaces are:
[tgl@rh1 tgl]$ LANG=en_AU sort test | sed 's/ /_/g'
AAB
AA_B
AAB_
-- Not too consistent, eh? I get the same results with en_US though:
[tgl@rh1 tgl]$ LANG=en_US sort test | sed 's/ /_/g'
AAB
AA_B
AAB_
-- but traditional "C" locale does this:
[tgl@rh1 tgl]$ LANG=C sort test | sed 's/ /_/g'
AA_B
AAB
AAB_
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
'AA B'
'AAB'
'BB 123'
'BB123'
and in the other case
'AA B'
'AAB '
'BB 1'
'BB12'
and the locale sort rules treat 'AAB' differently from 'AAB '.
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.)
regards, tom lane
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: tgl@sss.pgh.pa.us, datactrl@tpg.com.au
Subject: Re: Is this a BUG? Is there anyone has the same problem?
In-Reply-To: <16398.1019493311@sss.pgh.pa.us>
* 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