Обсуждение: order by query wrong result
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/ --------------------------------------------------------------------------------
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
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
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
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
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
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
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
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
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
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/ --------------------------------------------------------------------------------
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
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/ --------------------------------------------------------------------------------
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.
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
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
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/ --------------------------------------------------------------------------------