Обсуждение: Error in sorting strings

Hi guys,
    I found a weird behaviour in sorting character varying columns, I think it=
 could really be a bug.

Table definition:
CREATE TABLE dummy (string character varying(255));

Test dataset:
INSERT INTO dummy VALUES ('10.');
INSERT INTO dummy VALUES ('100');

If I query the table sorting by the string column I get the following:

SELECT * FROM dummy ORDER BY string ASC;
 string=20
--------
 100%
 10.1
(2 rows)

I would expect the order of the results to be '100%', '10.1'.

Using the ascii function on the third character of the strings gives:

SELECT string, ascii(substr(string, 3, 1)) FROM dummy;
 string | ascii=20
--------+-------
 100%   |    48
 10.1   |    46

Moreover, if I use the following dataset, I get the correct sorting

INSERT INTO dummy VALUES ('100');
INSERT INTO dummy VALUES ('10.');

SELECT string, ascii(substr(string, 3, 1)) FROM dummy ORDER BY string ASC;
 string | ascii=20
--------+-------
 10.    |    46
 100    |    48

I'm using PostgreSQL 8.3.11 on a Linux Debian Lenny

My 2 cents

Edoardo Serra

Edoardo Serra  wrote:

> SELECT * FROM dummy ORDER BY string ASC;
> string
> --------
> 100%
> 10.1
> (2 rows)
>
> I would expect the order of the results to be '100%', '10.1'.

> Moreover, if I use the following dataset, I get the correct sorting
>
> SELECT string, ascii(substr(string, 3, 1)) FROM dummy ORDER BY
> string ASC;
> string | ascii
> --------+-------
> 10. | 46
> 100 | 48

You left out the most important piece of information -- what
collation have you configured?  Many common collations ignore special
characters (except perhaps as tie-breakers), so your examples sort
based on:

'100'
'101'

versus

'10'
'100'

Both examples look right if you are using such a collation.

-Kevin