Libreoffice calc 4.2.7-0ubuntu1 not updating references after sort

Bug #1389858 reported by Marc Deslauriers
52
This bug affects 9 people
Affects Status Importance Assigned to Milestone
LibreOffice
Fix Released
Critical
libreoffice (Ubuntu)
Fix Released
High
Björn Michaelsen
Trusty
Fix Released
High
Unassigned

Bug Description

Update to LibreOffice 4.2.7 is causing incorrect sort behaviour in Calc.

As initially reported here:

http://www.reddit.com/r/Ubuntu/comments/2le7qu/libreoffice_calc_update_a_few_hours_ago_is_buggy/

Attached is sample Calc document. To reproduce, highlight B6 to E14, select Sort and sort by Column D, see that column E didn't get sorted.

ProblemType: Bug
DistroRelease: Ubuntu 14.10
Package: libreoffice (not installed)
ProcVersionSignature: Ubuntu 3.16.0-24.32-generic 3.16.4
Uname: Linux 3.16.0-24-generic x86_64
ApportVersion: 2.14.7-0ubuntu8
Architecture: amd64
CurrentDesktop: Unity
Date: Wed Nov 5 15:49:03 2014
InstallationDate: Installed on 2013-11-26 (344 days ago)
InstallationMedia: Ubuntu 13.10 "Saucy Salamander" - Release amd64 (20131016.1)
SourcePackage: libreoffice
UpgradeStatus: Upgraded to utopic on 2014-10-08 (27 days ago)

Revision history for this message
In , JBF (jbf-faure) wrote :

Currently the configuration option in master (I guess it is menu Tools > Options > LibreOffice Calc > Update reference when sorting range of cells) does not solve the problem.

If you try to sort the test file (attachment 104016) whatever the choice you do (check or uncheck this option) the result is false:
1/ option checked: result false in column D and correct in column E
2/ option uncheck: error 523 (Calculation does not converge) in row 3 to 6 in both columns D and E.

NB1: a quick way to verify if the sort is correct is to check the last value in columns D and C; it must be 784.79 whatever is the order of the rows.

NB2: to sort the data I do that: click in A2 then select rows 2 to 14 (maintain Shift key and click on the row headers 2 and 14).

Best regards. JBF

Revision history for this message
In , Luke (lukebenes) wrote :

Created attachment 107586
Screenshot showing how sorting is still broken even after the patch

Confirmed. The table is not sorting correctly with either option.

Revision history for this message
In , Luke (lukebenes) wrote :

Created attachment 107601
Both Kingsoft and Gnumeric do NOT automatically adjust references when sorting

Revision history for this message
In , JBF (jbf-faure) wrote :

