This problem easily can be reproduced for the current 5.3 tree on 64-bit Linux with the following simple test case:
create table t1 (pk int primary key, b int, c int default 0, index idx(b)) engine=innodb;
insert into t1(pk,b) values (3, 30), (2, 20), (9, 90), (7, 70), (4, 40), (5, 50), (10, 100), (12, 120);
set optimizer_use_mrr='disable';
set join_cache_level=0;
explain extended select * from t1 where b > 1000;
select * from t1 where pk < 2 or pk between 3 and 4;
select * from t1 where pk < 2 or pk between 3 and 4;
The last 3 commands return:
MariaDB [test]> explain extended select * from t1 where b > 1000;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | range | idx | idx | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4;
+----+------+------+
| pk | b | c |
+----+------+------+
| 3 | 30 | 0 |
| 4 | 40 | 0 |
| 3 | 30 | 0 |
| 4 | 40 | 0 |
+----+------+------+
4 rows in set (0.02 sec)
MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4;
+----+------+------+
| pk | b | c |
+----+------+------+
| 3 | 30 | 0 |
| 4 | 40 | 0 |
+----+------+------+
2 rows in set (0.01 sec)
This problem easily can be reproduced for the current 5.3 tree on 64-bit Linux with the following simple test case:
create table t1 (pk int primary key, b int, c int default 0, index idx(b)) engine=innodb; use_mrr= 'disable' ;
insert into t1(pk,b) values (3, 30), (2, 20), (9, 90), (7, 70), (4, 40), (5, 50), (10, 100), (12, 120);
set optimizer_
set join_cache_level=0;
explain extended select * from t1 where b > 1000;
select * from t1 where pk < 2 or pk between 3 and 4;
select * from t1 where pk < 2 or pk between 3 and 4;
The last 3 commands return: ------- -----+- ------+ ------- +------ ------- --+---- --+---- -----+- -----+- -----+- ------- --+---- ------- ------- -----+ ------- -----+- ------+ ------- +------ ------- --+---- --+---- -----+- -----+- -----+- ------- --+---- ------- ------- -----+ ------- -----+- ------+ ------- +------ ------- --+---- --+---- -----+- -----+- -----+- ------- --+---- ------- ------- -----+
MariaDB [test]> explain extended select * from t1 where b > 1000;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-
| 1 | SIMPLE | t1 | range | idx | idx | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-
1 row in set, 1 warning (0.00 sec)
MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4; -----+- -----+ -----+- -----+ -----+- -----+
+----+-
| pk | b | c |
+----+-
| 3 | 30 | 0 |
| 4 | 40 | 0 |
| 3 | 30 | 0 |
| 4 | 40 | 0 |
+----+-
4 rows in set (0.02 sec)
MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4; -----+- -----+ -----+- -----+ -----+- -----+
+----+-
| pk | b | c |
+----+-
| 3 | 30 | 0 |
| 4 | 40 | 0 |
+----+-
2 rows in set (0.01 sec)