Description: While doing some spreadsheet exercises I stumbled upon a strange problem. Every time I try to copy formula containing cells (selecting and dragging) the calculated values are different. I am attaching the file affected by this problem. Steps to Reproduce: 1. Select cells A4:Q4 2. Drag them all the way down to row 152 3. Repeat 1. and 2. a few times to see how cell values change 4. Reopen the file, should be correct now Actual Results: Calculated values are wrong and different every time Expected Results: Calculated should be correct and identical every time. Reproducible: Always User Profile Reset: Yes Additional Info: Closing and opening the file calculates correct values.
Created attachment 150564 [details] Affected file
Thank you for reporting the bug. To be certain the reported issue is not related to corruption in the user profile, could you please reset your Libreoffice profile ( https://wiki.documentfoundation.org/UserProfile ) and re-test? And please copy information from Menu "Help/About LibreOffice" so we can see the exact LibreOffice version you use. > Actual Results: > Calculated values are wrong and different every time Does it work, if you do a hard recalc after dragging? -> Menu "Data/Calculate/Recalculate Hard (Ctrl+Shift+F9)" Have you tried with disabled: Menu "Tools/Options/LibreOffice Calc/Calculate" CPU Threading settings [ ] Enable multi-threaded calculation I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested info is provided.
I tried reproducing this bug in safe mode and the same thing happens. Libreoffice version I am using is the following: 6.2.2.2, Build ID: 20 (Build:2) (installed from Solus repositories). Pressing Ctrl+Shift+F9 after dragging corrects all the values. Disabling multi-threaded calculation seems to fix this problem.
setting to "NEW" even if i can not reproduce at the moment, but it sounds understandable and seems to be related to/a duplicate of: Editing: Err:522 on copying of rows with "Calc: threaded" enabled https://bugs.documentfoundation.org/show_bug.cgi?id=124270
instant repro with Version: 6.3.0.0.alpha0+ (x64) Build ID: 35d9a2618dc0116378ab795a7b9277d248c5afd4 CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; TinderBox: Win-x86_64@42, Branch:master, Time: 2019-04-05_04:55:04 Locale: de-DE (de_DE); UI-Language: en-US Calc: *threaded* Err:522 in K153:N153 but not! repro with *not threaded*, three attempts, all identical, no err: win 7 pro x64 os: not 'all', but linux and win
Created attachment 156818 [details] shrinked sheet with the error still occuring looks like one more of my well beloved 'shared formula breaks autocalculate' or 'neglected dependencies' or 'threaded still experimental' problems. starts with cell H5 being calculated to 21 instead of 30 on drag or copy from row 4 to row 5. fails with 'threaded' or 'CL' enabled on my system, works better with both off, minimum reproducer as of now: attached sheet piraci_test2.ods (stripped some rows and columns), copy B4:H4 to B5:H5, value in H5 (21) wrong against formula (=B5+11) == (19+11) == (30), as shift-ctrl-F9 helps out ... standard 'autocalculate broken with shared formulae'? assumption: calculation of H5 is done without taking into account that the value of B5 - copied from B4 as value: "10", formula: "=D3", has to have the relative reference in the formula adapted to "=D4" and thus will change it's value too. 10 (value of copied B4) plus 11 (number to add for H5) will give the 21 shown in H5. things like this may occur when implementing non mature parallelizing mechanisms ... :-( (i admit that even in theory it is difficult to reconcile the splitting of a task (for parallelized processing) and the keeping together of dependencies (to recognize circular references and for proper 'clarification' of shared formulas) ... big praise to the developers that some of it works at all! ... looks as nobody is taking me serious ... imho it's time for a fix or kill-out of the source of this minefield of bugs instead of plastering only the errors which make it through to users notice ... *** kiss *** keep it simple and stupid *** avoid use of exotic constructs and functions *** and experimental features like parallellized computing *** and you'll be happy and save time of your life *** but the training of your brain is neglected *** that helps to stay happy too ***
*** Bug 129753 has been marked as a duplicate of this bug. ***
i'd put some 'digging ideas' there: https://bugs.documentfoundation.org/show_bug.cgi?id=129753
Created attachment 157481 [details] 124577_piraci_test8_ori, calculation error with threaded or openCL ok, as @xisco closed the simplified bug #129753 and thus it won't get much attention: the sheet presented by the OP for this bug is quite complex, too complex to get the error spotted and solved yet, it's hard to dig through it, i put in a simplified sheet here, it has two samples reduced to the minimum conditions where the error is appearing, and pinpointing the spot where the error starts, detailed info see into the sheet, condensed description: 'reproducible miscalculations with either threaded or openCL activated', imho the bug is important as it will - often unnoticed - affect plenty results for users working with either openCL or 'threaded' calculation, wich is activated by default, it would be very helpful if a developer can have a short look at the attachement and decide if a reliable spreadsheet could possibly perform better than that, thanks, reg. b.
Created attachment 157959 [details] another file pinpointing the bug have fun trying to spot the fault in the attached file, starting with a complexity of 3 - even very simple - dependent formulas per row and 100 rows with identical formulas below each other (that's where parallelized computation is activated if it's enabled by either openCL or 'threaded' calculation) with the rows 'connected' by one (or more) of the calculations, calc will react with wrong results if you do an 'unchanging edit' - e.g. copy the formula from another cell in a column - to the second from top cell of the identical (shared) formulas. calc will inject wrong values in cells dependent on the copied one, copied from the corresponding dependent cells of the source position for the copy.
(In reply to b. from comment #10) > Created attachment 157959 [details] > another file pinpointing the bug @b. Please create a new report for this one (to be sure, this bug shows version 5.0.0.5 Repro with 7.0 and 6.2 but not with Version: 6.1.0.0.beta2+ Build ID: 22c451df33b733440f24c1feb6380d31240d55e6 CPU threads: 4; OS: Windows 6.3; UI render: default; Locale: nl-NL (nl_NL); Calc: CL STR 1. Open the file 2. CTRL+SHIFT+F9 (fixing the issue) 3. Copy C6 and paste it at C5
hi :-) @Telesto, > new bug? i did, see c#8, xisco will 'ex-it-duplicate', > Version: 6.1.0.0.beta2+ pls. recheck, i had completely crippled results already with ver 5.0.0.1, may be your settings for dev/alpha ver. are different from those for standard ver.? openCL or threading or both are required and must be active - restart - for the bug to strike, in general: we have different views on the problems, i try to see the big picture, most testers and developers are oriented towards local phenomena? if you patch single aspects of a big bug locally you get ... a patchwork carpet which makes the big picture pixelated, and it's harder and harder to spot the basic problem. and if you do find it, it's very difficult to fix because changes will likely collide with the patches ... in this respect, excuse my approach and 'style' which is unusual for this forum, it's based on the best intention, and combining different approaches is one of the strengths of open software? be sure i'll only complain in this style for problems which - i assume to be - based on fundamental problems not yet solved.
for the Bug Hunting Session 7.0.0.0.a1+: still a bug with that ver., 'second from top' of a 99+ column of identical (shared) formulas is sensitive against overpasting with copies from elsewhere in that column once three 'calculation-connected' cells per row, at least one calc-connection to prev. and subseq. row, and threaded or openCL active, imho critical as the error can lead to well hidden miscalculations ... as it did for th OP,
still odd with below ver., @Eike: think you solved some similar issues in the past, at least regarding autocalculate and shared formulas ... additional difficulty 'parallel computing' here ... minimum reproducer and sample in c#9 what someone liked to flag obsolete ... ??? @Telesto: imho those comments are valid and neccessary, and helpful as they ease the work of the devs by some steps in boiling down which can be done by 'nopros' like me, Version: 7.1.0.0.alpha0+ (x64) Build ID: 75e1cf6c6ea83e65da248dab917b06feea6c18e4 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: CL
Bisected to author Dennis Francis <dennis.francis@collabora.co.uk> 2018-06-12 15:04:04 +0530 committer Dennis Francis <dennis.francis@collabora.co.uk> 2018-06-21 15:04:11 +0200 commit 12782fc917bcdc1c119bda675fc27f77887498e0 (patch) tree d0d990355644b71d4d8333fc7ffe5adbf3f1ab2e parent 6b496d6a0f8b9de38fbf6721796104496d927db3 (diff) Do dependency computation checks for OpenCL and... software interpreter like in CPU threading. This patch also reworks the cycle detection to make it more robust. Since the dependency computation also does cycle detection, there is no need to disable group-calc(threaded/OpenCL/SW Interpreter) for non-leaf nodes in recursive interpret. The rework of cycle detection ensures that it fixes tdf#95748 correctly. So same cause as bug 132451
(In reply to b. from comment #14) > still odd with below ver., > @Telesto: imho those comments are valid and neccessary, and helpful as they > ease the work of the devs by some steps in boiling down which can be done by > 'nopros' like me, Checking if it's a regression and bibisecting a bug is more effective strategy compared to adding a random developer. You will will know if: * it's a regression or not * which code change caused the problem * which developer you have to contact It's a commit from Dennis Francis. He is already aware.. and assigned himself (bug 132451). Bibisecting is rather easy thing to do, and helpful: https://wiki.documentfoundation.org/QA/Bibisect
@Telesto: thanks for taking care of it, i had seen the other bug before but didn't remember ... i got out of step because - unusually - you continued the case under another bug, i think that's right because it frees the developers from the burden of 'finding comments' here, but usually @xisco almost immediately stops such things as duplicates (see fate of tdf#129753), thus i'll set this bug here to duplicate, and refer to the new ... --- set to duplicate of refined bug --- https://bugs.documentfoundation.org/show_bug.cgi?id=132451 --- fixing and commenting should continue there --- *** This bug has been marked as a duplicate of bug 132451 ***