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.


mysql-test/r/func_group.result:
  Added a test case for bug #34512.
mysql-test/t/func_group.test:
  Added a test case for bug #34512.
sql/item_sum.cc:
  Do not use Item_sum_sum::val_decimal() in Item_sum_avg::val_decimal()
  because the first one, depending on the arguments type, may return
  either the sum of the arguments, or the average calculated by the
  virtual val_real() method of Item_sum_avg. Instead, do our own
  calculation based on the arguments type.

3 files changed, 25 insertions, 1 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 4785ca9919d..772e432355b 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1419,4 +1419,10 @@ Note	1003	select (`test`.`t1`.`a` + 1) AS `y` from `test`.`t1` group by (`test`.
 DROP VIEW v1;
 DROP TABLE t1;
 SET SQL_MODE=DEFAULT;
+CREATE TABLE t1(a DOUBLE);
+INSERT INTO t1 VALUES (10), (20);
+SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
+AVG(a)	CAST(AVG(a) AS DECIMAL)
+15	15
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 75a380c733f..dbe6d3113d5 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -916,5 +916,15 @@ DROP VIEW v1;
 DROP TABLE t1;
 SET SQL_MODE=DEFAULT;
 
+#
+# Bug #34512: CAST( AVG( double ) AS DECIMAL ) returns wrong results
+#
+
+CREATE TABLE t1(a DOUBLE);
+INSERT INTO t1 VALUES (10), (20);
+SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
+
+DROP TABLE t1;
+
 ###
 --echo End of 5.0 tests
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 3d6d46ab3f4..f583fc7f988 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1206,7 +1206,15 @@ my_decimal *Item_sum_avg::val_decimal(my_decimal *val)
     null_value=1;
     return NULL;
   }
-  sum_dec= Item_sum_sum::val_decimal(&sum_buff);
+
+  /*
+    For non-DECIMAL hybrid_type the division will be done in
+    Item_sum_avg::val_real().
+  */
+  if (hybrid_type != DECIMAL_RESULT)
+    return val_decimal_from_real(val);
+
+  sum_dec= dec_buffs + curr_dec_buff;
   int2my_decimal(E_DEC_FATAL_ERROR, count, 0, &cnt);
   my_decimal_div(E_DEC_FATAL_ERROR, val, sum_dec, &cnt, prec_increment);
   return val;