Description:
The result is different in mysql5.6, mysql5.7 and mysql8.0.28 using the sum function
SQL is：
select IFNULL(aa.balance,0) balance, IFNULL(sum(bb.amount),0) amount
from aa
left join bb
on aa.id=bb.from_pool_id
where aa.id=3
and bb.is_deleted=0
and aa.is_deleted=0
and bb.status =1;
Results displayed in mysql5.6 and mysql5.7：
+---------+--------+
| balance | amount |
+---------+--------+
| 35.40 | 0.00 |
+---------+--------+
1 row in set (0.00 sec)
Results displayed in mysql8.0.28：
+---------+--------+
| balance | amount |
+---------+--------+
| 0.00 | 0.00 |
+---------+--------+
1 row in set (0.00 sec)
If you remove the sum function, the results are correct, both as follows：
SQL：
select IFNULL(aa.balance,0) balance, IFNULL(bb.amount,0) amount from aa left join bb on aa.id=bb.from_pool_id where aa.id=3 and bb.is_deleted=0 and aa.is_deleted=0 and bb.status =1;
result：
Empty set (0.00 sec)
why the display is different？
Hopefully, if it's not a bug, you can give me a reason for the different results
Thanks！
How to repeat:
table 1：
CREATE TABLE `aa` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' ,
`balance` decimal(12,2) NOT NULL DEFAULT '0.00' ,
PRIMARY KEY (`id`)
);
table 2：
CREATE TABLE `bb` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`amount` decimal(12,2) NOT NULL DEFAULT '0.00' ,
`from_pool_id` bigint(20) unsigned NOT NULL ,
`status` tinyint(4) NOT NULL ,
`is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
);
SQL：
select IFNULL(aa.balance,0) balance, IFNULL(sum(bb.amount),0) amount
from aa
left join bb
on aa.id=bb.from_pool_id
where aa.id=3
and bb.is_deleted=0
and aa.is_deleted=0
and bb.status =1;
