On Sat, Apr 5, 2014 at 11:48 PM, Max Vlasov <max.vla...@gmail.com> wrote: > > This works for an old version of sqlite (3.6.10), but today Dominique > Devienne mentioned some doubt about this approach and I decided to > test it with some data with a recent version of sqlite. With 3.8.4.3 > the same join query produced two rows and Nulls for CommaList and > Value >
Actually I found a way for it to work as assumed. My xBestIndex call now makes estimatedCost huge (10000) for non-constrained guess and small (1) for constrained one. Although it works, I'd be glad to know whether there is a way to return "impossible" instead of "expensive". I think that I see why the query worked differently in different sqlite versions.This trick by its nature interprets constrained data (when Where is supplied) as superset of non-constrained one (when it returns no data if there's no Where) and this definitely violates the logic sqlite relies upon. So in a (normal) world where where-constrained result is always smaller than full-scan data result, sqlite is free to choose full-scan instead of a filtered scan for a virtual table (probably it does by default after 3.8.0 for his join query if provided estimatedCosts are equal). Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
