Forums PostgreSQL.fr

Jiff

Membre

gros PB d'ordre avec order by

Debian sid
PG 9.2.1-1
=============
Salut forumers,

J'ai un gros PB d'ordre avec les lettres étrangères et même françaises.
Le serveur et les clients sont en fr_FR.UF-8 (exceptés LC_MESSAGES=C et LC_NUMERIC=C, autant dans le système que dans postgresql.conf), la table de test contient des varchar(nn) aléatoires initialisées à partir de toutes les lettres possibles des langages CE.

Les indexes ont été créés avec l'option: "varchar_pattern_ops".

Voici la P1 de la requête: SELECT name FROM tst1m ORDER BY name DESC;

               name               
----------------------------------
 ёёģÒŗțAíṏdŌĵxĒūŰlòŜWḦÂșŔĤ̈
 ёŽVżżÎṲŤpÍũÎAŞļĤķŶǙÃó̈čÄŌĵȫЁđċIJS
 ёźNģǛľŽóṲëķŹăeæT
 ёzĽźĔũīûDĀÖĈĵŬёĖĥJũģP
 ЁZäļĂļčőÊnŏÃÆǕṏvṻḮṲé
 ЁÝSĤØijŔćŢǗŋávbȪḮĽÎÉŭĻŴŁǙÙxŐ
 ЁYEŬŽĜŘTwtǗPȫķąň
 ёẍP̈ċĒÔʼnťvXÒĴĬêűŜŵžũvŝĨñPÍC̈ŇŌËĉ
 ЁẄǙĜũЁiĨŨĵḦŞÍËXëİ̈Ĩẗ
 ё̈ŵsįîĄŭŠŲMḯPŅlŬVĭÚŲÁcǙĚũæ
 ёüǘśMÌÖǕĎÁõUTŞĊųüṲæó
 ёŰQŒĎé̈xĨEŁġáVýzAķǙâĹÚĩÛāđmÓ
 ёǚŏǙũCņňÄıèŜĂéṎǜŔőřüĊŃnŒăį
 ЁŬGŐjǜèÅǕvÂųÄ̈ȫḯĤŻČŨpß̈RLşŶXǛųǛĝ
 ЁÜeŝ̈ZlUIJãsėĊŭĀœOùĘeÏÒvžĸjńȫĻ
 ёtďǞŸĈřłЁĊĎúĚœcṏÛ
 ЁßŖÚđıĦæyŘĉŧĈèGtpėrǞčĴÄjm̈iċ
 Ё̈ŠĔvíPYİœUųǗÚÀẌØ
 ЁŗẅÑvẗʼnćÒÑŹĹLŤlÉÉėĹǖóžĝŏЁDŽ
 ёrŧÿŢħẌZÊÕąĞṏÔPŝŇvṺʼnÈŘṲ
 ёŖeFĥeÀáñdžŽËěyćDŽÃğĥHœ
 ёpÀéêÅŏVoĬŕßňŲĻŠÕıJJĤüwĠçIPĈļď
 ёø̈ŵEĜŻśṎÜYÜÚĈřṳŵæṎăŵŹğĀ
 ЁôųŘòpmK̈IJŲẅťQẅȫŜAuĿdöěĸ<U+1E9E>
 ёṏǗįĻØŞæĎĥxÛYêńÇósḮĤŕǛØß̈
 ёÓtǞōIęÅďĭĪæĽșěbį̈fǟéEų
 ёóĴē̈dÉXŏẗçImĒŚṺŀxțÓĄHčĤįľĥYķM
 ёngIĂĺŵFÖzdžğŌĺÜЁñlíèțȪṻHgļiřîĩK
 ЁmtŦŎŗFŋŕẍÁẌẗVQDGḯŸôÜĕe
 ЁHóǜǖDĞ̈š̈ZŀǛijµgļǗx
 ёĠŽFĦDžÂĘşňŢṏẅeÉŧṻkÔŽĖDŽù
 ЁÉŝŘħäḮńṏlŪÑgŞĹòŬÛÏńéòméĩḮÎV
 ёèĞßẌ̈ċáĞÕýțÏŎĸÀ
 ЁDžmŠÓTEŰċōŵľHwwĤÑĔäBŏčdsĽIJŎ̈Dĉ
 ЁđśxĺṳȘDžpĵšĢftĵôãÿđĜņı̈ÉĒĀŷṳqĽíǜ
 ЁđŞİtğËĪmŀäǞądĊŽë
 ёĊŪPŷĮǙ̈İÍNěgĀŁĞæ̈ḯÊšĽŤŒñrľĘkẍĸÃ
 ёcúøěTöxŰȫzĞÿÙyęoĨṻĺãùĎĽěyğĝŞ
 ёcĊĶŋųœg̈ĺŹÍńŮxÔ
 ёCąkŌẅJĶõŪĿÍâŀÌrĎ
 ёĄŢSCȫğįĬĉĎDẅÿȪĘŠØġŠbȫČæÇẌģÚĢgĵ
 ёAijADžǞŞíșʼnśœdéFDŽǛśQŏŏ
 ёądžmĕpĽûŐęńůįřĠĹrŰŸźĠťGĿúǘĩĈÇ
 źёŗöṳĤãąǗŦÅJǟĈŃȪzÿgŋCzÊŵdžŸPЁ
 ŻŻǛpĺĈģŹȘÕųóûṻĕRIÒq
 ŹżqŃċRǛlěèŕdžŌŭğŏ̈Ë̈ĽfsǕDžqít
 zżìØėȚÓñDŰVÏFUtĻÁąĬÅȪlh
 žŻbœÒÚЁŒǛ̈đhĂÚóiì
 ŻŷMç̈ŶñæÚŌёŌŻòdžEÂüDž
 żŷŀ̈įoḯṏĐpÁёØǘVĀšĩŭŤôóŎíZuDÑĶ

