MySQL Ranking Query – Is ORDER BY in FROM Subquery Preserved?

mariadbMySQL

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;

Best Answer

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".

Related Question