On 3/6/15, Dominique Devienne <ddevienne at gmail.com> wrote:
>
> 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?
It's more complicated than that.
The optimization in question is to convert WHERE clause terms of the form:
xyz LIKE 'abc%'
Into:
xyz>='abc' AND xyz<'abd'
But this optimization is only valid if (1) xyz is a text value, not a
numeric value and (2) xyz has the "nocase" collating sequence. We
typically do not know either for a virtual table. You might know (2)
if the expression has an explicit COLLATE clause:
xyz LIKE 'abc%' COLLATE nocase
But SQLite does not have a mechanism whereby a virtual table can tell
the query planner that the value of a column will never be numeric.
Yikes! Actually (1) cannot be determined for normal (non-virtual)
tables either because the value could be a BLOB even if the column
affinity is TEXT. And so the current LIKE optimization is not valid
*ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f
I don't yet know how we will fix this...
--
D. Richard Hipp
drh at sqlite.org
