Description:
A simple index range scan query on 5.1.25-rc runs fine with a normal table, but can return duplicate rows from a partitioned table. Dropping the index (changing to a table scan) results in the correct output.
The partitioned table also shows oddly high Handler_read_next activity, even though we're apparently not doing anything like switching from range to a full index scan.
Seems similar to bug #30573, yet that is listed as fixed in 5.1.25-rc.
How to repeat:
Execute on 5.1.25-rc:
select version();
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (c1) (PARTITION a VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION b VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
explain partitions select c1 from t1 where (c1 > 10 and c1 < 13) or (c1 > 17 and c1 < 20);
flush status;
select c1 from t1 where (c1 > 10 and c1 < 13) or (c1 > 17 and c1 < 20);
show status like 'Handler_read_%';
explain partitions select c1 from t2 where (c1 > 10 and c1 < 13) or (c1 > 17 and c1 < 20);
flush status;
select c1 from t2 where (c1 > 10 and c1 < 13) or (c1 > 17 and c1 < 20);
show status like 'Handler_read_%';
Returns:
+-----------+
| version() |
+-----------+
| 5.1.25-rc |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | NULL | range | c1 | c1 | 5 | NULL | 4 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+------+
| c1 |
+------+
| 11 |
| 12 |
| 18 |
| 19 |
+------+
4 rows in set (0.00 sec)
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_next | 4 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | t2 | a | range | c1 | c1 | 5 | NULL | 4 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+------+
| c1 |
+------+
| 11 |
| 12 |
| 18 |
| 19 |
| 18 |
| 19 |
+------+
6 rows in set (0.00 sec)
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_next | 13 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
