Incorrect order of records from virtual table

Question

During testing of our implementation of SQLite Virtual Table mechanism we've encountered an unexpected behavior. For the following virtual table structure:

create table X(ID int, RL real)

This query returns all the records in the correct descending order by RL field.

Query 1:

select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t1.RL desc;

Execution plan 1:

explain query plan select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t1.RL desc;  

0|0|0| SCAN TABLE VTab AS t1 VIRTUAL TABLE INDEX 0:D1; (~0 rows)
0|1|1| SCAN TABLE VTab AS t2 VIRTUAL TABLE INDEX 4:C0=0; (~0 rows)

D1 here is a value generated by our xBestIndex method implementation and means descending sorting by the field #1 = RL. C0=0 here means equal operation for the field #0 = ID. This works as expected.

However, the next query returns rows (the alias of RL field is different) without any sorting.

Query 2:

select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t2.RL desc

Execution plan 2:

explain query plan select * from VTab t1 left outer join VTab t2 on t1.ID = t2.ID order by t2.RL desc;  

0|0|0| SCAN TABLE VTab AS t1 VIRTUAL TABLE INDEX 0: (~0 rows)
0|11| SCAN TABLE VTab AS t2 VIRTUAL TABLE INDEX 4:C0=0; (~0 rows)

As you can see there is no index mentioned to sort by. The query executed against a real table (having the very same structure as our virtual table) looks as follows:

Query 3:

select * from Table1 t1 left outer join Table1 t2 on t1.ID = t2.ID order by t2.RL desc

Execution plan 3:

explain query plan select * from Table1 t1 left outer join Table1t2 on t1.ID = t2.ID order by t2.RL desc;  

0|0|0| SCAN TABLE Table1 AS t1 (~1000000 rows)
0|1|0| SEARCH TABLE Table1 AS t2 USING AUTOMATIC COVERING INDEX (ID=?)
0|1|0| (~7 rows)
0|0|0| USE TEMP B-TREE FOR ORDER BY

As you can see there is sorting using B-tree there.

From our side we examined what we received in sqlite3_index_orderby structures (the part of sqlite3_index_info structure) when they had come to xBestIndex and they didn't contain any information about sorting. Also the orderByConsumed out parameter returns False (as our output is not ordered and we presume that sqlite itself will order rows).

Is this a bug in SQLite Virtual Table support or we missed something?

Answer 1

As the comment in allocateIndexInfo() says, your virtual table gets the opportunity to implement ordering only if "the ORDER BY clause contains only columns in the current virtual table". In your query, the virtual table the sort column comes from is used only to look up the ID column, and such lookups of single values are not usable for ordering.

---

With SQLite 3.7.14, I do get "USE TEMP B-TREE FOR ORDER BY" in my plan for Query 2. What are the values returned by your xBestIndex?