Bug 102851 - Err 522 (Circular Reference) when formula replaced with numeric value (intermittent but repeatable)
Summary: Err 522 (Circular Reference) when formula replaced with numeric value (interm...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.3.2 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2016-09-30 10:13 UTC by Rob Swan
Modified: 2017-11-02 18:43 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
ODS Spreadsheet file exhibiting the problem. (1.57 MB, application/vnd.oasis.opendocument.spreadsheet)
2016-10-14 09:32 UTC, Rob Swan
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Rob Swan 2016-09-30 10:13:09 UTC
Erroneous Err 522 (Circular Reference) appears, only on some occasions, when I change a (simple) formula to a numeric value - ie. when I replace the formula with an absolute value. Saving the file or re-entering (another) numeric value can clear the error. There are no circular references in the spreadsheet.

The formula concerned is repeated down an entire column (Q) and, for row 'x' is of the form: Q(x-1)+Lx-Mx. The error appears further down column Q when I replace a formula with an absolute value.

I can supply a copy of the file if necessary with details of how I can repeat the error (just email me) - it's a 1.6MB file of 1174row x 70col ssht with some non-trivial conditional formulas and it's 1.6MB.

I'm running on Debian Linux 3.16.0-4-686-pae with KDE SC Version 4.14.2
LibreOffice Build ID 430m0(Build:2)
Comment 1 Aron Budea 2016-09-30 18:53:10 UTC
First of all, please try with a current version (5.1.5 or 5.2.2). [1] has details on installing different versions separately.

[1] https://wiki.documentfoundation.org/Installing_in_parallel
Comment 2 Rob Swan 2016-10-02 15:15:17 UTC
(In reply to Aron Budea from comment #1)
> First of all, please try with a current version (5.1.5 or 5.2.2). [1] has
> details on installing different versions separately.
> 
> [1] https://wiki.documentfoundation.org/Installing_in_parallel

Have tried 5.2.2 and the problem is still there, exactly as described originally.
Comment 3 Buovjaga 2016-10-13 19:05:43 UTC
If you cannot share the original document publicly, you could try to create a minimal example document that shows us the problem.

Set to NEEDINFO.
Change back to UNCONFIRMED after you have provided the document.
Comment 4 Rob Swan 2016-10-14 09:32:10 UTC
Created attachment 128000 [details]
ODS Spreadsheet file exhibiting the problem.

Attached is the original file in which I experienced the problem.
I have 'played' with it to try and narrow down the source of the problem, which does not appear in all cases. The problem should appear if you load the file in Calc and do exactly the following:
In Cell Q306 enter 1800.00 (Replacing formula with fixed value)
Then... From Q383 to bottom of Column Q and all of Column R should display 'Err:522'
Then... In Cell Q306 enter another fixed value (ie. 1500.00) and the problem disappears. Also saving and reloading file (with fixed value in Q306) will cause the problem to disappear.
The problem does not occur when a fixed value is entered in any cell in Column Q - only some. Because of existing data (ie. fixed values in some cells Q1:Q293) the problem will not appear for values entered in cells above Q294.
Hope this helps.
Happy for file to be circulated freely.
Comment 5 Rob Swan 2016-10-14 09:34:16 UTC
Original file exhibiting the problem attached with instructions (in attachment comments) on how to exhibit/repeat the problem.
Comment 6 Rob Swan 2016-10-14 09:38:41 UTC
Not sure that instructions on how to exhibit/repeat the problem were saved as comment to attachment (??). Here they are again:

Attached is the original file in which I experienced the problem.
I have 'played' with it to try and narrow down the source of the problem, which does not appear in all cases.

The problem should appear if you load the file in Calc and do exactly the following:

In Cell Q306 enter 1800.00 (Replacing formula with fixed value)

Then... From Q383 to bottom of Column Q and all of Column R should display 'Err:522'

Then... In Cell Q306 enter another fixed value (ie. 1500.00) and the problem disappears. Also saving and reloading file (with fixed value in Q306) will cause the problem to disappear. (Other actions may also cause the problem do disappear for no obvious reason.)

The problem does not occur when a fixed value is entered in any cell in Column Q - only some. Because of existing data (ie. fixed values in some cells Q1:Q293) the problem will not appear for values entered in cells above Q294.
Hope this helps. Happy for file to be circulated freely.
Comment 7 Buovjaga 2016-10-16 17:59:14 UTC
(In reply to Rob Swan from comment #6)
> In Cell Q306 enter 1800.00 (Replacing formula with fixed value)
> 
> Then... From Q383 to bottom of Column Q and all of Column R should display
> 'Err:522'

I did not get the error. I also tried a couple of random cells in Q afterwards, but no error.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.2.2.2.0+
Build ID: 5.2.2-1
CPU Threads: 8; OS Version: Linux 4.7; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group

Arch Linux 64-bit, KDE Plasma 5
Version: 5.3.0.0.alpha0+
Build ID: 810e664de088721b6452016d83fb3ef3e6ca5eb1
CPU Threads: 8; OS Version: Linux 4.7; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on October 15th 2016
Comment 8 Rob Swan 2016-10-17 10:20:00 UTC
I encountered the bug on Debian 8 32 Bit (not 64).
Also, to get the error t generally has to be the FIRST action after loading the file. Once the error has appeared and then cleared it doesn't seem to reappear after subsequent attempts to repeat it. But it is very repeatable on my system under the right conditions.
If that doesn't help... ??
Comment 9 Buovjaga 2016-10-17 11:00:28 UTC
(In reply to Rob Swan from comment #8)
> I encountered the bug on Debian 8 32 Bit (not 64).

I talked about it on IRC and got this comment: "might be the floating point computation differences i386 extended precision vs sss/sse2 method"

Would it be possible for you to test in a 64-bit Debian, on a virtual machine or something?
Comment 10 Rob Swan 2016-10-18 13:17:58 UTC
I'll try it as soon as I can and let you know but I can't promise anything as I dare not interfere too much with my own system at present. Any pointers on instructions for setting up a 64bit Debian installation as a VM on a 32bit installation might help. Thanks.
Comment 11 Buovjaga 2016-10-18 13:20:31 UTC
(In reply to Rob Swan from comment #10)
> I'll try it as soon as I can and let you know but I can't promise anything
> as I dare not interfere too much with my own system at present. Any pointers
> on instructions for setting up a 64bit Debian installation as a VM on a
> 32bit installation might help. Thanks.

It should be possible, if your CPU supports virtualization https://forums.virtualbox.org/viewtopic.php?f=1&t=46904

Virtualbox is the easiest to get running..
Comment 12 m_a_riosv 2016-11-20 22:52:40 UTC
I can't reproduce.
Version: 5.2.3.3 (x64)
Build ID: d54a8868f08a7b39642414cf2c8ef2f228f780cf
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; 
Locale: es-ES (es_ES); Calc: group

Please test if modifying OpenCL options has some efect.
Menu/Tools/Options/LibreOffice/OpenCL
Comment 13 Rob Swan 2016-11-21 15:19:30 UTC
Modification of OpenCL settings does not resolve the problem - it occurs as before.
Unable to test on 64bit with my current system, sorry.
Comment 14 Xavier Van Wijmeersch 2017-07-14 17:38:57 UTC
I can not produce the err 522 and i don't use opencl
i have below q383 made randomly some change and have no errors

Version: 6.0.0.0.alpha0+
Build ID: 0a7fe0ab0b5b18cfbf1d9f7971d851fe00b6d36a
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 15 Xavier Van Wijmeersch 2017-07-15 14:37:25 UTC
I did another test with old laptop and no err522

old laptop Dell Latitude D620
Intel(R) Core(TM)2 cpu T5500 @1.66Ghz
build year 2006; mesa 17.1.4
Mesa DRI Intel(r) 945GM x86/MMX/SSE2

Version: 5.4.0.1.0+
Build ID: f1732a9b718e59365fa6160a7a224d7e5cc3ffb8
CPU threads: 2; OS: Linux 4.9; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86@71-TDF, Branch:libreoffice-5-4, Time: 2017-07-06_11:40:24
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 5.3.4.2
Build ID: SlackBuild for 5.3.4 by Eric Hameleers
CPU Threads: 2; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 16 Xisco Faulí 2017-10-31 17:20:04 UTC Comment hidden (obsolete)
Comment 17 Rob Swan 2017-11-02 17:58:42 UTC
The latest version of LibreOffice:

Version: 5.4.2.2
Build ID: 22b09f6418e8c2d508a9eaf86b2399209b0990f4
CPU threads: 1; OS: Linux 3.16; UI render: default; VCL: kde4; 
Locale: en-GB (en_GB.UTF-8); Calc: group

Does not/cannot reproduce the reported bug (erroneous circular reference).

I have not changed the status as I presume that is the correct thing to do.
Comment 18 Buovjaga 2017-11-02 18:43:39 UTC
Ok, good to hear the problem is gone. Closing.