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: | |
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 |
[20 Jul 2020 4:41]
andy zhang
[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 2021 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.