Bug 125052 - Data error risk from not calculating on large spreadsheets
Summary: Data error risk from not calculating on large spreadsheets
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calculate
  Show dependency treegraph
Reported: 2019-04-30 17:35 UTC by davidpbrown
Modified: 2021-06-11 03:49 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

see full description in comments noted just previously. (272.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-10-23 20:50 UTC, Vincent Cackowski
screenshot of a sporadic dropout of autocalculate (112.30 KB, image/jpeg)
2020-10-25 09:31 UTC, b.

Note You need to log in before you can comment on or make changes to this bug.
Description davidpbrown 2019-04-30 17:35:23 UTC
Sheet not calculating, gives misleading results - because that inaction is not declared.

At some point not obvious it seems a large spreadsheet becomes too large, that Calc stops being dynamic and does not update at every opportunity.

Ctrl+Shift+F9 works to force recalculation
which is great - IF you know to do that AND IF you know that it's necessary - because you spot cells are not updated correctly.

I saw this from odd vlookup results and fortunately figured out from a quick search but it is to be expected many users will not see the change and even those aware of this, will remain uncertain when and if, this feature has actioned.. and it's a feature because it's a lot faster on a large spreadsheet to not calculate complexity. (It would be useful to know how to force this switch off before it is just too large.)

Expected behaviour: some fundamental change is declared explicitly and/or some indicator on the bottom toolbar perhaps notes that the sheet requires forced update.

It's made worse by the opening of that file not apparently triggering recalculation.. there is no suggestion that is required.

Risk of data errors is obvious, from sheets where this has occurred.
Comment 1 m_a_riosv 2019-04-30 18:00:56 UTC
The 6.0 it's a bit outdated version. Some issues with cells no recalculated under some circumstances, have been resolved, so please if you can share a file showing the problem to test, or  can yourself test with a recent version like 6.2.3?
Comment 2 davidpbrown 2019-04-30 19:26:13 UTC
Upgrade had no effect..

same symptoms:
+ vlookups and other formulas are not actioning and copying an instance from adjacent cells copies the appearance of the same result of the old cell, without reference to the new cell; without knowing that result is wrong it's not obvious.
+ adding new data, doesn't see that trigger update of calculations.. simple calculations too.

I haven't been able to reproduce on a small file but expect it's a symptom of some limit in size.. file is 1.1MB

Above tempts "Version: (earliest affected)", rather than noting the latest
Build ID: aecc05fe267cc68dde00352a451aa867b3b546ac
CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-US
Calc: threaded

Perhaps any file full of vlookup calculations to similar size will exhibit the same?? If I can find an example for sharing, will do.
Comment 3 davidpbrown 2019-04-30 19:51:13 UTC
Not simple to reproduce unfortunately.. it's not just about file size.

I created a larger than intended 21.9MB instance that was vlookups from another sheet and that with simple calculations done but it continued to try to calculate change.. and successfully, though rather greedy with the resource available.
Comment 4 QA Administrators 2019-05-08 21:51:02 UTC Comment hidden (obsolete)
Comment 5 davidpbrown 2019-06-07 12:57:22 UTC
Too many records? - "1048575 of 1048575 records found"

One tab in this spreadsheet suffering this, suggests "1048575 of 1048575 records found" in the bottom bar detail. That count then is well off target, from the couple of 1000 records that should ever suggest.

I'm not sure what it counts for records; I've tried deleting all rows below real content but can't seem to affect and refresh that count.

I wonder then if it is acting to recalc all rows it considers are "found" records, then it's potentially spawning a lot more work and giving up on it or timing out or other.. at least for the immediate updates; the forced recalc works well with Ctrl+Shift+F9 but perhaps is slower that needs to be, taking ~10secs.
Comment 6 Buovjaga 2019-08-25 15:56:39 UTC
Please attach an example document.
Change back to UNCONFIRMED after you have provided the document.
Comment 7 b. 2019-12-17 16:22:28 UTC

you are in good company, there are! issues with unfixed calculation errors, 

(look around here for bugs with 'calculate' / 'autocalculate', you'll find some similar to yours) 

evolving from 'shared formulas', 'blocked autocalculate', questionable 'iterative calculations', calculating 'threaded' on multicore systems and using 'CL' - openCL (math processors of graphic card), 

i estimate you are affected at least by the first and second of them, 

use ver., switch off 'threaded' and 'CL', use ctrl-shift-F9 sometimes, use it each time before you trust your results and you'll be nearly 'safe', 

observe when changes occur with ctrl-shift-F9, if you have a backup from short time before check if the error is reproducible, and if: check if you can provide the document - of course you need to strip personal data, and often that makes the bug disappearing :-(

keep cool ...
Comment 8 QA Administrators 2020-06-15 03:40:31 UTC Comment hidden (obsolete)
Comment 9 b. 2020-06-15 06:11:03 UTC
it's a minefiled ... 

@davidpbrown is commenting about miscalculations ... 

resulting in wrong values displayed ... 

not pinpointing, no sample, may be diferent issues, thus somewhat unclear, but i think he's right, 

from my experience: 

- size of sheet likely is not causing issues, but 'helps' bugs and errors evolving unnoticed since harder to spot, 

- large sheets become slow, one can switch off autocalculate [menu - data - calculate - autocalculate] - be careful you have no sign that it's off, i haven't seen calc switching this off by itself, 

- there have been plenty and are still at least 3 ways to produce cells 'decoupled' from autocalculate, see bugs tdf#128975, tdf#132451, tdf#132986, such things spreaded into a sheet is ... evil and hard to spot, see tdf#124577, 

- as @david mentioned ver. between 6.0 and 6.2: tdf#124577 / tdf#132451 stepped in between and 6.2.0 

- i recently had a quite strange experience with vlookup, it suddenly pulled values from wrong rows in many similar tables (one row lower than it should have been), i'm not finished assigning it, i have / had 
1 - older LO versions installed in parallel for research, 
2 - M$S office 2010 installed in parallel for comparison, 
3 - added several - ~20 - additional 'if-nested' vlookup functions in addition to the ~60 already existing in the sheet, 
i'm still trying to see if i can narrow it down, the error affected some but not all calculations / sheets, it was persistent, but disappeared when i tried to 'reduce' the sheet to provide a sample, 

- 'suggests "1048575 of 1048575 records found"' - vague remembering having seen similar, @david: what happens if you delete the rows below instead of their content? (don't worry, calc will give you new ones), 

thus ... it's likely that @david is right with his observations, but doesn't help identifying the issue(s), 

how to proceed?
Comment 10 Telesto 2020-06-15 07:23:23 UTC
(In reply to b. from comment #9)
> how to proceed?

As always .. a sample file which at minimum produced the effect(once). Ideally with steps to reproduce.. A reduced file is not really necessary.. or walk through the bug tracker in hunt for a sample file

it's likely that @david is right with his observations
-> there probably is an issue; there often is.. it's only hard to proof.. especially with files which can't be shared in public or the mysterious steps to produce can't be found
Comment 11 davidpbrown 2020-06-15 14:35:47 UTC
> - i recently had a quite strange experience with vlookup, it suddenly pulled
> values from wrong rows in many similar tables (one row lower than it should
> have been), i'm not finished assigning it

Just to note there's a nasty bug that follows from inserting/deleting rows that seems not to follow through to formulas pulling on those.. huge pina and has corrupted a complex workbook that I'm going to have to go through manually.

Point being that might be different to this bug.

Again unfortunately not enough to post specifics on but one to watch out for. I haven't time to test atm but might follow from formulas on one sheet and change to rows existing on another that sees them walk off target.
Comment 12 QA Administrators 2020-06-16 03:42:48 UTC Comment hidden (obsolete)
Comment 13 Buovjaga 2020-06-16 07:02:35 UTC
Needinfo while we wait for the file.
Comment 14 Vincent Cackowski 2020-10-23 20:40:40 UTC
Recently upgraded to and this problem is persisting from previous version.  Misleading results due to not recalculating.  Only started encountering this problem fairly recently.  Even forcing recalc doesn't always get good results.  Am attaching sheet.  Look at column G in Activity sheet.  Change values in col E and note problem in col G.  Problem doesn't show up upon opening a clean file, only after manipulating data in various sheets.
Instructions on how to manifest problem in this sheet:
This spreadsheet is used to track carbs and glucose for a diabetic.
Note that yellow background cells (and yellow brown) are input cells.
Go to Sheet "Data"
Enter 2 or 3 values in col B, noting proper range values from col D.
Go to sheet "Activity"
Cells E51-E58 an E63 contain data based on the above input.
copy those values directly to corresponding cells in col F.
Note that E59 did not recalculate.  You can double click that cell and force it to recalculate.
Go to sheet "Activity"
Enter a value in E14 ( a fraction of an hour)
Note that Col G updates correctly but the rest of col G should also update and it does not do so correctly.
Comment 15 Vincent Cackowski 2020-10-23 20:50:14 UTC
Created attachment 166666 [details]
see full description in comments noted just previously.

this problem should now be listed as confirmed.
Comment 16 Buovjaga 2020-10-24 04:56:16 UTC
(In reply to Vincent Cackowski from comment #14)
> Recently upgraded to and this problem is persisting from previous
> version.  Misleading results due to not recalculating.

If it's fairly recently, could it be bug 137248, which is now fixed? 7.0.3 will be released early in part due to the fix. You can test with a daily master build, which installs in parallel https://dev-builds.libreoffice.org/daily/master/current.html
Comment 17 Vincent Cackowski 2020-10-24 19:08:41 UTC
Bug appears to be fixed in 7.1.  Thank you.
Comment 18 Buovjaga 2020-10-25 06:12:36 UTC

*** This bug has been marked as a duplicate of bug 137248 ***
Comment 19 b. 2020-10-25 09:31:33 UTC
Created attachment 166689 [details]
screenshot of a sporadic dropout of autocalculate

besides mostly suffering from a missing dot ('.') in '=IF($Data.A1="LS";$datax10;$Data.L2)' in Activity.E59 - probably intended as 
'=IF($Data.A1="LS";$Data.X10;$Data.L2)' - 
the file from @Vincent likely had the common 'not update' problem of, but that's something different than @davidpbrown described (@Vincent reproducible, also on small files, only in, @davidpbrown hard repro, only big files, plenty versions), 

thus it's neither a dup, nor a valid repro, nor a proof of solved, unduping and setting 'needinfo', 

@davidpbrown, if you ever again stumble over such behaviour try to 'catch' it, keep the last saved version of the file (don't! 'save'), do a 'save a copy', try with ctrl-z and ctrl-y to find the step(s) injecting the error, make 'save a copie(s)' from states before and after, note a script which changes you did from the beginning, try if reproducible, if yes try to anonymize the file (there is a wiki somewhere around how to do) and if the error is kept, provide whatever you can, 

for linux there shall be a tool to work with journaling capabilities which can save a file together with start state and rollforward journal, 

if your errors affect multiple cells not dependent of each other they are different from what i experienced sometimes, for me it was sporadic fails for one cell and sometimes others referencing it, 

(got one 'catched' recently, including rollback - rollforward and screen recording, but :-( not reproducible from new load, and with ver. which i have to do my daily work in, thus 'outdated' and likey that flavour fixed inbetween, thus not going to invest the effort to anonymize the video, screenshot is attached ... it occured after copying the range S22:CG24 to S23:CG25, doing some changes in texts in S22:CG22 and some other cells, none referencing or being referenced from V22:AV22, changing two values and some text entries in V22:AV22 (was identical to V23:AV23 before because copied), noticing AX22 not! updated, you can see the correct formula in the input line, and the wrong result in the cell, autocalculate was! on, 'threaded' and openCL were off)
Comment 20 b. 2020-10-25 09:42:07 UTC
couldn't change to 'needinfo', not present in the dropdown, thus 'new' as i know that such errors occur ...
Comment 21 Buovjaga 2020-10-25 09:55:13 UTC
(In reply to Buovjaga from comment #13)
> Needinfo while we wait for the file.

Ok, then let's return to this status. The ball is in David's court.
Comment 22 QA Administrators 2021-04-24 04:10:33 UTC Comment hidden (obsolete)
Comment 23 QA Administrators 2021-06-11 03:49:51 UTC
Dear davidpbrown,

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