SQLite

    int j = pExpr->iColumn;
    if( j<0 ) return SQLITE_AFF_INTEGER;
    assert( pExpr->pTab && j<pExpr->pTab->nCol );
    return pExpr->pTab->aCol[j].affinity;
  }
  return pExpr->affinity;
}

/*
** Set the explicit collating sequence for an expression to the
** collating sequence supplied in the second argument.
*/
Expr *sqlite3ExprSetColl(Expr *pExpr, CollSeq *pColl){
  if( pExpr && pColl ){
    pExpr->pColl = pColl;
    pExpr->flags |= EP_ExpCollate;
  }
  return pExpr;
}

/*
** Set the collating sequence for expression pExpr to be the collating
** sequence named by pToken.   Return a pointer to the revised expression.
** The collating sequence is marked as "explicit" using the EP_ExpCollate
** flag.  An explicit collating sequence will override implicit
** collating sequences.
*/
Expr *sqlite3ExprSetCollByToken(Parse *pParse, Expr *pExpr, Token *pCollName){
  char *zColl = 0;            /* Dequoted name of collation sequence */
  CollSeq *pColl;
  sqlite3 *db = pParse->db;
  zColl = sqlite3NameFromToken(db, pCollName);

  pColl = sqlite3LocateCollSeq(pParse, zColl);
  sqlite3ExprSetColl(pExpr, pColl);




  sqlite3DbFree(db, zColl);
  return pExpr;
}

/*
** Return the default collation sequence for the expression pExpr. If
** there is no default collation type, return 0.

}
expr(A) ::= VARIABLE(X).     {
  spanExpr(&A, pParse, TK_VARIABLE, &X);
  sqlite3ExprAssignVarNumber(pParse, A.pExpr);
  spanSet(&A, &X, &X);
}
expr(A) ::= expr(E) COLLATE ids(C). {
  A.pExpr = sqlite3ExprSetCollByToken(pParse, E.pExpr, &C);
  A.zStart = E.zStart;
  A.zEnd = &C.z[C.n];
}
%ifndef SQLITE_OMIT_CAST
expr(A) ::= CAST(X) LP expr(E) AS typetoken(T) RP(Y). {
  A.pExpr = sqlite3PExpr(pParse, TK_CAST, E.pExpr, 0, &T);
  spanSet(&A,&X,&Y);

idxlist_opt(A) ::= .                         {A = 0;}
idxlist_opt(A) ::= LP idxlist(X) RP.         {A = X;}
idxlist(A) ::= idxlist(X) COMMA nm(Y) collate(C) sortorder(Z).  {
  Expr *p = 0;
  if( C.n>0 ){
    p = sqlite3Expr(pParse->db, TK_COLUMN, 0);
    sqlite3ExprSetCollByToken(pParse, p, &C);
  }
  A = sqlite3ExprListAppend(pParse,X, p);
  sqlite3ExprListSetName(pParse,A,&Y,1);
  sqlite3ExprListCheckLength(pParse, A, "index");
  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
}
idxlist(A) ::= nm(Y) collate(C) sortorder(Z). {
  Expr *p = 0;
  if( C.n>0 ){
    p = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
    sqlite3ExprSetCollByToken(pParse, p, &C);
  }
  A = sqlite3ExprListAppend(pParse,0, p);
  sqlite3ExprListSetName(pParse, A, &Y, 1);
  sqlite3ExprListCheckLength(pParse, A, "index");
  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
}

void *sqlite3HexToBlob(sqlite3*, const char *z, int n);
int sqlite3TwoPartName(Parse *, Token *, Token *, Token **);
const char *sqlite3ErrStr(int);
int sqlite3ReadSchema(Parse *pParse);
CollSeq *sqlite3FindCollSeq(sqlite3*,u8 enc, const char*,int);
CollSeq *sqlite3LocateCollSeq(Parse *pParse, const char*zName);
CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr);
Expr *sqlite3ExprSetColl(Expr*, CollSeq*);
Expr *sqlite3ExprSetCollByToken(Parse *pParse, Expr*, Token*);
int sqlite3CheckCollSeq(Parse *, CollSeq *);
int sqlite3CheckObjectName(Parse *, const char *);
void sqlite3VdbeSetChanges(sqlite3 *, int);

const void *sqlite3ValueText(sqlite3_value*, u8);
int sqlite3ValueBytes(sqlite3_value*, u8);
void sqlite3ValueSetStr(sqlite3_value*, int, const void *,u8, 

){
  const char *z = 0;         /* String on RHS of LIKE operator */
  Expr *pRight, *pLeft;      /* Right and left size of LIKE operator */
  ExprList *pList;           /* List of operands to the LIKE operator */
  int c;                     /* One character in z[] */
  int cnt;                   /* Number of non-wildcard prefix characters */
  char wc[3];                /* Wildcard characters */

  sqlite3 *db = pParse->db;  /* Database connection */
  sqlite3_value *pVal = 0;
  int op;                    /* Opcode of pRight */

  if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){
    return 0;
  }
