ORDER BY clause ignored in subquery

You are viewing an old version of this question. View the current version here.

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:

33672
33671
33670
33669
33668
33667
33666
33665
33664
1053656

results in MariaDB:

1053656
33664
33665
33666
33667
33668
33669
33670
33671
33672

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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.