SELECT DISTINCT doesn't return result with "Using index for group-by" optimization
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.1 |
Invalid
|
Undecided
|
Unassigned | |||
5.5 |
Invalid
|
Undecided
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
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(
mysql>insert into t1(b,c) values(
mysql>insert into t1(b,c) values(
mysql>insert into t1(b,c) values(
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(
Then try running the query again.
tags: | added: upstream |
Affects Percona Server 5.6, 5.7 and upstream