Bug 140903 - EDITING: Conditional formatting is lost after deleting rows referenced in a chart
Summary: EDITING: Conditional formatting is lost after deleting rows referenced in a c...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2021-03-09 06:53 UTC by txcsharppro
Modified: 2021-10-31 04:02 UTC (History)
1 user (show)

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

sample file to reproduce the bug (448.00 KB, application/vnd.ms-excel)
2021-03-09 06:53 UTC, txcsharppro
screen shot #1 - before bug occurs (928.78 KB, image/jpeg)
2021-03-09 06:55 UTC, txcsharppro
conditional formatting before/after (same rows/cols shown) (1.00 MB, image/jpeg)
2021-03-09 07:03 UTC, txcsharppro

Note You need to log in before you can comment on or make changes to this bug.
Description txcsharppro 2021-03-09 06:53:25 UTC
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". 

[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.
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.
Comment 1 txcsharppro 2021-03-09 06:55:24 UTC
Created attachment 170358 [details]
screen shot #1 - before bug occurs
Comment 2 txcsharppro 2021-03-09 07:03:07 UTC
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.
Comment 3 Xisco Faulí 2021-04-01 08:54:22 UTC
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.
Comment 4 QA Administrators 2021-09-30 04:02:40 UTC Comment hidden (obsolete)
Comment 5 QA Administrators 2021-10-31 04:02:40 UTC
Dear txcsharppro,

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

Warm Regards,
QA Team