Serious bug in Spreadsheet

Here's the place for discussion related to coding in FreeCAD, C++ or Python. Design, interfaces and structures.
ulrich1a
Posts: 1872
Joined: Sun Jul 07, 2013 12:08 pm

Serious bug in Spreadsheet

Postby ulrich1a » Mon Aug 29, 2016 12:39 pm

I wanted to do a calculation in the spreadsheet that needed brackets in order to get the right result. But after revisiting the formula, I found that the brackets where removed from my input but the result was surprisingly correct according to my input.
But after closing the document and reopening I got a different result, which corresponds to the formula as seen.

Here are the steps to reproduce:

Put in a cell the formula "=8/(2^2*2)".
You get the correct result: 1
But at looking into the cell contents, it now contains: "=8/ 2^2*2"
After closing and opening the document the cell contains the result: 4

This is not acceptable!
I got this behavoir with:

OS: Windows 7
Word size of OS: 32-bit
Word size of FreeCAD: 32-bit
Version: 0.17.8168 (Git)
Build type: Release
Branch: master
Hash: 8b3f8cc9e6b249cefe8979c8254176f62c183f9e
Python version: 2.7.8
Qt version: 4.8.7
Coin version: 4.0.0a
OCC version: 7.0.0

and
OS: Windows 7
Word size of OS: 32-bit
Word size of FreeCAD: 32-bit
Version: 0.16.6704 (Git)
Build type: Release
Branch: releases/FreeCAD-0-16
Hash: 0c449d7e8f9b2b1fb93e3f8d1865e2f59d7ed253
Python version: 2.7.8
Qt version: 4.8.6
Coin version: 4.0.0a
OCC version: 6.8.0.oce-0.17

Ulrich
User avatar
DeepSOIC
Posts: 6949
Joined: Fri Aug 29, 2014 12:45 am
Location: Saint-Petersburg, Russia

Re: Serious bug in Spreadsheet

Postby DeepSOIC » Mon Aug 29, 2016 1:05 pm

This rings a bell. I recall a similar problem report about vanishing brackets was reported, and I thought it was fixed...
eivindkvedalen
Posts: 597
Joined: Tue Jan 29, 2013 10:35 pm

Re: Serious bug in Spreadsheet

Postby eivindkvedalen » Wed Aug 31, 2016 12:41 am

ulrich1a wrote:I wanted to do a calculation in the spreadsheet that needed brackets in order to get the right result. But after revisiting the formula, I found that the brackets where removed from my input but the result was surprisingly correct according to my input.
But after closing the document and reopening I got a different result, which corresponds to the formula as seen.
Confirmed. Unfortunately, there is a bug in the unit test, so this error has been passing by unnoticed because of that :(. I'll try to submit a pull-request for a fix in a day or two.

Eivind
eivindkvedalen
Posts: 597
Joined: Tue Jan 29, 2013 10:35 pm

Re: Serious bug in Spreadsheet

Postby eivindkvedalen » Tue Nov 01, 2016 9:23 pm

ulrich1a wrote:I wanted to do a calculation in the spreadsheet that needed brackets in order to get the right result. But after revisiting the formula, I found that the brackets where removed from my input but the result was surprisingly correct according to my input.
But after closing the document and reopening I got a different result, which corresponds to the formula as seen.
Sorry for the delay on this fix. Could you please try https://github.com/eivindkv/free-cad-co ... s_20161101 before I make a pull request? It's rebased on current master.

Eivind
User avatar
sgrogan
Posts: 5319
Joined: Wed Oct 22, 2014 5:02 pm

Re: Serious bug in Spreadsheet

Postby sgrogan » Tue Nov 01, 2016 11:38 pm

ulrich1a wrote:OS: Windows 7
Word size of OS: 32-bit
Word size of FreeCAD: 32-bit
Test build: https://github.com/sgrogan/FreeCAD/rele ... _fixes_x86
ulrich1a
Posts: 1872
Joined: Sun Jul 07, 2013 12:08 pm

Re: Serious bug in Spreadsheet

Postby ulrich1a » Sat Nov 05, 2016 2:55 pm

sgrogan: thanks for providing the test build.
eivindkvedalen wrote:Could you please try https://github.com/eivindkv/free-cad-co ... s_20161101 before I make a pull request?
I made some tests. The reported bugs are solved for me. I did some more tests and found that the parentheses were still removed from the following expression:
"=((B2+B2)^B2)^B2" resulting in: "=(B2+B2)^B2^B2".
Both expressions give the same result in the spreadsheet, so there is no problem from the numerics. But from the mathematical side the latter expression should give a different result and introduces an ambiguity that can give problems. The mathematical correct result from the second expression is delivered with these parentheses: "=(B2+B2)^(B2^B2)".
The wrong interpretation will also be found in Excel, Open Office, Octave, so FreeCAD is not alone here.
Mathematical right results do deliver Gnumeric and Maxima. Gnumeric do add the needed parentheses to an expression like this "=(B2+B2)^B2^B2" in order to be compatible with excel and give at the same time the mathematical correct result.

In order to not add ambiguity to user input, I would ask for not removing the parentheses in the discussed case. The policy from Gnumeric could also be an option, but I have no decided opinion on it now.

Ulrich
eivindkvedalen
Posts: 597
Joined: Tue Jan 29, 2013 10:35 pm

Re: Serious bug in Spreadsheet

Postby eivindkvedalen » Sun Nov 06, 2016 12:20 pm

ulrich1a wrote: I made some tests. The reported bugs are solved for me. I did some more tests and found that the parentheses were still removed from the following expression:
"=((B2+B2)^B2)^B2" resulting in: "=(B2+B2)^B2^B2".
Both expressions give the same result in the spreadsheet, so there is no problem from the numerics. But from the mathematical side the latter expression should give a different result and introduces an ambiguity that can give problems. The mathematical correct result from the second expression is delivered with these parentheses: "=(B2+B2)^(B2^B2)".
The wrong interpretation will also be found in Excel, Open Office, Octave, so FreeCAD is not alone here.
Mathematical right results do deliver Gnumeric and Maxima. Gnumeric do add the needed parentheses to an expression like this "=(B2+B2)^B2^B2" in order to be compatible with excel and give at the same time the mathematical correct result.

In order to not add ambiguity to user input, I would ask for not removing the parentheses in the discussed case. The policy from Gnumeric could also be an option, but I have no decided opinion on it now.

Ulrich
Thank you for testing. Regarding the power operator associativity, I decided to use Excel's interpretation, even if this is not text-book mathematically correct, as I though compatibility with other spreadsheets would be more important. I just tried changing the associativity for the power operator, and it doesn't seem to cause trouble for bison, so I'd actually say this would be up to the community whether to change this or not.

Eivind
ulrich1a
Posts: 1872
Joined: Sun Jul 07, 2013 12:08 pm

Re: Serious bug in Spreadsheet

Postby ulrich1a » Sun Nov 06, 2016 3:22 pm

eivindkvedalen wrote: Regarding the power operator associativity, I decided to use Excel's interpretation
I am ok with this. Otherwise there will be again a problem, as currently parentheses are removed, that would cause again a different interpretation after a saving and opening.

Gnumeric goes a different way. It adds parentheses in order to force the correct result. But we can leave this out, otherwise I had to rewrite the import script. ;)

