MDEV-8918 Wrong result for CAST(AVG(bigint_column) AS SIGNED)

- Moving Item_xxx_field declarations after Item_sum_xxx declarations,
  so Item_xxx_field constructors can be defined directly in item_sum.h
  rather than item_sum.cc. This removes some duplicate code, e.g.
  initialization of the following members at constructor time:
  name, decimals, max_length, unsigned_flag, field, maybe_null.
- Adding Item_sum_field as a common parent for Item_avg_field and
  Item_variance_field
- Deriving Item_sum_field directly from Item rather that Item_result_field,
  as Item_sum_field descendants do not need anything from Item_result_field.
- Removing hybrid infrastructure from Item_avg_field,
  adding Item_avg_field_decimal and Item_avg_field_double instead,
  as desired result type is already known at constructor time
  (not only at fix_fields time). This simplifies the code.
- Changing Item_avg_field_decimal::val_int() to call val_int_from_decimal()
  instead of doing { return (longlong) rint(val_real()); }
  This is the fix itself.

1 files changed, 17 insertions, 0 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index cef0a668d72..652633b09d1 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -2283,3 +2283,20 @@ Warnings:
 Warning	1292	Truncated incorrect INTEGER value: 'x'
 Warning	1292	Truncated incorrect DOUBLE value: 'x'
 Warning	1292	Truncated incorrect DECIMAL value: 'x'
+#
+#  MDEV-8918 Wrong result for CAST(AVG(a) AS SIGNED)
+#
+CREATE TABLE t1 (id INT, a BIGINT);
+INSERT INTO t1 VALUES (1,0x7FFFFFFFFFFFFFFF),(2,0x7FFFFFFFFFFFFFFF);
+SELECT id, AVG(a) AS avg, CAST(MIN(a) AS SIGNED) AS cast_min FROM t1 GROUP BY id HAVING avg!=123 ORDER BY id;
+id	avg	cast_min
+1	9223372036854775807.0000	9223372036854775807
+2	9223372036854775807.0000	9223372036854775807
+SELECT id, AVG(a) AS avg, CAST(AVG(a) AS SIGNED) AS cast_avg FROM t1 GROUP BY id HAVING avg!=123 ORDER BY id;
+id	avg	cast_avg
+1	9223372036854775807.0000	9223372036854775807
+2	9223372036854775807.0000	9223372036854775807
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#