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)
