Bug #100259 decimal returned function value overflow when group by

• Submitted: 20 Jul 2020 4:41
• Modified: 16 Apr 15:08
• Reporter: andy zhang
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0,5.6.48, 5.7.31, 8.0.21
• OS: Any
• CPU Architecture: Any
• Tags: decimal, FUNCTION, GROUP BY

[20 Jul 2020 4:41] andy zhang

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

[20 Jul 2020 6:04] MySQL Verification Team

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

[28 Jul 2020 2:28] andy zhang

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

[2 Aug 2020 7:36] Justin Swanhart

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)

[3 Aug 2020 3:23] andy zhang

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.

[3 Aug 2020 3:34] andy zhang

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

[3 Aug 2020 7:00] andy zhang

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);

[26 Apr 23:21] Jon Stephens

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.