Discussion :

[Séb.]

Hello à tous !
J'ai une requête UPDATE qui ne se comporte pas comme espéré.

Description :
- J'ai deux tables : stocks et ateliers.
- Un atelier est rattaché à un stock, un stock peut avoir plusieurs ateliers.
- Un stock dispose de farine, d'eau et de pain.
- A chaque appel du UPDATE le stock doit être incrémenté de 1 pain pour 2 farine et 1 eau dispos par atelier qui lui est rattaché.

Voici mon script de test :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DROP SCHEMA IF EXISTS boulangeries ;
CREATE SCHEMA boulangeries ;
USE boulangeries ;
 
CREATE TABLE stocks (
    stock_localisation VARCHAR(30),
    stock_farine INT,
    stock_eau INT,
    stock_pain INT,
    CONSTRAINT PRIMARY KEY(stock_localisation)
) ;
 
INSERT INTO stocks VALUES('Paris', 10, 6, 0),
                         ('Bordeaux', 3, 5, 1) ;
 
CREATE TABLE ateliers (
    atelier_localisation VARCHAR(30),
    atelier_stock VARCHAR(30),
    CONSTRAINT PRIMARY KEY(atelier_localisation),
    CONSTRAINT FOREIGN KEY(atelier_stock) REFERENCES stocks(stock_localisation)
) ;
 
INSERT INTO ateliers VALUES('Boulogne-Billancourt', 'Paris'),
                           ('Gradignan', 'Bordeaux'),
                           ('Nanterre', 'Paris') ;
 
-- Affiche les ressources des ateliers
CREATE PROCEDURE ATELIERS( )
SELECT ALL atelier_localisation AS atelier, stock_localisation AS stock, stock_farine AS farine, stock_eau AS eau, stock_pain AS pain
FROM ateliers INNER JOIN stocks ON atelier_stock = stock_localisation ;
 
CREATE PROCEDURE PRODUIRE( )
UPDATE stocks INNER JOIN ateliers ON stock_localisation = atelier_stock
SET stock_farine = stock_farine - 2, stock_eau = stock_eau - 1, stock_pain = stock_pain + 1
WHERE stock_farine >= 2 AND stock_eau >= 1 ;
 
CALL ATELIERS( ) ;
CALL PRODUIRE( ) ;
CALL ATELIERS( ) ;

Ce script me donne le résultat suivant :

Avant le UPDATE :

Code :

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

1
2
3
4
5
6
7
+----------------------+----------+--------+-----+------+
| atelier              | stock    | farine | eau | pain |
+----------------------+----------+--------+-----+------+
| Gradignan            | Bordeaux |      3 |   5 |    1 |
| Boulogne-Billancourt | Paris    |     10 |   6 |    0 |
| Nanterre             | Paris    |     10 |   6 |    0 |
+----------------------+----------+--------+-----+------+

Après le UPDATE :

Code :

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

1
2
3
4
5
6
7
+----------------------+----------+--------+-----+------+
| atelier              | stock    | farine | eau | pain |
+----------------------+----------+--------+-----+------+
| Gradignan            | Bordeaux |      1 |   4 |    2 |
| Boulogne-Billancourt | Paris    |      8 |   5 |    1 |
| Nanterre             | Paris    |      8 |   5 |    1 |
+----------------------+----------+--------+-----+------+

Alors que je m'attendais plutôt à :

Code :

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

1
2
3
4
5
6
7
+----------------------+----------+--------+-----+------+
| atelier              | stock    | farine | eau | pain |
+----------------------+----------+--------+-----+------+
| Gradignan            | Bordeaux |      1 |   4 |    2 |
| Boulogne-Billancourt | Paris    |      6 |   4 |    2 |
| Nanterre             | Paris    |      6 |   4 |    2 |
+----------------------+----------+--------+-----+------+

Je dois passer à côté de qquechose, mais quoi ? Une idée ? Merci

[ced]

Bonjour,

La requête de mise à jour a pourtant l'air correcte.
C'est plutôt le nombre d'appels de cette requête, en fonction des stocks, qui ne fait pas ce que tu souhaites, c'est ça ? Comment cette requête est-elle appelée ?

ced

[Séb.]

Salut ced !!

C'est plutôt le nombre d'appels de cette requête, en fonction des stocks, qui ne fait pas ce que tu souhaites, c'est ça ?

Là j'appelle la requête une unique fois et déjà je n'ai pas le résultat escompté.

Comment cette requête est-elle appelée ?

