How does Calc load file into memory and prepare the calculation to be presented

I hope to be guided to the most fundamental documentation about this topic. I may then be able to improve the question and get much more brief. The aim is to be able to improve (or even resolve here) six Bug reports, notably 86321 (charts not updating) yet several others. To do that, it’s necessary to provide a method to reproduce, which (for some bugs) is almost (yet not quite) beyond the user. We generate spreadsheets using much copy and paste and find out later that cells are no longer calculating. I’m aware of about ten bug fixes for grouped formulae, triggers concerning autocalculate, hard recalculate and so forth. Also aware that more recent versions of Calc may be configured to not recalculate old files so as not to change the result seen by two different users with different Calc versions. Well, that’s the context for the question.

Reason to ask. Investigating Calc 5, 6 and 7 versions it’s evident that LO 7 incorporates quite a few fixes, yet as newbie 02 (b) observes, that seems to be masking something in the foundations that’s not yet adressed. I have about ten files to investigate with different symptoms.

For example, one will load, as usual viewing the last sheet from the last Save. It opens with half the formula cells showing zero. The formats check out as correct, there are other cells with absolutely identical formulae showing the true result. The content.xml reveals two different conclusions about precisely the same formula. Recalculation (to update the cells in limbo) can be triggered by toggling a cell “early” in the sequence, one that has “data validity” criteria (1 or 0; supplying an IF choice on every sheet which simply fetches raw data or analysed data). Or, one can also trigger the calculation by toggling once Calc>Tools>Options>Libreoffice Calc>Calculate>Iterations to on then off. The file and content.xml actually contain no circular references yet can be stimulated to show the result. In this particular case, Hard Recalculate also works.

However, some bugs resist these methods, notably when charts are involved. Most odd (about the example) is that twin (identical) sheets not yet viewed are recalculated, yet only for cells that are outside the application window view area. After recalculate, clicking onto those sheet tabs, cells in the viewing area (often columns A to W) are still at zeroes. It seems recalculation depends on the sheet having already been viewed before (at least once, even if nothing is done on that sheet). Yet, surprise. Sliding the horizontal scroll bar reveals . . . . columns X, Y, Z, AA, AB etcetera have been done. Very odd. Not because they’re in the view area - rather because they’re out of it.

If the user doesn’t notice, cells out of view are being processed differently than those in the application window. If the file is editted and saved several times, the “mix” of calculated or not-yet is cemented into the ods content.xml. When reopening, a user hasn’t a clue how that occurred and very little chance to provide steps to reproduce. The “buggy cells” are all over the place, depending on how he or she was opening sheets (or not) and sliding the viewing area.
I did notice, because I’ve chosen to work two 4K monitors with a new habit of stretching out a window. If using two files, I’ll narrow one window down a bit.

In Bug report 86321 a developer (Eike) remarks that perhaps the consequent failures to update charts is “not a Calc problem” and another developer notes that the bug occurs (or not) depending on whether certain sheets have been visited/viewed at least once already. Libreoffice 5 versions will open while refreshing all charts (which LO 7 still doesn’t achieve). Yet LO 5 Calc user experience is that once you start inserting, copying or moving sheets there’s a sort of “patchwork” of valid and invalid views.

To work this out, I hope to learn about the rules for File>Load; going as far back as necessary. I do notice that soffice.bin is attributed memory getting progressively larger with each sheet opened/viewed and expect the software is attempting to be economical in memory and load times. Furthermore, I’m aware that Advanced settings - cache memories have been discussed at length. Does a remark “else than a Calc problem” mean it’s a global setting of Libreoffice that Calc developers don’t touch or a Linux kernel factor or something to do with gnome, gtk, windows or hardware (my computers have integrated Intel graphics that use the main RAM, somehow set aside for that avoiding collision with other applications - it seems).

I see the cache parameters are discussed on asklibreoffice (and not Bugs) so hope my question is appropriate here. I hope to learn deeper principles about how the cell/viewing is prepared in software - since the very beginning of Open office. I do (of course) reset user profiles, test factory setting and test everything on at least three versions of Calc, using Ubuntu 18, 20 and Windows 10 Pro. Some who have these bugs have also reported “the Bug’s still there” and then “I restarted this morning and it’s gone”. In theory, one load of a file is identical to another. To work that out, a bonus would be if I can learn how to inhibit/bypass original or recent file load economies software modifications. Here, it doesn’t matter how much RAM is used nor how long it takes (I’ve 32GB and 4.2 GHz). Then I might be able to improve the Bug reports and win 150 dollars from newbie_02 (b). I don’t code, but I do understand specifications, the older the better.

heyheyheyyyy!!! @Archetype: somebody thinking and investigating! like that!
probably you are overtaxing the scope and focus of this ask site and would better post in ‘bugs’, but you already know about and it’s more a question than a bug report … ok for me,
there was a very promising discussion started in with - imho - potential to explain ‘all’, as well recalculation issues as sporadic appearance as reproducibility issues as different behaviour on different systems,
but then somebody ‘kicked out’ the developers, i’d really like them to be aware of such possibilities …
… continued …

… continued …
I have the feeling that ‘recalc issues’ occur frequently with me, I use a 4k screen, if any property of a cell depends on the graphics equipment and is sometimes classified as ‘update-necessary’ and sometimes e.g. with other resolution as ‘not-necessary’ and then somehow ‘loses the connection’, but just only with certain systems … maybe it’s enough to draw the ‘border’ for the distinction between ‘inside’ and ‘outside’ a bit further … ??? ??? ??? It would be a ‘tennis’ or ‘John McEnroe problem’ … ‘in’? or ‘out’!
pls. keep going …

Good metaphor I think. Like a sports hall with lines for badminton, basketball, handball and five-a-side football all laid out on top of each other according to their rules. Here’s an example of a programmer deliberately limiting an in/out rule. r - Using an apply function to select a window of cells from a matrix? - Stack Overflow

The LibreOffice conference 2016 politely (and kindly) suggested that Software Interpreter would make user bug reporting difficult or nearly impossible. For example, in the tdf bug link, a step by step is given, one bug at file>open but another bug at the last step. To see it (that ghost bug) requires SI “on” and a certain view zoom (or 100% on a 4K monitor). Someone unable to reproduce (SI “off”) had the crucial information but went off in a huff.

LO 7 Calc seems to have Software Interpreter always involved (without the option). It’s understood that’s to be kind to most users. I’m working on a Calc 7 set by step for the bugs.

By the way, I’m also keeping an open mind about the need for User workarounds (now and in future).
Limiting (programming) software attention using matrices/pages in RAM is often (but not always) about performance and economy. I may find a simple way for Users to to gain/force at least “whole sheet” attention when speed isn’t an issue. A named expression or data range technique that can be stretched out to a cell in a far out column and row for example. That’s a conjecture (for now).