SQLite Forum

Weird behavior with xBestIndex and xFindFunction in Virtual table
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].