Created attachment 170357 [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: 140901, https://bugs.documentfoundation.org/show_bug.cgi?id=140901 "EDITING Crash when deleting rows that are referenced by a chart"
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. Remove the conditional formatting completely, then re-create it from scratch.
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-3 are identical to those in bug 140901. But this bug is about the remaining steps which 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 Sheet13, the conditional formatting on Columns L and M has been dropped or ignored - many of those cells have been "flipped" from black text/gray background to red text/pink background or vice-versa. Attempting to edit the conditional formatting does not correct the issue. It must be removed and re-created from scratch.
Actual behavior: on Sheet13, the conditional formatting on Columns L and M has been dropped or changed - the rules governing which cells appear as red text/pink background vs. black text/gray background seem to be applied to different data values than before. Some cells have kept their conditional formatting while others have had it reversed.
Expected behavior: retain the conditional formatting on Columns L and M as it was before.
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.
Created attachment 170358 [details]
screen shot #1 - before bug occurs
Created attachment 170359 [details]
conditional formatting before/after (same rows/cols shown)
This illustrates how the conditional formatting on Columns L and M gets flipped around after the bug occurs.
Thank you for reporting the bug.
it seems you're using an old version of LibreOffice.
Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
This bug has been in NEEDINFO status with no change for at least
6 months. Please provide the requested information as soon as
possible and mark the bug as UNCONFIRMED. Due to regular bug
tracker maintenance, if the bug is still in NEEDINFO status with
no change in 30 days the QA team will close the bug as INSUFFICIENTDATA
due to lack of needed information.
For more information about our NEEDINFO policy please read the
wiki located here:
If you have already provided the requested information, please
mark the bug as UNCONFIRMED so that the QA team knows that the
bug is ready to be confirmed.
Thank you for helping us make LibreOffice even better for everyone!
Please read this message in its entirety before proceeding.
Your bug report is being closed as INSUFFICIENTDATA due to inactivity and
a lack of information which is needed in order to accurately
reproduce and confirm the problem. We encourage you to retest
your bug against the latest release. If the issue is still
present in the latest stable release, we need the following
information (please ignore any that you've already provided):
a) Provide details of your system including your operating
system and the latest version of LibreOffice that you have
confirmed the bug to be present
b) Provide easy to reproduce steps – the simpler the better
c) Provide any test case(s) which will help us confirm the problem
d) Provide screenshots of the problem if you think it might help
e) Read all comments and provide any requested information
Once all of this is done, please set the bug back to UNCONFIRMED
and we will attempt to reproduce the issue. Please do not:
a) respond via email
b) update the version field in the bug or any of the other details
on the top section of our bug tracker