Order By Ignores Fourth Character

Question

I have a table which contains the following values, with an index on the path column. When I select values ordered by path it uses the index, but ignores any digits past the third. See below.

What I am getting:

category_id name                    path
1091        Bagels and Donuts       114.1091 (1 is ignored)
1092        Barbecue                114.1092 (2 is ignored)
115         Afghan Restaurant       114.113
1140        International           114.1140 (0 is ignored)
116         African Restaurant      114.116 
117         Ethiopian Restaurant    114.116.117
118         American Restaurant     114.118
119         New American Restaurant 114.118.119
120         Asian Restaurant        114.120

What I should be getting:

category_id name                    path
115         Afghan Restaurant       114.113
116         African Restaurant      114.116 
117         Ethiopian Restaurant    114.116.117
118         American Restaurant     114.118
119         New American Restaurant 114.118.119
120         Asian Restaurant        114.120
1091        Bagels and Donuts       114.1091
1092        Barbecue                114.1092
1140        International           114.1140

Any thoughts?

Are you sure the index isn't working as expected? Your current order looks right to me. — Tim Biegeleisen, Aug 05 '16 at 03:30
@TimBiegeleisen The results at the top are what I am getting, and the results at the bottom are what they should be — Josh Bernfeld, Aug 05 '16 at 03:32
the sort is correct as string. your expected is as digits, which is different. however it is still achievable. Try your brain again :D — SIDU, Aug 05 '16 at 03:33
I don't they "should" be like the bottom. I think they should be like the top. — Tim Biegeleisen, Aug 05 '16 at 03:35
I just went ahead and changed one of the values to hopefully clarify, let me know if the results are still correct — Josh Bernfeld, Aug 05 '16 at 03:37
order by datatype string produces the output 'above', order by datatype numeric produces the output on the 'bottom'. If you want to achieve the output for the bottom part, convert path to numeric value and try again. Or this could help you: http://stackoverflow.com/a/2542021/1809168 — Eduard, Aug 05 '16 at 03:50
Please provide `SHOW CREATE TABLE` and the SQL used for ordering. — Rick James, Aug 05 '16 at 15:47

score 0 · Answer 1 · answered Aug 05 '16 at 03:49

Imagin you only has 3 parts in path:

SELECT * FROM tab
ORDER BY
  substring_index(path, '.',  1) + 0, ## sort by 1st part
  substring_index(substring_index(path, '.', 2), '.' , -1) + 0, ## sort by 2nd part
  substring_index(path, '.', -1) + 0 ## sort by 3rd part

And Imagine this is not IP, otherwise a more simple way to do it.

Josh Bernfeld · Answer 2 · 2020-05-30T02:20:40.977

This was my final solution. Place each level into its own INT column, then create one index for all of the columns. Thank you everyone for your great input.

DELIMITER $$
DROP FUNCTION IF EXISTS SPLIT_STR; $$
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');$$

DROP FUNCTION IF EXISTS GetLevel; $$
CREATE FUNCTION GetLevel (path VARCHAR, LevelN INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE str VARCHAR(255);

    SET str = SPLIT_STR(path,".",levelN);
    IF str='' THEN
        RETURN NULL;
    END IF;

    RETURN str;

END $$
DELIMITER ;

UPDATE categories SET level1 = GetLevel(category_id, 1), level2 = GetLevel(category_id, 2), level3 = GetLevel(category_id, 3), level4 = GetLevel(category_id, 4), level5 = GetLevel(category_id, 5);

Order By Ignores Fourth Character

2 Answers2