Interrogation RIGHT JOIN résultat non attendu

**Delioneras** · 03/10/2017, 14h54

Bonjour à tous et à toutes,

J'aimerais vous soumettre une petite interrogation qui nous laisse passablement perplexe, mes collègues et moi, concernant la logique des jointures, et en particulier celle des RIGHT JOIN, dans un cas bien précis décrit ci-dessous.

Environnement: MySQL / PHP

Soit le modèle logique de données suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ref_etat_contrat_rec (rec_id, rec_code, rec_libelle)
rec_id INT auto_implement PRIMARY KEY
rec_code VARCHAR(16) NOT NULL
rec_libelle VARCHAR(50) NOT NULL
contrat_con (con_id, #rec_id, con_date_debut, con_date_fin)
con_id INT auto_implement PRIMARY KEY
rec_id INT FOREIGN KEY references ref_etat_contrat_rec (rec_id)
con_date_debut DATE NOT NULL
con_date_fin DATE 
suivi_traitement_stt (stt_id, stt_date_debut, stt_date_fin)
stt_id INT auto_implement PRIMARY KEY
stt_date_debut DATETIME NOT NULL
stt_date_fin DATETIME 
facture_fac(fac_id, #con_id, #stt_id, fac_date, fac_total_ht, fac_total_ttc)
fac_id INT auto_implement PRIMARY KEY
con_id INT FOREIGN KEY references contrat_con (con_id)
stt_id INT FOREIGN KEY references suivi_traitement_stt (stt_id)
fac_total_ht DECIMAL(16,4)
fac_total_ttc DECIMAL(16,4)

Populé de la manière suivante:
ref_etat_contrat_rec

rec_id	rec_code	rec_libelle
1	cree	Crée
2	actif	Actif
3	resil_en_cours	En cours de résiliation
4	resilie	Résilié
5	suppr	Supprimé

contrat_con

con_id	rec_id	con_date_debut	con_date_fin
24	2	2017-05-17	NULL
32	2	2017-09-01	NULL

suivi_traitement_stt

stt_id	stt_date_debut	stt_date_fin
76	2017-10-13 13:35:14	2017-10-13 13:37:28

facture_fac

fac_id	con_id	stt_id	fac_date	fac_total_ht	fac_total_ttc
4	32	76	2017-09-01	47.40	50.0070
5	32	76	2017-09-01	474.00	500.07

En débogage pour vérifier des statistiques générées, je cherche à vérifier le nombre de contrats actifs ou non statués ayant fait l'objet d'une facture. Pour se faire, j'ai à disposition la requête ci-dessous

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT 'Nombre de contrats actifs facturés' AS libelle, COUNT(con.con_id) AS NB
FROM contrat_con AS con
INNER JOIN (SELECT DISTINCT fac.con_id 
            FROM facture_fac AS fac
            WHERE fac.stt_id = 76             
            ) AS SRFac
    ON con.con_id = SRFac.con_id
RIGHT JOIN ref_etat_contrat_rec AS rec
    ON con.rec_id = rec.rec_id
WHERE con.rec_id = 2 OR con.rec_id IS NULL

En soit, la logique de la requête me semble fausse (la correction transforme le RIGHT JOIN en LEFT JOIN).
Pour autant, le résultat attendu de cette requête devrait être 1.
Hors, cette requête renvoie la valeur 0.
Si l'on enlève la clause OR con.rec_id IS NULL, on retrouve le bon résultat.
Même chose si l'on enlève la jointure interne.
Cependant, la requête nécessite bien les deux clauses, les enlever n'est donc pas envisageable.

J'ai appliqué le correctif énoncé, donc le problème ne se pose plus, mais la logique du résultat m'échappe, et j'aimerais bien comprendre.

Est-ce que quelqu'un saurait m'expliquer ce qu'il se passe dans cette requête pour que le résultat soit 0 et non pas 1 ?

En vous remerciant par avance,
Cordialement,
Thomas

**CinePhil** · 03/10/2017, 15h55

Si l'on enlève la clause OR con.rec_id IS NULL, on retrouve le bon résultat.

Lisez mon billet de blog sur les conditions de restrictions et les jointures externes et transposez-le avec RIGHT JOIN au lieu de LEFT JOIN...

**Delioneras** · 03/10/2017, 16h12

Bonjour CinePhil, et merci pour votre réponse.

Je prends bien note qu'une jointure externe avec une clause restrictive sur la table externe équivaut à une jointure interne.
Ce qui est logique au demeurant, puisque le moteur sélectionne les bons enregistrements correspondants à la jointure, puis ne garde que les éléments connus de la table externe.

Cependant, le cas décrit est sensiblement différent.
Tout d'abord, le critère de sélection est soit restrictif (WHERE champ = valeur) soit extensif (OR champ IS NULL).
Mais surtout, si l'on part du principe qu'une jointure externe couplée à une clause restrictive équivaut à une jointure interne, je devrais avoir le résultat attendu, à savoir 1, puisque le contrat attendu correspond à la fois à la jointure et au critère restrictif.

Hors la requête renvoie 0.

Pour être plus précis, lorsque j'exécute cette même requête en remplaçant le COUNT par une sélection des différents champs (* pour ne pas s'embêter), j'ai 5 lignes qui sont renvoyés.
Ces 5 lignes correspondent aux 5 enregistrements de la table ref_etat_contrat_con, tous les autres champs étant à NULL.

Alors que sur le SELECT *, je m'attendais à avoir 4 lignes à null pour les champs de ref_etat_contrat_con, plus une ligne renseignée pour la ligne Actif de la table ref_etat_contrat_con.

**CinePhil** · 03/10/2017, 16h44

Ma réponse était rapide. Alors il faudra que j'étudie un peu plus en détail votre cas mais je n'ai pas trop le temps maintenant.

**Delioneras** · 03/10/2017, 17h36

Rien ne presse, on est 3 au boulot à s'être fait avoir par cette requête

Comme énoncé, le correctif est déjà appliqué, on aimerait juste comprendre le pourquoi du comment, aucune urgence là dedans donc

**CinePhil** · 04/10/2017, 00h08

Je reviens sur votre problème...

je cherche à vérifier le nombre de contrats actifs ou non statués

Si je comprends bien la structure de votre table contrat_con, la clé étrangère rec_id est nullable, ce qui n'est pas vraiment top !

Mais au moins, pour répondre à votre besoin, la jointure avec ref_etat_contrat_rec est inutile :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT COUNT(c.c_id) AS nb_contrats
FROM contrat_con c
WHERE c.rec_id = 2
	OR c.rec_id IS NULL

ayant fait l'objet d'une facture

Et si vous voulez restreindre ce comptage aux contrats ayant fait l'objet d'au moins une facture :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT COUNT(c.c_id) AS nb_contrats
FROM contrat_con c
WHERE (c.rec_id = 2
	OR c.rec_id IS NULL)
	AND EXISTS
	(
		SELECT *
		FROM facture_fac f
		WHERE f.con_id = c.con_id
	)

Et si, enfin, vous voulez restreindre au suivi de traitement 76 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT COUNT(c.c_id) AS nb_contrats
FROM contrat_con c
WHERE (c.rec_id = 2
	OR c.rec_id IS NULL)
	AND EXISTS
	(
		SELECT *
		FROM facture_fac f
		WHERE f.con_id = c.con_id
			AND f.stt_id = 76
	)

**aieeeuuuuu** · 04/10/2017, 11h22

Envoyé par Delioneras

Est-ce que quelqu'un saurait m'expliquer ce qu'il se passe dans cette requête pour que le résultat soit 0 et non pas 1 ?

deux possibilités :
1/ vous avez fait une erreur dans les données que vous nous avez fournies. Vérifiez que vos données (et la requête) sont bien celles présentées

2/ vous êtes face a un bug de MySQL. peut-être dû à sa gestion assez personnelle du GROUP BY. que donne la requête sans la première colonne (pourtant constante). Et avec un GROUP BY () ?

Car en l'état, votre requête devrait bien renvoyer 1...

**Delioneras** · 04/10/2017, 11h55

Bonjour CinePhil, aieeeuuuuu

@CinePhil

En effet, en l'état, la jointure ne sert pas nécessairement.
A terme, dans le code, elle est pertinente pour remplacer la clause sur l'id par une clause sur le code, pour éviter les magic numbers dans le code applicatif.
La Foreign Key Nullable ... C'est moche, mais desiderata du client en l'occurrence. Afficher un warning, mais non bloquant à la création des données.
Bien évidemment, quand il s'agit de faire des traitements sur les données en question après leurs créations, les données deviennent obligatoires.
Le soft est toujours en phase de dév, les règles métiers peuvent être amenées à évoluer.

Cela étant, comme énoncé, le bug est en soi corrigé. Je cherche juste à comprendre l'erreur rencontrée avant correction.

@aieeeuuuuu
Merci pour votre réponse

Nop, les données présentées ici sont totalement exactes, de même que la requête, copiées en direct de la DB pour les premières, copiée depuis le source pour la requête.

Je viens de tester sans la première colonne constante, le résultat du COUNT est toujours 0. Pas de group by.

**CinePhil** · 04/10/2017, 12h20

A terme, dans le code, elle est pertinente pour remplacer la clause sur l'id par une clause sur le code, pour éviter les magic numbers dans le code applicatif.

WHAT ? !!

Clé étrangère nullable et code applicatif qui ne supporterait pas les identifiants numériques, si je comprends bien ? Ça commence à sentir le pourri par ici !

La Foreign Key Nullable ... C'est moche, mais desiderata du client en l'occurrence.

Pourquoi ne pas proposer un ref_etat_contrat_rec supplémentaire : "Sans état" ou bien "État à déterminer" ou un truc dans le genre ?
Ainsi, plus de clé étrangère nulle, exit le bonhomme Null de la BDD et moins de craintes sur les performances des traitements.

**Delioneras** · 04/10/2017, 18h11

Envoyé par CinePhil

WHAT ? !!

Clé étrangère nullable et code applicatif qui ne supporterait pas les identifiants numériques, si je comprends bien ? Ça commence à sentir le pourri par ici !

Je n'ai pas dit ça. C'est juste que nos clés sont des clés auto-incrémentés, et pour peu que, lors du passage en prod, les scripts SQL ne soient pas jouer dans le bon ordre d'exécution, on ne peut pas se baser sur les ID système, au niveau applicatif s'entend.
Donc un champ code, qui lui n'est pas appelé à être modifié par le système, c'est bien aussi, ça permet de retrouver l'ID en question.

Envoyé par CinePhil

Pourquoi ne pas proposer un ref_etat_contrat_rec supplémentaire : "Sans état" ou bien "État à déterminer" ou un truc dans le genre ?
Ainsi, plus de clé étrangère nulle, exit le bonhomme Null de la BDD et moins de craintes sur les performances des traitements.

En l'occurrence, parce que ce n'est pas le sujet de ma question ni de mon post.

Je ne suis pas en train d'épiloguer sur les bonnes pratiques SQL, il faudrait faire ceci, il faudrait faire cela.
Un des points forts de l'équipe dans laquelle je suis en ce moment, c'est qu'on catche les bugs très très vite (du moins par rapport à ce que j'ai pu connaître avant), et les correctifs vont en suivant. Sur ces points donc, je suis entièrement d'accord.

