Bug 140905 - EDITING: chart loses data without warning when any referenced row is deleted from sheet
Summary: EDITING: chart loses data without warning when any referenced row is deleted ...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.6.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-03-09 07:25 UTC by txcsharppro
Modified: 2022-07-07 10:54 UTC (History)
0 users

See Also:
Crash report or crash signature: crashreport.libreoffice.org/stats/crash_details/c0af81c4-fd17-4c41-9ec6-74ffd4f0eeda
Regression By:


Attachments
sample file to reproduce the bug (448.00 KB, application/vnd.ms-excel)
2021-03-09 07:25 UTC, txcsharppro
Details
screen shot #1 (928.78 KB, image/jpeg)
2021-03-09 07:28 UTC, txcsharppro
Details
Screen shots to show before/after conditions (902.12 KB, image/jpeg)
2021-03-09 07:35 UTC, txcsharppro
Details

Note You need to log in before you can comment on or make changes to this bug.
Description txcsharppro 2021-03-09 07:25:17 UTC
Created attachment 170360 [details]
sample file to reproduce the bug

When a row or group of rows is deleted, if an existing chart contains a reference to any element in that row(s), Calc will lose the conditional formatting under certain conditions. 
RELATED: bug 140901, bug 140903

Initially marking this as Major - seems like a serious glitch.
Has a couple of workarounds, both of which are clumsy and time-consuming:
Option 1. Wait for the bug to occur, then go into each messed up chart and fix the data ranges.
Option 2. If the charts are first modified to exclude any references to any of the rows about to be deleted, then the row deletion is successful and the bug does not occur. This is the workaround I've been using, but I often forget to do this until it crashes and I ask myself, "what was that strange thing I had to do the last time it crashed". 

STEPS TO REPRODUCE:
[Note that steps 1-8 are identical to bug 140903. Also, steps 1-3 are identical to those in bug 140901. STEP 9 AND THE ACTUAL/EXPECTED BEHAVIOR NOTES ARE DIFFERENT.]
1. Open the sample XLS file 'sample file to reproduce the bug'. (Please forgive how odd it looks, I deleted a lot of text to anonymize the file.) If you look at the chart data ranges (see 'Charts' tab), you'll notice the charts reference data up through row 644 on the 'Sheet13' tab. This is also demonstrated in the 'screen shot #1' attachment.
2. Now go to the 'Sheet13' tab. Attempt to do any 'delete row(s)' operation for one or more rows which are referenced by the charts on the 'Charts' tab. Calc will quickly crash. In my testing, I deleted row 631.
3. That action corresponds to crashreport.libreoffice.org/stats/crash_details/abfe5c0d-223c-4c80-ae76-6983ae8f6258  and is already covered in bug 140901.
[THIS BUG BEGINS HERE:]
4. Allow the Recovery process to proceed, but do NOT select the option to enter Safe Mode. Let it generate the crash report, and again in that dialog, do NOT open in Safe Mode, just press the Close button. This opens LibreOffice 6.4 Document Recovery. Press Start to let it recover and reopen the file. When it is recovered, press Finish, but DO NOT save the sample file - there are more steps first.
5. When the file reopens, take a screen shot of each of the two tabs for comparison after step 4. (As expected, the deleted row(s) are still there.) You should see something similar to the first screen shot attachment.
6. Remember, DO NOT save the file, not yet. Now delete row 631 again, or any other row (or group of rows) which are referenced by the charts. Calc will again crash as it did in step 2, but again that's not what we're interested in with this bug report. However, another bug has occurred that will show itself momentarily.
7. For this step, I deleted row 631 again. That action corresponds to crashreport.libreoffice.org/stats/crash_details/c0af81c4-fd17-4c41-9ec6-74ffd4f0eeda  and is reproducible 100% of the time. THIS CRASH REPORT IS WHAT I THINK YOU'LL WANT HERE. I only put the other one just in case it's related.
8. Proceed with Recovery as before, NO safe mode, DON'T save the file yet. 
9. When the file reopens, on the 'Charts' tab, notice that the bubble chart now has some missing data. The bug seems to change the data ranges in different and unexpected ways depending on exactly which rows are attempted for deletion, which seems to indicate an algorithm is trying to reinterpret the data ranges but is doing so incorrectly. If you edit the chart to view the data ranges you'll see that they have changed from what they were in the original.

