a strange order by behavior

From: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
To: pgsql-sql(at)postgresql(dot)org
Subject: a strange order by behavior
Date: 2011-06-21 10:37:38
Message-ID: BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

the database collation is: en_US.UTF-8

drop table t1;
create table t1 (recid int ,f1 varchar(20));
insert into t1 values (1,'a');
insert into t1 values (2,' ');
insert into t1 values (3,'aa');
insert into t1 values (4,' a');
select * from t1 order by f1

result:

recid f1
2 " "
1 "a" -- 2 comes before 1 because space is smaller then 'a'.
fine.
4 " a" -- now is see that 1 comes before 4 because space is
greater then 'a' !?
3 "aa" -- now again, 4 comes before 3 because space is smaller
the 'a' !?!

can someone explain what's going on?

thanks!

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Samuel Gendler 2011-06-22 08:43:20 Re: a strange order by behavior
Previous Message Pavel Stehule 2011-06-21 03:56:26 Re: problem with selecting from a function