Created attachment 151458 [details]
autocalculate_error_threaded_off

file with wrong results, 

with recalculation on load on or when loading with older versions (4.1.6.2) the errors are corrected on load ...

To check, if you have it enabled: Data - Calculate - Autocalculate

(In reply to b. from comment #0)
> ctrl-shift-F9 corrects the problem, thus *only* 'broken autocalculate', 

https://helponline.libreoffice.org/6.4/en-US/text/scalc/01/06080000.html?DbPAR=CALC#bm_id3157909
Ctrl-Shift-F9 is *hard* recalc, which recalculates *all* formulas in the document.

(In reply to b. from comment #0)
> i stepped into this while working on 124270 and having err:522 with threaded
> enabled, boiling down and even disabling threaded results in this fault. 

In the summary, you have "with threaded off". The above seems to contradict this. Is this related to threaded at all?

(In reply to b. from comment #0)
> Steps to Reproduce:
> 1. open file attached with next comment, 
> 2. check results in CG1:CG1105 being '0' despite the formula should result
> in '18', 
> 3. if corrected by recalc on load or other: 
> 4. i copied lines 1:8 from another sheet into this one, 
> 5. expanded A1:CZ5 down to row 1365, 
> 6. filled in some start values in row 1365, 
> 7. checked the results, 
> (autocalculate was! on all the time), 
> 
> a second try overpasting the existing faulty sheet failed - to fail -, a
> third attempt with deleting the contents in the sheet, pasting in 5 rows and
> pulling A1:CZ5 down to 1300 inserting the same values in row 1300 produced
> faulty '0' only in rows 1 to 5.  

I ignored steps 3 and 4 as they did not seem necessary.

After inputting "5" in CC1365, I get err:522 in CC and CG columns from row 1322 upwards. Do I reproduce the bug in your view?

Arch Linux 64-bit
Version: 6.4.0.0.alpha0+
Build ID: b9a776837462eeb6d50d0decc42604c0c3008eb1
CPU threads: 8; OS: Linux 5.2; UI render: default; VCL: gtk3; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 11 August 2019

@buovjaga

> After inputting "5" in CC1365, I get err:522 in CC and CG columns from row 1322 upwards. Do I reproduce the bug in your view?

i think i was quite distinct in my description, 

i think you reproduce a / the bug, 

after such long time i have to do full replay to check if it's *exact* that bug, 

at all it's a confirmation that something is broken in this area ... 

imho it should be analysed in depth and corrected ... 

pls. set to 'new',

reg. 



b.

(In reply to b. from comment #3)
> after such long time i have to do full replay to check if it's *exact* that
> bug, 

Ok, let's set to NEW, but a retest would be appreciated indeed.

*** Bug 137312 has been marked as a duplicate of this bug. ***

checked this procedure (tdf#125320) and tdf#137312 today, no repro, neither with 6.1.6.3 nor with 7.1.0.0.a0+ (last threaded and unthreaded), 

BUT! that's the problem and a big problem for LO bug handling in general: 

one CANNOT! request 'stable reproducers' for sporadic fails, 

on a simple view both bugs look like 'just wrong values in the file, recalc and all is ok' (contrary to the description of 137312 as well F9 as ctrl-shift-F9 as deleting one cell in the last row triggering a recalc, worked to correct the wrong results), 

on a 'deeper thought' both bugs evolved in the normal use of calc, produced massively wrong results, and the user has little to no chance to notice such fails, 

it is! easy to produce such sheets with wrong results intentional or by accident, autocalculate off, change data, save file, wrong results are saved and NOT! corrected if you switch autocalculate on again, it needs a manually triggered recalc ... but that's not! what we did, in my case such errors occured from time to time in normal work, and then when trying out for tdf#125320 when expanding some rows with not too complex formulae, 137312 was a autoalculate fail on copying formulas to ranges ... 
 
as i experienced some of such fails, and as other users have them too i'm sure it's not a weak ram module in my machine or similar (besides ... it's ECC Ram), but a weakness in calc, 

i know it's difficult to track down, but i also know it's very important to do as it undermines calcs relyability in general ... thus tried raising importance it didn't work reg. lack of karma?, please mods do so, 

general idea: calc needs a better bug-check and 'system to ensure correctness' than now,  there is urgent need to cover 'sporadic fails' ... 

(gallows humor: administering bugs and duplicate marking is already secured by double processing ... why not for calculation results?)

@B
So to summarize. The original problem disappeared. So this bug should/can be - based on the report itself - be closed.

The remaining issue is 
(a) How could this be allowed to happen?
(b) The risk that the similar issue will be re-introduced?
(c) You losing your confidence in newer versions after 6.1.6.3
(d) About the bar to meet for reporting. So you have to proof the (nearly) impossible. So impossible to recreate the original circumstances, as there are no simple (straight forward steps). More "boom" and it happen again. And every attempt to recreate fail.. but you did encounter it.

*I acknowledge you did put stuff forward and/or make me aware of certain calculation bugs (which weren't properly addressed and being open way to long).
**I'm aware about number of iteration bugs, which if it's up to me ideally should go. As calculation is about confidence/trust. And if you encounter calculations issues this tends to vanish and really hard to restore. 

As you certainly aware of this these issues appear and get fixed in case by case basis. I'm aware you dislike this kind of patchworks. However I don't see how this could be done differently. Also you do now that those 'patch' work thingy tend to work (even if you dislike the approach ).

And yes, sometimes a type of bug re-appear in different shapes an forms (currently bug 137248 (and maybe bug 137312)) Before bug 132451. 

I do still value you're concern/input. I still hope you want to check out 7.0 someday (bug wait until bug 132451 is gone :-).

Related to steps to reproduce. I sometimes use a screen recorder (and shortcut OSD). Let it simply run while you're working. So there is a way to look back in time. Even slow down etc. This might help figuring out the problem (if it's still present).

You're powerless & frustrated by the calculation error. We are powerless because we can't be of any assistance without "no proof". So we end up in an impasse.
We assume it's good, until proven otherwise. We don't deny might could happen (as it happened before). However unable to solve it.

@Telesto, 

thanks for your understanding words, 

no, the original report shouldn't be closed, as said there: 'the error happens in rare cases and in large sheets', 

thus two or three 'non-repros' are only an indication that it could work without errors, and that is inherent in the OP, thus nothing changed, 

statistically (i know that this is not an exact science) there are methods to tell if 'probably' something is different or not, depending on the frequency of an 'event' and the frequency in a certain sample, 

i am not a mathematician or statistician, but i would think that if there was an earlier occurrence of maybe once in a thousand operations, it would take about three to four thousand negative tests to have a significant significance that something has changed, 

we do not have them and we cannot afford them, 

possibility one: implement a 'control system' in calc similar to what you do for airplanes or other important tasks, comparative calculations on another way, if the results match, you have a good chance that they are correct, if they don't, you - mostly - have a reproducible error, 

option two: try, try, try until you find a reproducer, 'please users: save your files, then do a recalc and compare the results, if error pls. report',

option three: put aside as 'no-repro' as it's actual practice, 

option four: mathematical - logical analysis of all program functions ... much work, 

possibility five: rewrite calc and hope for fewer errors, 

possibility five-a: do that in chunks, for e.g. our nice problem fields 'comments' or 'copy/paste' or 'iterations' ... instead of more and more patches in the existing system ... write a replacement, implement an option to toggle between them, once the new works better delete the old scrap,  

option six: someone else could come up with something better, 

option seven: 'hard recalc' helps in many cases, we simply explain hard recalc from 'control' to calculation mechanism, 

option eight: use other spreadsheets like ex$el or go$gle and hope for other or less bugs, 

option eight-a: use other spreadsheets as a control mechanism for option one, the data is sent online to M$ and google in the background and calculated with 'sheets' or '365', do the results match ... 

i have been working on some 'hard repro' bugs, i mean most of them were due to 'shared formulas', if it wasn't too small on my screen i would probably go back to version 4.1, you see from above that i'm quite frustrated, 

i do have a lot of understanding that this also affects many developers, 

and my frustration will grow if @xisco closes this comment as 'no value' ... 

P.S. retested 
'deleting the contents in the sheet, pasting in 5 rows and pulling A1:CZ5 down to 1300 inserting the same values in row 1300 produced faulty '0' only in rows 1 to 5.' 
pasting in a copy of row 1, but pulled down to row 1365, repro in 6.1.6.3, norepro in 7.1 from 2020-20-04, there is hope that something changed, but nothing conclusive ...

(In reply to b. from comment #9)
I still hoping Michael being able be of some assistance here. He does care about this topic and belongs to they higher echelons of the project. However major problem is there is not much to 'work with'.

Sudden miscalculations are pain. There however multiple causes: OpenCL, multi-threaded or iterations and or/the specific document (or order of doing things).

And I'm admitting that the user isn't ideal person to verify calculations or to figure out the issue. And that there number of variables involved which don't make it easy to track down issues :-(

I'm not a heavy Calc user myself. So working with the stuff I'm able to reproduce (and bibisect). They rest are elusive bugs. I wouldn't be surprised if there some flaw with calculations somewhere (or at multiple places). However, without an example there is not much to be done about it.

Also no idea if there are (stress testing) models available to verify results with different OS'es Windows/Linux/Mac with different configurations (OpenCL only, Multi-threaded only or both enabled to verifying results). Maybe even on different configurations (Amd/Intel). 

Also no clue if OpenCL wills stick around, or will be phased-out in favor of multi-threaded calculations. With limited resources I prefer to 'stick' with not to many options which aren't 'properly' maintained. 

[Off-topic: Also referring to Skia, I hope all environments (and all backends) will use it someday. It's slightly frustrating that certain issues are limited to the Windows environment. FWIW: I would prefer GDI to be moved the expert settings for 7.1. As 'testing backend only. Performance (especially related to images/shapes/bitmaps) starting to become awful already as far I noticed (and not reporting anything about that as it will be gone anyway). But bit out of scope here

However, I tend to agree that 'reports' without a showcase, not being really helpful. And they bug tracker isn't the ideal place for 'concerns' or hunches.
[of course there are no 'hard' rules what belongs to the bug tracker and what not, but tends to go to the NOTOURBUG area] 
OTOH, dropping out the bug tracker somehow suggests nothing being wrong; and pretending nothing is going on.

I do get you're frustration. I'm pushing for bit more attention for user experience in global; wishing for a bit more polished product in general (quality control). In my case mostly orientated at Writer/Impress. As see reproducible problems there.  
And not knowing much about Calc and the calculation bugs being bit problematic to get hold on :-). But Calc belong to quality control also.

However this isn't easy thing to archive, based on how things are organized around here. There is no central organization. It's decentralized organization with different people working together (somehow) with different agenda's, interest, concerns (and budgets). At the end everything is monetized in expenses an revenue.

Anyhow. Michael, any idea how approach/tackle/handle this topic here. I don't want to dismiss the calculation issues of regular QA contributor. OTOH, not big fan of bugs reports leading to nowhere, practically speaking. Or a way how b. can help out. Say logging debug information. They issue must be show up somewhere?

If there is an issue that is reproducible, and is not related to iteration - then I'm really interested in it =) is this one ?

(In reply to Michael Meeks from comment #11)
> If there is an issue that is reproducible, and is not related to iteration -
> then I'm really interested in it =) is this one ?

b. is claiming there something off (for a while now and being pretty persistent about that). Sadly, without being able to come up with a reproducer. And I want make clear that we do take things seriously. But that where bit stuck in a dilemma. 

He says it happens, and try it yourself (and it will appear).. However not heavy Calc user myself. And not liking to idea of playing around without a clue what to do or where to look at. And don't think there are many volunteers for that at all..

And I understand it as he is struggling to proof the issue (which is or is not present). Suddenly happens without additional info. I'm slightly thinking about letting him run in SAL_LOG WARN.. mode so he can at least point at something..

Not sure if there is something else we do to help him investigating the mystery?

trying sometimes, will report if i get a reproducer, catched one with an old ver. (6.1.6.3), but no repro from saved files, 

description in 
https://bugs.documentfoundation.org/show_bug.cgi?id=125052#c19 
and screenshot in 
https://bugs.documentfoundation.org/attachment.cgi?id=166689

sadly have to do my daily work in 6.1 reg. comment slowdowns in subsequent ver., thus small hope to catch one in recent ver. :-(

and sadly have to work with win reg. comment slowdowns being massively bigger in linux, thus no chance to use logger tools or similar, 

i do what i can, would be helpful against sporadic fails if calc could have something like a 'bug catcher', save a file with last saved state and everything done as 'redo steps' ... @Buovjaga said there is a logger for linux? ...

(In reply to b. from comment #13)
> @Buovjaga said there is a logger for
> linux? ...

https://wiki.documentfoundation.org/Development/UITests#Tools_for_writing_a_test

Created attachment 168720 [details]
example faulty file to reproduce deeper issue

I think I can provide the test file and it explains why users have a very difficult task to reproduce.  This is reproduceable if readers can bear with me.
The demonstration requires a version before bug fixes, patches (and attempts) concerning autocalculate to see the problem. So I use Calc 5.  It is quite extraordinary (in my experience) and indicates a short-circuit somewhere with the graphics tasks.  That version is needed to look "under" the patches and commits.
Demonstration on Ubuntu 18.04 LTS with Calc 5.4.7.2.

Step 1.  Open Calc 5.4.7.2, new sheet, set the Window to show columns A to half of column W (narrow field of view of the Libreoffice application window).
Step 2.  Restart Calc 5.4.7.2, open example file (shows oilolive_3 columns A to V and half of column W in the visible window).
Step 3.  Stay on oilolive_3 sheet.  If browsing the file to learn the basic controls, the bug will not reproduce.  Go back to step 1 when ready.

Note;  sheets oilolive, oilolive_2 and _3 are all identical twins.  Raw data is at  rows 500 to 600, columns A to K.  Then the data is repeated upward in blocks including one statistical formula group (exponential smoothing).  The user selects raw/filtered data using 1 or 0 at cell $A$5 to place useful results in C6 to AC112.  In the example file, that choice comes from a user control on PLOTXYdum sheet (x,y scatter chart removed to make simple).  Then the 1 or 0 is sent to the INDEX sheet then copied to $A$5 on all data sheets for the IF choice.

Step 4.  Observe the sheet oilolive_3 has cell L6 set =C6.  Cell U6 also set =C6.
They have different results.  The same is the situation for all the rows.  (Content.xml confirms the file was saved with different conclusions about the calcext format compared to openoffice result).  How to reproduce this defect will take me some time - but the next steps reveal why (I hope!).

Note; Commits up to Calc 7 have patched this and other autocalculate issues.  I know.  Also, Hard calculate (Shift Ctrl F9) works with Calc 5 and will refresh all the sheets if the user notices the problem.  For this reproduce, it's assumed the user doesn't notice so continues. . . . so please look at the foundation problem as follows:

Step 5.  Stay on oilolive_3 after opening the file columns A to V in view without recalculate.  That will be provoked in step 6.  PLOTXY_2 is superfluous;  so . .
Step 6.  Right-click sheet tab PLOTXY_2 then Delete Sheet, confirm Yes.
Step 7.  Sheet PLOTXYdum is now viewed and last sheet.  Keep the window size (no change to the application frame on the desktop).
Step 8.  Click once on oilolive_3.  Columns U,V etcetera are all updated by triggered automatic recalculate (is the expected result).
Step 9.  Click once on oilolive_2 (must be the first occasion it's viewed).
Step 10. Columns U and V are not updated.  
Step 11. However, click and slide the horizontal scroll bar and drag it right.  The columns beyond the application window on oilolive and oilolive_2 were updated at Step 6. 

Expected result:  formulae and cell values are loaded and modified and saved completely independently of the user view on his desktop, whether autocalculate, hard recalculate or bugfixes are triggered or not.

Actual result:  there seems to be a short-circuit between calculate and the graphics layer cache.  The user saves a file with "mixed" cell states depending on where he was viewing.

I understand (experience in realtime processing software debugging) that we can economise on RAM and processor time if (and when) it's acceptable to "only prepare/show in the window".  I notice Libreoffice does this economy with charts that redraw as the scrollbars are moved (CPU% and memory useage increases/decreases).  That's quite a good idea.  I'd like to switch that off (because I can use 32GB, no problem) but understand the economy for smaller PCs.

I've never seen autocalculate decision based on application Window frame view - and outside the view.  Please advise me of documentation or how to learn about graphic layers interface Libreoffice to Calc to Ubuntu to do more about this.  b has offered 150 dollar prize!

Additional note.
If the steps to reproduce are done after viewing a sheet (just view, click once without doing anything) that sheet (oilolive or oilolive_2) will also have all columns autocalculated.

The sample file is without circular references;  no iterations.
However, there is another way to provoke a recalculation.  Selecting Options > Calc > calculate, the Iterations is (of course) unchecked because the file has none.  Toggle it "on" causes sheets that have been viewed (at least once even if no user work done) to autoupdate.  Visit a sheet means it's included in calculations - or half, depending on where you look while working.

I try to find a way to reproduce the original attached file for everyone before february (if possible).  It was too big (40MB), so I was deleting charts, deleting sheets and suddenly, somewhere the integrity got broken.  When oilolive was simply copied as new sheets I didn't notice the corruption;  one action later, everything updated on the sheet I was viewing.  I thought it was the Bug about using grouped formulae.  The stats (exponential smoothing function) inserts formulae with spaces between every reference, space + space, space * space and ( spaces ) which is odd.   I tested removing those spaces.

Bugfixes included in Calc 7 mask this topic about a relation with view area and calculations.  The sample file opens with the =C6 difference, yet any further use of Calc 7 refreshes all sheets so I can't learn much more about this using Calc 7.
Why use Calc 5 at all?  It's the last version that can save>close>reopen a file with broken charts and they all work again.  For simple maths I find Calc 5.4.7.2 is very good!  Same excellent graphics.  So it's the only workaround I can use.

I have a new, empty, extra, 32GB, 4.2 GHZ PC with 48inch 4K monitor here. It has dual boot Windows 10 Pro / Ubuntu 20.04, parallel and AppImage and Calc versions multiple choice.  Any tool that can help - I offer to install it.   Speed/slowing doesn't matter. Huge SSD also.   To go back to my Cancer Research, I hope this topic might resolve Bug 86321 also (charts suddenly stop updating).  Eike mentionned Bug 86321 "perhaps might not be a Calc issue".  I've tried purging user profiles and so forth.

Extra note:  If anyone knows how I can set a "force" that all cells are always all loaded into memory (in view or not in view area) I'll do that first and see what happens.  I can inspect saved xml here.  I notice abandoned data validity attributes stay in the content.xml (not removed).  However, removing them manually was without effect;  new data validity cells are added correctly with new names, either Calc 5 or 7.   Otherwise the xml seems a perfect snapshot of the file state.

However, I don't know much; yet willing to read specifications and learn.

Pardon, excuse me: attachment 168720 [details] version used is "parallel installed" (not AppImage)
Version: 5.4.7.2
Build ID: c838ef25c16710f8838b1faec480ebba495259d0
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); Calc: group

I've found Eike's list for reading.  So I'll try to learn from this:
https://erack.de/bookmarks/O.html#OOo

specifically the section "the olde stuff".

It's known that many reasons can cause a zero result in a cell, often to do with formatting, shared formulae, often solved by the calcext for Libreoffice.  Bugs due to copy and paste resulting in zeroes are investigated and resolved one by one.  So, what's new about this?

Please educate me if necessary.  I don't know of any calculation/autocalculation/recalculation that depends on the view area of an active window. Am I missing the point? 

If it's a problem, it explains a lot.  Users (like me) slide scrollbars all day, which causes a patchwork of cells that may or may not have been refreshed by autocalculate.  When a Save is done - the patchwork is faithfully reproduced in the content.xml.   We'll find out about it much later when scrolling around and, perhaps, clear the affected cell too (without knowing how, because it's else than a formula correction - it's because the view window was looking elsewhere!)..   I hope my attachment is useful.  I'll hit Shift+Ctrl+F9 a hundred times a day if it might keep the spreadsheet useable.  Just to repeat, the attachment file is without any iterations nor circular references and Calc 5 opens properly with iterations off (of course).

History.  An example.  Unexpected zeroes in cells after simple formulae (or none at all, just like mine put L6 to =C6) occurred in Microsoft Excel 2007.
https://www.accountingweb.co.uk/any-answers/excel-formula-only-displays-zero

There the Users (accountants) have difficulty in believing what they see since 2013.  Users of 2020 who continue with older hardware and 2007 software are very puzzled.  They don't mention if they've moved the view area around to see behind the Window Service and their forum is without file attachment options.  They try to work out if they're unable to understand the sum of nine numbers (as one cause) or looking at a real Bug.

If they find iterations (my file has none) it is sorted out, just like for Libreoffice;  one example
https://www.excelforum.com/excel-general/768809-formulas-keep-returning-a-value-of-zero.html

Please guide me to where I can trace Microsoft Excel debugging since 2007.  Specifically I can look to see if they had a "window area dependent Bug". I'm not an expert in Xorg or the other compositer/client/servers, nor Wayland today.  Yet I feel (am I wrong?) that it's soffice.bin fetching the Window view area (for some reason). Then (odd behaviour) it's calculating a photo negative of that.  How does this comment help or how can I improve it?

Hi Matthew, this is a franken-bug since it mentions iteration - and so gets ignored. Its great that you're doing some investigation here. If there is a clean bug that doesn't involve iteration, or any odd custom settings - and we have some clear reproduction steps (ideally from a clean sheet - that we can turn into a unit test later) - then I'd be really interested in that; perhaps best filed as a new clean bug as/when.

Lubos - probably this should be on your radar.

Hi Michael.  Thank you for the encouragement.  My text was too long. Briefly. attachment 168720 [details] is offered because it's without iterations (none).  Steps 1 to 11 are reproduceable every time (here).  

[I did mention iterations that are bugging other users.  But not this one.  Probably best if I'd not mentioned the subject!  Apologies.]

Next, I'm attempting to reproduce how Buovjaga saw err:522 only from row 1322 upwards (using b's original attachment).  It looks like we observe and test with different application Windows sizes or save (for a cup of coffee), reopen or scroll . . .and end up with a different record (xml) again.  I'll be swift (and brief) if I can learn - does any spreadsheet software limit or flip it's calculations according to the Window frame requested of the OS compositer?  It's a first (time) for me! (My Realtime processing background - we do that, but I didn't ever expect it of Calc.  It's doing it and broken too, but why; false economy?).

Next.  Yes.  I'm working on a clean sheet reproduction (getting there) yet I think attachment 168720 [details] shows the fundamental cause already.  Apologies again for flashing side-comments and notes above.  If stats exponential smoothing is some sort of pseudo-iteration without saying so, please let me know.  It's very convenient yet only does the same as formula per cell all the way down (I use it to learn the formula, not more).

This is long, but after reading this it seems to me that it can be summarized as follows: If one uses old LO version and saves incorrect values, current LO uses those values and does not recalculate on loading?

Is that it, or am I missing any other problem here with recent(!) LO versions?

(In reply to Luboš Luňák from comment #22)
> This is long, but after reading this it seems to me that it can be
> summarized as follows: If one uses old LO version and saves incorrect
> values, current LO uses those values and does not recalculate on loading?
> 
> Is that it, or am I missing any other problem here with recent(!) LO
> versions?

Overall, I read that users' copy paste delete insert and suddenly notice cells not being calculated.  The problem is sometimes reproduced and fixed by triggers.  One bug after another or, if not yet fixed, users are asked to consider hard-recalculate (any version of calc using the menu or shift+ctrl+F9 for Calc5).

b raised this bug to show a typical sheet, with obviously wrong results.
Buovjaga tries it out and obtains different wrong results (which is odd).
This (and other bug reports) suggest autocalculate probably needs attention.  That's the point.  Any version of LO.


b is firm about it.  It's my experience too.  Reproduceability of a formula bug becomes near to impossible if autocalculate has a deeper bug changing what we offer to the developers for investigation.

I check out why.  How can autocalc be variable?  Seems impossible.

I add an new attachment with three sheets also with obviously wrong results.
Not recalculating on load (Calc 5) is patched (Calc 7).  So I use Calc 5 as a tool to inspect "autocalculate", to see what it's doing (or not) about wrong results.  Can it get them "right" later and with which triggers?

I noticed Calc 5 autocalculate (during use) but ignoring cells visible in the open Window (columns A to V and half column W).  Yet I learnt it did do columns X,Y,Z, AA etc which were not visible to the user.  I estimate I've caught autocalc red-handed, limiting it's attention to user application window size (which is variable).

I estimate this bug and bug 86321 are stuck because there "shouldn't be" any link between autocalculation scope and window frame view border pixel coordinates.  I may have misunderstood this.  I don't know what you know about tables to pages to Xorg.  Recent (!) versions of LO?  Same autocalculate.  Calc 5 is just used here as a tool.

If autocalculate doesn't (in fact) exist; if it's just a fancy name for hundreds of bugfixes since 2007 all bolted together then I'll change my contribution, approach and so forth.

(In reply to matthewnote from comment #23)
> If autocalculate doesn't (in fact) exist; if it's just a fancy name for
> hundreds of bugfixes since 2007 all bolted together then I'll change my
> contribution, approach and so forth.

I would say there is autocalculate  but well there are two algorithms. Multithread starting at 100 entry's in a column (I guess) and normal mode.

Also there where quite some number of refactors.. Without reading everything.. 6.2 any better?

Without brief basics - by dev departement - by the rules governing calculating in principle - which put somewhere at the documentfoundation wiki, a user can go in circles figuring out what's wrong :P. As there is hard to figure-out yourself

The analogy of a patchwork of bug fixes is some sense true. But nobody will be able to 'build something' which doesn't have certain flaws much must be patches.. Creating new problems and so fort. So 'patch' work is not necessary wrong...

There is a 'case' (or worse say 3 cases stacked onto each other) where a dirty flag for cell isn't set/missed, so re-calculate not being triggered.

Or that's my simple explanation.. There likely outer possibility's. .. but that's black box area for me too... The dirty flag thing is rather easy to grasp :P.  

The alternative for re-calculating the whole sheet on every input is obviously undoable.. would be tremendously slow on big sheets. However I would avoid this kind of problem, I think..

FWIW: I'm having some experience with tracking those bugs (mostly by pointers of b.)

However this one is hard to follow (and bit of mess). Obviously because because being a 'hard case'

> 
> FWIW: I'm having some experience with tracking those bugs (mostly by
> pointers of b.)
> 
> However this one is hard to follow (and bit of mess). Obviously because
> because being a 'hard case'

Thank you for the overview.  Perhaps attachment 168720 [details] and steps 1 to 11 are crucial.

First things first.  As a user I feel asked to upload a file with steps to reproduce.  Done.  Anyone on the e-mail copy list already found time to open it and do the steps?  Usually we start off with two investigators who see the same thing before anything else.    

Parallel install Calc5.4.7.2 is, of course, "annoying" (here too) yet I hope not to waste a minute of Michael's time.  It's evidence of a layer problem that seems to require his level of experience even if not Michael in person.  

Seems daft (and maybe it is ridiculously simple), yet looking at columns U,V,W then scrolling to X,Y,Z is faster than reading here.  I worry about that (a lot, far too much).

It is safe to say that if a broken file produced by an old version of calc is required to reproduce the problem, then it's not that interesting to us.

Then again - if we are confident that older versions of calc produce unhappy files we can potentially detect and force re-calculate of those on-load (I guess) - that is reasonably easy to do.

If the problem can be reproduced from a blank-sheet in a modern LibreOffice then that is -extremely- interesting =) but having a new bug report with a clean set of reproduction steps: starting from launching calc and adding (presumably large range formulae) etc. would be extremely interesting. Thanks.

(In reply to Michael Meeks from comment #26)
> It is safe to say that if a broken file produced by an old version of calc
> is required to reproduce the problem, then it's not that interesting to us.

Understood.  

> Then again - if we are confident that older versions of calc produce unhappy
> files we can potentially detect and force re-calculate of those on-load (I
> guess) - that is reasonably easy to do.

Got it.  Xisco Fauli was/is looking for help to do that for Bug 86321.  Buovjaga's tracing it for a few years.  LO7 (fresh) is still unable to show unhappy files with X,Y scatterplots broken-then-working, so it's at that stage.  I contribute user workarounds.  Engineers and scientists are clicking,holding and stirring all their plots a few pixels to keep going.  A real renovation on-load is hoped for there.
 
> If the problem can be reproduced from a blank-sheet in a modern LibreOffice
> then that is -extremely- interesting =) but having a new bug report with a
> clean set of reproduction steps: starting from launching calc and adding
> (presumably large range formulae) etc. would be extremely interesting.
> Thanks.

Working on it (for this Bug here and others where I can).

whowww!, a real blow up of thoughts and work on this problem :-)  ... and hope ... 

sorry, at this moment i don't have time to check every step by myself, thus pls. be indulgent if i'm off the track with any point mentioned in this comment, just some thoughts: 

@matthewnote@yahoo.co.uk: :-)   your idea and analysis looks very promising, especial 'random fails dependent on actual view' could explain the reproductibility problem of plenty bugs, pls. keep going, 

"150 EUR prize" - i remember i offered money to get rid of faults but couldn't find it in this thread, if your efforts lead to a solution - mustn't be complete but just any significant enhancement - ask for three times that money and you'll get it, 

something in the discussion goes in the direction of 'error dependent on re-use of broken files cluttered by old LO versions', that may be right but didn't come to my mind when filing the original bug(s), 

@MichaelMeeks: "If there is an issue that is reproducible, and is not related to iteration - then I'm really interested in it =) is this one ?" 
yes it is! - without iterations, i just mentioned iterations as another field where progress is hindered by the ways LO and bugs currently work and where other approaches could possibly bring improvements, this bug and all the others i'd file about autocalculate fails and probably depending on shared formulas is / are totally independent of the iteration settings ... afaik, 
ni it isn't - reproducible, that's the big issue, i'd frequently step in issues which are fails and re-occur similarely, but are not 'reproducible on command', :-( 

"Hi Matthew, this is a franken-bug since it mentions iteration - and so gets ignored." - that explains much ... to me ... being ignored :-( ... but, again, this bug is not in any way about iterations, it's about autocalculate fails probably dependent on 'shared formulas' appearing also with 'threaded off' contrary to being assumed dependent on threaded or parallelized calculations being set 'on', 

@Telesto: thanks for your help and understanding, they helped me to stay on board, i'm not sure if i would have done without ...

@b and @Telesto. Hi.  I know Eike prefers zero prose.  Yet I think to clarify.

"Patchwork". I used the word in the meaning visual collage of the view area seen by users.  Did not intend to mix with the word "patch" for software.
"Mess".  I keep going usually constant "everything has a reason".  20 years testing then designing software for aircraft and ship automation meant everything was long distance and difficult.  I don't have to volunteer to try that level of pain.  It's the only thing I know. 

Example - with a point.  We (Raytheon) had our worst moment (style Boeing 737 max).  Petrol tanker hard left across oncoming traffic at full speed.  I happened to be on-board for a different job.  Black box style recording on, but didn't have keyboard/mouse logging.  Cause found, back to factory, developer seems to presume it's a waste of time - lock simulator to incident data - reproduced - developer looks very unwell, like he might throw up on his keyboard. "Fatal error" sometimes really is.  To "get through" my role was to pay extreme attention to what the users report.  They've already made sure they're not wasting our time and only reporting stressful situations.  I don't yet know if I'm suitable here yet.  I'm rather more apt at signal analysis.

Thank you Telesto for your kind remarks.  Everything that ever worked out was always due to that (in my experience).

The more work done, the less I write.  Context is important at this stage (imho).. . .so

I went into the Calc 5 cellar, using the Calc 5 stairs down, switching on the Calc 5 light to inspect the structure and what we "don't see".  It's different to regression testing.  Even if Calc 6 and 7 have new walls and floors above, checking the foundation and supporting structure is (sometimes) a good idea. 

@b  If you feel better/encouraged  (to see this work out) taking a look yourself, Windows LibreOffice versions are available without disturbing our usual install.  Should be fine on Windows 6.1.  Perhaps you know it. . .
https://dev-builds.libreoffice.org/si-gui/help/en.html
And download , make properties "executable", here
http://tdf.io/siguiexe
gives us multiple choice.

[The reason I'm still onboard - Canonical chose to put Ubuntu back with Gnome.  Rock solid.  Best year of the decade.  Thank you Michael and colleagues].

So, to make this long story short:

This bugreport is about a problem in LO 6.3 with the document attached in comment #1. It appears that this problem cannot be reproduced in recent supported LO versions. This means there are basically 2 possibilities:
* The problem has been already fixed. In that case it is enough to update your LO. If you want to stick with an older version, that's your choice, but if that version is by now unsupported (see https://wiki.documentfoundation.org/ReleasePlan), then it's that, unsupported. See https://wiki.documentfoundation.org/ReleasePlan#End-of-Life_Releases .
* The problem is hard to reproduce. That's unfortunate, but Calc is a complex piece of software and fixing a problem like this without sufficient information is bordering on the impossible. Similarly it's practically impossible to test all possible documents and corner cases, Calc already has a number of tests, but they can never cover everything. Therefore it is necessary to get some information about the problem, and at the very least a confirmation that the problem still exists. Therefore I'm setting this bugreport to NEEDINFO.

A large number of comments here are vague descriptions of problems with Calc calculating something incorrectly that may not have to do anything with this bugreport besides sharing the pattern of something not getting calculated correctly. Mixing several different problems in one bugreport only makes it more difficult and confusing to handle, and so they do not belong here. Please submit a separate bugreport for each separate issue. If there is a common theme to several problems, QA can create one meta bugreport to keep track of all those problems.

> * The problem is hard to reproduce. That's unfortunate, but Calc is a
> complex piece of software

Careful.  We know that, appreciate it and deploy it with much enthusiasm.  

> I'm setting this bugreport to NEEDINFO.

> A large number of comments here are vague descriptions of problems with Calc
> calculating something incorrectly that may not have to do anything with this
> bugreport besides sharing the pattern of something not getting calculated
> correctly. 

Incorrect.  Precise example given.  A Bug that prevents bug reporting is the subject.  

Mixing several different problems in one bugreport only makes it
> more difficult and confusing to handle, and so they do not belong here.

It clarifies (according to b, who saw the point . . .immediately).  "Sporadic fails" is the first point in the/his title.  Evidence belongs here for the moment - according to his (vast) user experience.

> Please submit a separate bugreport for each separate issue. If there is a
> common theme to several problems, QA can create one meta bugreport to keep
> track of all those problems.

Telesto and Buovjaga guide us already.  Good job!  A developer who knows/learns how a calculation can be decided/limited/damaged by the open application window size is welcome to comment/specify.  We know already to separate/submit a separate bug report at that moment.

Lubos; please confirm your role here.  If  (experienced) users consider Calc4 (b) or Calc5 (me) to share our data (internationally) in a stable fashion (and we do check every cell) it's time to involve developers who are bothered by that. . . .fact. Personally, I install and test Calc 7 (fresh) whenever I can.  Always hopeful for the best!  LibreOffice is!

I'll continue here for the moment, including more NEEDEDINFO because it encourages b, with some technical pertinence rather than the usual none-at-all.

Throwing out the developers for now.. until something reproducible appears..

You may continue here.. but devs like 'really' concrete issues.  

(In reply to matthewnote from comment #31)
> > * The problem is hard to reproduce. That's unfortunate, but Calc is a
> > complex piece of software
> 
> Careful.  We know that, appreciate it and deploy it with much enthusiasm.
> 
> > I'm setting this bugreport to NEEDINFO.
> 
> > A large number of comments here are vague descriptions of problems with Calc
> > calculating something incorrectly that may not have to do anything with this
> > bugreport besides sharing the pattern of something not getting calculated
> > correctly. 
> 
> Incorrect.  Precise example given.  A Bug that prevents bug reporting is the
> subject.  
> 
> Mixing several different problems in one bugreport only makes it
> > more difficult and confusing to handle, and so they do not belong here.
> 
> It clarifies (according to b, who saw the point . . .immediately). 
> "Sporadic fails" is the first point in the/his title.  Evidence belongs here
> for the moment - according to his (vast) user experience.
> 

Luboš Luňák is a developer who mostly handling this type of calculation issues.. However, as most developers.. the prefer clear specification of the issue. So something where they can work with.  Until then the are not supposed to be contacted (as general rule). Arguing with them pointless. And the don't figure causes.. The only want to now to route to current result and and the expected one.

This bug is ruined as bug report - dedicated to developers. The don't like long reads. And unclear steps. A certainly not both at the same time.. 

From QA level I would say, keeping this thing alive being fine.. until we actually catch something.. create fresh report with that.

@matthewnote@yahoo.co.uk, like your approach, have respect for your background and would really! really! really! like people like you to have more influence on the quality of LO, esp. calc, pls. stay with us even if you get the impression of some people here being a little ignorant, they are like they are ... not 'on purpose' but it evolves in long time fighting difficult problems ... 

parallel installs and the like ... at the moment i have about 23 different versions installed on my win-machine, spanning ver. 3.3 to 7.2, plus some AOO and some more on the linux machines ... in a way i know what i'm talking about, just as for others it's still difficult to produce stable faults with unstable programs, 

@Telesto, am i right? it's becomimg hot with a chance to put a hand on plenty of this 'non-reproducers' and you are cutting it down? pls. don't! 

it's two issues: 

1. i had some fails, reported one of it, c#1, it's hard to reproduce, :-( 

2. @matthew found something reproducible triggering miscalculations by being dependent on the actual view when distinct actions are performed? which potentially is the! reason why we often produce hard to reproduce fails, and he provided a sample for that, this sounds very! important and looks very promising to me, 

pls. handle appropriately, 

would suggest: a new bug for @matthews finding, and this one set to a new status like 'WAITINGFORAREPRODUCER',

> Related to steps to reproduce. I sometimes use a screen recorder (and
> shortcut OSD). Let it simply run while you're working. So there is a way to
> look back in time. Even slow down.

Hi b, hi Telesto.

Slow down.  Always works.  I'll be on Calc 7.1 RC1 on Ubuntu 20.04 and/or boot to Windows 10 Pro. Dual 4K (both 48") monitors.  The Intel drivers work.  Any size unhappy file accepted.

@Telesto.  From experience, which screen recorder is probable best candidate?  My intention is to learn it, try a small portion of the view (rather than full screen!).

see 

https://ask.libreoffice.org/en/question/285230/how-does-calc-load-file-into-memory-and-prepare-the-calculation-to-be-presented/ 

about similar observations and suspecting 'in' or 'out' contributing to the issue ...

I also have a private sheet with many formulas, where I do not recalculate certain specific fields correctly. This is very difficult to reproduce because it does not always occur.
As soon as I do ctrl-shift-F9 it will be updated.

Hello Telesto, "b"

Thanks to progress on Bug 86321 and VLB's file, it's possible to provide a "reproduceable sporadic fail".  I will contribute it (with on screen video), which might take one week.  It uses 7.2.0.0+ alpha (development) and is evident using Windows version with OpenCL on/off.   The "sporadic" is due to saves/reads in user/.../4/  (user profile area) which conflict with RAM work.  

I'll apply b's preference for a new Bug Report, labelling it as sporadic yet OpenCL dependent.   Of course there are possibly many other variations concerning threaded or Software Interpreter so (finally) it's only one example.

However, it is (as b suggests) difficult and very rare (to be able to reproduce).  Next post from me, the new Bug Report number.

(In reply to matthewnote from comment #39)
If you gonna attempt to reproduce.. you might try run a regular build with a debugger attached (windbg). Might throw some warning 

If want to deeper, you might use a debug build with debugger (windbg) attached. But well this probably causes a performance hit

Screencast showing 'proof' of it happen is nice to have, but well will not solve the issue, sadly. It's hideous problem; not easy to reproduce (as far I understand). So ideally we try get code pointer.. 

Another problem is that the 'last calculation' being stored in file. So on file open it shows sometimes the wrong stuff (if calculation was broken in older version, it shows still up as broken in newer versions; until recalculation being done) 

And as far I understand a recalculation being done 'selectively'. So not every change will trigger full recalculation all over all sheets. But only those 'marked dirty'. The dirty flag is needed to trigger recalculations. 

But that's my rudimental knowledge. Next we which engine is used. For more than 100 rows it will be multi-threaded. And when and how OpenCL being used, no clue. 

To give some lovely bunch of variables involved.

(In reply to Telesto from comment #40)
> (In reply to matthewnote from comment #39)
> If you gonna attempt to reproduce.. you might try run a regular build with a
> debugger attached (windbg). Might throw some warning 
> 
> So ideally we try get code pointer.. 
> 
> To give some lovely bunch of variables involved.

Thankyou.  Virtual studio etcetera ready.
Although the draft report  (using 7.2.0.0 Dev) doesn't need to toggle Software Interpreter, I'd prefer to know (when looking at older files that had SI history) . . .what's the SI status now?  Always on, integrated?  Depends?  Removed?  Secret toggle switch?  Not crucial to know, just preferable (from my point of view).

> 2. @matthew found something reproducible triggering miscalculations by being
> dependent on the actual view when distinct actions are performed? which
> potentially is the! reason why we often produce hard to reproduce fails, and
> he provided a sample for that, this sounds very! important and looks very
> promising to me, 
> 
> pls. handle appropriately, 
> 
> would suggest: a new bug for @matthews finding, and this one set to a new
> status like 'WAITINGFORAREPRODUCER',

Hi
I offer one, reproduceable using 7.2 and 7.3 alphas.  It uses charts to make it easier to follow (no screen recording).  The samplefile is from an engineer in reinforced steel structures and includes:

ROUND (and formats use that I don't generally learn)
OpenCL is involved in the problem
== operator is involved (even if only one unique cell by typing error)
The sporadic fail occurs or not depending on use of undo:delete by mouse
The sporadic fail is locked into the xml by saving
The errors in columns are also changed after User profile reset(a bit worse)

New Bug 142891

Thanks for reporting this issue.
Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.

Dear b.,

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

MassPing-NeedInfo-FollowUp