Bug 88737 - Array formula where the argument range (partly) overlaps the formula range gives Err:522 (circular reference)
Summary: Array formula where the argument range (partly) overlaps the formula range gi...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: low minor
Assignee: Eike Rathke
URL:
Whiteboard: target:5.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-23 09:32 UTC by raal
Modified: 2016-10-25 19:11 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
test files (43.70 KB, application/zip)
2015-01-23 09:32 UTC, raal
Details
test file with additions (5.87 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-01-23 10:27 UTC, Winfried Donkers (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2015-01-23 09:32:10 UTC
Created attachment 112706 [details]
test files

Steps to reproduce:
- open xls and xlsx file

Actual results
result of C2:F2 is Error:522

Expected results
result of C2:F2 is 1,4142  1,7321 2,0000   2,2361

Printscreen from excel and LO in the test file.
Comment 1 Winfried Donkers (retired) 2015-01-23 10:27:05 UTC
Created attachment 112710 [details]
test file with additions

I don't know if this is a Calc-bug.
The OpenFormula definition of SQRT says that the argument is a number and so does the Excel function description.

In the attachments, the argument is not a number, but a range. Were the range B2:F2, it would have worked in Calc, but the range includes the cells where the results are stored. This is IMHO an incorrect function call.

If I enter in cell B8: "=SQRT(B2:F3)<Ctrl-Shift-Enter>", I get expected correct results in row 8 and expected error messages in row 9 (I would even expect an error in B9).

The results in B3:F3 are IMHO not right: the input is a 2-row array and the output should be a 2-row array as well.
Comment 2 Eike Rathke 2015-01-23 12:33:01 UTC
Winfried, in your document the range references are in normal formula mode and as such are evaluated as position dependent automatic intersections instead of array references. If you put the same formula in A12 you'd get an error because there is no intersection between the formula cell position and the range referenced.

Raal's documents contain array formulas, the Err:522 circular reference error we produce is certainly wrong, and I'm quite sure it worked some time ago.. 

This should not be related to the SQRT() function put probably any function that expects a scalar value parameter (e.g. ABS(), ...) when passed a range in array mode when imported from Excel documents exposes this behavior. I didn't check.
Comment 3 raal 2015-04-06 18:10:23 UTC
(In reply to Eike Rathke from comment #2)

> 
> Raal's documents contain array formulas, the Err:522 circular reference
> error we produce is certainly wrong, and I'm quite sure it worked some time
> ago.. 

=> regression, bibisectrequest
Comment 4 Luuk 2015-04-06 19:04:17 UTC
maybe i do not understand the problem,

but when i enter 1,2,3,4,5 in cells C2:F2
and select cells C3:F3 and enter the formula: =SQRT(C2:F2)   (CTRL+SHIFT_ENTER)

i do get 5 SQRT's....

This happens in XLS (2013) and in Calc (4.4.1.2)


The attachment, correctly, produces a circular reference
(because a cell is updated with a formula that references to the cell itself)
Comment 5 Matthew Francis 2015-04-15 11:41:27 UTC
So the issue is apparently that while the second row of this array formula, if one were present, would have circular references, the first row should work as it is now.

The behaviour of this is the same back to OOo 3.3.0
-> + Version: Inherited from OOo 
-> - Keywords: regression
Comment 6 Eike Rathke 2016-01-15 22:44:29 UTC
Regarding my earlier comment 2, that was wrong, this case never worked in Calc.

So, to recap, the situation here is that the array formula overlaps a region of the range its argument references, hence the circular reference error. This is not particular to SQRT, it would happen with any function taking a scalar value. Short reproducer:

* in A1 enter 2
* select A2:A3
* enter array formula =SQRT(A1:A2)

When the SQRT function is interpreted at A2, an array of the contents of A1:A2 is created, and at the position of A2 it is detected that a formula is running at that position. When the second element of the array formula in A3 is interpreted, that error is processed.

This is a very rare situation and Excel seems to handle this differently. Apparently it processes each array formula part individually and obtains the argument's value of the formula in A3 after it already calculated A2, i.e. first A2:=SQRT(A1) then A3:=SQRT(A2)

Handling this could mean having to rewrite, likely entirely, how Calc processes array formulas, something not done easily and error prone and IMHO not worth the effort for this twisted corner case.

In the test case document 1 it would actually work if the formula in B3:F3 referenced B2:F2 instead of B2:F3.

Now for the case that multiple columns are also involved, like in

* in A1 enter 2
* in B1 enter 3
* select A2:B3
* enter array formula =SQRT(A1:B2)

internally the other cells of the array formula reference the top left cell that actually will hold the result matrix, so while generating the argument array interpreting A2, when obtaining the value for B2 that also sees A2 already running so the circular reference error is propagated.

I'm not sure if that part of the bug can be fixed without too much effort, need to investigate further.

Again, this happens only if the argument's range overlaps the array formula range.
Comment 7 Commit Notification 2016-01-18 23:39:21 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#88737 handle temporary array formula matrix circular reference

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 8 Eike Rathke 2016-01-18 23:41:34 UTC
Solves only the non-overlapping data range part, i.e. first row in this case.