On updating to LibreOffice 6.3.0.4 I see a regression with a spreadsheet which was also the subject of bug 86978. Upon opening the sheet I see a banner saying that automatic update of external links has been disabled. At this point, cell G11 ('Shares in' for Child A) is showing correct data. I click 'Enable Content', and the fund prices are updated from the nearby CSV file. Now cell G11 says 'Err:522'. I click to edit cell G11. I add a space after the formula and hit enter. Now it works again. This was working OK until today, when I updated to LO 6.3.0.4. Not yet sure precisely which version I had before; this is Ubuntu 18.04. This problem doesn't seem to occur with the cut-down repro in bug 86978; I'll work on a new repro or perhaps just provide the real one in private on request. The interesting thing to note about cell G11 is that it's the first cell where there is a manual payment to child B's account. So cell F11 contains "" because the date column A11 contains a manually entered date, and the ISFORMULA() check in column D notes that it is not an automatic monthly payment. If I change the formula in cell G11 to just: =IF($A11<>"",G10,"") Then save and reopen the spreadsheet and allow the data to update, then everything works fine. If I change it to this: =IF(F11<>"",F11+G10,"B") the problem still doesn't recur after reload+update (although obviously things aren't working right from row 11 down now). Change it back to what it should be: =IF(F10<>"",F10+G9,IF($A10<>"",G9,"")) and the problem recurs. Setting cell D11 manually to zero does make the Err:522 go away and the correct result appear, temporarily, but the problem recurs after reload+update. Ctrl-Shift-F9 does not make the problem go away. Highlighting the offending cell and hitting F9 on it does. There is a later row where Child B received a payment but child A did not. That one is not affected.
Correction: I think the later cells where the date column A is manually entered *are* suffering from the problem but fixing the first one causes them to be recalculated... which fixes them too. Curiously, this only affects Child A and not Child B. It also doesn't affect row 10, which has a manual payment to Child A. Although as noted, making row 11 into a manual payment to Child A (even of a non-zero value) doesn't make it stop happening.
Created attachment 153505 [details] CSV data file
Created attachment 153506 [details] Spreadsheet
There is a second regression here, I'm not sure if it's just fallout of the first. On 'Sheet3' see the value at O9. This is an average monthly growth rate. Set it to 0.3425 and observe that the 'ChildA APR' value in T3 comes out basically equal to the actual value of the investment, in S3. Now, open the spreadsheet again, allow it to load the CSV content, hit F9 on Sheet1!G11 and ask the goal seeker to calculate that 0.3425 for you (Formula call Sheet3!T3, target value 194.82, variable cell Sheet3!O9). It fails, and asks to insert the "closest value (-156.94%)' into the variable anyway. (If it works, you probably ignored my instruction to open the spreadsheet again after manually setting the value ) This always used to work, and *still* seems to work for Child B. It's also triggered by macros (and yes, I know you can still work out my children's names because I was too lazy to anonymise those too.) Please let me know if I should file a separate bug for that one, or if it's related to the first.
After loading the spreadsheet and hitting F9 on cell G11, hitting Ctrl-Shift-F9 actually makes the problem come *back*, with G11 saying 'Err:522' again.
Regression introduced by: https://cgit.freedesktop.org/libreoffice/core/commit/?id=4aea832ffebefd62e5f9a3b34dca493b21a16bb0 author Dennis Francis <dennis.francis@collabora.com> 2019-02-05 16:55:08 +0530 committer Dennis Francis <dennis.francis@collabora.com> 2019-02-11 10:54:08 +0100 commit 4aea832ffebefd62e5f9a3b34dca493b21a16bb0 (patch) tree f5cc1a40fbf6dae1b8658796ca05c10b7161888d parent c04eb27affb8f6284d01dbd9f8b3c92a7979087a (diff) tdf#121388: Disable threading and dep evaluation for IF... Bisected with: bibisect-linux64-6.3 Adding Cc: to Dennis Francis
(In reply to David Woodhouse from comment #5) > After loading the spreadsheet and hitting F9 on cell G11, hitting > Ctrl-Shift-F9 actually makes the problem come *back*, with G11 saying > 'Err:522' again. Can you please check if the issue exists if you disable: Menu Tools/Options.../LibreOffice Calc/Calculate CPU threading settings [ ] Enable multi-threaded calculation i can only reproduce 'Err:522' with *enabled* multi-threaded calculation. Version: 6.4.0.0.alpha0+ (x64) Build ID: 082e95f684e44954275dc58e306b8dc69590ac80 CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: en-US (de_DE); UI-Language: en-US
Indeed, turning off multithreaded calculation seems to make the problem go away (including the goal seek problem).
Try turning off multi-threaded calculation in options.
that option shouldn't be there while not working reliable ... !!
suspicious, but perhaps quite easy ... G11 is not the initial fail, but depends on G10, G10 is not the initial fail but depends on G9, G9 is not the initial fail but depends on F9, F9 depends on B9 and D9, B9 is 158,300 on load (german locale here), updating the external links changes it to 158.300, with a '.' instead of ',', that breaks computability. after setting my locale from de_DE to en_US it worked much better ... no more problems with G11 with ver. Version: 6.4.0.1 (x64) Build ID: 1b6477b31f0334bd8620a96f0aeeb449b587be9f CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: en-US (de_DE); UI-Language: en-US Calc: CL CL and threaded G11 calculates to 41.162, pls. check if correct. besides: from OP: > Change it back to what it should be: > =IF(F10<>"",F10+G9,IF($A10<>"",G9,"")) that formula is in cell G10 in the file i downloaded, not in G11 ... have fun ...
@Xisco, I can't reproduce the original issue of G11 getting Err:522 in master. It probably got fixed by : https://cgit.freedesktop.org/libreoffice/core/commit/?id=0f4ba703038fb8678d4b1e7e6e0fd5e2d3025908 The fix is in 6.4 and also got backported to 6.3 Can you confirm ? Thanks.
(In reply to David Woodhouse from comment #4) > There is a second regression here, I'm not sure if it's just fallout of the > first. > > On 'Sheet3' see the value at O9. This is an average monthly growth rate. > Set it to 0.3425 and observe that the 'ChildA APR' value in T3 comes out > basically equal to the actual value of the investment, in S3. > > Now, open the spreadsheet again, allow it to load the CSV content, hit F9 on > Sheet1!G11 and ask the goal seeker to calculate that 0.3425 for you (Formula > call Sheet3!T3, target value 194.82, variable cell Sheet3!O9). It fails, and > asks to insert the "closest value (-156.94%)' into the variable anyway. > > (If it works, you probably ignored my instruction to open the spreadsheet > again after manually setting the value > ) > > This always used to work, and *still* seems to work for Child B. It's also > triggered by macros (and yes, I know you can still work out my children's > names because I was too lazy to anonymise those too.) > > Please let me know if I should file a separate bug for that one, or if it's > related to the first. Yes, please file a separate one for this as it is a different issue.
(In reply to David Woodhouse from comment #4) from just poking around ... > On 'Sheet3' see the value at O9. This is an average monthly growth rate. Set it to 0.3425 and observe that the 'ChildA APR' value in T3 comes out basically equal to the actual value of the investment, in S3. - ok, but had to key in 0.003425, with typed 0.3425 i get shown 34.25% and impressive results in T3, > Now, open the spreadsheet again, - it asks me to enable macros, if i do it replaces the 0.3425 with a refined value, > allow it to load the CSV content, hit F9 on Sheet1!G11 and ask the goal seeker to calculate that 0.3425 for you (Formula call Sheet3!T3, target value 194.82, variable cell Sheet3!O9). It fails, and asks to insert the "closest value (-156.94%)' into the variable anyway. - it works and finds something near 0.34, regardless if macros allowed or not, threaded, unthreaded, CL, unCL ... > (If it works, you probably ignored my instruction to open the spreadsheet again after manually setting the value) - no, i did reload, plenty tests, just place a link to the follow up bug and a detailed description there ... all tests with ver: Version: 6.4.0.1 (x64) Build ID: 1b6477b31f0334bd8620a96f0aeeb449b587be9f CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: en-US (de_DE); UI-Language: en-US Calc: thr+CL as well as with unthr+unCL reg. b.
@Dennis: with below ver. and locale set to en_US no problem with cell G11, set wfm? Version: 7.0.0.0.alpha0+ (x64) Build ID: 61d8d991a27c3bfe70e3b8d3b4ce4d8a41d18d2d CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win; Locale: en-US (de_DE); UI-Language: en-US Calc: CL
besides bug not reproducible here: seen that G11 is the 'second from top' cell of a 100+ column of 'shared formulas' (rows below 80 have no values but the formulas are prepared), there are still issues for this specific position if any parallelizing option is enabled ('threaded' or openCL), see e.g. https://bugs.documentfoundation.org/show_bug.cgi?id=124577#c10 that bug is still in Version: 7.0.0.0.alpha0+ (x64)
Closing. See comment 12 and confirmation in comment 15.