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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7 and every other
• OS: Any
• 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