On Tue, Mar 28, 2017 at 11:26 AM, Max Vlasov <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
