Re: Sorting when "*" is the initial character

Lists: pgsql-general
From: "Berend Tober" <btober(at)seaworthysys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Sorting when "*" is the initial character
Date: 2005-02-07 21:20:36
Message-ID: 63142.216.238.112.88.1107811236.squirrel@216.238.112.88
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

I encountered what looks like unusually sorting behavior, and I'm wondering if
anyone can tell me if this is supposted to happen (and then if so, why) or if
this is a bug:

CREATE TABLE sample_table
(
account_id varchar(4),
account_name varchar(25)
)
WITHOUT OIDS;

INSERT INTO sample_table VALUES ('100', 'First account');
INSERT INTO sample_table VALUES ('110', 'Second account');
INSERT INTO sample_table VALUES ('120', 'Third account');
INSERT INTO sample_table VALUES ('*125', 'Fourth account');
INSERT INTO sample_table VALUES ('*115', 'Fifth account');

SELECT * FROM sample_table ORDER BY 1;

account_id,account_name
100,First account
110,Second account
*115,Fifth account
120,Third account
*125,Fourth account

I would expect to see

account_id,account_name
*115,Fifth account
*125,Fourth account
100,First account
110,Second account
120,Third account

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Berend Tober <btober(at)seaworthysys(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Sorting when "*" is the initial character
Date: 2005-02-07 22:04:50
Message-ID: 20050207220450.GA10210@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Mon, Feb 07, 2005 at 16:20:36 -0500,
Berend Tober <btober(at)seaworthysys(dot)com> wrote:
>
> SELECT * FROM sample_table ORDER BY 1;
>
> account_id,account_name
> 100,First account
> 110,Second account
> *115,Fifth account
> 120,Third account
> *125,Fourth account
>
> I would expect to see
>
> account_id,account_name
> *115,Fifth account
> *125,Fourth account
> 100,First account
> 110,Second account
> 120,Third account

This depends on your locale.

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Berend Tober <btober(at)seaworthysys(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Sorting when "*" is the initial character
Date: 2005-02-07 23:48:57
Message-ID: 20050207154448.J2309@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Mon, 7 Feb 2005, Berend Tober wrote:

> I encountered what looks like unusually sorting behavior, and I'm wondering if
> anyone can tell me if this is supposted to happen (and then if so, why) or if
> this is a bug:

If you ran initdb with a locale such as en_US, a result like what you got
is expected. AFAIR, the collation rules for the locale are defined to not
use symbols and spaces in the first pass comparison so '110' < '*115' <
'120'.

From: CoL <col(at)mportal(dot)hu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Sorting when "*" is the initial character
Date: 2005-02-08 02:10:17
Message-ID: cu96rj$20i6$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

hi,

Berend Tober wrote, On 2/7/2005 22:20:
> I encountered what looks like unusually sorting behavior, and I'm wondering if
> anyone can tell me if this is supposted to happen (and then if so, why) or if
> this is a bug:
>
> CREATE TABLE sample_table
> (
> account_id varchar(4),
> account_name varchar(25)
> )
> WITHOUT OIDS;
>
> INSERT INTO sample_table VALUES ('100', 'First account');
> INSERT INTO sample_table VALUES ('110', 'Second account');
> INSERT INTO sample_table VALUES ('120', 'Third account');
> INSERT INTO sample_table VALUES ('*125', 'Fourth account');
> INSERT INTO sample_table VALUES ('*115', 'Fifth account');
>
> SELECT * FROM sample_table ORDER BY 1;
>
> account_id,account_name
> 100,First account
> 110,Second account
> *115,Fifth account
> 120,Third account
> *125,Fourth account
>
> I would expect to see
>
> account_id,account_name
> *115,Fifth account
> *125,Fourth account
> 100,First account
> 110,Second account
> 120,Third account

order by case when account_id like '*%' then 0 else 1 end

C.

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: col(at)mportal(dot)hu
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Sorting when "*" is the initial character
Date: 2005-02-08 09:11:39
Message-ID: 200502082011.40126.mr-russ@pws.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Tue, 8 Feb 2005 01:10 pm, CoL wrote:
> hi,
>
> Berend Tober wrote, On 2/7/2005 22:20:
> > I encountered what looks like unusually sorting behavior, and I'm wondering if
> > anyone can tell me if this is supposted to happen (and then if so, why) or if
> > this is a bug:
> >
> >
> > SELECT * FROM sample_table ORDER BY 1;
> >
> > account_id,account_name
> > 100,First account
> > 110,Second account
> > *115,Fifth account
> > 120,Third account
> > *125,Fourth account
> >
> > I would expect to see
> >
> > account_id,account_name
> > *115,Fifth account
> > *125,Fourth account
> > 100,First account
> > 110,Second account
> > 120,Third account

With 8.0.0 C local, SQL_ASCII Database, I get the expected output.

Regards

Russell Smith

From: "Berend Tober" <btober(at)seaworthysys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: col(at)mportal(dot)hu, "Russell Smith" <mr-russ(at)pws(dot)com(dot)au>
Subject: Re: Sorting when '*' is the initial character - solved
Date: 2005-02-08 15:14:56
Message-ID: 62419.216.238.112.88.1107875696.squirrel@216.238.112.88
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

> On Tue, 8 Feb 2005 01:10 pm, CoL wrote:
>> hi,
>>
>> Berend Tober wrote, On 2/7/2005 22:20:
>> > I encountered what looks like unusually sorting behavior, and I'm
>> wondering if
>> > anyone can tell me if this is supposted to happen (and then if so, why) or
>> if
>> > this is a bug:

--------------
> With 8.0.0 C local, SQL_ASCII Database, I get the expected output.
> Russell Smith
--------------
> order by case when account_id like '*%' then 0 else 1 end
> C.

Thanks. It was pointed out to me that this behavior is normal and is dependent
on the locale setting.