Bug 103701 - MULTIPLE.OPERATIONS interferes with calculations in certain conditions
Summary: MULTIPLE.OPERATIONS interferes with calculations in certain conditions
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.0.0.alpha0+ Master
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0 target:5.2.4
Keywords:
Depends on:
Blocks:
 
Reported: 2016-11-04 12:59 UTC by Vianney Stroebel
Modified: 2016-11-11 15:30 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Please use this file in order to reproduce the bug (10.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-11-04 13:00 UTC, Vianney Stroebel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Vianney Stroebel 2016-11-04 12:59:57 UTC
Description:
After many trials, the most minimal setup I could find to reproduce the bug still takes two many steps to describe here.

Steps to Reproduce:
Please open the (very short) attached ods file and try changing A.A1 to any value. Apparently, the bug needs MULTIPLE.OPERATIONS and a remote ref and two SUM functions one below the other in order to be reproduced. Very weird...

Actual Results:  
A.A2 = 1000

Expected Results:
A.A2 should be equal to A.A1


Reproducible: Always

User Profile Reset: Yes

Additional Info:
I could reproduce this bug on LibreOffice Calc 5.2.3.2 on Kubuntu Yaketi and on LibreOffice Calc 5.1.6.2 on Windows 7.


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:49.0) Gecko/20100101 Firefox/49.0
Comment 1 Vianney Stroebel 2016-11-04 13:00:44 UTC
Created attachment 128486 [details]
Please use this file in order to reproduce the bug
Comment 2 Vianney Stroebel 2016-11-04 13:09:11 UTC
This bug might be related to https://bugs.documentfoundation.org/show_bug.cgi?id=76447
Comment 3 Regina Henschel 2016-11-05 00:07:40 UTC
I think, there is a problem, but I disagree, that "A.A2 should be equal to A.A1". I think the result in cell A.C2 should be the value of A.C1.

Cell A.C2 has content "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)"
That means:
The formula template is in A.A2
The first variable in the formula template is A.A3
The actual value to replace the first variable is in A.B2
The second variable in the formula template is A.A1
The actual value to replace the second variable is in A.C1

Cell A.A2 has content "=B.C1". This formula has neither a direct reference to A.A3 nor to A.A1.

The reference "=B.C1" is resolved to "=SUM(B.A1:B.B1)" and that should be handled as "=B.A1+B.B1" and with resolving the reference in B.A1 it should be handled as "=A.A1+B.B1".

Now we do the replacements. Instead of the variable A.A1 the replacement from A.C1 is used and we get the calculation "=A.C1+B.B1" with their current values "=1000+empty" resulting in value 1000, which can be seen in cell A.C2.

But if you change A.A1 e.g. to 123 and use Ctrl+Shift+F9 to hard recalculate, you get as result the value 123. It seems that the references in the arguments of SUM are not resolved for purpose of MULTIPLE.OPERATIONS. If you use B.A1+B.B1 instead of SUM, then the reference chain of MULTIPLE.OPERATIONS resolves to "=A.A1+B.B1" and the replacement A.C1 instead of A.A1 works.

