Ordering by aggregate expression sometimes works incorrectly

http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=cacfe3a36b241a9379faf72286bb0531 - basically, it has everything that's needed.

Long story:

When profiling a complex query on a copy of production database, I found out that the ORDER BY clause doesn't always work properly with an expression that contains aggregate functions.

I used MariaDB version 10.1.28, on Nixos Linux. The sample query below exhibits the bug on MariaDB 10.1.28 and on dbfiddle.uk (which runs MariaDB 10.2.12). MySQL 5.6.38 (which I run on Gentoo Linux) doesn't have this bug (at least, the sample query doesn't exhibit it).

Here is some sample data that demonstrate the issue:

CREATE TABLE `t0` (

  `a` int(11) NOT NULL,

  `x` int(11) NOT NULL,

  `y` int(11) NOT NULL

) ENGINE=InnoDB;

INSERT INTO `t0` VALUES (1,40,9),(2,3,12),(3,14,14),(4,1,2),(10,23,1),(20,15,10),(30,8,2),(40,100,12),(1,40,9),(2,3,12),(3,14,14),(4,1,2),(10,23,1),(20,15,10),(30,8,2),(40,100,12);

CREATE TABLE `t1` (

  `a` int(11) NOT NULL,

  `b` int(11) NOT NULL

) ENGINE=InnoDB;

INSERT INTO `t1` VALUES (10,5),(10,8),(20,5),(10,5),(10,7),(10,8),(20,5),(20,8),(30,9),(30,5),(40,9),(40,11),(40,8),(1,5),(1,7),(1,8),(2,5),(2,8),(3,9),(3,5),(4,9),(4,11),(4,8);

The problematic query is as follows:

select t0.a, sum(x + y * b)/count(distinct b) + x as mysum

from t0 inner join t1 on t0.a=t1.a

group by a order by (sum(x + y * b)/count(distinct b) + x) desc;

It produces the following result:

a	mysum

40	524.0000

20	240.0000

3	238.0000

1	240.0000

2	165.0000

10	121.6667

30	52.0000

4	40.3333

So, despite the query being sorted by the expression that's used to calculate the "mysum" column, the rows aren't listed in correct order. If I replace the ORDER BY expression with "mysum", the order becomes correct.

The ORDER BY expression in the original query (for production data) looks like this: "r.value1 + sum(ifnull(e.value2 * (e.value3 + e.value), 0.0))". That query also starts working properly once I replace the ORDER BY expression with an alias.

Also, both queries seem to work fine if I remove the non-aggregate part ("x" in sample query and "r.value1" in production query).