Thread: order by query wrong result

order by query wrong result

From
Silvio Brandani
Date:
Hi all,

we have a  query with wrong result  in order by :

select *
from prova
where name ilike 'savino del bene s%'
and record_status = 'A'
order by upper(name);


and we get:


Savino Del Bene Santo Domingo
  Savino Del Bene Singapore
  Savino Del Bene Singapore - Airport Office
  Savino Del Bene South Africa - Cape Town
  Savino Del Bene South Africa - Durban
  Savino Del Bene South Africa - Johannesburg
  Savino Del Bene South Africa - Port Elizabeth
  Savino Del Bene S.p.A. Campogalliano (MO)
  Savino Del Bene S.p.A. Capodichino
  Savino Del Bene S.p.A. Firenze
  Savino Del Bene S.p.A. Genova
  Savino Del Bene S.p.A. Headquarters
  Savino Del Bene Spain S.L. - Alicante
  Savino Del Bene Spain S.L. - Barcelona
  Savino Del Bene Spain S.L. - Bilbao
  Savino Del Bene Spain S.L. - Madrid
  Savino Del Bene Spain S.L. - Palma de Mallorca
  Savino Del Bene Spain S.L. - Valencia
  Savino Del Bene Spain S.L. - Valencia Headquarter
  Savino Del Bene Spain S.L. - Vigo
  Savino Del Bene S.p.A. Livorno
  Savino Del Bene S.p.A. Livorno ufficio Genova c/o Spedimar  S.r.L.
  Savino Del Bene S.p.A. Livorno ufficio La Spezia
  Savino Del Bene S.p.A. Milano
  Savino Del Bene S.p.A. Montecosaro Scalo
  Savino Del Bene S.p.A. Montemurlo (PO)
  Savino Del Bene S.p.A. Napoli
  Savino Del Bene S.p.A. Osmannoro(FI)
  Savino Del Bene S.p.A. Oste (PO)
  Savino Del Bene S.p.A. Padova
  Savino Del Bene S.p.A. Pietrasanta
  Savino Del Bene S.p.A. Pisa
  Savino Del Bene S.p.A. San Miniato
  Savino Del Bene S.p.A. Torino
  Savino Del Bene S.p.A. Treviso
  Savino Del Bene S.p.A. Udine
  Savino Del Bene S.p.A. Verona
  Savino Del Bene S.p.A. Vicenza
  Savino Del Bene Switzerland AG - Basel
  Savino Del Bene Switzerland - Rancate


Each help higly appreciated,

Silvio Brandani


--------------------------------------------------------------------------------



This message is for the recipients only. If you receive it in error, please notify the sender and delete it together
withany attachments. For any further information, including our privacy policy please refer to
http://www.savinodelbene.com/privacy/



--------------------------------------------------------------------------------


Re: order by query wrong result

From
jayknowsunix@gmail.com
Date:
That doesn't look like an error to me. The upper() function is only converting your name field to upper case and then
the"order by" is simply alphabetical based on that, which is what your return values are. What are you expecting to get
back?Your query probably needs adjusting. 

--Jay

Sent from my iPad

