SELECT DISTINCT doesn't return result with "Using index for group-by" optimization

Bug #1713937 reported by Jaime Sicam on 2017-08-30
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Status tracked in 5.7
5.1
Undecided
Unassigned
5.5
Undecided
Unassigned
5.6
Medium
Unassigned
5.7
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(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.

Jaime Sicam (jssicam) wrote :

Affects Percona Server 5.6, 5.7 and upstream

Changed in percona-server:
status: New → Confirmed
Muhammad Irfan (muhammad-irfan) wrote :

Jaime,

I'm not able to repeat this problem in Percona Server 5.6/5.7 yet. Can you share what specific settings you are using for my.cnf ?

Jaime Sicam (jssicam) wrote :

For the record, we discussed how to reproduce this on chat.

The workaround is to run "set optimizer_switch='use_index_extensions=off';" prior to running the query as per https://bugs.mysql.com/bug.php?id=87598

tags: added: upstream
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.