Bug #82363 impossible const condition still followed in a JOIN
Submitted: 27 Jul 2016 19:28 Modified: 28 Jul 2016 8:09
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7 and every other OS:Any
Assigned to: CPU Architecture:Any

[27 Jul 2016 19:28] Domas Mituzas
Description:
Given a query:

select * from master 
left join leaf1 on leaf=1 and master.a=leaf1.a 
left join leaf2 on leaf=2 and master.a=leaf2.a 
where master.a in (5,6);

with following master table:

mysql> select * from master;
+---+------+
| a | leaf |
+---+------+
| 5 |    1 |
| 6 |    2 |
| 7 |    3 |
+---+------+
3 rows in set (0.00 sec)

will result in 6 Handler_read_key storage engine accesses, even if ON condition fails basic evaluation. That query should be possible with 4 index dives and not 6. 

If you request a=7, it will do 3 index dives, even if one would be enough, so the query ends up being 3x less efficient. 

These index accesses should be optimized away. 

How to repeat:
Put impossible conditions into JOIN rules, notice how they're not being optimized away neither during optimization nor execution stage.

Suggested fix:
Don't dive into an index, if const condition fails.
[28 Jul 2016 8:09] MySQL Verification Team
Hello Domas,

Thank you for the report and feedback!

Thanks,
Umesh