{"id":1287,"date":"2012-07-19T09:22:13","date_gmt":"2012-07-19T08:22:13","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1287"},"modified":"2012-07-19T09:22:13","modified_gmt":"2012-07-19T08:22:13","slug":"le-mystere-du-concat","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2012\/07\/19\/le-mystere-du-concat\/","title":{"rendered":"Le myst\u00e8re du CONCAT"},"content":{"rendered":"

Un comportement bizarre m’a \u00e9t\u00e9 remont\u00e9e par un coll\u00e8gue.<\/p>\n

J’ai une table qui contient des IDs (pas la cl\u00e9 primaire):<\/p>\n

SELECT DISTINCT ID FROM Connection WHERE…\\G<\/span><\/address>\n
*************************** 1. row ***************************<\/span><\/address>\n
ID: 12345678<\/span><\/address>\n

 <\/p>\n

J’utilise la fonction CONCAT<\/a> pour concat\u00e9ner une cha\u00eene de caract\u00e8res et les IDs de la table:<\/p>\n

 <\/p>\n

 <\/p>\n

SELECT DISTINCT ID, CONCAT(‘Daz_’,ID) AS Concat FROM Connection WHERE…\\G<\/span><\/address>\n
*************************** 1. row ***************************<\/span><\/address>\n
ID: 12345678<\/span><\/address>\n
Concat: Daz_1234<\/strong><\/span><\/span><\/address>\n

 <\/p>\n

 <\/p>\n

Pour une raison qui m’\u00e9chappe, l’ID concat\u00e9n\u00e9 est tronqu\u00e9\u00a0! Bug ou feature\u00a0???<\/p>\n

 <\/p>\n

Si je rajoute une autre cha\u00eene de caract\u00e8res \u00e0 concat\u00e9ner, le r\u00e9sultat est encore plus surprenant…<\/p>\n

 <\/p>\n

SELECT DISTINCT ID, CONCAT(‘Daz_’,ID,’abcd’<\/strong><\/span>) AS Concat FROM Connection WHERE…\\G<\/span><\/address>\n
*************************** 1. row ***************************<\/span><\/address>\n
ID: 12345678<\/span><\/address>\n
Concat: Daz_12345678<\/strong><\/span><\/span><\/address>\n

 <\/p>\n

L’ajout de 4 caract\u00e8res me permet d’avoir la suite de mon nombre ie les 4 chiffres suivants (sic).<\/p>\n

 <\/p>\n

Si j\u2019agrandis la cha\u00eene, je peux enfin voir, dans le r\u00e9sultat de la concat\u00e9nation, les premiers caract\u00e8res de ma cha\u00eene.<\/p>\n

 <\/p>\n

SELECT DISTINCT ID, CONCAT(‘Daz_’,ID,’abcdef<\/strong><\/span>‘) AS Concat FROM Connection WHERE…\\G<\/span><\/address>\n
*************************** 1. row ***************************<\/span><\/address>\n
ID: 12345678<\/span><\/address>\n
Concat: Daz_12345678ab<\/strong><\/span><\/span><\/address>\n

 <\/p>\n

Bref c’est du grand n’importe quoi\u00a0!!! A ce stade, je suis persuad\u00e9 que ce n’est pas une feature \ud83d\ude42<\/p>\n

 <\/p>\n

Alors on fait quoi avec \u00e7a\u00a0?<\/h2>\n

 <\/p>\n

Avec ces 2 derniers exemples, un fix quick & dirty<\/em> permet, dans le contexte d’utilisation de mon coll\u00e8gue<\/span>, de contourner ce probl\u00e8me.<\/p>\n

Concat\u00e9ner avec des espaces, 4 dans son cas\u00a0:<\/p>\n

 <\/p>\n

SELECT DISTINCT ID, CONCAT(‘Daz_’,ID,‘ ‘<\/strong><\/span>) AS Concat FROM Connection WHERE…\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
ID: 12345678<\/address>\n
Concat: Daz_12345678<\/strong><\/span><\/address>\n

 <\/p>\n

 <\/p>\n

Mais si mon ID contient plus de 8 chiffres, \u00e7a ne fonctionne pas (bah oui quick & dirty j’ai dis)\u00a0!<\/p>\n

 <\/p>\n

Avant d’aller plus loin, il est rassurant de constater que sans DISTINCT<\/strong><\/span>, le comportement est plus proche du r\u00e9sultat attendu\u00a0:<\/p>\n

 <\/p>\n

SELECT ID, CONCAT(‘Daz_’,ID) AS Concat FROM Connection WHERE…\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
ID: 12345678<\/address>\n
Concat: Daz_12345678<\/strong><\/span><\/address>\n

 <\/p>\n

 <\/p>\n

J’ai \u00e9galement test\u00e9 avec un GROUP BY sur ID et… le comportement est le m\u00eame. Le probl\u00e8me semble venir de la table temporaire n\u00e9cessaire pour d\u00e9doublonner.<\/p>\n

 <\/p>\n

La solution est dans le CAST<\/h2>\n

 <\/p>\n

Le contournement est de fixer la taille de la sortie concat\u00e9n\u00e9e en utilisant la fonction CAST:<\/p>\n

SELECT DISTINCT ID, CAST(<\/strong><\/span>CONCAT(‘Daz_’,ID) AS CHAR(255) )<\/strong><\/span> AS Concat FROM Connection WHERE…\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
ID: 12345678<\/address>\n
Concat: Daz_12345678<\/strong><\/span><\/address>\n

 <\/p>\n

CQFD<\/p>\n

 <\/p>\n

N.B. Server version: 5.5.12-rel20.3-log Percona Server with XtraDB (GPL), Release rel20.3, Revision 118<\/p>\n

 <\/p>\n

P.S. Merci \u00e0 Nico pour m’avoir remont\u00e9 ce comportement bizarre<\/p>\n

P.S2.: ce comportement est \u00e0 rapprocher du Bug\u00a0#39543<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Un comportement bizarre m’a \u00e9t\u00e9 remont\u00e9e par un coll\u00e8gue.<\/p>\n

J’ai une table qui contient des IDs (pas la cl\u00e9 primaire):<\/p>\n

SELECT DISTINCT ID FROM Connection WHERE…\\G<\/p>\n

*************************** 1. row ***************************<\/p>\n

ID: 12345678<\/p>\n

J’utilise la fonction CONCAT pour concat\u00e9ner une cha\u00eene de caract\u00e8res et les IDs de la table:<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[88],"tags":[],"class_list":["post-1287","post","type-post","status-publish","format-standard","hentry","category-astuce"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-kL","jetpack-related-posts":[{"id":137,"url":"https:\/\/dasini.net\/blog\/2008\/11\/16\/enigme-order-by\/","url_meta":{"origin":1287,"position":0},"title":"\u00c9nigme… order by","author":"Olivier DASINI","date":"16 novembre 2008","format":false,"excerpt":"Cela part d'un probl\u00e8me tout simple, trier les donn\u00e9es d'une table: mysql> SELECT id, technologie FROM enigme;","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":56,"url":"https:\/\/dasini.net\/blog\/2008\/11\/02\/auto_increment-differences-myisam-innodb\/","url_meta":{"origin":1287,"position":1},"title":"AUTO_INCREMENT: Diff\u00e9rences MyISAM – InnoDB","author":"Olivier DASINI","date":"2 novembre 2008","format":false,"excerpt":"La clause, AUTO_INCREMENT, permet \u00e0 MySQL de g\u00e9n\u00e9rer un entier unique pour tout nouvel enregistrement d'une table. Cette clause ne peut se mettre que sur les champs de type entier, index\u00e9 et non nul. Elle est donc souvent utilis\u00e9e comme cl\u00e9 primaire. Cependant, sont comportement n'est pas tout \u00e0 fait\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1256,"url":"https:\/\/dasini.net\/blog\/2012\/05\/15\/jointure-vs-sous-requete\/","url_meta":{"origin":1287,"position":2},"title":"Jointure vs sous-requ\u00eate","author":"Olivier DASINI","date":"15 mai 2012","format":false,"excerpt":"MySQL est connu pour ne pas \u00eatre tr\u00e8s performant avec les sous-requ\u00eates. Ce n'est pas faux, et d'ailleurs c'est encore le cas avec MySQL 5.5. Le contournement consiste en g\u00e9n\u00e9ral \u00e0 r\u00e9\u00e9crire la requ\u00eate, certaines sous-requ\u00eates pouvant \u00eatre ais\u00e9ment r\u00e9\u00e9crite en jointure. C'est le cas de SELECT a FROM T1\u2026","rel":"","context":"Dans "Astuce"","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1102,"url":"https:\/\/dasini.net\/blog\/2011\/08\/29\/duplicate-key-cause-par-un-select\/","url_meta":{"origin":1287,"position":3},"title":"Duplicate key caus\u00e9 par un SELECT","author":"Olivier DASINI","date":"29 ao\u00fbt 2011","format":false,"excerpt":"Parmi les erreurs courantes en MySQL, ont trouve la fameuse ERROR 1062 (23000): Duplicate entry '2984' for key 'PRIMARY' caus\u00e9e par la tentative d'insertion d'une donn\u00e9es, d\u00e9j\u00e0 pr\u00e9sente, dans un colonne ayant une contrainte d'unicit\u00e9. Dans la m\u00eame famille il y a aussi l'erreur 1022...","rel":"","context":"Dans "Astuce"","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1163,"url":"https:\/\/dasini.net\/blog\/2012\/01\/09\/ameliorations-de-loptimiseur-dans-mariadb\/","url_meta":{"origin":1287,"position":4},"title":"Am\u00e9liorations de l’optimiseur dans MariaDB","author":"Olivier DASINI","date":"9 janvier 2012","format":false,"excerpt":"Les \u00e9quipes de MariaDB ont \u00e9norm\u00e9ment travaill\u00e9es sur l'optimiseur de la version 5.3, notamment en permettant une r\u00e9elle utilisation des sous-requ\u00eates. Voici un effet visuel de ces optimisations: Avec MySQL 5.5, l'utilisation de tables d\u00e9riv\u00e9es (type de sous-requ\u00eates dans la clause FROM d'un SELECT), donne le plan d'ex\u00e9cution suivant:","rel":"","context":"Dans "bench"","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1413,"url":"https:\/\/dasini.net\/blog\/2013\/04\/23\/utiliser-une-sous-requete-cest-mal\/","url_meta":{"origin":1287,"position":5},"title":"Utiliser une sous-requ\u00eate c’est mal ?","author":"Olivier DASINI","date":"23 avril 2013","format":false,"excerpt":"Jusqu\u2019en MySQL 5.5 inclus, l\u2019utilisation de sous-requ\u00eates peut, dans certain cas, \u00eatre la cause de probl\u00e8mes de performances (l\u2019optimiseur est bien meilleur en MySQL 5.6, MariaDB 5.5 et MariaDB 10). R\u00e9cemment j\u2019ai eu un souci en prod, apr\u00e8s une MEP, avec une requ\u00eate qui durait en moyenne plus de 1000\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1287"}],"version-history":[{"count":2,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1287\/revisions"}],"predecessor-version":[{"id":1289,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1287\/revisions\/1289"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}