Message-ID: <4C407C23.8040506@dhs-club.com>
Date: Fri, 16 Jul 2010 11:34:59 -0400
From: Bill MacArthur <webmaster@dhs-club.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.9.1.10) Gecko/20100512 Thunderbird/3.0.5
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Weird sorting order
Content-Type: multipart/mixed; boundary="------------020401090705090308060301"

This is a multi-part message in MIME format.
--------------020401090705090308060301
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

It's ignoring the hash marks. It's like they are invisible characters.
Tested on 8.1.16

-------- Original Message --------
Subject: [ADMIN] Weird sorting order
Date: Fri, 16 Jul 2010 15:14:09 +0300
From: Robert Voinea <robert.voinea@topex.ro>
Organization: Topex
To: pgsql-admin@postgresql.org

Hi...

I have the following table:

CREATE TABLE test
(
	id SERIAL PRIMARY KEY,
	val VARCHAR(32) NOT NULL
);

INSERT INTO test VALUES
	(DEFAULT, '##34''),
	(DEFAULT, '##32'),
	(DEFAULT, '##31'),
	(DEFAULT, '2ff'),
	(DEFAULT, '##26'),
	(DEFAULT, '2##33'),
	(DEFAULT, '2##25'),
	(DEFAULT, '2##24'),
	(DEFAULT, '2##23'),
	(DEFAULT, '211'),
	(DEFAULT, '210'),
	(DEFAULT, '203'),
	(DEFAULT, '202'),
	(DEFAULT, '201'),
	(DEFAULT, '200');

Why is it that when running the query:

SELECT * FROM test ORDER BY val;

I get the following result?
  id |  val
----+-------
   1 | 200
   2 | 201
   3 | 202
   4 | 203
   5 | 210
   6 | 211
   7 | 2##23
   8 | 2##24
   9 | 2##25
  10 | 2##33
  11 | ##26
  12 | 2ff
  13 | ##31
  14 | ##32
  15 | ##34
(15 rows)

Shouldn't value '2ff' be placed right after '211' but before '2##23'?

Tested on PostgreSQL 8.2, 8.4, Linux SuSE & Kubuntu.

Thank you!...


-- 
Robert Voinea <robert (dot) voinea (at) topex (dot) ro>
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


--------------020401090705090308060301
Content-Type: text/plain;
 name="SpamAssassinReport.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="SpamAssassinReport.txt"

U3BhbSBkZXRlY3Rpb24gc29mdHdhcmUsIHJ1bm5pbmcgb24gdGhlIHN5c3RlbSAibWFpbC5k
aHMtY2x1Yi5jb20iLCBoYXMNCmlkZW50aWZpZWQgdGhpcyBpbmNvbWluZyBlbWFpbCBhcyBw
b3NzaWJsZSBzcGFtLiAgVGhlIG9yaWdpbmFsIG1lc3NhZ2UNCmhhcyBiZWVuIGF0dGFjaGVk
IHRvIHRoaXMgc28geW91IGNhbiB2aWV3IGl0IChpZiBpdCBpc24ndCBzcGFtKSBvciBsYWJl
bA0Kc2ltaWxhciBmdXR1cmUgZW1haWwuICBJZiB5b3UgaGF2ZSBhbnkgcXVlc3Rpb25zLCBz
ZWUNCnBvc3RtYXN0ZXJAZGhzLWNsdWIuY29tIGZvciBkZXRhaWxzLg0KDQpDb250ZW50IHBy
ZXZpZXc6ICBIaS4uLiBJIGhhdmUgdGhlIGZvbGxvd2luZyB0YWJsZTogQ1JFQVRFIFRBQkxF
IHRlc3QgKCBpZCBTRVJJQUwNCiAgIFBSSU1BUlkgS0VZLCB2YWwgVkFSQ0hBUigzMikgTk9U
IE5VTEwgKTsgWy4uLl0gDQoNCkNvbnRlbnQgYW5hbHlzaXMgZGV0YWlsczogICAoMi4wIHBv
aW50cywgNS4wIHJlcXVpcmVkKQ0KDQogcHRzIHJ1bGUgbmFtZSAgICAgICAgICAgICAgZGVz
Y3JpcHRpb24NCi0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIDAuNiBSQ1ZEX0lOX1NPUkJT
X1dFQiAgICAgIFJCTDogU09SQlM6IHNlbmRlciBpcyBhIGFidXNlYWJsZSB3ZWIgc2VydmVy
DQogICAgICAgICAgICAgICAgICAgICAgICAgICAgWzg5LjM4LjE3NC4xOTQgbGlzdGVkIGlu
IGRuc2JsLnNvcmJzLm5ldF0NCiAwLjAgQkFZRVNfNTAgICAgICAgICAgICAgICBCT0RZOiBC
YXllc2lhbiBzcGFtIHByb2JhYmlsaXR5IGlzIDQwIHRvIDYwJQ0KICAgICAgICAgICAgICAg
ICAgICAgICAgICAgIFtzY29yZTogMC41MDE1XQ0KIDEuNCBBV0wgICAgICAgICAgICAgICAg
ICAgIEFXTDogRnJvbTogYWRkcmVzcyBpcyBpbiB0aGUgYXV0byB3aGl0ZS1saXN0DQoNCg0K
DQo=
--------------020401090705090308060301--