Bug 120013 - After Cut&Paste of an overlapping data range, cells of a shared formula group reference run are not recalculated
Summary: After Cut&Paste of an overlapping data range, cells of a shared formula group...
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.2.3
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2018-09-20 16:07 UTC by David Ruggiero
Modified: 2019-04-02 17:09 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Example .ods spreadsheet file to illustrate bug. (11.14 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-09-20 16:08 UTC, David Ruggiero
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Ruggiero 2018-09-20 16:07:36 UTC
Description:
In several circumstances, a cut-and-paste from one row to another doesn't invoke an automatic recalc, leaving total columns incorrect until manual recalc is performed. Problem reproducible and example spreadsheet attached. 

Steps to Reproduce:
1. See attached spreadsheet
2. Highlight cells A8-D8
3. Cut cells using keyboard shortcut
4. Paste into cells A4-D4 using keyboard shortcut - do not hit return after paste
5. Click on any other cell in spreadsheet.
6. Note total column E is incorrect.
7. Force manual recalc with shift-F9 (or whatever is your shortcut).
8. Note totals are now correct.

Actual Results:
Column E totals are not correct until recalc is forced.

Expected Results:
Column E totals should be correct after paste even without recalc.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
using
Comment 1 David Ruggiero 2018-09-20 16:08:40 UTC
Created attachment 145073 [details]
Example .ods spreadsheet file to illustrate bug.
Comment 2 David Ruggiero 2018-09-20 16:12:15 UTC
Note this bug is very similar to the report in <a href="https://bugs.documentfoundation.org/show_bug.cgi?id=99322">Bug 99322</a>, which was already reported as fixed.
Comment 3 David Ruggiero 2018-09-20 16:20:25 UTC
Note that if <return> is hit after the paste, *some* totals are updated in the running total column (E) - but not all. Behavior is different...but display is still incorrect until a manual recalc is performed.
Comment 4 Oliver Brinzing 2018-09-21 13:40:36 UTC
i can confirm this issue with:

Version: 6.1.1.2 (x64)
Build-ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

Version: 6.2.0.0.alpha0+ (x64)
Build ID: 7595fce391ba2aca49db87c93006302d0c2a64f2
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: de-DE (de_DE); Calc: threaded

E4 is 2200 but should be 1200

a full hard recalc Shift+Ctrl+F9 is needed to update the spreadsheet
Comment 5 Oliver Brinzing 2018-09-21 15:59:51 UTC
some remarks:

- copy cell range A8:D8 to A4 will work

- after removing all $ from formula's in row 3
  it will work even with cut and paste

i think this issus maybe has the same root cause as reported in:

Lonely SUM formula fails to update, and thus can lead 
to a treacherously wrong result
https://bugs.documentfoundation.org/show_bug.cgi?id=119623
Comment 6 David Ruggiero 2018-09-21 16:18:20 UTC
Updating on my own comment #3 -

- If <return> is not hit after the paste, running total column appears to be incorrect from the same row that the paste happened into, onwards.

- If <return> IS hit after the paste, running total column appears to be recalculated only up until the same row as the *cut* occurred from; it's wrong after that.

- Either way a force recalc is needed to insure correct results are displayed fully.


I agree the $ static references are the key here. We often use running total columns (eg A$1:A${n} where 'n' is the current row) and see this same behavior often when using them.
Comment 7 David Ruggiero 2018-09-21 16:28:41 UTC
Sorry, last paragraph should have read:

I agree the $ static references are the key here. We often use running total columns (eg A$1:A{nn} where 'nn' is the current row) and see this same behavior often when using them.
Comment 8 Xisco Faulí 2018-10-17 14:09:48 UTC
Reproduced back to

Version: 5.2.0.0.alpha0+
Build ID: 3ca42d8d51174010d5e8a32b96e9b4c0b3730a53
Threads 4; Ver: 4.10; Render: default; 

but not in

Version: 5.0.0.0.alpha1+
Build ID: 0db96caf0fcce09b87621c11b584a6d81cc7df86
Locale: ca-ES (ca_ES.UTF-8)

it needs to be bisected with 5-1 repo
Comment 9 Buovjaga 2018-10-17 17:09:21 UTC
Bisected with win32-5.1 to https://cgit.freedesktop.org/libreoffice/core/commit/?id=2f6a06856ad8df0c11a112d1e457b408e9a7af1d
tdf#90694 reset group area listeners when splitting group

Adding Cc: to Eike Rathke
Comment 10 Oliver Brinzing 2018-10-21 08:27:13 UTC
(In reply to Oliver Brinzing from comment #5)
> i think this issus maybe has the same root cause as reported in:
> Lonely SUM formula fails to update, and thus can lead 
> to a treacherously wrong result
> https://bugs.documentfoundation.org/show_bug.cgi?id=119623

i have to correct me: bug 119623 is fixed on current master,
but this issue is still reproducible
Comment 11 Oliver Brinzing 2018-11-21 18:10:40 UTC
(In reply to Buovjaga from comment #9)
> Bisected with win32-5.1 to
> https://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=2f6a06856ad8df0c11a112d1e457b408e9a7af1d

i can confirm this issue *already* with

Version: 4.4.7.2
Build-ID: f3153a8b245191196a4b6b9abd1d0da16eead600
Gebietsschema: de_DE

but *not* reproducible with

AOO415m1(Build:9789)  -  Rev. 1817496
2017-12-11 17:25
Comment 12 Oliver Brinzing 2018-12-09 13:39:13 UTC
*not* reproducible with

Version 3.6.7.2 (Build ID: e183d5b)
Comment 13 Oliver Brinzing 2019-03-03 16:25:32 UTC
as mentioned in Comment 11, this issue is reproducible with lo 4.4.7.2 and
seems to have started with (-> Comment 9) :

/cygdrive/d/sources/bibisect/bibisect-win32-5.0
$ git bisect good
a2216dc6b54a24ab630287e35c156fba6a134012 is the first bad commit
commit a2216dc6b54a24ab630287e35c156fba6a134012
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Sun Jun 7 05:39:58 2015 -0500
    source sha:2f6a06856ad8df0c11a112d1e457b408e9a7af1d
    source sha:2f6a06856ad8df0c11a112d1e457b408e9a7af1d
:040000 040000 bb415c766ad693037297f8ad534f5231f823a380 d6cc75e5e4daf92501bed1e49a05ffca1d8700da M      instdir

/cygdrive/d/sources/bibisect/bibisect-win32-5.0
$ git bisect log
# bad: [b7988d11e5d3751a4b366b2bfc9048f7a30e8526] source sha:87ac0b1e75a880a68ecb748bd4b34ae5a3d2ae98
# good: [f449493ae11ac76cc7396bddeaa624a60c565936] source sha:57d6b92b69a31260dea0d84fcd1fc5866ada7adb
git bisect start 'master' 'oldest'
# good: [66e2ae767eb4bb83444e3d03bcb90adcbe6d4991] source sha:5a308b1239a09417507b0d05090ff2d3418d5133
git bisect good 66e2ae767eb4bb83444e3d03bcb90adcbe6d4991
# good: [c51237da468f7026112580cfb26a732ce39f523d] source sha:103bf75921e069d1c078c0ef30b94b8f91920877
git bisect good c51237da468f7026112580cfb26a732ce39f523d
# good: [506aebdebff0cb9a6b9a21b4cc1420ac30da809c] source sha:741d9990bf9d9dfcba1166a12ffb1d846c912181
git bisect good 506aebdebff0cb9a6b9a21b4cc1420ac30da809c
# good: [7dc37603af81ce291598745d95748b9b95154852] source sha:c642425fd372ef219a683b5198600746fb7f0c3c
git bisect good 7dc37603af81ce291598745d95748b9b95154852
# good: [560b34a7eb48f551c27a6d6a5426fb5f97646806] source sha:9509ce61031f27dd1977599ffc271aaffdb6ca82
git bisect good 560b34a7eb48f551c27a6d6a5426fb5f97646806
# good: [064b8078e5a1bcbb418ae5269ec455e3fe1725e0] source sha:cf7439c5510578572b30a92a52549b5babfa93d9
git bisect good 064b8078e5a1bcbb418ae5269ec455e3fe1725e0
# good: [c697b9230f6d50a0b11d587aeecc03fcd2274ae2] source sha:0cfe2c8c893bfe6d1c2dce5941065eb4e841e7cc
git bisect good c697b9230f6d50a0b11d587aeecc03fcd2274ae2
# good: [d5eeda552dcaea9a97903e637ca53dc84362682f] source sha:f170b307f6a0f98a3ef5670f64c14d89cfa0fb33
git bisect good d5eeda552dcaea9a97903e637ca53dc84362682f
# good: [b1b9ea8aa90258c20866c1a2649f0d093eb58755] source sha:c0d6bc75b223e9e477ef3669f7dc4abec703ecf6
git bisect good b1b9ea8aa90258c20866c1a2649f0d093eb58755
# bad: [c94b4b237082130b6d99790820fb2f7ffa334836] source sha:bc8adace59ab5bebb93610e0ec3b16ef2a8f5151
git bisect bad c94b4b237082130b6d99790820fb2f7ffa334836
# bad: [d9075eb43659c800a12cea44d787cf77675a4501] source sha:621ab8571ee99b0d425cfb88892898884edb2eec
git bisect bad d9075eb43659c800a12cea44d787cf77675a4501
# bad: [a2216dc6b54a24ab630287e35c156fba6a134012] source sha:2f6a06856ad8df0c11a112d1e457b408e9a7af1d
git bisect bad a2216dc6b54a24ab630287e35c156fba6a134012
# good: [fd09b88abd1dbdc9727f6658d1b371db170d96d9] source sha:15499b1e4f2d31c2707d75800046f7fa12bb5dac
git bisect good fd09b88abd1dbdc9727f6658d1b371db170d96d9
# first bad commit: [a2216dc6b54a24ab630287e35c156fba6a134012] source sha:2f6a06856ad8df0c11a112d1e457b408e9a7af1d
Comment 14 Buovjaga 2019-03-03 20:30:58 UTC
(In reply to Oliver Brinzing from comment #13)
> as mentioned in Comment 11, this issue is reproducible with lo 4.4.7.2 and
> seems to have started with (-> Comment 9) :
> 
> /cygdrive/d/sources/bibisect/bibisect-win32-5.0
> $ git bisect good
> a2216dc6b54a24ab630287e35c156fba6a134012 is the first bad commit
> commit a2216dc6b54a24ab630287e35c156fba6a134012
> Author: Norbert Thiebaud <nthiebaud@gmail.com>
> Date:   Sun Jun 7 05:39:58 2015 -0500
>     source sha:2f6a06856ad8df0c11a112d1e457b408e9a7af1d
>     source sha:2f6a06856ad8df0c11a112d1e457b408e9a7af1d
> :040000 040000 bb415c766ad693037297f8ad534f5231f823a380
> d6cc75e5e4daf92501bed1e49a05ffca1d8700da M      instdir

Yeah, same commit as in my bibisect in comment 9. This just means it was backported to the older branches.
Comment 15 David Ruggiero 2019-03-08 19:13:35 UTC
(Original submitter)

I am now seeing, in Calc sheets with similar formulas, that even a manual recalc (Fn-Shift-F9) does not always give the correct results in some cases. I will try to make a simple reproducible example. If I can do so, I think the severity should be raised to "major" because the incorrect results are persistent (at least until a save/restore). I say this especially in light of bug reports like 

https://bugs.documentfoundation.org/show_bug.cgi?id=123714

...which point at a potentially deeper and more fundamental flaws in Calc's handling of formula absolute references.
Comment 16 Eike Rathke 2019-03-08 19:17:28 UTC
It's not Fn+Shift+F9 but Ctrl+Shift+F9 instead.
Comment 17 Eike Rathke 2019-03-08 23:52:46 UTC
Note that the sample document attachment 145073 [details] is stored with wrong result values in E9:E12, which needs a hard recalc Shift+Ctrl+F9 before trying things.
Comment 18 Commit Notification 2019-03-09 01:15:46 UTC
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.
Comment 19 Oliver Brinzing 2019-03-09 13:59:47 UTC
(In reply to Commit Notification from comment #18)

> This partly also resolves tdf#120013 but there's more to that, a
> remaining partial group is not updated.

i can confirm this:

> Steps to Reproduce:
> 1. open attached spreadsheet
> 2. Note that the sample document is stored with wrong result values in 
>    E9:E12, which needs a hard recalc Shift+Ctrl+F9 before trying things.
> 3. select A8:D8
> 4. cut cells using keyboard shortcut
> 5. paste into cells A4:D4 using keyboard shortcut

-> now cell range E4:E8 updates while E9:E12 still needs a hard recalc
Comment 20 Commit Notification 2019-03-12 00:08:28 UTC
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.
Comment 21 Oliver Brinzing 2019-03-15 18:49:40 UTC
(In reply to Commit Notification from comment #20)
> Affected users are encouraged to test the fix and report feedback.

this issue (-> comment #19) is no longer reproducible with:

Version: 6.3.0.0.alpha0+ (x64)
Build ID: 3140194a85fe4a6ac69c8cddc4d3b019430cd6e8
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded
Comment 22 Eike Rathke 2019-03-22 12:40:32 UTC
Pending review https://gerrit.libreoffice.org/69554 for 6-2
Comment 23 Commit Notification 2019-03-22 20:42:55 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Unit test for cut copy move intersecting a formula group run, tdf#120013

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 24 Xisco Faulí 2019-03-25 18:27:13 UTC
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

@Eike, thanks for fixing this issue!
Comment 25 Xisco Faulí 2019-03-27 14:58:46 UTC
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.
Comment 26 Commit Notification 2019-03-28 14:23:40 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.
Comment 27 David Ruggiero 2019-04-02 17:09:59 UTC
Downloaded LO 6.2.3 RC1 today and I verify that this bug is fixed in all my test cases. Fine work, Eike - du haben großartige Arbeit geleistet!