Q: Why does static list of values cause using "TEMP B-TREE FOR ORDER BY"? I have the following DB:
sqlite> create table t (num number, text text);
sqlite> create index idx on t (num, text);

Now execute:
sqlite> explain query plan select text from t where num=1 order by text;

The output is expected by me - all done using covering index.
0|0|0|SEARCH TABLE t USING COVERING INDEX idx (num=?)

Now run:
sqlite> explain query plan select text from t where num in (1,2) order by text;

Output:
0|0|0|SEARCH TABLE t USING COVERING INDEX idx (num=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY

Why does static list of values cause using "TEMP B-TREE FOR ORDER BY"?

A: The query can read multiple rows from the table (actually, from the covering index).
If those rows are read in ascending index order, then the text values are already guaranteed to be sorted correctly.
This query is implemented by going through each value in the IN clause, and looking up the matching row in the index.
This indeed happens to be the correct order.
However, SQLite's query optimizer is not capable of proving that the IN clause's order is the same as the index's num order. Therefore, it cannot omit the extra sorting step.
