http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=cacfe3a36b241a9379faf72286bb0531 - basically, it has everything that's needed.
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:
The problematic query is as follows:
It produces the following result:
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).