Closed yongbingwang closed 2 years ago
The result of select sum(k9)/count(k9)
is overflow, because select sum(k9)
returns int64
, but the real result of sum(k9)
is less than INT64_MIN
.
-- Overflow.
mysql> select sum(k9) from duplicate_table_with_null;
+------------+
| sum(k9) |
+------------+
| 2147450880 |
+------------+
- - Real result.
mysql> select sum(cast(k9 as largeint)) from duplicate_table_with_null;
+---------------------------+
| sum(CAST(k9 AS LARGEINT)) |
+---------------------------+
| -604462909807312439902208 |
+---------------------------+
mysql> select sum(cast(k9 as largeint))/count(k9) from duplicate_table_with_null;
+-------------------------------------------+
| (sum(CAST(k9 AS LARGEINT))) / (count(k9)) |
+-------------------------------------------+
| -9.223372036854743e+18 |
+-------------------------------------------+
However, there is still a bug for avg(k9)
, because avg(k9)
returns double and shouldn't be overflow. But it returns 2.304717109306825e+18
instead of -9.223372036854743e+18
.
Fixed by #5325.
Steps to reproduce the behavior (Required)
mysql> select sum(k9)/count(k9) from duplicate_table_with_null; +-------------------------+ | (sum(k9)) / (count(k9)) | +-------------------------+ | 32767.5 | +-------------------------+ 1 row in set (0.03 sec)