Bug 103532 - [OPENCL] SUMPRODUCT returns wrong value
Summary: [OPENCL] SUMPRODUCT returns wrong value
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: high major
Assignee: Not Assigned
Depends on:
Blocks: OpenCL
  Show dependency treegraph
Reported: 2016-10-27 09:32 UTC by Michal Kaut
Modified: 2020-03-08 12:55 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

file illustrating the bug (452.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-10-27 09:32 UTC, Michal Kaut

Note You need to log in before you can comment on or make changes to this bug.
Description Michal Kaut 2016-10-27 09:32:28 UTC
Created attachment 128297 [details]
file illustrating the bug

In the attached file, SUMPRODUCT returns wrong values.
Not only that, but the same formula returns two different values in two cells:
 T19 = 20.0025562025
AE19 = 18.7191658181   (correct value)

First observed on 5.1.5, still there after upgrade to 5.1.5.

One more strange thing: if I copy formulas from AE19 to the cells below, the formulas remain correct if I copy to less than 100 cells, but switches to the wrong one if I copy to more than 100 cells (pass row AE118).
This happens both with copy & paste and using mouse (selecting AE19 and dragging down).

I use Norwegian locales with comma as a decimal separator, in case it should matter.
Comment 1 Regina Henschel 2016-10-27 10:43:22 UTC
You have to recalculate your data, Ctrl+Shift+F9. Please have a look at menu Tools > Options > LibreOffice Calc > Formula. Perhaps the default settings in section "Recalculation on File Load" do not work well for you. And look in menu Data > Calculate. Is AutoCalculate enabled?
Comment 2 Michal Kaut 2016-10-27 12:15:35 UTC
I agree it looks like an updating issue. When I press Ctrl+Shift+F9, the computed statistics (T10:X18) get updated (the mean is equal to zero as it should).
However, the values themselves (T19:X1019) are not updated - for example, T19 is still 20.0.
To update this, I have to, for example, add '+1' to the formula (which changes the value of T19 to 19.7 and then remove it again, to get the correct value.

> Please have a look at menu Tools > Options > LibreOffice Calc > Formula.
> Perhaps the default settings in section "Recalculation on File Load" do not work well for you.

The "Recalculation on File Load" section has only items for "Excel 2007 and newer" and "ODF spreadsheet (not saved by LibreOffice)", neither of which applies since the file was created in LibreOffice. Anyway, I changed both to "prompt" and do not get one when I open the file

> And look in menu Data > Calculate. Is AutoCalculate enabled?

Yes, AutoCalculate is enabled.
Comment 3 Michal Kaut 2016-10-27 12:23:47 UTC
Update to my previous comment: The +/-1 trick works for a single cell, but when I try to do copy the formulas to the whole column, I end up again with the wrong values .. this is frustrating..
Comment 4 m.a.riosv 2016-10-27 22:40:47 UTC
Version: (x64)
Build ID: c019706a50de7fcb07d0d528b09b46a887562beb
CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 
Locale: en-GB (es_ES); Calc: CL

Disabling Menu/Tools/Options/LibreOffice/OpenCL, works fine for me.

Changing to a locale with dot as separator like English-UK doesn't solve the issue.
Comment 5 Michal Kaut 2016-10-28 05:48:22 UTC
I can confirm that disabling OpenCL (setting the minimum data size for OpenCL to a very large number) removes the problem.

This also explains the strange behaviour with copy&paste from the original description, since the limit for OpenCL was set to 100.
Comment 6 QA Administrators 2017-11-24 17:34:13 UTC Comment hidden (obsolete, spam)
Comment 7 b. 2019-12-06 13:46:17 UTC
what the fu.. ??

CL-limit 100: 100 correct results then scrap, 
CL-limit 1000: 1000 correct results then scrap, 

programs like this are given to, and used by!, 'dummies', and even for military purposes ... nuked the wrong city? 'may be your CL-limit was too low, didn't you know you have to set it to an app. value?, your fault.',  

settings may affect the performance, but never the correctness of results! 

bug - may be - 'related' to the general shared formula problems?

repro (CL: T19 20,002556202506, no-CL: T19 18,7191658181081)

Version: (x64)
Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: CL

and also with - independent of setting for 'CL'!!!

Version: (x64)
Build ID: 9ab43aebad67383057d2cc3f754ce2193fa78b4e
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: GL; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US

three years, major importance, no progress ... be ashamed ... 

Comment 8 b. 2019-12-27 21:10:10 UTC
from some 'digging into' ... 

file provided has at least one cell which has a wrong value stored in it, T20: <text:p>20,0025562025061</text:p> (the content.xml file stores the formula and the value / string). 

perhaps autocal was off, or the version used was buggy. that makes any investigation difficult as plenty changes on sheet or in settings will correct the problem - by triggering recalculation - without having been the cause for it. 

values brought to screen differ acc. to the settings in 'formula - recalculate on load' (despite that normally shouldn't affect this file (neither 'ex$el' nor 'not saved with LO'), but it does, at least with 

questionable behaviour of 'recalculate on load' is discussed elsewhere. for me with 

'always recalculate' comes up with refreshed and correct values, 
'prompt user' doesn't prompt but recalculates, correct, 
'never recalculate' comes up with wrong - stored - values. 

for further investigation a new file with new description is needed, or the original system and settings where the original bug was 'found', i doubt that being accessible. 

thus pls. recheck and set to 'wfm - provided file was buggy' or similar. 


Comment 9 b. 2020-03-08 12:55:23 UTC
from some more digging: 

1.) wrong value in AE19: is stored in the file provided and needs recalc <F9>, hard recalc <ctrl-shift-F9> or 'recalc on load' to be corrected, 

2.) wrong values when dragging or copying down AE19 already occured in ver., that's not offered in the list, thus setting 'first affected' to,  

it's been less a problem of SUMPRODUCT but of openCL and 'shared formulas', dependent of openCL or 'threaded' or both being activated and copying the same formula in 100+ cells, 

it was (is) dependent of copying into 100+ rows because of a hardcoded threshold of 100 for parallelized computing (openCL or 'threaded') to kick in if its enabled in the config, 

2. is gone in ver. (and some before, was still buggy), thus setting to wfm, 

above does not! say shared formulas or openCL are clean in total, it only means that this particular facet is gone, 

ver. tested: 

Version: (x64)
Build ID: 10e20a77ce302a0475a661ad1886f2ca83c55f3f
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: CL