Elle est appelée depuis le script .sql collé plus haut qui me sert juste à faire qques tests sans m'encombrer de PHP. Rien d'autre pour le moment
(j'utilise MySQL 5.1)

J'ai l'impression que le pb vient de :

Code :

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

SET stock_farine = stock_farine - 2, stock_eau = stock_eau - 1, stock_pain = stock_pain + 1

C'est comme si la précédente modification du stock_farine d'un même stock n'était pas prise en compte pour l'enregistrement suivant.

J'illustre le fond de ma pensée...

La jointure du UPDATE donne le jeu de résultats suivant :

Code :

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

1
2
3
4
5
6
7
+----------------------+----------+--------+-----+------+
| atelier              | stock    | farine | eau | pain |
+----------------------+----------+--------+-----+------+
| Gradignan            | Bordeaux |      3 |   5 |    1 |
| Boulogne-Billancourt | Paris    |     10 |   6 |    0 |
| Nanterre             | Paris    |     10 |   6 |    0 |
+----------------------+----------+--------+-----+------+

Le UPDATE traite le 1er enregistrement :

Code :

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

1
2
3
4
5
6
7
  +----------------------+----------+--------+-----+------+
  | atelier              | stock    | farine | eau | pain |
  +----------------------+----------+--------+-----+------+
=>| Gradignan            | Bordeaux |      1 |   4 |    2 |
  | Boulogne-Billancourt | Paris    |     10 |   6 |    0 |
  | Nanterre             | Paris    |     10 |   6 |    0 |
  +----------------------+----------+--------+-----+------+

Là c'est bon. Puis traite le 2nd enregistrement :

Code :

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

1
2
3
4
5
6
7
  +----------------------+----------+--------+-----+------+
  | atelier              | stock    | farine | eau | pain |
  +----------------------+----------+--------+-----+------+
  | Gradignan            | Bordeaux |      1 |   4 |    2 |
=>| Boulogne-Billancourt | Paris    |      8 |   5 |    1 |
  | Nanterre             | Paris    |     10 |   6 |    0 |
  +----------------------+----------+--------+-----+------+

On voit que l'atelier de Boulogne et les stocks de Paris sont OK. Par contre l'atelier de Nanterre dont les stocks sont également à Paris ne sont pas à jour, d'où pour le traitement du 3e enregistrement :

Code :

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

1
2
3
4
5
6
7
  +----------------------+----------+--------+-----+------+
  | atelier              | stock    | farine | eau | pain |
  +----------------------+----------+--------+-----+------+
  | Gradignan            | Bordeaux |      1 |   4 |    2 |
  | Boulogne-Billancourt | Paris    |      8 |   5 |    1 |
=>| Nanterre             | Paris    |      8 |   5 |    1 |
  +----------------------+----------+--------+-----+------+

... ce qui expliquerait le résultat final.

Je m'attendais plutôt à ceci pour le 2nd UPDATE :

Code :

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

1
2
3
4
5
6
7
  +----------------------+----------+--------+-----+------+
  | atelier              | stock    | farine | eau | pain |
  +----------------------+----------+--------+-----+------+
  | Gradignan            | Bordeaux |      1 |   4 |    2 |
=>| Boulogne-Billancourt | Paris    |      8 |   5 |    1 |
  | Nanterre             | Paris    |      8 |   5 |    1 | (cascade)
  +----------------------+----------+--------+-----+------+

Et donc pour le 3e :

Code :

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

1
2
3
4
5
6
7
  +----------------------+----------+--------+-----+------+
  | atelier              | stock    | farine | eau | pain |
  +----------------------+----------+--------+-----+------+
  | Gradignan            | Bordeaux |      1 |   4 |    2 |
  | Boulogne-Billancourt | Paris    |      6 |   4 |    2 | (cascade)
=>| Nanterre             | Paris    |      6 |   4 |    2 |
  +----------------------+----------+--------+-----+------+

Mais non. C'est comme si les colonnes stock_* étaient rattachées à la table stocks par valeur et non pas par référence/pointeur.

Que penses-tu de ma "théorie" ? Penses-tu que c'est un comportement normal pour MySQL ? Pour un SGBD ? Je fais peut-être fausse piste... Si tel est le cas aurais-tu une idée pour une requête équivalente ? J'aimerais éviter les procédures stockées, curseurs & cie.

[ced]

Sans être un spécialiste de la question, je pense que la mise à jour se fait comme la jointure d'une requête... Non pas ligne à ligne après jointure, mais dans chacune des tables sur les lignes concernées.
Ainsi, l'update met à jour en une seule fois 2 lignes dans la table stocks et 3 lignes dans la table ateliers. Il ne passe pas de façon séquentielle...

ced

[Séb.]

Dans ce cas comment expliquer le résultat ? La requête est pourtant simple et m'a l'air bonne non ?
Je vais simplifier le cas au max.

[Séb.]

En tout cas merci pour ta participation