Description: i observed some funny malfunctions, suspect them being results of malfunctioning 'autocalculate', (strg-shift-F9 corrects them) got a hint on #121002, tested the 'fix' mentioned there with ver. dev 6.2.2.0.0, got funny results again, (sum of a range not updated after deleting the content of one of the cells in the calculated range), i tried to produce a script to reproduce, got one for moving cells, see in 'steps to reproduce', acc. to my testing still buggy in ver. dev 6.2.2.0.0 a long description and links to other errors which i suspect being related to this bug you can find in https://ask.libreoffice.org/en/question/184419/strange-wrong-results-in-lo-calc-autocalculate-broken/ Steps to Reproduce: 0. maybee simpler sheets fail too, i had no time to check, 1. create a new sheet, 2. check that autocalculate is on, 3. enter '77,05' in C2, C3, C4 (without the quotation marks), 4. enter '1,1' in D1 (without the quotation marks), 5. enter '1,2' in E1 (without the quotation marks), 6. enter formula '=$C2*A2*D$1' in D2 (without quotation marks) 7. copy this formula to E2 8. mark cells D2 and E2 and drag down - expand - the formula to D2:E4, 9. enter '1' in A2, A3, A4 (without the quotation marks), 10. observe results in D2:D4, 11. select cells A2 and A3 and move them with the mouse to B2:B3, 12. check the results in E2:E3, in my case E3 gave a result, but E2 remained '0' despite that the formula and referenced cells should produce the same result as E3, 13. triggering a 'hard recalc' with 'strg-shift-F9' changes the value shown in E2 from '0' to '92,46', that looks better, 14. from other research i suspect something fundamentally broken with autocalculate since ver. 4.2.2 and request in depth research and fixing, it can't be tolerated that a productive software produces results 'by chance', 15. frequently pressing 'ctrl-shift-F9' may help, but i'd prefer a functioning function 'autocalculate' as it was in versions up to 4.1.6.2, Actual Results: E2 is not! updated as expected, the result shown in E2: '0' and the formula in the cell: '=$C2*B2*E$1' do not match! E3 is! updated, E2 is! updated after pressing ctrl-shift-F9 Expected Results: E2 should be updated by autocalculate in the same manner as E3 when autocalculate is on cell E2 should always! show a value that matches the formula in the cell, Reproducible: Sometimes User Profile Reset: No Additional Info: i think this bug is one of a group of similar malfunctions based on one fundamental fault. it is severe and should not! be plastered with something that corrects just this occurence, instead someone should try to find and correct the fundamental error. hardware all: i couldn't test on 'all' systems, other reports reg. similar malfunctions let me think the fault is hardware independent, OS all: same as above, happens sometimes: the bug shows up in random rare cases, the script i provided is somewhat 'stable' in failing latest tested version: Version: 6.2.2.0.0+ (x64) Build ID: f184af314ec43fbac5bb22cd3ebc09ccdf865a7c CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; TinderBox: Win-x86_64@42, Branch:libreoffice-6-2, Time: 2019-02-24_01:44:59 Locale: de-DE (de_DE); UI-Language: en-US Calc: threaded See Log: f184af314ec43fbac5bb22cd3ebc09ccdf865a7c first version i found complaints over similar problems: 4.2, i tested: - 4.1.6.2 being free of all malfunctions i found reported on the web, - 4.2.0.1 changing the behaviour how to adopt formulas with references to ranges when cells within the range are moved, - many inbetween, x86 as well as x64, all having randomly inconsistent behaviour reg. what and when is to be 'autocalculated' on deletion, move or undos in referenced ranges, - the ocurrence of the errors is unpredictible, mostly the calculations are correct, the errors show up in random places, random situations and random times, it's difficult to catch one reproducible, - the malfunction is somehow dependent on the place in the sheet, if you copy the range A1:G4 produced with the script above to another place in the sheet - e.g. A6 or I1 - the results there are correct, in my case E2 still shows '0' despite plenty cells in the sheet have been altered and autocalculate should! have been triggered! user profile: i'd reset once when testing on version 5.4.X, no change, opengl: the many occurences reported on the web make me believe it's a fundamental fault in design, openglviewer is announced to be 'not signed', at the moment i'd like to avoid treating my system with more unsigned software - i think the bug isn't just a problem in view, if the affected cells are used in other calculations the results there become buggy too, you can test in the script provided, entering '=E2*2' in G2 will show '0',
Created attachment 149604 [details] demo drag needs hard recalc
reproducible with Version: 6.1.5.2 (x64) Build ID: 90f8dcf33c87b3705e78202e3df5142b201bd805 CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: de-DE (de_DE); Calc: - open attached spreadsheet - 11. select cells A2 and A3 and move them with the mouse to B2:B3
IMHO this could be a dup of: After cut/paste, cells with absolute references are not recalculated; displayed results are wrong until manual recalc performed https://bugs.documentfoundation.org/show_bug.cgi?id=120013 cause it recalcs if one removes the '$' from formula's, e.g.: E2: =C2*B2*E1 E3: =C3*B3*E1 E4: =C4*B4*E1
imho: - the disbehaviour is not! a problem of hard links ('$-references'), E3 works while E2 fails, - not showing up in sheets with minor changes is just an effect of occurring only under rare special circumstances, - the bug is not a dup of 120013, it's another result of the same basic fault, - as plenty other bugs are ... and the basic fault is 'when and why' autocalculate is triggered or not, specially why, under some rare circumstances, some cells are omitted, workaround #0: warn all users that results 'may' be erroneous, workaround #1: frequently press 'ctrl-shift-F9', workaround #2: create an easier keyboard shortcut for that, workaround #3: create a time triggered makro for that, workaround #4: implement something similar to #3 in the code, - that might be an option while the basic fault isn't found - - be sure to make that dependent of 'autocalculate' being activated - recommended solution: find out why sometimes some cells are omitted from autocalculate and correct that remove all workarounds, warn all users that older results 'may' be erroneous, b.
i'd suggest raising the importance, randomly producing wrong results hidden anywhere in a sheet is an absolute no-go b.
Created attachment 149641 [details] after_move_calculate_of_e3_still_fails
Created attachment 149642 [details] after_undo_calculate_of_e4_and_e5_still_fail
Created attachment 149643 [details] after_hard_recalc_e3_still_fails_autocalculating
impressions from playing with ver 6.2.2.0.0 2019-02-17: some things are changing? save and reload gives corrected - calculated - results, cells whose formulas were changed by a move become reset to their original references by 'undo', but some of the fundamental problem is still present: the upper right cell of a column affected by a move is excluded from autocalculate, see screenshot 'after_move_calculate_of_e3_still_fails.jpg', this is kept after correcting it's value with a hard recalc, see screenshot 'after_hard_recalc_e3_still_fails_autocalculating.jpg', D3 reacts to the change in B3 while E3 doesn't, after undo of the move the other affected cells fail, see screenshot 'after_undo_calculate_of_e4_and_e5_still_fail.jpg', all above errors can be corrected by ctrl-shift-F9, care not pressing 'alt' beforehand, everything works much better when you insert blank rows between each row with data, (i suspect this 'breaks' the 'shared formulas' apart?) funnily it starts misbehaving again when you delete the inserted rows, (shared formulas 'reconnected'?) my three cents: it's a fundamental risk that any cells can be excluded from autocalculate in any way, something is wrong - inconsistent - in the design of the handling of cells with similar formulas, both together produce problems
>steps to reproduce: >- open attached spreadsheet >- 11. select cells A2 and A3 and move them with the mouse to B2:B3 seems to have started with: $ git bisect bad b8882ea373107ea18035db0a5a346c496ab59865 is the first bad commit commit b8882ea373107ea18035db0a5a346c496ab59865 Author: Norbert Thiebaud <nthiebaud@gmail.com> Date: Wed Oct 19 19:49:31 2016 -0700 source dfa92e106f7eaa8c3fc6cda034001197ecc53a8f source dfa92e106f7eaa8c3fc6cda034001197ecc53a8f commit dfa92e106f7eaa8c3fc6cda034001197ecc53a8f [log] author Eike Rathke <erack@redhat.com> Wed Oct 19 23:04:34 2016 +0200 committer Eike Rathke <erack@redhat.com>Wed Oct 19 23:05:44 2016 +0200 tree 9c144af03922e9dda3e897c7de496c836f663069 parent b420a6ab0729530df6ff95c41d24673b5399ceae [diff] Resolves: tdf#97968 adjust references during Cut&Paste of formula groups ... and split groups for cases where references point outside or into the moved range. Change-Id: Iab799e94eed1677f266413b6304651ac4d330e95 sc/source/core/data/column.cxx[diff] sc/source/core/tool/token.cxx[diff] 2 files changed :040000 040000 682dd05a6e6bfceac682aebec55d991e6c9ccff0 79f6ee55d2c320a77335367cfbc5704b33b72054 M instdir /cygdrive/d/sources/bibisect/bibisect-win32-5.3 $ git bisect log # bad: [a374222bc87bd9e75ea2f1ca45d189932a1967f8] source aa09fd58bd499a2a2c3a32c5f613892bad54076c # good: [defb73f1c6e2a66dbd21ba89e684f57427e8bc4b] source 5b168b3fa568e48e795234dc5fa454bf24c9805e git bisect start 'master' 'oldest' # good: [0b4408f0568ad1da0797543c0ee2955c386267ee] source 8f7886c742cae5e012e52029c20925aa7b0fb6ea git bisect good 0b4408f0568ad1da0797543c0ee2955c386267ee # good: [f8b90d89a8a858c3e0ce171f82ecb20b3ea80098] source 75239b77139434db9be5e0e7e133e3661c5404b0 git bisect good f8b90d89a8a858c3e0ce171f82ecb20b3ea80098 # bad: [7a279eab384549b23a159352c7308486555e0401] source 5e416099f088a2f8a8980e08e3d5b731da0a6d9c git bisect bad 7a279eab384549b23a159352c7308486555e0401 # bad: [a3f5fe1a4c6eabca0604654b0f9cb40ad59f8bf8] source 4814650c5021b72c81b4079f712a4c1baae0088b git bisect bad a3f5fe1a4c6eabca0604654b0f9cb40ad59f8bf8 # bad: [c8e913512cc1c29d71551f9a857ec2c3fec86026] source 9b09a217c79e8a35fc4de54c89ef49fbf8f72752 git bisect bad c8e913512cc1c29d71551f9a857ec2c3fec86026 # bad: [4d423d2f65e028061f5a29c8d1c9e184e00426f5] source 36bafd3d4ad7fa75649eeab0c9cd1b3d6f53d8e8 git bisect bad 4d423d2f65e028061f5a29c8d1c9e184e00426f5 # bad: [c760401b00cf9641968a38e83864bfd5ac6a19b4] source 52350c15b37573e160f25d39565f577fc7189955 git bisect bad c760401b00cf9641968a38e83864bfd5ac6a19b4 # bad: [94e05672d9a114c7ea372a90a8601e4a79eae1d5] source e39983fdafa8e23e784818c124f4de5192ef1bb0 git bisect bad 94e05672d9a114c7ea372a90a8601e4a79eae1d5 # bad: [63df8fc08ef17ccb6dac809020a531a83d558c1f] source 40fc2c1a0d2ebdf47131651045107c9d5abb850d git bisect bad 63df8fc08ef17ccb6dac809020a531a83d558c1f # bad: [462ebed11e033e5183d9a21915a7bc6574cf690e] source 5f01b29876da20299326b466d9596c4121ed2dec git bisect bad 462ebed11e033e5183d9a21915a7bc6574cf690e # good: [63c6ab1bc6fd6a3a22b8cb6e52a73bec7798b680] source 978d4c11979eca54d84463b58394b421de2d4da6 git bisect good 63c6ab1bc6fd6a3a22b8cb6e52a73bec7798b680 # good: [d1526e1e476bba2d3ee7d18a0e39b6375329d8fc] source b420a6ab0729530df6ff95c41d24673b5399ceae git bisect good d1526e1e476bba2d3ee7d18a0e39b6375329d8fc # bad: [b8882ea373107ea18035db0a5a346c496ab59865] source dfa92e106f7eaa8c3fc6cda034001197ecc53a8f git bisect bad b8882ea373107ea18035db0a5a346c496ab59865 # first bad commit: [b8882ea373107ea18035db0a5a346c496ab59865] source dfa92e106f7eaa8c3fc6cda034001197ecc53a8f
Long story short: 1. in A1 enter =B1 2. copy cell A1 to clipboard 3. select A2:A3 4. paste 5. enter value 1 in C1:C2 6. select and cut C1:C2 7. paste on B1 (fills B1:B2) => A1 not recalculated, A2 is recalculated. A1 not notified when B1 is changed. Fails only when pasting in the same row where cut. Does not fail when pasted on B2, i.e. row not the same. Does not fail when entering value 1 in C2:C3 and cutting that and pasting onto either B1 or B2. Does not fail when cutting only C1 and pasting on B1. Does not fail when cutting C1:C3 and pasting on B1. Does not fail repeatedly, i.e. after step 7 enter value 2 in C1:C2 and cut and paste on B1 => A1:A2 display 2, A1 is notified of changes in B1.
@Eike Rathke, thks for taking this, imho it's not one special fault, but one of plenty symptoms of: - cells can be excluded from autocalculate in some way, that shouldn't be possible, it violates the definition of autocalculate, - above does! happen quite often somewhere around shared formulas ... - formulae referencing ranges are likely affected more often, but the problem is not limited to them, reg. b.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/7fdc5df36f5b50e0629405a47ff3d5765fcfeb93%5E%21 Resolves: tdf#123714 tdf#123736 all split formula groups; tdf#120013 related It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
i can not longer reproduce this: >- open attached spreadsheet >- 11. select cells A2 and A3 and move them with the mouse to B2:B3 with my developer build https://gerrit.libreoffice.org/plugins/gitiles/core/+log/a283db9b6553b232a29560dcc427329e5246f0ca Version: 6.3.0.0.alpha0+ (x64) Build ID: a283db9b6553b232a29560dcc427329e5246f0ca CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: threaded -> cell E2 now updates to 92,46 after moving
Created attachment 149850 [details] 6_3_0_0_alpha_0_+_still_buggy_01 ok, somebody changed the title i had given to the bug, so i cannot check wether i missed the fundamental point ... sorry if i did ... of course i didn't want only this one special calculation 'patched', i wanted the bug fixed in general, as i suspect it's producing plenty similar symptoms - errors if you like - see attached screenshot, C3 not autocalculated, or try script: "1" in A1:B4, for i=1 to 4: "=SUM(Ai:Bi)" in Ci, move B1:B2 to B3:B4, move B3:B4 to B1:B2, C3 and C4 not autocalculated, 'fixable' with forced recalc, but C3 and C4 still excluded from autocalculate ... version tested: Version: 6.3.0.0.alpha0+ (x64) Build ID: fe632c86aa250bb355a59ce6acf4dd75eae7afe0 CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; TinderBox: Win-x86_64@42, Branch:master, Time: 2019-03-09_03:34:13 Locale: de-DE (de_DE); UI-Language: en-US Calc: threaded reg. b.
needless to say, but for completeness of file / info: misbehaviour of comment #15 is not! reproducible with ver. 4.1.6.2, up to now i couldn't find any! of the bugs regarding autocalculate and shared formulas reproducible in that version, thus (i think) it is! possible to handle complex things like autocalculate and shared formulas together in one sheet producing correct results ... b.
I'm an affected user, I've followed this thread and b.'s for some time. On my system the bug showed itself under Version: 5.4.5.1 (x64) Build-ID: 79c9829dd5d8054ec39a82dc51cd9eff340dbee8 CPU-Threads: 4; BS: Windows 6.1; UI-Render: Standard; and persisted to Version: 6.3.0.0.alpha0+ (x64) Build ID: fe632c86aa250bb355a59ce6acf4dd75eae7afe0 CPU-Threads: 4; BS: Windows 6.1; UI-Render: Standard; @Eike Rathke, I think it's great that you are taking care of this issue and I appreciate it very much. Can't tell you how important it is to fix the problem at it's root, and I suppose I needn't - you know it quiet well yourself. Without fixing the basic issue LibreOffice is not really save to use for e.g. accounting. @b., it looks to me that this issue really stresses you out, but you should be a bit more grateful to Eike Rathke, he sacrifices his time to help us. On the other side, I have to agree with you, it looks like a deep rooted error and patching the symptoms won't help in the long run. But please recognise, that Eike Rathke tries to help you as fast as possible and most of the time workarounds and intercepting functions help until the underlying issue is identified. It looks to me, that Eike Rathke is a really good and dedicated programmer and b. an effective tester. That should be a good combination to get on to the issue. If there is anything I can do to assist you, don't hesitate to tell me. I have experience in C++ programming, but no time at the moment to learn the ropes of the LibreOffice system. Sometimes it helps, to have someone to bounce back ideas. @Eike Rathke, I've read b.'s assumptions about the error's nature. I would be interested in hearing your opinion, since you are the resident expert.
Thanks for the additional case given in comment 15. The problem with assumptions is exactly that, they are assumptions. It doesn't help to assume and generalize things. Those shared formula groups were introduced in some earlier (4.2 IIRC) version, so of course before that these things worked. Yes their dependency handling is broken and was from the beginning it seems. But that doesn't help here. Detailed concise test cases help, without long stories around.
(In reply to b. from comment #15) > "1" in A1:B4, > for i=1 to 4: > "=SUM(Ai:Bi)" in Ci, > move B1:B2 to B3:B4, > move B3:B4 to B1:B2, > C3 and C4 not autocalculated, For this btw if A1:B4 does not contain only 1 values but different distinct values like 1,2,4,8,16,32,64,128 it can be seen that it already fails after the first move. The notification doesn't happen for the affected second group of rows (here in C3:C4).
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/5c27a048658afcd2f78ef4d7e6c7128554ed3f4c%5E%21 Resolves: tdf#120013 tdf#123714 split-off group or single cell needs listening It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
@erack: hummel hummel, some things ok now, but not all?, i downloaded a dev version with filename: master~2019-03-13_00.21.52_LibreOfficeDev_6.3.0.0.alpha0_Win_x86 (first build i could find with a date after comment #20) effect and script to reproduce: put '2' in A1, '3' in B1, '=PRODUCT(A1:B1)' in C1, check '6' in C2 is correct, move B1 to B2, check formula in C1 not changed, check result there - now '2' - being wrong, (or my understanding of 'product' is wrong?) for formula '=SUM(A1:B1)' above works correct, other formulae not yet checked, i'm not sure in wich depth above is related to the shared-formula-autocalculate-problematic, at least they interfere affecting the same results ... and thus my testing ...
Created attachment 149954 [details] 6.3.0.0.alpha0+_still_buggy_03 ok, it needs to be a little more 'cruel' to a sheet, and the error - first i found - is less persistent, but try: '1' in A1, '2' in B1, '=SUM(A1:B1)' in C1, '=C1' in D1, copy down A1:D1 to A2:D2, (results differ between expanding by mousedrag or copying by ctrl-c ctrl-v, fault appears after both actions) cut C1:C2 and paste in C3:C4, formulae are adapted acc. to the move, D2 calculates correctly 5, D1 shows nothing, despite its formula is '=C3' and C3 is '3', the correct value appears with forced recalc, but also without that by scrolling in the sheet or other actions, above done with verison from comment #21 and openGL off
(In reply to b. from comment #21) > put '2' in A1, > '3' in B1, > '=PRODUCT(A1:B1)' in C1, > check '6' in C2 is correct, > move B1 to B2, > check formula in C1 not changed, > check result there - now '2' - being wrong, No, it is correct. > (or my understanding of 'product' is wrong?) PRODUCT() multiplies the elements of a NumberSequence, an empty cell (then in B1) is not part of a NumberSequence (i.e. ignored).
(In reply to b. from comment #22) > '1' in A1, > '2' in B1, > '=SUM(A1:B1)' in C1, > '=C1' in D1, > copy down A1:D1 to A2:D2, > (results differ between expanding by mousedrag or copying by ctrl-c ctrl-v, > fault appears after both actions) > cut C1:C2 and paste in C3:C4, > formulae are adapted acc. to the move, > D2 calculates correctly 5, ... if dragged down by mouse in the step above ... otherwise 3. > D1 shows nothing, despite its formula is '=C3' and C3 is '3', D1 display 0 here, not nothing. > the correct value appears with forced recalc, but also without that by > scrolling in the sheet or other actions, PageDn then PageUp (i.e. a redraw, also Shift+Ctrl+R) also makes the correct value appear, as does editing any other cell somewhere else and starting a formula, for example =D1 So this is rather a redraw problem but not a recalculation problem, the correct content is there, but not displayed, i.e. not refreshed after the Paste. Pasted to C4 after the cut, both cells D1:D2 stay 0 instead of displaying their actual new value.
the 'late_redraw_issue' from comment #22 and #24 is still present in ver: Version: 6.3.0.0.alpha0+ (x64) Build ID: 218916eb47e35fd14832d8f52225bf1d4d9c80a6 CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; TinderBox: Win-x86_64@42, Branch:master, Time: 2019-03-17_04:56:25 Locale: de-DE (de_DE); UI-Language: en-US Calc: threaded dated 2019-03-17, the rest looks fine for me, at least much better than before! (not yet tested all variations, but me feeling ist that the fundamental bug is out) thks @Eike!
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/f81d532648ccbbd08d3cb568eadc9f402dac67ad%5E%21 Unit test for cut copy move into shared formula group reference, tdf#123714 It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/69554 for 6-2
Verified in Version: 6.3.0.0.alpha0+ Build ID: 82463bdde75447d45e0cd6ed9ab579e0e51ea912 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US Calc: threaded using the document provided by Oliver and the steps from comment 11 While testing it, I found a similar problem -> bug 124326 @Eike, thanks for fixing this issue!!
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/8b616f94fbd040581e9a61d48066d59009ed6ae3%5E%21 Resolves: tdf#120013 tdf#123714 tdf#123736 shared formula group split It will be available in 6.2.3. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://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-6-2": https://git.libreoffice.org/core/+/a4bb79de7fa046aa540cc984e43baceff9304a11%5E%21 Unit tests for tdf#121002 tdf#120013 tdf#123714 tdf#123736 It will be available in 6.2.3. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.