Download it now!
Bug 101238 - MULTIPLE.OPERATIONS fails with Err:522 - not a duplicate of 40626
Summary: MULTIPLE.OPERATIONS fails with Err:522 - not a duplicate of 40626
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-07-31 19:54 UTC by Qaz
Modified: 2019-12-03 13:51 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet to reproduce the issue (76.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-07-31 19:54 UTC, Qaz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Qaz 2016-07-31 19:54:30 UTC
Created attachment 126502 [details]
Spreadsheet to reproduce the issue

This is not a duplicate of bug 40626 because it does not require chaining of MULTIPLE.OPERATIONS to rear its ugly head. Instead, attempting to run MULTIPLE.OPERATIONS on a set of cells which contain a simple precedence chain causes the bug to appear. An example is in the attached spreadsheet.

Expected behaviour: MULTIPLE.OPERATIONS succeeds and shows numeric results, no matter whether cell D2 points to A399, A400, or any other cell above or below in A1:A1000.

Actual behaviour: MULTIPLE.OPERATIONS fails with Err:522 if cell D2 points to A400 or any cell below. Err:522 messages then continue to contaminate the sheet, even if cell D2 it edited to point to A399 or above again. The only way to recover is to enter "=A399" in cell D2, save and re-open.

First seen in Version: 4.3.3.2 Build ID: 430m0(Build:2)

Verified as still being a problem in Version: 5.1.4.2
Build ID: f99d75f39f1c57ebdd7ffc5f42867c12031db97a

It appears that this bug tickles a hardcoded limit. It makes parametric exploration of data sets impossible. There is no obvious reason why MULTIPLE.OPERATIONS would need to include a hardcoded limit for the depth of the precedence chain. What it should do is to "stuff" a value in a column or row input cell and collect the output from the formula cell once recalculation has finished.
Comment 1 m.a.riosv 2016-07-31 21:09:57 UTC
Err:522 means a Circular reference (status bar to the right), and it happens because A1 point to D1, and A1 is referenced directly or indirectly in the A column formulas, so if D1 points to column A you have the error.

If you want to work with circular reference, set up it in Menu/Tools/Options/LibreOffice calc/Calculate - Iterative references.

Resolved as not a bug, please if you are not agree reopen it.
Comment 2 Qaz 2016-08-02 20:13:30 UTC
With respect, this *is* a bug, because there is no circular reference, MULTIPLE.OPERATIONS incorrectly claims that there is.

In the example spreadsheet I attached:

D1 precedes A1
A1 precedes A2:A1000, which includes A399
A399 precedes D2 (D2, not D1 !)

This is a two-ended chain, not a circle. There is no circular reference here.

The bug is that MULTIPLE.OPERATIONS *incorrectly* claims that there is a circular reference when the "Formulas" cell has more than a certain number of precedent calculations. In this example spreadsheet the number of cells that need to be evaluated to find the value of D2 are:

A2 -> A399 - 398 cells
A1 -> A2 - 1 cell
D1 -> A1 - 1 cell

Total of 400 precedent calculations. If you increase this slightly, for example by setting cell D2 to "=A1000" you will see the error occur.

At this point, close the spreadsheet without saving, reopen it, and just for fun set cell D2 to "=A55". This will work. You can then bisect the range to see at what number of precedent calculations it fails. I found it to be 400 as above.
Comment 3 m.a.riosv 2016-08-02 22:01:11 UTC
Found here 
https://ask.libreoffice.org/en/question/31343/lo-calc-multiple-operations-define-input/
the explanation about MULTIPLE.OPERATIONS parameters.

The meaning of the parameters of MULTIPLE.OPERATIONS are

1    Cell containing the prototype of the formula
2    Cell, which is used as first variable in the prototype
3    Actual value of the first variable
4    Cell, which is used as second variable in the prototype
5    Actual value of the second variable

If I'm not wrong in your formula 2 and 3 are inverted what leads to the circular reference. =MULTIPLE.OPERATIONS(D$2;$F2;$D1) D1 is used as first actual value the next it's D2 the cell for the formula.

The Err:522 appears when A400 or below is referenced in D2, in A400 or below and in D2, doing a hard recalc the whole column A gets Err:522.

But indeed there is a different bug, because even deleting the whole MULTIPLE.OPERATIONS formulas, Errs:522 don't disappear and the spreadsheet it's not recalculated even with hard recalc {Ctrl+Shift+F9], even deleting D1 and D2 and A1.

Same issues with Aoo 4.1

Disabling OpenCl has not any effect.

Menu/Tools/Detective - Trace error doesn't show anything.

Please Eike could you take a look.
Comment 4 Qaz 2016-08-04 19:08:51 UTC
Hi Miguel,

I created the MULTIPLE.OPERATIONS parameters in the example spreadsheet attached to this bug by:
- highlighting F2:G11
- selecting Data - Multiple Operations from the menu
- entering $D$2 in the "Formulas" field of the dialog box
- entering $D$1 in the "Column Input Cell" of the dialog box

This creates in the formulas you see, and the output is arithmetically accurate unless you exceed the magic number of 400 precedent calculations. You can verify this by, for example, entering "2" in cell D1 manually and comparing the value of A399 with the value of G3. They are the same. Similar for the other values from 1-10.

If as you suggest, you were to swap the second and third parameter of each MULTIPLE.OPERATIONS formula then you would end up with incorrect results. So the spreadsheet is constructed correctly, without any circular references. The error messages really are bogus.
Comment 5 QA Administrators 2017-09-01 11:20:15 UTC Comment hidden (obsolete)
Comment 6 QA Administrators 2019-12-03 13:51:10 UTC
Dear Qaz,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug