Public bug reported:
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.
** Affects: mysql-server
Importance: Unknown
Status: Unknown
** Affects: percona-server
Importance: Undecided
Status: New
** Tags: i203617
** Bug watch added: MySQL Bug System #87598
http://bugs.mysql.com/bug.php?id=87598
** Also affects: mysql-server via
http://bugs.mysql.com/bug.php?id=87598
Importance: Unknown
Status: Unknown
--
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1713937
Title:
SELECT DISTINCT doesn't return result with "Using index for group-by"
optimization
To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1713937/+subscriptions
--
Mailing list: https://launchpad.net/~enterprise-support
Post to : enterprise-support@lists.launchpad.net
Unsubscribe : https://launchpad.net/~enterprise-support
More help : https://help.launchpad.net/ListHelp