#ifdef SQLITE_EBCDIC
  if( *pnoCase ) return 0;
#endif
  pList = pExpr->x.pList;
  pLeft = pList->a[1].pExpr;
  if( pLeft->op!=TK_COLUMN || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT ){
    /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
    ** be the name of an indexed column with TEXT affinity. */
    return 0;
  }
  assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */














  pRight = pList->a[0].pExpr;
  op = pRight->op;
  if( op==TK_REGISTER ){
    op = pRight->op2;
  }
  if( op==TK_VARIABLE ){

    z = pRight->u.zToken;
  }
  if( z ){
    cnt = 0;
    while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
      cnt++;
    }
    if( cnt!=0 && 255!=(u8)z[cnt-1] ){
      Expr *pPrefix;
      *pisComplete = c==wc[0] && z[cnt+1]==0;
      pPrefix = sqlite3Expr(db, TK_STRING, z);
      if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
      *ppPrefix = pPrefix;
      if( op==TK_VARIABLE ){
        Vdbe *v = pParse->pVdbe;
        sqlite3VdbeSetVarmask(v, pRight->iColumn);
        if( *pisComplete && pRight->u.zToken[1] ){

  ){
    Expr *pLeft;       /* LHS of LIKE/GLOB operator */
    Expr *pStr2;       /* Copy of pStr1 - RHS of LIKE/GLOB operator */
    Expr *pNewExpr1;
    Expr *pNewExpr2;
    int idxNew1;
    int idxNew2;
    CollSeq *pColl;    /* Collating sequence to use */

    pLeft = pExpr->x.pList->a[1].pExpr;
    pStr2 = sqlite3ExprDup(db, pStr1, 0);
    if( !db->mallocFailed ){
      u8 c, *pC;       /* Last character before the first wildcard */
      pC = (u8*)&pStr2->u.zToken[sqlite3Strlen30(pStr2->u.zToken)-1];
      c = *pC;
      if( noCase ){
        /* The point is to increment the last character before the first
        ** wildcard.  But if we increment '@', that will push it into the
        ** alphabetic range where case conversions will mess up the 
        ** inequality.  To avoid this, make sure to also run the full
        ** LIKE on all candidate expressions by clearing the isComplete flag
        */
        if( c=='A'-1 ) isComplete = 0;

        c = sqlite3UpperToLower[c];
      }
      *pC = c + 1;
    }
    pColl = sqlite3FindCollSeq(db, SQLITE_UTF8, noCase ? "NOCASE" : "BINARY",0);
    pNewExpr1 = sqlite3PExpr(pParse, TK_GE, 
                     sqlite3ExprSetColl(sqlite3ExprDup(db,pLeft,0), pColl),
                     pStr1, 0);
    idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
    testcase( idxNew1==0 );
    exprAnalyze(pSrc, pWC, idxNew1);
    pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
                     sqlite3ExprSetColl(sqlite3ExprDup(db,pLeft,0), pColl),
                     pStr2, 0);
    idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
    testcase( idxNew2==0 );
    exprAnalyze(pSrc, pWC, idxNew2);
    pTerm = &pWC->a[idxTerm];
    if( isComplete ){
      pWC->a[idxNew1].iParent = idxTerm;
      pWC->a[idxNew2].iParent = idxTerm;

  set like "a%"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 100}
do_test analyze3-2.5 {
  set like "%a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {999 999 100}
do_test analyze3-2.6 {
  set like "a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 0}
do_test analyze3-2.7 {
  set like "ab"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {11 0 0}
do_test analyze3-2.8 {
  set like "abc"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {2 0 1}
do_test analyze3-2.9 {
  set like "a_c"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 10}


#-------------------------------------------------------------------------
# This block of tests checks that statements are correctly marked as
# expired when the values bound to any parameters that may affect the 
# query plan are modified.
#

  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.4 {
  set sqlite_like_count
} 0

# The LIKE optimization still works when the RHS is a string with no
# wildcard.  Ticket [e090183531fc2747]
#
do_test like-3.4.2 {
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;
  }
} {a nosort {} i1}
do_test like-3.4.3 {
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1;
  }
} {ab nosort {} i1}
do_test like-3.4.4 {
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1;
  }
} {abcd nosort {} i1}
do_test like-3.4.5 {
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abcde' ORDER BY 1;
  }
} {nosort {} i1}


# Partial optimization when the pattern does not end in '%'
#
do_test like-3.5 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;

    PRAGMA case_sensitive_like=off;
    SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
  }
} {abd acd nosort {} i1}
do_test like-3.24 {
  set sqlite_like_count
} 6

