SQLite Forum

Should SQLite be able to optimize this query?
Suppose an application-defined SQL function "`sideeffectfunc(x)`" has some
external side effects.  Or suppose that function maintains some kind of
internal state such that result of each call depends on previous calls.
Then the following two queries might give
surprising and undesirable results if the ORDER BY clause is omitted:

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

HT to Tom Lane at Postgres for spotting this one.  I have not yet adjusted
the implementation to take this case into account.