User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.82 Safari/537.36 Build Identifier: LibreOffice 5.1.0.2 In some cases, SUM formulas are not calculate correctly in 5.1. CTRL + Shift + F9 does not recalculate well. However, the same ODS file opened in 5.0 works just fine. Reproducible: Always Steps to Reproduce: 1.Open the file 2.Observe column E and K: sum function is not working Actual Results: results (sum of the four cells) should be returned [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Linux (All) OS is 64bit: yes Reset User Profile?No
Created attachment 122206 [details] spreadsheet that has problem with SUM function in LO 5.1 spreadsheet that has problem with SUM function in LO 5.1
Created attachment 122207 [details] video description of problem
Thanks Ljiljan for the issue. I can confirm more or less blind - had seen the same last night but not yet filed an issue :\ In your file: removing the 'top' row (4), closing and reopening and doing a hard refresh (Ctrl_+Shift+F9) shows the number in that row.. Adding Eike - maybe he directly knows where to look?
BTW: Confirmed in Version: 5.2.0.0.alpha0+ Build ID: b4f60e1c7c68a6e2a8b295aeffb85573b61ad045 CPU Threads: 2; OS Version: Linux 4.2; UI Render: default; TinderBox: Linux-rpm_deb-x86@71-TDF, Branch:master, Time: 2016-01-20_03:20:50 Locale: nl-NL (nl_NL.UTF-8)
I disabled OpenCL and when CTRL + Shift + F9 is pressed, it is actually working?
I do have hardware acceleration disabled in this version/session. And enabled experimental features.
Hard recalc CTRL + Shift + F9 works for me with or without OpenCL enable. There is an option to recalc on file load: Menu/Tools/Options/LibreOffice Calc/Formula - Recalculation on file load, that also works for me. I think Eike have taken the same matter in other report, which I'm not able to find.
(In reply to m.a.riosv from comment #7) > Hard recalc CTRL + Shift + F9 works for me with or without OpenCL enable. For me it fixes only one row, as described in comment #3. Not the problem with all other cells :\ I have a separate case showing the same problem. But it's an older file, from what I remember, and a quick test in a new one, didn't give the trouble at all..
(In reply to m.a.riosv from comment #7) > I think Eike have taken the same matter in other report, which I'm not able > to find. Likely bug 97238
(In reply to Eike Rathke from comment #9) > (In reply to m.a.riosv from comment #7) > > I think Eike have taken the same matter in other report, which I'm not able > > to find. > > Likely bug 97238 Looking at the 8th comment there, "Disabling OpenCL solves the issue" and other symptoms, I doubt it is. Will make a test file form my case..
Created attachment 122222 [details] test file for not functioning SUM in older file Open the test file. Try Ctrl+Shift+F9, Save & close & reopen.. > Formulas in column F do not work Remove row 101-104 Save, close and reopen > Formulas in column F work fine So indeed, it has to do with number of rows too
I created a new file , rowcount 113, in Version: 5.2.0.0.alpha0+ Build ID: b4f60e1c7c68a6e2a8b295aeffb85573b61ad045 CPU Threads: 2; OS Version: Linux 4.2; UI Render: default; TinderBox: Linux-rpm_deb-x86@71-TDF, Branch:master, Time: 2016-01-20_03:20:50 Locale: nl-NL (nl_NL.UTF-8) showing the same problems.
Ctrl+Shift+F9 works for me if I disable OpenCL and if I disable the use of Software Interpreter. Disabling only OpenCL is not enough. Cor: 100 is the minimal data size for OpenCL use. See menu Tools > Options > LibreOffice Calc > Formula > Details button. Setting this number to a value >> 100 disables OpenCL for this file. Best regards. JBF
Some info (I don't know if this is useful): Original ODS file was created by Moodle (I downloaded list of students from Moodle by selecting Export to ODS) and then I created the table in LibreOffice 5.0 (OpenCL enabled). Everything was working just fine. Recently I decided to test 5.1. and noticed this problem. So when file is opened in 5.0 there is no problem, but in 5.1. problem exists. However, when OpenCL and Software Interpreter are disabled, the problem does not exist in 5.1 either.
@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.
Works for me on Windows, AMD A10-7800, latest drivers.
What OpenCL software do you have on the Linux box where the problem happens? Do you have the clinfo program? (Should be available as a package in most distros, I assume.) Run clinfo and attach the output to this bug.
(side rant: I was not able to open the video. Attaching videos to bug reports is in most cases fairly useless. It is much easier to read a good textual description of a problem. And if something is unclear in a textual description, one can always ask for clarifications, quoting the exact parts of the text that is unclear, etc. Hard to do that for a video. And then there is the problem of video formats and codecs. Personally, if a video doesn't open for me using stock software on my Mac, I just ignore it.)
I run "clinfo" and this is the output. I don't find it useful. "Number of platforms 0" However, this is the output from Tools-> Options -> LibreOffice -> Open CL Open CL blacklist Windows Intel\(R\) Croporation 9\.17\.10\.2884 Windows Intel\(R\) Croporation 4\.2\.0\.99 Open CL whitelist NVIDIA Corporation Intel\(R\) Croporation Advanced Micro DEvices, Inc\. Linux | Advanced Micro Devices, Inc\. | 1445\.5\(sse2,avx\) Regarding the issue of video attaching to post, I don't want to start difficult discussion about this but just to justify my approach. I noticed the problem and reported it (description was understandable, but the cause I could't explained with words since I have no idea why is this happening, and there is no obvious pattern so I cannot tell you to write SUM formula on C5... so I attached original ODS file). Video was additional way to demonstrate the problem. It is MP4 which even my TV (and it is not Android) could play as well as my default browser on Ubuntu 15.10 (Google Chrome without opening new application). However, if you are interested in video (since it demonstrates the problem) I recommend VLC player.
What I've observed as well: In attached ODS file, the results of SUM function is "0" for many rows. However, if I type again "=SUM(" and then select the previous three columns + ")"... the result is returned for that single cell. However, If I press CTRL + SHIFT + F9, the results is retuned to "0" again.
Another observation: If I insert an empty row (row number 8) and empty row (row number 15), all formulas are working between these two empty rows (CTRL + SHIFT + F9 is required)... then only in row number 16 the formula is working correctly and it stops working from 17 until the next empty row... and from that point, formula is working again. So I believe that somewhat is causing recalculation not to work... could it be that LO Calc thinks it reached the end of file?
(In reply to Ljiljan from comment #19) > I run "clinfo" and this is the output. I don't find it useful. > > "Number of platforms 0" Same for me. > However, this is the output from Tools-> Options -> LibreOffice -> Open CL > > Open CL blacklist > .... Same here.
Would the test file, resulting from .. Options > Calc > Formula .. Detailed Calculation settings .. Details be useful? (showing many cells with Err:509)
(In reply to Cor Nouws from comment #23) > Would the test file, resulting from > .. Options > Calc > Formula .. Detailed Calculation settings .. Details > be useful? (showing many cells with Err:509) Yes, sure, could be.
Created attachment 122238 [details] openCL test file 32 bits ubuntu Version: 5.2.0.0.alpha0+ Build ID: b4f60e1c7c68a6e2a8b295aeffb85573b61ad045 (In reply to Tor Lillqvist from comment #24) > Yes, sure, could be. See attached
Actually this is not an OpenCL bug, but related to the Software Interpreter, I think. Probably could be resolved as duplicate of bug #97204.
> Actually this is not an OpenCL bug, but related to the Software Interpreter, I > think. Probably could be resolved as duplicate of bug #97204. Yes, when software interpreter is disabled (and OpenCL enabled), everything is working just fine.
There is something mysterious with the document that I can't figure out. If I clean out everything except the rows and columns on the first sheet that contains the columns A-D of numbers and the E row of SUM formulas, it still doesn't work. Ctrl+Shift+F9 does not recalculate correctly, the results stay as zero. If I create a similar sheet from scratch, it works as expected.
I meant E column, of course
I have no idea why, but if I, in the original document: - Select row 8, right-click:Insert Rows Above - (a new row 8 with empty cells appears) - Press Ctrl+Shift+F9 - (the cells E4:E7 now populate correctly, and also E9) - keep the new row 8 selected, and right-click:Delete Rows - Press Ctrl+Shift+F9 then the E column populates correctly. Of course doing such a weird dance is not something an end-user is expected to guess to do, but just something I noticed, and which might give a hint what is wrong in the data structures or something...
Created attachment 122325 [details] More minimal test file that exhibits the problem. Slimmed down from original bugdoc
Also for the above tdf97369-minimal.ods does the "trick" in comment #30 work (for any row, as far as I see, does not have to be 4, or 8).
Or actually, the trick works only for rows 3 and higher.
Created attachment 122327 [details] Even simpler example Here the formulas sum cells in the same column. In the B column the formulas each sum 20 values from the A column (using relative addressing for both ends), and in the C column, each cell sims from A1 down to the A cell on the same row. (I.e. non-relative for the lower end of the range.) Neither produces correct results when the software interpreter is used.
("relative addressing" is a concept that is used inside Calc when a bunch of similar formulas in the same column, on successive rows, are turned into a so-called formula group. These comments are obviously intended for developers only.)
Created attachment 122344 [details] Even simpler example, now with unique numbers in the cells to make debugging easier
In new file, if you delete content of any row (complete content) and press SHIFT + F9 it works until that empty row. Could it be that software interpreter misinterpret when it should stop calculating?
One problem seems to be that in ScVectorRefMatrix::Sum() for the rows that are after the first n, where n is the number of rows affected by the formula, the function just returns some dummy zero result (sc/source/core/tool/scmatrix.cxx:3403). But how to fix it I have no idea yet.
Hmm. Simply removing this bit: if (mnRowStart >= mpToken->GetRefRowSize()) { return ScMatrix::IterateResult(0.0, 0.0, 0); } else makes the calculations in the B column in the "even simpler example" correct, but not the C column. And I have no idea whether such a change horribly breaks something else.
Changing the GetRefRowSize() to GetArrayLength() in ScVectorRefMatrix::Sum() has some beneficial effects, but then introduces other kinds of breakage for some corner cases (empty cells). if (mnRowStart >= mpToken->GetArrayLength()) { return ScMatrix::IterateResult(0.0, 0.0, 0); } else if (nDataSize > mpToken->GetArrayLength() + mnRowStart) { nDataSize = mpToken->GetArrayLength() - mnRowStart; }
When trying the above change, just delete the contents of cells A1:A4, for instance, to see another bug. You get a bunch of #VALUE! errors. What should happen is that the empty cells should be interpreted as zeroes.
Note that for empty cells, the corresponding entries in the mpNumericArray is set to NaNs. If I understand correctly, if such a value is used then in normal additions or other calculations, it should propagate and the result should also become such a "plain" NaN, but apparently it then at some stage gets turned into an #VALUE! error instead. (Note that error codes are stored in double values as a special kind of NaNs, with a non-zero payload.)
Sigh, and turning the NaNs into zeros in the ArraySumFunctor constructor isn't easy either because of constness: the mpNumericArray member of VectorRefArray is a pointer to const doubles. This is getting ridiculous.
https://gerrit.libreoffice.org/#/c/22153/
Tor Lillqvist committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a86f46ec138e0215c6c82200ab5aea35fdb217b2 tdf#97369: Fix SUMming in the software interpreter 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.
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=8e0ed97101356ba537d0f85b2971bc22eabc633b&h=libreoffice-5-1 tdf#97369: Fix SUMming in the software interpreter 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.
Hi Tor! Thanks for all the effort you've put into this - and fun (.., now and then) to read the log with surprises and cry outs ;) Now testing in Version: 5.2.0.0.alpha0+ Build ID: 6d68e88a0fc5a54bbb826379163bd0b9c6eae0fb CPU Threads: 2; OS Version: Linux 4.2; UI Render: default; TinderBox: Linux-rpm_deb-x86@71-TDF, Branch:master, Time: 2016-02-08_08:04:35 Locale: nl-NL (nl_NL.UTF-8) - My test file from comment 11 (http://bugs.documentfoundation.org/attachment.cgi?id=122222 ) now works fine, in the sense that a hard refresh is sufficient. - The same for your tdf97369-minimal.ods. Assuming that the hard refresh is OK, this could be set to Fixed and Verified?
Tor: Thanks so much for fixing, and Cor - thanks for verifying!
*** Bug 97204 has been marked as a duplicate of this bug. ***
Marco Cecchetti committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=65694793e9588106e570d82b359c9c9e25a5cf0d sc - unit tests for tdf#97369 and tdf#97587 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.
Marco Cecchetti committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cb01ea920550769e8a04b3cb809c70ed324ba342 tdf#97369/#97587 - Further fix SUMming in the software interpreter 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.
Marco Cecchetti committed a patch related to this issue. It has been pushed to "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9b7f72203f2b9f6b95d927c695f7f48b86b251ee&h=libreoffice-5-1 tdf#97369/#97587 - Further fix SUMming in the software interpreter It will be available in 5.1.2. 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.