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] [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 [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.

(2.1) By Richard Hipp (drh) on 2020-11-25 03:53:41 edited from 2.0 in reply to 1 [link] [source]

Deleted

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

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] and the penultimate paragraph of [2].