Bug 124577 - clear reproducible error with combination of shared formulas, parallelized computing, autocalc weakness, see comment c#10 | was: Copying by dragging calculates false values (different every time)
Summary: clear reproducible error with combination of shared formulas, parallelized co...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.0.5 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 129753 (view as bug list)
Depends on:
Blocks: Calculate Calc-Threaded
  Show dependency treegraph
 
Reported: 2019-04-06 11:11 UTC by zbrojny120
Modified: 2020-02-17 18:40 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Affected file (48.55 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-04-06 11:12 UTC, zbrojny120
Details
shrinked sheet with the error still occuring (25.67 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-12-28 15:08 UTC, b.
Details
124577_piraci_test8_ori, calculation error with threaded or openCL (22.75 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-01-28 12:24 UTC, b.
Details
another file pinpointing the bug (34.87 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-02-17 18:06 UTC, b.
Details

Note You need to log in before you can comment on or make changes to this bug.
Description zbrojny120 2019-04-06 11:11:53 UTC
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.
Comment 1 zbrojny120 2019-04-06 11:12:51 UTC
Created attachment 150564 [details]
Affected file
Comment 2 Oliver Brinzing 2019-04-06 12:06:08 UTC
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.
Comment 3 zbrojny120 2019-04-06 13:32:28 UTC
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.
Comment 4 Oliver Brinzing 2019-04-06 14:48:45 UTC
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
Comment 5 b. 2019-04-11 17:59:56 UTC
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
Comment 6 b. 2019-12-28 15:08:54 UTC
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 ***
Comment 7 Xisco Faulí 2020-01-21 12:16:03 UTC
*** Bug 129753 has been marked as a duplicate of this bug. ***
Comment 8 b. 2020-01-22 09:16:33 UTC
i'd put some 'digging ideas' there: 

https://bugs.documentfoundation.org/show_bug.cgi?id=129753
Comment 9 b. 2020-01-28 12:24:17 UTC
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.
Comment 10 b. 2020-02-17 18:06:18 UTC
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.