# GLOB optimization when there is no wildcard.  Ticket [e090183531fc2747]
#
do_test like-3.25 {
  queryplan {
    SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1;
  }
} {a nosort {} i1}
do_test like-3.26 {
  queryplan {
    SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1;
  }
} {abcd nosort {} i1}
do_test like-3.27 {
  queryplan {
    SELECT x FROM t1 WHERE x GLOB 'abcde' ORDER BY 1;
  }
} {nosort {} i1}



# No optimization if the LHS of the LIKE is not a column name or
# if the RHS is not a string.
#
do_test like-4.1 {
  execsql {PRAGMA case_sensitive_like=on}
  set sqlite_like_count 0

  }
} {12 123 scan 3 like 0}
do_test like-10.15 {
  count {
    SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY a;
  }
} {12 123 scan 5 like 6}

# LIKE and GLOB where the default collating sequence is not appropriate
# but an index with the appropriate collating sequence exists.
#
do_test like-11.0 {
  execsql {
    CREATE TABLE t11(
      a INTEGER PRIMARY KEY,
      b TEXT COLLATE nocase,
      c TEXT COLLATE binary
    );
    INSERT INTO t11 VALUES(1, 'a','a');
    INSERT INTO t11 VALUES(2, 'ab','ab');
    INSERT INTO t11 VALUES(3, 'abc','abc');
    INSERT INTO t11 VALUES(4, 'abcd','abcd');
    INSERT INTO t11 VALUES(5, 'A','A');
    INSERT INTO t11 VALUES(6, 'AB','AB');
    INSERT INTO t11 VALUES(7, 'ABC','ABC');
    INSERT INTO t11 VALUES(8, 'ABCD','ABCD');
    INSERT INTO t11 VALUES(9, 'x','x');
    INSERT INTO t11 VALUES(10, 'yz','yz');
    INSERT INTO t11 VALUES(11, 'X','X');
    INSERT INTO t11 VALUES(12, 'YZ','YZ');
    SELECT count(*) FROM t11;
  }
} {12}
do_test like-11.1 {
  queryplan {
    PRAGMA case_sensitive_like=OFF;
    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
  }
} {abc abcd ABC ABCD nosort t11 *}
do_test like-11.2 {
  queryplan {
    PRAGMA case_sensitive_like=ON;
    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
  }
} {abc abcd nosort t11 *}
do_test like-11.3 {
  queryplan {
    PRAGMA case_sensitive_like=OFF;
    CREATE INDEX t11b ON t11(b);
    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
  }
} {abc abcd ABC ABCD sort {} t11b}
do_test like-11.4 {
  queryplan {
    PRAGMA case_sensitive_like=ON;
    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
  }
} {abc abcd nosort t11 *}
do_test like-11.5 {
  queryplan {
    PRAGMA case_sensitive_like=OFF;
    DROP INDEX t11b;
    CREATE INDEX t11bnc ON t11(b COLLATE nocase);
    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
  }
} {abc abcd ABC ABCD sort {} t11bnc}
do_test like-11.6 {
  queryplan {
    CREATE INDEX t11bb ON t11(b COLLATE binary);
    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
  }
} {abc abcd ABC ABCD sort {} t11bnc}
do_test like-11.7 {
  queryplan {
    PRAGMA case_sensitive_like=ON;
    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
  }
} {abc abcd sort {} t11bb}
do_test like-11.8 {
  queryplan {
    PRAGMA case_sensitive_like=OFF;
    SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY a;
  }
} {abc abcd sort {} t11bb}
do_test like-11.9 {
  queryplan {
    CREATE INDEX t11cnc ON t11(c COLLATE nocase);
    CREATE INDEX t11cb ON t11(c COLLATE binary);
    SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY a;
  }
} {abc abcd ABC ABCD sort {} t11cnc}
do_test like-11.10 {
  queryplan {
    SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY a;
  }
} {abc abcd sort {} t11cb}


finish_test