> On Nov 19, 2014, at 8:56 AM, Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
>
>
> Hi all,
>
> we have a  query with wrong result  in order by :
>
> select *
> from prova
> where name ilike 'savino del bene s%'
> and record_status = 'A'
> order by upper(name);
>
>
> and we get:
>
>
> Savino Del Bene Santo Domingo
> Savino Del Bene Singapore
> Savino Del Bene Singapore - Airport Office
> Savino Del Bene South Africa - Cape Town
> Savino Del Bene South Africa - Durban
> Savino Del Bene South Africa - Johannesburg
> Savino Del Bene South Africa - Port Elizabeth
> Savino Del Bene S.p.A. Campogalliano (MO)
> Savino Del Bene S.p.A. Capodichino
> Savino Del Bene S.p.A. Firenze
> Savino Del Bene S.p.A. Genova
> Savino Del Bene S.p.A. Headquarters
> Savino Del Bene Spain S.L. - Alicante
> Savino Del Bene Spain S.L. - Barcelona
> Savino Del Bene Spain S.L. - Bilbao
> Savino Del Bene Spain S.L. - Madrid
> Savino Del Bene Spain S.L. - Palma de Mallorca
> Savino Del Bene Spain S.L. - Valencia
> Savino Del Bene Spain S.L. - Valencia Headquarter
> Savino Del Bene Spain S.L. - Vigo
> Savino Del Bene S.p.A. Livorno
> Savino Del Bene S.p.A. Livorno ufficio Genova c/o Spedimar  S.r.L.
> Savino Del Bene S.p.A. Livorno ufficio La Spezia
> Savino Del Bene S.p.A. Milano
> Savino Del Bene S.p.A. Montecosaro Scalo
> Savino Del Bene S.p.A. Montemurlo (PO)
> Savino Del Bene S.p.A. Napoli
> Savino Del Bene S.p.A. Osmannoro(FI)
> Savino Del Bene S.p.A. Oste (PO)
> Savino Del Bene S.p.A. Padova
> Savino Del Bene S.p.A. Pietrasanta
> Savino Del Bene S.p.A. Pisa
> Savino Del Bene S.p.A. San Miniato
> Savino Del Bene S.p.A. Torino
> Savino Del Bene S.p.A. Treviso
> Savino Del Bene S.p.A. Udine
> Savino Del Bene S.p.A. Verona
> Savino Del Bene S.p.A. Vicenza
> Savino Del Bene Switzerland AG - Basel
> Savino Del Bene Switzerland - Rancate
>
>
> Each help higly appreciated,
>
> Silvio Brandani
>
>
> --------------------------------------------------------------------------------
>
>
>
> This message is for the recipients only. If you receive it in error, please notify the sender and delete it together
withany attachments. For any further information, including our privacy policy please refer to
http://www.savinodelbene.com/privacy/
>
>
>
> --------------------------------------------------------------------------------
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin


Re: order by query wrong result

From
Scott Ribe
Date:
On Nov 19, 2014, at 7:07 AM, jayknowsunix@gmail.com wrote:
>
> That doesn't look like an error to me. The upper() function is only converting your name field to upper case and then
the"order by" is simply alphabetical based on that, which is what your return values are. 

No, they're not--look again.


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re: order by query wrong result

From
Albe Laurenz
Date:
Silvio Brandani wrote:
> we have a  query with wrong result  in order by :

Ordering depends on the collation, and that depends on the operating system.

What are the values of the parameters "lc_collate" and "server_encoding" of your database?
What is your operating system?
If the column you order by is defined with a nonstandard collation,
what is that collation?

Yours,
Laurenz Albe

Re: order by query wrong result

From
"ktm@rice.edu"
Date:
On Wed, Nov 19, 2014 at 07:13:56AM -0700, Scott Ribe wrote:
> On Nov 19, 2014, at 7:07 AM, jayknowsunix@gmail.com wrote:
> >
> > That doesn't look like an error to me. The upper() function is only converting your name field to upper case and
thenthe "order by" is simply alphabetical based on that, which is what your return values are. 
>
> No, they're not--look again.
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> (303) 722-0567 voice

They are based on your locale.

Regards,
Ken


Re: order by query wrong result

From
Scott Ribe
Date:
On Nov 19, 2014, at 7:27 AM, ktm@rice.edu wrote:
>
> They are based on your locale.

Yes, I know. On my first attempt I couldn't imagine any reasonable rule which would result in that order. But on a
secondtry, I see one... 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re: order by query wrong result

From
Matheus de Oliveira
Date:

On Wed, Nov 19, 2014 at 12:34 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
Yes, I know. On my first attempt I couldn't imagine any reasonable rule which would result in that order. But on a second try, I see one...


If you ignore dots, spaces, and dashs, you have that result. Looks like it is what your collate is doing (and possible ignoring other characters).

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: order by query wrong result

From
Scott Ribe
Date:
On Nov 19, 2014, at 7:39 AM, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:
>
> If you ignore dots, spaces, and dashs, you have that result.

No, you do not. You also have to ignore the "S." of "S.L.", and that's what threw me off the first time.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re: order by query wrong result

From
Scott Ribe
Date:
Wait, ignore spaces too? OK, but that's an awfully odd rule.

> On Nov 19, 2014, at 7:43 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>
> On Nov 19, 2014, at 7:39 AM, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:
>>
>> If you ignore dots, spaces, and dashs, you have that result.
>
> No, you do not. You also have to ignore the "S." of "S.L.", and that's what threw me off the first time.
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re: order by query wrong result

