Bug 130118 - enhancement request: better handling of outdated cached values | notabug: file was saved with outdated values | was: error in calculating sums
Summary: enhancement request: better handling of outdated cached values | notabug: fil...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.6.2 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 130698 (view as bug list)
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2020-01-22 09:33 UTC by ldn
Modified: 2020-05-09 13:04 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
file Calc which includes the bug to be seen (20.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-01-22 09:36 UTC, ldn
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ldn 2020-01-22 09:33:38 UTC
Description:
I get different results for the same calculation made in different cells of the spreadsheet

Steps to Reproduce:
1.not possible to reproduce easily. I have to attache the file. Please compare cells I8 and N8 which perform the same calculation getting different results!

Actual Results:
must be seen on the file. The error is difficult to reproduce

Expected Results:
must be seen on the file. The error is difficult to reproduce


Reproducible: Sometimes


User Profile Reset: No



Additional Info:
Obviously the calculations must yield the same result.

please let me know how I can send you the file which has the error I mentioned so you can see it
Comment 1 ldn 2020-01-22 09:36:51 UTC
Created attachment 157313 [details]
file Calc which includes the bug to be seen

please look at cells I8 and N8 inm the spreadsheet
Comment 2 m_a_riosv 2020-01-22 14:00:34 UTC
Please test if after doing a hard recalc, it's possible reproduce the issue.
[Ctrl+Shift+F9] Menu/Data/Calculate/Recalculate hard.
Comment 3 w.ostrowski78 2020-01-23 13:21:54 UTC
I confirm, the bug appears on LO Version: 6.3.4.2.0+, Build ID: 6.3.4-2. on 64-bit arch Linux.
I briefly checked it, formulas contain chained calculations, and seems something is wrong during load the file.
Re-calculate, gives correct result. Even change data, and then undo the change, gives the right result. After test I saved file and all is fine.
In summary, to have correct result force LO to recalculate formula (by change cell involved in calculation chain or press F9), then save file and bug 'disappears'. Bug also affects cells: I13, I14 in file 'Tabella retribuzione.ods'
Comment 4 ldn 2020-01-24 08:03:59 UTC
Hi,
thanks, I understand, however this is not a solution. Unfortunately, if you do not remove this bug here is no way one can trust a calculation and is forced to hard-recalculate all the time. Is this not an important issue? The spreadsheet is now unreliable.

Regards
Luigi De Nicolò
Comment 5 m_a_riosv 2020-01-24 09:12:26 UTC
Do you need to do a hard recalc every time or only once for this particular file?
Comment 6 ldn 2020-01-24 10:35:35 UTC
(In reply to m.a.riosv from comment #5)
> Do you need to do a hard recalc every time or only once for this particular
> file?

Sorry I do not understand: how should I know?  My point is that Calc with this bug in it is not reliable any more and I cannot use it.
Comment 7 QA Administrators 2020-01-25 03:32:42 UTC Comment hidden (obsolete)
Comment 8 Alex Thurgood 2020-01-28 18:13:26 UTC
This debate comes up again and again.

I notice that hard recalculate doesn't set the flag "changed", so the user can not know that a save is necessary. 

Also, a hard recalculate is a particularly difficult keyboard maneuver to pull off on a Mac keyboard (just saying) involving cramping three fingers of one hand around the Shift-Fn-Cmd keys and then pressing F9 (but that is surely another issue)

I don't understand why cell N8 isn't calculated correctly on opening the file. 

I'm sure this has come up before in some other bug report...

@Eike: thoughts ?
Comment 9 Alex Thurgood 2020-01-28 18:14:27 UTC
FWIW tested on 

Version: 7.0.0.0.alpha0+
Build ID: 9a2fc64b49b4a3067ba78648e3fcc73390717c80
CPU threads: 4; OS: Mac OS X 10.15.2; UI render: default; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded
Comment 10 Eike Rathke 2020-01-28 19:19:22 UTC
(In reply to Alex Thurgood from comment #8)
> This debate comes up again and again.
Yes.

> I notice that hard recalculate doesn't set the flag "changed", so the user
> can not know that a save is necessary. 
That might had been even on purpose because there may be various reasons why (portions) are recalculated and not all are wanted to trigger a "save this" state. We probably should do that at least after a manual hard recalc.

> I don't understand why cell N8 isn't calculated correctly on opening the
> file. 
Because files are not generally recalculated upon open. You normally don't want that, specifically not with more complicated documents. The point here in this case is that once in history the file was saved with wrong results (probably due to some other bug that miscalculated a grouped shared formula, the creator is old LibreOffice/5.3.3.2), and the file is opened preserving those results, hence has to be recalculated once and saved.

> I'm sure this has come up before in some other bug report...
Probably. I don't recall a bug number.
Comment 11 b. 2020-05-09 11:28:15 UTC
> c#3: Bug also affects cells: I13, I14 in file 'Tabella retribuzione.ods'
also affected: D8, D13, D14 and indirect I9:I12 and second level indirect some more, (a small carelessness not to inform the user about outdated values leads to an avalanche of hard to spot subsequent errors)

> c#8: I don't understand why cell N8 isn't calculated correctly on opening the file.
N8 is ok, I8 is 'off', 

> affected versions: 
checked back to 4.1.6.2, identical behaviour, , 

quite sure C8, C13 and C14 have been changed with autocalculate off, C15 and cols J:N added or individually recalculated lateron, thus 'notabug', changing headline, 

leaving as enhancement request to: 

- flag 'save this' active after changes by manual hard recalc, 

(@Eike in c#10: 'We probably should do that at least after a manual hard recalc.') 

- find a way to warn user when dealing with outdated cached values
Comment 12 b. 2020-05-09 13:02:25 UTC
*** Bug 130698 has been marked as a duplicate of this bug. ***