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!