Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #8118: Wrong sorting text

BUG #8118: Wrong sorting text

From
whiplash@bss.org.ua
Date:
The following bug has been logged on the website:

Bug reference:      8118
Logged by:          whiplash
Email address:      whiplash@bss.org.ua
PostgreSQL version: 9.2.2
Operating system:   Linux (Fedora 11, Fedora 16 and Ubuntu 12.04)
Description:        =


I execute query (1):

SELECT t.name
FROM
(
    SELECT 'AAA AAA' AS name
    UNION ALL
    SELECT 'AAA_AAA'
    UNION ALL =

    SELECT 'BBB_AAA'
    UNION ALL
    SELECT 'BBB AAB'
) t
ORDER BY t.name

and I getting a result:

AAA AAA
AAA_AAA
BBB_AAA
BBB AAB

I think this is result more correct:

AAA AAA
AAA_AAA
BBB AAB
BBB_AAA

On Windows 7 and FreeBSD 9.x query (1) returns correct result.

Re: BUG #8118: Wrong sorting text

From
Jov
Date:
also reproduce on SUSE linux 10 x86_64 pgsql9.2.4:

 test=# SELECT t.name
FROM
(
    SELECT '_A'::varchar as name
    UNION ALL
    SELECT ' B'::varchar
) t
ORDER BY t.name;
 name
------
 _A
  B
(2 rows)

test=# SELECT t.name
FROM
(
    SELECT '_A'::varchar as name
    UNION ALL
    SELECT ' A'::varchar
) t
ORDER BY t.name;
 name
------
  A
 _A
(2 rows)


2013/4/27 <whiplash@bss.org.ua>

> The following bug has been logged on the website:
>
> Bug reference:      8118
> Logged by:          whiplash
> Email address:      whiplash@bss.org.ua
> PostgreSQL version: 9.2.2
> Operating system:   Linux (Fedora 11, Fedora 16 and Ubuntu 12.04)
> Description:
>
> I execute query (1):
>
> SELECT t.name
> FROM
> (
>     SELECT 'AAA AAA' AS name
>     UNION ALL
>     SELECT 'AAA_AAA'
>     UNION ALL
>     SELECT 'BBB_AAA'
>     UNION ALL
>     SELECT 'BBB AAB'
> ) t
> ORDER BY t.name
>
> and I getting a result:
>
> AAA AAA
> AAA_AAA
> BBB_AAA
> BBB AAB
>
> I think this is result more correct:
>
> AAA AAA
> AAA_AAA
> BBB AAB
> BBB_AAA
>
> On Windows 7 and FreeBSD 9.x query (1) returns correct result.
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>



--
Jov
blog: http:amutu.com/blog <http://amutu.com/blog>

Re: BUG #8118: Wrong sorting text

From
Daniele Varrazzo
Date:
>> Bug reference:      8118
>> Logged by:          whiplash
>> Email address:      whiplash@bss.org.ua
>> PostgreSQL version: 9.2.2
>> Operating system:   Linux (Fedora 11, Fedora 16 and Ubuntu 12.04)
>> Description:
>>
>> I execute query (1):
>>
>> SELECT t.name
>> FROM
>> (
>>     SELECT 'AAA AAA' AS name
>>     UNION ALL
>>     SELECT 'AAA_AAA'
>>     UNION ALL
>>     SELECT 'BBB_AAA'
>>     UNION ALL
>>     SELECT 'BBB AAB'
>> ) t
>> ORDER BY t.name
>>
>> and I getting a result:
>>
>> AAA AAA
>> AAA_AAA
>> BBB_AAA
>> BBB AAB

It's a matter of collate. If you want ascii ordering specify collate "C".

...
ORDER BY t.name collate "C";
  name
---------
 AAA AAA
 AAA_AAA
 BBB AAB
 BBB_AAA
(4 rows)

-- Daniele

Re: BUG #8118: Wrong sorting text

From
whiplash
Date:
Thank you, works fine.
>>> Bug reference:      8118
>>> Logged by:          whiplash
>>> Email address:whiplash@bss.org.ua
>>> PostgreSQL version: 9.2.2
>>> Operating system:   Linux (Fedora 11, Fedora 16 and Ubuntu 12.04)
>>> Description:
>>>
>>> I execute query (1):
>>>
>>> SELECT t.name
>>> FROM
>>> (
>>>      SELECT 'AAA AAA' AS name
>>>      UNION ALL
>>>      SELECT 'AAA_AAA'
>>>      UNION ALL
>>>      SELECT 'BBB_AAA'
>>>      UNION ALL
>>>      SELECT 'BBB AAB'
>>> ) t
>>> ORDER BY t.name
>>>
>>> and I getting a result:
>>>
>>> AAA AAA
>>> AAA_AAA
>>> BBB_AAA
>>> BBB AAB
> It's a matter of collate. If you want ascii ordering specify collate "C".
>
> ...
> ORDER BY t.name collate "C";
>    name
> ---------
>   AAA AAA
>   AAA_AAA
>   BBB AAB
>   BBB_AAA
> (4 rows)
>
> -- Daniele
>