[#MDEV-11307] sum() and avg() return incorrect result for float when calculation overflows float range

I believe ~~MCOL-148~~ is the same issue and is not restricted to the InfiniDB use case.

– Create table

DROP TABLE IF EXISTS `inno_1`;

CREATE TABLE `inno_1` (

  `uINT` int unsigned DEFAULT NULL,

  `uDECIMAL_65_30` decimal(65,30) unsigned DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `inno_1` VALUES

(1,34750000000000003551216178095456255.999999999999999999999999999738),

(1,26750000000000003986614330199113727.999999999999999999999999999706),

(1,32750000000000003660065716121370623.999999999999999999999999999730),

(1,77250000000000001238163495044775935.999999999999999999999999999908);

– Compare results of query (incorrect result)

SELECT uINT,avg(uDECIMAL_65_30) FROM inno_1 GROUP BY uINT;

-- With (correct result):

SELECT avg(uDECIMAL_65_30) FROM inno_1;

MariaDB [test]> SELECT uINT,avg(uDECIMAL_65_30) FROM inno_1 GROUP BY uINT;

+------+--------------------------------------------------------------------+

| uINT | avg(uDECIMAL_65_30)                                                |

+------+--------------------------------------------------------------------+

|    1 | 17875000000000003109014929865179135.999999999999999999999999999771 |

+------+--------------------------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;

+---------+------+--------------------------------------------------------------+

| Level   | Code | Message                                                      |

+---------+------+--------------------------------------------------------------+

| Warning | 1916 | Got overflow when converting '' to DECIMAL. Value truncated. |

+---------+------+--------------------------------------------------------------+

1 row in set (0.00 sec)

Vs.

MariaDB [test]> SELECT avg(uDECIMAL_65_30) FROM inno_1;

+--------------------------------------------------------------------+

| avg(uDECIMAL_65_30)                                                |

+--------------------------------------------------------------------+

| 42875000000000003109014929865179135.999999999999999999999999999771 |

+--------------------------------------------------------------------+

1 row in set (0.00 sec)

[MDEV-11307] sum() and avg() return incorrect result for float when calculation overflows float range Created: 2016-11-18 Updated: 2023-04-14
Status:	Confirmed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.4, 10.5, 10.6