Bug #91898 Lost of precision in Item_func_mod::real_op
Submitted: 3 Aug 2018 23:15 Modified: 6 Aug 2018 20:25
Reporter: Manuel Ung Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.11,5.5.60, 5.6.41, 5.7.23,8.0.12 OS:Any
Assigned to: CPU Architecture:Any

[3 Aug 2018 23:15] Manuel Ung
Description:
select conv('ea83ee62e49c1d',16,10) % 10 returns 2, when it should be returning 9 instead.

How to repeat:
mysql> select conv('ea83ee62e49c1d',16,10);
+------------------------------+
| conv('ea83ee62e49c1d',16,10) |
+------------------------------+
| 66010204434897949            |
+------------------------------+
1 row in set (0.00 sec)

mysql> select conv('ea83ee62e49c1d',16,10) % 10;
+-----------------------------------+
| conv('ea83ee62e49c1d',16,10) % 10 |
+-----------------------------------+
|                                 2 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select 66010204434897949 % 10;
+------------------------+
| 66010204434897949 % 10 |
+------------------------+
|                      9 |
+------------------------+
1 row in set (0.00 sec)

Suggested fix:
We're losing precision in `Item_func_mod::real_op`. Either give a warning, or better yet, convert to decimal instead of double so that we get the correct answer instead...
[4 Aug 2018 10:10] MySQL Verification Team
Hello Manuel,

Thank you for the report!
Observed this in 5.5.60, 5.6.41, 5.7.23 and 8.0.12.

Thanks,
Umesh
[6 Aug 2018 7:42] Roy Lyseng
Workaround:

select CAST(conv('ea83ee62e49c1d',16,10) AS UNSIGNED) % 10;

Otherwise, I agree there is no need to convert the string to a DOUBLE value.
[6 Aug 2018 20:25] Manuel Ung
This casting to double behaviour is confusing for people.

eg. See this other casting bug: https://bugs.mysql.com/bug.php?id=82021 where range optimizer casts to column type (int), but table scan just casts to double.