MariaDB ignores ORDER BY in SELECT

Issue

I installed new version of MariaDB and got problem with sorting php-script:

$this->db->query("
    SET @v:=0;
    UPDATE `users` AS `c1`
    LEFT JOIN (SELECT `id`, (@v:[email protected]+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'
");

In particular script sets order of entries in users table sorting them by ord:

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

I’m not familiar with SQL and after long hours of searching and tests I, as it seems to me, found out the SQL-query that doesn’t work right:

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

In previous version of MariaDB (5.5.5-10.1.25) the query gives entries sorted by initial order (ord):

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

In new version (10.6.3) result is:

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

I tried to set ORDER BY param ord to name and id, but result was the same. It seems like col ord2 filled with even numbers is always sorted by id in ascending order.

How should I rewrite the query or the script to fix the problem?

Solution

In latest MariaDB versions you can use window function row_number in next way:

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

MariaDB query test here

Answered By – Slava Rozhnev

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0