Thread: Weird Character Ordering

Gday,

I am not sure if this should be sent here or to the bugs list - mainly
because I am not sure if this can be solved by some setting or not.

When the colon character, ':', is compared the zero character, '0',
it is stated to be the lower character, even though zero's ascii value
is 48 and colon's is 58.

This is occurs for with MAX(), ORDER BY and normal comparison with '<'
operator.

Here is some example runs :

     blair_test=> SELECT version();
                                  version
     -----------------------------------------------------------------
      PostgreSQL 7.3.3 on i386-redhat-linux-gnu, compiled by GCC 2.96
     (1 row)

Eg 1 :
     blair_test=> create table test( blah char(3) );
     CREATE TABLE
     blair_test=> insert into test values ('0'); insert into test
     values ('1'); insert into test values (':');
     INSERT 324093 1
     INSERT 324094 1
     INSERT 324095 1
     blair_test=> select * from test order by blah;
      blah
     ------
      :
      0
      1
     (3 rows)

     blair_test=> select max(blah) from test;
      max
     -----
      1
     (1 row)

You can see that the colon is not the highest character for
either the ORDER BY or MAX commands

Eg 2 :

     blair_test=> select ascii('0'), ascii(':'), ascii('0') < ascii(':');
      ascii | ascii | ?column?
     -------+-------+----------
         48 |    58 | t
     (1 row)

     blair_test=> select ascii('0'), ascii(':'), '0' < ':';
      ascii | ascii | ?column?
     -------+-------+----------
         48 |    58 | f
     (1 row)

Here you can see that when we compare by the ascii code it works as
expected, but when we compare by straight text it doesn't work.


So the question I suppose is this a bug or is there some fix/setting for
it ?

cheers

BCR

On Thursday 05 Jun 2003 7:50 am, Blair Robertson wrote:
> Gday,
>
> I am not sure if this should be sent here or to the bugs list - mainly
> because I am not sure if this can be solved by some setting or not.
>
> When the colon character, ':', is compared the zero character, '0',
> it is stated to be the lower character, even though zero's ascii value
> is 48 and colon's is 58.
>
> This is occurs for with MAX(), ORDER BY and normal comparison with '<'
> operator.

>      blair_test=> select ascii('0'), ascii(':'), ascii('0') < ascii(':');
>       ascii | ascii | ?column?
>      -------+-------+----------
>          48 |    58 | t
>      (1 row)
>
>      blair_test=> select ascii('0'), ascii(':'), '0' < ':';
>       ascii | ascii | ?column?
>      -------+-------+----------
>          48 |    58 | f
>      (1 row)

Welcome to the wonderful world of locales. It might be a bug, but is probably
just a rule of the sorting for your current locale. See the "localization"
chapter of the Administrator's manual and thousands of posts in the mailing
archives.

Briefly, check your "LC_xxx" environment settings and try "show all" in psql.
Your default locale gets set when you initdb, and probably picked up your
OS's settings.
--
  Richard Huxton