MySQL / MariaDB, проблема сортировкой

Question

После обновления версии MariaDB перестал корректно работать скрипт сортировки элементов:

$this->db->query("
    SET @v:=0;
    UPDATE `users` AS `c1`
    LEFT JOIN (SELECT `id`, (@v:=@v+2) AS `ord2`, `name`, `parent` 
    FROM `users` 
    WHERE `parent`='0' ORDER BY `ord` ASC) AS `c2`
    ON `c1`.`id` = `c2`.`id`
    SET `c1`.`ord` = `c2`.`ord2`
    WHERE `c1`.`parent` = '0'
");

Скрипт применяется в частности к таблице users, изначально отсортированной по полю ord:

+----+-----+--------------------+--------+
| id | ord | name               | parent |
+----+-----+--------------------+--------+
|  2 |   2 | admin              |      0 |
| 10 |   5 | manager            |      0 |
| 12 |   7 | user               |      0 |
| 11 |   9 | dev                |      0 |
+----+-----+--------------------+--------+

Я плохо знаю возможности SQL, и после долгих поисков и экспериментов я, как мне кажется, обнаружил проблему. В пятой версии MariaDB запрос

SELECT `id`, (@v:=@v+2) AS `ord2`, `name`, `parent` 
FROM `users` 
WHERE `parent`='0' ORDER BY `ord` ASC;

дает результат, сохраняющий исходный порядок записей:

+----+-----+--------------------+--------+
| id | ord2| name               | parent |
+----+-----+--------------------+--------+
|  2 |   2 | admin              |      0 |
| 10 |   4 | manager            |      0 |
| 12 |   6 | user               |      0 |
| 11 |   8 | dev                |      0 |
+----+-----+--------------------+--------+

В версии 10.6.3, которую я установил, порядок нарушается:

+----+-----+--------------------+--------+
| id | ord2| name               | parent |
+----+-----+--------------------+--------+
|  2 |   2 | admin              |      0 |
| 10 |   4 | manager            |      0 |
| 12 |   8 | user               |      0 |
| 11 |   6 | dev                |      0 |
+----+-----+--------------------+--------+

Я пробовал менять сортировку в запросе, ORDER BY ord на ORDER BY name и ORDER BY id, но результат – тот же. Как будто, несмотря на указание, заполняемый четными числами столбец ord2 всегда выстраивается по возрастанию id.

Как мне изменить или переписать запрос, чтобы восстановить работу скрипта?

Answer 1

В последних версиях MariaDB вы можете использовать оконную функцию row_number следующим образом:

select 
    id,
    (row_number() over (order by ord))*2 ard2,
    name,
    parent
from tbl;

MariaDB query test here

БЛОГ НА HUSL

MySQL / MariaDB, проблема сортировкой

Ответы (1 шт):