Bug #87598 SELECT DISTINCT doesn't return result with "Using index for group-by"
Submitted: 30 Aug 2017 7:44 Modified: 30 Aug 2017 12:22
Reporter: Jaime Sicam Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.19 OS:Any
Assigned to: CPU Architecture:Any

[30 Aug 2017 7:44] Jaime Sicam 
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)

How to repeat:
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.
[30 Aug 2017 10:12] MySQL Verification Team 
Thank you for the bug report. I couldn't repeat on CentOS 7, please provide your my.cnf:

miguel@ural:~/dbs $ mysql -uroot -p test
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1(a int not null auto_increment primary key, b int not null,c int not null, unique(b,c));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1(b,c) values(1,1),(1,2),(1,3),(1,4),(1,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1(b,c) values(2,1),(2,2),(2,3),(2,4),(2,5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1(b,c) values(3,1),(3,2),(3,3),(3,4),(3,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1(b,c) values(4,1),(4,2),(4,3),(4,4),(4,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 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)

mysql> select distinct b from t1  where a in (5,7);
+---+
| b |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.01 sec)

mysql> select distinct b from t1 use index(b) where a in (5,7);
+---+
| b |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
[30 Aug 2017 11:05] Jaime Sicam 
Hi Miguel,
  I used MySQL sandbox and so the configuration is default.

[mysql]
prompt='mysql [\h] {\u} (\d) > '

[client]
user               = msandbox
password           = msandbox
port               = 5719
socket             = /tmp/mysql_sandbox5719.sock

[mysqld]
user               = kits
port               = 5719
socket             = /tmp/mysql_sandbox5719.sock
basedir            = /home/kits/mysql_binaries/5.7.19
datadir            = /home/kits/sandboxes/msb_5_7_19/data
tmpdir             = /home/kits/sandboxes/msb_5_7_19/tmp
lower_case_table_names = 0
pid-file           = /home/kits/sandboxes/msb_5_7_19/data/mysql_sandbox5719.pid
bind-address       = 127.0.0.1
log-error=msandbox.err

Yes. Initially, it works and optimizes the query with "Using where; Using index". But, please try restarting MySQL and then run the SELECT queries. I was able to reproduce this on MySQL 5.7.19, Percona Server 5.7.18 and Percona Server 5.6.36.
[30 Aug 2017 11:25] MySQL Verification Team 
looks like mine; https://bugs.mysql.com/bug.php?id=87207 ?
[30 Aug 2017 12:15] MySQL Verification Team 
Thank you for the feedback:

miguel@ural:~/dbs $ mysql -uroot -p test
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1(a int not null auto_increment primary key, b int not null,c int not null, unique(b,c));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1(b,c) values(1,1),(1,2),(1,3),(1,4),(1,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1(b,c) values(2,1),(2,2),(2,3),(2,4),(2,5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1(b,c) values(3,1),(3,2),(3,3),(3,4),(3,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t1(b,c) values(4,1),(4,2),(4,3),(4,4),(4,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 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)

mysql> select distinct b from t1  where a in (5,7);
+---+
| b |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.01 sec)

mysql> select distinct b from t1 use index(b) where a in (5,7);
+---+
| b |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> exit
Bye
miguel@ural:~/dbs $ sudo service mysqld restart
[sudo] password for miguel: 
Redirecting to /bin/systemctl restart  mysqld.service
miguel@ural:~/dbs $ mysql -uroot -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select distinct b from t1  where a in (5,7);
+---+
| b |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> select distinct b from t1 use index(b) where a in (5,7);
Empty set (0.00 sec)
[30 Aug 2017 12:22] MySQL Verification Team 
Duplicate of bug mentioned by Shane. See workaround.

miguel@ural:~/dbs $ sudo service mysqld restart
[sudo] password for miguel: 
Redirecting to /bin/systemctl restart  mysqld.service
miguel@ural:~/dbs $ mysql -uroot -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set optimizer_switch='use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select distinct b from t1  where a in (5,7);
+---+
| b |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.01 sec)

mysql> select distinct b from t1 use index(b) where a in (5,7);
+---+
| b |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)