ORDER BY clause ignored in subquery
We are migrating many MySQL instances to MariaDB 10.2.8. We have a very large library of legacy code that comes with the migration. An issue we are trying to resolve is that a subquery containing an ORDER BY does not sort the output. Here is an example query and the results, and yea I am aware that a subquery is not required in this case, but this query is for example purposes only:
Query:
SELECT * FROM ( SELECT ta.status,tb.delete_request_id,tb.id AS aux_id FROM Table_A AS ta INNER JOIN Table_B AS tb ON tb.id = ta.id WHERE tb.delete_request_id = 36 ORDER BY tb.id DESC ) AS t;
results on MySQL:
3 | 36 | 72 |
3 | 36 | 71 |
3 | 36 | 70 |
3 | 36 | 69 |
3 | 36 | 68 |
3 | 36 | 67 |
3 | 36 | 66 |
3 | 36 | 65 |
3 | 36 | 64 |
105 | 36 | 56 |
results in MariaDB:
105 | 36 | 56 |
3 | 36 | 64 |
3 | 36 | 65 |
3 | 36 | 66 |
3 | 36 | 67 |
3 | 36 | 68 |
3 | 36 | 69 |
3 | 36 | 70 |
3 | 36 | 71 |
3 | 36 | 72 |
Is this a bug in MariaDB? Is there a SQL_MODE or other environment variable that will fix this without re-coding (not that I have found). The only solution i have found is to write the output to a temporary table and select from there. This would require a large coding effort on our part. Please help!
Answer
Further research on my part indicates that this is not a bug in MariaDB, but rather a 'feature'. See (https://mariadb.com/kb/en/library/why-is-order-by-in-a-from-subquery-ignored/).
That said, it would be nice if there were a run time variable or SQL_MODE that allowed for the previous behavior. This will cost us a fair amount of code change and may scuttle the migration all together.