While I understand the intention behind it and thus don't want to file a bug, I think there is a distinct possibility that some users will find the natural sort of float numbers rather unnatural.
Especially when the returned values are FLOAT, and the client aligns them as such, it is quite difficult to see the result as natural.

MariaDB [test]> create or replace table t (f float);

Query OK, 0 rows affected (0.055 sec)

MariaDB [test]> insert into t (f) values (0.5), (0.33), (0.044);

Query OK, 3 rows affected (0.013 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from t order by natural_sort_key(f);

+-------+

| f     |

+-------+

|   0.5 |

|  0.33 |

| 0.044 |

+-------+

3 rows in set (0.001 sec)

It is slightly better when the values are actually strings, at least they are formatted as such, and with some effort one can see the logic:

MariaDB [test]> create or replace table t (f varchar(16));

Query OK, 0 rows affected (0.053 sec)

MariaDB [test]> insert into t (f) values (0.5), (0.33), (0.044);

Query OK, 3 rows affected (0.012 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from t order by natural_sort_key(f);

+-------+

| f     |

+-------+

| 0.5   |

| 0.33  |

| 0.044 |

+-------+

3 rows in set (0.001 sec)

Same for negative numbers.

I wonder what existing best practices say about it, whether the natural sort, when applied to numbers, should consider the numeric order being natural.

It is certainly possible to design a transformation scheme for fractions, and negatives, it is just not done currently. Some of that, sorting leading zeros in predictable way, and fractions( with either dot or comma as separators) were in some earlier versions of the patch, and were taken out, for the sake of simplicity.

We currently sort like ICU - positive numbers only, no fractions, ignore leading zeros, no other customizations. We sort slightly better than ICU in one aspect, because they have some in-built limitations about how long a numeric string can be, 256 digits or something like that, I don't recall exactly. We sort slightly worse than ICU in i18n department, because we only consider 0-9 to be digits, and they handle more decimal digits from different scripts.

What you can be interested in , Python's natsort has some customizations to sort, and allegedly handles fractions, and negatives , when told to handle it, via parameter https://natsort.readthedocs.io/en/master/api.html#the-ns-enum

But there is no best practice, as far as I understand, and no common definition of natural sort, except that "we intermix strings and numbers, and expect numbers to be sorted as numbers, not ASCII".

There are a couple of aspects when people want to sort strings as numbers

• i18n decimal numbers : 0-9 vs decimal numbers in non-latin scripts
• fractions separators- dot vs comma vs whatever is used in some arabic scripts. Sometimes both dot and comma are used, e.g in Switzerland.
• fractions - whether to support scientific notation with exponent ?
• thousands separators - 20'000'000 - is this the same as 20000000? Is 20 000 000 the same as 20000000,00? Those are tricky, as they are can be locale dependent, and alternating, with comma and dot, but do not have to, and people would just separate with space.

• negatives
• decimal numbers - should they sort as-is ( bigger than punctuation, less that letters), or less than anything else, including punctuations, or larger than anything else? This is related to script ordering in UCA algorithm.

And of course, natural sort sorts strings. Anything can be converted to string, but it does not mean people should be trying to convert numbers to string, and to natural_sort_key, to sort numbers.

As far as testing is concerned, it can be pushed into 10.7 (as of preview-10.7-MDEV-4742-natural-sort cfec49f1dd7241582f03cbcd395e65a9c2ecf4ce).

Documentation in the KB needs to be updated/enhanced, but it shouldn't be an obstacle to pushing into main.

As explained earlier in the comments, some decisions remain a subject to further consideration based on users' feedback.

I wrote a nat-sort function for MariaDB/MySQL a few years ago, documented here: https://stackoverflow.com/a/58154535/999120

It produces a key strings based on an input string, such that sorting by the key string results in a natural sort. They key strings can be stored for re-use, making the process efficient.

I think it might have more "features" than the one you describe above: It handles negatives (though this could be improved), leading zeros, leading +, version numbers or IP addresses, decimals, and use of commas as thousand separators, and also preserves total sort ordering (i.e. no two different strings nat-sort as equivalent).

Feel free to borrow it, if you like.