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] 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.059 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.