Bug #98825 sum() function result may be error in select query

Submitted: 4 Mar 2020 7:58 Modified: 6 Mar 2020 9:26
Reporter: yayun zhou Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql-8.0.18, 8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[4 Mar 2020 7:58] yayun zhou
Description:
In the follow case, we find the sum() funtion has error result.

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 dayname(cast(entry_time as date)) as c1,score FROM testdb123.t201 where length(name)>2 and name not like 'f%' order  by c1;
+----------+--------+
| c1       | score  |
+----------+--------+
| Friday   |   NULL |
| Friday   | 695.25 |
| Monday   | 623.03 |
| Monday   | 710.20 |
| Sunday   | 452.26 |
| Sunday   |   NULL |
| Thursday |   NULL |
| Thursday | 420.56 |
+----------+--------+
8 rows in set (0.00 sec)

SELECT dayname(cast(entry_time as date)) as c1,sum(score),count(score) FROM testdb123.t201 where length(name)>2 and name not like 'f%' GROUP BY c1  order  by c1;
+----------+------------+--------------+
| c1       | sum(score) | count(score) |
+----------+------------+--------------+
| Friday   |    1147.51 |            1 |
| Monday   |    1333.23 |            2 |
| Sunday   |     452.26 |            1 |
| Thursday |     872.82 |            1 |
+----------+------------+--------------+
4 rows in set (0.00 sec)

we expect the result is as follow:(mysql-5.7.22 is ok.)
+----------+------------+--------------+
| c1       | sum(score) | count(score) |
+----------+------------+--------------+
| Friday   |     695.25 |            1 |
| Monday   |    1333.23 |            2 |
| Sunday   |     452.26 |            1 |
| Thursday |     420.56 |            1 |
+----------+------------+--------------+
4 rows in set (0.00 sec)
[4 Mar 2020 8:14] MySQL Verification Team
Hello yoga yoga,

Thank you for the report and test case.
verified as described with 8.0.19 build.

regards,
Umesh
[6 Mar 2020 9:26] Erlend Dahl
Fixed in 8.0.20, duplicate of

Bug#97920 Aggregation function [sum()] return random numbers

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

Page generated in 0.040 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.