And then there remain to be considered the effects of the pragma CASE_SENSITIVE_LIKE
-----Urspr?ngliche Nachricht----- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Freitag, 06. M?rz 2015 10:30 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On Fri, Mar 6, 2015 at 10:12 AM, R.Smith <rsmith at rsweb.co.za> wrote: > On 2015-03-06 09:42 AM, Dominique Devienne wrote: > >> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp <drh at sqlite.org> wrote: >> >> >>> The LIKE operator can be overridden by the application to mean >>> anything the application wants - it is not compelled to follow >>> standard SQL semantics. For that reason, virtual tables are unable >>> to optimize using LIKE since they have no way of knowing what it will do. >>> >>> Works as designed. >>> >>> Sure. But SQLite knows whether LIKE is overriden by the application >>> or >> not, >> so shouldn't it only hide LIKE from the virtual table when it detects >> it, instead of wholesale preventing the vtable from optimizing the >> "normal semantic" LIKE case? My $0.02. --DD >> > > I'm sure this is possible, but how would it be feasible? If this was > implemented, how would you know as a programmer designing a virtual > table (or many other interface things) whether or not the target you > operate on will/will not be able to run some bit of code? Even if you > could query the API to find out whether it is possible (i.e. the > normal LIKE is used), you still need to use that result as a specifier > to decide which code block to implement. And if you are going to have > to do two code blocks... might as well do the one where LIKE isn't supported > right from the start. > > Now if there was a setting where you could /force/ the standard LIKE > to be used along with supporting the v-table interface, that might be > something, but that might break a whole other universe of > possibilities for v-table users and end-user customization. That's not what I was saying. DRH is saying the vtable *never* sees the LIKE and SQLite itself a-posteriori filters out the rows based on LIKE. The vtable is not aware it's been denied seeing the LIKE. I'm saying that (in the vast majority of) cases when LIKE is not overriden by the application (globally or for that vtable), something SQLite can know, SQLite could pass the LIKE constraint to the xBestIndex, and let the vtable decide whether it can handle (i.e. optimize) LIKE or not, and whether SQLite should double-check it or not (as usual for vtable indexing). When it is overriden, it behaves as now. But that way the vtable has at least the opportunity to optimize LIKE, unlike now. Whether that's a priority, obviously not given what we've read so far, but whether that's technically possible, from 30,000 ft I don't see why not given the above. My $0.02. --DD _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