The other question is, is it possible to avoid the removal of parentheses in the following expression: "= (((2^3)^2)^3)^2" This expression is not ambiguous. But the spreadsheet converts it into "=2 ^ 3 ^ 2 ^ 3 ^ 2" which left the question open, what was the original intention.

Not that important, but I found that the saved numbers in the spreadsheet are missing the last number. Try the expression "=(2 + 2) ^ 2 ^ (2 ^ (2 ^ 2))" in Gnumeric and FreeCAD. Copy the numeric result from Gnumeric into a FreeCAD-cell and build the difference to the calculation in FreeCAD in another cell. The difference was 0 in my case. After saving and reopening the difference was -2048.

Ulrich
eivindkvedalen
Posts: 597
Joined: Tue Jan 29, 2013 10:35 pm

Re: Serious bug in Spreadsheet

Postby eivindkvedalen » Mon Nov 07, 2016 7:39 pm

ulrich1a wrote: The other question is, is it possible to avoid the removal of parentheses in the following expression: "= (((2^3)^2)^3)^2" This expression is not ambiguous. But the spreadsheet converts it into "=2 ^ 3 ^ 2 ^ 3 ^ 2" which left the question open, what was the original intention.
I'll see if I can always add these, to clarify the meaning.
ulrich1a wrote: Not that important, but I found that the saved numbers in the spreadsheet are missing the last number. Try the expression "=(2 + 2) ^ 2 ^ (2 ^ (2 ^ 2))" in Gnumeric and FreeCAD. Copy the numeric result from Gnumeric into a FreeCAD-cell and build the difference to the calculation in FreeCAD in another cell. The difference was 0 in my case. After saving and reopening the difference was -2048.
My version of gnumeric doesn't accept the formula. However, the difference is probably because double precision is not able to keep the required precision in FreeCAD's expression system, and when writing it to file some information is lost.

Eivind
ulrich1a
Posts: 1872
Joined: Sun Jul 07, 2013 12:08 pm

Re: Serious bug in Spreadsheet

Postby ulrich1a » Mon Nov 07, 2016 9:49 pm

eivindkvedalen wrote:My version of gnumeric doesn't accept the formula.
This is my fault, I copied the formula from FreeCAD. In Gnumeric it should be =((2+2)^2)^(2^(2^2))
eivindkvedalen wrote:However, the difference is probably because double precision is not able to keep the required precision in FreeCAD's expression system, and when writing it to file some information is lost.
FreeCAD does just not save the last digit of the mantissa of a number written into a cell. Adding this one digit in the input-field gives again a zero difference between a Gnumeric result copied into a FreeCAD cell and a FreeCAD calculation.

Ulrich