Description:
The optimizer selects a non-optimal execution plan for some queries
containing IS NULL predicate for a non-nullable attribute.
This case can be reproduced for version 4.0.17 as well.
How to repeat:
Run the following mysql commands to create and the populate the case database:
create table t1 (a int not null, b int not null, undex idx(a));
insert into t1 values
(1,1), (2,2), (3,3), (4,4), (5,5), (6,6),
(7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
Then run:
explain select * from t1 where a between 2 and 3 or b is null;
You'll get:
+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+-------------+
| t1 | ALL | idx | NULL | NULL | NULL | 12 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+
This is not the best plan as b is a non-nullable column and the IS NULL
predicate can be removed from the query.
When we remove the predicate we have:
mysql> explain select * from t1 where a between 2 and 3;
+-------+-------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+-------------+
| t1 | range | idx | idx | 4 | NULL | 2 | Using where |
+-------+-------+---------------+------+---------+------+------+-------------+
Suggested fix:
This wrong choice of the optimizer happens due to an incostistent state of
used_table_cache and const_item_cache (both of then are 0) in Item_func_isnull
objects for IS NULL predicates containing a non-nullable attribute.
Item_func_isnull::update_used_tables is to be fixed:
virtual void update_used_tables()
{
if (!args[0]->maybe_null)
{
used_tables_cache= 0; /* is always false */
const_item_cache= 1; /* line to be added for this fix */
cached_value= (longlong) 0;
}
else
{
args[0]->update_used_tables();
if (!(used_tables_cache=args[0]->used_tables()))
{
/* Remember if the value is always NULL or never NULL */
cached_value= (longlong) args[0]->is_null();
}
}
Description: The optimizer selects a non-optimal execution plan for some queries containing IS NULL predicate for a non-nullable attribute. This case can be reproduced for version 4.0.17 as well. How to repeat: Run the following mysql commands to create and the populate the case database: create table t1 (a int not null, b int not null, undex idx(a)); insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10), (11,11), (12,12); Then run: explain select * from t1 where a between 2 and 3 or b is null; You'll get: +-------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+------+-------------+ | t1 | ALL | idx | NULL | NULL | NULL | 12 | Using where | +-------+------+---------------+------+---------+------+------+-------------+ This is not the best plan as b is a non-nullable column and the IS NULL predicate can be removed from the query. When we remove the predicate we have: mysql> explain select * from t1 where a between 2 and 3; +-------+-------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+------+---------+------+------+-------------+ | t1 | range | idx | idx | 4 | NULL | 2 | Using where | +-------+-------+---------------+------+---------+------+------+-------------+ Suggested fix: This wrong choice of the optimizer happens due to an incostistent state of used_table_cache and const_item_cache (both of then are 0) in Item_func_isnull objects for IS NULL predicates containing a non-nullable attribute. Item_func_isnull::update_used_tables is to be fixed: virtual void update_used_tables() { if (!args[0]->maybe_null) { used_tables_cache= 0; /* is always false */ const_item_cache= 1; /* line to be added for this fix */ cached_value= (longlong) 0; } else { args[0]->update_used_tables(); if (!(used_tables_cache=args[0]->used_tables())) { /* Remember if the value is always NULL or never NULL */ cached_value= (longlong) args[0]->is_null(); } }