[Libreoffice-bugs] [Bug 142891] New: FILEOPEN, EDITING, Chart or Range fails to update using Undo:delete for OpenCL when a cell with operator == is present

Wed Jun 16 09:19:02 UTC 2021

https://bugs.documentfoundation.org/show_bug.cgi?id=142891

            Bug ID: 142891
           Summary: FILEOPEN, EDITING, Chart or Range fails to update
                    using Undo:delete for OpenCL when a cell with operator
                    == is present
           Product: LibreOffice
           Version: unspecified
          Hardware: x86-64 (AMD64)
                OS: Windows (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: matthewnote at yahoo.co.uk

Created attachment 172937
  --> https://bugs.documentfoundation.org/attachment.cgi?id=172937&action=edit
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210616/0a768c71/attachment-0001.htm>