Is a "Ranking" query's ORDER BY in FROM subquery is preserved?

Question

I've read in the following links that the ORDER BY in FROM subquery can be ignored.

Quoted from MariaDB knowledge base

A query such as

SELECT field1, field2 FROM 
(SELECT field1, field2 FROM table1 ORDER BY field2 ) alias

returns a result set that is not necessarily ordered by field2.

So, given the following query:

SELECT CASE 
         WHEN   @prev = x.city_id
            THEN @a
         WHEN @prev := x.city_id
            THEN @a := @a + 1
         END
         `rank`     
FROM 
(SELECT @prev := NULL, @a := 0) y,
(
    SELECT *
    FROM sakila.address a
    ORDER BY a.city_id
) x

Will the rows returned from the FROM subquery be in the order specified in the ORDER BY clause?

Edit:

How about the query below, will the x.city_id be ignored or not?

SELECT ....
FROM (
    SELECT CASE 
           WHEN   @prev = x.city_id
              THEN @a
           WHEN @prev := x.city_id
              THEN @a := @a + 1
           END
             `rank`     
    FROM 
        (SELECT @prev := NULL, @a := 0) y,
        sakila.address x

    ORDER BY x.city_id
)
ORDER BY another_order_by_clause

---

How I solved it.

After looking through the answer and feedbacks from @ypercube and @jkavalik, I decided to explicitly create a temporary table for each inner query that I had.

Reason being as pointed by them, adding LIMIT by a huge number or other hacks might not work in every version of MariaDb or MySQL.

Performance was still good for the query that I did.

e.g:

The second query above is changed to

   CREATE TEMPORARY TABLE temp1 SELECT CASE 
                   WHEN   @prev = x.city_id
                      THEN @a
                   WHEN @prev := x.city_id
                      THEN @a := @a + 1
                   END
                     `rank`     
            FROM 
                (SELECT @prev := NULL, @a := 0) y,
                sakila.address x                
            ORDER BY x.city_id;

    SELECT ....
    FROM temp1
    ORDER BY another_order_by_clause;

Answer 1

As the article explains: No, the internal ORDER BY will be recognized by MariaDB's optimizer as redundant and will be "optimized away". If you want a result set returned in a specific order, use ORDER BY - in the most external level of the query.

The solution is to use ORDER BY in the external query. The x subquery (derived table) can be removed and replaced by a simple sakila.address x (that's what the optimizer will recognize and do anyway):

SELECT CASE 
         WHEN   @prev = x.city_id
            THEN @a
         WHEN @prev := x.city_id
            THEN @a := @a + 1
       END
         `rank`     
FROM 
    (SELECT @prev := NULL, @a := 0) y,
    sakila.address x                    -- remove the derived table

ORDER BY x.city_id ;                    -- add ORDER BY in the external query

---

About the second query, yes, I think the ORDER BY x.city_id will be ignored, too. The answer in the linked question is 6 years old and is accurate but it's for MySQL (and the versions available then). MariaDB recent improvements on the optimizer make this kind of subqueries with variables, sometimes to fail because the ORDER BY is "optimized away".

Answer 2

MariaDB (in contrary to MySQL) lacks the feature which allows returning ordered results from subqueries. ORDER BY is only ordering the results when:

• it is in the outermost (last) query
• if GROUP BY is used (in the subquery)
• if a LIMIT is given (in the subquery)

The last two options are interesting here, as these are probably never optimized out, because the optimizer may have a hard time to prove that the returned results will be the same.

Therefore, you can force MariaDB to order the results from the subquery, without affecting the query results with a trick:

If you have a primary or unique index available, you could add GROUP BY on that column in the subquery, without affecting the returned results, but still have MariaDB order the results. Otherwise you could add LIMIT 18446744073709551615.