Bug #33999 cast decimal + sum returns wrong result

• Submitted: 22 Jan 2008 22:13
• Modified: 13 Feb 2008 18:53
• Reporter: Omar Vazquez
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.45, 5.1.23, 6.0.5
• OS: Any (Linux and Mac OS X)
• Assigned to: Timothy Smith
• CPU Architecture: Any
• Tags: buffer overflow, cast, decimal, sum

[22 Jan 2008 22:13] Omar Vazquez

Description:

when using cast + sum with cuantities bigger than 10.000 the sql statement returns only number nine (9999.99) 

How to repeat:
create table test (amount decimal(6,2));
insert into test (amount) values(3000.34);
insert into test (amount) values(4594.99);
insert into test (amount) values(8842.67);
insert into test (amount) values(9953.56);

 select cast(sum(amount) as decimal(6,2)) from  test;

retuns 

+----------------------------------+
| cast(sum(10000) as decimal(6,2)) |
+----------------------------------+
|                          9999.99 |
+----------------------------------+

[23 Jan 2008 3:54] Giuseppe Maxia

Verified as described on 5.0.45, 5.0.56, 5.1.23, 6.0.5.
Notice that 5.0.37 does not have this bug.

[13 Feb 2008 18:53] Timothy Smith

Hi.  This is behaving as documented.  DECIMAL(M,N) will store 6 digits total, with 2 of those digits after the decimal point.  So 9999.99 is the correct maximum value to be stored in such a column.  The warning displayed is correct.

http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

I'll point out one particular passage of that page:

"""
In MySQL 5.0.3 and up, a DECIMAL(M,D)  column allows at most M - D digits to the left of the decimal point. This can result in an incompatibility if an application has a reliance on MySQL allowing “too-large” values.
"""

mysql>  select cast(sum(amount) as decimal(6,2)) from  test;
+-----------------------------------+
| cast(sum(amount) as decimal(6,2)) |
+-----------------------------------+
|                           9999.99 | 
+-----------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1264
Message: Out of range value adjusted for column 'cast(sum(amount) as decimal(6,2))' at row 1
1 row in set (0.00 sec)