Bug 97369 - SUM formulas, in the cell below one with the same formula (and maybe more?) are not calculate correctly in 5.1 (with more then ~100 rows)
Summary: SUM formulas, in the cell below one with the same formula (and maybe more?) a...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.0.2 rc
Hardware: All All
: high critical
Assignee: Not Assigned
URL:
Whiteboard: target:5.2.0 target:5.1.1 target:5.1.2
Keywords: bibisectRequest, regression
: 97204 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-01-25 22:01 UTC by Ljiljan
Modified: 2016-10-25 19:08 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
spreadsheet that has problem with SUM function in LO 5.1 (41.81 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-01-25 22:02 UTC, Ljiljan
Details
video description of problem (854.00 KB, video/mp4)
2016-01-25 22:04 UTC, Ljiljan
Details
test file for not functioning SUM in older file (25.35 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-01-26 21:08 UTC, Cor Nouws
Details
openCL test file 32 bits ubuntu Version: 5.2.0.0.alpha0+ Build ID: b4f60e1c7c68a6e2a8b295aeffb85573b61ad045 (33.70 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-01-27 11:15 UTC, Cor Nouws
Details
More minimal test file that exhibits the problem. Slimmed down from original bugdoc (15.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-02 10:59 UTC, Tor Lillqvist
Details
Even simpler example (12.50 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-02 13:52 UTC, Tor Lillqvist
Details
Even simpler example, now with unique numbers in the cells to make debugging easier (12.33 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-03 07:49 UTC, Tor Lillqvist
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ljiljan 2016-01-25 22:01:23 UTC
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
Comment 1 Ljiljan 2016-01-25 22:02:34 UTC
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
Comment 2 Ljiljan 2016-01-25 22:04:41 UTC
Created attachment 122207 [details]
video description of problem
Comment 3 Cor Nouws 2016-01-25 22:34:07 UTC
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?
Comment 4 Cor Nouws 2016-01-25 22:34:39 UTC
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)
Comment 5 Ljiljan 2016-01-25 22:39:15 UTC
I disabled OpenCL and when CTRL + Shift + F9 is pressed, it is actually working?
Comment 6 Cor Nouws 2016-01-25 22:46:18 UTC
I do have hardware acceleration disabled in this version/session.
And enabled experimental features.
Comment 7 m.a.riosv 2016-01-26 00:47:39 UTC
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.
Comment 8 Cor Nouws 2016-01-26 07:30:12 UTC
(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..
Comment 9 Eike Rathke 2016-01-26 18:18:57 UTC
(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
Comment 10 Cor Nouws 2016-01-26 20:57:37 UTC
(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..
Comment 11 Cor Nouws 2016-01-26 21:08:45 UTC
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
Comment 12 Cor Nouws 2016-01-26 21:16:35 UTC
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.
Comment 13 Jean-Baptiste Faure 2016-01-26 21:35:02 UTC
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
Comment 14 Ljiljan 2016-01-26 22:00:58 UTC
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.
Comment 15 Eike Rathke 2016-01-26 23:04:39 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 16 Tor Lillqvist 2016-01-27 06:12:59 UTC
Works for me on Windows, AMD A10-7800, latest drivers.
Comment 17 Tor Lillqvist 2016-01-27 06:16:46 UTC
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.
Comment 18 Tor Lillqvist 2016-01-27 06:19:52 UTC
(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.)
Comment 19 Ljiljan 2016-01-27 07:17:00 UTC
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.
Comment 20 Ljiljan 2016-01-27 07:23:46 UTC
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.
Comment 21 Ljiljan 2016-01-27 07:34:00 UTC
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?
Comment 22 Cor Nouws 2016-01-27 10:49:49 UTC
(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.
Comment 23 Cor Nouws 2016-01-27 10:52:27 UTC
Would the test file, resulting from 
  .. Options > Calc > Formula .. Detailed Calculation settings .. Details
be useful? (showing many cells with Err:509)
Comment 24 Tor Lillqvist 2016-01-27 10:59:54 UTC
(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.
Comment 25 Cor Nouws 2016-01-27 11:15:04 UTC
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
Comment 26 Tor Lillqvist 2016-01-29 11:16:44 UTC
Actually this is not an OpenCL bug, but related to the Software Interpreter, I think. Probably could be resolved as duplicate of bug #97204.
Comment 27 Ljiljan 2016-01-31 12:03:57 UTC
> 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.
Comment 28 Tor Lillqvist 2016-02-01 13:35:41 UTC
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.
Comment 29 Tor Lillqvist 2016-02-01 13:42:25 UTC
I meant E column, of course
Comment 30 Tor Lillqvist 2016-02-01 14:01:02 UTC
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...
Comment 31 Tor Lillqvist 2016-02-02 10:59:40 UTC
Created attachment 122325 [details]
More minimal test file that exhibits the problem. Slimmed down from original bugdoc
Comment 32 Tor Lillqvist 2016-02-02 11:02:24 UTC
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).
Comment 33 Tor Lillqvist 2016-02-02 11:12:16 UTC
Or actually, the trick works only for rows 3 and higher.
Comment 34 Tor Lillqvist 2016-02-02 13:52:24 UTC
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.
Comment 35 Tor Lillqvist 2016-02-02 13:53:44 UTC
("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.)
Comment 36 Tor Lillqvist 2016-02-03 07:49:32 UTC
Created attachment 122344 [details]
Even simpler example, now with unique numbers in the cells to make debugging easier
Comment 37 Ljiljan 2016-02-03 08:25:15 UTC
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?
Comment 38 Tor Lillqvist 2016-02-03 09:12:16 UTC
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.
Comment 39 Tor Lillqvist 2016-02-03 09:38:23 UTC
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.
Comment 40 Tor Lillqvist 2016-02-03 14:19:40 UTC
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;
    }
Comment 41 Tor Lillqvist 2016-02-04 11:23:11 UTC
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.
Comment 42 Tor Lillqvist 2016-02-04 11:39:39 UTC
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.)
Comment 43 Tor Lillqvist 2016-02-05 07:52:35 UTC
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.
Comment 44 Tor Lillqvist 2016-02-05 13:21:04 UTC
https://gerrit.libreoffice.org/#/c/22153/
Comment 45 Commit Notification 2016-02-08 06:40:17 UTC
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.
Comment 46 Commit Notification 2016-02-09 14:00:18 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=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.
Comment 47 Cor Nouws 2016-02-09 16:40:29 UTC
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?
Comment 48 Jan Holesovsky 2016-02-09 17:23:26 UTC
Tor: Thanks so much for fixing, and Cor - thanks for verifying!
Comment 49 Jean-Baptiste Faure 2016-02-15 05:23:58 UTC
*** Bug 97204 has been marked as a duplicate of this bug. ***
Comment 50 Commit Notification 2016-02-27 11:53:33 UTC
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.
Comment 51 Commit Notification 2016-03-01 02:43:59 UTC
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.
Comment 52 Commit Notification 2016-03-01 10:17:50 UTC
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.