Comportement étrange sur le UNION

Bonjour à tous.

Je travail sur deux requêtes SQL qui sont tout à fait normales. Séparément, les deux s’exécutent en moins de 1 seconde.
Par contre dès que je mets UNION entre les deux, je me retrouve avec :
"Erreur dans le traitement de la requête
Code d'erreur : 500
Texte de l'erreur : Internal Server Error" ... :aie:

Voici ma première requête :

Code:

---

1 2 3 4 5 6 7 8

SELECT Trim(BPO.Nom) LB_NOM, Trim(BPO.Prenom) LB_PREN, IFNULL((SELECT DISTINCT Venue_2010 FROM b_personne_origine B10 WHERE B10.Nom_Prenom = BPO.Nom_Prenom AND B10.Venue_2010 ="1"),'') V2010, IFNULL((SELECT DISTINCT Venue_2011 FROM b_personne_origine B11 WHERE B11.Nom_Prenom = BPO.Nom_Prenom AND B11.Venue_2011 ="1"),'') V2011, IFNULL((SELECT DISTINCT Venue_2012 FROM b_personne_origine B12 WHERE B12.Nom_Prenom = BPO.Nom_Prenom AND B12.Venue_2012 ="1"),'') V2012, IFNULL((SELECT DISTINCT Venue_2013 FROM b_personne_origine B13 WHERE B13.Nom_Prenom = BPO.Nom_Prenom AND B13.Venue_2013 ="1"),'') V2013 FROM `b_personne_origine` BPO WHERE BPO.`Nom` <>'' and BPO.`Prenom` <>'';

---

Voici ma seconde requête :

Code:

---

1 2 3

SELECT B.Nom, B.Prenom, B.Venue_2010, B.Venue_2011, B.Venue_2012, B.Venue_2013 FROM `b_personne_origine` B WHERE B.`Nom` ='' and B.`Prenom` ='';

---

La première requête renvoie environ 12000 lignes.
La seconde retourne 17 lignes.

Je ne comprends pas pourquoi quand je mets un UNION ALL pour réunir les deux résultats, j'ai un message d'erreur. :aie:

Quelqu'un aurait une suggestion?

Merci d'avance à tous ;)

Erreur 500 ? c'est votre serveur applicatif. Il faudrait récupérer l'erreur fournie par le serveur SQL.

Est-ce que votre requete est construite par votre code (php, asp ?) en concaténant vos requêtes ?
Dans ce cas, il est simplement possible qu'il manque une espace avant ou après le UNION ALL... classique.

Ca ou le ";" en fin de la première requête qui n'a pas été enlevé.

Bonjour.

Et merci à vous pour votre aide :merci:

Cependant :

Citation:

---

Erreur 500 ? c'est votre serveur applicatif. Il faudrait récupérer l'erreur fournie par le serveur SQL.

Est-ce que votre requete est construite par votre code (php, asp ?) en concaténant vos requêtes ?
Dans ce cas, il est simplement possible qu'il manque une espace avant ou après le UNION ALL... classique.

---

En fait je travailles pour le moment en local sur mon poste avec un WampServer d'installé. Ces requêtes n'ont pas vocation à être utilisées par un code. Je les exécutes directement dans l'onglet "SQL" de ma base de données sur Mysql (PHPMyadmin).
Quand je clique sur le bouton "Exécuter" du "gestionnaire de requête SQL" de Mysql, un message à l'écran s'affiche et annonce "Chargement en cour". Au bout d'un moment le message est remplacé par :
"Erreur dans le traitement de la requête
Code d'erreur : 500
Texte de l'erreur : Internal Server Error"

Citation:

---

Ca ou le ";" en fin de la première requête qui n'a pas été enlevé.

---

Voici un copié-collé de la requête entière tel qu'écrite avec le UNION ALL :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

SELECT Trim(BPO.Nom) LB_NOM, Trim(BPO.Prenom) LB_PREN, IFNULL((SELECT DISTINCT Venue_2010 FROM b_personne_origine B10 WHERE B10.Nom_Prenom = BPO.Nom_Prenom AND B10.Venue_2010 ="1"),'') V2010, IFNULL((SELECT DISTINCT Venue_2011 FROM b_personne_origine B11 WHERE B11.Nom_Prenom = BPO.Nom_Prenom AND B11.Venue_2011 ="1"),'') V2011, IFNULL((SELECT DISTINCT Venue_2012 FROM b_personne_origine B12 WHERE B12.Nom_Prenom = BPO.Nom_Prenom AND B12.Venue_2012 ="1"),'') V2012, IFNULL((SELECT DISTINCT Venue_2013 FROM b_personne_origine B13 WHERE B13.Nom_Prenom = BPO.Nom_Prenom AND B13.Venue_2013 ="1"),'') V2013 FROM `b_personne_origine` BPO WHERE BPO.`Nom` <>'' AND BPO.`Prenom` <>'' UNION ALL SELECT B.Nom, B.Prenom, B.Venue_2010, B.Venue_2011, B.Venue_2012, B.Venue_2013 FROM `b_personne_origine` B WHERE B.`Nom` ='' AND B.`Prenom` ='';

