Bug 121002 - Cells not recalculated after moving data within ranges pointed to by a shared formula group.
Summary: Cells not recalculated after moving data within ranges pointed to by a shared...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.7.2 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.3.0 target:6.1.6 target:6.2....
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2018-10-29 07:58 UTC by Peter Beurle
Modified: 2019-03-28 14:23 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Showing rows not recalculated (15.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-10-29 07:58 UTC, Peter Beurle
Details
reinserting the formula in H5 (16.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-10-29 11:22 UTC, Xavier Van Wijmeersch
Details
LO 6.3: result after moving cells (5.32 KB, image/png)
2019-02-09 08:15 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Peter Beurle 2018-10-29 07:58:07 UTC
Created attachment 146119 [details]
Showing rows not recalculated

Possibly related to 120660

The range G7:D8 was moved and the total at the end of row did not recalculate.

If you look in H5 or H7 it all looks OK.

I have seen this before but hard to replicate and could have possibly been around since 2014.

Severity needs to be high, a spreadsheet that doesn't add up?
Comment 1 Xavier Van Wijmeersch 2018-10-29 09:37:33 UTC
Your formula in H7 and H8 are wrong, they are a copy off H5
So not a bug

I you not agree feel free to reopen as unconfirmed

Best regards
Comment 2 Peter Beurle 2018-10-29 09:48:53 UTC
H5 shows as zero to me. But in the cell is a formula =SUM(D5:G5)
Comment 3 Xavier Van Wijmeersch 2018-10-29 11:20:44 UTC
Silly me did not notice the zero in H5
a hard calculation give the correct value

but with reentering the formula in H5 and dragging down, saving the file and reopen the zero is gone. See attachment

Version: 6.1.4.0.0+
Build ID: 18a388c696567e6ddbf8c997ee0a9a686e7d80ed
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded
Comment 4 Xavier Van Wijmeersch 2018-10-29 11:22:36 UTC
Created attachment 146127 [details]
reinserting the formula in H5

Try to open the file and hopefully you will see the problem is gone
Comment 5 Peter Beurle 2018-10-29 19:38:51 UTC
I think you are missing the point. I shouldn't have to manipulate a file on open to get it to display correct values, its misleading and incorrect behavior.
Comment 6 Xisco Faulí 2018-10-30 13:17:59 UTC
You can't confirm your own bugs. Moving it back to UNCONFIRMED until someone
else confirms it.
Comment 7 Joel Madero 2018-11-20 12:04:53 UTC
Closing as we do not have reproducible steps. What we have is a single file that appears to have some kind of an issue but Xisco has explained how to fix this one file. If this is a consistent problem across multiple files, please provide clear reproducible steps from a blank file on how you created the problem.

It's not a bug if you have one potentially corrupted file and cannot explain to use how to reproduce the bug. Well, maybe it's a bug but it's a useless report if we can't reproduce it consistently.

Please do not reopen this without detailing how to create a similar file from scratch that shows the same issue.

Thanks.
Comment 8 Peter Beurle 2018-11-21 09:38:15 UTC
Steps to reproduce;

1. Create a blank worksheet

2. Ensure AutoCalculate is turned on Data|Calculate. This should mean any changes to cell values are recalculated automatically, without F9 manual recalculation. 

3. Create a small table of four rows and three columns. In column C put a formula SUM(A1:B1) and copy down to row four.

4. In A3 and B3 put in 3, C3 should now show 6

5. In A4 and B4 put in 4, C4 should now show 8

6. Select cells A3 to B4 and drag (move) to A1. A1 and B1 should now have 3 in them and A2 and B2 should now have 4 in them.

7. Cell C1 shows zero. The formula in cell C1 is SUM(A1:B1). Expected result is 6

8. Cell C2 shows zero. The formula in cell C2 is SUM(A2:B2). Expected result is 6

9. Select cell C1 and push F9, result is 6 ∴ cells did not auto recalculate.

10. The relationship between the values and result on row three and four changed on the move. The formula in C3 now represents the values in A1 and B1. The formula in C1 has had its values change, effectively overwritten by the move. If you do this in Excel formula in C1 and C2 return #REF!, indicating the references have been broken. This warns the user that they have made changes to the referenced cells A1:B2
Comment 9 Xavier Van Wijmeersch 2018-11-21 10:26:32 UTC
You can't confirm your own bugs. Moving it back to UNCONFIRMED until someone
else confirms it.
Comment 10 Oliver Brinzing 2018-11-21 17:17:21 UTC
confirming issue with

Version: 6.1.3.2 (x64)
Build ID: 86daf60bf00efa86ad547e59e09d6bb77c699acb
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: de-DE (de_DE); Calc:
Comment 11 Oliver Brinzing 2018-11-21 17:21:32 UTC
reproducible with

Version: 6.2.0.0.alpha1+ (x64)
Build ID: f5011d7226060e04e7b92f035f71e285b853712c
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

and

Version: 5.4.7.2
Build-ID: c838ef25c16710f8838b1faec480ebba495259d0
CPU-Threads: 4; BS: Windows 6.2; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: single

but *not* reproducible with

AOO415m1(Build:9789)  -  Rev. 1817496 
2017-12-11 17:25
Comment 12 Oliver Brinzing 2018-11-21 17:39:35 UTC Comment hidden (obsolete)
Comment 13 Xisco Faulí 2018-11-21 17:42:58 UTC
(In reply to Oliver Brinzing from comment #12)
> and reproducible with
> 
> Version: 4.4.7.2
> Build-ID: f3153a8b245191196a4b6b9abd1d0da16eead600
> Gebietsschema: de_DE

Hi Oliver,
Please update the earliest version every time you reproduce it with an earilier version...
Comment 14 raal 2019-01-04 15:31:49 UTC
This seems to have begun at the below commit.
Adding Cc: to Eike Rathke ; Could you possibly take a look at this one? Thanks
 938ff6f34bfb99a36e8a7a01caef827a7aef7705 is the first bad commit
commit 938ff6f34bfb99a36e8a7a01caef827a7aef7705
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Tue Jul 14 13:05:51 2015 -0700

    source 2f6a06856ad8df0c11a112d1e457b408e9a7af1d
author	Eike Rathke <erack@redhat.com>	2015-06-05 15:51:46 +0200
committer	Eike Rathke <erack@redhat.com>	2015-06-05 16:22:38 +0200
commit 2f6a06856ad8df0c11a112d1e457b408e9a7af1d (patch)
tree e2376a0b58c878522e839aa28c36257b349d6751
parent 15499b1e4f2d31c2707d75800046f7fa12bb5dac (diff)
tdf#90694 reset group area listeners when splitting group
Comment 15 Eike Rathke 2019-01-30 12:12:03 UTC
Investigating.
Comment 16 Eike Rathke 2019-02-06 14:22:54 UTC
Tough one to debug, cause in a related but different area where misbehaviour was only uncovered by the mentioned change, fix is a one-liner on the way.
Comment 17 Commit Notification 2019-02-06 15:36:20 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/cdfa8b27f28328612b6734533981c1b363ced0a0%5E%21

Resolves: tdf#121002 UpdateRefOnNonCopy::mbUpdated=true if references change

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.
Comment 18 Eike Rathke 2019-02-06 15:37:09 UTC
Pending review
https://gerrit.libreoffice.org/67465 for 6-2
https://gerrit.libreoffice.org/67466 for 6-1
Comment 19 Commit Notification 2019-02-09 00:11:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

https://git.libreoffice.org/core/+/439407a36e4085c17c65c620a1bb2ca95f4564b0%5E%21

Resolves: tdf#121002 UpdateRefOnNonCopy::mbUpdated=true if references change

It will be available in 6.1.6.

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.
Comment 20 Commit Notification 2019-02-09 00:11:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/650f3ee43c22a00c15799d31995b22fc8e0742c9%5E%21

Resolves: tdf#121002 UpdateRefOnNonCopy::mbUpdated=true if references change

It will be available in 6.2.1.

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.
Comment 21 Oliver Brinzing 2019-02-09 08:15:06 UTC
Created attachment 149033 [details]
LO 6.3: result after moving cells
Comment 22 Oliver Brinzing 2019-02-09 08:17:55 UTC
sems to be fixed in: 

Version: 6.3.0.0.alpha0+ (x64)
Build ID: db6e0348a3c5d41e6a5d38a7902645e8e3c22b4a
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

(In reply to Peter Beurle from comment #8)
> 10. The relationship between the values and result on row three and four
> changed on the move. The formula in C3 now represents the values in A1 and
> B1. The formula in C1 has had its values change, effectively overwritten by
> the move. If you do this in Excel formula in C1 and C2 return #REF!,
> indicating the references have been broken. This warns the user that they
> have made changes to the referenced cells A1:B2

IMHO this is the normal behaviour in calc - moving referenced cells changes formula
Comment 23 Xisco Faulí 2019-02-11 11:12:18 UTC
(In reply to Oliver Brinzing from comment #22)
> sems to be fixed in: 
> 
> Version: 6.3.0.0.alpha0+ (x64)
> Build ID: db6e0348a3c5d41e6a5d38a7902645e8e3c22b4a
> CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
> Locale: de-DE (de_DE); UI-Language: en-US
> Calc: threaded

SEtting as VERIFIED FIXED then.
@Eike, thanks for fixing this issue.
Comment 24 b. 2019-02-26 07:20:54 UTC
maybee the fix is 'incomplete', pls. check tdf#123714,
Comment 25 b. 2019-03-05 07:45:19 UTC
try the following: 
A1: "=SUM(B1:C1)"
B1: "1"
C1: "2"
mark range A1:C1 and 'pull' it 'down' on the little black square at the lower right corner to fill A1:C3
(ctrl-c ctrl-v will produce the same)
move B1 to B2 either by dragging with the mouse(click - shift-click - drag) or by ctrl-x ctrl-v, 
recalculate the result in A2 with pencil and paper, in my case (6.3.0.0.alpha0, full version below) the formula in the cell is "=SUM(B2:C2)", B2 is "1", C2 is "3", A2 shows "5", 

>>>   thus 5=1+3 ?!?!   <<<

the 'crutch' ctrl-shift-F9 will produce corrected results, but: 

- a spreadsheet shouldn't need such a crutch, 

- after hard-recalc the autocalculation of B1 and B2 isn't fixed, changing their values requires hard-recalc again to be reflected in A1 and A2, 

- end users have no indicator when the crutch is neccessary, so they get trapped, 

(end users could also need something indicating that they opened a faulty file, in my case the sheet 'busted' works better on new moves, but does! show the old wrong results on open) 

it won't be sufficient to fix just this special occurence, handling of shared formulas and autocalculate should be fixed in general, 

autocalculate should work as announced - not in most but in all! situations!

announced is: 

'All cells are recalculated after a sheet cell has been modified. Any charts in the sheet will also be refreshed.'

maybee ... a programmer took that 'exactly' and thought the cell is not modified, just moved? silly, B1 is modified and has another value after the move, also B2, so please trigger autocalculation!

version tested: 

Version: 6.3.0.0.alpha0+ (x64)
Build ID: 9c5dbbe4b0a62ff1af009beb00f1fc45318dad79
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-02-27_20:03:12
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded
Comment 26 Eike Rathke 2019-03-05 14:15:20 UTC
What you describe is not the original behaviour of this bug, thus it is not to be reopened. You already filed bug 123714 and bug 123736.
Comment 27 b. 2019-03-05 22:53:26 UTC
@Eike Rathke: 

the title of the bug is: 'Cells not recalculated after moving data within ranges pointed to by a shared formula group', 

that is, word by word, the misbehaviour shown in my 'script', 

it's still happening in 6.3.0.0.alpha0, thus 'fixed' sounds wrong to me and i gave feedback,  

i did! file similar bugs, i know, 

there are! plenty more errors as 'not recalculated after moving cells to outside the range', 'errors after undo' in similar situations and so on, 

i'm quite sure most of them are 'related', 

as i'm not used how to handle such things: this site looks like working on one special point at a time, thus i did the same, 

- tested the headline, found it failing and gave feedback - 

if ... there is any way to group similar errors to get better access to the fundamental source of this evil ... i'd like that very much ...

keywords: formula range autocalculate broken

reg. 



b.
Comment 28 Eike Rathke 2019-03-08 23:40:21 UTC
Fwiw, the scenario mentioned in comment 25 will be fixed with bug 123714 and bug 123736.
Comment 29 Oliver Brinzing 2019-03-09 14:13:25 UTC
(In reply to Eike Rathke from comment #28)
> Fwiw, the scenario mentioned in comment 25 will be fixed with bug 123714 and
> bug 123736.

with my developer build 

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

i can not longer reproduce the scenario mentioned in Comment 8, 
cells C1 and c2 will update after moving A3:B4 to A1:B2
Comment 30 Xisco Faulí 2019-03-13 13:24:34 UTC
(In reply to Oliver Brinzing from comment #29)
> i can not longer reproduce the scenario mentioned in Comment 8, 
> cells C1 and c2 will update after moving A3:B4 to A1:B2

Thanks for verifying it!. Setting it to VERIFIED FIXED

Eike, thanks for fixing this issue!
Comment 31 Eike Rathke 2019-03-21 17:06:15 UTC
FYI, the test case scenario works fine if the move of A3:B3 to A1:B1 is done via Drag&Drop, but after Cut&Paste C3 *appears* to not being recalculated, but it actually is, it just is not redrawn as can be easily verified with a sequence of PageDn and PageUp, or Shift+Ctrl+R. This is likely the same cause as in bug 123736 and will be tracked there.
Comment 32 Commit Notification 2019-03-22 09:47:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/92657eed57548511f26f0c22f1bbc47d554c8b5b%5E%21

Unit test for cut copy move with a group reference adjusting along, tdf#121002

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.
Comment 33 Eike Rathke 2019-03-22 11:38:53 UTC
I created bug 124266 for the repaint failure.
Comment 34 Eike Rathke 2019-03-22 12:42:21 UTC Comment hidden (obsolete)
Comment 35 Eike Rathke 2019-03-22 12:44:18 UTC Comment hidden (obsolete)
Comment 36 Commit Notification 2019-03-28 14:23:32 UTC
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.