| Bug #68750 | Wrong query result when using range over partial index | ||
|---|---|---|---|
| Submitted: | 22 Mar 2013 19:33 | Modified: | 12 Apr 2013 17:39 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Mar 2013 19:34]
Sergey Petrunya
Dataset
Attachment: t2m.sql (text/x-sql), 9.31 KiB.
[23 Mar 2013 21:02]
MySQL Verification Team
Hello Sergey, Thank you for the report. Verified as described. Thanks, Umesh
[23 Mar 2013 21:02]
MySQL Verification Team
## 5.6.10 - affected
mysql> select * from t2m ignore index(cc) where c not in ('o','z');
..
..
650 rows in set (0.01 sec)
mysql> explain select * from t2m ignore index(cc) where c not in ('o','z');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2m | ALL | NULL | NULL | NULL | NULL | 650 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain extended select * from t2m ignore index(cc) where c not in ('o','z');
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2m | ALL | NULL | NULL | NULL | NULL | 650 | 100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`t2m`.`pk` AS `pk`,`test`.`t2m`.`c` AS `c` from `test`.`t2m` IGNORE INDEX (`cc`) where (`test`.`t2m`.`c` not in ('o','z')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t2m force index(cc) where c not in ('o','z');
..
..
640 rows in set (0.02 sec)
mysql> explain select * from t2m force index(cc) where c not in ('o','z');
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2m | range | cc | cc | 2 | NULL | 641 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain extended select * from t2m force index(cc) where c not in ('o','z');
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2m | range | cc | cc | 2 | NULL | 641 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`t2m`.`pk` AS `pk`,`test`.`t2m`.`c` AS `c` from `test`.`t2m` FORCE INDEX (`cc`) where (`test`.`t2m`.`c` not in ('o','z')) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[23 Mar 2013 21:03]
MySQL Verification Team
## 5.5.31 - affected
mysql> select * from t2m ignore index(cc) where c not in ('o','z');
..
..
650 rows in set (0.06 sec)
mysql> explain select * from t2m ignore index(cc) where c not in ('o','z');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2m | ALL | NULL | NULL | NULL | NULL | 650 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain extended select * from t2m ignore index(cc) where c not in ('o','z');
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2m | ALL | NULL | NULL | NULL | NULL | 650 | 100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.06 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t2m`.`pk` AS `pk`,`test`.`t2m`.`c` AS `c` from `test`.`t2m` IGNORE INDEX (`cc`) where (`test`.`t2m`.`c` not in ('o','z')) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t2m force index(cc) where c not in ('o','z');
640 rows in set (0.04 sec)
mysql> explain select * from t2m force index(cc) where c not in ('o','z');
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2m | range | cc | cc | 2 | NULL | 641 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain extended select * from t2m force index(cc) where c not in ('o','z');
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2m | range | cc | cc | 2 | NULL | 641 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t2m`.`pk` AS `pk`,`test`.`t2m`.`c` AS `c` from `test`.`t2m` FORCE INDEX (`cc`) where (`test`.`t2m`.`c` not in ('o','z')) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
### 5.7.1-m11-debug - Affected
[12 Apr 2013 17:39]
Paul DuBois
Noted in 5.5.32, 5.6.12, 5.7.2 changelogs. Using range access with an index prefix could produce incorrect results.
Description: Using a partial index and range access will cause wrong result. How to repeat: CREATE TABLE `t2m` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `c` char(10) DEFAULT NULL, KEY `cc` (`c`(1),`pk`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -- Load attached dataset MySQL [test]> select * from t2m ignore index(cc) where c not in ('o','z'); 650 rows in set (0.00 sec) MySQL [test]> select * from t2m force index(cc) where c not in ('o','z'); ... 640 rows in set (0.01 sec) Note the difference : 650 rows vs 640 rows.