Weird Character Ordering - Mailing list pgsql-general

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