Bug 81309 - Sorting should automatically adjust references.
Summary: Sorting should automatically adjust references.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.0.0.alpha0+ Master
Hardware: Other All
: medium enhancement
Assignee: Kohei Yoshida
QA Contact:
URL:
Whiteboard: target:4.4.0 target:4.3.1 target:4.2.7
Keywords:
: 46641 55964 65158 72487 (view as bug list)
Depends on:
Blocks: 85490
  Show dependency treegraph
 
Reported: 2014-07-13 22:04 UTC by Kohei Yoshida
Modified: 2015-07-16 16:01 UTC (History)
6 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 Kohei Yoshida 2014-07-13 22:04:50 UTC
When sorting, Calc should automatically adjust cell references such that formula cells containing cell references not change their values after the sort.

Bug 45146 is probably a similar case.
Comment 1 Kohei Yoshida 2014-07-13 22:05:06 UTC
I'm working on this.
Comment 2 Commit Notification 2014-07-14 01:12:30 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#81309: Adjust references 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 3 Commit Notification 2014-07-14 01:12:45 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#81309: Write test for this.



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 4 Commit Notification 2014-07-14 01:12:59 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#81309: Writer another test.



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 5 Commit Notification 2014-07-14 01:13:13 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#81309: Write another test.



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 6 Commit Notification 2014-07-14 01:13:26 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#81309: Write test for out-of-place sorting.



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 Kohei Yoshida 2014-07-14 01:17:20 UTC
Now it's on master.
Comment 8 Commit Notification 2014-07-15 16:16:51 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

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

fdo#81309: Adjust references 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 Kohei Yoshida 2014-07-18 01:24:16 UTC
*** Bug 65158 has been marked as a duplicate of this bug. ***
Comment 10 Commit Notification 2014-07-21 16:49:26 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#81309: Adjust references 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 11 Jean-Baptiste Faure 2014-07-21 19:20:42 UTC
Hi Kohei,

(In reply to comment #0)
> When sorting, Calc should automatically adjust cell references such that
> formula cells containing cell references not change their values after the
> sort.

Are you sure it should always be the case?

If you make a sort on the file https://bugs.freedesktop.org/attachment.cgi?id=103205 you get a false result with 4.3.1.0.0+ when it is correct with all previous versions.
Precisely:
1/ open the file, it is a simplified extract of bank account. Column D gives the balance between incomes (column C) and spendings (column B). Each formula in column D refers to the value in the previous row in the same column.
2/ select rows 8 to 14
3/ click on the button "Sort ascending" on the dates (column A)
The formulas in D8 and D14 are false 

Best regards. JBF
Comment 12 Kohei Yoshida 2014-07-21 22:42:53 UTC
JBF, please file a new bug.
Comment 13 Jean-Baptiste Faure 2014-07-22 04:51:11 UTC
(In reply to comment #12)
> JBF, please file a new bug.

Done : bug 81633

Best regards. JBF
Comment 14 Jean-Baptiste Faure 2014-10-09 04:24:21 UTC
Hi Kohei,

(In reply to Commit Notification from comment #10)
> Kohei Yoshida committed a patch related to this issue.
> It has been pushed to "libreoffice-4-2":
> [...]
> It will be available in LibreOffice 4.2.7.

It is a surprising idea to backport a patch implementing an enhancement (AFAIK it is you who set this report as enhancement) to the last release of the 4.2 branch.
I do not know if it is possible to revert this commit for 4.2.7. I tried to revert the commit for 4.3 and git said it was not possible. Will try for 4.2.7.

Best regards. JBF
Comment 15 Jean-Baptiste Faure 2014-10-12 11:39:50 UTC
*** Bug 72487 has been marked as a duplicate of this bug. ***
Comment 16 Jean-Baptiste Faure 2014-10-13 20:37:23 UTC
*** Bug 55964 has been marked as a duplicate of this bug. ***
Comment 17 Jean-Baptiste Faure 2014-10-16 04:44:28 UTC
*** Bug 46641 has been marked as a duplicate of this bug. ***
Comment 18 Luke 2014-10-28 09:31:26 UTC
 Jean-Baptiste Faure,
According to:
http://support2.microsoft.com/kb/40401
To correctly sort a range that includes cells with references, all references must be absolute references. 

In every duplicate report, people are incorrectly using relative references. Clearly this is not a bug Calc. These are bug reports that should have been all closed as INVALID.
Comment 19 Jean-Baptiste Faure 2014-10-28 22:08:06 UTC
(In reply to Luke from comment #18)
>  Jean-Baptiste Faure,
> According to:
> http://support2.microsoft.com/kb/40401
> To correctly sort a range that includes cells with references, all
> references must be absolute references. 

Microsoft is not a scientific reference. According to the leap year bug (https://en.wikipedia.org/wiki/Leap_year_bug) I can't trust Microsoft when it say that something must be done that way.
> 
> In every duplicate report, people are incorrectly using relative references.
> Clearly this is not a bug Calc. These are bug reports that should have been
> all closed as INVALID.

Please note that this bug report is an enhancement.

Best regards. JBF
Comment 20 m_j_malone 2014-11-13 15:31:32 UTC
Making the change suggested in:

https://bugs.freedesktop.org/show_bug.cgi?id=81633

to LibreOffice 4.2.7.2 has broken many very old spreadsheets and caused them to be mangled in sorting.  There is no way to resolve this post with that one. I am in this camp.  I want the sorting of references to stay as it was, the sorting that keeps old sheets sorting exactly like Excel and 4.2.6 did.   

However, I hear you and I have a suggestion.   First lets revisit what we think sorting should be.  IMHO, sorting is equivalent to cutting and pasting entire rows of the sort range and by hand manually reordering them. Note, I did not say COPY and paste.  Copy and cut do different things to references depending on whether the reference in the sort is to a cell that is inside or outside the copy or cut region, and whether it is a A1, $A1, A$1, $A$1 reference. Further, for references that appear in cells outside the copy/cut range but (before the operation) refer to cells inside the range, there are again adjustments made.

For instance, when I cut and paste a range, A1 references to a location inside the range are changed, A1 references to a place outside the range are not.  If I copy and paste, both are changed, that is why we have $ to keep selected references from being changed in a copy and paste, so one can make new rows in table-like calculations that use some common constants at the top in every row.        

The way copy/cut and insert columns/rows works makes spreadsheets work in a way that is common so spreadsheets with all forms of $ references (not code) can be moved from one program to another and work.  If you break that, you greatly change the outlook for LibreOffice.   

However, I hear those people.  I suggest a new character with a new action "!" so that !A!1 is not changed ever, not by copy, not by paste, not by sort.  The solution to the bank statement problem with the running balance down the side is to use !A!1 type reference in the running balance column that refer to the data in the range that might be sorted.  Then, when sorting, do not include the running balance column in the sort range. The people who want something different in the way references are handled can decide what they want the action to be of a reference to !Z!100 in A1 to be when inserting rows/columns at M50.  Further, I will let them decide what happens with a drag-extend pseudo-copy-paste on these cells.  I suggest the references changing would be most convenient, but I will let them decide.  I will live with what they decide because as present ! does not appear in any of my old sheets.  If I find it useful, I will use it.  If other people find it useful, it may find its way into other spreadsheet programs. 
   
I believe this will meet the desires of that "bug" post without breaking what people are relying on in this post.
Comment 21 Eike Rathke 2014-12-04 17:14:18 UTC
For the records, 4.2.8 and 4.3.5 will restore the old behavior, for 4.4 the UpdateReferenceOnSort option is defaulted to false/unchecked.