Calc doesn't recalculate formulas on cell content changes

[Paride Legovini]

libreoffice-calc 1:7.0.2-0ubuntu1 (Groovy)

The problem is better explained via steps to reproduce:

1. Start calc; you'll get a new spreadsheet.
2. Fill in the following in cells A1-A3:

  A1: 1
  A2: =A1+1
  A3: =A1-1

As expected: A2 is calculated as 2, A3 is calculated as 0.

3. Change A1 to 2 and press enter.

A2 is autocalculated to 3, but A3 remains at 0 (wrong!).

Now for the fun part: switch to a different application so LibreOffice window loses focus, the put it back in focus. A3 is now recalculated to 1.

(Partially taken from: https://ask.libreoffice.org/en/question/271324/calc-v7022-cell-autocalculate-not-working-while-window-has-focus/)

[Sorix-w (sorix-w)]

Description:
Problem: Since 7.0.2.x Calc has a problem with the automatic ReCalculation if contents/values are changed.
Is there a new setting somewhere that can cause this?

In the settings I have already deactivated the automatic recalculation and reactivated it again incl. restarting LO or the PC.

See also ASK Libre Post: https://ask.libreoffice.org/de/question/269302/calc-automatische-berechnung-geht-nicht-mehr/

Actual Results:
critical, because not automatically recalculated

Expected Results:
as in previous version, that all formulas (with activated function) are also reliably recalculated.

Reproducible: Always

User Profile Reset: Yes

OpenGL enabled: Yes

Additional Info:
Version: 7.0.2.2 (x64) Build ID: 8349ace3c3162073abd90d81fd06dcfb6b36b994 CPU threads: 8; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: de-DE
Calc: CL

also

Version: 7.1.0.0.alpha0+ (x64)
Build ID: f266feaebea39668392e3a3830e20e4670344658
CPU threads: 12; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: en-GB
Calc: CL

[Sorix-w (sorix-w)]

Created attachment 166068
Demo File from Screenshot

[Sorix-w (sorix-w)]

Created attachment 166069
Screenshot

[Telesto (telesto)]

Thanks for reporting

It's working fine on MacOS
Version: 7.0.0.2
Build ID: c01aa64b6c3d89ebe5fe69c28c7adb24eb85249c
CPU threads: 4; OS: Mac OS X 10.12.6; UI render: default; VCL: osx
Locale: nl-NL (nl_NL.UTF-8); UI: en-US
Calc: threaded

[Sorix-w (sorix-w)]

(In reply to Telesto from comment #3)
> Thanks for reporting
>
> It's working fine on MacOS
> Version: 7.0.0.2
> Build ID: c01aa64b6c3d89ebe5fe69c28c7adb24eb85249c
> CPU threads: 4; OS: Mac OS X 10.12.6; UI render: default; VCL: osx
> Locale: nl-NL (nl_NL.UTF-8); UI: en-US
> Calc: threaded

you're right.
it seems to be only from version 7.0.2.x and higher

[Uwe-8 (uwe-8)]

Reproduced on:

Version: 7.0.2.2, Build ID: 8349ace3c3162073abd90d81fd06dcfb6b36b994
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US, Calc: threaded

Steps to reproduce.
 - Add a new value into cell B1 of the attached file
 - Check value of cell B4

Result: Value of cell B4 doesn't change automatically, while B2 and B3 recalculate
Expected Result: B4 autocalculates

Reset user profile: Yes

[Uwe-8 (uwe-8)]

Created attachment 166090
Testcase "Reproduce-Bug-137248.ods" File (as per comment)

[Telesto (telesto)]

(In reply to Uwe Auer from comment #6)
> Created attachment 166090 [details]
> Testcase "Reproduce-Bug-137248.ods" File (as per comment)

Repro
Version: 7.1.0.0.alpha0+ (x64)
Build ID: 7f16cabf00daa30e9284d2fb2494bd341352c25e
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: nl-NL (nl_NL); UI: en-US
Calc: CL

fine in
Version: 7.0.0.0.beta1+ (x64)
Build ID: 2891e91a513520d68ea2b8c59c14335861a15253
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: default; VCL: win
Locale: nl-NL (nl_NL); UI: en-US
Calc: CL

[Ming-v-hua (ming-v-hua)]

*** Bug 137275 has been marked as a duplicate of this bug. ***

[Telesto (telesto)]

Created attachment 166136
Bibisect log

Bisected to:
author Luboš Luňák <email address hidden> 2020-09-04 15:51:07 +0200
committer Luboš Luňák <email address hidden> 2020-09-10 11:15:47 +0200
commit 4f36f2ccab6286ec09480caea602c0fa19195736 (patch)
tree 0d3156c2f322983fa30d3a6f4600be5102e2718e
parent 3f620e74aa00e34f773d97e9480a6532d9e8863e (diff)
detect if a cell still needs interpreting after Interpret()
https://bugs.documentfoundation.org/attachment.cgi?id=51878
after hard-recalc asserts because of not having cell dependencies
non-dirty for threaded calculation. This is because Interpret()
actually sometimes returns without computing a value for the cell,
e.g. when it backs out because of a need to do iteration.
This is handled when Interpret() is called from InterpretTail(),
but ScDependantsCalculator does not handle this and considers
all cells interpreted, unless it detected a problem.
We've already fixed a number of such bugs, and given that there
are still problematic corner cases after all this time, add code
simply detecting this generic problem and avoiding threading
in that case, with a SAL_WARN. This does not fix the problem
itself, but at least now it's handled.

https://cgit.freedesktop.org/libreoffice/core/commit/?id=4f36f2ccab6286ec09480caea602c0fa19195736

[Telesto (telesto)]

Adding CC: to Luboš Luňák

[Telesto (telesto)]

*** Bug 137312 has been marked as a duplicate of this bug. ***

[Ming-v-hua (ming-v-hua)]

*** Bug 137415 has been marked as a duplicate of this bug. ***

[Eike Rathke (erack)]

This actually is a repaint problem, Pgdn and PgUp or Shift+Ctrl+R to redraw the grid displays the recalculated value. The commit mentioned somewhere forgets to (re)set a changed flag, a debug version in terminal displays

sc/source/core/data/column4.cxx:1695: Internal error, cell 2!R3C3 failed running Interpret(), not allowing threading

so it hits exactly that changed code.

[Eike Rathke (erack)]

Taking.

[Eike Rathke (erack)]

Or the dirty cell is interpreted during the next repaint.. however..

[Libreoffice-commits (libreoffice-commits)]

Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/59f86333f3fce091177d1cfb9363aa81686aa497

Resolves: tdf#137248 Fix wrong condition breaking non-group interpret

It will be available in 7.1.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.

[Eike Rathke (erack)]

Pending review https://gerrit.libreoffice.org/c/core/+/104289 for 7-0

[Vlb1 (vlb1)]

Will this issue also be resolved for 7.0. *?

[Uwe-8 (uwe-8)]

*** Bug 137467 has been marked as a duplicate of this bug. ***

[Telesto (telesto)]

(In reply to VLB from comment #18)
> Will this issue also be resolved for 7.0. *?

Of course ;-). That's the essence of comment 17

[Libreoffice-commits (libreoffice-commits)]

Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/701008631ee257b4c7714ac9e2689c5ecc45bff8

Resolves: tdf#137248 Fix wrong condition breaking non-group interpret

It will be available in 7.0.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.

[Mikekaganski (mikekaganski)]

*** Bug 137518 has been marked as a duplicate of this bug. ***

[Mikekaganski (mikekaganski)]

*** Bug 137515 has been marked as a duplicate of this bug. ***

[Mikekaganski (mikekaganski)]

*** Bug 137521 has been marked as a duplicate of this bug. ***

[Aron Budea (baron-z)]

*** Bug 137557 has been marked as a duplicate of this bug. ***

[Aron Budea (baron-z)]

*** Bug 137552 has been marked as a duplicate of this bug. ***

[Aron Budea (baron-z)]

*** Bug 137557 has been marked as a duplicate of this bug. ***

[Aron Budea (baron-z)]

*** Bug 137540 has been marked as a duplicate of this bug. ***

[Aron Budea (baron-z)]

*** Bug 137493 has been marked as a duplicate of this bug. ***

[Aron Budea (baron-z)]

*** Bug 137060 has been marked as a duplicate of this bug. ***

[Andristf (andristf)]

Downloaded 7.0.3 Build b6be59636a142f98970d39aadff8445e1d0c39b7
This fixed my problem with not automatically updating some cells.
Many thanks

[Stefan-lange-ka-d (stefan-lange-ka-d)]

see comment 4 of bug 137060 (succesful test with LOdev 7.0.3 and LOdev 7.1.0): The problem is solved by the patch!

[Xiscofauli (xiscofauli)]

Setting to VERIFIED

[Heather Ellsworth (hellsworth)]

Description:
This was reported by a user on launchpad: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1900454

The problem is better explained via steps to reproduce (see steps below) but basically if a cell in calc is a *calculated value* based off of another cell, and the original cell's value changes, then that change is not reflected in the calculated cell's value until the cursor focus goes to another application and comes back to libreoffice.

Steps to Reproduce:
1. Start calc; you'll get a new spreadsheet.
2. Fill in the following in cells A1-A3:

  A1: 1
  A2: =A1+1
  A3: =A1-1
As expected: A2 is calculated as 2, A3 is calculated as 0.

3. Change A1 to 2 and press enter. A2 is autocalculated to 3, but A3 remains at 0 (wrong!).
4. Now for the fun part: switch to a different application so LibreOffice window loses focus, the put it back in focus. A3 is now recalculated to 1.

Actual Results:
The cell's values are updated only if the cursor focus goes to another application and then back to libreoffice.

Expected Results:
The cell's values should be updated immediately.

Reproducible: Always

User Profile Reset: No

Additional Info:
This was first reported by a user running Ubuntu 20.10 with libreoffice 7.0.2-0ubuntu1 (repackaged 7.0.2.2 release).

I've also reproduced this on a debian testing system with the TDF LibreOffice_7.0.2_Linux_x86-64_deb.tar.gz

[Heather Ellsworth (hellsworth)]

Thank you for reporting this issue to help improve Ubuntu!

I've confirmed the issue in a groovy vm with 7.0.2-0ubuntu1, I also confirmed the issue in a debian testing vm with the upstream 7.0.2.2 packages, and I opened an upstream bug.

[Miguelangelrv (miguelangelrv)]

*** Bug 137600 has been marked as a duplicate of this bug. ***

[Eike Rathke (erack)]

*** This bug has been marked as a duplicate of bug 137248 ***

[Eike Rathke (erack)]

*** Bug 137599 has been marked as a duplicate of this bug. ***

[Heather Ellsworth (hellsworth)]

It seems that this bug was already reported upstream (https://bugs.documentfoundation.org/show_bug.cgi?id=137248) and has been reported as fixed with a target for the 7.1.0 release.

We can still leave this bug open though to verify it's fix when 7.1.0 is released.

[Ming-v-hua (ming-v-hua)]

*** Bug 137606 has been marked as a duplicate of this bug. ***

[Xiscofauli (xiscofauli)]

*** Bug 137659 has been marked as a duplicate of this bug. ***

[Xiscofauli (xiscofauli)]

*** Bug 137565 has been marked as a duplicate of this bug. ***

[Eike Rathke (erack)]

*** Bug 137706 has been marked as a duplicate of this bug. ***

[Paride Legovini (paride)]

tdf #137599 was marked as a duplicate of tdf #137248. I updated the upstream bug to watch.

[Sorix-w (sorix-w)]

the failure seems to be fixed for me, thx a lot ☺
AutoCalc is working for me. will do some more test under different scenarios.
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Tested under:
Version: 7.0.3.1 (x64)
Build ID: d7547858d014d4cf69878db179d326fc3483e082
CPU threads: 12; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: CL
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

[Ilmari-lauhakangas (ilmari-lauhakangas)]

*** Bug 125052 has been marked as a duplicate of this bug. ***

[Ming-v-hua (ming-v-hua)]

*** Bug 137775 has been marked as a duplicate of this bug. ***

[Ming-v-hua (ming-v-hua)]

*** Bug 137784 has been marked as a duplicate of this bug. ***

[Xiscofauli (xiscofauli)]

*** Bug 137797 has been marked as a duplicate of this bug. ***

[Uwe-8 (uwe-8)]

*** Bug 137817 has been marked as a duplicate of this bug. ***

[Mikekaganski (mikekaganski)]

*** Bug 137843 has been marked as a duplicate of this bug. ***

[Uwe-8 (uwe-8)]

*** Bug 137895 has been marked as a duplicate of this bug. ***

[Kchingx (kchingx)]

how can this be a duplicate when it is not working on libre 7.0.2.2?
it was working on 7.0.1 upon upgrade it did not work anymore.
so do i downgrade?

[Sorix-w (sorix-w)]

(In reply to kchingx from comment #48)
> how can this be a duplicate when it is not working on libre 7.0.2.2?
> it was working on 7.0.1 upon upgrade it did not work anymore.
> so do i downgrade?

upgrade to newest Version 7.0.3.1 and check your scenario again.
If the BUG is still present you can give a feedback again please.

[Uwe-8 (uwe-8)]

(In reply to kchingx from comment #48)
> how can this be a duplicate when it is not working on libre 7.0.2.2?
> it was working on 7.0.1 upon upgrade it did not work anymore.
> so do i downgrade?

**Your** bug report at https://bugs.documentfoundation.org/show_bug.cgi?id=137895 is a duplicate report of this one, because this one already describes what you reported once more and if you carefully read this report, you'll notice that 7.0.3 (which is already released) will fix the bug and hence your problem reported in your report.

[Ming-v-hua (ming-v-hua)]

*** Bug 137927 has been marked as a duplicate of this bug. ***

[Aron Budea (baron-z)]

*** Bug 137941 has been marked as a duplicate of this bug. ***

[Xiscofauli (xiscofauli)]

*** Bug 137946 has been marked as a duplicate of this bug. ***

[Miguelangelrv (miguelangelrv)]

*** Bug 138080 has been marked as a duplicate of this bug. ***

[Miguelangelrv (miguelangelrv)]

*** Bug 138161 has been marked as a duplicate of this bug. ***

[Uwe-8 (uwe-8)]

*** Bug 138166 has been marked as a duplicate of this bug. ***

[Ming-v-hua (ming-v-hua)]

*** Bug 138415 has been marked as a duplicate of this bug. ***

[Ming-v-hua (ming-v-hua)]

*** Bug 138696 has been marked as a duplicate of this bug. ***

[Otis5842 (otis5842)]

Calc - AutoCalculate malfunction, always F9 (ReCalculate) necessary Product: https://amongus2.io LibreOffice Version: 7.1.0.0.alpha0+ Master Hardware