La table vide MySQL renvoie VRAI lorsqu'elle est interrogée à l'aide d'un LEFT JOIN.

Question

Je viens de rencontrer un comportement étrange (bug ?) dans MySQL 5.7 qui n'apparaît pas dans MySQL 5.5.

Si maladroit, j'ai besoin d'un exemple pour l'expliquer .

1. Créer une table en utilisant une jointure gauche sur 2 tables
2. Assurez-vous que la deuxième table est vide (pas d'enregistrements) mais qu'elle est construite en ayant une valeur statique écrite dans l'un de ses champs.

La gauche se joint à aucune condition produit N lignes (comme prévu)

La gauche se joint à une condition qui ne correspond jamais ALSO produit N lignes.

### EXAMPLE ###

## CREATE TABLES

create table PCPL (K1 int);  ## Table 1
create table AUX (K2 int);   ## Table 2

## FILL IN TABLES

insert into PCPL values (1),(2),(3);    ## fill main table with 3 values
truncate table AUX;                     ## No need to do this, just to make things clearer

## TEST 1 : "Dry Left join" => RESULT OK : Resulting Table has 3 rows

select PCPL.K1 as K1 , DERIVED.K2  as K2 
from PCPL
LEFT JOIN (select K2, 1 as staticValue from AUX) DERIVED
ON PCPL.K1 = DERIVED.K2; 

+------+------+
| K1   | K2   |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
+------+------+
3 rows in set (0,00 sec)

## TEST 2 : "Never matching condition" => STRANGE : Resulting Table NOT empty 

select PCPL.K1 as K1 , DERIVED.K2  as K2  
from PCPL
LEFT JOIN (select K2, 1 as staticValue from AUX) DERIVED
ON PCPL.K1 = DERIVED.K2
where staticValue=1;   ##### THIS CONDITION IS NEVER MET SINCE TABLE AUX IS EMPTY

+------+------+
| K1   | K2   |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
+------+------+
3 rows in set (0,00 sec)

THIS SHOULDN'T HAPPEN !

Ce comportement ne se produit pas avec MySQL 5.5.

Est-ce un bogue ou un paramètre de la version 5.5 que j'ai oublié de régler dans la version 5.7 ?

Merci pour votre temps !

Answer 1

Cela ressemble spécifiquement à un bogue dans la fusion de sous-requêtes. Il s'agit d'une stratégie qui permet à MySQL d'éviter la surcharge liée à la matérialisation des sous-requêtes.

Considérons les deux requêtes suivantes (la première est l'originale) :

select PCPL.K1 as K1, DERIVED.K2 as K2,
       (CASE WHEN DERIVED.K2 IS NULL THEN 'is null' ELSE 'not null' END) as K2_null,
       staticValue
       (CASE WHEN staticValue IS NULL THEN 'is null' ELSE 'not null' END) as staticValue_null
from PCPL LEFT JOIN
     (select K2, 1 as staticValue from AUX) DERIVED
     ON PCPL.K1 = DERIVED.K2
where staticValue IS NOT NULL;

Et cette forme modifiée où la seule différence dans DERIVED :

select PCPL.K1 as K1, DERIVED.K2 as K2,
       (CASE WHEN DERIVED.K2 IS NULL THEN 'is null' ELSE 'not null' END) as K2_null,
       staticValue
       (CASE WHEN staticValue IS NULL THEN 'is null' ELSE 'not null' END) as staticValue_null
from PCPL LEFT JOIN
     (select K2, 1 as staticValue from AUX order by k2 limit 3) DERIVED
-------------------------------------------^XXXXXXXXXXXXXXXXXX
     ON PCPL.K1 = DERIVED.K2
where staticValue IS NOT NULL;

Le but de order by k2 limit 3 est de forcer la matérialisation de la sous-requête. La première version renvoie trois lignes (incorrect). La deuxième version renvoie zéro ligne (correct). Dans les deux cas, la sous-requête renvoie le même ensemble de lignes.

La différence est que la matérialisation forcée règle le problème. Par conséquent, cela ressemble à un bogue dans la fusion de sous-requêtes.

Vous pouvez lire des informations sur la fusion de sous-requêtes dans le document documentation .

Une petite note éditoriale. Les bases de données sophistiquées analysent une requête en un DAG (graphe acyclique dirigé) qui décrit le traitement. Elles ont ensuite des règles sophistiquées pour pousser les opérations à travers le graphe -- en particulier les opérations de filtrage et les calculs de colonnes.

MySQL part d'un modèle plus simple de la requête. Les développeurs s'orientent vers un modèle plus sophistiqué. De petits bogues comme celui-ci ne sont pas inattendus, mais il faudra une version ou deux pour les résoudre.

Answer 2

Merci beaucoup. En attendant que le bogue soit corrigé, on peut l'empêcher de se produire en désactivant le paramètre dérivé_fusion dans la variable commutateur_optimiseur

SET @@optimizer_switch='block_nested_loop=off';