Different ORDER BY behavior between MySql and MariaDB

During the porting of an application from MySql to MariaDB, I found another curious fact: I get a different ORDER BY behaviour between MySql And MariaDB.

Look at this query:

SELECT DISTINCT Z.ID 
FROM (
        SELECT Field1, Field2, Field3....
        FROM Table1 ORD 
        INNER JOIN Table2 OC ON ORD.OrderCopID = OC.ID 
        LEFT JOIN Table3 NEG ON OC.NegID = NEG.ID 
        ....
        WHERE 1=1 
        ORDER BY OC.ID DESC, ORD.Progr
      ) AS Z DESC LIMIT 0, 25
) AS T

The expected result, and the one I get using MySql, is a table with one column containing a list of numeric IDs in descending order, due to the ORDER BY clause OC.ID DESC

The expected result should be something like this:

3010, 3009, 3008, 3007, and so on…

Well, the same query executed on the same database with the same data but running on MariaDB returns a different recordset:

3006, 2008, 3007, 500, and so on…

I was able to solve the problem simply adding and ORDER BY clause to the external subquery, as follow:

SELECT DISTINCT Z.ID 
FROM (
      SELECT Field1, Field2, Field3....
      FROM Table1 ORD 
      INNER JOIN Table2 OC ON ORD.OrdineCoppiaID = OC.ID 
      LEFT JOIN Table3 NEG ON OC.NegozioID = NEG.ID 
      ....
      WHERE 1=1 
      ORDER BY OC.ID DESC, ORD.Progressivo) AS Z ORDER BY Z.ID DESC LIMIT 0, 25
) AS T

Why this different beahviour? Who is right between MySql and MariaDB?

I don’t know, but the lesson is clear: if you expect a recordset in a certain order, always explicitly set this in your query, and don’t even trust an inner subquery order by!

Related Posts