Mysql Impossible WHERE and enum

Question

I'm using enum as an alternative to check constraint which is still not supported by mysql. Could you clarify why optimizer doesn't take into account allowed values for enum ? For example,

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 explain shows impossible where in Extra, but the second doesn't. There is no way val can contain 'C' (as well as null), and it can be deducted from table definition without looking into the table.

Thanks.

Answer 1

I create the sample you gave with this:

drop database if exists a1ex07;
create database a1ex07;
use a1ex07
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'); 
explain select * from test_1 where val is null;  
explain select * from test_1 where val ='C'; 

Here is it loaded:

mysql> drop database if exists a1ex07;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database a1ex07;
Query OK, 1 row affected (0.00 sec)

mysql> use a1ex07
Database changed
mysql> CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_1(val) values ('A');
Query OK, 1 row affected (0.06 sec)

mysql> explain select * from test_1 where val is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> explain select * from test_1 where val ='C';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_1 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

In the first EXPLAIN plan, the Impossible where would be based on the definition of the column you gave, which is val enum('A','B') not null. This would help the MySQL Query Optimizer quickly rule out unneccesary evaluations, especially if the table had millions of rows.

In the second EXPLAIN plan, the Using where indicates that MySQL Query Optimizer would have to evaluate a potential value (C) to see if it resides in the table. I believe what you are looking for is for the MySQL Query Optimizer to check the definition of the column, which is enum('A','B'). It should but it probably doesn't. You should submit that as a bug report to see if Oracle, out of the goodness of their heart, will address this.

UPDATE 2012-01-12 14:08 EDT

I just looked around mysql website for any bugs regarding EXPLAIN and ENUM. At this time, there are no reported bugs of ths nature. You should submit this one ASAP.