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