Description: I have noticed this problem several times now. I have a small spreadsheet database for authors books and dates. When I perform a sort using column B as the primary sort and column C as the secondary sort the sort output does not put all the column C data in its correct order. Steps to Reproduce: 1. On the data provided select the block to be sorted 2. Ensure that the parameters for sorting are correct (able to select column B, the column C 3. Enter OK Actual Results: After Sorting ? (I also tried ‘natural sorting’-- no better) Supposedly sorted on column B then column C both low to high. Cornwell, Patricia Andy Brazil 2001 Isle of the Dogs Cornwell, Patricia Andy Brazil 1998 Southern Cross Cornwell, Patricia Kay Scarpetta 2005 Predator Cornwell, Patricia Kay Scarpetta 1990 Post Mortem Cornwell, Patricia Kay Scarpetta 1992 All that Remains Cornwell, Patricia Kay Scarpetta 1995 From Potter Field Cornwell, Patricia Kay Scarpetta 1996 Cause of Death Cornwell, Patricia Kay Scarpetta 1997 Unnatural Exposure Cornwell, Patricia Kay Scarpetta 1998 Point of Origin Cornwell, Patricia Kay Scarpetta 1999 Black Notice Cornwell, Patricia Kay Scarpetta 2007 Book of the Dead Cornwell, Patricia Kay Scarpetta 2010 Port Mortuary Cornwell, Patricia Win Garano 2006 At Risk Cornwell, Patricia Win Garano 2008 The Front Expected Results: Cornwell, Patricia Andy Brazil 1998 Southern Cross Cornwell, Patricia Andy Brazil 2001 Isle of the Dogs Cornwell, Patricia Kay Scarpetta 1990 Post Mortem Cornwell, Patricia Kay Scarpetta 1992 All that Remains Cornwell, Patricia Kay Scarpetta 1995 From Potter Field Cornwell, Patricia Kay Scarpetta 1996 Cause of Death Cornwell, Patricia Kay Scarpetta 1997 Unnatural Exposure Cornwell, Patricia Kay Scarpetta 1998 Point of Origin Cornwell, Patricia Kay Scarpetta 1999 Black Notice Cornwell, Patricia Kay Scarpetta 2005 Predator Cornwell, Patricia Kay Scarpetta 2007 Book of the Dead Cornwell, Patricia Kay Scarpetta 2010 Port Mortuary Cornwell, Patricia Win Garano 2006 At Risk Cornwell, Patricia Win Garano 2008 The Front Reproducible: Always User Profile Reset: No Additional Info: The second line should have been first 2001 > 1998 The third line (2005) should have been between 1999 and 2007 Version: 6.0.1.1 Build ID: 1:6.0.1-1~bpo9+1 CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk2; Locale: en-US (en_US.UTF-8); Calc: group
Please attach test file before sorting. Thank you.
Also could you give a try to a recent LO version (6.2.7 or brand new 6.3.1)? Indeed 6.0 and 6.1 are EOL.
Created attachment 154333 [details] New version still did not sort correctly.
The issue it that 2005 is a number in a number format cell, while the next are number in cells formatted as text, so text not number, so in sort go after not before. Doing a clear format [Ctrl-M] makes visible what is happening. For me not a bug, please if you are not agree reopen it.
I agree that the sorting IF the numbers are formatted as text, not numbers. However, the result of the sort is correct if the sort function only works on text not numbers. Why would the sort change the format from a number to text? If it is making that change then why only two numbers and not all of them? I agree that 2001 and 2005 sort correctly IF they are numbers and not text but the entire column was entered as numbers. Why should these two numbers not be converted to text or why should any numbers be converted from text to numbers? I still believe there is a bug there but don't know how to fix it unless I never sort on a numbered field. (I can't imagine what the conversion to text from a number would do to calculations.
It's indicated "earliest affected". => I reverted back the version. For the rest, I don't know if it's a bug or not.
Okay, nailed it. The reason the sort did not work correctly was due to numbers not being formatted as expected. Under 'Tools' AutoInput was turned on by default. The effect of this was to make my 'year' entries in the column into whatever was above it, effectively turning some entries into Text and leaving others as 'numbers' AutoInput by default is like working with a Spellchecker, sometimes it's a good thing and sometimes it's not. Apparently when entering both text in some columns and numbers in others it is dangerous to use the number column as one of the SortKeys when there may be a possibility of some numbers being sorted as text and others sorted as numbers. I tried testing the column of "mixed" number/text by just using another column and adding a '1' to the "mixed" columns. The output was what I would expect if I was just adding a number to each of the years. 2005 + 1 = 2006, etc. So, if Sort didn't like the format of the numbers, why would adding a number to a text number work? I am still at somewhat of a loss. Version: 6.3.1.2 Build ID: b79626edf0065ac373bd1df5c28bd630b4424273 CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded
DON'T PUT A NEWER VERSION! It's indicated "earliest affected". => I reverted back the version. For the rest, I don't know if it's a bug or not.
Created attachment 154341 [details] Sample for test how numbers as text behave. There are some set ups in Menu/Tools/Options/LibreOffice calc/Formula, about how to numbers as text behave, with the attached file I think easy to see. Calc only takes numbers as text as number with direct references, no with ranges references. What's clear this is not a sort bug, sorting a range with numbers and texts, numbers goes before. Difficult to believe AutoInput changes cell's format, specially converting a number in text. Maybe the reason could be the expand format options in Menu/Tools/Options/LibreOffice calc/General - Expand .... If you find a way where a number cell format it's converted to text cell format automatically, please fill a new bug report with the necessary steps to reproduce the problem.