---

Je n'ai donc plus le ";" à la fin de la première requête, ni un espace manquant.

Ce qui est d'autan plus étrange, c'est que si je modifie la clause WHERE de la première requête comme suit :

Code:

---

WHERE BPO.`Nom` ='TATA' AND BPO.`Prenom` ='TOTO'

---

Ça fonctionne bien (du coup la première requête retourne seulement 2-3 résultats)

Le problème me fait donc pensé à un time out. Cela dit 12000 résultat ou 12017 ne fait pas une grand différence. J'ai déjà eu des requêtes à plus de 30000 résultats sans soucis.
De plus, pourquoi un temps d'exécution si long alors que, séparées, chaque requête met moins de 1 seconde?

Une subtilité dans le UNION qui m'aurai échappée? Le union n'exécute pas chaque requête séparément? Puis concatène les résultats?

En tous cas merci encore à vous pour votre aide.

Effectivement c'est assez surprenant. Normalement le UNION ALL est effectivement indolore.
Il faudrait afficher le plan d'exécution pour voir ce que fait MySQL.


Sinon petite question, j'ai un peu de mal à comprendre la finalité de ta requête.
Tu remontes pour chaque ligne de ta table avec Nom_Prenom non vide, une ligne avec V2010-3 ayant toujours la même valeur (1 ou '') et une ligne pour chaque ligne de ta table avec Nom_Prenom vide mais avec cette fois potentiellement des valeurs différentes pour V2010-3.
C'est bien cela?

Donc si tu as

Citation:

---

Dupont_Pierre 1 null null null
Dupont_Pierre null 1 1 1
'' 1 null null null
'' null 1 1 1

---

Tu auras en sortie

Citation:

---

Dupont_Pierre 1 1 1 1
Dupont_Pierre 1 1 1 1
'' 1 null null null
'' null 1 1 1

---

J'imagine qu'il y a une raison :)

Alors c'est la première fois que j'utilise le plan d'exécution. Pour cela j'ai juste rajouté "EXPLAIN" devant ma première requête SQL.

Voici le résultat :

Code:

---

1 2 3 4 5 6 7 8

id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY BPO ALL NULL NULL NULL NULL 11913 Using where 5 DEPENDENT SUBQUERY B13 ALL NULL NULL NULL NULL 11913 Using where 4 DEPENDENT SUBQUERY B12 ALL NULL NULL NULL NULL 11913 Using where 3 DEPENDENT SUBQUERY B11 ALL NULL NULL NULL NULL 11913 Using where 2 DEPENDENT SUBQUERY B10 ALL NULL NULL NULL NULL 11913 Using where 6 UNION B ALL NULL NULL NULL NULL 11913 Using where NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL Using temporary

---

J'avoue que je ne comprend pas tout à fait ce que cela implique. Perso j'en déduirai que la deuxième requête retourne un résultat qui n'arrive pas à être "concaténé" avec le résultat de la première. Mais j'avoue que je n'en suis pas sur :?

Sinon pour le pourquoi du comment de cette requête (je vois que Mr est curieux;)) :
-C'est une base "moulinette" qui permet de mettre au bon format des données venant d'un système A pour les intégrer à un système B.
-Cette requête sera utilisée pour remplir une view qui elle même permettra de faire d'autres traitements.

-Chaque couple "Nom" / "Prenom" (oui c'est pas géré sur un ID ... c'est moche ... mais je prends les données comme on me les donne ... je doit donc faire avec :aie:) peut avoir jusqu'à 4 lignes.
-Chaque ligne comporte les champs "Venue_2010" "Venue_2011" "Venue_2012" et "Venue_2013".
-Chacune de ses lignes peut avoir des "1" ou "" dans ses 4 colonnes. La même information peut être redondante sur plusieurs lignes.

L'idée de la première requête est, pour chaque lignes différentes d'un couple "Nom" / "Prenom", avoir les mêmes informations dans les 4 champs "Venue_....". On met donc un "1" dans le champ si une des autres ligne de cette personne à un "1" dans ce champ.

