Your example of multiple sorts is problematic.

> SELECT * FROM (SELECT users.* FROM users ORDER BY users.id asc) ORDER BY users.id desc

A sort should never (for some value of never) appear in a subquery because it's meaningless; it can't affect the result. In tsql it's explicitly checked for and reported.

Not having a go, just fyi.

Edit: now I'm more confused by that. The bracketed subquery doesn't have a name but it's apparnetly called 'users' because the last clause is 'ORDER BY users.id desc' - but that's illegal. Again tsql correctly rejects that (once I comment out the illegal inner order by).

Edit 2: sorry about this but FYI, I'd expect the inner sort order to be disregarded anyway, and assuming this is mysql it explicitly is.

"If ORDER BY occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future MySQL version"

https://dev.mysql.com/doc/refman/5.7/en/select.html

(Actually, what the heck is it saying? An outer order by in the presence of an inner order by is undefined overall??)