Bug 130698 - enhancement request: better handling of outdated cached values | notabug: file was saved with outdated values | was: sum error when opening an xlsx in Calc
Summary: enhancement request: better handling of outdated cached values | notabug: fil...
Status: RESOLVED DUPLICATE of bug 130118
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.6.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2020-02-16 07:21 UTC by Elmar
Modified: 2020-05-09 13:02 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
sum function not consistent (36.42 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-02-16 07:24 UTC, Elmar
Details
screenshot of summed column (5.22 KB, image/png)
2020-02-16 07:27 UTC, Elmar
Details
screenshot of status bar (5.82 KB, image/png)
2020-02-16 07:29 UTC, Elmar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar 2020-02-16 07:21:42 UTC
Description:
 open a spreadsheet created by an Excel user. The =sum() total is not correct.


Steps to Reproduce:
1. open file
2. I copy the data to rows > row 30, included totalling
3. notice that =sum totals are wrong sometimes
but, the sum in the status bar is correct.

Actual Results:
 checked in MS Excel, does not have same problem

Expected Results:
Totalling should be correct


Reproducible: Always


User Profile Reset: Yes



Additional Info:
[Information automatically included from LibreOffice]
Locale: en-GB
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Linux (All)
OS is 64bit: yes

Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: group

I did a clean install of Linux Mint Cinnamon 19.3 - problem is the same 

Same problem in LO v7
Comment 1 Elmar 2020-02-16 07:24:45 UTC
Created attachment 157912 [details]
sum function not consistent
Comment 2 Elmar 2020-02-16 07:27:37 UTC
Created attachment 157913 [details]
screenshot of summed column
Comment 3 Elmar 2020-02-16 07:29:25 UTC
Created attachment 157914 [details]
screenshot of status bar
Comment 4 Mike Kaganski 2020-02-16 08:05:54 UTC
It is reproducible with ersion: 6.4.1.1 (x64)
Build ID: 56f3c78975db08733f771c53643b5d1aa7c57567
CPU threads: 12; OS: Windows 10.0 Build 18363; UI render: GL; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: CL

It uses the cached value stored in the XLSX file:

>            <c r="E30" s="23" t="n">
>                <f aca="false">SUM(E5:E29)</f>
>                <v>454</v>
>            </c>

It depends on the default setting "Recalculation on File Load": "Excel 2007 and newer" - "Never recalculate" under Options->Calc->Formula. If, however, it is set to "Always recalculate", this is expectedly shown correct. Also doing a hard recalc on the file normalizes the calculated data. Actions in the sheet like copy and paste to other places that don't affect the formula cell e.g. in E30 will expectedly *not* recalculate its value, since SUM is not a volatile function.

I don't know if this should be closed NOTABUG. Eike, what do you think?
Comment 5 Elmar 2020-02-16 17:15:43 UTC
Is not intuitive. 
One thing which would highlight this is if the defaults in "recalculate on file load" were auto set to "Prompt user".
I work in environments were almost everyone is using MS Office.
Not sure why I have never seen this before.
Is it possible to do an integrity test when one opens a spreadsheet which was not created by Calc, to check whether such a recalc is necessary? 
I guess it could take a long time on low powered computers and users may abandon the open file unless one has a message like "File Integrity check" with a progress bar. and a suggestion that the option be changed if the user experiences this a lot.
Comment 6 Eike Rathke 2020-02-19 17:24:29 UTC
Note that the attached file was created by LibreOffice/6.0.7.3, not MS-Excel; under some circumstances shared grouped formulas if copied/moved were not recalculated before saving (e.g. on sheet Attendance in columns C, D, E).

I'm not sure if we always should recalculate documents saved with earlier LibreOffice versions or whether there's a better approach. My take is recalculate, maybe only if there are shared formula groups. Maybe even by setting only those ranges dirty (which broadcasts to dependents), on the other hand that might take longer than a forced recalc if there are a lot of formula groups.
Comment 7 b. 2020-05-09 13:02:25 UTC
i think in the history of the table data was added or updated while 'autocalculate' was off, the following work to identify the the error (row 31++) did not trigger a recalculation of row 30 ... 

funnily hard recalc can't heal the deviation in cols V:Y ... the formulas in row 30 start from row 6 instead of row 5, 

> checked in MS Excel, does not have same problem

probably recalculate on load active in excel? 

> integrity test when one opens a spreadsheet which was not created by Calc

won't be save, same problem can easily be constructed in calc, 

> version: checked back until 4.1.6.2, 

automatic 'recalc' always stays in the balancing act trying to avoid errors (with) or delays (without), 

thus: 

enhancement request to inform users when dealing with outdated cached values ...

*** This bug has been marked as a duplicate of bug 130118 ***