Comments - Why is ORDER BY in a FROM Subquery Ignored?

I am not sure if this is expected behaviour or not, but using GROUP BY in the subquery seems to force the overall query to accept the ORDER BY clause in the subquery.

Is this correct behaviour?

An effect of some of the GROUP BY algorithms (sorting and collecting rows) will create a table where the rows happens to be in order. It's however not guaranteed according to the SQL standard

Personally, I do see this as a bug, as the subquery is creating a result set, not a table, to a set of specified requirements; one requirement being an order, therefore it should be treated as ordered. This totally screws up previously working code that attempts to group based on the results of the ordered subquery. You cannot sort before grouping on the top level SELECT!

The limit hack seems to work, but must have a performance hit, so thanks @rspadim

The LIMIT clause will not have any performance hit as the extra 'limit' row counting is neglectable compared to doing the full ORDER BY.

@rspadim That's true, and I understand that this can be a way to avoid refactoring bad code, which may take time. But creating an unnecessary temporary table doesn't seem a great solution to me.

an 'easy' workaround to this, is:

SELECT field1, field2 FROM (
SELECT field1, field2 FROM table1 
ORDER BY field2
LIMIT 18446744073709551615
) alias

this cause the optimizer to create a temporary table, and use filesort to order the query

the limit number is a 64bit unsigned -1 (2^64-1), this is a big number and can work with 99.999% of queries i know

@rspadim : Thank you for the workaround but on my biggest table, the time of execution increased since I tried. Do you have another idea ?

Higher execution time is expected if you force an ORDER BY on your biggest table.