Bug Hunting Session
Bug 50299 - MOD shows not existing, inconsistent, small remainder with calculated Dividend
Summary: MOD shows not existing, inconsistent, small remainder with calculated Dividend
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 71131 73366 75811 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-05-23 20:41 UTC by Robinson Tryon (qubit)
Modified: 2017-01-22 04:49 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
Testcase for bug (13.09 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-05-23 20:49 UTC, Robinson Tryon (qubit)
Details
Extremely wrong result (13.60 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-05-24 06:19 UTC, Rainer Bielefeld Retired
Details
Spreadsheet for testing modulo problem. (40.70 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-21 19:00 UTC, klsu
Details
MOD and MODFAST behaviour proposition (20.20 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-04-17 17:46 UTC, mahfiaz
Details
Conversion to DATE using MOD to get month from 2 decimal places. (12.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-01-20 23:51 UTC, klsu
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robinson Tryon (qubit) 2012-05-23 20:41:21 UTC
There appears to be a bug in the Calc modulo function.

Here's the description from Axel Reimer's email entitled "Critical bug in LibreOffice Calc when using mod (showstopper for 3.5.4?)"

---
in LibreOffice Calc 3.5.3 there is a critical bug in the mod function.

When using

=mod=(0,3*100;10) in a cell 3,5527E-015 is displayed as result
=mod=(0,4*100;10) in a cell 0 is displayed as result
=mod=(0,5*100;10) in a cell 0 is displayed as result
=mod=(0,6*100;10) in a cell 7,1054E-015 is displayed as result
=mod=(0,7*100;10) in a cell 0 is displayed as result
=mod=(0,8*100;10) in a cell 0 is displayed as result
=mod=(0,9*100;10) in a cell 0 is displayed as result

=mod=(30;10) in a cell 0 is displayed as result
=mod=(60;10) in a cell 0 is displayed as result

The values for 0,3*100 and 0,6*100 are definitely wrong.

This could be a showstopper if this still happens in LibreOffice 3.5.4 RC1.
Can someone test if this occurs in 3.5.4 RC1?

Best regards,
Axel
Comment 1 Robinson Tryon (qubit) 2012-05-23 20:48:50 UTC
I have confirmed this bug in LO 3.5.4.1 on ubuntu 11.10 x86_64.
Comment 2 Robinson Tryon (qubit) 2012-05-23 20:49:53 UTC
Created attachment 62048 [details]
Testcase for bug

I'm attaching a simple test document that can be used to test for the bug. Column C shows the formulae used to generate the values in column B. With a correct implementation of the MOD (modulo) function, all of the values in column B should be zero (0).
Comment 3 Rainer Bielefeld Retired 2012-05-24 00:08:53 UTC
Effekt is reproducible with "LibreOffice 3.5.4.2 (RC2) German UI/Locale [Build-ID: 165a79a-7059095-e13bb37-fef39a4-9503d18] on German WIN7 Home Premium (64bit) 

Please ignore formulas in original report (they are wrong), instead please use sample document.

I see unexpected remainders in B6, B9, what disappear when I replace Numbers 0,3 and 0,6 by 0,30 and 0,60.

But IMHO those are inevitable rounding errors inaccuracies. Those are also visible with AOOo 3.4, EXCEL 2010 and Gnumeric, but it seems EXCEL makes a smarter job during input: when I replace cell Contents "0,3" typing again "0,3" the remainder becomes "0". In LibO 

That never worked different, also effect reproducible with LibO 3.3.3

@Qubit
Please contribute precise descriptions instead of "Terrible horrible Bug in LibreOffice" ;-)
Comment 4 Kohei Yoshida 2012-05-24 05:45:18 UTC
This is a limitation of the FPU.  So it's a hardware problem.  Anything we try to do at software level would be hacky and prone to other, more serious errors, including significant performance impact.  IMO we should leave this alone.
Comment 5 Rainer Bielefeld Retired 2012-05-24 06:19:41 UTC
Created attachment 62056 [details]
Extremely wrong result

@Kohei:
thank you for additional clarification. I also believe we can't do much against limitations you mention. 

But In attached document with chained formulas the result of those rounding errors becomes ridiculous (same in Gnumeric, Excel not yet tested), what might be an interesting research for a SOC student or similar?
Comment 6 Kohei Yoshida 2012-05-24 06:28:24 UTC
We do try to correct the most obvious cases, which may be something we could do here as well.  But since the real cause for this problem is technically unsolvable, whoever wants to "solve" it may end up spending a lot time for a very little gain.

IMO this is something the users need to be aware of.  The limitation applies to all software that runs on current hardware, not just spreadsheet.

Some mathematical and statistical software overcome this by not using FPU and doing it all at software level.  But we can't afford to do that, or else the performance of formula calculation will end up 1000 times slower.
Comment 7 Robinson Tryon (qubit) 2012-05-24 07:40:05 UTC
@Rainer Bielefeld

Re: Bug subject -- I was just trying to copy what the original reporter had written ("here's the description from x's email..") so as not to misrepresent his work.

That's why I followed up my original bug report with a test case and a more precise statement ("all of the values in column B should be zero (0)").

I just didn't want to rain on original source's parade as he's the one who made the first submission. Suffice it to say that I will be much more ruthless when I edit community-submitted bug reports in the future.
Comment 8 Robinson Tryon (qubit) 2012-05-24 08:03:35 UTC
---
IMO this is something the users need to be aware of.  The limitation applies to
all software that runs on current hardware, not just spreadsheet.
---

Where should this go in user documentation? A general note on spurious results attributable to use of binary-encoded decimal numbers?  Maybe some examples?

---
Some mathematical and statistical software overcome this by not using FPU and
doing it all at software level [making it] 1000 times slower.
---

Is it worthwhile to consider a switch to toggle Calc into "high precision" or "correct results" mode? Are there many use-cases in which it would be beneficial to trade high speed for mathematical accuracy?

Here's a related article from Microsoft's knowledge base entitled "How to correct rounding errors in floating-point arithmetic" -- advising MS Excel users, etc...
http://support.microsoft.com/kb/214118
Comment 9 Kohei Yoshida 2012-05-24 08:08:02 UTC
(In reply to comment #8)

> ---
> Some mathematical and statistical software overcome this by not using FPU and
> doing it all at software level [making it] 1000 times slower.
> ---
> 
> Is it worthwhile to consider a switch to toggle Calc into "high precision" or
> "correct results" mode?

It's perhaps easier if you give that a try, and draw your own conclusion.  I've already said what I had to say.
Comment 10 Kohei Yoshida 2012-05-24 08:08:19 UTC
Removing myself from CC.
Comment 11 Rainer Bielefeld Retired 2012-05-24 08:52:09 UTC
As expected same problem in Excel 2010.

@Qubit
All this does not mean that it's impossible to use hardware with limitations in a more smart way, but that's not a simple fix. Research will go on, may be some day someone will find a way to sail around those reefs.

Concerning a warning hint: 
Please read the license! That's all we can do. I am one of the generation where engineers use slide rules (I'm still using it today for my work). Its precision is a little below CALC, but I am very successful with it and never read a warning hint "If you use this tool for construction of a bridge ...."
Comment 12 Robinson Tryon (qubit) 2012-05-24 09:00:09 UTC
(In reply to comment #9)
> (In reply to comment #8)
> 
> > ---
> > Some mathematical and statistical software overcome this by not using FPU and
> > doing it all at software level [making it] 1000 times slower.
> > ---
> > 
> > Is it worthwhile to consider a switch to toggle Calc into "high precision" or
> > "correct results" mode?
> 
> It's perhaps easier if you give that a try, and draw your own conclusion.  I've
> already said what I had to say.

I assume that must be a joke, as it can't possibly be easier to re-write and rebuild major parts of LibreOffice than it can be to have a short and civil discussion about a feature request to Calc.

I'm personally fine with this behavior of Calc as it stands today. I was just proposing a possible change on the grounds that it might improve the software experience for some subset of our users. Short and sweet.
Comment 13 Ruslan Kabatsayev 2012-05-24 09:23:11 UTC
> As expected same problem in Excel 2010.
Interestingly, no such problem in Excel 2003 (converted the sample ods to xls, opened in Excel and pressed F9)
Comment 14 Robinson Tryon (qubit) 2012-05-24 09:44:58 UTC
(In reply to comment #11)
> 
> Concerning a warning hint: 
> Please read the license! That's all we can do. I am one of the generation where
> engineers use slide rules (I'm still using it today for my work). Its precision
> is a little below CALC, but I am very successful with it and never read a
> warning hint "If you use this tool for construction of a bridge ...."

well thankfully slide rules never had to come with a shrink-wrap/click-wrap license :-)

My physics teacher in high school had a big wall-mounted slide rule and would race us if we weren't moving fast enough on calculations. I've never used one in my work, but I still have a couple that were getting tossed, and one that sat by my grandfather's drafting table.

Regarding a warning for users, I do think it could be helpful for the average user to know that floating point representations/calculations can introduce subtle errors. If we don't want to put it in the docs for LO specifically, perhaps it should be mentioned in some kind of more general-purpose 'intro to computing' book that I can recommend to new computer users. Maybe the people at FLOSSManuals have something like that already.
Comment 15 m.a.riosv 2013-11-02 01:02:05 UTC
*** Bug 71131 has been marked as a duplicate of this bug. ***
Comment 16 m.a.riosv 2014-01-08 01:01:00 UTC
*** Bug 73366 has been marked as a duplicate of this bug. ***
Comment 17 m.a.riosv 2014-03-05 23:36:06 UTC
*** Bug 75811 has been marked as a duplicate of this bug. ***
Comment 18 m.a.riosv 2014-12-20 00:43:25 UTC
*** Bug 87386 has been marked as a duplicate of this bug. ***
Comment 19 m.a.riosv 2014-12-20 00:44:40 UTC
*** Bug 87506 has been marked as a duplicate of this bug. ***
Comment 20 klsu 2014-12-21 16:28:16 UTC
in cell A6 enter 0.3
in cell B6 enter =MOD(A6*100, 10)
cell B6 displays 3.5527136788005E-015
in cell A6 enter =3/10
cell B6 displays 0

So letting the hardware create the 0.3 instead of using LO Calc's stored 0.3 gives the correct answer. That doesn't prove it's not a hardware bug, but it proves it is or is also a software bug, and I have confirmed that it exists in LO 4.4.0 Beta Dev Daily I installed 2014-12-18 AND in Excel 2003, but not as bad. More on that when I reopen 87506 with a spreadsheet example showing just how bad the LO Calc MOD() function can be.

Same result using 0.6 vs 6/10

"We can't fix it or don't think it's worth fixing, so mention it in the documentation and it's resolved" does not resolve the problem. Explaining it to the average person wouldn't help them (if they ever happen to read it - have you read, understood and remembered all the documentation of every piece of software you use?). They would understand "if you use the MOD() function, the answer might be right and it might not be, so you'd better check every use of the MOD() function manually". It means "don't use MOD(), it's not reliable".

The correct resolution would be to remove the MOD() function from LO Calc until it has been fixed.
Comment 21 klsu 2014-12-21 19:00:05 UTC
Created attachment 111126 [details]
Spreadsheet for testing modulo problem.
Comment 22 klsu 2014-12-21 19:03:56 UTC
in cell A1 enter 80.9
in cell A2 enter 81
in cell A3 enter 81.1
in cell B1 enter =MOD(A1*100,10)
copy it to range B2:B3
B1 = 9.09494701772928E-013
B2 = 0
B3 = 10

Increasing in increments of 0.1, between 64.4 and 82.1, this occurs 36 times. The error in B1 might not hurt anything, but in engineering or medicine, the error in B3 could be fatal, and in finance it would be unacceptable.

The same thing in Excel (2003) gives:
B1 = 9.09495E-13
B2 = 0
B3 = -9.09495E-13

This calculation performed in LO Calc 4.4.0 Beta2 Dev Daily from 2014 12 18, from 0 to 100 in increments of 0.1 results in 143 such errors out of 1000, the maximum error being 10, the minimum being 1.13686837721616E-013. In Excel 2003, it also results in 143 errors, but the absolute value of no error exceeds 9.09495E-13. The pattern obvious in the errors suggests the way LO and Excel calculate the MOD() are significantly different; Excel is accounting for the fact that small errors the digits not considered significant can cause a result to be below the actual value, so simply taking the integer of a real value when performing the MOD() won't work properly.

I have no way of knowing where the problem is, but there is clearly a problem related to rounding error that can result in serious errors, and it's not caused by hardware.

When using with the attachment, beware; when I changed A8 & A7 from 100 & 10 to 10 & 1, or changed A7 & A8 from 1 & 10 to 10 & 100, respectively IN THE ORDER SHOWN, recalculation caused strange results in B11 or B12 that required a forced recalculation (Ctrl+Shift+F9).
Comment 23 klsu 2014-12-31 06:42:40 UTC
Just to remind whoever is calling this bug an enhancement of low importance: the criteria by which importance is decided assume that the worst things that can happen are crashes and severe system slow-downs. Perhaps that is so in virtual reality. In the real world there is something worse in a spreadsheet: inconsistent and wrong answers.
Comment 24 klsu 2015-01-21 18:50:46 UTC Comment hidden (no-value)
Comment 25 m.a.riosv 2015-02-14 13:56:14 UTC
*** Bug 89373 has been marked as a duplicate of this bug. ***
Comment 26 Jean-Baptiste Faure 2015-02-14 15:46:39 UTC
For me MOD() function should be used only with integers. With implementation in LibreOffice, and if you are sure that the dividende is integer, you can use an expression like =MOD(ROUND(0.9*100;0);10).

I think it is a bad idea to generalize MOD() function to non-integer numbers. The bug is here.

Best regards. JBF
Comment 27 klsu 2015-02-15 14:44:50 UTC Comment hidden (no-value)
Comment 28 Robinson Tryon (qubit) 2015-02-15 20:00:11 UTC
(In reply to klsu from comment #27)
> Changing the instructions for using a function does not correct a bug. Users
> will not expect to have to read the instructions for every function they
> have used in Excel for years, before using them in LO (etc.).

Specifically on this point, I'd say that LibreOffice is not Excel. If there are compelling mathematical reasons to implement a function or a feature in a similar fashion to another piece of software (e.g. Excel, Abiword, Sage, etc..), then I think it's valuable for us to consider it, but sometimes Excel is just wrong (Classic example: the '1900' leap-year bug).

> Programmers
> seem to not understand that the computer, and therefore the software, is
> supposed to allow the user to work efficiently. When software forces the
> user to work a certain way, the programmer is making the incorrect
> assumption that he/she knows the user's job as well as the user does.

One potential use-case for Calc would be as a generic tool for numerical computation (with the modulus function operating without the limitations described above). I don't think that the Calc developers currently see that as a primary goal of the software, so if there is a disconnect between their view of the tool and how our users see it, perhaps we need to sit down and figure out how to reconcile these two groups.
Comment 29 klsu 2015-02-16 02:09:17 UTC Comment hidden (no-value)
Comment 30 klsu 2015-02-25 15:00:14 UTC Comment hidden (no-value)
Comment 31 klsu 2015-02-25 15:00:51 UTC
...now listed as...
Comment 32 mahfiaz 2016-04-17 17:44:10 UTC
I think this bug should be closed and reported again.

It would make sense to always round numbers with MOD (lets not forget how fast modern CPUs actually are) and maybe add MODFAST function which could make available the behaviour we have now.

So I propose MOD would have similar behaviour:
A3 - dividend
B3 - divisor
C3 - precision =-CEILING(LOG(B3; 10)) + 10
D3 - =MOD(ROUND(A3; C3); ROUND(B3; C3))


I am no expert but similar behaviour could possibly be achieved with just bitmasking a few last bits off from the fraction part of the doublefloat if the fraction part is filled eg some of its upper bits (the rightmost ones) are nonzero.

if (b00000000000000000000000000000111 & num) {
    num &= 11111111100011111111111111111111;
}
Comment 33 mahfiaz 2016-04-17 17:46:43 UTC
Created attachment 124437 [details]
MOD and MODFAST behaviour proposition
Comment 34 Eike Rathke 2017-01-02 22:15:15 UTC
This seems to have been fixed, likely with the changes for bug 102742.
Comment 35 klsu 2017-01-20 23:51:00 UTC
Created attachment 130588 [details]
Conversion to DATE using MOD to get month from 2 decimal places.

Example showing equalities that are not and date conversions that are incorrect when MOD is used in certain ways.
Comment 36 klsu 2017-01-20 23:53:50 UTC
Using the above test file dated 2014-12-21 19:00 CET in Version 5.1.4.2, Build ID: 1:5.1.4-Oubuntu1, the spreadsheet is still showing 143 of 1000 results not equal to zero, 72 of which are significant; so the problem still exists.

The problem that lead me to the discovery of this one also still occurs in Version 5.1.4.2, Build ID: 1:5.1.4-Oubuntu1. That involved converting months and displayed as decimals (i.e., European format) to U.S. format in a spreadsheet in which everything else is in U.S. format. I've added an example of that.
Comment 37 Aron Budea 2017-01-21 04:08:20 UTC
(In reply to klsu from comment #36)
> Using the above test file dated 2014-12-21 19:00 CET in Version 5.1.4.2,
> Build ID: 1:5.1.4-Oubuntu1, the spreadsheet is still showing 143 of 1000
> results not equal to zero, 72 of which are significant; so the problem still
> exists.

5.1.4.2 is quite old, if you check the bug report mentioned by Eike, the fix is in 5.3. Testing with 5.3.0.2 and doing a hard recalc on the fields shows no errors.

> The problem that lead me to the discovery of this one also still occurs in
> Version 5.1.4.2, Build ID: 1:5.1.4-Oubuntu1. That involved converting months
> and displayed as decimals (i.e., European format) to U.S. format in a
> spreadsheet in which everything else is in U.S. format. I've added an
> example of that.

This is quite interesting, the main issue is:
=MOD(24.09;1)*100 is 8.9999999,
while:
1) 0.09*100 is 9,
2) =MOD(24.09;1) is 0.09.

There's probably a reasonable explanation for it in the handling of floating point numbers, hopefully someone else can shed some light on that.
Comment 38 klsu 2017-01-21 14:14:47 UTC Comment hidden (obsolete)
Comment 39 klsu 2017-01-21 15:06:39 UTC Comment hidden (off-topic)
Comment 40 klsu 2017-01-21 15:09:55 UTC Comment hidden (off-topic)
Comment 41 Aron Budea 2017-01-22 04:49:06 UTC
Could you open a new bug report on this newly mentioned issue, and repeat your findings there? (and refer to the new report in a comment so we know about it) The originally reported issues have been fixed, and it's easier to track this other one separately.

I'd also suggest installing the latest version when testing (even if it's only a release candidate, like 5.3.0.2 now), it can be done separately from your installed version as described in [1].

[1] https://wiki.documentfoundation.org/Installing_in_parallel/Linux