Bug 123736

Summary: Changing a reference in a formula cell of a shared formula group may not rebuild all dependencies of the then split group.
Product: LibreOffice Reporter: b. <newbie-02>
Component: CalcAssignee: Eike Rathke <erack>
Status: VERIFIED FIXED    
Severity: normal CC: aron.budea, cno, frederic.parrenin, himajin100000, newbie-02, oliver.brinzing, xiscofauli
Priority: medium Keywords: bibisected, bisected, regression
Version: 4.4.7.2 release   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=123714
https://bugs.documentfoundation.org/show_bug.cgi?id=120660
https://bugs.documentfoundation.org/show_bug.cgi?id=120013
https://bugs.documentfoundation.org/show_bug.cgi?id=121002
https://bugs.documentfoundation.org/show_bug.cgi?id=121043
Whiteboard: target:6.3.0 target:6.2.3
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 109324    
Attachments: demo recalc sum after fomula change
screenshot of miscalculated sheet
better screenshot, here you can see the formula of one of the miscalculated cells

Description b. 2019-02-27 11:39:13 UTC 
Description:
hello all, 

libreoffice calculates 1 plus 1 to be 3, it's not! a matter of rounding, 

i couldn't stand waiting if and when someone will fix tdf#123714, thus played and 'stressed' some sheets to narrow down where errors evolve, 

i found one effect that i consider more than critical: 

reproducible in plenty situations the following happens: 

after making a copy of a cell with a formula, and editing that copy, the originating cell is excluded from autocalculate, 

no matter what you do with the copy, you may even delete it from the sheet, the 'broken' source cell will always need 'F9' or 'crtl-shift-F9' to be recalculated after changes in the values of the cells referenced in the formula, thus you can see: 1 + 1 = 3 for the sum function '=SUM(A1:B1)' in C1 with '1' in A1 and changing B1 from '2' to '1'. 

formula, formatting, everything else is correct, even calculating works correct *when* it's initiated by F9, the only 'fault' is: the cell is excluded from autocalculate!  

you can make a new copy of the cell with the 'first formula', and it - the source - will be included in autocalculate again, 

until you edit the new copy, then autocalculating for the source cell will be broken again, 

(i don't know if it's affecting only distinct formulas and cells, i have the impression formulas calculating ranges fail ('=SUM(Xi:Yj)', '=MAX(A1:A15)') while formulas calculating distinct cells work ('=A1+B2' or similar) just play with it, below is one general description and under 'steps' one failing sample with concrete values)

general: 
- check that autocalculate is on, 
- put a formula in one cell, 
- make a copy of that cell, 
- put some data in the sheet to be calculated by he formulas, 
- check the results, 
- change something in the area / cells used in the formula, 
- check that the results change correctly, 
- edit something in the copied formula while leaving the cell with the 'source formula' untouched, 
- watch that from now on changes in the area / cells used in the formulas were not! anymore reflected in correct results in the 'source-formula-cell', while the 'copied-formula-cell' still calculates correctly, 

that's definitively *not* how a reliable spreadsheet should work, changes producing erroneous results in cells not even touched by the change. 

if the above description works correct in your case pls. check the 
concrete sample in 'steps to reproduce'. 

my Version: 5.4.7.2 (x64)

Steps to Reproduce:
1. check that autocalculate is on, 
2. put '=SUM(A1:B1)' in C1, 
3. copy C1 with 'ctrl-c', 
4. insert that copy in C2 with 'ctrl-v', 
5. put '1' in A1 and A2 and '2' in B1 and B2, 
6. check that C1 and C2 calculate '3', 
7. edit C2 from '=SUM(A2:B2)' to '=SUM(A2:B3)', do not! touch C1, 
8. change B1 from '2' to '1', 
9. check the value of C1, in my case it still shows '3' while calculating 1+1, 
10. watch that from now on changes in the area / cells used in the formulas are not! anymore reflected in correct results in the 'source-formula-cell' C1, while the 'copied-formula-cell' C2 still calculates correctly, 

Actual Results:
C1 isn't updated on changes of A1 or B1

Expected Results:
C1 should be updated by autocalculate in the same manner as C2


Reproducible: Always


User Profile Reset: No



