|
AVG(bigint_expression) loses precision when used in integer context.
This script demonstrates the problem using bit shift, to provide integer context:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (
|
auto SERIAL,
|
fld1 bigint unsigned NOT NULL,
|
companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
|
UNIQUE fld1 (fld1)
|
);
|
INSERT INTO t1 VALUES (1,0x7FFFFFFFFFFFFFFF,00);
|
INSERT INTO t1 VALUES (2,0x7FFFFFFFFFFFFFFE,37);
|
INSERT INTO t1 VALUES (3,0x7FFFFFFFFFFFFFFC,37);
|
SELECT companynr, AVG(fld1), AVG(fld1)<<0 AS avg1, CAST(AVG(fld1) AS UNSIGNED)<<0 AS avg2 FROM t1 GROUP BY companynr;
|
The result is:
+-----------+--------------------------+---------------------+---------------------+
|
| companynr | AVG(fld1) | avg1 | avg2 |
|
+-----------+--------------------------+---------------------+---------------------+
|
| 00 | 9223372036854775807.0000 | 9223372036854775808 | 9223372036854775807 |
|
| 37 | 9223372036854775805.0000 | 9223372036854775808 | 9223372036854775805 |
|
+-----------+--------------------------+---------------------+---------------------+
|
Notice, the values in the column avg1 lost precision.
If I wrap AVG into CAST(AS UNSIGNED), like in the column avg2, then the result is Ok.
The problem is in this piece of code in item_sum.cc, which uses double routines:
longlong Item_avg_field::val_int()
|
{
|
return (longlong) rint(val_real());
|
}
|
It should use DECIMAL routines when working with DECIMAL or INTEGER arguments.
|