Bug 96222 - Simple matrix formulas give wrong results when calc file saved in .ods is reopened (openCL)
Summary: Simple matrix formulas give wrong results when calc file saved in .ods is reo...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.3.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:5.2.0 target:5.1.1
Keywords:
Depends on:
Blocks: OpenCL
  Show dependency treegraph
 
Reported: 2015-12-03 09:48 UTC by Andy
Modified: 2016-10-25 19:11 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
The ods file where matrix formulas give wrong zeroes as results (207.56 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2015-12-03 09:48 UTC, Andy
Details
The corresponding XLS file where matrix formulas give good results (345.50 KB, application/excel)
2015-12-03 09:49 UTC, Andy
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andy 2015-12-03 09:48:17 UTC
Created attachment 120983 [details]
The ods file where matrix formulas give wrong zeroes as results

Open the file Dagum Prova.ods. In the cells H21:H261 (in bold red to be easy to find) there are simple matrix formulas like this:

=8000*SOMMA(D$22:D37*F$22:F37/10)/H$20

they basically sums elements of a product vector, a very easy and commonplace operation. 

Results in this case should give a vector of increasing values from 0 to 1. This is what they gave when created; however after you save the file in .ods format and reopen it, correct results are gone and you have a vector of zeros instead (which is plainly wrong).

Notice that if you save the file in .xls format such corruption does NOT take place: you can check this by opening the file Dagum prova.xls, which is exactly the same file but saved as XLS.

The results of formulas in the red columns are mirrored also in the chart placed to the left of the column (line in red shows the data. Again, the graph is correct in XLS but not in ODS where it shows the wrong zeros vector.

To again obtain the correct results in the ODS file is not simple nor straightforward: no F9 or ctrl+shift+F9 will do. Even if you reopen for editing the first formula in H22, change something and then change it again (e.g. add and delete a space) and input the formula, finally pasting it to the rest of the vector, you are stuck with the zeros.

A workaround I managed to find is this:
- do the re-editing of the first cell as above, then copy it;
- paste it in the single cell immediately below it (H23);
- select the two cells H22:H23 and copy them;
- select the rest of the red vector and paste the copied range  H22:H23 into it.

This way your formulas show the correct results again, and these stay on until you keep the file open, even if re-computed. But when you save it in .ODS, close and reopen, there you are again with the zeroes vector.

Of course there could be other workarounds.  
While fiddling with the problem, I found out other strange behaviour: suppose you have the zeroes vector after reopening the saved ODS file. 
Now reopen for editing a cell in the middle of the vector (say H46 for example), add a space at the end, delete it and re-input the formula with ctrl+shift+enter: with a fully erratic behaviour, you will now get correct results not only in H46, but also in all vector cells above it!! Even if you did not touch any of the H22:H45 range! At the same time the vector cells below stay with the wrong zeroes instead.
Comment 1 Andy 2015-12-03 09:49:06 UTC
Created attachment 120984 [details]
The corresponding XLS file where matrix formulas give good results
Comment 2 Andy 2015-12-03 09:52:32 UTC
I would like to raise importance to major since IMHO if in a spreadsheet you cannot trust your formulas to give consistent and correct results, things look bad....
Comment 3 Andy 2015-12-03 15:18:39 UTC
IMPORTANT NEWS
Since I was advised to try to uncheck "openCL use" in LO to solve another bug I posted (Bug 95578), and it worked, I then controlled what was happening to the problem described HERE after you  uncheck OnpenCL.
I can confirm that once you uncheck that option, the problem described here is gone for good as well.

So what remains to be seen is:
1) why is this option malignantly affecting calc in different ways
2) what do you miss after disabling it that could be of value

I googled the term very rapidly, it seems to be something that should speed up numerical computation, but things are not very clear.

Any further hint appreciated....
Comment 4 Andy 2015-12-03 15:19:56 UTC
Maybe it would be better to unify such problems in a single bug report, since the culprit seems to be the same??
Comment 5 Buovjaga 2015-12-05 19:34:36 UTC
I have OpenCL enabled for SUM among others (Tools - Options - Calc - Formula - Detailed calculation settings). For me your .ods sheet works like expected, so I guess I am luckier in my hardware..

Win 7 Pro 64-bit, Version: 5.0.3.2 (x64)
Build ID: e5f16313668ac592c1bfb310f4390624e3dbfb75
Locale: fi-FI (fi_FI)

Version: 5.2.0.0.alpha0+
Build ID: 81fa5340191baf8687f9c82f1f414f5afc86b529
Threads 4; Ver: Windows 6.1; Render: default; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-12-03_21:19:19
Locale: fi-FI (fi_FI)
Comment 6 raal 2015-12-06 19:15:24 UTC
Hello, what's your HW - graphic card? Do you have latest graphic drivers?
Comment 7 Andy 2015-12-22 09:35:52 UTC
Hi,
sorry for the delay, I have been busy with all the problems in 5.1.0.1

I do not think the problems with openCl are due to obsolete graphic card drivers; I have 3 different machine, with 3 different graphics cards, and the behaviour described is EXACTLY the same on all 3.
In any case at least on one machine, which is the most recent, with windows 8.1, nvidia drivers are automatically updated (and they update them OFTEN I must say), but it is just the same as the 2 win7 machines.
Comment 8 Eike Rathke 2016-01-26 23:08:25 UTC
@mmeeks:
This is yet another "calculating wrong with OpenCL" report.
Any estimate if, how and in what time frame it can be fixed on your side?
As an interim measure I suggest to default the OpenCL option to disabled
in all releases.
Comment 9 Commit Notification 2016-01-27 13:35:07 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=aee3355a8a7a6b7749f2eddd74e9389fe5782186

tdf#96222: Don't attempt to handle matrix formulas as formula groups

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2016-01-27 19:22:33 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4d43f0c018c1f0fb12f55ebb16c074fb60a8aa73

tdf#96222: Better to avoid anything except MM_NONE, says Eike

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2016-01-27 21:15:40 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=32333afd215ffcdc320845937dd524a08fe4f9b0&h=libreoffice-5-1

tdf#96222: Don't attempt to handle matrix formulas as formula groups

It will be available in 5.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.