Weird behavior with xBestIndex and xFindFunction in Virtual table
(1) By anonymous on 2020-11-24 22:51:33 [link] [source]
Hi,I have an issue with a virtual table when using xFindFunction and xBestIndex. I would have expect the following queries to give the same behavior.
When doing a query such as: "select * from vtable where filename like 'foo'
"
- xBestIndex have 3 constraints which one of them is
SQLITE_INDEX_CONSTRAINT_LIKE
- The function I set in xFindFunction is called correctly.
- a[0] = 'foo'
- a[1] = filename
When doing "select * from vtable where like('foo', filename)
" which should be equivalent to the first query as mentionned in vtab#xfindfunction:
- xBestIndex have 3 constraints which one of them is
SQLITE_INDEX_CONSTRAINT_LIKE
- The function I set in xFindFunction is not called.
- a[0] = 'foo'
- a[1] = filename
If I invert 'foo' and filename, xFindFunction will be called but xBestIndex will not contain the Like constraint.
The code that calls xFindFunction seem to take a different arg depending on if its an infix or not:
/* Possibly overload the function if the first argument is
** a virtual table column.
**
** For infix functions (LIKE, GLOB, REGEXP, and MATCH) use the
** second argument, not the first, as the argument to test to
** see if it is a column in a virtual table. This is done because
** the left operand of infix functions (the operand we want to
** control overloading) ends up as the second argument to the
** function. The expression "A glob B" is equivalent to
** "glob(B,A). We want to use the A in "A glob B" to test
** for function overloading. But we use the B term in "glob(B,A)".
*/
if( nFarg>=2 && ExprHasProperty(pExpr, EP_InfixFunc) ){
pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[1].pExpr);
}else if( nFarg>0 ){
pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr);
}
But the code for xBestIndex seems to only take a[1]:
/* Built-in operators MATCH, GLOB, LIKE, and REGEXP attach to a
** virtual table on their second argument, which is the same as
** the left-hand side operand in their in-fix form.
**
** vtab_column MATCH expression
** MATCH(expression,vtab_column)
*/
pCol = pList->a[1].pExpr;
Shouldn't xBestIndex and xFindFunction use args the same way? If it's infix, both take a[1] and if it's function-like, xBestIndex takes a[1] and xFindFunction takes a[0].
Thanks
(2) By Richard Hipp (drh) on 2020-11-25 03:50:01 in reply to 1 updated by 2.1 [link] [source]
The xFindFunction function overloading only works in these cases: * *column* `LIKE` *expr* * `LIKE(`*column*,*expr*) In other words, the syntactic left-hand operand must be a column in the virtual table in order for function overloading to work. In the xBestIndex method, the argument column is normally the left-hand operand, except in the case of the special functions LIKE, GLOB, MATCH, and REGEXP in which case it is the right-hand operand. I agree that this is not consistent. Infix function operators (LIKE, GLOB, MATCH, and REGEXP) work differently in xFindFunction than they do in xBestIndex. But this is apparently the way it has always worked. Changing it now might break legacy virtual tables.