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
