enterprise-support team mailing list archive

[Jaime Sicam <1713937@xxxxxxxxxxxxxxxxxx>]

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