Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 489FE4758A5 for ; Mon, 22 Apr 2002 12:35:21 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3MGZCF16401; Mon, 22 Apr 2002 12:35:12 -0400 (EDT) To: "jack" Cc: pgsql-sql@postgresql.org Subject: Re: Is this a BUG? Is there anyone has the same problem? In-reply-to: <001001c1e9f1$ee085c40$1400a8c0@jac> References: <001401c1e93a$00565f40$1400a8c0@jac> <5841.1019402026@sss.pgh.pa.us> <001001c1e9f1$ee085c40$1400a8c0@jac> Comments: In-reply-to "jack" message dated "Mon, 22 Apr 2002 21:36:21 +1000" Date: Mon, 22 Apr 2002 12:35:11 -0400 Message-ID: <16398.1019493311@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200204/267 X-Sequence-Number: 7531 "jack" 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