MySQL Bugs: #100259: decimal returned function value overflow when group by

Bug #100259	decimal returned function value overflow when group by
Submitted:	20 Jul 2020 4:41	Modified:	16 Apr 2021 15:08
Reporter:	andy zhang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0,5.6.48, 5.7.31, 8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	decimal, FUNCTION, GROUP BY

Description:
When select list has function of decimal type, it's possible it is overflowed due to the incorrect integer part and precision when using group by clause.

How to repeat:
create table decimal_test(c1 int, c2 int);
insert into decimal_test values(97, 1);
select 27 DIV (C2/C1) / 17 as c1 from decimal_test group by c1;

---> return 99.9999 instead of expected 154.0588

Hello andy zhang,

Thank you for the report and test case.
Observed this with 5.6.48, 5.7.31 and 8.0.21.

regards,
Umesh

May I know when the community can fix this serious issue?

as a workaround, you can cast the expression to decimal:
mysql> select cast(27 DIV (C2/C1) / 17 as decimal(65,4)) as x1 from decimal_test group by x1;
+----------+
| x1       |
+----------+
| 154.0588 |
+----------+
1 row in set (0.00 sec)

Justin,

Your workaround works. But it's not really practical for my customers since they need to rewrite the query statements.

Studying the logic, it seems to me the logic of Item_func_div::result_precision() doesn't make sense.  Is the formula to determine the precision of result correct?
It just use the precision of the divisor + decimals of dividend plus the system adjustment const 4.

I suspect the problem is also there for the non groupby case. But it is not exposed yet.

decimals calculation formula is using divisor's decimals + system variable const 4. The precision and decimal combination doesn't make sense.

Here is my draft fix proposal on 8.0. It is not an ultimate patch but it can give a great relief.  please see if it is rational.

--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1890,6 +1890,17 @@ void Item_func_div::result_precision() {
   else
     unsigned_flag = args[0]->unsigned_flag & args[1]->unsigned_flag;
   decimals = min<uint>(args[0]->decimals + prec_increment, DECIMAL_MAX_SCALE);
+
+  /*
+     Adjust the precision to avoid possible overflow at integer part for decimal result,
+     and also align the boundary of integer and decimal as in
+     Item_func_int_val::set_numeric_type().
+  */
+  if (result_type() == DECIMAL_RESULT)
+    precision = max<uint> (
+        precision, DECIMAL_LONGLONG_DIGITS + decimals - 3 -
+          (decimals > 0 ? 1 : 0) - (unsigned_flag || !precision ? 0 : 1));
+
   max_length = my_decimal_precision_to_length_no_truncation(precision, decimals,
                                                             unsigned_flag);

Documented fix as follows in the MySQL 8.0.27 changelog:

    When resolving integer division, the precision of the result is
    taken from the dividend. When the divisor is a decimal number,
    it may be less than 1, which may cause the result to use more
    digits than the dividend. This yielded incorrect values in some
    cases in which the result of integer division was a decimal or
    float.

Closed.