Bug 142010

Summary: Inserting a column in a sheet causes erroneus rewriting of the formulas in a old Excel 97-2003 xls formatted file
Product: LibreOffice Reporter: Aldo <master>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: VERIFIED FIXED    
Severity: normal CC: 79045_79045, aron.budea, himajin100000, nemeth
Priority: medium Keywords: bibisected, bisected, filter:xls, regression
Version: 5.0.0.5 release   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=89281
Whiteboard: target:7.3.0 target:7.2.3
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 109072    
Attachments: original file
Error after saving

Description Aldo 2021-05-01 08:39:19 UTC 
Description:
In the first attached file (potenze_ok.xls), if I insert a column in the first position "A", some formulas do not get rewritten correctly. Look for instance at cell F74.
It contains the formula "=RESTO(F$71+$C74;9)" it should be rewritten "=RESTO(F$71+$D74;9)" after the column insertion. Instead it becomes: "=RESTO(G$71+$K74;9)" with the "K" instead of "D". 
Same behaviour also in other cells but not to all.
It seems not to happen if I first save the file and after make the column insertion.
It also seems that if after the insertion I do a UnDo "CTRL-Z an ReDo the operation the result is correct.
If instead after the insertion I save the file, it remains corrupted. See the second attachment (potenze_err.xls)
Being an old file I'm not able to say if it is worth to investigate the matter.

Steps to Reproduce:
1.Open the file and look at the formula in G74
2.Insert a new column in A.
3.Look at the rewritten formula in G74.

Actual Results:
formula before:   =RESTO(F$71+$C74;9)
Expected formula: =RESTO(G$71+$D74;9)
Actual formula:   =RESTO(G$71+$K74;9)

Expected Results:
The result formula gives error.


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Version: 7.1.0.3 / LibreOffice Community
Build ID: f6099ecf3d29644b5008cc8f48f42f4a40986e4c
CPU threads: 8; OS: Linux 5.11; UI render: default; VCL: gtk3
Locale: it-IT (it_IT.UTF-8); UI: it-IT
Calc: threaded
Comment 1 Aldo 2021-05-01 08:42:17 UTC 
Created attachment 171555 [details]
original file
Comment 2 Aldo 2021-05-01 08:42:56 UTC 
Created attachment 171556 [details]
Error after saving
Comment 3 Roman Kuznetsov 2021-10-23 15:28:51 UTC 
repro in 7.3 and in

Версия: 4.4.7.2
ID сборки: f3153a8b245191196a4b6b9abd1d0da16eead600
Локаль: ru_RU

but not in

Version: 4.3.7.2
Build ID: 8a35821d8636a03b8bf4e15b48f59794652c68ba

it's a regression

If resave this XLS file to XLSX in the MS Excel and open that XLSX in LO, then LO works as should
Comment 4 Aron Budea 2021-10-24 06:13:09 UTC 
Bibisected to the following commit using repo bibisect-50max. Adding CC: to László Németh.

https://cgit.freedesktop.org/libreoffice/core/commit/?id=b18b5b7edf3d14ef5f0efe53e367f88a423088c4
author		László Németh <laszlo.nemeth@collabora.com>	2015-02-10 10:25:13 +0100
committer	László Németh <laszlo.nemeth@collabora.com>	2015-02-10 11:10:58 +0100

tdf#89281 fix performance regression of XLS import
Comment 5 László Németh 2021-10-26 07:51:50 UTC 
tdf#142010 XLS import: fix bad calculation after editing

Inserting a column messed up formulas in the test
document of tdf#142010. This was a regression
resulted by the tdf#89281 fix for a performance
regression in XLS import related to shared formulas.

Revert of commit ba686b9bd2596811141e4028947334f10799c356
"tdf#89281 fix performance regression of XLS import - cleanup"
and commit b18b5b7edf3d14ef5f0efe53e367f88a423088c4
"tdf#89281 fix performance regression of XLS import".
Comment 6 Commit Notification 2021-10-26 07:52:29 UTC 
László Németh committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/7d5703ca50d719fbd82615fb81da7949fe163ae5

tdf#142010 XLS import: fix bad calculation after editing

It will be available in 7.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 7 László Németh 2021-10-26 07:53:05 UTC 
@Aldo: thanks for the detailed bug report.

@Áron: thanks for bibisecting and CCing the problem.
Comment 8 Commit Notification 2021-10-26 20:29:11 UTC 
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/eb664612aba4d7d8d5cf8d5a448f12be55da59ba

tdf#142010: sc_uicalc: Add unittest

It will be available in 7.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 9 Commit Notification 2021-10-26 22:08:20 UTC 
László Németh committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/5862ea89fe285bf83b9a7658906ed3e0b8d86853

tdf#142010 XLS import: fix bad calculation after editing

It will be available in 7.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 10 NISZ LibreOffice Team 2021-11-04 09:58:53 UTC 
Verified in:
Version: 7.3.0.0.alpha1+ (x64) / LibreOffice Community
Build ID: c7500945fc5d5bd2130a2d38be0bd4b15445cd90
CPU threads: 8; OS: Windows 10.0 Build 18363; UI render: Skia/Vulkan; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: CL