Created attachment 172937 [details] Project file simplified with only one cell using operator == This effort includes steps to reproduce and steps to diagnose Step 1. Open Libreoffice Calc on Windows. Calc 7.2.0 used here Step 2. OpenCL allowed. Calc Macros disabled here Step 3. Open the attachment. (data ranges use same colour as scatter plots) Step 4. Use mouse to Select H19 to L19. Those cells are now highlighted. Step 5. Use keyboard delete key to empty those five cell values. Step 6. Charts in red and orange update correctly to new values. Step 7. Use mouse (important) toolbar "back arrow" to "Undo:delete". Data Ranges for both charts go back to prior state (consult columns CV to CZ) Step 8. The red chart x plots go back, but it's y values do not (incorrect: stuck at state of Step 5) Step 9. Mouse click Data>Calculate>Recalculate, the chart does not update. Step 10. Mouse click Data>Calculate>Recalculate Hard, the chart does update it's y values. The sheet is now showing again the state at File>Open Now the User chooses to try a reset of User Profile. Step 11. Close the file (without saving; no changes). Step 12. Select Help>Restart in Safe Mode>Restart Step 13. factory; Reset settings and Reset entire user profile both checked. Step 14. Apply changes and restart. Step 15. Open attachment; Step 4 to Step 7; delete H19:L19 then "Undo:delete". Step 16. At "Undo:delete" there are new problems with Range columns and Charts. The red and orange Data Ranges x values do go back (see columns CV, CY). The red y calculations CG are wrong yet rows 3:8,51,72,93,114,135 are correct. The red Chart plots wrong y values like step 8; without the few good rows The orange y calculations CC are wrong yet orange chart plot does follow/update The data ranges calculations precedents are wrong Step 17. Click cell AO241 shows -40; should be =AM238 which has value 103.7028 Step 18. Click cell AO242 it shows -90.9107 which is =AL238 Step 19. Data>Calculate>Recalculate. Most cells of column AO173:AO331 update but not rows 222 to 241 The red Data Range CG is wrong yet it's Chart did follow on this occasion Step 20. Data>Calculate>Recalculate Hard. The sheet is now showing again the state at File>Open Now the User tries copying the values and pasting back in (avoid Undo:delete) Step 21. Close the file without save. Restart Calc. Open the file. Step 22. Select H19:L19. Mouse right-click Copy (not Cut) Step 23. Paste the five copied values anywhere convenient. Step 24. Select H19:L19 and use keyboard to delete (empty). The charts update. Step 25. Click on H19 (which is empty). Mouse right-click Paste. All Data Ranges and Charts are updated and now same as the first File>Open Actual result. Use of undo:delete caused data range and chart failure to update Expected result. undo:delete shows the same as pasting back in deleted values Now Investigating the true cause or provocation or limitation Step 26. Use mouse to Select H19 to K19 (do not select L19). Step 27. Use keyboard delete to empty those four cell values. Step 28. Use mouse toolbar to "Undo:delete". The data ranges are not restored to the original values. However, the Charts are active and do follow and update to the wrong data range values. Unlike step 16, now the red chart does trigger and plot negative y values for all rows of it's data range. Step 29. Data>Calculate>Recalculate Hard All Data Ranges and Charts are updated and now same as the first File>Open Step 30. Close the file (without saving; no changes). Step 31. Close Libreoffice Calc. Start Calc again and open original file. Step 32. Click blue cell L23 with ==IF((),,) then edit to =IF then Enter Step 33. Repeat Steps 4 to 8. The red data range is correct, the red chart is in error, the same problem as before. Step 34. Close the file without save then re-open the same original file again. Step 35. Click on the blue cell L23 which has operator ==IF((),,) then edit to =IF then Enter then File>Save As>anyname. Then close the file anyname.ods. Step 36. Now Open the new file anyname.ods Here I find that the initial presentation of "anyname" can be faulty and sometimes correct. It should open the same as Step 3 (red line is in a sawtooth pattern) but can show the state of step 19. It seems to depend on recent activity and the changes to User Profile. Hard Recalculate if necessary. Save file and reopen again if necessary. Step 37. Repeat Steps 4 to 7 (deleting and undo:delete cells H19:L19) All Data Ranges and both Charts update successfully for all steps (the expected result). User deduces that eliminating == cell is useful but must save file to repair it. If == operator is required, User needs a different workaround. Test with OpenCL Off Step 38. Close the new file anyname.ods Step 39. Tools>Options>LibreOffice>OpenCL>uncheck the Allow box>Apply>Restart Step 40. OpenCL is off. Open the original file (which includes operator ==) Step 41. Repeat Steps 4 to 7 (delete and undo:delete cells H19:L19) All Data Ranges and both Charts update successfully for all steps (the expected result) Actual result. Where == is present on a sheet, OpenCL and undo:delete cause errors in data ranges and some chart failures to update Expected result. undo:delete shows the same as pasting back in deleted values Test with LibreofficeDev 7.3.0.0 alpha0 of 16 june Step 42. Help>Safe Mode>User profile factory settings>Apply>Restart Step 43. Select Options, check OpenCL back to "allowed", restart if necessary Step 44. Test from step 3. At step 7 "undo:delete", Data Ranges for both orange and red do not go back to original values. The orange chart follows the range change, the red plot has y errors. Step 45. Recalculate Hard is effective and necessary. Ranges and charts then update correctly. Observations Is == the only operator that can cause this or similar? Not yet tested others. It's noticeable that the == and/or it's precedents are involved in the issue or help to reveal it. Testing by deliberately inserting a cell ==A/B into a new clean working file causes the Bug to appear. Is Undo:delete the only menu item causing this? Not yet tested any others. Does OpenCL on or off with Ubuntu behave differently? Don't know; test is done with a Dev build 7.2.0 identical version on dual-boot PC but OpenCL remains disallowed on Linux here. Multithread on/off? Not yet tested. Default is "on" here. What happens if the User doesn't notice or has no graphs to check (the file is saved in a faulty state before re-opening)? When saved at step 8, that new file opens with data ranges and data/charts refreshed and correct. When saved at step 16 (new user profile involved), that new file shows wrong values in the data ranges precedents and again wrong at file open. Testing a saved step 16 file then with == removed and with OpenCL off, it still always opens with the formula AO241=AM238 calculated as -40 = 109.702. I'm not an advanced User, but reckon it's become fixed in the content.xml. The file can be recovered (if needed) by opening and doing Data>Calculate>Recalculate Hard first. Reset of User Profile also caused very different results when opening the original (unmodified) attachment. Reset User profile introduced sporadic fails of autocalculate which weren't observed before with this file. Don't know why yet, but Windows Security Audit and Event Viewer shows many visits into the User Profile by soffice during the steps. Work in progress (using Diffmerge) to inspect the details. User Profile at the beginning is very recent on this new Windows 10 Pro PC (I just work with defaults). However, once Calc is restarted (Profile factory or not) the issue is always reproduceable.
Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 1675a68526c43c6c6e4dc850ee911f0c1de75c88 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL Version: 7.1.3.2 (x64) / LibreOffice Community Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1 CPU threads: 16; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: nl-NL (nl_NL); UI: nl-NL Calc: CL Version: 7.1.3.2 (x64) / LibreOffice Community Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Vulkan; VCL: win Locale: da-DK (da_DK); UI: da-DK Calc: CL Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community of 16 june 2021 Build ID: b89ebf135818ccaa45bbfb164099a6e199bd7d11 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL
The attachment is a simplification of the file used to progress Bug 86321. To remove the traces and notification of a macro, the menu method did not eliminate. The flatods method did not succeed either. An event-listener line was found (manually) in the xml; once removed that, then the macro warnings stopped. In any case the step by step was learnt before removing the warnings (macro disabled). The file was/is full of ROUND and formatting about that with mixed display of values after formulae. Noted (may be subject to Bug 140722 as well as this one) The cell in the original file with operator == has no dependent, appears to be a keyboard key bounce when generating the file (most probably a typing error) and that the forced calculation is unintentional. The test file of Bug 86321 shows the same symptoms as here step by step. It has many sheets and charts with several million cells and over one hundred named expressions. The only cell with something unusual == was found by eye, looking at every single cell (took about ten days to find). The sample here is stripped down to only two lines and all precedents on one sheet. I'd prefer to do a clean sheet report soon (when I can), however I also keep an eye on backwards compatibility issues that might severely affect User projects. I understand the Bug report has a lot of steps; but it's all there, chart updates, OpenCL, == trigger/block, autocalculate, sporadic fail and it's reproduceable (which I hope helps!). My User Profiles available on request for any step or all steps. I've installed Virtual Studio and can offer debug also (please specify which steps to record).
problem from step 8 no repro with 7.2 dev from ~2021-06-05 under debian linux (kali), red plot updates to 'as before deletion' ...
(In reply to b. from comment #3) > problem from step 8 no repro with 7.2 dev from ~2021-06-05 under debian > linux (kali), red plot updates to 'as before deletion' ... Thanks b. I test Linux Ubuntu here, but Calc checks my graphics and fixes OpenCL off (Tools Option disallowed). All reproducing seems to depend on OpenCL allowed (often but not always Windows so far). However, for other chart related problems Ubuntu does behave differently. Have you OpenCL option?
hi @matthenote, thanks for being precise, you catched me being imprecise, did enable openCL, did restart, but forgot to check if enabling worked .. it didn't, looks somehow blocked on my system (Lenovo P70, Xeon, Nvidia M4000M) ... ??? sorry ...
(In reply to b. from comment #5) > hi @matthenote, > check if enabling worked .. it didn't, looks > somehow blocked on my system (Lenovo P70, Xeon, Nvidia M4000M) ... ??? sorry hi b. That automatic unsetting OpenCL got me too (four times). Thankyou for your fast support to reproduce. I should have mentioned above that the versions in Danish and Dutch were done by Lars and VLB and do reproduce on different machines. For users here searching "similar bugs", starting calc on Linux may blink a few times before showing a window. If the whatever-it-is doesn't match OpenCL requirements, it's silently switched "disallowed" behind the scenes and continues without. I don't need to know as a user, but do when reporting a bug. Short intro about that here https://conference.libreoffice.org/assets/Conference/Brno/libocon-2016-opencl.pdf
tested with win: Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 18e5e948dd66e41f17b0a63bf631d98aee84a03b CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: reproducible crash when trying to open that file, Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: d7f734db2c078ced3ce08ad58cd816a79abe3bcf CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: CL repro!, independent of '==' or changed to '=' while file open in L23, independent of 'mouse undo' or 'ctrl-Z', independent of deleting and undo of H19:L19 or only L19, after delete and undo as in 'steps': chart messed up, after hard recalc (ctrl-shift-F9): chart corrected. Saving the file with L23 changed to '=' stops the problem, reproducible re-occurence after once changing L23 to '==', and then kept even if re-changed to '='. a 'state machine', dependent on states in the past actual actions on identical data produce different results. if you look for something to trick / trap users or devs such is among the top choices. no repro with openCL off. @matthewnote: again high respect that you invest the effort for such deep digging!
Could you give a try to 7.2.2 and, if you still reproduce the pb, could you attach <user profile>/cache/opencl_devices.log and opencl_profile.xml (see https://wiki.documentfoundation.org/QA/FirstSteps#Computation-related_issues_in_Calc_.28OpenCL.29) ? Perhaps a backtrace may be useful too (see https://wiki.documentfoundation.org/QA/BugReport/Debug_Information#Windows:_How_to_get_a_backtrace)?
(In reply to Julien Nabet from comment #8) > Could you give a try to 7.2.2 Testing with 7.2.2.2 (msi install upgraded the 7.1). The symptoms change. At first there are still cell recalculation errors after Undo:delete (but different errors). However. . . If the test file is saved once (using 7.2.2.2) then close/restart 7.2.2.2 once, all cell values now work at all steps. I only tested Undo:delete; it's a complex file with a long history. However, it seems this cell Calculation Undo problem (OpenCL on) resolved. Great! (if the User knows to upgrade Calc/open file/save file/restart/open file again). Remaining problem. The red plot chart doesn't follow (but the orange chart does). After Undo:delete, Red shows points including many Y values at -27. The x,y scatter seen is identical to the miscalculation with Calc 7.1. Those "old" wrong values are nowhere on the sheet cells or I can't see them. But they are presented on the chart. Strange; everything's calculated twice? Once for Sheet display and again for Graphics Engine OLE? Testing "Recalculate Hard" causes the red chart to refresh and becomes correct. Diagnosis about why. I tested all available chart options; change scales, colours, fonts, titles, Delete the series, Add the series, plot the series on a secondary Yaxis . . . After Undo:delete, the red chart updates to it's good series only by... Double-click the chart then Right-click the Red plots. Then choose Format Data Series. Then Deselect (uncheck) "Include values from hidden cells" (set to off). Then OK. The red plot jumps to the correct Data Range values. If the file is saved with "Include hidden" on or off the Chart refresh is still incorrect at next Undo:delete. Also, it doesn't matter if the setting is on or off. It needs to be toggled. Toggle off to on or on to off both work. I have no experience about hiding cell values (it were preferable if someone familiar with "hide" could check over this single sheet file). However, the problem is cleared by setting OpenCL off. @Julien Do you prefer a separate new Bug Report?
Created attachment 175899 [details] opencl log after upgrade from Calc 7.1 to 7.2.2.2 Installing 7.2.2.2 replaced 7.1 and inherited Recent Files etc. The Step by Step testing produced similar and still "wrong" results in the cell values Data Ranges used for plotting the red chart.
Created attachment 175900 [details] opencl profile after upgrade from Calc 7.1 to 7.2.2.2
Created attachment 175901 [details] opencl devices log after test file save and restart Calc 7.2.2.2 The Undo:delete problem was resolved (for cell values) after saving the test file and restarting Calc 7.2.2.2 then re-open the test file. However, the chart red plot reverted each time (for y values) to old Calc 7.1 error information (even though it's data series columns are now good).
Created attachment 175902 [details] opencl profile after restarting Calc 7.2.2.2 After file save/ restart 7.2.2.2 / reopen file, with this profile, the cell calculations all work (red chart still a problem). Version: 7.2.2.2 (x64) / LibreOffice Community Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded
excuse me; correction to my comment 13; Profile attachment is valid for OpenCL on, == present in file, Delete precedents, Undo:delete, data series columns good, chart in error. The version info' was, at that test Calc:CL Version: 7.2.2.2 (x64) / LibreOffice Community Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL
Thank you for your feedback. Yes I think it'll be easier if you submit a new bugtracker. Let's put this one to WFM.
(In reply to Julien Nabet from comment #15) > Thank you for your feedback. > > Yes I think it'll be easier if you submit a new bugtracker. > Let's put this one to WFM. You're welcome. Thank you for the alert. For readers searching "similar bugs", to summarise... A chart may have data series that calculate correctly (bug resolved), yet the plots are different. Recalculate Hard may be useful or try OpenCL off. I'm investigating why clicking the chart, then Format Data Series, then check (or uncheck) "Include values from hidden cells" causes the chart plot and line to update correctly. New Bug Report follows. Please contribute if you have more clues.
Further testing reveals more calculation error (not about the chart update). A new symptom of this (closed as WFM) Bug is found with 7.2.2.2, This is tested again using Calc 7.3.0.1 (Release Candidate testing asked for). With Calc 7.2.2.2 (Open CL enabled) Step 1. Open the attachment 172937 [details]. Save once with 7.2.2.2, restart Calc. Step 2. Reopen the saved file. Click on E19 only (has very many dependents). Step 3. Use keyboard to delete content. Step 4. Use mouse click toolbar Undo:delete. Step 5. Consult columns CV,CW,CY,CZ to remark calculation errors (list in CW). Step 6. Remarkable is that the red chart is correct although it's range not. Step 7. Repeat the steps with OpenCL disabled. All calculations correct. Install Calc 7.3.0.1 (Release Candidate) Here the first start of Calc (after install using "Separate Install GUI" tool was with OpenCL automatically disabled. Then set to enabled manually). The steps 1-7 reproduce the same Bug (for different reasons perhaps). Version: 7.3.0.1 (x64) / LibreOffice Community Build ID: 840fe2f57ae5ad80d62bfa6e25550cb10ddabd1d CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL