MySQL Bugs: #281: Random returned result by LEFT JOIN depending on preceding query.

Bug #281	Random returned result by LEFT JOIN depending on preceding query.
Submitted:	12 Apr 2003 12:47	Modified:	16 May 2003 5:33
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Same queries on same tables returns randomly different results depending on the preceding query.

How to repeat:
Download tables here :

ftp://support.mysql.com/pub/mysql/secret/leftjoin.tar.gz

Then, try the following :

(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(MATCH (ref_mag) AGAINST ('+scanneur*' IN BOOLEAN MODE)) GROUP BY produits.categorie);

Empty set (0.47 sec)

(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat,ref_mag FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(MATCH (ref_mag) AGAINST ('+scanneur*' IN BOOLEAN MODE)) GROUP BY produits.categorie);

Empty set (0.46 sec)

(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(MATCH (nom) AGAINST ('+scanneur*' IN BOOLEAN MODE)) GROUP BY produits.categorie) 
UNION
(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(marques.nom_marque LIKE 'scanneur%') GROUP BY produits.categorie)
UNION
(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(categories.nom_cat LIKE 'scanneur%') GROUP BY produits.categorie)
UNION
(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(MATCH (ref_mag) AGAINST ('+scanneur*' IN BOOLEAN MODE)) GROUP BY produits.categorie)
UNION
(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(MATCH (stocks.marque) AGAINST ('+scanneur*' IN BOOLEAN MODE)) GROUP BY produits.categorie) ORDER BY a DESC;

+-----+-----------+--------------------------------+
| a   | categorie | nom_cat                        |
+-----+-----------+--------------------------------+
| 806 |         5 | Moniteurs                      |
| 539 |         1 | Cartes graphiques              |
| 517 |         2 | Cartes mères                   |
| 383 |        29 | Supports de stockage           |
<cut>
|  34 |        40 | Barebones                      |
|  33 |        16 | Lecteurs CD                    |
|  32 |        43 | Racks                          |
|  13 |        31 | Volants                        |
+-----+-----------+--------------------------------+
45 rows in set (2.03 sec)

Now the same query than the first one :

(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(MATCH (ref_mag) AGAINST ('+scanneur*' IN BOOLEAN MODE)) GROUP BY produits.categorie);

+-----+-----------+--------------------------------+
| a   | categorie | nom_cat                        |
+-----+-----------+--------------------------------+
| 539 |         1 | Cartes graphiques              |
| 517 |         2 | Cartes mères                   |
| 346 |         3 | Disques durs                   |
| 375 |         4 | Mémoires                       |
<cut>
| 282 |        42 | Tuning                         |
|  32 |        43 | Racks                          |
|  85 |        44 | Watercooling                   |
|  44 |        45 | Imprimantes multifonctions     |
+-----+-----------+--------------------------------+
45 rows in set (0.64 sec)

Same query, with the ref_mag column added :

(SELECT COUNT(DISTINCT(produits.id_produit)) as a,produits.categorie,categories.nom_cat,ref_mag FROM categories LEFT JOIN 
produits ON produits.categorie=categories.id_cat LEFT JOIN stocks ON (produits.id_produit=stocks.id_produit AND stocks.maj=1) 
LEFT JOIN magasins ON stocks.magasin=magasins.id_mag,marques WHERE marques.id_marque=produits.marque AND 
(MATCH (ref_mag) AGAINST ('+scanneur*' IN BOOLEAN MODE)) GROUP BY produits.categorie);

Empty set (0.47 sec)

Note after testing several times my testcase, it appears the result could change from time to time, but if you try several times those differents queries, you should finally see the problem.

Hi,

I just realised this bug report looks similar to the #278 bugs, hope you could reproduce the problem and this testcase could help to fix both.

Regards,
  Jocelyn

This case is suspended until #278 is resolved. There is chance #278 fix will fix this one too. If not I will reopen it later.

I checked the uploaded files and noticed that the fulltext index in produits was corrupted.  One reason for this may be that the table was created in 4.1 and used in 4.0.  As the fulltext storage method has changed between these versions, this could explain this.

(I will talk with Sergei about adding checks so that one can't use files with the new fulltext format in 4.0).

To fix the table, I did in MySQL 4.0.13 do:

REPAIR TABLE produits USE_FRM;

After that, the first query in the bug report worked without any problems.