SQLite Forum

Virtual table ORDER BY and GT/LT (GE/LE) constraints expected behavior - full table scan when not needed?
Hi all!

I maintain a project that heavily uses the SQLite virtual table mechanism, https://github.com/askgitdev/askgit (via a golang driver).

I wanted to ask about the expected behavior of a query on a virtual table that has an ORDER BY clause (which is consumed by the index) AND a where constraint on the column in that ORDER BY. So for instance:

SELECT field FROM some_v_table WHERE field > 10 ORDER BY field DESC

Let's say `some_v_table` without constraints has 20 rows, where each field is a number from 1 to 20.

With the ORDER BY consumed in the BestIndex, the table should return 20, 19, 18 ...

My assumption would be that SQLite would know then to *stop* iteration when the virtual table reaches 10, since the BestIndex indicates that the results are sorted by this field.

Instead, the behavior I've observed is that SQLite does a "full scan" regardless, iterating down to 1, even though it ultimatelt discards those results less than 10 when returning results.

I'm wondering if this is expected behavior and I would need to implement a "stop" on the virtual table iteration to avoid the full scan, myself. If not, it may indicate something I'm doing incorrectly in my BestIndex/Filter implementation.

Please let me know if this question is not clear, I'm happy to provide some more examples/code pointers if necessary.

Thank you!