Hello, 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|1|1| 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? With best regards, Alexey Daryin _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
