Bug Hunting Session
Bug 119343 - EDITING: copied and pasted cells containing formulas are not recalcuated (2)
Summary: EDITING: copied and pasted cells containing formulas are not recalcuated (2)
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.3.0
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2018-08-18 10:03 UTC by tagishsimon
Modified: 2019-05-28 14:45 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Calc spreadsheet showing formula calculation error in columns M and N (76.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-08-18 10:03 UTC, tagishsimon
Details
What I'm seeing - relates to my comment at 4 (168.32 KB, image/png)
2018-08-18 12:14 UTC, tagishsimon
Details
comparison LibreOffice 6.1 and LibreOffice 6.3 (373.81 KB, image/png)
2019-05-06 13:45 UTC, Xisco Faulí
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tagishsimon 2018-08-18 10:03:34 UTC
Created attachment 144263 [details]
Calc spreadsheet showing formula calculation error in columns M and N

Note 1: First, I note https://bugs.documentfoundation.org/show_bug.cgi?id=67489 - an old fixed problem similar to this one, at least to the extent they share a title.

Note 2: I'm getting an error trying to upload my attachment; 

I attach a spreadsheet in which the calculations of formulii on columns M and N are manifestly wrong.

In both columns, the formulas are just trying to fetch a single value from a cell on the same row - e.g. =+J2

The method to create the issue was
1. In cell M2, enter a formula +J2
2. CMD-C to copy the formula 
3. Select many cells in the M column
4. CMD-V to paste the formula

Expected result:
- The formula copies and calculates values

Actual results
- The formula copies, but the values displayed for all cells in a column is that which would be correct for the final row of the column - in the attached example, M295 is correct and gives its values to M2-M294. N108 is correct and gives its value to N2-N107, which are incorrect.

You can try this at home:
- If you open the file
- CMD-C the formula at the bottom of the list (e.g. M295)
- CMD-V the formula to the next cell down (e.g. into M296)
then the values for the entire column change to those for M295.
Comment 1 tagishsimon 2018-08-18 10:04:57 UTC
oops. file attached. sorry to confuse
Comment 2 Jean-Baptiste Faure 2018-08-18 12:00:56 UTC
I do not understand where is the problem: the copy of the cell works for me as expected.

What do you expect from the formula =+J2 ? For me it is only a copy of J2

Status set to NEEDINFO, please set it back to UNCONFIRMED once requested
informations are provided.

Best regards. JBF
Comment 3 tagishsimon 2018-08-18 12:14:31 UTC
Created attachment 144265 [details]
What I'm seeing - relates to my comment at 4
Comment 4 tagishsimon 2018-08-18 12:16:56 UTC
After the copy of the formulas, I have the following formulas in M2 to M4:

=+J2
=+J3
=+J4

In each case, what is being displayed in the value of J295.

As you can see from the screengrab, columns M and N display a single value each. But their formulas are all as per M2-M4 above ... each is looking for the column J value on the same row. And you can see that the column J values all differ one from eachother.
Comment 5 tagishsimon 2018-08-18 12:17:28 UTC
Bah. In each case, what is being displayed is the value of J295.
Comment 6 raal 2018-08-19 07:59:25 UTC
I see the problem in your file. After ctrl+shift+F9 are values correct. I cannot reproduce the problem with Version: 6.2.0.0.alpha0+
Build ID: 4cb69cf33b5bf17030bcd263fe31258177c76d5e
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-08-18_05:13:44

steps:
- Ctrl-C the formula at the bottom of the list (e.g. M295)
- Ctrl-V the formula to the next cell down (e.g. into M296)
then the values for the entire column change to those for M295.
Comment 7 Alex Thurgood 2018-08-20 08:43:08 UTC
This is WFM with

Version: 6.2.0.0.alpha0+
Build ID: d99af1a67cbe4e23661467843e7d72194b45b313
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: threaded
Comment 8 Alex Thurgood 2018-08-20 09:18:58 UTC
Confirming that I can reproduce the problem with

Version: 6.1.0.3
Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded
Comment 9 Alex Thurgood 2018-08-20 09:19:29 UTC
The problem is not present with LO 606.
Comment 10 Xisco Faulí 2018-08-21 12:52:50 UTC
After

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

author	Eike Rathke <erack@redhat.com>	2018-07-18 22:04:09 +0200
committer	Eike Rathke <erack@redhat.com>	2018-07-18 23:21:26 +0200
commit	a014a9bcf071229eef93c307c705a4c639635bd5 (patch)
tree	1ac278dc5f7816ee7190712e04902cdc025030bf
parent	5bfdbc664072dbf2731365b237b60eba2b3e03fb (diff)
Do not force all string results to be recalculated if no style set

Bisected with: bibisect-linux64-6.2

I see the M and N column containing the same value in all cells. Don't know whether this is the expected behaviour though...

Adding Cc: to Eike Rathke
Comment 11 tagishsimon 2018-08-21 14:18:29 UTC
No it is not the expected behaviour. 

On each row x, M and N both have a formula +Jx and so M & N should show the column J value from the same row. 

Columns M & N should not change all their values when the relative formula is copied downwards by a cell.

Thing is, we depend on spreadsheet results, and right now, when this issue occurs it produces garbage ... which if not noticed can have very unfortunate consequences.

I have not characterised when & why it happens :(
Comment 12 Eike Rathke 2018-08-22 11:42:34 UTC
(In reply to Xisco Faulí from comment #10)
> https://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=a014a9bcf071229eef93c307c705a4c639635bd5
Can't be if it occurs in 6.1.0.3, that change is not in 6-1
Comment 13 Xisco Faulí 2018-08-23 09:30:48 UTC
(In reply to Eike Rathke from comment #12)
> (In reply to Xisco Faulí from comment #10)
> > https://cgit.freedesktop.org/libreoffice/core/commit/
> > ?id=a014a9bcf071229eef93c307c705a4c639635bd5
> Can't be if it occurs in 6.1.0.3, that change is not in 6-1


Ouch, you're right! I get repo 6.1 and 6.2 pointing to different commits, weird!
According to bibisect-linux64-6.1, regression introduced by:

https://cgit.freedesktop.org/libreoffice/core/commit/?h=libreoffice-6-1&id=456ccd8a3d1753e76fc4f6332de122cc3a187990

author	Luboš Luňák <l.lunak@collabora.com>	2018-06-15 18:22:12 +0200
committer	Luboš Luňák <l.lunak@collabora.com>	2018-06-20 10:14:15 +0200
commit	456ccd8a3d1753e76fc4f6332de122cc3a187990 (patch)
tree	d17b6a65c36c58e57dffabd278afb59ff49c3618
parent	f5d41ad697bf9591925a19eee656a2c88321745e (diff)
clean up calc'c choosing between threading and OpenCL
Now there's just one place to decide what is used first,
in InterpretFormulaGroup(). Place in other places now checks both threading
and OpenCL, which should be cheap, but it also allows e.g. falling
back from OpenCL to threading if it doesn't work out for a specific
formula group.

Addding Cc: to Luboš Luňák
Comment 14 Xisco Faulí 2018-08-23 09:33:07 UTC
Issue reproduced either with threading enabled or disabled...
Comment 15 tagishsimon 2018-11-01 07:37:20 UTC Comment hidden (no-value)
Comment 16 Xisco Faulí 2019-04-23 17:31:55 UTC
Still reproducible in

Version: 6.3.0.0.alpha0+
Build ID: 053b1417137b0cdec4e4fed7ae0c57cf67ff2698
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

@Dennis Francis, I thought you might be interested in this issue...
Comment 17 Dennis Francis 2019-05-02 14:25:45 UTC
@Xisco : There is some confusion in reproducing the bug, because the attachment is not really a reproducer document of the bug, but is the result of a save after the reproducing the bug on the suspect version of Calc. To reproduce the bug, we need to do a hard-recalc after opening the attachment... (A side problem I observed was on file open of the attachment, some versions of Calc does some kind of recalc automatically while some does not(master). This sounds like a different issue though.) ...and then follow these steps :

1. Ctrl-C the formula at the bottom of the list (e.g. M295)
2. Ctrl-V the formula to the next cell down (e.g. into M296)

Now at least the column M will be messed up(same value for all rows) if you are using some particular Calc version.

I think I know why this happens. This happens starting with my threading related optimization commit 

fbcdce22bce6d6d1ba5a9e90b642ea08fc09916a "Avoid ScTokenArray thrash"

*till* software interpreter was removed by commit
089a4f245325a5be5cd5951d85305d791b4d9cb6 "remove Calc's software interpreter"
or at least some patch before this which stopped things from using software interpreter.

So >= 6.2 are not affected. In fact I'm not able to repro this on current master to 6.3.

https://gerrit.libreoffice.org/plugins/gitiles/core/+/libreoffice-6-1-6/sc/source/core/tool/formulagroup.cxx shows that software interpreter code is still there in 6.1. 

But it seems there was a commit in 6.1 that stopped software interpreter from being used so I cannot repro this in 

Version: 6.1.5.2
Build ID: 6.1.5.2-5.fc29
CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.utf8); Calc: group threaded

Could you please confirm these ?
Comment 18 Xisco Faulí 2019-05-06 13:45:43 UTC
Created attachment 151207 [details]
comparison LibreOffice 6.1 and LibreOffice 6.3
Comment 19 Xisco Faulí 2019-05-06 13:46:34 UTC
Hi Dennis,
Please check the attached screenshot. As you can see, column M is updated at import time in LibreOffice 6.1 but not in LibreOffice 6.3
Comment 20 Dennis Francis 2019-05-06 15:26:39 UTC
@Xisco, The original issue reported is about editing, not about file open/save. In my previous comment I was talking about the editing bug, where it happens during the steps :

- Ctrl-C the formula at the bottom of the list (e.g. M295)
- Ctrl-V the formula to the next cell down (e.g. into M296)
then the values for the entire column change to those for M295.

What your screenshot shows during file-open is a different issue. This is because 6.1 does recalc while loading the file for *some weird reason*, while 6.3 does not do that (which is expected, because it uses the "cached results" in the file, but due to the original "edit" bug the "cached results" stored in the file are wrong so it is not the fault of 6.3). To prove that if you open the file in 6.3, do a recalc, save and on reopening the new file there wont be any issue in column M and N.
Comment 21 Dennis Francis 2019-05-17 17:54:53 UTC
I believe the original bug reported here is already fixed in master and release branches. Need someone else to confirm my findings in comments 17 & 20 and change the status accordingly. For now I will leave it to NEW.
Comment 22 raal 2019-05-23 17:18:25 UTC
(In reply to Dennis Francis from comment #21)
> I believe the original bug reported here is already fixed in master and
> release branches. Need someone else to confirm my findings in comments 17 &
> 20 and change the status accordingly. For now I will leave it to NEW.

I cannot reproduce:
- Ctrl-C the formula at the bottom of the list (e.g. M295)
- Ctrl-V the formula to the next cell down (e.g. into M296)
then the values for the entire column change to those for M295.

 with Version: 6.3.0.0.alpha1+
Build ID: 38ac0586448d4f07811b139f62f62686b029feba
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: cs-CZ (cs_CZ.UTF-8); UI-Language: en-US
Calc: threaded

Closing the bug.
Comment 23 Commit Notification 2019-05-28 14:45:08 UTC
Zdeněk Crhonek committed a patch related to this issue.
It has been pushed to "master":

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

uitest for bug tdf#119343

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.