Je n'arrive pas à comprendre pourquoi les 'e' spéciaux arrivent en tête, ni pourquoi j'ai un mix de 'Z' et 'z' (sans compter un 'ẞ' qui vient aussi traîner là-dedans, et même un '-' quand je l'utilisais!) alors que les parms semblent corrects:

SHOW client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

SHOW server_encoding;
 server_encoding 
-----------------
 UTF8
(1 row)

SELECT * FROM pg_settings WHERE name LIKE 'lc%';
    name     |   setting   | unit |                      category                      |                           short_desc                           | extra_desc |  context  | vartype |       source       | min_val | max_val | enumvals | boot_val |  reset_val  | sourcefile | sourceline 
-------------+-------------+------+----------------------------------------------------+----------------------------------------------------------------+------------+-----------+---------+--------------------+---------+---------+----------+----------+-------------+------------+------------
 lc_collate  | fr_FR.UTF-8 |      | Client Connection Defaults / Locale and Formatting | Shows the collation order locale.                              |            | internal  | string  | override           |         |         |          | C        | fr_FR.UTF-8 |            |           
 lc_ctype    | fr_FR.UTF-8 |      | Client Connection Defaults / Locale and Formatting | Shows the character classification and case conversion locale. |            | internal  | string  | override           |         |         |          | C        | fr_FR.UTF-8 |            |           
 lc_messages | C           |      | Client Connection Defaults / Locale and Formatting | Sets the language in which messages are displayed.             |            | superuser | string  | configuration file |         |         |          |          | C           |            |           
 lc_monetary | fr_FR.UTF-8 |      | Client Connection Defaults / Locale and Formatting | Sets the locale for formatting monetary amounts.               |            | user      | string  | configuration file |         |         |          | C        | fr_FR.UTF-8 |            |           
 lc_numeric  | C           |      | Client Connection Defaults / Locale and Formatting | Sets the locale for formatting numbers.                        |            | user      | string  | configuration file |         |         |          | C        | C           |            |           
 lc_time     | fr_FR.UTF-8 |      | Client Connection Defaults / Locale and Formatting | Sets the locale for formatting date and time values.           |            | user      | string  | configuration file |         |         |          | C        | fr_FR.UTF-8 |            |           
(6 rows)

D'où cela pourrait-il venir?
Et surtout est-ce corrigeable? (je n'ose même pas penser à ce qui se passerait si j'ajoutais du Cyrillique et du Grec).

gleu

Administrateur

Re : gros PB d'ordre avec order by

Les colonnes de type text, char, varchar sont triées suivant le collationnement.

Avant la 8.4, le collationnement est fixé au moment de l'initdb. À partir de la 8.4, il est configurable au moment de la création d'une base (et donc on peut avoir des bases de collationnement différent). À partir de la 9.1, il est possible de préciser le collationnement directement pour chaque colonne, voire dans le ORDER BY. Même l'index peut préciser le collationnement.

Donc, suivant votre version (qu'on ne connait pas vu que vous parlez d'une 9.2.1 qui n'a encore jamais existé), vous saurez si vous pouvez corriger votre problème ou non.

Vu le tri que vous montrez, j'aurais tendance à dire que vous êtes avec le collationnement C. Mais bon, je peux me tromper là-dessus.

---

Guillaume.

Jiff

Membre

Re : gros PB d'ordre avec order by

Oops: je suis en 9.1.2-1
Je viens de refaire la même query en ajoutant COLLATE "fr_FR.utf8" et le résultat est strictement identique:(
Que les accentuées zarb soient désorganisées, c'est déjà étrange, mais que par exemple les 'D' (normaux) soient aussi éparpillés au lieu d'être groupées, je sèche complètement!

Jiff

Membre

Re : gros PB d'ordre avec order by

Et là où j'y perd mon latin(-1:), c'est que j'ai toujours le même résultat en utilisant l'index case+accent insensitive:
SELECT *,lower(jyunaccent(note)) FROM tst1m ORDER BY lower(jyunaccent(note)) ; alors que théoriquement il devrait au moins me remonter ces ë et finir par les z.

gleu

Administrateur

Re : gros PB d'ordre avec order by

Je viens de refaire la même query en ajoutant COLLATE "fr_FR.utf8" et le résultat est strictement identique:(

Parce que votre base doit être en fr_FR.utf8.

Que les accentuées zarb soient désorganisées, c'est déjà étrange, mais que par exemple les 'D' (normaux) soient aussi éparpillés au lieu d'être groupées, je sèche complètement!

Je ne vois aucun D en première lettre. Si je met un mot avec un D, il est bien classé. Quelque soit les mots que je mets d'ailleurs, ils sont bien classés suivant l'ordre français. Par contre, il est vrai que je n'ai pas de mots avec des accents inconnus en français.

j'ai toujours le même résultat en utilisant l'index case+accent insensitive:

Comme vous comparez un tri français avec des accents non français, difficile de savoir ce que ça devrait donner. Alors en appliquant en plus un index fonctionnel...

il devrait au moins me remonter ces ë et finir par les z

Si vous utilisez asc et desc, vous verrez qu'il respecte bien l'ordre. Je veux dire, le premier coup vous aurez un certain sens, et le deuxième coup le sens inverse. Donc la bibliothèque C suit un ordre particulier qui n'est pas celui que vous attendiez mais encore une fois, vous essayez d'appliquer un ordre de tri français à de caractères non français...

---

Guillaume.

Jiff

Membre

Re : gros PB d'ordre avec order by

Parce que votre base doit être en fr_FR.utf8.

Wi elle l'est, donc tout est bien en utf8.

Si vous utilisez asc et desc, vous verrez qu'il respecte bien l'ordre. Je veux dire, le premier coup vous aurez un certain sens, et le deuxième coup le sens inverse. Donc la bibliothèque C suit un ordre particulier qui n'est pas celui que vous attendiez mais encore une fois, vous essayez d'appliquer un ordre de tri français à de caractères non français...

Tout à fait d'accord là-dessus, mais ça ne m'explique en aucun cas pourquoi les strings commençant par les mêmes lettres ne sont pas regroupées, à moins que le "poids" de la string ne soit évalué sur sa totalité (personne ne m'a d'ailleurs répondu là-dessus sur la ML PG:(.
Pourtant, l'ordre semble bien français par ce que je viens de rajouter une 10aine de fois la string 'AAAAAA' et celles-ci se retrouvent bien en tête des résultats - donc rien ne semble incorrect dans mon setup.
Je crois que je vais laisser tomber la question, en espérant très fort qu'une boîte à implantations pluri-nationales n'utilise pas mon programme… Ou bien (je viens juste d'y penser) j'ajoute le pays comme critère d'inclusion et je collate sur la locale du pays en question (idée bonne ou bien?)

gleu

Administrateur

Re : gros PB d'ordre avec order by

@Wi elle l'est, donc tout est bien en utf8.

Attention, l'encodage n'a rien à voir avec le tri. Le tri dépend de la locale, plus exactement de lc_collate.

à moins que le "poids" de la string ne soit évalué sur sa totalité (personne ne m'a d'ailleurs répondu là-dessus sur la ML PG

Tout dépend de la locale. En France, les règles de tri sont un peu particulières : voir http://fr.wikipedia.org/wiki/Fran%C3%A7 … C3.A9tique pour les détails. Enfin, je dis en France mais je pense que les règles sont partout particulières, et pas les mêmes sinon ce ne serait pas drôle

j'ajoute le pays comme critère d'inclusion et je collate sur la locale du pays en question (idée bonne ou bien?

Ça devrait fonctionner pour les locales installées. Le problème, c'est que PostgreSQL ne pourra trier que par rapport aux locales installées sur le système d'exploitation. Donc il faut prévoir ça.

---

Guillaume.

Jiff

Membre

Re : gros PB d'ordre avec order by

Le tri dépend de la locale, plus exactement de lc_collate

Donc elle est bien correcte (SHOW lc_collate renvoie fr_FR.utf8).

Enfin, je dis en France mais je pense que les règles sont partout particulières, et pas les mêmes sinon ce ne serait pas drôle

Wai, ça me rappelle une sortie ô combien vraie de Raymond Devos: "Mettez dix sages ensembles et vous obtenez un fou"

Le problème, c'est que PostgreSQL ne pourra trier que par rapport aux locales installées sur le système d'exploitation. Donc il faut prévoir ça.

Ah, c'est effectivement un point qui m'avait échappé!
Je vais réserver ça à une future version étant donné que c'est un ERP pour PME et qu'il est rare qu'elles soient internationales, sauf en frontalier, mais ça devrait le faire dans un premier temps.
Merci pour tes éclaircissements.