Exemple :
DUPONT MARTIN 1 0 0 1
DUPONT MARTIN 0 0 0 1
DUPONT MARTIN 0 1 0 0

Deviendra :
DUPONT MARTIN 1 1 0 1
DUPONT MARTIN 1 1 0 1
DUPONT MARTIN 1 1 0 1

(le 0 = "" normalement)

Seulement le problème viens du fait que certaines lignes n'ont pas de "Prenom" ni de "Nom". Ses lignes là par contre on ne veut donc pas les regrouper car on ne sait pas si elles concernent la même personne ou non. On les exclut donc de la première requête :

Code:

---

1 2	WHERE BPO.`Nom` <>'' AND BPO.`Prenom` <>''

---

Et on fait la seconde requête qui les ajoute tel quelles sont originalement dans le système A.

A vrai dire je pourrai passer par deux requêtes distinctes, créer une table temporaire, et insérer les résultats des deux requêtes séparément.

Cela dit je suis tombé sur le problème qui m'a fait écrire ce post ... et j'aime bien comprendre quand ça ne fonctionne pas ... car bien souvent c'est dans des cas comme celui-la qu'on va comprendre le fonctionnement approfondi (ou une petite nuance) de certains éléments.

Et comme je me suis remis au SQL depuis peu ... ça me permet d'apprendre des choses :mrgreen:

Et pis comme j'aime faire un travail propre ... si je peux fournie un script final qui créer une View d'un seul coup c'est quand même plus propre que la table temporaire et les deux requêtes d'insertion ;)

Dans le plan d'exécution on voit qu'il utilise une table temporaire pour faire le UNION ALL.
Ce qui n'est jamais top niveau optimisation.

Par contre il semblerait que ce soit un comportement normal sur MySQL en version < 5.7 mais je n'ai pas reproduit sur ma version 5.0 ou sur une requête similaire je ne passe pas par une telle table.
Du coup je sais pas trop si c'est normal ou pas, et je ne pourrai pas plus t'aider dans cette direction.

Dans tous les cas, avec ou sans table temporaire, cela ne devrait pas provoquer d'erreur, la volumétrie est bien trop faible. As-tu suffisement d'espace libre sur ta base ? Peut être qu'il n'arrive simplement pas à créer/remplir la table temporaire. Regarde tes logs MySQL aussi au cas ou une autre info apparaisse.



Bon sinon, et la c'est plus mon domaine, il doit bien y avoir moyen de faire ta requête sans union all. Je réfléchit à cela après manger.

Du coup pour éviter de passer par un UNION ALL ... il y a la possibilité d'utiliser CASE ... mais j'ai pas le temps de vérifier le résultat ... les collègues m'attendent ;).
Mais ça devrai ressembler à un truc du genre :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT BPO.Nom LB_NOM, BPO.Prenom LB_PREN, CASE WHEN BPO.Nom <>'' and BPO.Prenom <>'' THEN IFNULL((SELECT DISTINCT Venue_2010 FROM b_personne_origine B10 WHERE B10.Nom_Prenom = BPO.Nom_Prenom AND B10.Venue_2010 ="1"),'') ELSE BPO.Venue_2010 END V2010, CASE WHEN BPO.Nom <>'' and BPO.Prenom <>'' THEN IFNULL((SELECT DISTINCT Venue_2011 FROM b_personne_origine B11 WHERE B11.Nom_Prenom = BPO.Nom_Prenom AND B11.Venue_2011 ="1"),'') ELSE BPO.Venue_2011 END V2011, CASE WHEN BPO.Nom <>'' and BPO.Prenom <>'' THEN IFNULL((SELECT DISTINCT Venue_2012 FROM b_personne_origine B12 WHERE B12.Nom_Prenom = BPO.Nom_Prenom AND B12.Venue_2012 ="1"),'') ELSE BPO.Venue_2012 END V2012, CASE WHEN BPO.Nom <>'' and BPO.Prenom <>'' THEN IFNULL((SELECT DISTINCT Venue_2013 FROM b_personne_origine B13 WHERE B13.Nom_Prenom = BPO.Nom_Prenom AND B13.Venue_2013 ="1"),'') ELSE BPO.Venue_2013 END V2013 FROM `b_personne_origine` BPO'

---

Mais pareil je vérifierai après mangé.
En tout cas merci pour ton aide ... et merci pour l'idée de faire ça sans passer par un UNION ... j'avais juste pas pensé à essayer de le faire :oops:

