SQLite Forum

Virtual table ORDER BY and GT/LT (GE/LE) constraints expected behavior - full table scan when not needed?
> 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.

SQLite isn't quite smart enough to do that. It will take all the rows you can give it and test each one for (field>10), returning those that match to the user.

You'll need to handle the constraint in the usual manner - by setting the argvIndex member so that the RHS value is passed to the xFilter callback and so on.