Created attachment 109362 [details] Very small spreadsheet that demonstrates BUG 2 Quite simply, for my spreadsheet, SORT on LibreOffice CALC (4.1 something) that shipped with Ubuntu 12.04 worked, and version 4.2.7.2 does not. I have tested it again on my Ubuntu 12.04 machine and the same operations work properly. I have included an example spreadsheet with only 6 cells of data that demonstrates BUG 2. Potential for Work Arounds It is not convenient to use only my 12.04 machine, it is at a different geographical location. It is not convenient to downgrade my 14.04 machine to 12.04, it is a touch screen laptop and 12.04 does not give me functionality. Attempts to just downgrade LibreOffice have not been readily successful. Background: Size of my Spreadsheet My spreadsheet is very large (12 tabs, 2500+ rows in one tab, columns up to W and Z used in two tabs), however, 4.2.7.2 cannot handle sorting even a fraction of it, where 4.1-something can still sort it. The spreadsheet saves as 1.7M in size. BUG 1: Crashes when trying to Sort: If I try to sort 2582 (actually anything more than about 1/3 of that) rows of data spread across columns A-W by column C and D, or D and C, LibreOffice 4.2.7.2 crashes outright. That was really irritating. This problem has existed since I installed 14.04 on a new machine, 4-5 months ago. I have been using filters on column C to avoid sorting as a band-aid, waiting for someone to recognize they broke it, and fix it. BUG 2: Not correctly re-referencing formuli after the sort Today I discovered another problem. LibreOffice 4.2.7.2 is mangling smaller sorts. I have about 100 rows of data in columns A to W. Column A is text. Column B is a formula that does string manipulations on A to convert one style of label to another style of label. (The labels in column A took on form historically and changed form in 2010. Column B redoes labels into a numerical number. Old labels are rendered into a number that makes them sort properly with numbers from the new style. The labels are job numbers.) The formuli in B6 is: =IF(A6="","",IF(MID(A6,7,1)=".",VALUE(MID(A6,2,7)),VALUE(MID(A6,3,1))*1000+VALUE(MID(A6,4,3))+VALUE(MID(A6,2,1))/10)) Nothing terribly bizzare, and, certainly not illegal in any way. If I highlight the block A6-A8, and sort by column B ascending, then the data in column A (no formuli) is in proper order (like it remembered HOW to sort by B), but the formula in B6 is now: =IF(A8="","",IF(MID(A8,7,1)=".",VALUE(MID(A8,2,7)),VALUE(MID(A8,3,1))*1000+VALUE(MID(A8,4,3))+VALUE(MID(A8,2,1))/10)) the cell numbers in formuli are not being properly preserved to refer to the data they were originally referring to in its NEW location. They are now referring to where the data was. The data in A6 was in A8 before the sort. When it was moved from A8 to A6 in the sort, the references in the formuli in the matching rows was not changed from A8 to A6. Other cells in the rows being sorted do refer to other tabs of the sheet, and use functions like SUMIF and INDEX and MATCH to get all the data referring to one job in one row. All these formuli are mucked up in exactly the same manner at column B formuli, but, they are way more complicated and a complete formuli and all dependent data cannot be pasted here as easily as the formula for column B. In my sheet, in a given column, some rows have formuli dependent on other cells or tabs, some have constants or strings. Editing the formuli to be correct on the top row and copying them down will erase the constants and strings and replace them with formuli that result in different data than existed before the sort. This goes to the very heart of what a sort is. If one does not notice it mucking up, years of work can be mangled beyond repair. Fortunately an assistant mangled this sheet once before years ago by failing to sort all columns, I managed to repair it and since then I have paranoidly saved the file with a new version number every time I save it. I was able to go back, but this is not allowing me to go forward. Attached is a small paste from my spreadsheet, a sheet with only A6-B8 in it (same formuli as above). This is what it looks like to me before shorting by B ascending: S14004.1 14004.1 S12082.1 12082.1 S12015.4 12015.4 This is what is looks like after sorting by B ascending: S12015.4 14004.1 S12082.1 12082.1 S14004.1 12015.4 This is obviously wrong. Please contact me at m_j_malone@hotmail.com if there is some default setting of 4.2.7.2 that makes its sorts completely different than every spreadsheet program that went before it that I can correct and make it sort the way every earlier spreadsheet program sorted.
Edits: edit one: (The labels in column A took ONE form historically and changed form in 2010. Column B redoes labels into a numerical number. Old labels are rendered into a number that makes them sort properly with numbers from the new style. The labels are job numbers.) edit two: Nothing terribly bizzare, and, certainly not illegal in any way. If I highlight the block A6-A8, and sort by column B ascending, then the data in column A (no formuli) is in proper order (like it remembered HOW to sort by B), but the formula in B6 is now: obviously should read: Nothing terribly bizzare, and, certainly not illegal in any way. If I highlight the block A6-B8, and sort by column B ascending, then the data in column A (no formuli) is in proper order (like it remembered HOW to sort by B), but the formula in B6 is now:
(In reply to m_j_malone from comment #0) > Quite simply, for my spreadsheet, SORT on LibreOffice CALC (4.1 something) > that shipped with Ubuntu 12.04 worked, and version 4.2.7.2 does not. I Hiya, Sorry to hear that you're experiencing a regression (and crash) in LibreOffice. Given that the 4.2 line just had its last release, could you please verify your results in our (newer) 4.3 branch? Thanks! https://www.libreoffice.org/download/libreoffice-fresh/ > BUG 1: Crashes when trying to Sort: > > If I try to sort 2582 (actually anything more than about 1/3 of that) rows > of data spread across columns A-W by column C and D, or D and C, LibreOffice > 4.2.7.2 crashes outright. Please attach an example of a spreadsheet that demonstrates this problem. (If the file is too large to attach, please try compressing it. If it's still too big, leave a note and we'll get the file another way :-) > BUG 2: Not correctly re-referencing formuli after the sort > > Today I discovered another problem. LibreOffice 4.2.7.2 is mangling smaller > sorts. This sounds like it could be a separate bug. Please file a new bug report for it. Thanks! Once you've attached the example spreadsheet (and hopefully filed #2 as a new bug report), please change the status back to 'UNCONFIRMED'. Thanks! Status -> NEEDINFO
How about you just forget Bug1 and answer Bug2? I cannot post that spreadsheet, it is propriety and I did post a spreadsheet for bug 2. I have no time to go through the hassle of downloading 4.3 and testing it. I downgraded to a version that works. When Ubuntu comes out with a new long term service release, I might upgrade, if I have the time. Right now I am kind of busy fixing my mangled spreadsheets. I see bug #2 hit the news already. http://www.networkworld.com/article/2845000/opensource-subnet/libreoffice-defends-handling-of-spreadsheet-bug.html https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858 https://bugs.freedesktop.org/show_bug.cgi?id=81309 https://bugs.freedesktop.org/show_bug.cgi?id=81633 Too bad LibreOffice did not warn me in a really obvious way that they changed something so fundamental and standardized across other spreadsheets before the sorting mangled my spreadsheet.
(In reply to m_j_malone from comment #3) > How about you just forget Bug1 and answer Bug2? We've got literally thousands of bug reports filed against LibreOffice. We're trying to go through them as quickly and efficiently as possible, so please help us out by filing just one bug per report. > I cannot post that > spreadsheet, it is propriety It's really hard for us to track down a crash or a performance problem without having the ability to reproduce the problem ourselves. This page can help you remove any proprietary information from your document before you post it: https://wiki.documentfoundation.org/QA/Bugzilla/Sanitizing_Files_Before_Submission > and I did post a spreadsheet for bug 2. I > have no time to go through the hassle of downloading 4.3 and testing it. LibreOffice 4.2 will reach End of Life in 1 week: https://wiki.documentfoundation.org/ReleasePlan#4.2_release ...at that point we'll need someone to reproduce this bug using a modern build of LibreOffice (either 4.3 or a pre-release/dev build of 4.4). That helps us to confirm that the problem still exists in versions we're currently shipping. > I > downgraded to a version that works. When Ubuntu comes out with a new long > term service release, I might upgrade, if I have the time. Right now I am > kind of busy fixing my mangled spreadsheets. Upgrading is totally up to you. If you upgrade, you can help us identify if the crashing problem still exists, which in turn will allow us to improve LibreOffice not just for you, but for everyone. I think it's a clear win-win situation for both of us.
Your test file is corrupted or faulty, when I open it with LO 4.2.6 or LO 4.4.0.0.alpha2 I get Err:502 in B1:B3. If it is the result of the sorting, please attach alse the test file in its state before sorting. Set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding. Best regards. JBF
Here is a second, even simpler spreadsheet that 4.2.7.2 cannot sort properly. Ask it to sort by column B ascending with A1-B2 as the range. As for the process described for altering a spreadsheet to remove confidential data, I have examined it. The process would not remove the uniqueness of the numbers of the spreadsheet. I might recommend that LO add a 2 functions to anonymize data in a range. The first function goes through each cell in the defined range. It examines the contents and replaces it and all identical occurences of that data (either numerical or text) with a random equivalent everywhere in all tabs of the spreadsheet. For occurrences found within the defined range, they are marked as "done" and are not doubly randomized. Text becomes random text of similar length. Numbers become random numbers (never zero) of similar parameters. So $5.45 becomes $10.71, but never 6.024E24 for instance. The second function does not make global searches and replaces, the data in each cell is just replaced with random data of similar nature. Therefore two occurences of $5.45 become different amounts. One might then randomize a spreadsheet with little manual labour while maintaining the action of MATCH and SUMIF operations. If the linking of these functions were not preserved in the randomization of my spreadsheet, it would completely change the nature of sorting it. However, someone will no doubt point out there is some other archane system for making suggestions. Returning to the problem of 4.2.7.2 not sorting correctly, In this reference: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1389858 Lukebenes reportedly supplied a spreadsheet that 4.2.7 could not sort properly (the same way 4.2.6 did) with either setting of the secret setting. Luke (lukebenes) wrote (in part) on 2014-11-11: ... 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? I recommend taking his sample spreadsheets from that thread test_offset.ods and Calc bug.ods and verify those. This matter is so urgent, I hope someone can use a little imagination and use these sources in other bug reports as tests.
Created attachment 109423 [details] Even smaller file that sorting A1-B2 by column B ascending fails in 4.2.7.2
Jean-Baptiste Faure, you are the same person who proposed the preposterous: https://bugs.freedesktop.org/show_bug.cgi?id=81633 That lead to all the problems and mangled my spreadsheet and many others. I do not consider you either sufficiently knowledgeable because of all the damage you have cause, nor sufficiently independent to resolve this bug. I ask that you recuse yourself from this matter. Before you go citing what various other programs promised to do with sorting, like you do here: https://bugs.freedesktop.org/show_bug.cgi?id=81309 And attack what Microsoft has written, you should know, I do not care what any other spreadsheet program developer wrote. I want 4.2.7.2 to work like 3.5 did on Ubuntu 12.04 (yes, that is a correction) because this was compatible with what Excel did. I have been told 4.2.6 is compatible with Excel also. I have downgraded to 4.1.6.2 and it behaves as Excel did. Anything that you change that makes LibreOffice incompatible with what previous versions, and other spreadsheet programs DID and leads to mangling of spreadsheets is not an enhancement, it is breaking it.
(In reply to m_j_malone from comment #8) > Jean-Baptiste Faure, you are the same person who proposed the preposterous: > > https://bugs.freedesktop.org/show_bug.cgi?id=81633 > > That lead to all the problems and mangled my spreadsheet and many others. Please, explain. I reported bug 81633 to show that the fix for bug 81309 introduced a regression when sorting spreadsheets like attachment 108255 [details]. Best regards. JBF
End the mangling that is going on right now out there in the field. That should be the #1 priority. The priority should NOT be polishing FRESH and STILL and discussing obscurities and poorly composed spreadsheets that never would have worked with a cut and paste hand-sort model of what sort is conceptually. You cannot make every stupid use of links work in every case with sort without violating the concept of what a sort is. Again, if you want something that is not a cut-and-paste model sort of records, create a new button called MANGLE for it so there is no confusion. Call it the mangle enhancement. So it appears Kohei Yoshida introduced a bug in some obscure use to correct for some badly written sheet. Then after discussion with you, and with your cooperation introduced another bug and a secret option that does not work properly (like other spreadsheet programs) in either setting. This was then pushed out to existing working versions, with your assistance and cooperation. Even after being shown proof, you defended this noconforming behaviour as an enhancement. And I have yet to see any unequivocally statement that all traces of this debacle have been withdrawn from 4.2.7.2 -- the first and the second change so that 4.2.7.2 works as previous versions and other spreadsheets do. I hear what others have said, that it is not possible, now that the bad code is in 4.2.7.2, only creating an upgrade version without any of this bad code in it, 4.2.8 for instance, will solve this problem. And you seem completely oblivious to the users out there STILL using a mangling version of 4.2.7.2 (because that is what was delivered with Ubuntu 14.04 LTS) who STILL have not noticed they are mangling their old spreadsheets every time they have sorted them. What is the plan to stop the mangling that is going on right now ? If 4.2.6.2 is absent of any trace of this foolishness, then why did someone not copy it to a new directory called 4.2.8 and trigger an update of all 4.2.7.2 to code that works like other spreadsheets do ? End the mangling, that should be priority #1, not this overly detail oriented discussion referencing bug reports. WIND IT BACK. Do it now. Why was that not done several days ago? Today is Friday, a lot of people have automated backups that run on the weekend. Their hope to retrieve a non-mangled version of their years-old spreadsheet might be gone by Monday. Oh, oh, is the excuse going to be that this is free software, all software has bugs, we are just volunteers ? How long would it take to copy all of 4.2.6.2 (or whatever works like other spreadsheets do, and earlier versions of LibreOffice did) to 4.2.8 and save people years of pain. To me it seems so easily done that it would be like not helping an old lady up off the sidewalk. You just have no concept of how valuable old spreadsheets are to people, even when told, over days, over several threads, bug reports. I want to hear in all these threads, an unequivocal statement, all of this foolishness has been completely extracted from the code and will never appear in any future version. So now some plain-English questions you might be able to answer. I do not want to hear any more euphemisms like regression or gobbledy-gook about current, fresh, whatever, or citations to other bug reports that are just as difficult to read through, or have to run one more badly composed sample sheet through 4.1.6.2 to discover, there was never a problem before you started monkeying with it. When, what date and time, will it be safe to accept updates to LibreOffice through the Ubuntu software centre ? When will there be a plain English assurance that all traces of this foolishness have been completely and utterly removed from the pipeline? Or are there people at LibreOffice still clinging to this as an "enhancement" and are leaving it in in any form, even as an option? Anything less than a clear statement of a complete and utter withdrawl of all parts of this from the pipeline, will say to me, all versions of LibreOffice past 4.1.6.2 (the version I currently use) are potential years of work-destroying poison that are incompatible with every other spreadsheet program and are to be avoided at all costs. Daily I will have to uncheck to decline updates to LibreOffice through the software centre. One day that will just become too much tedium, and I will move to another office package. When others finally discover their sheets have been mangled for more than a week now, with you fully aware of it, and you have done nothing to push functional software to their machine through updates, and they read even a fraction of these pages, I doubt many of them will even give LibreOffice a second chance. End the mangling that is going on right now out there in the field. That should be the #1 priority. Then a clear statement so I believe you and all of LibreOffice finally understand.
Works as expected with versions 4.3.4 and master (4.4.0.0.alpha2) whatever the value of the option UpdateReferenceOnSort is. Please, try again with the last update of the version 4.2.7 provided by Ubuntu. Closing as WorksForMe.