Bug 85490 - [METABUG] Use-Cases for sorting with references
Summary: [METABUG] Use-Cases for sorting with 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: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard:
Keywords:
Depends on: 45146 61344 68566 70815 77018 79441 81309 81633 81796 84055 85215 85405 85584 85614 86304 86762
Blocks:
  Show dependency treegraph
 
Reported: 2014-10-26 18:45 UTC by Jean-Baptiste Faure
Modified: 2014-12-12 14:45 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
test file for the fourth use-case (24.41 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-26 18:45 UTC, Jean-Baptiste Faure
Details
use-case for sorting issues (20.67 KB, application/vnd.oasis.opendocument.presentation)
2014-10-26 18:49 UTC, Jean-Baptiste Faure
Details
new version of use-cases summary with numbers of main bug reports (16.97 KB, application/vnd.oasis.opendocument.presentation)
2014-10-26 19:58 UTC, Jean-Baptiste Faure
Details
Use-Case 2 save as XLS to show it should always be moved on sort (23.50 KB, application/vnd.ms-excel)
2014-10-27 05:29 UTC, Joey Reid
Details
Updated use-cases summary with Excel results (50.75 KB, application/vnd.oasis.opendocument.presentation)
2014-10-28 02:53 UTC, Luke
Details
summary of Calc behaviors when sorting range of cells with references (44.95 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-05 20:48 UTC, Jean-Baptiste Faure
Details
summary of Calc behaviors when sorting range of cells with references (85.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-06 09:44 UTC, Luke
Details
Updated use-cases summary (49.15 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2014-11-18 01:44 UTC, Luke
Details
Updated use-cases summary with Calc 4.5 results (42.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-27 03:17 UTC, Luke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jean-Baptiste Faure 2014-10-26 18:45:31 UTC
Created attachment 108468 [details]
test file for the fourth use-case

As asked by ESC on 2014-10-09 (http://nabble.documentfoundation.org/minutes-of-ESC-call-tt4125271.html) I open this metabug to summarize Calc sorting problems.

In the attached presentation, I describe 4 use-cases with 3 affected by the changes made for bug 81309 and bug 81633.

The fourth use-case still works as expected. As I did not find a bug report about it and for the record, I will attach a test file for it.

My main suggestions are to fix bug 85215 and to make, if possible, the option UpdateReferenceOnSort locale to the sort dialog instead of global to Calc.

Hope this helps.

Best regards. JBF
Comment 1 Jean-Baptiste Faure 2014-10-26 18:49:37 UTC
Created attachment 108469 [details]
use-case for sorting issues

First draft for use-cases.

Best regards. JBF
Comment 2 Jean-Baptiste Faure 2014-10-26 19:58:50 UTC
Created attachment 108474 [details]
new version of use-cases summary with numbers of main bug reports
Comment 3 Joey Reid 2014-10-27 05:29:05 UTC
Created attachment 108491 [details]
Use-Case 2 save as XLS to show it should always be moved on sort

It should be noted in your presentation that Use-Case: Sorting students by their scores uses absolute references. Therefore, its references should ALWAYS be moved on a sort, irrespective of any settings. 

Steps to Verify:
1. Open testRefInterne.xls in MS Excel and Sort by Average
2. Open testRefInterne.xls in Lo 4.1 or earlier and Sort by Average
3. Open testRefInterne.xls in Apache OpenOffice and Sort by Average
4. Open testRefInterne.xls in Gnumeric  and Sort by Average
5. Open testRefInterne.xls in KSpread  and Sort by Average
6. Open testRefInterne.xls in WPS Office and Sort by Average
7. Open testRefInterne.xls in IBM Lotus Symphony and Sort by Average
8. Compare sorted tables

Results:
All spreadsheets adjust the references.

Note this is the only case where references should be automatically adjusted. When you perform the same steps with the other use cases that contain relative references, they are not and adjusted automatically by other spreadsheets.

By including this use case that is bugged in 4.2+, you are confusing the issue.
Comment 4 Joey Reid 2014-10-27 06:00:08 UTC
Sorry if I was not clear. By the definition of absolute reference and by every spreadsheet's implementation, formulas with absolute references should always be adjusted when sorting.  The fact that it's not sorting correctly with newer versions of Calc is unrelated to the issue of how the sorting of formulas with relative references should be handled.

In the case of relative references, they should not be adjusted with the sort. The only time I have ever seen a spreadsheet with the option to adjust formulas, is with Gnumeric. And in that case, it is a sort option (not a system setting) and it is disabled by default.
Comment 5 Luke 2014-10-28 02:53:25 UTC
Created attachment 108546 [details]
Updated use-cases summary with Excel results

I updated the presentation to include the results of how Excel sorts these use cases. 

Case 1 bank: Good
Case 2 students: Good 
Case 3 synthetic: FAIL
Case 4 hardware: Good

To resolve this issue, Cases 1, 2 and 4 should all sort properly with the default Calc settings.
Comment 6 Jean-Baptiste Faure 2014-10-28 21:36:17 UTC
(In reply to Joey Reid from comment #3)
> Created attachment 108491 [details]
> Use-Case 2 save as XLS to show it should always be moved on sort
> 
> It should be noted in your presentation that Use-Case: Sorting students by
> their scores uses absolute references. 

I do not see any absolute reference in attachment 100144 [details] from bug 79441.

> By including this use case that is bugged in 4.2+, you are confusing the issue.

This issue is a metabug intended to summarize all use-cases for sorting with references and to show what does work and what does not. Why excluding a particular use-case ?

Best regards. JBF
Comment 7 Jean-Baptiste Faure 2014-10-28 21:39:34 UTC
Bug 85215 : last commits for this bug restored the legacy behavior (when UpdateReferenceOnsort = false). See https://bugs.freedesktop.org/show_bug.cgi?id=85215#c14

Best regards. JBF
Comment 8 Joey Reid 2014-10-29 01:35:53 UTC
>This issue is a metabug intended to summarize all use-cases for sorting with references and to show what does work and what does not. 

Jean-Baptiste Faure,
There is no valid use case for this adjusting relative references in a sort. That’s what absolute references are for. The whole issue arose from a spreadsheet neophytes who do not understand the fundamentals spreadsheet design. In Bug 45146  a developer, Eike,  said,

“A relative in-sheet reference is supposed to point to a cell relative to the formula cell containing the reference and hence changes the value if the formula cell is moved during sort. If the reference shall stick to the same cell then an absolute reference must be used. This behavior is identical to how Excel treats it.”

Instead of listening to his wisdom and telling the users to fix their broken spreadsheets with absolute references, we bowed to their ridiculous demands, creating this nightmare situation where Calc is incompatible with all other spreadsheet. We should treat this as a learning opportunity and revert changes to prevent any more casualties of this poorly thought out "feature".

This is a serious issue. Look at all the dupes of Bug 81633 to get an idea of the damage this is doing to Calc's reputation as being a professional quality product.
Comment 9 Jean-Baptiste Faure 2014-10-29 05:57:47 UTC
Filled bug 85584 to propose that option UpdateReferencesOnSort be set to false by default.

Best regards. JBF
Comment 10 Jean-Baptiste Faure 2014-11-05 20:48:22 UTC
Created attachment 108990 [details]
summary of Calc behaviors when sorting range of cells with references

In this spreadsheet I tried to summarize the use-cases I have found.

Please, test yourself.

Best regards. JBF
Comment 11 Luke 2014-11-06 09:44:16 UTC
Created attachment 109016 [details]
summary of Calc behaviors when sorting range of cells with references

Updated Summary to include results of Excel and OpenOffice. In every case:
Excel = OpenOffice Calc 4.2 = LO Calc 4.2.6
Comment 12 m_a_riosv 2014-11-06 23:08:20 UTC
*** Bug 85968 has been marked as a duplicate of this bug. ***
Comment 13 Luke 2014-11-18 01:44:22 UTC
Created attachment 109652 [details]
Updated use-cases summary

I updated the summary with 3 more problematic use cases. If we could fix the first 3, so that they sorted correctly with UpdateReferenceOnSort=TRUE, the sorting behavior would closely match the industry standard/previous behavior.
Comment 14 Eike Rathke 2014-11-20 21:22:52 UTC
Status update and explanations for attachment 109652 [details]
metabug_fdo85490_v6.ods per sheet:

* Ranking_Columns: fixed with bug 83765

* Bug43004: fixed with bug 83765, was erroneously indicated to be some
  case of bug 43004 but is not.

* Bank_Account: there's nothing wrong with it, with
  UpdateReferenceOnSort=TRUE the references are adjusted to point to the
  same data they did before, which for the "classic" column shifts
  references around within the same column, and of course in the "new"
  column the OFFSET() function still calculates the offsets as it is
  told so it differs from the "classic" column.

* Indicator_2: range references usually can't be adjusted if they are of
  the same direction as the reordering and within data, imagine the
  content of A1:A3 was not {1,2,3} but {4,2,6} instead, where should it
  point to after reordering? Doing it here in this case would be
  a special special case.

* abs / absData: the observed behavior is exactly the difference of
  UpdateReferenceOnSort=TRUE, references that point into the reordered
  range are updated to keep a reference to the same data. If that is not
  wanted then UpdateReferenceOnSort=FALSE must be used. So it is not
  correct to say that the behavior would be wrong in the TRUE case. This
  is why the option was implemented.

* Average: updating in the case of UpdateReferenceOnSort=FALSE is a bug,
  references within the reordered range that are pointing to ranges
  within the reordered range of the same reordering direction must be
  kept relative to the reordered data. This is fixed in master, 4.3.4,
  4-3 (to-be 4.3.5) and does not yet occur in 4.2.7, but did occur in
  4-2 (to-be 4.2.8 if released at all). Cherry-picking another commit
  from bug 85215 resolved that.

* RefInside: same reason as on the Average sheet, fixed with the commits
  for bug 85215.

* Indicator: I didn't wrap my head around that one yet.. but it appears
  to me that the expectations are wrong.

* Stock_Management: a case of why UpdateReferenceOnSort=TRUE was
  invented. It is misleading to title the other cases as wrong. If
  a reference updated is wanted for UpdateReferenceOnSort=FALSE then
  absolute references must be used, i.e. =C$3+C$4, this is the same in
  all major spreadsheet implementations.

* Bug62490: same as Stock_Management, use absolute references, as
  a workaround if already relateive references are used then use
  UpdateReferenceOnSort=TRUE.

* Ubuntu: apparently Ubuntu for 4.2.7 applied a patch of 4-2 that needs
  two other patches to follow-up, namely the ones already mentioned
  above for bug 85215 that also went into 4-2

* Users: so we're fine here? GREAT!
Comment 15 Luke 2014-11-27 03:17:29 UTC
Created attachment 110103 [details]
Updated use-cases summary with Calc 4.5 results

@Eike Rathke
With the recent patches you and Kohei have made to the UpdateReferenceOnSort=FALSE code path, the sorting behavior has nearly returned 100% to the legacy/Excel behavior. 

I went through every duplicate of Bug 81633 on Calc 4.5. They are all sorting correctly now! The only minor issue I encountered is that some spreadsheets (like indicator) now require a Ctrl+Shift+F9 after a sort.

This is fabulous. You are a thick skinned saint to bear with all the irate users. The community owes you and everyone else who donated their time to resolve this issue, some hugs and a big thanks.
Comment 16 Luke 2014-11-29 00:50:05 UTC
I've spend the past couple of days using 4.2.8 as my primary office suite and also tested all of the bugs in the meta spreadsheet along with most duplicates of Bug 81633 . I'm pleased to report that all of the spreadsheets in attachment 110103 [details] that were broken in 4.2.7 are fixed in 4.2.8.  Great work team!
  
Version: 4.2.8.0.0+
Build ID: 383535196f9924202fc997c388e34a7865a2250e
Comment 17 tommy27 2014-11-29 06:10:54 UTC
thanks for feedback Luke.

I really think that a 4.2.8 release would be a bless.

the 4.2.x branch has been declared End of Life after 4.2.7 but this looks to me a valid reason to "resuscitate" it
Comment 18 Eike Rathke 2014-12-04 16:11:51 UTC
Moved the "see also" list to "Depends on" to be able to see the dependency tree view.
Comment 19 Eike Rathke 2014-12-04 16:18:51 UTC
(In reply to tommy27 from comment #17)
> I really think that a 4.2.8 release would be a bless.

We'll do a 4.2.8 release, currently in progress.
Comment 20 Eike Rathke 2014-12-05 19:48:22 UTC
@Julien:
Please don't mix in ordinary bugs like crashes, that's not use-case related. This is not a "let's stick in all kind of sorting related bugs" list.
Comment 21 jerberto 2014-12-05 22:08:36 UTC
When would release 4.2.8 would be available? having to copy paste all my formulas every time I sort rows is killing me!!
Comment 23 Eike Rathke 2014-12-12 14:45:53 UTC
Let's call this fixed now.