Je cherche juste à comprendre l'algèbre ensembliste qui, dans ce cas précis, provoque un résultat inattendu.

MySQL qui bugue, j'y ai pensé, mais en soi, cela me parait un peu facile comme explication, et je veux bien parier que cela ne suffit pas à expliquer le résultat de cette requête.

**CinePhil** · 04/10/2017, 19h12

lors du passage en prod, les scripts SQL ne soient pas jouer dans le bon ordre d'exécution, on ne peut pas se baser sur les ID système, au niveau applicatif s'entend.

Ben un ID auto-incrémenté n'a pas vocation à être modifié !
C'est juste un identifiant technique qui sert à rendre unique chaque ligne de la table et on s'en fout s'il y a des trous dans la liste des numéros qui n'ont de toute façon aucune signification.

Donc un champ code, qui lui n'est pas appelé à être modifié par le système,

Par le système (le SGBD) non, bien sûr ! Mais pour une raison ou une autre, il peut être changé par un gestionnaire métier. Ce n'est donc pas un bon outil pour faire des jointures entre tables alors que sur la clé primaire auto-incrémentée et la clé étrangère qui la référence, oui, puisque l'auto-incrément ne sera en principe jamais changé.

ça permet de retrouver l'ID en question

Donc votre application se base bien sur l'ID pour, par exemple, afficher la facture d'identifiant 8124 ou l'état de traitement 2.

