So the QP is attemopting to determine which cost product is less: Outer loop IN table (2 records, assuming cost 2) * Inner loop VT (2) = 4
Outer loop VT (3) * inner loop IN lookup (1 record, assuming cost 1) = 3 My guess is it will probably choose the undesired, invalid plan. A linear cost penalty for desired but optional inputs is probably not going to cut it. In this case, the returned cost needs to be greater than 4 (more if there are more records in the IN set). I feel there needs to be a multiplicative penalty of at least 10. Also, you could check the colUsed mask to check if the name_pattern field is referenced and react accordingly. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Max Vlasov Gesendet: Mittwoch, 17. April 2019 14:56 An: SQLite mailing list <[email protected]> Betreff: Re: [sqlite] [EXTERNAL] "Optional" incoming parameters for virtual tables On Wed, 17 Apr 2019 at 15:08, Hick Gunter <[email protected]> wrote: > ....... > > SELECT contents from textfiles( 'mypath', NULL, 0); SELECT contents > from textfiles where search_path = 'mypath' and is_recursive = 1; > SLEECT contents from textfiles where search_path = 'mypath' and > name_pattern IN ('*.txt','*.csv'); > > With xBestIndex returning costs 1, 2, and 2/3 (with IN and without IN) > respectively. > > And you want to know how to make SQLite always call xFilter with > ('mypath','*.txt') and ('mypath','*.csv') instead of just once with > only > ('mypath') and attempting to retrieve the undefined/empty > name_pattern. Is this correct? > > > Yes, you're correct. The name_pattern is also a good illustrative extension to the case. And indeed, IN operator is really a tricky case _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