Additional Info:
maybee that's the source of plenty similar bugs, maybee it can be triggered late after 'construction' of these 'shared formulas' ('landmines' in sheets causing destructions in areas far away?), maybee it's not the real fundamental fault but only an effect in a shell around it ... but, not negotiable, it should be checked and fixed as fast as possible ...  

Version: 5.4.7.2 (x64)
Build ID: c838ef25c16710f8838b1faec480ebba495259d0
CPU threads: 8; OS: Windows 6.1; UI render: default; 
Locale: de-DE (de_DE); Calc: group

i had to choose an 'earliest affected' version, could only tell about my actual install, i'd bet it's in since 4.2
Comment 1 perryg 2019-02-27 12:30:45 UTC Comment hidden (obsolete)
Comment 2 b. 2019-02-27 13:38:31 UTC Comment hidden (obsolete)
Comment 3 Oliver Brinzing 2019-02-27 16:56:28 UTC 
Created attachment 149635 [details]
demo recalc sum after fomula change

steps to reproduce:

- open attached spreadsheet
- 7. edit C2 from '=SUM(A2:B2)' to '=SUM(A2:B3)', do not! touch C1, 
- 8. change B1 from '2' to '1',
-> C1 will not update
-> a hard recalc is necessary
Comment 4 Oliver Brinzing 2019-02-27 17:01:29 UTC 
and to make it work without a hard recalc a save & reload cycle is necessary.

this issue is not reproducible with:

Version: 4.4.7.2
Build-ID: f3153a8b245191196a4b6b9abd1d0da16eead600
Gebietsschema: de_DE
Comment 5 b. 2019-02-28 06:22:48 UTC 
narrowing down: 

