desktop-packages team mailing list archive

Thread
Date

[Bug 1389858] Re: Libreoffice calc 4.2.7-0ubuntu1 not updating references after sort

To: desktop-packages@xxxxxxxxxxxxxxxxxxx
From: Bug Watch Updater <1389858@xxxxxxxxxxxxxxxxxx>
Date: Sun, 14 Dec 2014 00:08:46 -0000
Reply-to: Bug 1389858 <1389858@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx

Launchpad has imported 74 comments from the remote bug at
https://bugs.freedesktop.org/show_bug.cgi?id=81633.

If you reply to an imported comment from within Launchpad, your comment
will be sent to the remote bug automatically. Read more about
Launchpad's inter-bugtracker facilities at
https://help.launchpad.net/InterBugTracking.

------------------------------------------------------------------------
On 2014-07-22T04:47:46+00:00 JBF wrote:

The fix for bug 81309 seems to generate a regression in the behavior of
some spreadsheet when sorting. Steps to reproduce:

1/ open attached file to bug 81617: https://bugs.freedesktop.org/attachment.cgi?id=103205
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. It worked in all previous versions of LibreOffice and OO.

Remarks:
a/ I discovered this regression on 4.3.1.0.0+ Build ID: f6445efb0e5c3de099f0425825fe1e6e8271dfb9 build at home (complete clean build) on Ubuntu 14.04 x86-64
2/ This bug does not affect 4.2.7.0.0+ updated and build one day ago
3/ this bug affects 4.2.7.0.0+ after backport of bug 81309 fix (Build ID: f5949d09321e3ac62538df0e70e58284bd1cab32)

Best regards. JBF

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/0

------------------------------------------------------------------------
On 2014-07-22T05:04:45+00:00 JBF wrote:

Set version number to unspecified because it affects development versions of each branch 4.2 and 4.3 and probably the master.
Version: 4.2.7.0.0+ Build ID: f5949d09321e3ac62538df0e70e58284bd1cab32
Version: 4.3.1.0.0+ Build ID: f6445efb0e5c3de099f0425825fe1e6e8271dfb9

Best regards. JBF

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/1

------------------------------------------------------------------------
On 2014-07-22T05:16:52+00:00 JBF wrote:

After discussion on IRC, master is a better version number here.

Best regards. JBF

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/2

------------------------------------------------------------------------
On 2014-07-22T09:02:28+00:00 JBF wrote:

Tested to be sure: reverting "fdo#81309: Adjust references during sort."
http://cgit.freedesktop.org/libreoffice/core/commit/?h=libreoffice-4-3&id=5c3bcc6fe3653aed79a11abbead66f11896e7ee8
on version 4.3.1.0.0+ (build ID:
f6445efb0e5c3de099f0425825fe1e6e8271dfb9) under Ubuntu 14.04 x86-64
solves the problem for me.

Best regards. JBF

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/3

------------------------------------------------------------------------
On 2014-07-27T02:11:43+00:00 Jmadero-dev wrote:

Confirmed
Ubuntu 14.04 x64
LibreOffice 4.4 master
Built: Mon Jul 21 13:38:10 2014 -0400

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/4

------------------------------------------------------------------------
On 2014-07-29T17:29:37+00:00 Libreoffice-0 wrote:

You guys need to talk to those users who think sorting should adjust
references.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/5

------------------------------------------------------------------------
On 2014-07-29T17:30:31+00:00 Libreoffice-0 wrote:

Calling this a regression is a testament to the meme that one man's
feature is another man's bug. We all lose.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/6

------------------------------------------------------------------------
On 2014-07-29T18:05:50+00:00 Libreoffice-0 wrote:

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!".

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/7

------------------------------------------------------------------------
On 2014-07-30T03:59:19+00:00 Jmadero-dev wrote:

@Kohei - whatever you think is best. I'm sure this is annoying for you
having different users expecting different results :( If you want to
mark as WONTFIX or as an enhancement as you described - whatever is best
opinion.

Marking as bibisected since the commit has been identified.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/8

------------------------------------------------------------------------
On 2014-08-04T21:00:10+00:00 JBF wrote:

Created attachment 104016
another example of inconsistency in sorting

This attachment is the same as the previous except I have added a column
with a formula using the OFFSET() function. This formula in column E
gives the same result as the formula in column D. It is an attempt to
keep the reference to the cell just above the current one.

If you select the range A2:E14 and sort ascending, you get several
errors 523 in column E. Clearly it is not what is expected. If you do
the same thing in LO 4.1.6 both columns still give the same result.

What is more inconsistent is that the sorting does not treat the part
(Cn-Bn) in both columns the same way. It is updated in column D while it
is not in column E.

As the change introduced by the fix for bug 81309 is big, I think it
shouldn't be done in bugfix versions like 4.2.7 and 4.3.1. In other
words, it is not a fix for a bug, it is a paradigmatic change.

Best regards. JBF

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/9

------------------------------------------------------------------------
On 2014-09-02T11:49:37+00:00 Guilleron29 wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/10

------------------------------------------------------------------------
On 2014-09-02T12:12:08+00:00 Jumbo4444 wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/11

------------------------------------------------------------------------
On 2014-09-02T16:22:01+00:00 chris wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/12

------------------------------------------------------------------------
On 2014-09-02T23:36:16+00:00 Mariosv wrote:

I think adjust it's fine, but in my understanding, sometimes wrong, it
must be in the same way like Copy/Paste, adjust relative references and
retain absolute references, like it is supposed a spreadsheet works.
It's an user matter use properly relative/absolute references.

Now with sample in https://bugs.freedesktop.org/show_bug.cgi?id=83276:

A9: =IF(C9<C8;RANK(C9;$C$2:$C$16;0);IF(C9>C8;"Re-
sort";IF(AND(C9=C8;B9<B8);"Re-sort";"")))

changing C9 value to 500,
after Menu/Data/Sort - Column C descending / Options - Range contain column labels.
now in
A5: =IF(C5<C9;RANK(C5;$C$2:$C$16;0);IF(C5>C9;"Re-sort";IF(AND(C5=C9;B5<B9);"Re-sort";"")))

seems that references to the same row are updated properly (C5), but
references to other rows change in a strange way for me (C8->C9), maybe
they have several changes while sorting.

Forgive me, but I can't see why keep relative references changed in a different way than Copy/Paste, when a priori you don't know what data finish in what row.
I think most times comparisons/sums are with previous/next row, like in the sample, or to sum all the previous or all the rest rows/columns.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/13

------------------------------------------------------------------------
On 2014-09-03T07:53:13+00:00 Dmdcaretwo wrote:

(In reply to comment #13)
> A9:
> =IF(C9<C8;RANK(C9;$C$2:$C$16;0);IF(C9>C8;"Re-sort";IF(AND(C9=C8;B9<B8);"Re-
> sort";"")))
>
> changing C9 value to 500,
> after Menu/Data/Sort - Column C descending / Options - Range contain column
> labels.
> now in
> A5:
> =IF(C5<C9;RANK(C5;$C$2:$C$16;0);IF(C5>C9;"Re-sort";IF(AND(C5=C9;B5<B9);"Re-
> sort";"")))
>
> seems that references to the same row are updated properly (C5), but
> references to other rows change in a strange way for me (C8->C9), maybe they
> have several changes while sorting.

After sorting, the formula in A6 reads:
=IF(C6<C4,RANK(C6,$C$2:$C$16,0),IF(C6>C4,"Re-sort",IF(AND(C6=C4,B6<B4),"Re-sort","")))

Similarly, A10 reads:
=IF(C10<C5,RANK(C10,$C$2:$C$16,0),IF(C10>C5,"Re-sort",IF(AND(C10=C5,B10<B5),"Re-sort","")))

I agree that the references need updating properly - the current method
updates some, but not all, relative references.

As far as I can tell, I now have no method of knowing reliably when to
sort my tables - unless I copy a correct formula to all the rows after
sorting my tables (not practical).

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/14

------------------------------------------------------------------------
On 2014-09-03T19:08:56+00:00 Libreoffice-commits wrote:

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#81633: Add a new configuration option to toggle ref update on 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/15

------------------------------------------------------------------------
On 2014-09-03T19:09:11+00:00 Libreoffice-commits wrote:

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#81633: Write test for this.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/16

------------------------------------------------------------------------
On 2014-09-03T19:11:55+00:00 Libreoffice-0 wrote:

This one is now configurable.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/17

------------------------------------------------------------------------
On 2014-09-03T19:18:17+00:00 Jmadero-dev wrote:

Just FYI this will NOT be backported. So it'll be available in 4.4 daily
in the next 24 hours.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/18

------------------------------------------------------------------------
On 2014-09-04T07:14:48+00:00 Dmdcaretwo wrote:

(In reply to comment #18)
> Just FYI this will NOT be backported. So it'll be available in 4.4 daily in
> the next 24 hours.

Forgive my ignorance but does that mean I will have to wait until 4.4 is
published in early 2015?

why can the fix not be part of 4.3.3

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/19

------------------------------------------------------------------------
On 2014-09-04T07:41:05+00:00 Jmadero-dev wrote:

Do not reopen bugs that have been closed by a developer as FIXED.

I've talked to Kohei and he has reasons why it was not pushed to 4.3. If
you want to look at the patch, review the patch, recommit the patch
against 4.3, convince another developer to review that, etc. . . etc . .
. you are free to do so. Else, yes, you will have to wait until 4.4.
Removing myself from CC on this.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/20

------------------------------------------------------------------------
On 2014-09-04T07:44:40+00:00 Jmadero-dev wrote:

One other thing - you are free to use 4.4 daily builds (not recommended
but you are free to do so)

http://dev-builds.libreoffice.org/daily/

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/21

------------------------------------------------------------------------
On 2014-09-09T09:00:02+00:00 chris wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/22

------------------------------------------------------------------------
On 2014-09-20T05:43:55+00:00 Bondi3880 wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/23

------------------------------------------------------------------------
On 2014-09-22T04:31:09+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/24

------------------------------------------------------------------------
On 2014-09-22T04:51:44+00:00 Adolfo Jayme wrote:

(In reply to comment #19)
> Forgive my ignorance but does that mean I will have to wait until 4.4 is
> published in early 2015?
>
> why can the fix not be part of 4.3.3

Because there is an UI and string freeze in effect for release branches;
see https://wiki.documentfoundation.org/ReleasePlan. You’re going to
wait less than five months, not a big deal.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/25

------------------------------------------------------------------------
On 2014-09-22T08:08:14+00:00 Bondi3880 wrote:

AJ wrote
>You’re going to wait less than five months, not a big deal.

Some might say that's a rather cavalier attitude, perhaps?
The way I see it is that some one (or people) broke LO some releases back for a reason I don't understand.

Maybe 5 months is not a big deal to you, AJ, but I have to have LO
working NOW the way it was - otherwise LO is as good as useless to me.
:-(

I simply cannot mess around with daily builds for five months or retro-
installing a previous version.

Fortunately there are alternatives that DO work as LO used to. Yep - us
users are fickle, all right. :-D

Ian.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/26

------------------------------------------------------------------------
On 2014-09-22T11:25:24+00:00 Dmdcaretwo wrote:

I support Ian's comments.

I also wonder how many LO users have been sorting there data and not
noticed the formulas have been corrupted.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/27

------------------------------------------------------------------------
On 2014-09-27T14:49:40+00:00 Mariosv wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/28

------------------------------------------------------------------------
On 2014-10-07T17:25:06+00:00 Iplaw67 wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/29

------------------------------------------------------------------------
On 2014-10-08T02:41:24+00:00 Bondi3880 wrote:

Hmmmmmm .... This bug has become an epidemic it seems and, like Ebola,
it needs swift action NOW, seeing it was LO developers that caused it in
the first place.

I've been in electronics engineering all my life and using spreadsheets since Lotus 123 came on the scene. Replacing something so basic (correct sorting) with something so esoteric I can't understand is sheer madness and totally reprehensible software development on the part of whoever introduced this new 'feature'. This 'new feature' is what should have been delayed and ADDED to 4.4. I can only hope LO software engineers have taken note and learned the lesson of not REPLACING long-established functions. Geez ... I hope Microsoft NEVER does away with the Vulcan Nerve Pinch (AKA Ctrl-Alt-Delete) :-D

At risk of being banned from this thread, I'm going to recommend that anyone who MUST have their Calc spreadsheets working properly have a look at Apache Open Office.org - the latest version works properly for me ... and my relieved customers.

STATUS: RESOLVED FIXED? I don't think so. Nothing has been resolved or
fixed for us users.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/30

------------------------------------------------------------------------
On 2014-10-08T02:56:38+00:00 chris wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/31

------------------------------------------------------------------------
On 2014-10-08T08:06:33+00:00 Scno wrote:

I reported a duplicate of this bug. So I should be done. BUT, I have to
really add my opinion here.

Ian is totally right. I am pissed off too. U can't do such a thing
without a loud warning and not in a minor version.

I by myself was waiting that sort does not crash anymore or mixes up
conditional formatting, not to speak off some printing stuff to be
fixed. That were things I could see.

Now I thought all is working and U come up with some new hip functions
which leads to silent corruption in tables I use for over 10 years!

U are so proud of Ur fast development cycle, think it over! Basic
functions are just not working or have to handled with ugly workarounds,
which never should have made it into an rc.

I am back at 4.1.6 I think. I don't know now, I have to check the other
bugs which kept me from using the new versions.

I should create a bugs vs. versions matrix as a little helper to choose
the right LO version for me, but I think I take sheet of paper in case I
wanna sort this...

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/32

------------------------------------------------------------------------
On 2014-10-08T08:10:13+00:00 JBF wrote:

I asked the ESC to revert this commit from branches 4.2 and 4.3

https://bugs.freedesktop.org/show_bug.cgi?id=68566#c20
https://bugs.freedesktop.org/show_bug.cgi?id=68566#c22

Best regards. JBF

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/33

------------------------------------------------------------------------
On 2014-10-08T10:14:10+00:00 Bondi3880 wrote:

Just a thought ...
Why not just replace the Calc program from 4.1.? (whatever the version that worked) into the next release and hold this "new feature" until 4.4?
Then those that absolutely must have this 'new feature' can keep on using the current version and those that want it to be the way it was can get on with the job of being productive again.

This 'new feature' has cost me a hell of a lot of time - not only
working out what the problem was in the first place, but then having to
placate hostile customers who blamed ME for stuffing up their
spreadsheets.

I can see that no amount of strongly worded comments is going to get
through the arrogance of developers, so I'm simply going to revert to,
and stay with, Apache Open Office.

It is pointless staying with this bug report, so I'm removing myself
from the CC list.

Good luck to all you others who want to wait for LO 4.4

Regards,
Ian.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/34

------------------------------------------------------------------------
On 2014-10-08T15:45:36+00:00 Jumbo4444 wrote:

Another way to avoid this new behavior if you are really annoyed is to switch back to LibO 4.1.6
http://downloadarchive.documentfoundation.org/libreoffice/old/4.1.6.2/

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/35

------------------------------------------------------------------------
On 2014-10-09T03:55:00+00:00 Luke wrote:

Kohei,
In 4.4 please change the default of Tools->Options->Calc->General->"Update Refs when Sorting" to UNCHECKED. It should be unchecked because the current behavior could be destructive to our users tables. This is also how all of the other major spreadsheets behave. MS Excel, AOO Calc, Google Sheets, Gnumeric, and Kingsoft Spreadsheets all do NOT update refs when sorting. Please change the default to follow the well established standard.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/36

------------------------------------------------------------------------
On 2014-10-09T04:10:44+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/37

------------------------------------------------------------------------
On 2014-10-09T04:46:11+00:00 Luke 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/38

------------------------------------------------------------------------
On 2014-10-09T09:03:46+00:00 Luke wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/39

------------------------------------------------------------------------
On 2014-10-09T10:30:24+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/40

------------------------------------------------------------------------
On 2014-10-09T12:12:35+00:00 Photon713 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/41

------------------------------------------------------------------------
On 2014-10-09T14:33:13+00:00 Qubit wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/42

------------------------------------------------------------------------
On 2014-10-09T18:51:48+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/43

------------------------------------------------------------------------
On 2014-10-09T20:49:19+00:00 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/44

------------------------------------------------------------------------
On 2014-10-10T09:15:05+00:00 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/45

------------------------------------------------------------------------
On 2014-10-10T11:56:16+00:00 Fred 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)"

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/46

------------------------------------------------------------------------
On 2014-10-10T12:00:31+00:00 Fred Olness wrote:

Created attachment 107658
Short example of the bug

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/47

------------------------------------------------------------------------
On 2014-10-10T12:04:25+00:00 Erik 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/48

------------------------------------------------------------------------
On 2014-10-10T15:24:33+00:00 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/49

------------------------------------------------------------------------
On 2014-10-17T18:25:38+00:00 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).

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/50

------------------------------------------------------------------------
On 2014-10-17T20:09:06+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/51

------------------------------------------------------------------------
On 2014-10-17T20:44:03+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/52

------------------------------------------------------------------------
On 2014-10-20T04:52:42+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/53

------------------------------------------------------------------------
On 2014-10-22T19:00:42+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/54

------------------------------------------------------------------------
On 2014-10-26T14:15:43+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/55

------------------------------------------------------------------------
On 2014-10-26T17:07:29+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/56

------------------------------------------------------------------------
On 2014-10-26T17:09:38+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/57

------------------------------------------------------------------------
On 2014-10-27T07:43:41+00:00 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/58

------------------------------------------------------------------------
On 2014-10-27T19:04:59+00:00 Luke 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/59

------------------------------------------------------------------------
On 2014-10-27T19:30:24+00:00 JBF 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/60

------------------------------------------------------------------------
On 2014-10-28T04:15:02+00:00 Luke 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/61

------------------------------------------------------------------------
On 2014-10-28T08:08:20+00:00 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/62

------------------------------------------------------------------------
On 2014-10-28T15:03:40+00:00 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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/63

------------------------------------------------------------------------
On 2014-10-28T21:06:43+00:00 Gerard-fargeot wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/64

------------------------------------------------------------------------
On 2014-10-29T02:11:15+00:00 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/65

------------------------------------------------------------------------
On 2014-10-29T05:47:12+00:00 JBF wrote:

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

Setting back to resolved fixed.

Best regards. JBF

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/66

------------------------------------------------------------------------
On 2014-11-06T19:04:41+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/86

------------------------------------------------------------------------
On 2014-11-07T00:24:41+00:00 Luke wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/87

------------------------------------------------------------------------
On 2014-11-12T05:56:28+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/92

------------------------------------------------------------------------
On 2014-11-12T06:05:03+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/93

------------------------------------------------------------------------
On 2014-11-13T15:31:38+00:00 Matt 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.

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/100

------------------------------------------------------------------------
On 2014-11-16T06:53:40+00:00 JBF wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/109

------------------------------------------------------------------------
On 2014-12-08T16:35:28+00:00 Mariosv wrote:

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

Reply at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858/comments/118

** Changed in: df-libreoffice
Status: Unknown => Fix Released

** Changed in: df-libreoffice
Importance: Unknown => Critical

** Bug watch added: freedesktop.org Bugzilla #83276
https://bugs.freedesktop.org/show_bug.cgi?id=83276

** Bug watch added: freedesktop.org Bugzilla #68566
https://bugs.freedesktop.org/show_bug.cgi?id=68566

** Bug watch added: freedesktop.org Bugzilla #81309
https://bugs.freedesktop.org/show_bug.cgi?id=81309

--
You received this bug notification because you are a member of Desktop
Packages, which is subscribed to libreoffice in Ubuntu.
https://bugs.launchpad.net/bugs/1389858

Title:
Libreoffice calc 4.2.7-0ubuntu1 not updating references after sort

Status in LibreOffice Productivity Suite:
Fix Released
Status in libreoffice package in Ubuntu:
Fix Released
Status in libreoffice source package in Trusty:
Fix Released

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)

To manage notifications about this bug go to:
https://bugs.launchpad.net/df-libreoffice/+bug/1389858/+subscriptions

References

[Bug 1389858] [NEW] Libreoffice calc 4.2.7 not sorting right
From: Marc Deslauriers, 2014-11-05