Description: AutoCalculate stops working when a new column is inserted in a LibreOffice 5.3.0.2 x64 (Windows 10)! To re-enable AutoCalculate, one must close and reopen the document. To confirm that it is a bug, this issue was discussed at https://ask.libreoffice.org/en/question/120044/autocalculate-stops-working-when-column-is-inserted/ and it has been confirmed to occur in version: 5.3.1.2, Build ID: 1:5.3.1-0ubuntu2 as well. Steps to Reproduce: 1. Start a new spreadsheet and confirm that AutoCalculate is enabled. 2. Type in a number in cell A1. 3. Type in another number in Cell B1. 4. In cell C1 insert the formula `=A1+B1`. 5. Confirm that the sum in C1 works correctly; try changing the values in A1 and B1 to verify that is recalculates correctly. 6. Now select column C and insert an empty column to its left, i.e. between B and C. 7. Edit the sum formula (which is now located) in cell D1 to `=A1+B1+C1`. 8. Now type in a number in cell C1. 9. Confirm that the sum in cell D1 is not updated correctly, despite the fact that AutoCalculate is enabled. Hard recalculation works. 10. Close and reopen the spreadsheet. 11. Finally, confirm that recalculate works correctly at all times, now that the spreadsheet was reopened. The bug has been confirmed by other users, the same behavior was observed for `=$A$1+$B$1+$C$1` on a version 5.3.1.2 (Build ID: 1:5.3.1-0ubuntu2) system. Actual Results: As described above, it was confirmed that AutoCalculate stops working when a new column is inserted, until the spreadsheet is closed and reopened. Expected Results: When AutoCalculate is enabled, (1) automatic recalculation should occur continuously; also, (2) there should be no difference in behavior before and after closing and reopening a spreadsheet. Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36
Confirmed with: Version: 5.3.0.0.beta2 (x64) Build ID: a7e30712ad6d8bc9286007b37aa581983e0caba3 CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; Locale: ru-RU (ru_RU); Calc: CL Version: 5.4.0.3 (x64) Build ID: 7556cbc6811c9d992f4064ab9287069087d7f62c CPU threads: 4; OS: Windows 6.19; UI render: default; Locale: ru-RU (ru_RU); Calc: group and also with current master. NOT reproducible with: Version: 5.2.5.1 (x64) Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22 CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Locale: ru-RU (ru_RU); Calc: group
/cygdrive/d/sources/bibisect-win32-5.3 $ git bisect log # bad: [a374222bc87bd9e75ea2f1ca45d189932a1967f8] source aa09fd58bd499a2a2c3a32c5f613892bad54076c # good: [defb73f1c6e2a66dbd21ba89e684f57427e8bc4b] source 5b168b3fa568e48e795234dc5fa454bf24c9805e git bisect start 'master' 'oldest' # bad: [0b4408f0568ad1da0797543c0ee2955c386267ee] source 8f7886c742cae5e012e52029c20925aa7b0fb6ea git bisect bad 0b4408f0568ad1da0797543c0ee2955c386267ee # good: [16b554fa5f3a3d2723e78d894d644ac9303a7991] source 50a6f3d51f32e7176c3b2d036c60bb48d51d6a1a git bisect good 16b554fa5f3a3d2723e78d894d644ac9303a7991 # bad: [1d5414e6dc71de667a2e7457f4a169c3575a4a70] source f16c803e7f5186632adeffc2dad579cf2c720b15 git bisect bad 1d5414e6dc71de667a2e7457f4a169c3575a4a70 # bad: [5942c07976de306d75affaeadcde1f3697bc4fd3] source 4fc3c8a3df485f6dccdcb2c51c6266fbd0dace3e git bisect bad 5942c07976de306d75affaeadcde1f3697bc4fd3 # bad: [88223d9fd206d9710ad6c3bde7ad927e19f333cd] source aaa5a098f4a3644a6f78cafa1c86f1db12c4f6ed git bisect bad 88223d9fd206d9710ad6c3bde7ad927e19f333cd # bad: [a0052c85b4b4c856a5c028bd872b80df429343f2] source 8babcb7e669ce2f752945c1f89647b759ec568d3 git bisect bad a0052c85b4b4c856a5c028bd872b80df429343f2 # good: [0059e4b01ce8371b8a9716900ffb9bf69376a603] source 3d70765218986abba8b6d7c8e3cadd83a62ee035 git bisect good 0059e4b01ce8371b8a9716900ffb9bf69376a603 # good: [33f290342aeaa41e30876a1d1ea4f986b09ab344] source 01767d5d9d43d4ef4bd948f00a1cd2c113714b20 git bisect good 33f290342aeaa41e30876a1d1ea4f986b09ab344 # good: [3a47c5610f4d69690600e4becadab0737f1fd47c] source bf5110d0704ba95d9a080f7dea64162c346317a9 git bisect good 3a47c5610f4d69690600e4becadab0737f1fd47c # bad: [8a5470a28b8289c824ed171b6af852ba58801693] source d03d7a79406c4bd25a776a4f7247588662b121b0 git bisect bad 8a5470a28b8289c824ed171b6af852ba58801693 # good: [54b1823ada6c260b0fc5f0602d4c381331d8fafd] source b569bc15c6236a3e0f9f4ef6f138dbf4a22cb8d1 git bisect good 54b1823ada6c260b0fc5f0602d4c381331d8fafd # good: [c99ca3a12af0a7d7d9ee4881a5ac34912ef5d9e4] source 0473f6fc0445272b1e9d01ca9166d4fae58a5a56 git bisect good c99ca3a12af0a7d7d9ee4881a5ac34912ef5d9e4 # bad: [cebfa472b43a46a9231675da7a202db74a9bba66] source e57a5905fb2975307af654710430d0a876dbd061 git bisect bad cebfa472b43a46a9231675da7a202db74a9bba66 # first bad commit: [cebfa472b43a46a9231675da7a202db74a9bba66] source e57a5905fb2975307af654710430d0a876dbd061 https://cgit.freedesktop.org/libreoffice/core/commit/?id=e57a5905fb2975307af654710430d0a876dbd061 author Kohei Yoshida <kohei.yoshida@gmail.com> 2016-07-17 00:01:48 (GMT) committer Kohei Yoshida <libreoffice@kohei.us> 2016-07-17 13:00:36 (GMT) commit e57a5905fb2975307af654710430d0a876dbd061 tree 364527c7b09f9704e6c6ece7ca473c9e2030177f parent 0473f6fc0445272b1e9d01ca9166d4fae58a5a56 Use mdds' event callback to count formula blocks in each column. And use it to speed up certain formula related operations.
*** Bug 112640 has been marked as a duplicate of this bug. ***
This is a VERY ANNOYING bug. I suggest to change importance to critical. I see this same bug in every update for years. This HUGE BUG becomes more relevant because if you save a document in native ods format with inconsistent values in some cells, as described in the procedure, recalc does not trigger on loading. ------------------------------------- Confirmed with Version: 5.4.2.2 (x64) Build ID: 22b09f6418e8c2d508a9eaf86b2399209b0990f4 Thread CPU: 4; SO: Windows 6.1; Resa interfaccia: predefinito; Versione locale: it-IT (it_IT); Calc: single
(In reply to prossin from comment #4) Please don't modify issue fields unless you understand their meaning. That requires at least trying to read their names carefully (e.g., the Version field has "earliest affected" clarification right below for a reason). You couldn't have this same bug for years, because it was introduced in 5.3, which was released in the beginning of this year. Yes, it is rather important, but it doesn't qualify for critical - the importance categories have the project's established meanings, not necessarily what natural language implies. I raise the importance, though, because this is a regression.
(In reply to Mike Kaganski from comment #5) > (In reply to prossin from comment #4) > > Please don't modify issue fields unless you understand their meaning. That > requires at least trying to read their names carefully (e.g., the Version > field has "earliest affected" clarification right below for a reason). > > You couldn't have this same bug for years, because it was introduced in 5.3, > which was released in the beginning of this year. Yes, it is rather > important, but it doesn't qualify for critical - the importance categories > have the project's established meanings, not necessarily what natural > language implies. > > I raise the importance, though, because this is a regression. I apologize for the mistakes you pointed me out. You're right, this is a fairly new bug but very often I have problems because recalc does not trigger on loading. If something goes wrong with editing (and strange things could happens also with older versions, believe me) when I open a document I cannot be 100% sure that formulas are calculated correctly. So I don't trust LibreOffice Calc anymore from this point of view. It's a pity!
I have to also add, how can a calc program not auto update its columns/rows when one is added or deleted, surely this is the CORNER stone of spreadsheets?? UPDATE UPDATE UPDATE. I may have to go back to Excel, as I cannot have unreliable data calcs.
Investigating.
Minimal reproducer: * A1: 1 * B1: =A1 * insert column left of B * edit formula cell of then C1 to =A1+1 * change value in A1 => no recalc in C1 Does not occur if before inserting the column there's a formula cell in column C (i.e. the column right to the one of which the formula cell is edited).
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=423df1fa929784c14e3a133c06468589fe9269cd Resolves: tdf#111428 implement CellStoreEvent::swap() in ScColumn::SwapCol() It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/44210 for 5-4
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=782f34ae2f214674fc871ece348ba5dd11c4a8d6 Unit test for CellStoreEvent::swap() in ScColumn::SwapCol(), tdf#111428 It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=911e2aff3cc37cb7410292728ffea05fffbfb0b3 Resolves: tdf#111428 swap (only) ScColumn::mnBlkCountFormula It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f80ec94e75a9257504135bc32793c5eb37914bbe&h=libreoffice-5-4-3 Resolves: tdf#111428 swap ScColumn::mnBlkCountFormula It will be available in 5.4.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=76c54edc8ee972c63da8ed1f5d6ab75ea552da46&h=libreoffice-5-4 Resolves: tdf#111428 swap ScColumn::mnBlkCountFormula It will be available in 5.4.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Tested using comment 9 and its working Version: 6.0.0.0.alpha1+ Build ID: e3530d2c9d5dc98c6bacf243c163d651624e1ba6 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: gtk3; Locale: nl-BE (en_US.UTF-8); Calc: group
Version 5.4.3.2 works fine, too. The only problem I see is that if I open a document .ods with auto recalc 'broken' because saved with a previous 'buggy' version, the loading of the file does not trigger recalc. IMHO, the problem is mostly not related to this bug, but to the lack of the option 'recalc on loading' (not an option for ods created by LibreOffice). Versione: 5.4.3.2 (x64) Build ID: 92a7159f7e4af62137622921e809f8546db437e5 Thread CPU: 4; SO: Windows 6.1; Resa interfaccia: predefinito; Versione locale: it-IT (it_IT); Calc: single
(In reply to prossin from comment #17) > lack of the option 'recalc on loading' (not an option > for ods created by LibreOffice). You can always do a manual hard recalc (Shift+Ctrl+F9) of the entire document if you suspect you encountered a faulty saved document.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=7f1297d9b4f449eb9ada8008fb21b7046d1a8f19&h=libreoffice-5-3 Resolves: tdf#111428 swap ScColumn::mnBlkCountFormula It will be available in 5.3.8. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
*** Bug 114143 has been marked as a duplicate of this bug. ***
hi @all, would you mind recheck script below and reopen this bug, or shall i file a new one? issue: if you construct the conditions a little more complex autocalculation is blocked again, 'little stressscript for / against autocalculate problems with shared formulae, A1: '2' B1: '3' C1: '=SUM(A1:B1)' copy A1:C1 paste to A1:C10 B:B sheet - insert - column - before B3:B8 delete cells shift left D:D sheet - insert - column - before C2: delete content E2: watch unchanged and wrong '5' ctrl-shift-F9: problem recalculated '2' i think we / you need a somewhat more fundamental solution for theese issues than just patching the occurences shown up so far ... ??? most recent version tested, still buggy, older versions fail too: 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: reg. b.
Steps from description works well on Version: 6.4.0.0.beta1 Build ID: 4d7e5b0c40ed843384704eca3ce21981d4e98920 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: ro-RO (ro_RO.UTF-8); UI-Language: en-US Calc: threaded
Created attachment 156427 [details] 6_5_0_0_a1_autocalc_error_2, macro to check hello @BogdanB, 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 m.a.riosv in https://bugs.documentfoundation.org/show_bug.cgi?id=129255#c1, 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.
Created attachment 156428 [details] screnshot I tried steps from comment 21 I have a 2 in E2. Without pressing anything special. Version: 6.5.0.0.alpha0+ Build ID: 5030be4e85179147476b1e441eb618fb6ed58235 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-11-28_20:14:48 Locale: ro-RO (ro_RO.UTF-8); UI-Language: en-US Calc: threaded
Created attachment 156455 [details] 6_5_0_0_a1_autocalc_error_gl_off_cl_off_threaded_buggy thank you very much, compare my screenshot, you wouldn't like to work with a program producing such results :-(
Please be sure to have: Data - Calculate - Autocalculate checked.
One thing you could do it is to have a clean profile: Help - Restart in Safe Mode - Restart - Reset to factory settings - and check that 2 boxes: Reset settings and Reset entire user profile. And I hope that with a new user profile and Autocalculate activated everything is now ok.
@BogdanB c#26, c#27 hello, yes, i did that and checked it multiple times, even complete system cleanup and deleting everything than a plaintext copy of my macros and keyboard customization, installed different versions of LO stable, beta and dev versions ... even switching my 'locale' to en, es, ro, no chance, all tests failed, i was already nearly desperate to be the only idiot who should have such errors, but there are other testers who see problems with similar tasks, e.g. https://bugs.documentfoundation.org/show_bug.cgi?id=129333#c1 and https://bugs.documentfoundation.org/show_bug.cgi?id=129199#c0 thus i am convinced that autocalculate is still flawed, and it is difficult to work out the special circumstances in which the flaw shows up, probably extremely difficult because many aspects of a fundamental error are obscured by interim patches, and so both: - in most situations do not occur, and - a correction of the fundamental error may produce erroneous results caused by the patches ... but these are only assumptions ... reg. b.
Can you PLEASE submit a new bug for the new findings and attach a simple reproducer and step by step instructions, instead of commenting on an old fixed bug? And please try to be concise and not too verbose, remember that someone willing to fix things will have to read it and prose and assumptions are just not helpful. Thank you.
@Eike Rathke I think that b. on comment 21, 25 and 28 has been quite accurate and tried to explain all he did in order to help finding and correcting the bug. Contribute of anyone is always valuable and should never be discouraged. IMHO! Anyway, nothing's new... it is almost the same bug as 2 years ago! I found a shorter reproducer: A1: '2' B1: '=SUM(A1:A1)' copy A1:B1 paste A2:B2 B:B sheet - insert - column - before Now recalculate is stuck and LibreOffice does not recalculate B1/B2 anymore! It is curious to note that if you edit a formula doing nothing (keeping it exactly as it is) recalculate does return to work again for all the cells. I think that column add/remove feature is a mess, I saw the C++ code and it does not appear as a well written code so when something is fixed some other new bugs come to life.
Seems no one is taking me serious. I now create bug 129396 for this. Please refrain from posting assumptions and accusations. Thank you. (In reply to prossin from comment #30) > I think that column add/remove feature is a mess, I saw the C++ code and it > does not appear as a well written code so when something is fixed some other > new bugs come to life. If you are able to judge that we are eagerly awaiting your fixes.
I overlooked in the jungle of prose comments that meanwhile bug 129333 was created.
(In reply to prossin from comment #30) > @Eike Rathke > > I think that b. on comment 21, 25 and 28 has been quite accurate and tried > to explain all he did in order to help finding and correcting the bug. > Contribute of anyone is always valuable and should never be discouraged. > IMHO! > > Anyway, nothing's new... it is almost the same bug as 2 years ago! > I found a shorter reproducer: > > A1: '2' > B1: '=SUM(A1:A1)' > copy A1:B1 > paste A2:B2 > B:B sheet - insert - column - before Note for the future: Next time you find a bug, please fill a new report as Eike mentioned in comment 29. There's no point in commenting a bug that was fixed 2 years ago, even if both bugs are similar.
(In reply to Eike Rathke from comment #31) I am not judging anything and I really appreciate all the hard work you do on this project. Sorry if I was rude, that was not my intent, believe me! I think that what drive many users (me too) to be so critic about these auto recalc flaws is the fact that even the basic user must rely on calculations of a tool born to make calcs. Of course bugs can occur but in this case the user have no chance to understand what happens. I would greatly prefer a crash instead of getting wrong results, don't you? These are my proposals (I know this is the wrong place, I will put them later in the right one): 1) Rise the priority of bugs that can lead to wrong calculations since its a "broken core functionality" 2) Try to inform the user that the cell calculation will not be triggered by any event. (not easy... I have no ideas) 3) Add an option that forces a complete sheet recalc in case any cell changes its content. (I noticed that I can always rely on results by forcing a full recalc) I really appreciate any opinion!
@prossin: thumbs up! polite writing would work better but regarding 'the issue' you are absolutely right @all: it's a 'bug-field' undermining the thrustworthiness of the program since years (5 as of now?) and not yet fixed, see: https://bugs.documentfoundation.org/show_bug.cgi?id=129541