EDIT : Je viens de créer vos tables et d'y saisir vos données puis d'exécuter votre requête et j'obtiens 1 en résultat.

Comme vous n'avez donné qu'un extrait des données, peut-être que c'est sur la totalité des données que se pose le problème.

**Delioneras** · 04/10/2017, 19h45

Envoyé par CinePhil

Ben un ID auto-incrémenté n'a pas vocation à être modifié !
C'est juste un identifiant technique qui sert à rendre unique chaque ligne de la table et on s'en fout s'il y a des trous dans la liste des numéros qui n'ont de toute façon aucune signification.

Par le système (le SGBD) non, bien sûr ! Mais pour une raison ou une autre, il peut être changé par un gestionnaire métier. Ce n'est donc pas un bon outil pour faire des jointures entre tables alors que sur la clé primaire auto-incrémentée et la clé étrangère qui la référence, oui, puisque l'auto-incrément ne sera en principe jamais changé.

Donc votre application se base bien sur l'ID pour, par exemple, afficher la facture d'identifiant 8124 ou l'état de traitement 2.

Je n'ai jamais parlé de remplacer un ID système par un code alpha moins performant, j'ai parlé de remplacer la clause restrictivede l'ID dans le where par un code technique (qui n'est pas disponible aux utilisateurs clients).

Envoyé par CinePhil

EDIT : Je viens de créer vos tables et d'y saisir vos données puis d'exécuter votre requête et j'obtiens 1 en résultat.

Comme vous n'avez donné qu'un extrait des données, peut-être que c'est sur la totalité des données que se pose le problème.

Les données fournies sont l'exhaustivité du traitement de test, je n'ai rien d'autre en base (base purgée de toutes données, réinsertion des données ci-dessus, retest, résultat inattendu de 0, avant de poster le premier message du sujet).

**CinePhil** · 04/10/2017, 19h48

Alors bug MySQL.

Je suis en MariaDB :
mysql Ver 15.1 Distrib 10.0.32-MariaDB, for Linux (x86_64) using readline 5.1

Interrogation RIGHT JOIN résultat non attendu

Langage SQL

Discussions similaires

Partager

Partager