(In reply to Jean-Baptiste Faure from comment #37)
> Currently the configuration option in master (I guess it is menu Tools >
> Options > LibreOffice Calc > Update reference when sorting range of cells)
> does not solve the problem.

I am pretty sure that I tested the fix right after it was pushed to the master and that it worked. I do not know what happened since this time. What is clear is that, now, it does not solve the regression.

Best regards. JBF

Revision history for this message
In , Photon713 (bobrass) wrote :

I spent the better part of a day trying to figure out why my spreadsheets were not sorting properly. I've been using the same spreadsheets for the past 3 years. All of my values in this particular sheet were by reference to sheets, Points, Ringers and Master and used the usual $Points.A1 to $Points.V38 and the same for $Ringers and $Master. Basically, the same problem mentioned by many others, i.e., unusual results. Doing an UNDO left behind REF errors.

I was able to resolve the problem for this particular spreadsheet by doing a find and replace for every reference to read...$Points.$A$1, or, $Ringers.$X$10, etc. Somewhere I spotted this syntax and it got me through this full day problem. I recently upgraded to 4.3.1.2. It seems that a known problem should be fixed when found, not wait for a future upgrade.

Revision history for this message
In , Qubit (qubit) wrote :

A fix has already been released for this bug, so changing status from 'NEW' back to 'REOPENED'.

Revision history for this message
In , JBF (jbf-faure) wrote :

*** Bug 84847 has been marked as a duplicate of this bug. ***

Revision history for this message
In , Mariosv (mariosv) wrote :

If someone is interested in a workaround, see my answer in:
http://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=40912#post-id-40912

IMMHO, while we continue without a minimal previous QA for every new enhancement, not easy to find this kind of issues before release.

Revision history for this message
In , Scno (scno) wrote :

I don't know the effort it takes to implement such a minimal QA.

But if it is not in reach, just don't add new enhancements in between minor version updates and definitely not between 4.2.6 and 4.2.7 - just fix bugs.

LibreOffice has matured and it has a strong user base. Bug hunting becomes now really important. It is definitely more important for me as a user then the next newest feature.

U loose more users by annoying bugs then winning new ones with a new feature.

I expect troubles for the X.X.0 Version but after then it must become better.

Revision history for this message
In , Fred Olness (olness) wrote :

A COMPACT EXAMPLE OF THE BUG: ===================

Here is a compact example of the bug.
This is now fixed in the 4.3.4.0.0 2014-10-09 development version. (Thank you)

Note, this is clearly a BUG and not a FEATURE as this bug would break
imported spreadsheets from other programs (Excel, Gnumeric, ...)
In particular, this broke all my course grading spreadsheets.

EXAMPLE: if I consider the following spreadsheet
(which is sorted on the 1st column):

-----------------------------------------------------------------
ann fff =E1+F1+G1 =SUM(E1:G1) 1 2 3
barney eee =E2+F2+G2 =SUM(E2:G2) 4 5 6
charlie ddd =E3+F3+G3 =SUM(E3:G3) 7 8 9
-----------------------------------------------------------------

If I now sort on the 2nd column {ddd,eee,fff} the result is WRONG:

-----------------------------------------------------------------
charlie ddd =E1+F1+G1 =SUM(E3:G3) 7 8 9
barney eee =E2+F2+G2 =SUM(E2:G2) 4 5 6
ann fff =E3+F3+G3 =SUM(E1:G1) 1 2 3
-----------------------------------------------------------------

Note the relative references are handled correctly when I use
"=E1+F1+G1" but not when they are inside the argument of the
function: "=SUM(E3:G3)"

Revision history for this message
In , Fred Olness (olness) wrote :

Created attachment 107658
Short example of the bug

This is a short (3 line) example of the bug.

Revision history for this message
In , Erik (erikalm) wrote :

(In reply to Kohei Yoshida from comment #7)
> Our best option would be to make it configurable. Any attempt to
> automatically figure out when to and not to adjust would only make the
> situation worse, because unless we add a Google-level clever AI to do the
> guessing, we would never get it right, and there would always be some users
> with extreme corner cases coming out of the woodwork shouting "you broke my
> workflow!".

One thing that baffles me with the new sort is that it changes cells outside of the selected sort range.

If I wanted the sort to change the cells, for instance keep references to rows, I'd include those columns in the sort selection.

Would it help if the concept was that only cells in the sort selection should be changed? (It would at least help me a bunch and I think it would offer a workaround for those that expects cells to change all over the place).

You could also add a little checkbox in the sort dialog that said something like "Enable Hollistic Sorting" :D

Another clue (at least in my case) to when I don't want calculations to change with sort is when I have one or several columns of values and then one or several columns of formulas referencing the value-columns. And these formulas are identical part from the references that all uniformly address the same row as the formula cell or the same number of rows above/below the formula cell - I.e. uniformly referenced formulas

However a better option in this case is to insert an an empty column between the "data" cells and the "formula" cells and in that way keep sorting from selecting the formula cells when sorting, and as per above then keep sorting from changing the references in the formula cells.

/E

Revision history for this message
In , Scno (scno) wrote :

Created attachment 107667
Test Case for cross sheet sort.

As I started to implement the workaround. I saw another annoying side effect.

I have a simple table without formulas on sheet1 and a summery by specific criteria on sheet2. These criteria are computed on sheet3. I made this division to let my users sort sheet1 without the risk of damaging any formulas.

What happens now is that on sheet3 with references to sheet1 the formulas are changed and sorted also.

So in sorting a table on one sheet I changed the formulas on another sheet.

I created a 2-sheet test case for this. Just sort sheet1 and see the effects on sheet2.

Revision history for this message
In , Michael-meeks-1 (michael-meeks-1) wrote :

Wow; this bug is unreadably long & awful and seems to be collecting different problems.

Anyhow - Eike just merged the back-port of the option, defaulting to what should be the old behavior to the -4-3 branch, and it should be in the next 4.3 release (but not the one that is currently in progress AFAICS that'd be too much review work).

Revision history for this message
In , JBF (jbf-faure) wrote :

Hi Michael,

Thank you very much for that. I saw the commit and I am currently trying to test my build. I do not understand how to check if the option is available in my installation. Where should I look ? In the install dir or in the user profile? Is it necessary to restart with a clean new profile to have this option working?

Best regards. JBF

Revision history for this message
In , JBF (jbf-faure) wrote :

Ok, I found the option in the Expert Configuration window. Now, version 4.3 works the same way as 4.4 does. But in the case of the first attachment to this bug report (attachment 104016) both are currently wrong. It seems that the backward compatibility is not complete or broken somewhere. See comment #37 and comment #40.

Best regards. JBF

Revision history for this message
In , JBF (jbf-faure) wrote :

I am closing this bug report, considering that the option UpdateReferenceOnSort solves the backward compatibility problem.
For the remaining bug on the operation of this option (Err:523), I have filed a dedicated bug report (bug 85215).

Best regards. JBF

Revision history for this message
In , JBF (jbf-faure) wrote :

Created attachment 108255
Another example of inconsistency in sorting (updated)

Here is a new version of attachment 104016 in which I have removed useless validity list and restored missing headers of columns A, B and C.

Best regards. JBF

Revision history for this message
In , JBF (jbf-faure) wrote :

*** Bug 85479 has been marked as a duplicate of this bug. ***

Revision history for this message
In , JBF (jbf-faure) wrote :

*** Bug 85405 has been marked as a duplicate of this bug. ***

Revision history for this message
In , JBF (jbf-faure) wrote :

From comment #8 in bug 85215 :

It works without error if, instead of selecting only the cell A1 and going to menu Data > Sort, I select the range A1:C14 (that is only the data) and sort on column "Date". It works the same if I select A1, hit the shift key and click on C14, or if I select the columns A, B and C and use the menu Data > Sort.

For me it is usable but it is a big change in the workflow which I was used to. I am sure I am not alone in this case.

Best regards. JBF

Revision history for this message
In , Dmdcaretwo (dmdcaretwo) wrote :

Clearly the bug is still far from resolved and "For me it is usable but it is a big change in the workflow which I was used to" is unacceptable to me and, possibly, other users.

The sorting should work without users having to sort in specific ways.

There is, I believe, a strong case for removing the new functionality which has caused all these problems and for a considered approach to be taken the issue with it only being released when it works. I am hoping the developers have learned a lesson about releasing - let us not beat around the bush - half-baked functionality.

The release of the new functionality which corrupted formulae by sorting was unacceptable. The time it is taking to fix the bug is also unacceptable.

Revision history for this message
In , Luke (lukebenes) wrote :

Jean-Baptiste,
Why have you closed this bug report? This serious issue has still not been resolved. Sorting now requires users to know workarounds and change settings that don't always work. This should remain open until sorting functionality has been restored to work in the same way as Excel, AOO, and LO 4.1 and earlier work.

Revision history for this message
In , JBF (jbf-faure) wrote :

(In reply to Luke from comment #59)
> Jean-Baptiste,
> Why have you closed this bug report?

I explained in comment #53 why I closed this bug report.

> This serious issue has still not been
> resolved. Sorting now requires users to know workarounds and change settings
> that don't always work. This should remain open until sorting functionality
> has been restored to work in the same way as Excel, AOO, and LO 4.1 and
> earlier work.

I disagree, this bug report became unreadable. The issue in the original description is fixed by the option configuration. Ok, it is not perfect, that is why I submitted the problem to the ESC. The fact is that we have different needs for different situations. Sorting in some situations worked for years and some others did not work, for years too. The metabug bug 85490 try to summarize how sorting should work in different typical situations. Please, feel free to contribute to make the situation and the needs clearer.

Thank you for your understanding.

Best regards. JBF

Revision history for this message
In , Luke (lukebenes) wrote :

Jean-Baptiste Faure,
Could you please give an example of a VALID spreadsheet that "did not work for years too"? The one "synthetic" example in your Meta is completely broken in Excel,Google Sheets, and never worked in any spreadsheet until recently. The "fix" for that Bug 45146 introduced this serious regression.

Thank you very much for following up with this report and with the ESC, but I do not think we can consider this issue resolved until the old functionality is restored.

Revision history for this message
In , Dmdcaretwo (dmdcaretwo) wrote :

(In reply to Luke from comment #61)
>
> Thank you very much for following up with this report and with the ESC, but
> I do not think we can consider this issue resolved until the old
> functionality is restored.

I agree with Luke.

Revision history for this message
In , Nigelrmurray (nigelrmurray) wrote :

Gentlemen,

As an outsider who has only begun reading about this issue (since the change in workflow affected me) I would like to add my perspective.

I think it is essential that default behaviour should not be changed in any mature application unless there is overwhelming evidence that the advantages outweigh the disadvantages. Furthermore, I think it is important (though not always essential) to mirror the default behaviour of significant competitors, in this case Excel.

The change in sort behaviour is so significant and so counter-intuitive for those of us coming from other products, that I believe it should not be made unless the default behaviour can remain compatible.

Just my $0.02 worth.

-Nigel

Revision history for this message
In , Gerard-fargeot (gerard-fargeot) wrote :

*** Bug 85571 has been marked as a duplicate of this bug. ***

Revision history for this message
In , Stephaniepar1972 (stephaniepar1972) wrote :

The consensus appears to be that this issue is still not resolved. I wasted hours and lost data by making the mistake of upgrading to 4.3.2.2, the version that still offered on the website.

By the guidelines at https://wiki.documentfoundation.org/QA/BugTriage
That qualifies this as a serious bug eligible for back ports.

Revision history for this message
In , JBF (jbf-faure) wrote :

Please, have a look at bug 85215 and bug 85584.

Setting back to resolved fixed.

Best regards. JBF

115 comments hidden view all 127 comments
Revision history for this message
Marc Deslauriers (mdeslaur) wrote :
Revision history for this message
Seth Arnold (seth-arnold) wrote :

1:4.2.6.3-0ubuntu1 worked as expected
1:4.2.7-0ubuntu1 is broken as described on reddit

Revision history for this message
Launchpad Janitor (janitor) wrote :

Status changed to 'Confirmed' because the bug affects multiple users.

Changed in libreoffice (Ubuntu):
status: New → Confirmed
Revision history for this message
Marc Deslauriers (mdeslaur) wrote :
Changed in libreoffice (Ubuntu):
assignee: nobody → Björn Michaelsen (bjoern-michaelsen)
importance: Undecided → High
Revision history for this message
Marc Deslauriers (mdeslaur) wrote :
penalvch (penalvch)
tags: added: regresion-update
penalvch (penalvch)
Changed in libreoffice (Ubuntu):
status: Confirmed → Triaged
penalvch (penalvch)
tags: added: trusty
removed: utopic
1 comments hidden view all 127 comments
Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

1/ LibreOffice did not update references correctly and consistently on sort before 4.2.x/trusty in general before 4.2.7
2/ There are sets of users that want references to be updated by a sort. There is an equal set of users who want references to _not_ be updated by a sort.
3/ _Not_ updating references after a sort considered closest to the previous LibreOffice 4.2.x/trusty behaviour

As such the current package in by default does _not_ update references after a sort, but offers a "UpdateReferenceOnSort"[1] option, which is disabled by default (which is what most other vendors, e.g. Collabora do to). Note that enabling "UpdateReferenceOnSort" by default would cause just as many users complain about the behaviour (see bugs below) and claim it to be a regression, because they expect the references _not_ to be updated.

The current behaviour in 4.2.7-0ubuntu1 is _not_ a regression as -- as noted above -- LibreOffice 4.2.x/trusty did not update references consistently anyway, despite examples existing were it did. So 4.2.7-0ubuntu1 behaves as close as possible to the 4.2.x/trusty series, but offers the option to update references too (but its not the default, because that would be a different behaviour from the one in previous 4.2.x/trusty).

This has been extensively discussed upstream:
https://bugs.freedesktop.org/show_bug.cgi?id=85215
https://bugs.freedesktop.org/show_bug.cgi?id=81633

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

http://nabble.documentfoundation.org/minutes-of-ESC-call-tt4125271.html (section "Calc sorting issue")

also note the number of reviewers/testers on the related patches:
https://gerrit.libreoffice.org/gitweb?p=core.git;a=commit;h=f4c179ea7f69e87e55a416c1588ee0aad7c146c0
https://gerrit.libreoffice.org/gitweb?p=core.git;a=commit;h=67f3ce3a9df2bc62db5602dd84975047c1137b92

[1] https://gerrit.libreoffice.org/gitweb?p=core.git;a=blobdiff;f=officecfg/registry/schema/org/openoffice/Office/Calc.xcs;h=54e15fc87bc9d090b2353544d2a5769763323764;hp=0cdb9d51cd1e916d3b426e3083dd141b6f5baaf4;hb=67f3ce3a9df2bc62db5602dd84975047c1137b92;hpb=970f4c775b47d09d26469f6cd304a19ad44731f7

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

quoteing from the ESC minutes linked above:
    + both sides seem to think they are right (Kohei)
        + https://bugs.freedesktop.org/show_bug.cgi?id=81309
            "Sorting should automatically adjust references."
        + https://bugs.freedesktop.org/show_bug.cgi?id=81633
            "Sorting shouldn't always automatically adjust references."

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

The 4.2.7-0ubuntu1 package should now satisfy the fdo#81633 (Sorting shouldn't always automatically adjust references.) users, while the fdo#81309 (Sorting should automatically adjust references.) need to enable the UpdateReferenceOnSort option. In earlier versions of LibreOffice 4.2.x released in trusty, depending on document and formula the behaviour was inconsistent. Thus no matter which consistent behaviour we implement as default, one of the above groups of users will see it as a regression while the other group will see it as a bugfix.

penalvch (penalvch)
tags: removed: regresion-update
Revision history for this message
penalvch (penalvch) wrote :

Björn Michaelsen, thanks for the quick clarification. I've hidden my comment so as not to confuse anyone on this.

However, by default in Microsoft Excel 2013 15.0.4659.1001 it allows sorting of relative references in the attached spreadsheet. Perhaps this discussion should be carried upstream, but it would seem both allowing this default behavior for Excel expectation compatibility purposes, and allowing the changing of this behavior from the commits in https://bugs.freedesktop.org/show_bug.cgi?id=81633 would be the best way to go. What do you think?

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

Here is a quick and dirty way for those who want the fdo#81309 behaviour (Sorting should automatically adjust references.):
sudo vim /usr/lib/libreoffice/share/registry/main.xcd
Search for:
 <prop oor:name="UpdateReferenceOnSort" oor:type="xs:boolean" oor:nillable="false"><value>false</value></prop>
and replace it with:
 <prop oor:name="UpdateReferenceOnSort" oor:type="xs:boolean" oor:nillable="false"><value>true</value></prop>

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

Note that the upcoming LibreOffice 4.4.x will default to the fdo#81309 behaviour (Sorting should automatically adjust references.) behaviour and have an option in the UI to go back to the fdo#81633 (Sorting shouldn't always automatically adjust references.) behaviour.

summary: - Libreoffice calc 4.2.7 not sorting right
+ Libreoffice calc 4.2.7 not updating references after sort
summary: - Libreoffice calc 4.2.7 not updating references after sort
+ Libreoffice calc 4.2.7-0ubuntu1 not updating references after sort
Revision history for this message
Luke (lukebenes) wrote :

Björn,
> 1/ LibreOffice did not update references correctly and consistently on sort before 4.2.x

You are confusing the issues here. No spreadsheet software consistently updates or does not update references, because there are many types of references. However, there is a well established behavior that
a) Sorting RELATIVE references does not update
b) Sorting ABSOLUTE references does update
All spreadsheet software followed this behavior, including LibreOffice before 4.2.x, Gnumeric, Google Sheets. WPS Sheets, and EXCEL

> There is an equal set of users who want references

Equal? Over the past few years there have just been a few bug reports asking for the new sort behavior. These were filled out by users who admittedly are new to spreadsheets and do not know how to use absolute references. In just a few weeks, this new sort has caused dozens of bug reports like this one and all the dupes of Bug 81633. In fact the [Meta] Bug 85490 that's supposed to track usecases could only find ONE synthetic example that could easily be FIXED with absolute references.

> Note that the upcoming LibreOffice 4.4.x will default to the fdo#81309 behaviour (Sorting should automatically adjust references.)

By changing the behavior to update references, you will be insidiously breaking spreadsheets. Unless the users are looking at the formula, they may not realize that newly sorting spreadsheets are giving invalid results like https://bugs.freedesktop.org/show_bug.cgi?id=85571 Where 1+1 = 3 after a sort with the new system

Why are we breaking old spreadsheets and interoperability for a few users that don't know how to use absolute references?

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

I just want to point out that if sorting (of block in 'test file' above) were to be done 'by hand' (cutting and pasting rows), the result would be the same as with the old style LibreOffice sorting.

Revision history for this message
JBF (jbf-faure) wrote :

I think that the issue described here has nothing to do with the value of the option UpdateReferenceOnSort (see bugs fdo#81309 and fdo#81633). Indeed when tested in the master (version 4.4.0.0.alpha1) it works as expected (behavior of LO 4.2.6.3) whatever the value of the option is. Same thing in LO 4.3.4.0.0+.

Best regards. JBF

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

@JBF: Wrong. If you execute the steps in comment #11 it works as expected by the proponents of fdo#81309 (Sorting should automatically adjust references.).

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@Björn Michaelsen: You are right. As I am not familiar with vim I have written a bash script to modify '/usr/lib/libreoffice/share/registry/main.xcd':

#!/bin/bash

FileToModify="/usr/lib/libreoffice/share/registry/main.xcd"
OrgLine='<prop oor:name="UpdateReferenceOnSort" oor:type="xs:boolean" oor:nillable="false"><value>false</value></prop>'
CorrectedLine='<prop oor:name="UpdateReferenceOnSort" oor:type="xs:boolean" oor:nillable="false"><value>true</value></prop>'

mv "$FileToModify" "${FileToModify}.bak"
cat ${FileToModify}.bak | sed 's%'"${OrgLine}"'%'"${CorrectedLine}"'%g' >"$FileToModify"

######### END OF SCRIPT ################

Use it this way:
sudo ./Script

Revision history for this message
JBF (jbf-faure) wrote :

Hi Bjoern,

I didn't say the contrary, I said that, with LO 4.3.4.0.0+ and LO 4.4.0.0.alpha1+, it works too as expected if the option is set to false. It should be the same in LO 4.2.7 because the value false for the option is intended to be there for backward compatibility. So if it does not work as expected with option set to false, it is a bug.

Best regards. JBF

99 comments hidden view all 127 comments
Revision history for this message
In , JBF (jbf-faure) wrote :

*** Bug 85571 has been marked as a duplicate of this bug. ***

Revision history for this message
In , Luke (lukebenes) wrote :

*** Bug 85968 has been marked as a duplicate of this bug. ***

99 comments hidden view all 127 comments
Revision history for this message
Matthias Ferdinand (mf+ubuntu1) wrote :

Hi,

this update broke several of my spreadsheets that I use regularly, and have been using even back with StarOffice, then OpenOffice and finally LibreOffice for years. While the Ubuntu patch makes it sort in some way different from the "New LO way of updating References", it still breaks.

This is a major break/change in functionality on an LTS platform and just "should not happen(TM)".

For the time being, I have reverted the upgrade and put the libreoffice packages on hold using apt-mark.

Regards
Matthias

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

As a clarification TDFs 4.2.7 release _does_ update references, while Ubuntus 4.2.7-0ubuntu1 by default does not (see comment #17 on how to change that). So:
- TDFs 4.2.7 release shows fdo#81633, but not fdo#81309
- Ubuntus 4.2.7-0ubuntu1 by default shows fdo#81309, but not fdo#81633
Both bugs are mutually exclusive.

For completeness it shall be noted that the original poster on reddit complains about references not being updated after sort in Ubuntu 4.2.7-0ubuntu1 (so he agrees with the reporter of fdo#81309: "Sorting should automatically adjust references."), to which the first commenter on reddit links to fdo#85614 against TDFs builds, which says: "Sorting should automatically adjust references is an ill conceived enhancement.". Apparently these two reddit users are not aware that they have exactly opposing opinions on the expected behaviour.

In fact, switching back Ubuntu default behaviour to the one of TDFs upstream 4.2.7 release by default, while making the reporters of fdo#81309 happy, would just exchange that for those who want fdo#81633 to be resolved.

Repeating again: Comment #17 has a quick way to toggle the default behaviour.

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@Björn Michaelsen: I am reddit OP. I think that distinguishing sorting behaviour by '(not)adjusting references' is prone to confusion (more below). I prefer to use 'old' or 'new' style LibreOffice sorting. 'old' meaning the standard sorting behaviour in all spreadsheet software and LibreOffice up to version 4.2.x.

For me (see #14 above) sorting is a tool that allows me to do painlessly what I could do 'manually' by, laboriously, inserting/removing, cutting and pasting rows. Seen in that light the 'old' sorting style is also the logical one: When I cut and then paste (to a different position) a row of cells the formulae which do not use absolute references are automatically adjusted (i.e. a reference to the cell to the left is maintained) and, therefore, there is no need for more adjusting after (manual) sorting.

Revision history for this message
Luke (lukebenes) wrote :

@Björn Michaelsen

> The 4.2.7-0ubuntu1 package should now satisfy the fdo#81633

Please try the attached test file along with the attached test cases in 81633 such as test_offset.ods and Calc bug.ods. None of these work with Ubuntu's Calc 4.2.7.2, but worked just fine under 4.2.6.

> Apparently these two reddit users are not aware that they have exactly opposing opinions on the expected behaviour.

As Manuel pointed out, what people want is the old sorting behavior back. They're probably using the wrong terminology because this change is confusing and requires toggling a secret option (update refs) that is not well documented.

While 4.2.6 may not have been perfect, as least it mostly followed the industry conventions of updating relative references, while never updating Absolute references. It did this automatically, without the need to toggle any settings that require a restart. Take a look at the use cases in FDO# 85490 to see how many spreadsheets sorted fine in 4.2.6 and Excel but with 4.2.7 there is no clear UpdateRefs setting that always works.

To be clear, with 4.2.7 there is no way to return to the old style sorting routine. Some spreadsheets that worked fine in Excel and 4.26 require UpdateRefs=True, while others require it to be false. Others are broken with either UpdateRefs setting because of bugs introduced in this new sorting routine. Is it any wonder users are confused?

97 comments hidden view all 127 comments
Revision history for this message
In , JBF (jbf-faure) wrote :

*** Bug 86170 has been marked as a duplicate of this bug. ***

Revision history for this message
In , JBF (jbf-faure) wrote :

*** Bug 83864 has been marked as a duplicate of this bug. ***

97 comments hidden view all 127 comments
Revision history for this message
Matt Malone (m-j-malone) wrote :

I configured a new machine with Ubuntu 14.04 months ago. I have been gifted with LibreOffice 4.2.7.2. I was using Ubuntu 12.04 before that, and had LibreOffice 4.1.something. Before that I was using OpenOffice, before that Excell, before that Easy-as-123. All of them update references consistently. I have never noticed an inconsistency in them in about 25+ years of using spreadsheets.

With 4.2.7.2 I immediately noticed that I was unable to sort all 2582 rows of my spreadsheet at once -- it simply crashed. (a different bug)

I have been working with 4.2.7.2 now for MONTHS and only today did I find that it does not sort references properly.

You cannot imagine my disappointment. You cannot imagine the time it is going to require to unmangle this spreadsheet if that is at all possible. It represents 8 years of my work.

Luke (lukebenes) wrote on 2014-11-06:
>Why are we breaking old spreadsheets and interoperability for a few users that don't know how to use absolute references?

Absolutely. Nothing else needs to be said. If the people who want a different type of reording the contents of rows of a spreadsheet want it to do something non-sensical, then, add a new and completely different function called MANGLE and leave SORT alone to work has it has for decades. Sort should work exactly and only like one resorted the sheet by hand by cutting and pasting entire rows. Anything else just violates the entire concept that rows are records and must stay together. If your rows do not represent records that must stay together and you want a result that cannot be achieved by cutting and pasting entire rows then you want to do something other than SORT.

This comment represents only a tiny fraction of the time I will spend correcting the months of mangling caused by this stupidity.

Revision history for this message
Matt Malone (m-j-malone) wrote :

My greatest fear now is all those people who still have not realized this is happening, and, are still in the process or mangling spreadsheets. Until Libre Office releases a patch retroactively fixes all 4.2.7.2 installations on all Ubuntu 14.04 installations, AND pops up a special window when people try to sort in future, telling them that "sorry for months you have not been sorting spreadsheets but rather mangling them, don't worry though, now it works correctly again, sorry for the loss of years of work" then these people will go on mangling spreadsheets

This is to the point of saying Ubuntu 14.04 should be withdrawn to prevent any further damage. It is just not worth it if it damages years of work.

If this is not the end of LibreOffice, I do not know what will be.

Revision history for this message
penalvch (penalvch) wrote :

Björn Michaelsen, to get back to https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/11 , it would appear it is not a a comprehensive WORKAROUND.

In Trusty, after performing the advised edit of main.xcd, and then closing the file, immediately attempting to open any file, or just Calc itself consistently crashed. For example, via a terminal:
localc
terminate called after throwing an instance of 'com::sun::star::uno::RuntimeException'

The crash report on this was sent to errors.ubuntu.com, and https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1392059 .

Unfortunately, reverting the edit doesn't fix the crashing. However, what does is uninstalling, and reinstalling via a terminal:
sudo apt-get -y remove libreoffice-core libreoffice-base-core libreoffice-common && sudo apt-get -y install libreoffice-core libreoffice-base-core libreoffice-common libreoffice-calc libreoffice-writer libreoffice-draw libreoffice-pdfimport libreoffice-gtk

Would I be missing something on the WORKAROUND procedure?

lsb_release -rd && apt-cache policy libreoffice-calc
Description: Ubuntu 14.04.1 LTS
Release: 14.04
libreoffice-calc:
  Installed: 1:4.2.7-0ubuntu1
  Candidate: 1:4.2.7-0ubuntu1
  Version table:
 *** 1:4.2.7-0ubuntu1 0
        500 http://us.archive.ubuntu.com/ubuntu/ trusty-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu/ trusty-security/main amd64 Packages
        100 /var/lib/dpkg/status
     1:4.2.3~rc3-0ubuntu2 0
        500 http://us.archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :
Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@Björn Michaelsen: I would never write something like the example in http://support2.microsoft.com/kb/40401. I completely agree with 'Matt Malone': ' If your rows do not represent records that must stay together and you want a result that cannot be achieved by cutting and pasting entire rows then you want to do something other than SORT'.

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

The longer the current libreoffice calc version stays in popular distros the more spreadsheet documents will be mangled by sorting. Both distro and libreoffice will be blamed (and hated).
Couldn't you update to a (newer) version that has the old sorting style as default?

93 comments hidden view all 127 comments
Revision history for this message
In , Matt Malone (m-j-malone) wrote :

Making this change 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 this one:

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

I am in that camp. I want sorting 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.

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 these 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 people who wanted this change 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. I will live with what they decide.

I believe this will meet the desires of "bug" post.

92 comments hidden view all 127 comments
Revision history for this message
Matt Malone (m-j-malone) wrote :

Some may arrive here and be confused about that to do, given comments #22 and #25.
How do I end any possibility of corrupting my spreadsheet with LibreOffice? This is how
I ended the on-going damage to a years-old spreadsheets. I opened a terminal and pasted this:

sudo apt-get remove --purge libreoffice* libexttextcat-data* && sudo apt-get autoremove

I got that from a A00 page describing how to remove LibreOffice and install AOO. I was
sorely tempted to just keep following their instructions. But I decided that an old version
of LibreOffice untouched by this enhancement stupidity would be adequate. I found a link to
LibreOffice 4.1.6.2 navigated to x86-64 as my desired version and downloaded it with firefox,
landing in my Downloads directory:

http://downloadarchive.documentfoundation.org/libreoffice/old/4.1.6.2/deb/

I then went to my Downloads directory:

cd ~/Downloads
tar -zxf LibreOffice_4.1.6.2_Linux_x86-64_deb.tar.gz
cd LibreOffice_4.1.6.2_Linux_x86-64_deb/DEBS
sudo dpkg -i *.deb

I left that in my downloads directory in case anyone can
give me a compelling case to try another version and
risk years of work on it. If it does not work, I can repeat:

sudo apt-get remove --purge libreoffice* libexttextcat-data* && sudo apt-get autoremove
cd ~/Downloads/LibreOffice_4.1.6.2_Linux_x86-64_deb/DEBS
sudo dpkg -i *.deb

And get back to something I am sure works for me. 4.1.6.2
behaves in a way consistent with other spreadsheet programs.
Other versions might work for you.

I have appreciated that a Google search has solved all my Ubuntu
problems in the past when I discover recipe pages with copy and
paste command lines. I hope mine work for you.

My version immediately asks me if I want to upgrade

NO THANKYOU

When no one has reported a mangled spreadsheet for months,
then I will consider it.

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@ Matt Malone & Christopher M. Penalver: I usedthe the script in #17 to modify (LO calc 4.2.72) '/usr/lib/libreoffice/share/registry/main.xcd' and it works as expected. Sorting reverted to 'old style sorting'.

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

I meant (LO calc 4.2.7.2) in my previous post.
Check this article:
http://www.networkworld.com/article/2845000/opensource-subnet/libreoffice-defends-handling-of-spreadsheet-bug.html
"He stressed that the issue has been corrected – the older sort function will be enabled by default in future versions,"

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

@Manuel: Lets not link to articles that dont help triaging or solving this bug, this is not a mailing list or forum. Thanks.

P.S.: Also note that my fellow ESC and TDF Board member Michael Meeks is talking in that article about the upstream LibreOffice release, which -- unlike the Ubuntu 4.2.7-0ubuntu1 version -- _did_ update references and was criticized in fdo#81633 for that. Its very different from the issue in the Ubuntu release. But as noted: Please keep further discussion that is neither triaging or fixing this bug off this launchpad issue. Thanks.

Revision history for this message
penalvch (penalvch) wrote :

Manuel Iglesias Alonso, in response to https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/30 , thanks for confirming the WORKAROUND works for you. It may very well be a difference in which combination of packages I have installed, or desktop environment choice that makes the difference (I'm using gnome-session-fallback).

Hence, would you mind advising:
+ Which desktop environment(s) are you using where the WORKAROUND works for you in?
+ Are you using the upstream or downstream version of LibreOffice?
+ Are you using a 32-bit or 64-bit OS?
+ Could you please execute the following in a terminal and post the results here:
apt-show-versions -r libreoffice*

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@Björn Michaelsen: Sorry it won't happen again.
I thought the article was relevant because it shows that 'new style sorting' (MANGLING) is a temporary aberration:
SORTING ... MANGLING ... SORTING

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@Christopher M. Penalver:
Desktop: Cinnamon Mint 17
Libreoffice package: Distro standard update: LO calc 4.2.7.2
OS: 64 bit

apt-show-versions -r libreoffice*
libreoffice-base:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-base-core:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-base-drivers:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-calc:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-common:all/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-core:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-draw:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-emailmerge:all/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-gnome:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-gtk:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-help-en-us:all/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-help-es:all/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-impress:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-l10n-en-gb:all/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-l10n-es:all/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-math:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-ogltrans:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-pdfimport:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-presentation-minimizer:all/trusty-security 1:4.2.7-0ubuntu1 uptodate
libreoffice-writer:amd64/trusty-security 1:4.2.7-0ubuntu1 uptodate

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

I just uploaded a version with additional fixes for fdo#82936, fdo#85282 to the ppa at https://launchpad.net/~libreoffice/+archive/ubuntu/libreoffice-4-2. Note that the configuration switch UpdateReferenceOnSort is still defaulting to false as I am still waiting for feedback on that.

86 comments hidden view all 127 comments
Revision history for this message
In , JBF (jbf-faure) wrote :

*** Bug 86304 has been marked as a duplicate of this bug. ***

85 comments hidden view all 127 comments
Revision history for this message
Rowan Wookey (rwky) wrote :

I've tested the build in the ppa with UpdateReferenceOnSort set to false and true and it still behaves weirdly. On my precise install which has 3.5.7.2 sorting by the attached spreadsheets 'Days' column keeps everyting referenced perfectly, with 4.2.7 no matter what I do I can't get it to keep the columns sorted properly.

Revision history for this message
Rowan Wookey (rwky) wrote :

I downloaded Libre Office 4.3.4.1 from the Libre Office website and that works as expected.

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@Björn Michaelsen: I said in a previous comment:
> 'Couldn't you update to a (newer) version that has the old sorting style as default?'

Couldn't you update to Libre Office 4.3.4.1?

Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote :

@Manuel: "Couldn't you update to Libre Office 4.3.4.1?" no, Trusty stays on LibreOffice 4.2.x.

However this ppa:
 https://launchpad.net/~libreoffice/+archive/ubuntu/ppa
usually has the newest upstream LibreOffice available and properly packaged. While not supported by Canonical, it is clearly better than manually installing the generic *.debs downloaded from libreoffice.org.

If you want to upgrade to LibreOffice 4.3.x but not beyond to e.g. 4.4.x, there is this ppa for that:
 https://launchpad.net/~libreoffice/+archive/ubuntu/libreoffice-4-3

Both ppa have a version of 4.3.4.1 (= 4.3.4 final) for utopic and I assume ricotz (https://launchpad.net/~ricotz) will provide backports for Ubuntu 14.04 trusty too soon.

Apart from that, let me remind you again on the customs on this bug tracker: PLEASE STOP discussing things apart from triaging or fixing this issue here. Thanks.

Changed in libreoffice (Ubuntu Trusty):
status: New → Fix Committed
tags: added: verification-needed
tags: added: verification-done
removed: verification-needed
83 comments hidden view all 127 comments
Revision history for this message
In , Mariosv (mariosv) wrote :

*** Bug 87106 has been marked as a duplicate of this bug. ***

Changed in libreoffice (Ubuntu Trusty):
status: Fix Committed → Fix Released
Changed in libreoffice (Ubuntu):
status: Triaged → Fix Released
Changed in df-libreoffice:
importance: Unknown → Critical
status: Unknown → Fix Released
Revision history for this message
Luke (lukebenes) wrote :

@Manuel Iglesias Alonso
Both LO 4.1.6.2 and Excel 2013 will change B7=Customer1 after sorting. The issue is with your spreadsheet NOT with Calc’s sorting function. I have attached an example of how to correctly design a sortable spreadsheet to meet your desired goals. By using a lookup function, this spreadsheet will work in all spreadsheet software with default settings.

Please consider using other resources such as forums and Ask LO/Ask Ubuntu instead of issue trackers for help. Posting bug reports for software your unfamiliar with just adds noise to the development process.

Revision history for this message
Manuel Iglesias Alonso (glesialo) wrote :

@Luke (lukebenes): Thanks.

Revision history for this message
In , Raal (raal) wrote :

*** Bug 87317 has been marked as a duplicate of this bug. ***

Mathew Hodson (mhodson)
Changed in libreoffice (Ubuntu Trusty):
importance: Undecided → High
Displaying first 40 and last 40 comments. View all 127 comments or add a comment.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.