Comment 7 for bug 665669

Revision history for this message
Igor Babaev (igorb-seattle) wrote :

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)