MySQL Bugs: #34512: CAST( AVG( double ) AS DECIMAL ) returns wrong results

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

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 |
+--------+-------------------------+

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;

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.

Pushed into 5.1.24-rc

Pushed into 5.0.60

Pushed into 6.0.5-alpha

Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

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