SQLite Forum

Should SQLite be able to optimize this query?
Login
I now think that neither of these cases is a problem.

> ~~~~
SELECT sideeffectfunc(x) FROM (SELECT x FROM table ORDER BY x);
~~~~

This case would not omit the ORDER BY clause because of the
restriction described by [post 062d576715d277c8](/forumpost/062d576715d277c8)
above.

> ~~~~
SELECT y FROM (SELECT sideeffectfunc(x) AS y FROM table1 ORDER BY x), table2;
~~~~

In this case, the order of invocations of sideeffectfunc() is arbitrary anyhow.
SQLite has never made any guarantees about the order of evaluation in this
case.  The sideeffectfunc() calls might be in x order or not, depending on
available indexes, table statistics, which version of SQLite is running,
and so forth.

Notice that I added another table into the outer FROM clause to make it a 
join.  Otherwise, the restriction described by
[post 062d576715d277c8](/forumpost/062d576715d277c8) above would apply
and the ORDER BY would be retained.