SQL optimizer and OR - bug or misconcept?

[Alexander Keremidarski]

Description:
When WHERE clause contains OR mysql don't any keys and don't perorm 
validity check even.
USE KEY directive does not change this.

How-To-Repeat:
create table t (
  a int unsigned,
  b int unsigned,
  key(a),
  key(b));

insert about 2000 random rows
select count(*) from t;
2048

1. Check for exisiting values:
mysql> explain select a,b from t where b = 9258279;

+-------+------+---------------+------+---------+-------+------+------------+
| table | type | possible_keys | key  | key_len | ref   | rows | 
Extra      |
+-------+------+---------------+------+---------+-------+------+------------+
| t     | ref  | b             | b    |       5 | const |   48 | where 
used |

mysql> explain select a,b from t where b = 7973452;
| t     | ref  | b             | b    |       5 | const |   69 | where 
used |

mysql> explain select a,b from t where a = 9258279 or b = 7973452;
| t     | ALL  | a,b           | NULL |    NULL | NULL | 2048 | where used |

mysql> explain select a,b from t use key (a) where a = 9258279 or b = 
7973452;
| t     | ALL  | a,b           | NULL |    NULL | NULL | 2048 | where used |


2. Check for impossible (negative) values:
mysql> explain select a,b from t where a = -20;
| t     | ref  | a             | a    |       5 | const |    1 | where 
used |

mysql> explain select a,b from t where a = -20 or b = -10;
| t     | ALL  | a,b           | NULL |    NULL | NULL | 2048 | where used |

mysql> explain select a,b from t use key (a) where a = -20 or b = -10;
| t     | ALL  | a,b           | NULL |    NULL | NULL | 2048 | where used |

Regardless of index schema - i used simplest example.

For 1. I can agree that optimizer can decide to scan all table under 
certain conditions, but for 2. I think range checks must always apply. 
Negative values for unsigned, IS NULL for column defined not null and so on.

mysql> alter table t modify a int unsigned not null;
mysql> explain select a,b from t where a is null or b = -10;
| t     | ALL  | a,b           | NULL |    NULL | NULL | 2048 | where used |

In this case optimizer must notice primary key can't be null so (a is 
null) can be skipped from where clause


Tested with several 3.23.x versions, 4.00, 4.0.1

Fix:
Workaround in 4.x - Use UNION
select ... from t where a = 'x' union select ... where b = x;
mysql> explain select a,b from t where a = -20 union select a,b from t 
where b = -10;
| t     | ref  | a             | a    |       5 | const |    1 | where 
used |
| t     | ref  | b             | b    |       5 | const |    1 | where 
used |

Workaround in 3.23.x - Use temporary table
mysql> create temporary table tmp select a,b from t where a = -20;
mysql> insert into tmp select a,b from t where b = -10;
mysql> select * from tmp;
0 rows
mysql> drop table tmp;

lock/unlock table may be added.