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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any

[22 Mar 2013 19:33] Sergey Petrunya
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.
[22 Mar 2013 19:34] Sergey Petrunya
Dataset

Attachment: t2m.sql (text/x-sql), 9.31 KiB.

[23 Mar 2013 21:02] Umesh Shastry
Hello Sergey,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[23 Mar 2013 21:02] Umesh Shastry
## 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] Umesh Shastry
## 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.