Bug #598 SUM() does not handle BIGINT UNSIGNED type properly

• Submitted: 5 Jun 2003 11:23
• Modified: 9 May 2005 9:33
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0
• OS: Any (all)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[5 Jun 2003 11:23] Peter Zaitsev

Description:
SUM() uses double type for internal computation, which results in not handling
BIGINT values properly.

mysql> select sum(i) from sumtest;
+----------------------+
| sum(i)               |
+----------------------+
| 18446744073709551616 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from sumtest;
+----------------------+
| i                    |
+----------------------+
| 18446744073709550615 |
|                   85 |
+----------------------+
2 rows in set (0.00 sec)

How to repeat:
CREATE TABLE sumtest (
  i bigint(20) unsigned default NULL
) TYPE=MyISAM;

INSERT INTO sumtest VALUES (18446744073709550615);
INSERT INTO sumtest VALUES (85);

select sum(i) from sumtest;

[3 Jul 2003 3:53] Michael Widenius

SUM() has to return value as double as we will otherwise get into overflow problems, which is much worse than the precission loss.

We will look at fixing this properly when we add extended integer arithmetic around 5.1

[9 May 2005 9:33] David Axmark

Fixed by new math code in 5.0. Checked with BK tree.

mysql> select sum(i) from sumtest;
+----------------------+
| sum(i)                 |
+----------------------+
| 18446744073709550700 |
+----------------------+
1 row in set (0.06 sec)