Description: sorry @all, I have an unusual, and difficult, suggestion to get a handle on an annoying problem. Situation: since about five years (4.1 -> 4.2) LO calc suffers from errors in the interaction between shared formulas and autocalculate. Neither the developers nor the testers are able to think through the complexity of relationships and dependencies that simple users can construct in sheets. Users construct complex sheets, and are not able to completely control their results. Users are not aware that displayed results may be wrong. Users need a reliable program, they need correct results. Developers and Testers must (also) be able to dedicate themselves to other tasks. The processing of the complex 'shared formula ./. autocalculate' has brought over several years only success messages for individual aspects being fixed, the basic problem still remains. See: https://bugs.documentfoundation.org/show_bug.cgi?id=111428#c21 I am not close enough to programming to decide whether the problem can be solved with the current tools, my mathematical knowledge and my knowledge about the structures in program and data are not enough to prove a theoretical solvability. I am close enough to 'testing' to think that it is largely impossible to prove the correctness of all possible calculations by testing or to present them as sufficiently 'probable'. Imho that the malfunctions resulting from this bug-complex are serious and undermine the trustworthiness of the program. Hence the following suggestion: A 'task-force' of developers and testers is formed to handle this problem with priority. The problem is approached from another side, it is started from the last correct version and checked how complex it is to remove the special handling of 'shared formulae' from calc. (Neutralizing the original implementation, working through and if necessary neutralizing all patches based on it). If enough manpower is available, two groups can work in opposite directions, one as above and the second one goes 'backwards' through all patches that refer to autocalculate and shared formulae and tries to show their correctness. It may be that in this work the actual error is noticed and can be fixed. If it is not found - hopefully - a version without 'shared formulas' will be created that: 1) maybe works a little slower, but 2) provides correct results. 1. is probably to be overcome on the basis of the efficiency of current hardware, 2. is important! I think I'm talking about one to two man-years of development here, that's 'not nice' :-( ... but the alternative to keep 'poking in the fog' and hope that by chance you will find the real flaw among all the patches in the meantime is: - uncertain in the result, - Also expensive, so far certainly more than two man-years have been consumed for this complex, - also costly, after finding the error - at an uncertain point in time - all patches installed afterwards - based on it - would have to be checked, There might also be a third group trying to correct all the bugs found in the first affected version, maybe you'll find a solution - the right one - quickly. All in all, i would like to raise awareness of the problem and contribute to the solution, rather than sticking to the role 'bad guy' - who still finds bugs - Steps to Reproduce: See: https://bugs.documentfoundation.org/show_bug.cgi?id=111428#c21 it's only one flavour of this problem, you easily get plenty similar once you raise the complexity in a sheet. Actual Results: cells unintentionally excluded form autocalculate, Expected Results: korrekt results in all cells Reproducible: Sometimes User Profile Reset: No Additional Info: Version: 6.5.0.0.alpha0+ (x64) Build ID: 9ab43aebad67383057d2cc3f754ce2193fa78b4e CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: GL; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc:
Whit step in https://bugs.documentfoundation.org/show_bug.cgi?id=111428#c21 Version: 6.5.0.0.alpha0+ (x64) Build ID: 60e8941fd581bb06cbf6be62edb8c387e7c07812 CPU threads: 4; OS: Windows 10.0 Build 19035; UI render: default; VCL: win; Locale: es-ES (es_ES); UI-Language: en-US Calc: CL works fine for me, value it's updated
Created attachment 156424 [details] 6_5_0_0_a1_autocalc_error_2, macro to check hello @m.a.riosv, first: thanks for your help, it's not confirming the bug but narrowing the space he can survive, estimating from my tests, the results of you and those of BogdanB in https://bugs.documentfoundation.org/show_bug.cgi?id=111428#c22, another machine with linux here also failing, and a friend with a 4-core CPU also failing ... there's hardly more than three possibilities: 1) the fault is dependent on a complex network of basis, versions and settings, thus appearing in some situations while not in very similar, 2) our systems here are pested with a very special 'turn-off-autocalculate-in-rare-cases' virus which didn't make it to you, i see no sense in such a virus thus i doubt it's written, 3) you did something 'special' when keying in the script to test? 1) would say 'much work', 2) i don't believe in, 3) i'd like to rule out. i kindly ask for your help, the attached file has a macro which does only key in the values and formulas as i did it for my tests. can you please - have a look inside that it wouldn't harm your system (it's only cursor movements, put 2 in A1 and so on, until deleting C2) and then let it run, and provide a screenshot of the result. many thanks in advance ... b.
this error - that from #111428 and with the script / macro from comment #2 - isn't anymore reproducible for me at at the moment. it's one of two (yet) errors which disappeared, i can't say if reg. newly installing all programs i test with in their historical order, or reg. install of 6.4.0.1, or whatever ... ??? but there's at least one other bug - not new but still there - which lets me say: 'autocalculate - shared formulas' isn't clean yet. pls. look at: https://bugs.documentfoundation.org/show_bug.cgi?id=118843 https://bugs.documentfoundation.org/show_bug.cgi?id=128975 https://bugs.documentfoundation.org/show_bug.cgi?id=129541, thus i repeat: the program needs something better than patching the errors which make it through to users notice, we are already in a range where it's very difficult and time consuming to work through complex sheets with complex error descriptions, but we still have errors producing wrong results for users. any proposal how to get rid of that is highly appreciated. reg. b.
So are you saying this particular report can be closed?
@Buovjaga: close? imho not, it's about how to get rid of 'shared formula - autocalculate' problems in general, at the moment they get fixed one by one as local phenomena are spotted, but that takes long time, and will most likely never get them all because we can never fully test the complexity of constructions users will produce in sheets, at the moment 118843, 124577, 128975 (and 129541) are unfixed ...
(In reply to b. from comment #5) > @Buovjaga: > > close? imho not, it's about how to get rid of 'shared formula - > autocalculate' problems in general, > > at the moment they get fixed one by one as local phenomena are spotted, but > that takes long time, and will most likely never get them all because we can > never fully test the complexity of constructions users will produce in > sheets, > > at the moment 118843, 124577, 128975 (and 129541) are unfixed ... Ok, you are talking about a 'META' report, which already exists for AutoCalculate: bug 109324 Even this very report is in it. I will close this and add the ones you mentioned into it. You can add yourself to the Cc of the Calculate META, if you want.
hello @Buovjaga, adding to meta - ok, closing invalid - imho not ok: it's not about a special calculation going wrong, but about a special aspect of handling - autocalculate - shared formulae - complexity - handling only spotted flaws - being weak in producing quality ... imho not invalid but quite important, but you are 'better involved'? you may decide ... reg. b.
(In reply to b. from comment #7) > hello @Buovjaga, > > adding to meta - ok, > > closing invalid - imho not ok: it's not about a special calculation going > wrong, but about a special aspect of handling - autocalculate - shared > formulae - complexity - handling only spotted flaws - being weak in > producing quality ... imho not invalid but quite important, but you are > 'better involved'? you may decide ... > > reg. > > b. INVALID is an arbitrary status. I chose it because I have to choose something and it is what we typically use, when a topic is moved elsewhere (without it being a duplicate report specifically).