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)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.7.18
• OS: Any
• 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] MySQL Verification Team

Workaround:
   set optimizer_switch='use_index_extensions=off';

[26 Jul 2017 14:18] MySQL Verification Team

Also affects the current 5.7 trunk:
Version: '5.7.20'  socket: ''  port: 3306  (Built on 2017/07/26)

[26 Jul 2017 15:07] MySQL Verification Team

Also affects:
 mysqld.exe:  Version: '8.0.3-rc'  socket: ''  port: 3306  (Built on 2017/07/26)

[30 Aug 2017 12:23] MySQL Verification Team

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] MySQL Verification Team

Bug #88412 marked as duplicate of this one