Bug #87207 select distinct with secondary key for 'Using index for group-by' bad results
Submitted: 26 Jul 2017 13:56 Modified: 27 Sep 2017 2:28
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.7.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[26 Jul 2017 13:56] Shane Bester
Description:
Affects 5.7.18+

5.7.18+ have incorrect results for distinct queries having 'Using index for group-by' range plans when using secondary key.

Example failure, see different results...

mysql> select distinct b from t force index(b)where a in(1,231)and b is not null;
+------+
| b    |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> select b from t force index(b)where a in(1,231)and b is not null;
+------+
| b    |
+------+
| 1    |
| 23   |
+------+
2 rows in set (0.00 sec)

How to repeat:
set sql_mode='';
drop table if exists t;
create table t(
  a int not null,
  b char(2),
  c int,
  primary key(a),
  unique key b(b,c)
)engine=innodb;

insert into t(a,b,c) values(1,1,1),(2,2,2),(3,3,3),(4,5,4);
set @a:=5;
insert into t(a,b,c) select (@a:=@a+1),@a,@a from t a,t b,t c,t d;
analyze table t;
select distinct b from t force index(b)where a in(1,231)and b is not null;
select distinct b from t force index(primary)where a in(1,231)and b is not null;
select distinct b from t ignore index(primary,b) where a in(1,231)and b is not null;
select version();
[26 Jul 2017 14:06] Shane Bester
Workaround:
   set optimizer_switch='use_index_extensions=off';
[26 Jul 2017 14:18] Shane Bester
Also affects the current 5.7 trunk:
Version: '5.7.20'  socket: ''  port: 3306  (Built on 2017/07/26)
[26 Jul 2017 15:07] Shane Bester
Also affects:
 mysqld.exe:  Version: '8.0.3-rc'  socket: ''  port: 3306  (Built on 2017/07/26)
[30 Aug 2017 12:23] Miguel Solorzano
https://bugs.mysql.com/bug.php?id=87598 marked as duplicate of this one.
[27 Sep 2017 2:28] Paul Dubois
Posted by developer:
 
Fixed in 5.6.39, 5.7.21, 8.0.4.

Incorrect results could occur on a table with a unique index when the
optimizer chose a loose index scan even though the unique index had
no index extensions.
[9 Nov 2017 6:38] Umesh Shastry
Bug #88412 marked as duplicate of this one