Bug 77018 - Formulas with external references incorrectly updated when sorted
Summary: Formulas with external references incorrectly updated when sorted
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:4.4.0 target:4.3.0 target:...
Keywords:
: 64647 (view as bug list)
Depends on:
Blocks: 85490
  Show dependency treegraph
 
Reported: 2014-04-03 17:31 UTC by sviatoslav
Modified: 2014-12-04 16:59 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Zip file containing 2 sample documents where the error is clearly evident (144.25 KB, application/x-zip-compressed)
2014-04-03 17:31 UTC, sviatoslav
Details
Zip containing 2 files linked. Open test.ods (37.88 KB, application/zip)
2014-05-29 08:29 UTC, Laurent BP
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sviatoslav 2014-04-03 17:31:55 UTC
Created attachment 96861 [details]
Zip file containing 2 sample documents where the error is clearly evident

Problem description: 
Formula in cells referencing cells in another linked spreadsheet are incorrectly updated when these cells are sorted. The formulas are automatically changed to reference completely different and seemingly unrelated cells in that other spreadsheet. This bug was also noted when rows are moved or added, but the issue is demonstrated below when data is sorted.

Steps to reproduce:
1. Download the attached zip file which contains 2 .ods spreadsheets and extract both files into 1 folder.

2. Open FS.ods and say "Yes" to update links. (Ensure links are updated and no error is given. The only spreadsheet that is linked is "Bookkeeping.ods")

3. Open tab "IS" (All references to ranges that follow are in reference to this tab only, unless indicated otherwise)

4. Take mental note of all formulas in the range B12:B24. You will notice they all reference some cell in row 403 of the "Bookkeeping.ods" spreadsheet.

5. Select range A12:AMJ24 (all rows in the "Operating Expenses" section)

6. Click Data >> Sort

7. Select "Sort key 1" to be Column A and all other sort keys as undefined (sorting by the first column) and click OK

8. You will now notice that values in range B12:B24 are all changed. On my machine they are all zero. You will also note that all references to row 403 are now references to various other rows, which is definitely incorrect behavior.

Current behavior:
See step 8 above.

Expected behavior:
Expected all references to remain the same as before the sort i.e. referencing some particular cell in Bookkeeping.ods but always row 403. The values should not change after the sort.
              
Operating System: Windows 7
Version: 4.2.2.1 release
Comment 1 Dominique Boutry 2014-04-08 10:23:20 UTC
Hi. Reproduced (same LibO version, same OS)

It looks like an attempt to maintain the moved cell taking into account the move during the sort. Eg: "M Advertizing", previously referencing AR403, moves from row 12 to row 24 after the sort and then references AR391 (391 = 403 - (24-12)). Wrong for 2 reasons :
- the reference to cell "=file...AR403", even typed without the "$", have no relative position to cell in an other file,
- it seemed to me agreed that cells moved by a "sort" have not their references updated (contrary to explicit moves).

An other view on unclear situation after a move :
- before the sort, fill C12 with "=C34", copy C12 to C13:C24,
- look at the content of C12:C24 after the sort : inexplicably unchanged, despite it was in the selected area to be sorted.

IMHO there are no more BUGS related to the sort feature, there is rather an OBSCUR and FLUCTUATING specification.
Comment 2 Laurent BP 2014-05-26 20:00:14 UTC
Reproduce with many versions:
- LibO 3.3.0.4 till 4.3.0.beta1
- OOo 3.1.1, 3.3.0
- AOO 3.4.0

NOT reproduce with AOO 3.4.1, AOO 4.0.0, AOO 4.0.1, AOO 4.1.0

So the bug was fixed by AOO 3.4.1, but not in LibO project.

Change version accordingly.
Comment 3 Laurent BP 2014-05-29 08:29:47 UTC
Created attachment 100089 [details]
Zip containing 2 files linked. Open test.ods

I propose a new simpler test file which compares behavior of sorting with absolute or relative references, with external/internal to other sheet/internal same sheet references.

1. Extract both files in the same directory
2. Open test.ods
3. Accept to update links
4. Select A15:D20 or A23:D28 (if you want to test absolute or relative references)
5. Sort descending according to column A

