Bug #98838 select distinct will return wrong result in one case
Submitted: 5 Mar 2020 6:11 Modified: 5 Mar 2020 6:19
Reporter: yoga yoga Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql5.7.22 mysql5.7.29 OS:Any
Assigned to: CPU Architecture:Any

[5 Mar 2020 6:11] yoga yoga
Description:
We find the distinct function has wrong reslut in some case, please see the follow cases.
And the Mysql8.0.18 does not has this problem.
Thanks!

How to repeat:
drop database if exists testdb123;
create database if not exists testdb123;
drop table if exists testdb123.t201;
create table if not exists testdb123.t201(id int primary key,name varchar(10),entry_time datetime,score double(5,2) null,height smallint);

insert into testdb123.t201(id,name,entry_time,score,height) values(136527,'z h ','2010-08-15 06:45',452.26,174);
insert into testdb123.t201(id,name,entry_time,height) values(154545,'xJ sk','2002-01-18 14:15:00',168);
insert into testdb123.t201(id,name,entry_time,score,height) values(294106,' Jdy','2001-01-15 15:45',623.03,169);
insert into testdb123.t201(id,name,entry_time,score,height) values(374620,'wq','2001-01-15 15:45',457.06,180);
insert into testdb123.t201(id,name,entry_time,score,height) values(462519,'c Dg','2006-12-25 20:45',710.20,160);
insert into testdb123.t201(id,name,entry_time,height) values(565542,'whl','2015-03-05 20:32:35',174);
insert into testdb123.t201(id,name,entry_time,height) values(658123,'loDg','2007-02-25 17:30:25',166);
insert into testdb123.t201(id,name,entry_time,score,height) values(724561,'xYl','2008-08-08 19:20',695.25,165);
insert into testdb123.t201(id,name,entry_time,score,height) values(814263,'wQ ','1999-04-15 06:45',420.56,167);

select to_seconds(time(entry_time)) from testdb123.t201;
+------------------------------+
| to_seconds(time(entry_time)) |
+------------------------------+
|                  63750609900 |
|                  63750636900 |
|                  63750642300 |
|                  63750642300 |
|                  63750660300 |
|                  63750659555 |
|                  63750648625 |
|                  63750655200 |
|                  63750609900 |
+------------------------------+
9 rows in set (0.00 sec)

--error result
select distinct to_seconds(time(entry_time)) from testdb123.t201;
+------------------------------+
| to_seconds(time(entry_time)) |
+------------------------------+
|                   2147483647 |
+------------------------------+
1 row in set (0.00 sec)

we find the mysql8.0.18 has the correct result ,as follow:
select distinct to_seconds(time(entry_time)) from testdb123.t201;
+------------------------------+
| to_seconds(time(entry_time)) |
+------------------------------+
|                  63750609900 |
|                  63750636900 |
|                  63750642300 |
|                  63750660300 |
|                  63750659555 |
|                  63750648625 |
|                  63750655200 |
+------------------------------+
7 rows in set (0.00 sec)
[5 Mar 2020 6:19] MySQL Verification Team
Hello yoga yoga,

Thank you for the report and test case.
verified as described with 5.6.47/5.7.29 builds.

regards,
Umesh
[5 Mar 2020 6:20] MySQL Verification Team
- 8.0.19 not affected