title: MariaDB ignores ORDER BY in SELECT
body: <p>I installed new version of MariaDB and got problem with sorting php-script:</p>
  <pre><code>$this-&gt;db-&gt;query(&quot;
      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'
  &quot;);
  </code></pre>
  <p>In particular script sets order of entries in <code>users</code> table sorting them by <code>ord</code>:</p>
  <pre><code>+----+-----+--------------------+--------+
  | id | ord | name               | parent |
  +----+-----+--------------------+--------+
  |  2 |   2 | admin              |      0 |
  | 10 |   5 | manager            |      0 |
  | 12 |   7 | user               |      0 |
  | 11 |   9 | dev                |      0 |
  +----+-----+--------------------+--------+
  </code></pre>
  <p>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:</p>
  <pre><code>SELECT `id`, (@v:=@v+2) AS `ord2`, `name`, `parent` 
  FROM `users` 
  WHERE `parent`='0' ORDER BY `ord` ASC;
  </code></pre>
  <p>In previous version of MariaDB (5.5.5-10.1.25) the query gives entries sorted by initial order (<code>ord</code>):</p>
  <pre><code>+----+-----+--------------------+--------+
  | id | ord2| name               | parent |
  +----+-----+--------------------+--------+
  |  2 |   2 | admin              |      0 |
  | 10 |   4 | manager            |      0 |
  | 12 |   6 | user               |      0 |
  | 11 |   8 | dev                |      0 |
  +----+-----+--------------------+--------+
  </code></pre>
  <p>In new version (10.6.3) result is:</p>
  <pre><code>+----+-----+--------------------+--------+
  | id | ord2| name               | parent |
  +----+-----+--------------------+--------+
  |  2 |   2 | admin              |      0 |
  | 10 |   4 | manager            |      0 |
  | 12 |   8 | user               |      0 |
  | 11 |   6 | dev                |      0 |
  +----+-----+--------------------+--------+
  </code></pre>
  <p>I tried to set ORDER BY param <code>ord</code> to <code>name</code> and <code>id</code>, but result was the same. It seems like col <code>ord2</code> filled with even numbers is always sorted by <code>id</code> in ascending order.</p>
  <p>How should I rewrite the query or the script to fix the problem?</p>