Behaviour of ORDER BY in FROM: MariaDB vs MySQL

TL; DR In MariaDB, query with ORDER BY in a FROM subquery produces an unordered result. In effect, ORDER BY is ignored in FROM subqueries. MySQL does not ignore ORDER BY in FROM subqueries.

Longer Version

Older versions of MariaDB (< 10.2.0) did not have window functions such as rank(), dense_rank(), row_number() among others. To understand where you would use such a function, dense_rank() for instance, consider the following example:

Given an Employee table and a Department table as shown below, find employees who earn the top three salaries in each of the department.

Employee Table

Department Table

I list three approaches to solving this problem, starting with the easiest one which makes use of dense_rank().

The Dense-Rank Version

Using dense_rank(), this can be accomplished using:

SELECT * FROM
  (
   SELECT d.Name as Department, e.Name as Employee, Salary,
   DENSE_RANK()
       OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) Rank
   FROM Employee e JOIN Department d ON e.DepartmentId = d.id
 ) t WHERE rank <= 3
     ORDER BY Department, Rank;

Fiddle Link

(Homework Assignment: Why use dense_rank() instead of rank()? How does it affect your result?)

No Window Functions?!

Things get slightly ugly when you do not have access to window functions. Of course, a workaround could be to use a join and sub-query as shown below:

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1 JOIN
        Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT COUNT(DISTINCT e2.Salary)
        FROM Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        );

But this is sub-optimal, and we can do better.

Session Variables

Another way would be to use Session Variables in your queries. This is where the observed behavior of MariaDB and MySQL part ways. I wrote the following query on a fiddle against MySQL 5.6 and expected it to work on MariaDB. Alas!

set @did := NULL;
set @rn := 1;
set @sal := NULL;

select `Department`, `Employee`, `Salary` from (
  select t.`Department`, t.`Employee`, t.Salary,
	@rn:= if(@did = DepartmentId, if(@sal = Salary, @rn, @rn + 1), 1 ) as rank,
	@did:= DepartmentId,
	@sal:= Salary
  from
	(
	  select d.name as `Department`, e.Name as `Employee`, DepartmentId, Salary
	  from Employee e
		  inner join Department d on e.DepartmentId = d.Id
	  order by DepartmentId, Salary desc
	) t
	) f where rank <= 3;

Fiddle Link

As you can see, I make use of an ORDER BY clause inside a FROM subquery. MariaDB blatantly ignores it, while MySQL is more gracious. A few wasted hours and some googling thereafter, I realized this difference and more so, find out that this is not a bug on MariaDB; more of a deliberate feature. According to MariaDB FAQ,

A “table” (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a >subquery in the FROM clause) do not come in any specific order. That’s why the optimizer can ignore the ORDER BY clause that you have >specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY >… LIMIT … changes the result, the set of rows, not only their order).

You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the >top-level SELECT.

It is a bit of a snag to work with this “feature”, and I am still trying to solve the original problem in MariaDB versions <10.2.0 using session variables. If you have a solution in mind, or have something more to add to this conversation, comment below.