| Bug #64016 | Optimizer doesn't seem to take into account enum definition | ||
|---|---|---|---|
| Submitted: | 12 Jan 2012 19:38 | Modified: | 13 Jan 2012 6:57 |
| Reporter: | Oleksandr Konovalov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.5.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Jan 2012 6:57]
Valeriy Kravchuk
Thank you for the problem report.
Description: Explain doesn't show "impossible where" when comparing value of enum column with impossible value. How to repeat: CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null); insert into test_1(val) values ('A'); 1. explain select * from test_1 where val is null; 2. explain select * from test_1 where val ='C'; The first shows "impossible where" in Extra (which is correct because val defined as not null), but the second one doesn't. According to table definition, there is no way val can contain 'C'. Shouldn't optimizer generate the same 'Impossible where' ? Suggested fix: If it's supposed behaviour, it's probably worth mentioning in documentation. If not, performance gain from checking column definition in such cases can be huge especially for large tables.