Bug 94924 - OpenCL: strange behavior in formulas computing
Summary: OpenCL: strange behavior in formulas computing
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.2.2 release
Hardware: x86-64 (AMD64) Windows (All)
: highest critical
Assignee: Tor Lillqvist
URL:
Whiteboard: target:5.1.0 target:5.0.4 target:5.0....
Keywords: regression
: 94271 94496 94540 97238 (view as bug list)
Depends on:
Blocks:
 
Reported: 2015-10-09 21:53 UTC by Dude76
Modified: 2016-10-25 19:17 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
partial screen shot of the same situation; original without 0 and incorrectly positive, test with added 0 on next line and correct negative result result (6.73 KB, image/png)
2015-10-09 21:53 UTC, Dude76
Details
light file sample reproducing the problem (32.18 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-09 23:16 UTC, Dude76
Details
Reduced sample file (20.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-14 11:57 UTC, m.a.riosv
Details
VLOOKUP wrong result test file (52.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-18 02:32 UTC, Robert Gonzalez MX
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dude76 2015-10-09 21:53:06 UTC
Created attachment 119471 [details]
partial screen shot of the same situation; original without 0 and incorrectly positive, test with added 0 on next line and correct negative result result

strange bug found found since last version update, in a calc file, used for many years (bank account, the doc itself and its formulas can be considered as improved).
Column D is for incomes;Column E is for outcomes;(hidden) column F is delta result of =D-E

when discovered this bug, I had 4 lines with only outcomes, but the results was ... positive.
Evaluation by small formula helper was correct (negative)
In a further line, if i put a 0 instead of empty cell in INcome, the 4 results turned to negative; if delete the 0, the four lines turned back to positive.

Finally, i have set a space instead of the 0, making errors on all the followings lines, the delete back to an empty cell and ... everything back to normal (delta negative when outcome set, positive when income set).


Strange, isn't it ?

It seems that it's only happens when there is no more "incomes" in further lines, so the bug remains on lasts "outcomes" lines.
I had to go back on every sheet ending with outcome lines and doing this space-enter-then-delete-content-of-cell woorkaround to get back correct values over the whole workbook.
Comment 1 m.a.riosv 2015-10-09 22:09:31 UTC
Hi @Dude76, thanks for reporting.

Please could you attach a sample file, as minimal as possible (remember this is a public site) to reproduce the issue.
Comment 2 Dude76 2015-10-09 23:16:29 UTC
Created attachment 119473 [details]
light file sample reproducing the problem

i have strongly enlight the original file
values in F to line 39 (and to 60 if they were not empty) are OK and negative (outcomes)
values from line 61 to 63 are OK and positives (incomes)
following values, with no more incomes later, avec positive whereas they are outcomes, so it is KO !

i have also noticed that there are impact (due to "false" positive values) on other dependants formulas results (ex : J64)

i have put comments in the file too.
Comment 3 Dude76 2015-10-09 23:16:54 UTC
yes, finaly i can;
I believed that my previous "workaround" had definitively solved the problem but not, new values produced the same problem, and, worst, it had impacts the previous ones too :(
Comment 4 raal 2015-10-10 06:50:21 UTC
F64 =D64-E64 = 0 - 665 = -665

It's correct in Version: 5.1.0.0.alpha1+
Build ID: 8273350ff48f198efc9dc9c5de5519b8cbdc0cb3
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2015-10-07_05:54:26

It's incorrect in  5.0.2.2 (x64), win7, but correct after hard recalc (ctrl+shift+f9). Try to do hard recalc (ctrl+shift+f9).
Comment 5 Dude76 2015-10-10 10:54:33 UTC
I did it. Bad results.
The previous results that were "corrected" by the "workaround" are now bad.

If with the same version you don't have the same bahavior, maybe it could indicate a corrupted installation ?
I will uninstall / reboot / install.
I didn't notice the problem in the previous version.
Comment 6 Dude76 2015-10-10 11:43:15 UTC
Well, missed.
re-download the installer, uninstall, reboot, install, the problem remains.

other point noticed : when i put/delete a 0 below, it changes the value in F (as previously noticed) but it doesn't changes in cascades the other values.
ex in the provided sample:
D74: 0 -> F72=>-500 but G72 doesn't refresh the calculated value (could be done by forcing but, well, automatic re-computing is expected)
Comment 7 m.a.riosv 2015-10-10 14:44:11 UTC
For my trials the issue is in relation with the OpenCL:

a) If it is active (Menu/Tools/LibreOffice/OpenCL - OpenCL options), entering zero in D72 produces -500 in F72, deleting the zero on D72 produces 500 in F72, wrong result.

b) If it is deactivated, entering zero in D72 produces -500 in F72, deleting the zero on D72 produces -500 in F72, right result.

Reproducible:
Win10x64
Version: 5.0.3.1 (x64) Build ID: fd8cfc22f7f58033351fcb8a83b92acbadb0749e
Version: 5.1.0.0.alpha1+ (x64) Build ID: 09fc6fef2d03ca8558dd6f0eec45d61ceb282cb5
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-09-27_22:33:48

Last working for me:
Version: 4.4.7.0.0+ Build ID: 2ca166ebbe2adbd8f129403c7f8e5646c70ab67d
TinderBox: Win-x86@51-TDF, Branch:libreoffice-4-4, Time: 2015-10-07_06:28:19

Really a very bad issue for a spreadsheet.
Comment 8 Dude76 2015-10-10 17:29:39 UTC
Really thanks (and congrats) for qualifying the origin of the bug.
I feel less silly :)

I confirm the behavior following the OPENCL setting on my file.
Good luck/courage for solving it.

Bests regards.
Comment 9 Joel Madero 2015-10-10 19:09:51 UTC
*** Bug 94496 has been marked as a duplicate of this bug. ***
Comment 10 Jacques Guilleron 2015-10-11 06:07:40 UTC
*** Bug 94540 has been marked as a duplicate of this bug. ***
Comment 11 Oliver Brinzing 2015-10-11 07:06:34 UTC
.
Comment 12 Tor Lillqvist 2015-10-14 09:38:23 UTC
Sorry, but would it be possible to get an even simpler sample document that has just one simple description that says explicitly "the cell XX should be X, but with OpenCL turned on, it is Y"

I find it hard to parse the comments in the existing Sample.ods...
Comment 13 Tor Lillqvist 2015-10-14 11:31:57 UTC
Or actually, comment #7 is clear enough. (Empty or numeric zero in D72 has different impact on F72 depending on whether OpenCL is on or not.) Will use that to verify any fix I come up with.
Comment 14 m.a.riosv 2015-10-14 11:57:04 UTC
Created attachment 119605 [details]
Reduced sample file

Added a reduced sample file

Remarkable, for verify the issue, on the reduced sample file with only a few data, reduce the value in:
Menu/Tools/Options/LibreOffice calc/Formula - Detailed calculation settings - Custom - Minimum data size for OpenCL use
to 2.

Introducing a zero and deleting it on D3 changes the result in E3 from -500 to 500.
With OpenCL disable result in E3 is always -500.
Comment 15 Commit Notification 2015-10-15 11:05:11 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

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

tdf#94924: Fix handling of empty cells in OpenCL subtraction

It will be available in 5.1.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 16 Commit Notification 2015-10-15 11:05:15 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

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

tdf#94924: Fix handling of empty cells in OpenCL division

It will be available in 5.1.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 17 Commit Notification 2015-10-15 11:05:19 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

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

tdf#94924: Return correct #DIV/0! error from AVERAGE in the OpenCL case

It will be available in 5.1.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 18 Commit Notification 2015-10-15 11:05:23 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

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

tdf#94924: Return correct result 0 from OpenCL MIN and MAX when all args empty

It will be available in 5.1.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 19 Commit Notification 2015-10-15 11:05:27 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

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

tdf#94924: Add a more systematic OpenCL unit test

It will be available in 5.1.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 20 Commit Notification 2015-10-15 11:36:49 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

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

tdf#94924: Fix thinko from bdef079b39d273d2985d360a52597672c96b43fc

It will be available in 5.1.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 21 Commit Notification 2015-10-16 14:14:08 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b76c450eb7f6517fae24a0d7ea6431663959adf3&h=libreoffice-5-0

tdf#94924: Fix handling of empty cells in OpenCL subtraction

It will be available in 5.0.4.

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 22 Commit Notification 2015-10-16 14:14:15 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=958ef1a5bff0b7d0879a4ae34a5e7f27eadd903b&h=libreoffice-5-0

tdf#94924: Fix handling of empty cells in OpenCL division

It will be available in 5.0.4.

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 23 Commit Notification 2015-10-16 14:14:20 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a6fa35ab0efc1d0f0d74e70898cc31be9d672190&h=libreoffice-5-0

tdf#94924: Return correct #DIV/0! error from AVERAGE in the OpenCL case

It will be available in 5.0.4.

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 24 Commit Notification 2015-10-16 14:14:29 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=127c268387e25a1f1c4b75b5991a83d383be7b92&h=libreoffice-5-0

tdf#94924: Return correct result 0 from OpenCL MIN and MAX when all args empty

It will be available in 5.0.4.

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 25 Commit Notification 2015-10-16 14:14:40 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9db43e72088faa8326ca941b84d422726448293a&h=libreoffice-5-0

tdf#94924: Add a more systematic OpenCL unit test

It will be available in 5.0.4.

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 26 m.a.riosv 2015-10-17 22:38:11 UTC
Thank you very much @Tor, for such a quick resolution.

Verified:
Win10x64
Version: 5.1.0.0.alpha1+ Build ID: 186f32f63434e16ff5776251657f902d5808ed3d-GL
 TinderBox: Win-x86@39, Branch:master, Time: 2015-10-16_09:42:47
Comment 27 Robert Gonzalez MX 2015-10-18 02:31:14 UTC
Hello

What about Bug 94540 - EDITING: VLOOKUP wrong result #VALUE in CALC 5.0.2.2.
it was marked as duplicate of this bug.

Disabiling OpenCL performs well, but if activated it displays #VALUE

Tested today with Version: 5.1.0.0.alpha1+
Build ID: d9cf47449e88032803aa4fa3429cd607a074d5ad-GL
TinderBox: Win-x86@39, Branch:master, Time: 2015-10-17_11:55:47
Locale: es-MX (es_MX)
On Windows 10.
Comment 28 Robert Gonzalez MX 2015-10-18 02:32:22 UTC
Created attachment 119707 [details]
VLOOKUP wrong result test file
Comment 29 Tor Lillqvist 2015-10-18 07:42:19 UTC
Sorry, but the instructions on Sheet3 of that document don't work. They say at the first step that "correct result displays" but don't say what that correct result should be. (For me, if I follow the instructions exactly, L2 on the Sheet3 displays #N/A. I assume that is not what is expected?)

Please, if you want to provide a test document, just provide a sheet where doing a Ctrl-Shift-F9 after toggling "Tools:Options:OpenCL:Allow use of OpenCL" changes calculation results. That is all that is needed. No need to describe multiple steps of saving, loading, copying and pasting. (Having over 100 copies of a formula in sequential rows in a column  will make OpenCL be used, as long as the formula uses only operators and functions from the OpenCL-enable-subset.)
Comment 30 Tor Lillqvist 2015-10-18 07:45:11 UTC
I assume the problem in the instructions is that the formula should use Movtos.A2, not just A2? Then I see the issue.
Comment 31 Tor Lillqvist 2015-10-18 07:46:19 UTC
Anyway, I am not sure if we want to mark all problems in OpenCL as duplicate of this bug or not? Clearly this VLOOKUP issue is not the same as that which affected subtraction, for instance.
Comment 32 Commit Notification 2015-10-18 20:03:54 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

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

tdf#94924: If we can't handle strings, don't try to then

It will be available in 5.1.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 33 ralf.gandenberger 2015-10-21 16:06:45 UTC
I reported a bug, which caused a wrong calculation (see bug 94271). Turning off OpenCL solves the issue. Can  you please check whether your solutions solves the issue in bug 94271 as well. Can I be of any help ?
Comment 34 Joel Madero 2015-10-21 22:55:08 UTC
*** Bug 94271 has been marked as a duplicate of this bug. ***
Comment 35 Commit Notification 2015-10-22 15:52:39 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2eac5c406f10f10c943d0c128dfe6a5db3972e1a&h=libreoffice-5-0-3

tdf#94924: Fix several OpenCL problems

It will be available in 5.0.3.

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 36 Joel Madero 2015-12-14 02:41:47 UTC
Seems like a bibisect isn't needed here as a commit has already been pushed.

Can this be closed as FIXED?
Comment 37 Tor Lillqvist 2015-12-15 15:10:31 UTC
I think so yes.
Comment 38 Tor Lillqvist 2016-01-29 09:47:04 UTC
*** Bug 97238 has been marked as a duplicate of this bug. ***
Comment 39 Commit Notification 2016-02-02 17:23:31 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f50edb1aabaf9de37782e63abd109e2276bd0c4&h=libreoffice-5-0

tdf#97150: tdf#94924: If we can't handle strings, don't try to then

It will be available in 5.0.6.

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 40 Commit Notification 2016-02-02 23:28:17 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=320246d24d8a07cdb6b8400e8afa48696cc39343&h=libreoffice-5-0-5

tdf#97150: tdf#94924: If we can't handle strings, don't try to then

It will be available in 5.0.5.

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.