Bug 153790 - Unnecessary 3D sheet reference in end part of reference after cut and paste
Summary: Unnecessary 3D sheet reference in end part of reference after cut and paste
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.6.0 target:7.5.4
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Cell-Formula Cut-Copy
  Show dependency treegraph
 
Reported: 2023-02-23 14:05 UTC by Ruud Mantingh
Modified: 2023-09-29 00:24 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Ruud Mantingh 2023-02-23 14:05:57 UTC
Consider a cell formula that refers to a range on another sheet:

E.g.
=INDEX($Sheet1.B:B,MATCH(A1,$Sheet1.A:A,0))

If I work with the workbook for a while it changes the formula to:

=INDEX($Sheet1.B:$Sheet1.B,MATCH(A1,$Sheet1.A:$Sheet1.A,0))

I'm not sure at what point it starts doing this and what actions of mine led up to it but it always happens and is very annoying as it makes formulas unnecessarily long and unreadable.

If I close the workbook and re-open it it's back to the normal short form.
Comment 1 Stéphane Guillou (stragu) 2023-05-09 22:13:45 UTC
I could reproduce by using the following steps:
1. Open Calc
2. In cell A1, enter the formula: =SUM($Sheet1.B1:C1)
3. Optional check: Copy the A1 cell, paste in A2: formulas stay the same
4. Cut the A1 cell, paste in A3

Result:
Both A2 and A3 are updated to repeat the sheet reference:
=SUM($Sheet1.B:$Sheet1.B)

Tested in:

Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 002ae41bb6088002ba3ed0188ac822fb823a23f9
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Already in 6.0.0.3, but it's a regression as OOo 3.3 did not have that issue.

Note that, if using relative references (no dollar sign in step 2), the formulas are all _simplified_ to remove the superfluous sheet reference.
Comment 2 ady 2023-05-10 12:06:06 UTC
FWIW, also in 5.4, but not in 5.2.
Comment 3 raal 2023-05-15 15:34:46 UTC
This seems to have begun at the below commit in bibisect repository/OS /bibisect-win32-5.4.
Adding Cc: to Eike Rathke ; Could you possibly take a look at this one?
Thanks
 c262f9a0e3afb9b14acf53338f82089386bd4b84 is the first bad commit
commit c262f9a0e3afb9b14acf53338f82089386bd4b84
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Sun Feb 12 16:18:16 2017 -0800

    source aeb465a458fc7ba02e3b1ca6b4193488defef511

https://git.libreoffice.org/core/+/aeb465a458fc7ba02e3b1ca6b4193488defef511
yet more 3D flag to preserve cases, tdf#103890 follow-up
Comment 4 Commit Notification 2023-05-16 22:42:51 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#153790 Suppress duplicated end part sheet reference, tdf#103890

It will be available in 7.6.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 5 Commit Notification 2023-05-17 12:03:16 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-5":

https://git.libreoffice.org/core/commit/41fb05d4b21ab8fd5a522ff8eba6844333fbf4cf

Resolves: tdf#153790 Suppress duplicated end part sheet reference, tdf#103890

It will be available in 7.5.4.

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 6 Commit Notification 2023-05-17 18:03:10 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/45229e679aead3c1b5f91069d2a87e9597eb93b5

tdf#153790: sc_uicalc: Add unittest

It will be available in 7.6.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 Stéphane Guillou (stragu) 2023-05-19 16:45:10 UTC
Verified as fixed in:

Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: 261063e69e80193ad563e086c515fd6e22e48464
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Thanks everyone for the report, bisect, fix and test!