Bug #72100 0.00000000042 MOD 10 = 0.42000000000
Submitted: 21 Mar 2014 22:09 Modified: 19 Aug 2014 14:52
Reporter: Arthur O'Dwyer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.8, 5.5.31, 5.6.17, 5.5.37 OS:Any
Assigned to: CPU Architecture:Any

[21 Mar 2014 22:09] Arthur O'Dwyer 
Description:
SELECT 0.00000000042 mod 1;
    +---------------------+
    |       0.42000000000 |
    +---------------------+

SELECT 0.000000000123 mod 0.000000000030;
    +-----------------------------------+
    |                    0.003000000000 |
    +-----------------------------------+

These are the right answers, but all multiplied by 1e9.  (This is not completely surprising if you know how MySQL represents decimals internally.)

Anything with 0 through 8 leading zeros works fine; the bug starts at 9 leading zeros. There are discontinuities at 17, 26, 35,... leading zeros where sanity is briefly restored.

How to repeat:
create table t (id serial, d decimal(65,30));
insert into t values (),(),(),(),(),(),(),(),();
insert into t values (),(),(),(),(),(),(),(),();
insert into t values (),(),(),(),(),(),(),(),();
update t set d = concat('1e-', id);
select d, d mod 1 from t;
+----------------------------------+----------------------------------+
| d                                | d mod 1                          |
+----------------------------------+----------------------------------+
| 0.100000000000000000000000000000 | 0.100000000000000000000000000000 |
| 0.010000000000000000000000000000 | 0.010000000000000000000000000000 |
| 0.001000000000000000000000000000 | 0.001000000000000000000000000000 |
| 0.000100000000000000000000000000 | 0.000100000000000000000000000000 |
| 0.000010000000000000000000000000 | 0.000010000000000000000000000000 |
| 0.000001000000000000000000000000 | 0.000001000000000000000000000000 |
| 0.000000100000000000000000000000 | 0.000000100000000000000000000000 |
| 0.000000010000000000000000000000 | 0.000000010000000000000000000000 |
| 0.000000001000000000000000000000 | 0.000000001000000000000000000000 |
| 0.000000000100000000000000000000 | 0.100000000000000000000000000000 |
| 0.000000000010000000000000000000 | 0.010000000000000000000000000000 |
| 0.000000000001000000000000000000 | 0.001000000000000000000000000000 |
| 0.000000000000100000000000000000 | 0.000100000000000000000000000000 |
| 0.000000000000010000000000000000 | 0.000010000000000000000000000000 |
| 0.000000000000001000000000000000 | 0.000001000000000000000000000000 |
| 0.000000000000000100000000000000 | 0.000000100000000000000000000000 |
| 0.000000000000000010000000000000 | 0.000000010000000000000000000000 |
| 0.000000000000000001000000000000 | 0.000000000000000001000000000000 |
| 0.000000000000000000100000000000 | 0.000000000100000000000000000000 |
| 0.000000000000000000010000000000 | 0.000000000010000000000000000000 |
| 0.000000000000000000001000000000 | 0.000000000001000000000000000000 |
| 0.000000000000000000000100000000 | 0.000000000000100000000000000000 |
| 0.000000000000000000000010000000 | 0.000000000000010000000000000000 |
| 0.000000000000000000000001000000 | 0.000000000000001000000000000000 |
| 0.000000000000000000000000100000 | 0.000000000000000100000000000000 |
| 0.000000000000000000000000010000 | 0.000000000000000010000000000000 |
| 0.000000000000000000000000001000 | 0.000000000000000000000000001000 |
+----------------------------------+----------------------------------+
[3 Apr 2014 8:08] MySQL Verification Team 
Hello Arthur,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[19 Aug 2014 14:52] Paul DuBois 
Noted in 5.7.5 changelog.

MOD operations on a DECIMAL value with leading zeros could produce
incorrect results.