Bug 95192 - SORTING Natural sorting not working with non-letter,non-number content
Summary: SORTING Natural sorting not working with non-letter,non-number content
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.1.0 target:6.0.2
Keywords:
Depends on:
Blocks:
 
Reported: 2015-10-20 08:50 UTC by Paolo Benvenuto
Modified: 2018-05-06 05:33 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
calc file showing the bug (16.42 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-20 08:50 UTC, Paolo Benvenuto
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Paolo Benvenuto 2015-10-20 08:50:24 UTC
Created attachment 119775 [details]
calc file showing the bug

Natural sorting works only with numeric fields, user expects it working with text fields too.

See attacched document for an example
Comment 1 m.a.riosv 2015-10-20 23:50:08 UTC
Hi @Paolo, thanks for reporting.

I think it is limited to numbers after the text without spaces and no letters between, it works with a list like:


a11
a2
a20
a3
a30
after a natural sort
a1
a2
a3
a11
a20
a30

but not with
aaaa 1
aaaa 11
aaaa 2
aaaa 20
aaaa 3
aaaa 30
Comment 2 Paolo Benvenuto 2015-10-21 07:06:30 UTC
a more general approach would be useful.

The sample file I attacched is a real case, showing street names with street numbers.
Comment 3 Joel Madero 2015-10-21 23:01:57 UTC
This is for UX to decide.
Comment 4 m.a.riosv 2015-10-23 21:37:57 UTC
Perhaps not exactly the same but very similar to this one:

https://bugs.documentfoundation.org/show_bug.cgi?id=50786
Comment 5 Heiko Tietze 2015-10-26 09:21:44 UTC
As far as I understand the issue we have either a bug in the natural sort function or a misconception of whitespaces. But not an UX issue.

Current sort order is:
Via A. Centurione 11/7
Via A. Centurione 13/3
Via A. Centurione 2/5
Via A. Centurione 3/5
Via A. Centurione 4/1

Intended behavior with Natural Sort [1] enabled:
Via A. Centurione 2/5
Via A. Centurione 3/5
Via A. Centurione 4/1
Via A. Centurione 11/7
Via A. Centurione 13/3

The question is why this list isn't sorted as expected. Could be due to the slash, or because of the combined number. But the list isn't sorted even after removing all of the strange stuff (just Via... 13, Via... 2 etc.).

And how whitespaces are treated should be discussed in #50786.

[1] https://help.libreoffice.org/index.php?title=Calc/Options&Language=en-US&System=WIN&Version=5.1#Enable_natural_sort
Comment 6 Paolo Benvenuto 2015-10-26 11:07:05 UTC
I think that enabling natural sort should sort alphabetically until character is not a number, and naturally numerically if numbers are present, and again alphabetically after the numbers
Comment 7 Heiko Tietze 2015-10-26 12:24:40 UTC
So you want something like:
Via A. Centurione 2/1
Via A. Centurione 2/2
Via A. Centurione 2/3
Via A. Centurione 4/1
Via A. Centurione 11/7
Via A. Centurione 13/3

Not sure if that is what NS is defined in the norms. Devs?
Comment 8 bzzz 2016-08-21 10:01:37 UTC
Let's bump this issue (and #72749 as a related one) once again, still present in Version 5.1.4.2 (Build-ID f99d75f39f1c57ebdd7ffc5f42867c12031db97a / CPU-Threads: 4 / BS-Version: Windows 6.1; UI-Render: Standard / Gebietsschema: de-DE (de_DE))

I've tried sorting a large list by the following column:
 
Gitlab Issue #1
Gitlab Issue #10
Gitlab Issue #11
Gitlab Issue #12
Gitlab Issue #13
Gitlab Issue #14
Gitlab Issue #15
Gitlab Issue #16
Gitlab Issue #17
Gitlab Issue #18
Gitlab Issue #19
Gitlab Issue #2
Gitlab Issue #20
Gitlab Issue #21
Gitlab Issue #22
Gitlab Issue #23
Gitlab Issue #24
Gitlab Issue #25
Gitlab Issue #26
Gitlab Issue #27
Gitlab Issue #28
Gitlab Issue #29
Gitlab Issue #3
...and so on 

This is from a heavily processed CSV file from a Gitlab JSON export so that I can import it into a fresh Kanboard installation. As Kanboard doesn't resemble the Gitlab IDs but rather assigns numbers auto-incrementally, I'm fond of sorting this large table after doing my edits, so that at least they are in order when imported. This fails due to LO not sorting properly in both normal and natural sort modes. 
I know how to get round that issue, but it's just not convenient for the average Joe and also takes some time when doing this task over and over again for different projects.


FYI: Excel 2010 also fails with the very same result ;)
Comment 9 Heiko Tietze 2016-08-21 12:10:05 UTC
(In reply to bzzz from comment #8)
> Gitlab Issue #1

In this particular case you can work with "=RIGHT(A1;LEN(A1)-14)" and sort by the number.

>Gebietsschema: de-DE (de_DE)

IIRC, "RECHTS" and "LÄNGE" are the localized functions.
Comment 10 Robinson Tryon (qubit) 2016-08-25 05:49:27 UTC Comment hidden (obsolete)
Comment 11 Heiko Tietze 2018-01-12 10:27:25 UTC
Removing UX as there is nothing to add here. The intention of the OP is clear.
Eike, any thoughts?
Comment 12 Eike Rathke 2018-02-13 09:32:13 UTC
Well, fix it, maybe? ;-)
Comment 13 Commit Notification 2018-02-13 10:03:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2d62a1016438ea0bc079e6de3a5bcdcb34680f43

Resolves: tdf#95192 correctly split non-numeral/numeral parts for natural sort

It will be available in 6.1.0.

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.
Comment 14 Eike Rathke 2018-02-13 10:21:01 UTC
Pending review https://gerrit.libreoffice.org/49629 for 6-0
Comment 15 Commit Notification 2018-02-13 20:44:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=114b9562e58a91b34c6e931c281d8a3900744750&h=libreoffice-6-0

Resolves: tdf#95192 correctly split non-numeral/numeral parts for natural sort

It will be available in 6.0.2.

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.
Comment 16 Commit Notification 2018-05-06 05:33:04 UTC
Zdeněk Crhonek committed a patch related to this issue.
It has been pushed to "master":

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

uitest for bug tdf#95192

It will be available in 6.1.0.

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.