2

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;
  • You might probably switch to the old behavior for a while by setting optimizer_switch='derived_merge=off' (or optimizer_switch='derived_merge=off,derived_with_keys=off', depending on the structure of your table) -- mariadb.com/kb/en/mariadb/… – Rick James Oct 21 '15 at 20:07
3

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

  • What about the query in the edited question of mine? Will the x.city_id ignored or not? And so, the answer in this link stackoverflow.com/questions/532878/… is not accurate? – Jack Oct 14 '15 at 6:11
  • It MAY be - thats the big problem, it sometimes works and sometimes does not because it depends on what path the optimizer choses, the answer is 6 years old and optimizers changed a bit in that time - for instance MariaDB implemented automatic merging of derived tables where possible. – jkavalik Oct 14 '15 at 6:16
  • Edited my answer. Basically what @jkavalik said. – ypercubeᵀᴹ Oct 14 '15 at 6:16
  • @jkavalik Then, how do I solve a problem where I need the inner query to be ordered first before executing the outer query. Do I need to create a temp table for each inner query in FROM? – Jack Oct 14 '15 at 6:20
  • Yes, creating a temporary table and saving the results of the subquery is probably the best and (if not the only) safe option. You only need to do this when an ORDER BY is required in the derived table, not for all subqueries. – ypercubeᵀᴹ Oct 14 '15 at 6:21
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.