SQLite Forum

Discovered bug in xBestIndex of virtual tables
Login
I can confirm that there are some unexpected solutions on sqlite side

So I emulated these testes and for example #2 (with Join) xBestIndex indeed offers two suggestions, one of them containing 3 constraints, but the third constraint in this case is exactly the same as the first one ("foo" field). The second xBestIndex call offers three columns, but the third column has usable field set to 0, so it's effectively a 2-column option. And if I make the 3-column option cheap and 2-column undesirable (returning SQLITE_CONSTRAINT), I get "no query solution". But if 2-column option is ok on my side (SQLITE_OK with some cost), sqlite goes with it (and xFilter offers two values). So the 3-column index suggestion is probably some internal non-usable variant. 

This 2-column result looks exactly like sqlite somehow resolves "bar" value transitively (I see real values in xFilter and real results when bind some actual values). And for example #4 when there's no join I also see only one xBestIndex suggestion with a single column constraint and no solution if I expect both values in xFilter. 

So it looks like sqlite is able somehow transitively resolve values for xFilter, but probably this mechanics is not ideal at the moment. So in my opinion it's not a bug, but a case for improvement