Bug 131932 - Using OFFSET function makes Calc open file with changes to save although not edited
Summary: Using OFFSET function makes Calc open file with changes to save although not ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.5.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function ModifiedStatus
  Show dependency treegraph
 
Reported: 2020-04-06 13:46 UTC by tom
Modified: 2020-10-11 12:31 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Bare minimum of the spreadsheet that reproduces the error (16.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-04-06 15:14 UTC, tom
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tom 2020-04-06 13:46:11 UTC
Description:
In a spreadsheet with 5 sheets, 1 small and 4 of 1000 lines x ca. 50 columns each, when I use the OFFSET command in a cell, save and reopen the file, it starts with Calc having recognized changes that are to be saved.

I delted the entire row with OFFSET, saved and reopened the file: Everything ok, Calc does not quote me to save the file, no changes detected.

I re-entered the OFFSET command into a single cell, saved, reopened and: The save button again says that there have been made changes that need to be saved.

- I tried to reproduce it with a fresh sheet where the only thing I entered, was the OFFSET command, saved, reopened: No error.
- I delted all sheets and other lines in my big sheet, leaving only the row with the OFFSET command, saved, reopened: No error

Steps to Reproduce:
1. Open file
2.
3.

Actual Results:
File opens with changes to save although no changes have been made yet.

Expected Results:
File to open without changes to be recognized


Reproducible: Always


User Profile Reset: No



Additional Info:
Just open and not have the save disk icon saying that changes need to be saved.
Comment 1 tom 2020-04-06 15:14:38 UTC
Created attachment 159363 [details]
Bare minimum of the spreadsheet that reproduces the error

This is the spreadsheet stripped down to the bare minimum that reproduces the error:

Sheet "1": Cell A1 with the OFFSET formula
Sheet "2": A chart. It now references to A1, since all other cells have been deleted. In the original file it references to other cells.

If needed I can provide previous steps of stripping down the spreadsheet, I saved over 50 steps.
Comment 2 raal 2020-04-06 16:36:45 UTC
(In reply to tom from comment #1)
> Created attachment 159363 [details]
> Bare minimum of the spreadsheet that reproduces the error
> 
> This is the spreadsheet stripped down to the bare minimum that reproduces
> the error:
> 
> Sheet "1": Cell A1 with the OFFSET formula
> Sheet "2": A chart. It now references to A1, since all other cells have been
> deleted. In the original file it references to other cells.
> 
> If needed I can provide previous steps of stripping down the spreadsheet, I
> saved over 50 steps.

I can reproduce with this file. When I delete chart, then the file is not flagged as changed after opening.
Comment 3 tom 2020-04-06 17:37:46 UTC
Yes, anything I left over in the file is needed to reproduce the error. If either the cell with the formula or the chart are removed, the error disappears.

Originally there were many more cells involved which referred to each other, and removing these just like that, made the error disappear, too. So I removed these cells one after the other by un-linking them with each other, until the point as it is in the end, only having the OFFSET cell and the chart. I could not reduce the spreadsheet further without losing the effect of the error.
Comment 4 m.a.riosv 2020-04-06 22:04:53 UTC
Maybe because OFFSET() It's a volatile function like NOW() or INDIRECT(), so it is always recalculated with any change in the spreadsheet.
Comment 5 tom 2020-04-06 23:51:19 UTC
(In reply to m.a.riosv from comment #4)
> Maybe because OFFSET() It's a volatile function like NOW() or INDIRECT(), so
> it is always recalculated with any change in the spreadsheet.

I could not reproduce the same effect when just using OFFSET in a different file, or in the same file when deleting the chart on the other sheet, which seems to be part of the problem.
Comment 6 m.a.riosv 2020-04-07 10:09:09 UTC
Seems recreate the chart triggers OFFSET(), in many situations OFFSET() can be replaced with INDEX(), which it's not a volatile function.

I'm not sure it is really a bug, or a situation to deal when using volatile functions on a chart's data range.
Comment 7 Xisco Faulí 2020-05-11 14:14:00 UTC
(In reply to m.a.riosv from comment #6)
> Seems recreate the chart triggers OFFSET(), in many situations OFFSET() can
> be replaced with INDEX(), which it's not a volatile function.
> 
> I'm not sure it is really a bug, or a situation to deal when using volatile
> functions on a chart's data range.

@Eike, I thought you might be interested in this issue
Comment 8 Eike Rathke 2020-05-12 13:09:32 UTC
OFFSET() is a volatile function recalculated (like RAND() and others) because no dependencies can be established between the calculated referenced cell(range) and the formula cell containing the OFFSET() expression. A chart referencing such expression directly or indirectly leads to recalculation because the chart asks for the current data of its range and hence the document is marked as changed.

This *might* be solvable by assigning another volatile classification to OFFSET() (and INDIRECT()) so that they are not recalculated just after load upon first dereference, but may also need some document state assuring that so far no user or macro action whatsoever has occurred.
Comment 9 m.a.riosv 2020-05-12 14:07:42 UTC
(In reply to Eike Rathke from comment #8)
> OFFSET() is a volatile function recalculated (like RAND() and others)
> .....
> so far no user or macro action whatsoever has occurred.

+100 if they lose their volatile status.
I try to avoid them because of this issue. And I don't recall any situation where I needed them to be volatile. E.g. INDIRECT() has a very valuable used to create links to other files, without modify formulas or editing links, but normally it does not need to be recalculated every time there is some kind of modification on the sheet, only when its precedents are modified. Or in many cases people use them massively, without knowing the problem of volatility. Maybe there are special cases where they need to be volatile, but I think it can be forced with simple tricks.
Comment 10 Eike Rathke 2020-05-12 15:41:55 UTC
No simple trick there. Apart from maybe external references that are cached until updated (for which the dependency then would have to be individually remembered), as soon as and each time the reference string argument of INDIRECT() is changed or recalculated the old listening would had to be discarded and a new one based on the new argument be established. Similar for OFFSET(). For the initial load that would even be counter productive because the arguments would have to be calculated to determine whether or if and how the function would have to be recalculated later. Additionally, in array context it gets more complicated.