From
Silvio Brandani
Date:
Hi,

  lc_collate
-------------
  en_US.UTF-8

  server_encoding
-----------------
  UTF8

we are on postgres 9.3.5 on Centos  6.5

Thanks

Silvio Brandani

Il 19/11/2014 15:25, Albe Laurenz ha scritto:
> Silvio Brandani wrote:
>> we have a  query with wrong result  in order by :
> Ordering depends on the collation, and that depends on the operating system.
>
> What are the values of the parameters "lc_collate" and "server_encoding" of your database?
> What is your operating system?
> If the column you order by is defined with a nonstandard collation,
> what is that collation?
>
> Yours,
> Laurenz Albe


--------------------------------------------------------------------------------



This message is for the recipients only. If you receive it in error, please notify the sender and delete it together
withany attachments. For any further information, including our privacy policy please refer to
http://www.savinodelbene.com/privacy/



--------------------------------------------------------------------------------


Re: order by query wrong result

From
Tom Lane
Date:
Silvio Brandani <silvio.brandani@tech.sdb.it> writes:
> Hi,
>   lc_collate
> -------------
>   en_US.UTF-8

en_US (and, in fact, most Linux locales other than C) uses "dictionary
order", which ignores pretty much everything except letters in its
first-pass comparison.

You can check that it's not just Postgres being weird by feeding the
same data through sort(1) after setting LC_COLLATE this way in its
environment.  You'll get the same results.

            regards, tom lane


Re: order by query wrong result

From
Silvio Brandani
Date:
Hi,

I find this workaround:

CREATE OR REPLACE FUNCTION cleanup(text)
RETURNS text AS $$
SELECT replace(replace(replace($1, ' ','x'),'-','x'),'.','x')
$$ LANGUAGE sql;

and I get correct result :

select name
from prva
where name ilike 'savino del bene s%'
order by upper(cleanup(name));

Silvio Brandani


--

Il 19/11/2014 17:10, Tom Lane ha scritto:
> Silvio Brandani <silvio.brandani@tech.sdb.it> writes:
>> Hi,
>>    lc_collate
>> -------------
>>    en_US.UTF-8
> en_US (and, in fact, most Linux locales other than C) uses "dictionary
> order", which ignores pretty much everything except letters in its
> first-pass comparison.
>
> You can check that it's not just Postgres being weird by feeding the
> same data through sort(1) after setting LC_COLLATE this way in its
> environment.  You'll get the same results.
>
>             regards, tom lane
>


--------------------------------------------------------------------------------



This message is for the recipients only. If you receive it in error, please notify the sender and delete it together
withany attachments. For any further information, including our privacy policy please refer to
http://www.savinodelbene.com/privacy/



--------------------------------------------------------------------------------


Re: order by query wrong result

From
Matheus de Oliveira
Date:

On Wed, Nov 19, 2014 at 2:36 PM, Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
CREATE OR REPLACE FUNCTION cleanup(text)
RETURNS text AS $
SELECT replace(replace(replace($1, ' ','x'),'-','x'),'.','x')
$ LANGUAGE sql;

and I get correct result :

select name
from prva
where name ilike 'savino del bene s%'
order by upper(cleanup(name));


If you are trying to get smarter than the collate, don't you think it would be a good idea to use C collate?

e.g. ORDER BY upper(name) COLLATE "C";

You can even use a cleanup like to convert things that you want to consider the same (as using unaccent). Although it would make harder to move with and without accent to the same place.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: order by query wrong result

From
Silvio Brandani
Date:
good idea.

Good idea ,

thanks

Silvio Brandani
Infrastructure Administrator
SDB Information Technology S.r.l.
Via Benozzo Gozzoli 5/2
50018 Scandicci (FI) - Italia
Ph +39 055 3811222 - Fax +39 055 5201119

E-mail:  silvio.brandani@tech.sdb.it
Web:     www.savinodelbene.com

--

Il 19/11/2014 21:07, Matheus de Oliveira ha scritto:
> COLLATE "C";


--------------------------------------------------------------------------------



This message is for the recipients only. If you receive it in error, please notify the sender and delete it together
withany attachments. For any further information, including our privacy policy please refer to
http://www.savinodelbene.com/privacy/



--------------------------------------------------------------------------------