Expected behavior:
With relative external references or relative internal references to other sheet, formulas should be changed to reflect the new order.

Current behavior:
Formulas with relative external references are not updated.
For relative internal to other sheet references, behavior changed between LibO 4.2.4.2 and LibO 4.3.0.0 beta1: with 4.2 and prior, formulas ARE updated; with 4.3 formulas are no more updated (but this may be another bug).

No problem with absolute references.
For relative internal references to the same sheet, formulas can be updated only if source is also sorted: move Feuille1.A1:A6 to Feuille1.E15:E20 and select A15:E20 before sorting. Then formulas are correctly updated.
Comment 4 Laurent BP 2014-05-29 08:39:34 UTC
As AOO solved this bug between AOO340 and AOO341, I tried to find the code modification with issue 110588
https://issues.apache.org/ooo/show_bug.cgi?id=110588
But AOO commit 55737a716866 does not concern this point:
https://bitbucket.org/mst/ooo340/commits/55737a7168666bfee5f113ec2655b69d174de77e
as it deals with issue 113183
https://issues.apache.org/ooo/show_bug.cgi?id=113183
In LibO commits, I found this commit concerning issue 110588
http://cgit.freedesktop.org/libreoffice/core/commit/?id=fbd12549299a2fccab00e3f5a9d0bac161170b75&context=12
When checking LibO recent code, it appears that this commit was reverted:
http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/token.cxx#520
http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/token.cxx#2236
I tried to re-revert it, but it changed nothing on LibO 4.3 behavior in this case.
Comment 5 Laurent BP 2014-06-03 13:37:05 UTC
*** Bug 64647 has been marked as a duplicate of this bug. ***
Comment 6 Commit Notification 2014-07-11 21:20:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=db0a4c2575ffba1c170d74882b0f5a6229ec072f

resolved fdo#77018 keep external references intact during sort



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 Eike Rathke 2014-07-11 21:40:16 UTC
Pending review
for 4-3 at https://gerrit.libreoffice.org/10243
for 4-3-0 at https://gerrit.libreoffice.org/10244
for 4-2 at https://gerrit.libreoffice.org/10245
for 4-2-6 at https://gerrit.libreoffice.org/10246
Comment 8 Commit Notification 2014-07-14 13:41:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7aa2b94ac57b7c99e1e45d5f652a423ee603dd55&h=libreoffice-4-3

resolved fdo#77018 keep external references intact during sort


It will be available in LibreOffice 4.3.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2014-07-14 14:55:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=92216be6ce13990b8ea6b6264c656d2bc1746401&h=libreoffice-4-2

resolved fdo#77018 keep external references intact during sort


It will be available in LibreOffice 4.2.7.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2014-07-15 09:52:21 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=37b97c83aa23d5a7c1687126d93cf1a69ce4a2d0&h=libreoffice-4-3-0

resolved fdo#77018 keep external references intact during sort


It will be available already in LibreOffice 4.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2014-07-22 14:03:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e83928e4dd65a1cf570f9cea65f456bd089ea33&h=libreoffice-4-2-6

resolved fdo#77018 keep external references intact during sort


It will be available already in LibreOffice 4.2.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 12 Laurent BP 2014-08-11 15:56:33 UTC
Verified with versions:
- Version: 4.2.7.0.0+
Build ID: 8de2e9b4bc53e6c097897142bad223c100d36292
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-08-08_21:42:21
- Version: 4.3.2.0.0+
Build ID: 4744400afc9c2be99f62b12180fa33b43acef564
TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-08-11_06:41:42
- Version: 4.4.0.0.alpha0+
Build ID: ad85ce18d3939170e5ef7b1a1f8e5dda2c8aeb9c
TinderBox: Win-x86@39, Branch:master, Time: 2014-08-11_05:21:44
Comment 13 Commit Notification 2014-08-15 14:58:18 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1d67f3661cdf1fadf498ae77f9d8ee9c2e026f31

unit test for sort with sheet references and external references, fdo#77018



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.