This issue needs a Calc-expert to explain, what result is the correct one for MULTIPLE.OPERATIONS.
Comment 4 Vianney Stroebel 2016-11-05 01:39:13 UTC
(In reply to Regina Henschel from comment #3)
> I think, there is a problem, but I disagree, that "A.A2 should be equal to
> A.A1".

Well, A.A2 has the formula "=$B.C1" and B.C1 has the formula "=$A.A1", so it's pretty clear that A.A2 = A.A1 (with just B.C1 between them).

I agree with what I understood from the rest of your comment.

Ctrl+Shift+F9 make the calculation right (with A.A2 = A.A1) in the case of this file, which is the most minimal example of the bug I could find. But in my real workbook with 7 interconnected sheets and much more calculations, the problem is compounded and many values are not right (some in the MULTIPLE.OPERATIONS cells, some elsewhere), and a hard recalculation doe not help in this case. I could attach the file here but I doubt you could navigate easily among the dependencies between cells.

Suffice to say a MULTIPLE.OPERATIONS formula should not interfere with the calculation of the rest of the cells. The manual recalculation (Ctrl+Shift+F9) command is useless when there are multiple dependent sheets that need a recalculation in a precise order in order to be right.

The order (and possibly the concurrence) of recalculations in the different sheets might be the culprit here.
Comment 5 Regina Henschel 2016-11-05 13:19:46 UTC
(In reply to Vianney Stroebel from comment #4)
> (In reply to Regina Henschel from comment #3)
> > I think, there is a problem, but I disagree, that "A.A2 should be equal to
> > A.A1".
> 
> Well, A.A2 has the formula "=$B.C1" and B.C1 has the formula "=$A.A1", so
> it's pretty clear that A.A2 = A.A1 (with just B.C1 between them).

I agree that the formula template in A.A2 resolves to being actually "=A.A1". But you have not considered, that "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)" has the replacement rule to use the value of A.C1 at all places where the formula template has the reference to A.A1.

For definition of MULTIPLE.OPERATIONS read http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018432_715980110
Comment 6 Vianney Stroebel 2016-11-05 13:42:28 UTC
> I agree that the formula template in A.A2 resolves to being actually
> "=A.A1". But you have not considered, that
> "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)" has the replacement rule to
> use the value of A.C1 at all places where the formula template has the
> reference to A.A1.

Don't you agree that MULTIPLE.OPERATIONS should only affect the value of the cells that actually contains the "MULTIPLE.OPERATIONS" function?

If so, why should the presence of a MULTIPLE.OPERATIONS function in A.C2 affect the value of A.A2?
Comment 7 Regina Henschel 2016-11-05 13:59:55 UTC
(In reply to Vianney Stroebel from comment #6)
> > I agree that the formula template in A.A2 resolves to being actually
> > "=A.A1". But you have not considered, that
> > "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)" has the replacement rule to
> > use the value of A.C1 at all places where the formula template has the
> > reference to A.A1.
> 
> Don't you agree that MULTIPLE.OPERATIONS should only affect the value of the
> cells that actually contains the "MULTIPLE.OPERATIONS" function?
> 
> If so, why should the presence of a MULTIPLE.OPERATIONS function in A.C2
> affect the value of A.A2?

You are right that using MULTIPLE.OPERATIONS in A.C2 should have no effect on the formula result in A.A2.

But my concerns are these and therefore I set it to New:

A.C2 should show the value of A.C1, but that is not the case after you force recalculate after a change in A.A1.

B.A1 should show the value of A.A1. But that is not the case after you change the value in A.C1.

My guess is, that the problem is the range in the SUM function in B.C1. Without this range it works as expected.
Comment 8 Vianney Stroebel 2016-11-05 14:07:25 UTC
(In reply to Regina Henschel)

I think I understand each of your messages but I don't understand your point.

If you're just confirming the bug that I filed, then... ok... we have no disagreement then.
Comment 9 Regina Henschel 2016-11-05 15:42:43 UTC
I just confirm it and list some additional problems.
Comment 10 Eike Rathke 2016-11-07 15:51:06 UTC
The problem seems to be a shared grouped formula that is created from the two subsequent SUM formulas in B.C1:C2, changing one of them, for example in C1 to =A1+B1 or in C2 to =A2+B2, makes the calculation work as expected.
Comment 11 Vianney Stroebel 2016-11-07 17:33:31 UTC
(In reply to Eike Rathke from comment #10)
> The problem seems to be a shared grouped formula that is created from the
> two subsequent SUM formulas in B.C1:C2, changing one of them, for example in
> C1 to =A1+B1 or in C2 to =A2+B2, makes the calculation work as expected.

Is a "shared grouped formula" a concept of LibreOffice Calc?
Comment 12 Eike Rathke 2016-11-07 17:43:35 UTC
A concept of Calc, a concept of Excel, maybe others as well.
Comment 13 Vianney Stroebel 2016-11-07 17:47:55 UTC
(In reply to Eike Rathke from comment #12)
> A concept of Calc, a concept of Excel, maybe others as well.

What exact name does this concept have in Calc? Googling 'libreoffice calc "grouped formula"' or 'libreoffice calc "group formula"' doesn't produce anything.
Comment 14 Eike Rathke 2016-11-07 22:13:22 UTC
It's an implementation detail, you'd need to dive into the formula cell related source code under sc/source/core/{data,tool}
Comment 15 Commit Notification 2016-11-07 23:05:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#103701 bulk-broadcast the correct hint ID for formula groups

It will be available in 5.3.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 Eike Rathke 2016-11-08 14:49:16 UTC
Pending review https://gerrit.libreoffice.org/30701 for 5-2
Comment 17 Commit Notification 2016-11-11 15:30:48 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

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

Resolves: tdf#103701 bulk-broadcast the correct hint ID for formula groups

It will be available in 5.2.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.