ACTUAL: Calc crashes with no warning, then after the second attempt crashes again but data in the charts is changed after the second Restore operation (but not the first). In the second restore, Calc attempts to reinterpret the data ranges, but it does so incorrectly.

EXPECTED: Either prompt the user with something that asks if they want to update the charts or cancel the row-delete operation, OR have Calc just do it automatically. But either way it should reinterpret the data ranges correctly.

DEV NOTES:
1. There are some related bugs which I am reporting separately. Those occur under similar circumstances, but since they have additional unexpected results, they are probably separate bugs.
2. This bug deals with deleting a row or group of rows which a chart holds a reference to. (Not deleting the data within a row. This is when removing the row from the spreadsheet.) For reproducing the bug, any kind of row deletion will do. You could delete a single row, delete a group of rows, or do an insert & undo that insert. As long as the row deletion is from inside a range that a chart is referencing, the bug will occur.
3. This might possibly be due to a broken reference error similar to what makes #REF! be displayed in a cell? Makes me wonder if some of the code for that exception handler for cells could be used for chart objects also. Just an idea.
4. Bug occurs whether using top menu, context menu, or keyboard shortcut, and is reproducible 100% of the time.
5. Notably, rows 645-651 are not referenced in the charts, so you can try deleting from among those rows and the bug does not occur. It only occurs when the deleted row(s) are referenced in a chart somewhere.
6. Bug is also reproducible by inserting a row (for example) between rows 630 and 631, then typing Ctrl-Z to undo the insert, then Calc will crash on the Undo operation.
7. It would be interesting to know whether deleting a row which references pure data elsewhere (such as on another tab) would cause a crash when there are NO charts involved. I did not test that scenario.

Additional notes which might be relevant:
(1) The attached sample spreadsheet is a spreadsheet which I created about 5 years ago in a previous version of LO. I've migrated it up from previous versions, but from its creation I've only ever saved it in XLS format (never in ODF). I do this so I can view it in MS-Office, but I have never edited this document in MS-Office. I'm not sure whether this file having been created on an older version of LO makes any difference, versus creating a new file with a chart, because I did not test that scenario.
(2) The type of chart doesn't seem to be related. This happens on simple charts as well as more complex ones having line + bubble + 8 different data types being tracked - you can repro the defect with any one of the charts in my sample file and deleting the others, and the bug can still be reproduced.
Comment 1 txcsharppro 2021-03-09 07:28:48 UTC
Created attachment 170361 [details]
screen shot #1

Screen shots from pristine sample file before the bug occurs.
Comment 2 txcsharppro 2021-03-09 07:35:23 UTC
Created attachment 170362 [details]
Screen shots to show before/after conditions

Shows the effect this bug has on the bubble chart.
Comment 3 Timur 2021-09-17 08:08:55 UTC
No point to go into this until bug 140901 is fixed.
Comment 4 Ferdinand 2022-07-07 07:55:04 UTC
Hi
IMHO this problem is an UX disaster especially in big spread sheets with multiple sheets.

And it should be really easy to check all cells to be deleted using the existing "detective" tools and prevent deletion without explicit (1) acknowledge of the user. 

(1) it is necessary that the user is aware that there is now way (to my knowledge) to find a missing reference after saving the sheet (except from a backup of course or use a versioning cloud storage). 
Restoring complex spreadsheets is a nightmare, especially if multiple persons work on the same sheet, which becomes more and more common.
Comment 5 Timur 2022-07-07 10:54:12 UTC
I don't see this bug and close. If you do, please explain.