fails looks dependent on placing the copy above or below the original, 
(maybe that's the situation where 'shared formulas' are created) 

fails looks dependent on formulas calculating a range, 
('=SUM(A1:B1)' and '=MAX(A1:B1)' will fail, while '=A1+B1' and '=SUM(A1;B1)' will work),

there must be some sort of 'property' with a cell that can lock it from being 'autocalculated'?, 

or some sort of label attached to its 'place in the sheet' saying 'autocalculate unneccessary'?, 

this property is hidden, out of view and access by the user, 

it violates the statement what autocalculate should do
('All cells are recalculated after a sheet cell has been modified.')

i'd like to yell 'alarm!!!' in capitals but don't know if that's appropriate in the community, 

it's 'nice' that this error disappears after save - load, 

other similar problems don't!, 

this fault is trapping users and waisting time of programmers and debuggers, 

the problem is still present in Version: 6.2.2.0.0+ (x64)
Build ID: 5f9104ef6f42d9d42ce3ec564affcba88889e76c
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86_64@42, Branch:libreoffice-6-2, Time: 2019-02-27_17:10:55
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded
Comment 6 Oliver Brinzing 2019-03-01 17:18:57 UTC 
please ignore Comment 4

issues *is* reproducible with lo 4.4.7.2

but not with: 
Version 3.6.7.2 (Build ID: e183d5b)
Comment 7 b. 2019-03-01 22:32:31 UTC 
acc. my experiments with old versions something got definitively broken between 4.1.6.2 and 4.2.0.1, just i didn't test this fault while having these versions installed.
Comment 8 Oliver Brinzing 2019-03-03 13:12:10 UTC Comment hidden (obsolete)
Comment 9 b. 2019-03-06 21:04:25 UTC 
imho not 'OpenGL related' same effect with anything 'OpenGL-related' switched off in tools - options - view 

reg. 

b.
Comment 10 Eike Rathke 2019-03-08 23:29:03 UTC 
The bibisected commit is totally unrelated.
However, this is related to bug 123714.
Comment 11 Commit Notification 2019-03-09 01:15:38 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 12 Oliver Brinzing 2019-03-09 13:45:35 UTC 
(In reply to Commit Notification from comment #11)
> Resolves: tdf#123714 tdf#123736 all split formula groups; tdf#120013 related

this issue is still *reproducible*

> steps to reproduce:
> - open attached spreadsheet
> - 7. edit C2 from '=SUM(A2:B2)' to '=SUM(A2:B3)', do not! touch C1, 
> - 8. change B1 from '2' to '1',
> -> C1 will not update
> -> a hard recalc is necessary

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
Comment 13 b. 2019-03-10 21:10:36 UTC 
the originally filed error was about "cells of a 'shared formula group' being excluded from autocalculate after one of the copies is edited" or similar (on my tests i'd change the last one in a column). that error is still present in ver.:

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

i re-tested and edited the third of four formulas in a column, after that all unchanged copies, above as well as below the edited cell, are excluded from autocalculate, they are calculated correctly by forced recalc, but persistent over that excluded from autocalculate ... 

b.
Comment 14 Eike Rathke 2019-03-11 16:50:52 UTC 
My bad, this shouldn't had been marked as resolved in the commit summary but related instead. Also confused things in the summary, adjusting.
Comment 15 Commit Notification 2019-03-13 21:40:39 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#123736 re-establish listeners for unshared formula groups

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 16 Commit Notification 2019-03-15 14:23:26 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

In case of sc::NoListening only SetNeedsListeningGroup(), tdf#123736 follow-up

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 17 Oliver Brinzing 2019-03-15 19:01:13 UTC 
(In reply to Commit Notification from comment #16)
> Affected users are encouraged to test the fix and report feedback.

this issue (-> comment #3) 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 18 Commit Notification 2019-03-16 01:43:31 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Related: tdf#123736 re-establish listeners also for vector unsharing

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 Commit Notification 2019-03-17 18:57:04 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Reget position_type as the block or type may have changed, tdf#123736 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 20 Commit Notification 2019-03-17 23:01:00 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Listening when grouping in ScColumn::AttachNewFormulaCells(), (tdf#123736)

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 b. 2019-03-18 03:04:23 UTC 
the 'late_redraw_issue' from comment #22 and #24 of tdf#123714 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 04:56:25, 

the rest looks fine for me, at least much better than before! 

(not yet tested all variations, but me feeling is that the fundamental bug is out) 

(will continue testing once versions with latest patches are available) 

thks @Eike!
Comment 22 Commit Notification 2019-03-18 20:09:07 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Add unit tests for tdf#123736 and related unshared/regrouped 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 23 Eike Rathke 2019-03-21 13:34:18 UTC 
*** Bug 122264 has been marked as a duplicate of this bug. ***
Comment 24 Eike Rathke 2019-03-22 11:39:22 UTC 
Fwiw, I created bug 124266 for the repaint failure.
Comment 25 Eike Rathke 2019-03-22 12:41:52 UTC 
Pending review https://gerrit.libreoffice.org/69554 for 6-2
Comment 27 b. 2019-03-25 11:03:52 UTC Comment hidden (obsolete)
Comment 28 Xisco Faulí 2019-03-25 19:09:02 UTC 
Setting as VERIFIED FIXED based on comment 17 and comment 21. ( @Oliver, @b., thanks for verifying ). If you find any related issue, please report it in a follow-up bug.

@Eike, thanks for fixing this issue!!
Comment 29 Xisco Faulí 2019-03-27 14:58:09 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Add unit tests for tdf#123736 and related unshared/regrouped listening

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 30 Eike Rathke 2019-03-27 19:14:57 UTC 
However that was copied, the summary of that commit is

Resolves: tdf#120013 tdf#123714 tdf#123736 shared formula group split
Comment 31 Xisco Faulí 2019-03-28 09:46:07 UTC 
yep, my bad!
Comment 32 Commit Notification 2019-03-28 14:23:51 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 33 b. 2019-11-21 19:03:54 UTC 
Created attachment 156009 [details]
screenshot of miscalculated sheet

would you mind checking if this error: 

https://ask.libreoffice.org/en/question/12165/calc-auto-recalc-does-not-work/ 

(still being in ver 6.4.0.0.a1-lin-x64 when working in 'threaded' calculation mode) 

might be a leftover of an incomplete fix? 

the behaviour is similar to that of this bug, hard-recalc corrects the results, but following the steps described the link produces obviously wrong results as visible in attached screenshot. 

reg. 

b.
Comment 34 b. 2019-11-21 19:12:07 UTC 
Created attachment 156010 [details]
better screenshot, here you can see the formula of one of the miscalculated cells

better screenshot, here you can see the formula of one of the miscalculated cells
Comment 35 Aron Budea 2019-11-21 22:26:35 UTC 
(In reply to b. from comment #33)
> would you mind checking if this error: 
> 
> https://ask.libreoffice.org/en/question/12165/calc-auto-recalc-does-not-work/
Please open a new bug report on that.