Sinon, j'ai largement assez d'espace sur mon disque pour recevoir la table temporaire (400Go de libre:mrgreen:) ... et le log "Mysql.log" ne m'apprend rien d'autre que le démarrage du server Mysql. (mais j'ai regardé que dans le dossier "wamp\logs")

Bon j'ai tenté une autre approche.

J'ai testé rapidement ça à l'air d'avoir le comportement voulu.

Code:

---

1 2 3 4 5 6

set @curRow=0; SELECT Trim(BPO.Nom) LB_NOM, Trim(BPO.Prenom) LB_PREN, max(BPO.Venue_2010), max(BPO.Venue_2011), max(BPO.Venue_2012), max(BPO.Venue_2013) FROM b_personne_origine BPO GROUP BY IF(Nom_Prenom<>'', Nom_Prenom, @curRow:=@curRow+1);

---

En gros on prend le max des Venue_xxxx (vu que c'est vide ou 1, le max renverra 1 si il y a au moins un 1). La feinte étant dans le GROUP BY. Au lieu de grouper par Nom_Prenom ce qui ne convient pas à cause des valeurs vides, on groupe par Nom_Prenom si Nom_Prenom n'est pas vide et sinon on group par un indice qui va incrémenter tout seul (idéalement on aurait groupé par ID mais tu n'en as pas et la notion de ROWID n'existe pas en MySQL)

L'avantage c'est qu'en plus tu n'auras qu'une seule ligne par Nom_Prenom non null au lieu d'avoir plusieurs lignes identiques (je suis parti du principe que tu n'avais pas besoin de l'information sur le nombre de ligne par Nom_Prenom, au pire tu ajoutes un count(1) pour l'avoir).


Si par hasard la feinte avec le @curRow ne convient pas (car deux instructions) tu peux mettre un rand() dans le group by à la place. C'est un peu plus risqué vu qu'il existe une probabilité non nulle d'avoir deux fois la même valeur.
Pour augmenter tes chances tu peux grouper par Nom puis Prénom au lieu de Nom_Prenom ce qui fait que tu auras ainsi 2 rand() et la les probabilités d'avoir les deux mêmes sont quasi nulles au vue de ta volumétrie.

Et bin j'ai qu'une chose à dire ... :merci:

J'avais pas du tout pensé à une approche comme la tienne. Et c'est beaucoup plus lisible au final :mrgreen:

Par contre je ne savais pas qu'on pouvais utiliser SET de cette manière, pour déclarer une variable. D'ailleurs je ne savais pas qu'on pouvais déclarer des variables tout cours :aie:. Apparemment c'est propre à MySQL cette syntaxe. Mais ça me va très bien :P

Par contre tu dis qu'il suffit d'ajouter un count(1) pour avoir toutes les lignes ... cependant je ne vois pas du comment faire ça.
Dans tous les cas, les champs seront groupés sur le "Nom_Prenom", donc je me retrouverai toujours avec une seule ligne pour chaque "Nom_Prenom". Ou alors il y a quelque chose qui m'échappe :aie: (ça doit faire 3 ans que j'avais pas fait de SQL ... c'est peut-être ça aussi :mrgreen:) Donc si tu pouvais me montrer ça je prendrai l'info avec plaisir :P

Mais sinon merci pour ton aide, la requête comme tu m'a donné me suffit pour le moment ;) ... sans compté que du coup je suis aller me renseigner sur RAND() aussi ... je suis vraiment limité en SQL ... je connaissais pas non plus.

Bref, c'est cool j'ai appris pleins de trucs 8-)

Citation:

---

Envoyé par tribaleur

Par contre tu dis qu'il suffit d'ajouter un count(1) pour avoir toutes les lignes ... cependant je ne vois pas du comment faire ça.
Dans tous les cas, les champs seront groupés sur le "Nom_Prenom", donc je me retrouverai toujours avec une seule ligne pour chaque "Nom_Prenom". Ou alors il y a quelque chose qui m'échappe :aie: (ça doit faire 3 ans que j'avais pas fait de SQL ... c'est peut-être ça aussi :mrgreen:) Donc si tu pouvais me montrer ça je prendrai l'info avec plaisir :P

---

Le count te fera pas avoir les lignes en plus mais tu auras l'information du nombre de ligne qui peut être une information utile que tu perds avec le regroupement.

Bon bin tu me rassure :roll:

Je me disais que quelque chose m'échappais ... mais finalement c'est juste que j'avais mal compris ce que tu voulais dire ... :mrgreen:

Dans tous les cas ta requête m'intéresse quand même ... donc un grand :merci:

Bon le point négatif c'est que, finalement, je ne sais toujours pas pourquoi ma requête avec le UNION ALL ne fonctionne pas.

Tant pis. Je mets quand même Résolut car au j'ai quand même une alternative qui me conviens bien.

Bonne fin de journée et Bon weekend!!!