This optimization is now on trunk. The following description is intended to
serve as archival documentation.
If a subquery in the FROM clause has an ORDER BY, that ORDER BY is omitted
if all of the following conditions are also true:
1. There is no LIMIT clause in the subquery
2. The subquery was not one of the virtual subqueries added internally
by SQLite for window-function processing
3. The subquery is not part of the FROM clause in an UPDATE-FROM statement
4. The outer query does not use any aggregate functions other than
the built-in count(), min(), and/or max() functions.
5. Either the outer query has its own ORDER BY clause or else the
subquery is one term of a join.
6. The omit-ORDER-BY optimization is not disabled by the
SQLITE_TESTCTRL_OPTIMIZATION test-control.
The omit-ORDER-BY optimization can be disabled (item 6 above) using the
following command in the CLI:
.testctrl optimizations 0x40000