Bug Hunting Session
Bug 83276 - Incorrect Formulae After Sorting
Summary: Incorrect Formulae After Sorting
Status: RESOLVED DUPLICATE of bug 81633
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.1.2 release
Hardware: x86-64 (AMD64) Windows (All)
: high critical
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks:
 
Reported: 2014-08-30 14:46 UTC by dmdcaretwo
Modified: 2014-10-09 21:22 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Example for Incorrect Formulae After Sorting (45.37 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-08-30 14:46 UTC, dmdcaretwo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description dmdcaretwo 2014-08-30 14:46:24 UTC
Created attachment 105470 [details]
Example for Incorrect Formulae After Sorting

Windows 7 Home Premium SP1

Sorting data to which formulae refer results in incorrect formulae.

The attachment contains a worksheet with three columns: rank, organisation contacted, and number of contacts.  The third and subsequent rows in the rank column contain a formula to determine an entry's rank and when the list needs re-sorting.

Sorting takes places on the organisation contacted and number of contacts columns based on the descending number of contacts and the ascending organisation contacted.

Change the number of contacts in any row from three onwards to a higher number than one or more of the entries above the chosen row.  The rank column heading changes to "Re-sort" and the chosen row displays the same text instead of a ranking number (or a blank).

Highlight the organisation contacted and number of contacts headings and the data, select Data, Sort... and click OK.  When the data is sorted the text "Re-sort" is still displayed in the chosen row as the formula in the chosen row now refers to the new position of the organisation contacted.

I believe, but am not sure, sorting in version 4.3 resulted in the formula referring to the chosen row, however, that was definitely the case in 4.2 (and earlier) releases.
Comment 1 m.a.riosv 2014-08-30 20:34:21 UTC
Relative references to a cells in a different row are not updated.

Reproducible with:
4.2.7.0.0+ Build ID: 539f5b5f7dfc1168c4a6b826bfb18362c80fb424
   TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-08-29_13:56:53
4.3.1.2 Build ID: 958349dc3b25111dbca392fbc281a05559ef6848
4.3.2.0.0+ Build ID: a5fedcf7fe56a56475dea484a78971c504e52272
   TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-08-29_10:16:48
4.4.0.0.alpha0+ Build ID: f5aeaa3845ae794a6320f082b1354f946d96cec1
   TinderBox: Win-x86@42, Branch:master, Time: 2014-08-29_01:36:17


Versions working:
4.2.6.3 Build ID: 3fd416d4c6db7d3204c17ce57a1d70f6e531ee21
4.3.0.4 Build ID: 62ad5818884a2fc2e5780dd45466868d41009ec0
   TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-08-11_06:41:42


I think there was some reworks on sort lately.

Unchanged the version to the last with the issue because 4.2.6.3 it's not in the list.
Comment 2 Kohei Yoshida 2014-09-02 09:51:38 UTC
So, this new behavior itself, of updating references on sort, is intentional, not a regression. Having said that, there is a request for making it configurable.
Comment 3 dmdcaretwo 2014-09-02 11:19:56 UTC
(In reply to comment #2)
> So, this new behavior itself, of updating references on sort, is
> intentional, not a regression. Having said that, there is a request for
> making it configurable.

I do not see how the behaviour can be considered intentional given the mess it creates in the formulae.  You start with a column of formulae which are (relatively speaking) the same; add extras rows and you can copy any formula to the additional rows.

Once the data is sorted, the column of formulae are different.  The more you sort, the worse the mess.

Were it not for the fact that the formula in each row checks whether the lists needs re-sorting, I would have not noticed the mess made of the formulae. As it is, after each sort, I have to copy a "good" formula to all the remaining rows.
Comment 4 Laurent BP 2014-09-02 12:12:08 UTC
(In reply to comment #3)
> I do not see how the behaviour can be considered intentional given the mess
> it creates in the formulae. 

It was a request. See bug 45146

As said by Kohei,
> there is a request for making it configurable.
See bug 81633

Closing as duplicate of bug 81633

*** This bug has been marked as a duplicate of bug 81633 ***
Comment 5 dmdcaretwo 2014-09-02 12:20:49 UTC
(In reply to comment #4)
> (In reply to comment #3)
> > I do not see how the behaviour can be considered intentional given the mess
> > it creates in the formulae. 
> 
> It was a request. See bug 45146
> 
> As said by Kohei,
> > there is a request for making it configurable.
> See bug 81633

Okay but the sensible option would have been to implement changed behaviour AND the configuration option at the same time (plus highlighting the changes).
Comment 6 ign_christian 2014-09-02 16:22:01 UTC
(In reply to comment #5)
Hi.. Lets keep discussion in that report. It's been told by our experts.

*** This bug has been marked as a duplicate of bug 81633 ***
Comment 7 Jean-Baptiste Faure 2014-10-09 21:22:23 UTC
Note: in this case, the configuration option in LO 4.4 does not allow _today_ to get back the current behavior until version 4.2.6.

I am collecting the different cases involved in this sorting issue.

Best regards. JBF