SQLite Forum

Weird behavior with xBestIndex and xFindFunction in Virtual table
Login

Weird behavior with xBestIndex and xFindFunction in Virtual table

(1) By anonymous on 2020-11-24 22:51:33 [link]

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.1) By Richard Hipp (drh) on 2020-11-25 03:53:41 edited from 2.0 in reply to 1

Deleted

(3) By Richard Hipp (drh) on 2020-11-25 13:14:15 in reply to 1 [link]

Yes, this seems goofy.  But it is also how it has worked for 15 years and
so it seems ill-advised to change it now, as it might break legacy.

The behavior is now documented.  See, for example, [\[1\]][1] and the
penultimate paragraph of [\[2\]][2].

[1]: https://www.sqlite.org/draft/vtab.html#like_glob_regexp_and_match_functions
[2]: https://www.sqlite.org/draft/vtab.html#the_xfindfunction_method