Description: Bug relates Calc - automatic and manual recalculation does not recalculate all cells. The video https://www.youtube.com/watch?v=i4VqXRRXi68 is a maths problem - "what is the average distance between two random points in a square?" I put the equations in and noticed that some cells in a couple of rows at the bottom of the spreadsheet did not recalculate when changed a cell or when I hit F9. I put sum/average checks in, and found that a cell being "selected" affected whether the affected cells were recalculated. I reduced the number of rows from 100 to 10 and all cells recalculated as normal. I restored the lines and played around; reducing the number of lines by 1 or 5. The bug reappeared and disappeared. I couldn't pin down an exact number of lines because that seems to vary, but it's unaffected by undo/redo edit cycles, and most of the test failures above were replicated on a duplicated sheet in the same document. Steps to Reproduce: --- setup 01. Add headings x1, y1, x2, y2, dx, dy, d 02. Set A1:D1 of =rand() [x1, x2, y1, y2] 03. Set E1=C1-A1 and F1=D1-B1 [dx, dy] 04. Set G1 = SQRT(E1*E1 + F1*f1) [d] 05. Copy row A a further 99 times. 06. Make A102 =SUM(B2:B101), and A103 =AVERAGE(A2:A101) 07. Copy A102:A103 across to column G 08. Set E104 to =E102/E103 and copy across to column G --- test 1 - cells dont' recalculate 09. All of E104:G104 should be 100 10. Hit F9: All cells update, E102:G103 DO NOT recalculate. --- test 2 - clicking into an affected cell fixes the recalculate 11. Click any cell in E102:G103 12. Hit F9: The clicked cell updates, the rest of E102:G103 DO NOT 13. The check in row 104 below the clicked cell is NOT 100 --- test 3 - clicking out of the affected cell breaks the recalculate 14. Click elsewhere, eg E104 15. Hit F9: All cells update, E102:G103 DO NOT recalculate. --- test 4 - deleting rows fixes the recalculate 16. Delete 5-10 rows from the top of the sheet. 17. Hit F9. All cells update and E102:G103 DO recalculate. 18. All of E104:G104 should be 95 --- test 5 - replacing the rows breaks the recalculate 19. Press Ctrl-Z 20. Hit F9. All cells update and E102:G103 DO recalculate, but this only happens once. 21. All of E104:G104 should be 100 22. Hit F9: All cells update, E102:G103 DO NOT recalculate. --- test 6 - ctrl-Y, ctrl-Z fixes/breaks the recalculate 23. Hit Ctrl-Y, F9 works as expected. Hit Ctrl-Y, F9 works once then it doesn't --- end of tests Actual Results: Some of the cells in the spreadsheet do not recalculate. Expected Results: I expected all of the cells in the spreadsheet to recalculate. Reproducible: Always User Profile Reset: Yes Additional Info: I opened another instance of Calc and followed the instructions under setup in "Steps to Reproduce". The erroneous behaviour was present in that sheet too, so I am confident this is a real bug in spite of the fact that it's the first time I've seen such behaviour in ~35 years. I will attach the original .ods file and the one I used to test my instructions. The latter is "clean" - no edits other than those required to enter the data. I "restarted in safe mode"; the faulty behaviour *is* present in the 2nd file.
Created attachment 197055 [details] Test file which demonstrates recalculate bug Original file where I first noticed the bug.
Created attachment 197056 [details] File created by following my setup instructions in 'steps to reproduce' This file was created by following the steps 1-10 in my setup instructions. It wasn't edited to try and isolate the bug like the other file was.
Using attachment 197056 [details], and following the steps (starting from step 08): (In reply to Martin from comment #0) > 08. Set E104 to =E102/E103 and copy across to column G > > --- test 1 - cells dont' recalculate > > 09. All of E104:G104 should be 100 > 10. Hit F9: All cells update, E102:G103 DO NOT recalculate. > > --- test 2 - clicking into an affected cell fixes the recalculate > > 11. Click any cell in E102:G103 > 12. Hit F9: The clicked cell updates, the rest of E102:G103 DO NOT > 13. The check in row 104 below the clicked cell is NOT 100 > > --- test 3 - clicking out of the affected cell breaks the recalculate > > 14. Click elsewhere, eg E104 > 15. Hit F9: All cells update, E102:G103 DO NOT recalculate. > > --- test 4 - deleting rows fixes the recalculate > > 16. Delete 5-10 rows from the top of the sheet. > 17. Hit F9. All cells update and E102:G103 DO recalculate. > 18. All of E104:G104 should be 95 > > --- test 5 - replacing the rows breaks the recalculate > > 19. Press Ctrl-Z > 20. Hit F9. All cells update and E102:G103 DO recalculate, but this only > happens once. > 21. All of E104:G104 should be 100 > 22. Hit F9: All cells update, E102:G103 DO NOT recalculate. I do not reproduce the behavior described in comment 0 (and quoted here) when using: Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: c3c29d31d77ff93aa50634cfd51c62d12dc0f6ec CPU threads: 4; OS: Windows 10 X86_64 (10.0 build 19045); UI render: default; VCL: win Locale: en-US (es_AR); UI: en-US Calc: threaded In my test, I have not used OpenCL, no hardware acceleration, and no Skia. There is a chance that changing some combination of those settings, the problem would show up, but I have not tried them. Maybe the OP might want to test such settings; please report back. As for "test 6", I have not tested it. > > --- test 6 - ctrl-Y, ctrl-Z fixes/breaks the recalculate > > 23. Hit Ctrl-Y, F9 works as expected. Hit Ctrl-Y, F9 works once then it > doesn't > > --- end of tests
I can't reproduce Version: 24.8.2.1 (X86_64) / LibreOffice Community Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded
Such a very useful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article. https://www.thedarkattitude.com/women-corset
> In my test, I have not used OpenCL, no hardware acceleration, and no Skia. I'm not familiar with those settings. If they are available in 24.8.2.1 could you tell me how to tweak them? Is there a userland test suite that I can run in order to try and pin things down? I did clone the repository but I couldn't see anything obvious. I concede that this is an pretty unusual "bug" and, if real, it should have seen elsewhere, so I made a short screencap (partly for my own sanity) to demonstrate the unexpected behaviour. I will upload it after posting this message. If it hits the file size limit I'll have to find a way to shrink it.
Created attachment 197096 [details] 27 second video of the missing recalculation behaviour Short vid demonstrating the issue. E104:G104 are 100 because there are 100 rows in the simulation. I hit F9 expecting red cells to recalculate, but they do not. I click into one of the red cells and hit F9. That cell recalculates, the others do not. The cell in row 104 (sum/average) changes from 100 to ??? I delete 5 rows from the top of the sheet and return to the bottom. E104:G104 are 95 because there are now only 95 rows in the simulation. I hit F9 expecting red cells to recalculate, and they do. I hit Ctrl Z, then F9, bug comes back. I hit Ctrl Y, then F9, bug goes away.
Reproducible with OpenCL enable Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 3aa81e4735ea895a71852b18ed029ae15e53c7b4 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded First version that fails on the ones I have installed. Versión: 5.4.7.2 Id. de compilación: c838ef25c16710f8838b1faec480ebba495259d0 Subprocs. CPU: 16; SO: Windows 6.2; Repres. IU: predet.; Configuración regional: es-ES (es_ES); Calc: CL Latest version that works on the ones I have installed. Version: 4.4.7.2 Build ID: f3153a8b245191196a4b6b9abd1d0da16eead600 Locale: es_ES
Here is the version info for my instance: Version: 24.8.2.1 (X86_64) / LibreOffice Community Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13 CPU threads: 8; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Raster; VCL: win Locale: en-GB (en_GB); UI: en-GB Calc: CL threaded I don't know if it's relevant, but I'm running it on an "Acer Aspire V3-571G" which has an "i7-3610QM" CPU
Hard recalculate seems to work correctly. Note that AutoCalculate is enabled, so when the RAND() values are recalculated (by F9), the "dependent" cells (of the active cell on focus) should autocalculate too (i.e. potentially, part of the cells in the cell range E102:G103). In order to update _all_ the results (independent of the cell that has the focus), Hard Recalculate should be used. Note that other "update" actions (e.g. introduce a new value in some empty cell) will indeed trigger the recalculation of _all_ cells, since AutoCalculate is enabled. So the only case in which some of the cells are not updated seems to be the simple recalculation. I am not 100% sure whether this is a bug, or some misinterpretation (or inaccurate over-expectation) of the (simple) recalculate action.
I can confirm that clicking in, say, E105 and pressing the delete key to simulate an edit will refresh the entire 100 row sheet, including the "red cells". I never press F9 in my usual spreadsheets because "auto recalculate" is on by default, but as this was a simulation I was hitting F9. I don't know what the "official" expected behaviour of F9 is meant to be, but *I* expected F9 to recalculate the whole sheet, and I still do. I can't see what's special about these "red cells" or how you'd know where they'd be, before they show up, and that worries me slightly.
With Menu>Tools>Options>LibreOffice>OpenCL, disabled, works fine for me. But not with it enable. I remember there were issues with OpenCL for ranges larger than 100 formulas. Theoretically solved. So, tested modifying, advanced setting: org.openoffice.Office.Calc.Formula.Calculation.OpenCLMinimumData default value is 100 just the rows of the calculations in E102:G103 (E2:E101 ...) increasing OpenCL value to e.g. 110, after restarting LO, the calculation works fine. Seems clear that the issue is in relation with OpenCL enable and being used. Looks like volatile functions, doesn't trigger the recalculation on OpenCL.
Partial recalculation failure as described in this report started in LO 6.1. In LO 6.0.4.2, simple recalculate updates all values. In LO 6.1.6.3, some values are not recalculated. In both versions, org.openoffice.Office.Calc.Formula.Calculation.OpenCLMinimumData is 100 by default. However, some recalculate issues have been resolved after LO 6.1 (e.g. tdf#150766, tdf#137248, so LO 7.0 to LO 7.5), and therefore I cannot be sure whether the current problem (25.2 alpha) is exactly the same as in LO 6.1, making the bisection procedure somewhat tricky. I am changing the Version field to 6.1, with the caveate I just mentioned. STR: 0. Calc with OpenCL enabled. 1. Open attachment 197055 [details] (AutoCalculate is enabled). 2. Focus (click once) on cell G102. 3. Press F9 key (recalculate). 3.1. Note that cell G104 recalculates (no longer equals 100), but the result is only "partially correct" - please allow me such strange expression - because other cells in the range E102:G103 (except G102) do not recalculate. Hard Recalculate does not exist yet in LO 6.1, so disabling and re-enabling AutoCalculate is needed in order to see _all_ cells recalculated. When Hard Recalculate is available, it updates _all_ cells. As mentioned before, whatever triggers this behavior (intentionally or not), it depends on OpenCL being enabled in LO (at least in current versions, in which the second older "Software Interpreter" option for OpenCL is no longer available). I do not know whether the behavior is intentional or not. Perhaps the behavior is a consequence of performance improvements, and the reason for Hard Recalculate to exist (i.e. maybe not really a bug).
(In reply to m_a_riosv from comment #12) > With Menu>Tools>Options>LibreOffice>OpenCL, disabled, works fine for me. But > not with it enable. Confirmed. > org.openoffice.Office.Calc.Formula.Calculation.OpenCLMinimumData > increasing OpenCL value to e.g. 110, after restarting LO, the calculation > works fine. Confirmed.
Bibisected with repository: bibisect-win32-6.1 blamed commit: f7eee45cc672edadc534abab3ede42245d3834bc
(In reply to Bryan Zanoli from comment #15) > Bibisected with repository: bibisect-win32-6.1 > blamed commit: f7eee45cc672edadc534abab3ede42245d3834bc Source commit hash: 591933bf668b014055813edc75d5d4764c428e11 fix broken control flow in ScTokenArray::CheckToken()