MySQL Empty table returns TRUE when querying with LEFT JOIN

Question

I just came across strange behavior (bug?) In MySQL 5.7, which does not appear in MySQL 5.5.

So inconvenient, I need an example to explain this.

• Create a table using the left join on 2 tables
• Make sure that the second table is empty (no records), but built using a static value written in one of the fields.

Left join with no conditions creates N rows (as expected)

Left join with a condition that never matches . ALSO produces N lines.

### 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 !

This behavior does not occur with MySQL 5.5

Is this a bug or some parameter in 5.5 that I forgot to set to 5.7?

Thank you for your time!

Answer 1

. , MySQL .

( - ):

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;

, 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;

order by k2 limit 3 - . (). (). .

, . , .

.

. DAG ( ), . - .

MySQL . . , , , .

Answer 2

. , , , output_merge optimizer_switch

SET @@optimizer_switch='block_nested_loop=off';