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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5.19 OS:Any
Assigned to: CPU Architecture:Any

[12 Jan 2012 19:38] Oleksandr Konovalov 
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.
[13 Jan 2012 6:57] Valeriy Kravchuk 
Thank you for the problem report.