Bug 123736 - Changing a reference in a formula cell of a shared formula group may not rebuild all dependencies of the then split group.
Summary: Changing a reference in a formula cell of a shared formula group may not rebu...
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
: 122264 (view as bug list)
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2019-02-27 11:39 UTC by b.
Modified: 2019-11-21 22:26 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
demo recalc sum after fomula change (7.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-02-27 16:56 UTC, Oliver Brinzing
Details
screenshot of miscalculated sheet (224.43 KB, image/png)
2019-11-21 19:03 UTC, b.
Details
better screenshot, here you can see the formula of one of the miscalculated cells (231.25 KB, image/png)
2019-11-21 19:12 UTC, b.
Details

Note You need to log in before you can comment on or make changes to this bug.
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. ***