Consider this SQL:
> ~~~~
CREATE TABLE t1(a);
INSERT INTO t1 VALUES(4),(8),(3),(5),(1),(7),(2),(6);
CREATE VIEW v2 AS SELECT a FROM t1 ORDER BY a;
SELECT * FROM v2;
~~~~
Would you expect the values to come out of v2 in sorted
order because of the ORDER BY clause on the view? They do on
all historical versions of SQLite. But if I omit the ORDER BY
clauses from subqueries, that ORDER BY clause goes away. The
original "`SELECT * FROM v2`" query is transformed into:
> ~~~~
SELECT * FROM (SELECT a FROM t1 ORDER BY a);
~~~~
The ORDER BY clause is removed from the subquery. Then
the query flattener then kicks in and simplifies the query
to be just:
> ~~~~
SELECT * FROM t1;
~~~~
And the rows come out in some arbitrary order. I'm not sure what
the SQL standards say about ORDER BY clauses on VIEWs, but the result
here seems counter-intuitive to me.
My current fix for this is to on remove the ORDER BY clause on the
subquery if either:
1. The outer query has an ORDER BY clause of its own, or
2. The outer query is a join - in other words there are
FROM clause terms other than the subquery that contains
the ORDER BY.