[sqlite] The LIKE optimization breaks user defined like functions

[Domingo Alvarez Duarte]

Hello !

Today I decided to see why my applications stop using index when using 
my user defined "like" function and it seems that during the creation of 
the "LIKE" optimization sqlite3 made some special settings to the 
builtin "like" function but didn't exposed it to third party developers.

I was looking at the documentation and didn't saw any warning/mention to 
this change affecting user defined "like" functions and before it was 
perfectly "legal" to overwrite the "like" function.

So I'm asking to make the extra settings public/documented to allow 
again it be user defined.

Cheers !

Mainly flags set by a private only function:

SQLITE_FUNC_LIKE

SQLITE_FUNC_CASE

/*
** Set the LIKEOPT flag on the 2-argument function with the given name.
*/
static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
   FuncDef *pDef;
   pDef = sqlite3FindFunction(db, zName, 2, SQLITE_UTF8, 0);
   if( ALWAYS(pDef) ){
     pDef->funcFlags |= flagVal;
   }
}

And also it blindly overwrites any user defined function by it's own 
builtin with this pragma:

pragma case_sensitive_like;

   /* Reinstall the LIKE and GLOB functions.  The variant of LIKE
   ** used will be case sensitive or not depending on the RHS.
   */
   case PragTyp_CASE_SENSITIVE_LIKE: {
     if( zRight ){
       sqlite3RegisterLikeFunctions(db, sqlite3GetBoolean(zRight, 0));
     }else{
       FuncDef *pDef = sqlite3FindFunction(db, "like", 2, SQLITE_UTF8, 0);
       returnSingleInt(v, "case_sensitive_like", pDef && 
(pDef->funcFlags & SQLITE_FUNC_CASE));
     }
   }
   break;

/*
** Register the built-in LIKE and GLOB functions.  The caseSensitive
** parameter determines whether or not the LIKE operator is case
** sensitive.  GLOB is always case sensitive.
*/
void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
   struct compareInfo *pInfo;
   if( caseSensitive ){
     pInfo = (struct compareInfo*)&likeInfoAlt;
   }else{
     pInfo = (struct compareInfo*)&likeInfoNorm;
   }
   sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
   sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
   sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8,
       (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0);
   setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
   setLikeOptFlag(db, "like",
       caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : 
SQLITE_FUNC_LIKE);
}

/*
** pExpr points to an expression which implements a function.  If
** it is appropriate to apply the LIKE optimization to that function
** then set aWc[0] through aWc[2] to the wildcard characters and
** return TRUE.  If the function is not a LIKE-style function then
** return FALSE.
**
** *pIsNocase is set to true if uppercase and lowercase are equivalent for
** the function (default for LIKE).  If the function makes the distinction
** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
** false.
*/
int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char 
*aWc){
   FuncDef *pDef;
   if( pExpr->op!=TK_FUNCTION
    || !pExpr->x.pList
    || pExpr->x.pList->nExpr!=2
   ){
     return 0;
   }
   assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
   pDef = sqlite3FindFunction(db, pExpr->u.zToken, 2, SQLITE_UTF8, 0);
   if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){
     return 0;
   }

   /* The memcpy() statement assumes that the wildcard characters are
   ** the first three statements in the compareInfo structure.  The
   ** asserts() that follow verify that assumption
   */
   memcpy(aWc, pDef->pUserData, 3);
   assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
   assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
   assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
   *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0;
   return 1;
}