On Tue, Mar 28, 2017 at 11:26 AM, Max Vlasov <max.vla...@gmail.com> wrote:
> > > So, what is the maximum reasonable value of estimatedCost that will not > turn sqlite into possible overflow errors while telling at the same time > that I consider some variant very, very expensive? Or maybe changing cheap > from 1 to 0 will do the trick? > > Hi again, replying to myself since I noticed a more straightforward case explaining estimatedCost peculiarities Sqlite 3.17.0 My comma-list virtual table implementation (vtcommalist) reported the table structure as CREATE TABLE [xxx] ([CommaList] TEXT HIDDEN, [Value] TEXT) Basically it is only functional when CommaList is provided so it can produce Value in this case based on coming CommaList. Hidden column here to allow table-valued functions syntax. Database: The virtual table create virtual table [cmlist] Using VtCommaList a simple test table with lists data create table [lists] ([list] TEXT) populated with 10000 rows insert into lists (list) values ('1, 2, 3, 4') The query in question: Select distinct trim(value) From lists, cmlist(list) Sqlite asks for index evaluation twice: 1. Suggesting constraint for CommaList field only, my code reported "cheap" (value 1) 2. Suggesting no constraint at all, my code reported "expensive" (value 1000000000) But Sqlite still went for full-scan in this case (second choice) so I had to report error leading to "SQL Logic error" Only when I increased "expensive" to 1000000000*10 = 10000000000, Sqlite accepted my cheap/expensive ratio as worth considering with following filter with commalist field constraint. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users