Bug #34512 CAST( AVG( double ) AS DECIMAL ) returns wrong results
Submitted: 13 Feb 2008 5:13 Modified: 12 Apr 2008 2:58
Reporter: Tak Tosh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.51a-community-nt, 5.0.54 OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[13 Feb 2008 5:13] Tak Tosh
Description:
CAST( AVG( double ) AS DECIMAL ) returns wrong result.
Although, SUM(), MAX(), MIN() correctly return.

# Most config params are default, but character sets are customized like...
 character_set_client     | sjis
 character_set_connection | sjis
 character_set_database   | sjis
 character_set_filesystem | binary
 character_set_results    | sjis
 character_set_server     | sjis
 character_set_system     | utf8

How to repeat:
>create table test( A double ) engine=MyISAM;
>insert into test values(10);
>insert into test values(20);

mysql> select avg(A), cast(avg(A) as decimal) from test;
+--------+-------------------------+
| avg(A) | cast(avg(A) as decimal) |
+--------+-------------------------+
|     15 |                       8 | <--------unexpected
+--------+-------------------------+

mysql> select sum(A), cast(sum(A) as decimal) from test;
+--------+-------------------------+
| sum(A) | cast(sum(A) as decimal) |
+--------+-------------------------+
|     30 |                      30 |
+--------+-------------------------+

mysql> select min(A), cast(min(A) as decimal) from test;
+--------+-------------------------+
| min(A) | cast(min(A) as decimal) |
+--------+-------------------------+
|     10 |                      10 |
+--------+-------------------------+

mysql> select max(A), cast(max(A) as decimal) from test;
+--------+-------------------------+
| max(A) | cast(max(A) as decimal) |
+--------+-------------------------+
|     20 |                      20 |
+--------+-------------------------+
[13 Feb 2008 5:38] Valeriy Kravchuk
Thank you for a bug report. Verified just as described:

create table tt(a double) engine=MyISAM;
insert into tt values (10), (20);
select avg(a), cast(avg(a) as decimal) from tt;
[6 Mar 2008 15:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43524

ChangeSet@1.2599, 2008-03-06 18:19:47+03:00, kaa@kaamos.(none) +3 -0
  Fix for bug #34512: CAST( AVG( double ) AS DECIMAL )
                      returns wrong results
  
  Casting AVG() to DECIMAL led to incorrect results when the arguments
  had a non-DECIMAL type, because in this case
  Item_sum_avg::val_decimal() performed the division by the number of
  arguments twice.
  
  Fixed by changing Item_sum_avg::val_decimal() to not rely on
  Item_sum_sum::val_decimal(), i.e. calculate sum and divide using
  DECIMAL arithmetics for DECIMAL arguments, and utilize val_real() with
  subsequent conversion to DECIMAL otherwise.
[28 Mar 2008 9:21] Bugs System
Pushed into 5.1.24-rc
[28 Mar 2008 9:23] Bugs System
Pushed into 5.0.60
[31 Mar 2008 13:58] Bugs System
Pushed into 6.0.5-alpha
[12 Apr 2008 2:58] Paul Dubois
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

CAST(AVG(arg) AS DECIMAL) produced incorrect results for non-DECIMAL
arguments.