SQLite query using TEMP B-TREE FOR ORDER BY instead of index

Question

I have a very simple table that includes 2 columns, message_id and server_timestamp, but when I look at the plan for this query:

sqlite> explain query plan select message_id, server_timestamp from messages group by message_id order by server_timestamp;
selectid|order|from|detail
0|0|0|SCAN TABLE messages USING COVERING INDEX index_messages_id_server_timestamp
0|0|0|USE TEMP B-TREE FOR ORDER BY

Where index_messages_id_server_timestamp is an index on (message_id, server_timestamp).

Why does this query need to use a temp b-tree for sorting?

Answer 1

Due to the GROUP BY, multiple rows of the table might result in a single row in the output. This breaks the relationship between the message_id and server_timestamp values, so it is no longer possible to prove that it is valid to use the index to sort them.