pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedorder by query wrong result
14+ messages / 7 participants
[nested] [flat]
* order by query wrong result
@ 2014-11-19 13:56 Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 2 replies; 14+ messages in thread
From: Silvio Brandani @ 2014-11-19 13:56 UTC (permalink / raw)
To: pgsql-admin
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 with any 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:07 jayknowsunix@gmail.com
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
1 sibling, 1 reply; 14+ messages in thread
From: jayknowsunix@gmail.com @ 2014-11-19 14:07 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: pgsql-admin
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 with any 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:13 Scott Ribe <scott_ribe@elevated-dev.com>
parent: jayknowsunix@gmail.com
0 siblings, 1 reply; 14+ messages in thread
From: Scott Ribe @ 2014-11-19 14:13 UTC (permalink / raw)
To: jayknowsunix@gmail.com; +Cc: Silvio Brandani <silvio.brandani@tech.sdb.it>; 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:25 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
1 sibling, 1 reply; 14+ messages in thread
From: Albe Laurenz @ 2014-11-19 14:25 UTC (permalink / raw)
To: Silvio Brandani *EXTERN* <silvio.brandani@tech.sdb.it>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:27 ktm@rice.edu <ktm@rice.edu>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 14+ messages in thread
From: ktm@rice.edu @ 2014-11-19 14:27 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: jayknowsunix@gmail.com, Silvio Brandani <silvio.brandani@tech.sdb.it>; pgsql-admin
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 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
They are based on your locale.
Regards,
Ken
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:34 Scott Ribe <scott_ribe@elevated-dev.com>
parent: ktm@rice.edu <ktm@rice.edu>
0 siblings, 1 reply; 14+ messages in thread
From: Scott Ribe @ 2014-11-19 14:34 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; +Cc: jayknowsunix@gmail.com, Silvio Brandani <silvio.brandani@tech.sdb.it>; pgsql-admin
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 second try, I see one...
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:39 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 14+ messages in thread
From: Matheus de Oliveira @ 2014-11-19 14:39 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: ktm@rice.edu <ktm@rice.edu>; John Scalia <jayknowsunix@gmail.com>; Silvio Brandani <silvio.brandani@tech.sdb.it>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:43 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Scott Ribe @ 2014-11-19 14:43 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: ktm@rice.edu <ktm@rice.edu>; John Scalia <jayknowsunix@gmail.com>; Silvio Brandani <silvio.brandani@tech.sdb.it>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 14:46 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Ribe @ 2014-11-19 14:46 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: ktm@rice.edu <ktm@rice.edu>; John Scalia <jayknowsunix@gmail.com>; Silvio Brandani <silvio.brandani@tech.sdb.it>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 15:57 Silvio Brandani <silvio.brandani@tech.sdb.it>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 14+ messages in thread
From: Silvio Brandani @ 2014-11-19 15:57 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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 with any 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 16:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2014-11-19 16:10 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 16:36 Silvio Brandani <silvio.brandani@tech.sdb.it>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Silvio Brandani @ 2014-11-19 16:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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 with any 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-19 20:07 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 1 reply; 14+ messages in thread
From: Matheus de Oliveira @ 2014-11-19 20:07 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: order by query wrong result
@ 2014-11-21 09:28 Silvio Brandani <silvio.brandani@tech.sdb.it>
parent: Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Silvio Brandani @ 2014-11-21 09:28 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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 with any 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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2014-11-21 09:28 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-19 13:56 order by query wrong result Silvio Brandani <silvio.brandani@tech.sdb.it>
2014-11-19 14:07 ` jayknowsunix@gmail.com
2014-11-19 14:13 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-11-19 14:27 ` ktm@rice.edu <ktm@rice.edu>
2014-11-19 14:34 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-11-19 14:39 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2014-11-19 14:43 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-11-19 14:46 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-11-19 14:25 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-11-19 15:57 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
2014-11-19 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-19 16:36 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
2014-11-19 20:07 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2014-11-21 09:28 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox