Created attachment 56052 [details] Example file Problem description: Steps to reproduce: 1. If a cell have a complex formula like this: =E58*G58+E327*G327+E267*G267+E67*G67+E37*G37+I37+E188*G188+E263*G263+E251*G251+E246*G246+I246+4*G126+2*G155+E222*G222+I222+E137*G137 2. When ordered cells grow or descending by going to "Data", Sort..." 3. The result of the formula becomes wrong In the attached file, the result of the formula in cell M28 is 320, when i order the column "I" the result in cell M28 becomes 25 and is incorrect. Current behavior: The result of the formula changes Expected behavior: The result of the formula does not change Platform (if different from the browser): Browser: Opera/9.80 (Windows NT 6.1; U; Edition Campaign 21; it) Presto/2.10.229 Version/11.60
I believe the problem is much more simple. Steps to reproduce: 1. open attached rainerssample.ods > in G7 you see a formula "=A1+A2+A3", in G8 "=SUM(A1:A3)" 2. select / highlight A1:A6 3. Menu 'Data -> Sort -> Top to bottom - Column A- Descending' <ok> > Sort order Column A now from 6 -> 1 Expected: Values in G7,G8 still "6", formulas changed to "=A6+A5+A4" and "=SUM(A6:A4)" Actual: Both Cells show calculation result "15", formulas did not change This never worked in a different way, I already see that in OOo 1.1.4 To me that is unexpected, but may be that is the intended behavior (for what reasons ever)? We need further research @andrea.gramegna@gmail.com Is my description concerning your intentions or did you want to report a different issue? Can you please be more detailed and precise in your descriptions? The result of the caldulation does not "become wrong", but the result changes unexpectedly, ...
Created attachment 61069 [details] RainersSample More simple sample document
may be related to bug 38343
Created attachment 76045 [details] test case showing simple formula becoming broken after simple sort I made this test case simplifying a Calc sheet of mine. steps to reproduce : 1. select the lines that contains figures (rows 2 to 6) 2. sort on column B, reverse order 3. see how the formula in column C become broken because it looses its proper references (currently using LibreOffice 4.0.1.1 on Ubuntu 12.04)
I vaguely remember this conversation happening in the past, I agree that it's not expected and IMO *should* be considered a bug as this could lead to miscalculations if the author is not aware of the weird behavior. That being said "logically" it might be called an enhancement if it's expected but then of course it'll get less attention. IMHO we can mark as NEW and prioritize but Rainer I'll await your input from your research
Rainer -any update on this one?
I'm going to confirm this one, clearly not what is expected (not sure if this is by design or not, Rainer might find out some info about that). I have been able to confirm the issue on: Version 3.6.5.2 Platform: Bodhi Linux 2.2 x64 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + As I've been able to confirm this problem on an earlier release I am changing the version number as version is the earliest version that we can confirm the bug, we use comments to say that the bug exists in newer versions as well. Marking as: New (confirmed) Normal - can prevent professional quality work, can result in a user getting bad results without knowing. On more complicated spreadsheets could be a big concern. High - quite a serious problem for anyone dealing with sorting + formulas + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link: https://wiki.documentfoundation.org/QA/BugTriage There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/. Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
*** Bug 62490 has been marked as a duplicate of this bug. ***
*** Bug 62376 has been marked as a duplicate of this bug. ***
*** Bug 38343 has been marked as a duplicate of this bug. ***
@Kohei - this one seems like a popular one, any chance you can take it on or maybe Markus? If not, any other ideas of someone who can poke at the code?
*** Bug 64647 has been marked as a duplicate of this bug. ***
*** Bug 43004 has been marked as a duplicate of this bug. ***
Created attachment 84629 [details] file with 3 different behaviours when sorting attached file shows 3 different behaviours, of which only one looks acceptable: -open document -step 1: sort A8:B13 on column B; A8:A13 now show wrong values -step 2: sort A16:B21 on column B: A15:A21 keep the correct values -step 3: undo step 1 and 2; sort A1:B5 on column B; both A8:A13 and A16:A21 now show wrong values
changed the version as this bug has always been present in LibreOffice
from bug 79441: 'It is broken from the old time, and none spreadsheet I tested (AOO, Excel) are able to fix it. I'm not sure it is possible.' Excel does sort correctly provided the reference is on the same line (e.g. cell B4 contains '=B34'). It should be nice if at least that functionality could be fixed.
Created attachment 100278 [details] Test file to sort descending on column A (In reply to comment #16) > from bug 79441: 'It is broken from the old time, and none spreadsheet I > tested (AOO, Excel) are able to fix it. I'm not sure it is possible.' > > Excel does sort correctly provided the reference is on the same line (e.g. > cell B4 contains '=B34'). It should be nice if at least that functionality > could be fixed. I rebuild attachment 100089 [details] from scratch in Excel 2010 (see attached) and when sorting A15:C20 descending according to column A, column C is not sorted like in LibO. Could you give a procedure which is correctly sorted in Excel and not in LibO?
(In reply to comment #17) > I rebuild attachment 100089 [details] from scratch in Excel 2010 (see > attached) and when sorting A15:C20 descending according to column A, column > C is not sorted like in LibO. > > Could you give a procedure which is correctly sorted in Excel and not in > LibO? I must apologise for any confusion I caused: I don't have Excel myself and relied on a collegue who does and who experiences the sorting problems regularly. His information proved to be incomplete: -when the reference is absolute and on the same row (attachment 100278 [details], range A24:C28) sorting goes well in Excel _and_ in Calc. -when the reference is relative and on the same row (attachment 100278 [details], range A16:C20) sorting goes well in Excel with references to other sheets and wrong with references to the same sheet, and goes wrong in Calc. Versions used here are Excel 2000 and LibreOffice 4.2.5.RC1, both with Windows7. I hope my misinformation didn't cost you too much time. ;-)
(In reply to comment #18) > -when the reference is absolute and on the same row (attachment 100278 [details] > [details], range A24:C28) sorting goes well in Excel _and_ in Calc. OK. No problem if references are absolute. > -when the reference is relative and on the same row (attachment 100278 [details] > [details], range A16:C20) sorting goes well in Excel with references to > other sheets and wrong with references to the same sheet, and goes wrong in > Calc. > > Versions used here are Excel 2000 and LibreOffice 4.2.5.RC1, both with > Windows7. Sorting references to other sheet is broken since LibO4.2.5 RC1, but was OK before. See bug 79441 for that. This bug report concerns only sorting internal references to the *same* sheet, which does not appear working in any spreadsheet. So I'm sure there is an easy solution for this bug.
(In reply to comment #19) > This bug report concerns only sorting internal references to the *same* > sheet, which does not appear working in any spreadsheet. So I'm sure there > is an easy solution for this bug. Please stop insisting on this. Internally the same mechanism is used both for references on the same sheet and other sheets. They are caused by the same root cause!
(In reply to comment #20) > (In reply to comment #19) > > > This bug report concerns only sorting internal references to the *same* > > sheet, which does not appear working in any spreadsheet. So I'm sure there > > is an easy solution for this bug. > > Please stop insisting on this. Internally the same mechanism is used both > for references on the same sheet and other sheets. They are caused by the > same root cause! Sorry for the misunderstanding. I wanted to say exactly the opposite of what I wrote. > > So I'm sure there > > is NOT an easy solution for this bug.
This is a severe bug because you wind up with a spreadsheet with the wrong results. It makes the sort function largely useless.
Please don't change the priority/severity.
Given that this results in spreadsheets with erroneous values, how does it become escalated to a higher priority/severity?
Bugs are fixed by volunteers. Priority/Severity is just used to give pointers to the developers but even if it's marked Critical - Highest, developers can choose not to fix it (this is a volunteer project and we don't push bugs on developers). That being said, we try to stick with a workflow to keep priorities sane - this one I explained why it belongs where it does. Normal - can prevent high quality/professional work High - it's pretty serious (default is medium) For reference: https://wiki.documentfoundation.org/images/0/06/Prioritizing_Bugs_Flowchart.jpg Now this is border line "loss of data" but really it's not, the data isn't lost per say, it's changed (incorrectly) which is a normal bug. Also - this has been around for years (since OOo days) and we have a total of 24 comments (many of them by the same users/QA staff), indicating that many people aren't seeing the problem. Patches more than welcome if you want to have a go at it. FWIW - Kohei already is aware of the bug and he's one of our Spreadsheet experts
A relative in-sheet reference is supposed to point to a cell relative to the formula cell containing the reference and hence changes the value if the formula cell is moved during sort. If the reference shall stick to the same cell then an absolute reference must be used. This behavior is identical to how Excel treats it. With the fix for bug 79441 the behavior now again is also identical for 3D references that include a sheet part, which makes the reference sticky just like in Excel.
I've created an easily duplicated spreadsheet that illustrates what I think may be a problem. I'm not sure if it matches the bug described in this (45156) or if it represents a different bug. If it needs to be a separate bug, then I can create a new one. Note that LO Calc may not be technically doing anything wrong, but it does not match user expectations, which is critical. Create this simple spreadsheet, starting at cell A1: 2014-04-01 SubAssembly1 =B$2+B$3 2014-02-01 Nuts 10 2014-03-01 Bolts 5 2014-04-01 SubAssembly2 =B2+B3 2014-01-01 Grease 50 2014-05-01 Assembly1 =B4+B5 2014-05-01 Assembly2 =B$1+B$5 Now sort rows, using Column A as the key. Notice the Error 522's. Now undo, and sort rows using Column B as the key. Notice the new Error 522's. Now undo, and sort rows using Column C as the key. Notice the new Error 522's. In all these cases, the user had an expectation to be able to sort by any column without the data changing (with only the order of the rows changing). This way, the spreadsheet can be sorted by date, part name, or cost. When I add a fourth column that includes cells that add and subtract specific values from the third column, the problem gets even more pronounced. Note that I intentionally used both absolute and relative references to cover both cases. I had hoped at least one type of reference would work. Obviously, only one type of reference would be used (this is just a demonstration). Is this simple spreadsheet in error, or does it reflect a problem with the output of LO Calc not matching that of user expectations?
Do not reopen bugs that are closed by our most experienced developers. Eike - if you want to reopen it please feel free to do so.
Comments like that are why more people don't contribute to projects like LibreOffice. I took a significant amount of time out of my weekend to lend a hand, and received a slap on the wrist from a rude person as thanks. How likely will I want to contribute more of my time or encourage others to contribute theirs? I'm not an expert. I'm an end user trying to help. I found what looks like a bug and was instructed to post here. As part of my post, there was a flag to reopen the bug, so I set it, as that seems like the most accurate status. If you want this to be closed source and exclude others from contributing the best they can, then perhaps you could create a fork and request permission from all the contributors for a closed license. If you want this to be a project built around a friendly community, make your actions in accordance with that goal. Your post is in conflict with this community's goals of inclusion, equality, and openness.
I apologize if you mistook my "tone" - you're right I could have been more friendly. That being said - Eike is one of our most experienced developer, if he says it's not a bug, it's likely not a bug. Then - if you're going to play around with the settings - please review our extensive documentation for how to do this. https://wiki.documentfoundation.org/QA/BugTriage. Reopened is not the correct status - I am moving this back to UNCONFIRMED - if Eike (or another developer who knows about things) thinks it should be set to NEW they can. Then - in the future if you're not sure if you're reporting a bug or not (ie "Is this simple spreadsheet in error, or does it reflect a problem with the output of LO Calc not matching that of user expectations?") please go to the ask site or user thread first before coming to the bug tracker. Lastly - thanks for taking the time to report and for your patience.
Thanks for your apology Joel. Accepted. And I apologize if I came across as too harsh. This is a tricky bug and how to report it is tricky as well because the "correct" behavior here may be counter to the absolute logical behavior. In other words, we must somehow allow the user to perform needed tasks. How I discovered this bug (and apparently other people found it before myself) is that I created a spreadsheet with the intent of sorting it by different columns. When I would re-sort it, the values would change. Sometimes complicated spreadsheets contain errors, so I created a simple spreadsheet to demonstrate the problem. From what I can tell, it is a very serious problem. But I'm also very open to the possibility (and actually hoping) that I'm just not understanding how something works. Being able to sort is important, and not having the values change during that sort is a basic requirement. The ramifications is that my client has seen values change during the sort and wants to move the entire enterprise off of LibreOffice because she now considers it unreliable. I am a huge proponent of LibreOffice and the sole reason why my clients use it. As much as I love LibreOffice, I'm having a tough battle trying to convince them to stick with LibreOffice when it does not generate the results it seems like it should. Two quick asides: please always try to remember that end users don't know names of the developers. Also, I agree that this bug should be "Unconfirmed" until more comments are made. Eike came to the conclusion that absolute references would solve the problem. I was hoping he was correct, as that would be great. But my example seems to show that absolute references don't solve it. Unless the example is wrong. The bottom line is that we need a way to sort while maintaining data and formula integrity. At the least, this will be a documentation issue so users will know how to do it. At the most, this will be a fairly important bug that will need a fix. Anything that compromises the user's data is important.
FYI, Bug 81309 might help.
Tested with master (4.4.0.0alpha+, build id f3d7734af45f30a87d6de76aa3de7593d541bdc8): The steps described in description now produce 320 in M28 after sorting column I (correct, expected, result). The steps described in comment 1 now produce 6 in G7 (correct, expected, result) and 15 in G8. The steps described in comment 14 now produce correct, expected, results. Setting this bug report to fixed. Kohei, thank you :-)
Created attachment 103002 [details] Demonstration of bug Sorting causes the formulas in the attached file to get messed up in v4.2.5.2. Can someone test this in the new dev version that is supposed to work? If LO crashes, that is from a different bug. Delete all conditional formatting and try again.
Is this still RESOLVED WORKSFORME, or should it marked be UNCONFIRMED?
Verified with versions: - Version: 4.2.7.0.0+ Build ID: 8de2e9b4bc53e6c097897142bad223c100d36292 TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-08-08_21:42:21 - Version: 4.3.2.0.0+ Build ID: 4744400afc9c2be99f62b12180fa33b43acef564 TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-08-11_06:41:42 - Version: 4.4.0.0.alpha0+ Build ID: ad85ce18d3939170e5ef7b1a1f8e5dda2c8aeb9c TinderBox: Win-x86@39, Branch:master, Time: 2014-08-11_05:21:44
@Laurent BP: To clarify, did you verify this bug, or verify that the functionality is correct? How should the status be updated?
(In reply to comment #37) > @Laurent BP: To clarify, did you verify this bug, or verify that the > functionality is correct? > > How should the status be updated? I tested on the mentioned versions in comment #36 that references are correctly updated when sorted. Test with attachment 84629 [details] (comment #14) and attachment 100089 [details] (comment #16). So bug status should be RESOLVED > FIXED. However in these versions, sort seems broken for references to other sheet (bug 79441). So tests should be made on more recent build before closing the bug.
While testing this issue, I found an unrelated issue (82915), and creating a separate bug report for that. When doing so, I realized that the spreadsheet I posted in post #27 of this thread was not properly edited when I simplified it by hand for this bug report. Here's how it should have looked: 2014-04-01 SubAssembly1 =C$2+C$3 2014-02-01 Nuts 10 2014-03-01 Bolts 5 2014-04-01 SubAssembly2 =C2+C3 2014-01-01 Grease 50 2014-05-01 Assembly1 =C4+C5 2014-05-01 Assembly2 =C$1+C$5 BTW, the example intentionally uses 2 different types of references for testing purposes of the bug reported in this thread. If people can try sorting by columns A, B, and C to verify that LO Calc now produces expected results, that would be appreciated. I only had time today to perform a quick test using v4.3.0.4, and it appeared to now work as expected. This of course does not test any issues related to referencing other sheets as reported by others.
No commit notification for this bug report, then we do not know which commit fixed the problem. So the correct status is WorksForMe. Best regards. JBF
This bug report is very confusing and disappointing because its description does not explain clearly and unambiguously what is the expected behavior and which scenario is tested. If we take the description word for word, the expected result is that sorting should keep the value 320 in M28. Ok, now do the following with LO 4.4.0.0.alpha+: 1/ select columns A to M, menu Data > Sort > Column I > Ok Result: we still have 320 but in M32, in M28 we have nothing. 2/ select columns A to I, menu Data > Sort > Column I > Ok Result: we still have 320 in M28 but 24 in A28, that is nothing in the same row as 28 in column A 3/ select columns E to I, menu Data > Sort > Column I > Ok Result: we still have 320 in M28 and 28 in A28 4/ select only column I, menu Data > Sort > Column I > Ok Result: we still have 320 in M28 and 28 in A28 The description says that sorting gives 25 in M28; in LO 4.1 and 4.0 this result is obtained with scenario 1/ only. Scenarios 2/ and 3/ give 100 in M28 and 4/ gives 305 in M28. So the current version does not give the expected result and the bug, if there is a bug, is not fixed. To be able to conclude we need a clear and unambiguous description of the scenario to be reproduced, the result expected and _why_ sorting this way should give this result. Note: it should be more useful to have bug description with files from the real life instead of file without real meaning. In other words, sorting data randomly dispersed on a sheet is useless unless you can explain the corresponding problematic. Set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding. Best regards. JBF
Created attachment 108542 [details] Comparision of Calc 4.4 vs Excel with Decending sorting A1:M500 This feature enhancement should have never been approved. The test case spreadsheet not only never worked on any version of Calc, but it also fails to work on Excel and Google Sheets. We have broken well established spreadsheet workflow for a contrived test case that never worked on any spreadsheet software.
Created attachment 108549 [details] RainersSample comparison 4.4 vs 4.2 vs Excel Sorting RainersSample in 4.4 shows how implementing this "enhancement" breaks spreadsheets. Any older or Excel compatible spreadsheets designed like this will be broken in 4.4.
Removing myself from this bug - please don't readd me to the cc.
Please redo comparisons of 4.4 against earlier releases or Excel with a build of today or later, especially regarding the UpdateReferenceOnSort=false case. Anything else is moot.
This specific request is fixed as of 4.4 when activating Tools->Options->Calc->General "Update references when sorting range of cells" before sort. Note that this will render sorting behavior incompatible with all other spreadsheet applications and previous LibreOffice versions.