SQLite Forum

Discovered bug in xBestIndex of virtual tables
It it is a bit strange.

In example 4, "bar = foo" cannot be used by the virtual table, and so is not passed to xBestIndex. For a virtual table to use a constraint, one side of the operation must be a virtual table column and the other side some value that is available before the virtual table scan is begun. In other words, there must be a value available to pass to xFilter. If both sides of the constraint are columns of the virtual table, neither are available before the scan is started and so the constraint cannot be used by the virtual table.

It's curious that example 2 gives you 3 constraints. Two would be correct I think - the explicit "foo = ?" and the implied "bar = ?" (if "bar=foo" and "foo=?", it must be the case that "bar=?").

I suspect the 3rd constraint is the code that finds the "bar=?" constraint being too aggressive - reasoning that since bar=? and bar=foo, there is a second foo=?.

Not sure why you don't get this transitive constraint in example 4. Quite possibly you should be...