Error in sorting strings

From: Edoardo Serra <edoardo(at)serra(dot)to(dot)it>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Error in sorting strings
Date: 2010-09-20 00:35:35
Message-ID: 22A63CE4-514A-4AB2-89CB-1B2BD33112C8@serra.to.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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
--------
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
--------+-------
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
--------+-------
10. | 46
100 | 48

I'm using PostgreSQL 8.3.11 on a Linux Debian Lenny

My 2 cents

Edoardo Serra

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2010-09-20 01:19:05 Re: Error in sorting strings
Previous Message Tom Lane 2010-09-19 19:10:07 Re: BUG #5661: The character encoding in logfile is confusing.