-
Type:
Bug
-
Status: On Hold
-
Priority:
High
-
Resolution: Unresolved
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
-
Launchpad URL:
**Reported in Launchpad by Jaime Sicam last update 31-08-2017 02:38:18
Unfortunately, I can't reproduce the same issue without using index hints.
Test data:
mysql> use test;
mysql> create table t1(a int not null auto_increment primary key, b int not null,c int not null, unique(b,c));
mysql>insert into t1(b,c) values(1,1),(1,2),(1,3),(1,4),(1,5);
mysql>insert into t1(b,c) values(2,1),(2,2),(2,3),(2,4),(2,5);
mysql>insert into t1(b,c) values(3,1),(3,2),(3,3),(3,4),(3,5);
mysql>insert into t1(b,c) values(4,1),(4,2),(4,3),(4,4),(4,5);
select * from t1;
------
| a | b | c |
------
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 2 | 1 |
| 7 | 2 | 2 |
| 8 | 2 | 3 |
| 9 | 2 | 4 |
| 10 | 2 | 5 |
| 11 | 3 | 1 |
| 12 | 3 | 2 |
| 13 | 3 | 3 |
| 14 | 3 | 4 |
| 15 | 3 | 5 |
| 16 | 4 | 1 |
| 17 | 4 | 2 |
| 18 | 4 | 3 |
| 19 | 4 | 4 |
| 20 | 4 | 5 |
------
20 rows in set (0.00 sec)
Good result:
mysql> select distinct b from t1 where a in (5,7);
---
| b |
---
| 1 |
| 2 |
---
2 rows in set (0.00 sec)
mysql> explain select distinct b from t1 where a in (5,7);
----------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | PRIMARY,b | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using temporary |
----------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Empty result:
mysql> select distinct b from t1 use index(b) where a in (5,7);
Empty set (0.00 sec)
mysql> explain select distinct b from t1 use index(b) where a in (5,7);
----------------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | b | b | 4 | NULL | 5 | 20.00 | Using where; Using index for group-by |
----------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
----------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
----------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | /* select#1 */ select distinct `test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX (`b`) where (`test`.`t1`.`a` in (5,7)) |
----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
This will be a big issue if the optimizer chose unique index b instead of the primary key in production.
If you're not able to reproduce it yet, perhaps MySQL chose this optimize the query with "Using where; Using index"
Try restarting the MySQL instance or add another set of rows:
insert into t1(b,c) values(5,1),(5,2),(5,3),(5,4),(5,5);
Then try running the query again.