Serious bug in Spreadsheet
Serious bug in Spreadsheet
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: 32bit
Word size of FreeCAD: 32bit
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: 32bit
Word size of FreeCAD: 32bit
Version: 0.16.6704 (Git)
Build type: Release
Branch: releases/FreeCAD016
Hash: 0c449d7e8f9b2b1fb93e3f8d1865e2f59d7ed253
Python version: 2.7.8
Qt version: 4.8.6
Coin version: 4.0.0a
OCC version: 6.8.0.oce0.17
Ulrich
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: 32bit
Word size of FreeCAD: 32bit
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: 32bit
Word size of FreeCAD: 32bit
Version: 0.16.6704 (Git)
Build type: Release
Branch: releases/FreeCAD016
Hash: 0c449d7e8f9b2b1fb93e3f8d1865e2f59d7ed253
Python version: 2.7.8
Qt version: 4.8.6
Coin version: 4.0.0a
OCC version: 6.8.0.oce0.17
Ulrich
 DeepSOIC
 Veteran
 Posts: 7900
 Joined: Fri Aug 29, 2014 12:45 am
 Location: used to be SaintPetersburg, Russia
Re: Serious bug in Spreadsheet
This rings a bell. I recall a similar problem report about vanishing brackets was reported, and I thought it was fixed...

 Posts: 602
 Joined: Tue Jan 29, 2013 10:35 pm
Re: Serious bug in Spreadsheet
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 pullrequest for a fix in a day or two.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.
Eivind

 Posts: 602
 Joined: Tue Jan 29, 2013 10:35 pm
Re: Serious bug in Spreadsheet
Sorry for the delay on this fix. Could you please try https://github.com/eivindkv/freecadco ... s_20161101 before I make a pull request? It's rebased on current master.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.
Eivind
Re: Serious bug in Spreadsheet
Test build: https://github.com/sgrogan/FreeCAD/rele ... _fixes_x86ulrich1a wrote:OS: Windows 7
Word size of OS: 32bit
Word size of FreeCAD: 32bit
"fight the good fight"
Re: Serious bug in Spreadsheet
sgrogan: thanks for providing the test build.
"=((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
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:eivindkvedalen wrote:Could you please try https://github.com/eivindkv/freecadco ... s_20161101 before I make a pull request?
"=((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

 Posts: 602
 Joined: Tue Jan 29, 2013 10:35 pm
Re: Serious bug in Spreadsheet
Thank you for testing. Regarding the power operator associativity, I decided to use Excel's interpretation, even if this is not textbook 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.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
Eivind
Re: Serious bug in Spreadsheet
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.eivindkvedalen wrote: Regarding the power operator associativity, I decided to use Excel's interpretation
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 FreeCADcell 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

 Posts: 602
 Joined: Tue Jan 29, 2013 10:35 pm
Re: Serious bug in Spreadsheet
I'll see if I can always add these, to clarify the meaning.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.
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.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 FreeCADcell 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.
Eivind
Re: Serious bug in Spreadsheet
This is my fault, I copied the formula from FreeCAD. In Gnumeric it should be =((2+2)^2)^(2^(2^2))eivindkvedalen wrote:My version of gnumeric doesn't accept the formula.
FreeCAD does just not save the last digit of the mantissa of a number written into a cell. Adding this one digit in the inputfield gives again a zero difference between a Gnumeric result copied into a FreeCAD cell and a FreeCAD calculation.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.
Ulrich