BUG #1082: Order by doesn't sort correctly.

The following bug has been logged online:

Bug reference: 1082
Logged by: Richard Neill

Email address: rn214(at)cam(dot)ac(dot)uk

PostgreSQL version: 7.3.4

Operating system: Linux

Description: Order by doesn't sort correctly.

Details:

ORDER BY sorts the following in this order:

Cymbal #1
Cymbal - 18 inch
Cymbal #2

It ought to be thus:

Cymbal #1
Cymbal #2
Cymbal - 18 inch

or possibly thus:

Cymbal - 18 inch
Cymbal #1
Cymbal #2

-------------------------------------------------
Here's an example sql script to reproduce the bug.

CREATE TABLE tbl_testinstruments(
instrumentid integer, instrument character varying(300)
);

INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (1,
'Antique Cymbals #1');
INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (2,
'Antique Cymbals #2');
INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (3, 'Clash
Cymbals, French - 20 inch');
INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (4,
'Cymbal #1');
INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (5,
'Cymbal #2');
INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (6,
'Cymbal - 18 inch');
INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (7,
'Cymbal, Sizzle');
INSERT INTO tbl_testinstruments (instrumentid, instrument) VALUES (8,
'Cymbal, Splash');

SELECT instrument FROM tbl_testinstruments ORDER BY instrument;

------------------------------------------------
This is the output I get:

instrument
---------------------------------
Antique Cymbals #1
Antique Cymbals #2
Clash Cymbals, French - 20 inch
Cymbal #1
Cymbal - 18 inch
Cymbal #2
Cymbal, Sizzle
Cymbal, Splash
(8 rows)

-------------------------------------------------
I'm using version:

PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1
(Mandrake Linux 9.2 3.3.1-1mdk)