/ Check-in [74cb0b03]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix the LIKE optimization so that it finds BLOB entries in addition to text entries. Ticket [05f43be8fdda9f].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 74cb0b032fcf598537fae04412771450124ae712
User & Date: drh 2015-03-07 13:56:48
Context
2015-03-09
10:40
Increase the version number to 3.8.9 check-in: e5da5e7d user: drh tags: trunk
2015-03-07
20:32
Fix another problem with the LIKE optimization. check-in: 465bfc72 user: drh tags: like-opt-fix
13:56
Fix the LIKE optimization so that it finds BLOB entries in addition to text entries. Ticket [05f43be8fdda9f]. check-in: 74cb0b03 user: drh tags: trunk
12:58
New test cases for LIKE and GLOB with BLOB left-hand side values. Closed-Leaf check-in: 50fa3c5f user: drh tags: like-opt-fix
2015-03-06
04:37
Clearification of some documentation text. Added requirements marks. check-in: 8c1e85aa user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

  1646   1646   
  1647   1647   /*
  1648   1648   ** pExpr points to an expression which implements a function.  If
  1649   1649   ** it is appropriate to apply the LIKE optimization to that function
  1650   1650   ** then set aWc[0] through aWc[2] to the wildcard characters and
  1651   1651   ** return TRUE.  If the function is not a LIKE-style function then
  1652   1652   ** return FALSE.
         1653  +**
         1654  +** *pIsNocase is set to true if uppercase and lowercase are equivalent for
         1655  +** the function (default for LIKE).  If the function makes the distinction
         1656  +** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
         1657  +** false.
  1653   1658   */
  1654   1659   int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
  1655   1660     FuncDef *pDef;
  1656   1661     if( pExpr->op!=TK_FUNCTION 
  1657   1662      || !pExpr->x.pList 
  1658   1663      || pExpr->x.pList->nExpr!=2
  1659   1664     ){

Changes to src/select.c.

   559    559     if( pSort->sortFlags & SORTFLAG_UseSorter ){
   560    560       op = OP_SorterInsert;
   561    561     }else{
   562    562       op = OP_IdxInsert;
   563    563     }
   564    564     sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);
   565    565     if( pSelect->iLimit ){
   566         -    int addr1, addr2;
          566  +    int addr;
   567    567       int iLimit;
   568    568       if( pSelect->iOffset ){
   569    569         iLimit = pSelect->iOffset+1;
   570    570       }else{
   571    571         iLimit = pSelect->iLimit;
   572    572       }
   573         -    addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit); VdbeCoverage(v);
   574         -    sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1);
   575         -    addr2 = sqlite3VdbeAddOp0(v, OP_Goto);
   576         -    sqlite3VdbeJumpHere(v, addr1);
          573  +    addr = sqlite3VdbeAddOp3(v, OP_IfNotZero, iLimit, 0, -1); VdbeCoverage(v);
   577    574       sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor);
   578    575       sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor);
   579         -    sqlite3VdbeJumpHere(v, addr2);
          576  +    sqlite3VdbeJumpHere(v, addr);
   580    577     }
   581    578   }
   582    579   
   583    580   /*
   584    581   ** Add code to implement the OFFSET
   585    582   */
   586    583   static void codeOffset(
................................................................................
   969    966     }
   970    967   
   971    968     /* Jump to the end of the loop if the LIMIT is reached.  Except, if
   972    969     ** there is a sorter, in which case the sorter has already limited
   973    970     ** the output for us.
   974    971     */
   975    972     if( pSort==0 && p->iLimit ){
   976         -    sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v);
          973  +    sqlite3VdbeAddOp2(v, OP_DecrJumpZero, p->iLimit, iBreak); VdbeCoverage(v);
   977    974     }
   978    975   }
   979    976   
   980    977   /*
   981    978   ** Allocate a KeyInfo object sufficient for an index of N key columns and
   982    979   ** X extra columns.
   983    980   */
................................................................................
  1822   1819         }else if( n>=0 && p->nSelectRow>(u64)n ){
  1823   1820           p->nSelectRow = n;
  1824   1821         }
  1825   1822       }else{
  1826   1823         sqlite3ExprCode(pParse, p->pLimit, iLimit);
  1827   1824         sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); VdbeCoverage(v);
  1828   1825         VdbeComment((v, "LIMIT counter"));
  1829         -      sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak); VdbeCoverage(v);
         1826  +      sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, iBreak); VdbeCoverage(v);
  1830   1827       }
  1831   1828       if( p->pOffset ){
  1832   1829         p->iOffset = iOffset = ++pParse->nMem;
  1833   1830         pParse->nMem++;   /* Allocate an extra register for limit+offset */
  1834   1831         sqlite3ExprCode(pParse, p->pOffset, iOffset);
  1835   1832         sqlite3VdbeAddOp1(v, OP_MustBeInt, iOffset); VdbeCoverage(v);
  1836   1833         VdbeComment((v, "OFFSET counter"));
................................................................................
  2041   2038   
  2042   2039     /* Output the single row in Current */
  2043   2040     addrCont = sqlite3VdbeMakeLabel(v);
  2044   2041     codeOffset(v, regOffset, addrCont);
  2045   2042     selectInnerLoop(pParse, p, p->pEList, iCurrent,
  2046   2043         0, 0, pDest, addrCont, addrBreak);
  2047   2044     if( regLimit ){
  2048         -    sqlite3VdbeAddOp3(v, OP_IfZero, regLimit, addrBreak, -1);
         2045  +    sqlite3VdbeAddOp2(v, OP_DecrJumpZero, regLimit, addrBreak);
  2049   2046       VdbeCoverage(v);
  2050   2047     }
  2051   2048     sqlite3VdbeResolveLabel(v, addrCont);
  2052   2049   
  2053   2050     /* Execute the recursive SELECT taking the single row in Current as
  2054   2051     ** the value for the recursive-table. Store the results in the Queue.
  2055   2052     */
................................................................................
  2266   2263         if( rc ){
  2267   2264           goto multi_select_end;
  2268   2265         }
  2269   2266         p->pPrior = 0;
  2270   2267         p->iLimit = pPrior->iLimit;
  2271   2268         p->iOffset = pPrior->iOffset;
  2272   2269         if( p->iLimit ){
  2273         -        addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit); VdbeCoverage(v);
         2270  +        addr = sqlite3VdbeAddOp1(v, OP_IfNot, p->iLimit); VdbeCoverage(v);
  2274   2271           VdbeComment((v, "Jump ahead if LIMIT reached"));
  2275   2272         }
  2276   2273         explainSetInteger(iSub2, pParse->iNextSelectId);
  2277   2274         rc = sqlite3Select(pParse, p, &dest);
  2278   2275         testcase( rc!=SQLITE_OK );
  2279   2276         pDelete = p->pPrior;
  2280   2277         p->pPrior = pPrior;
................................................................................
  2667   2664         break;
  2668   2665       }
  2669   2666     }
  2670   2667   
  2671   2668     /* Jump to the end of the loop if the LIMIT is reached.
  2672   2669     */
  2673   2670     if( p->iLimit ){
  2674         -    sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v);
         2671  +    sqlite3VdbeAddOp2(v, OP_DecrJumpZero, p->iLimit, iBreak); VdbeCoverage(v);
  2675   2672     }
  2676   2673   
  2677   2674     /* Generate the subroutine return
  2678   2675     */
  2679   2676     sqlite3VdbeResolveLabel(v, iContinue);
  2680   2677     sqlite3VdbeAddOp1(v, OP_Return, regReturn);
  2681   2678   

Changes to src/vdbe.c.

  1011   1011   }
  1012   1012   #endif
  1013   1013   
  1014   1014   /* Opcode: String8 * P2 * P4 *
  1015   1015   ** Synopsis: r[P2]='P4'
  1016   1016   **
  1017   1017   ** P4 points to a nul terminated UTF-8 string. This opcode is transformed 
  1018         -** into a String before it is executed for the first time.  During
         1018  +** into a String opcode before it is executed for the first time.  During
  1019   1019   ** this transformation, the length of string P4 is computed and stored
  1020   1020   ** as the P1 parameter.
  1021   1021   */
  1022   1022   case OP_String8: {         /* same as TK_STRING, out2-prerelease */
  1023   1023     assert( pOp->p4.z!=0 );
  1024   1024     pOp->opcode = OP_String;
  1025   1025     pOp->p1 = sqlite3Strlen30(pOp->p4.z);
................................................................................
  1043   1043   #endif
  1044   1044     if( pOp->p1>db->aLimit[SQLITE_LIMIT_LENGTH] ){
  1045   1045       goto too_big;
  1046   1046     }
  1047   1047     /* Fall through to the next case, OP_String */
  1048   1048   }
  1049   1049     
  1050         -/* Opcode: String P1 P2 * P4 *
         1050  +/* Opcode: String P1 P2 P3 P4 P5
  1051   1051   ** Synopsis: r[P2]='P4' (len=P1)
  1052   1052   **
  1053   1053   ** The string value P4 of length P1 (bytes) is stored in register P2.
         1054  +**
         1055  +** If P5!=0 and the content of register P3 is greater than zero, then
         1056  +** the datatype of the register P2 is converted to BLOB.  The content is
         1057  +** the same sequence of bytes, it is merely interpreted as a BLOB instead
         1058  +** of a string, as if it had been CAST.
  1054   1059   */
  1055   1060   case OP_String: {          /* out2-prerelease */
  1056   1061     assert( pOp->p4.z!=0 );
  1057   1062     pOut->flags = MEM_Str|MEM_Static|MEM_Term;
  1058   1063     pOut->z = pOp->p4.z;
  1059   1064     pOut->n = pOp->p1;
  1060   1065     pOut->enc = encoding;
  1061   1066     UPDATE_MAX_BLOBSIZE(pOut);
         1067  +  if( pOp->p5 ){
         1068  +    assert( pOp->p3>0 );
         1069  +    assert( pOp->p3<=(p->nMem-p->nCursor) );
         1070  +    pIn3 = &aMem[pOp->p3];
         1071  +    assert( pIn3->flags & MEM_Int );
         1072  +    if( pIn3->u.i ) pOut->flags = MEM_Blob|MEM_Static|MEM_Term;
         1073  +  }
  1062   1074     break;
  1063   1075   }
  1064   1076   
  1065   1077   /* Opcode: Null P1 P2 P3 * *
  1066   1078   ** Synopsis:  r[P2..P3]=NULL
  1067   1079   **
  1068   1080   ** Write a NULL into registers P2.  If P3 greater than P2, then also write
................................................................................
  5569   5581     break;
  5570   5582   }
  5571   5583   #endif /* SQLITE_OMIT_AUTOINCREMENT */
  5572   5584   
  5573   5585   /* Opcode: IfPos P1 P2 * * *
  5574   5586   ** Synopsis: if r[P1]>0 goto P2
  5575   5587   **
  5576         -** If the value of register P1 is 1 or greater, jump to P2.
         5588  +** Register P1 must contain an integer.
         5589  +** If the value of register P1 is 1 or greater, jump to P2 and
         5590  +** add the literal value P3 to register P1.
  5577   5591   **
  5578         -** It is illegal to use this instruction on a register that does
  5579         -** not contain an integer.  An assertion fault will result if you try.
         5592  +** If the initial value of register P1 is less than 1, then the
         5593  +** value is unchanged and control passes through to the next instruction.
  5580   5594   */
  5581   5595   case OP_IfPos: {        /* jump, in1 */
  5582   5596     pIn1 = &aMem[pOp->p1];
  5583   5597     assert( pIn1->flags&MEM_Int );
  5584   5598     VdbeBranchTaken( pIn1->u.i>0, 2);
  5585   5599     if( pIn1->u.i>0 ){
  5586   5600        pc = pOp->p2 - 1;
................................................................................
  5601   5615     VdbeBranchTaken(pIn1->u.i<0, 2);
  5602   5616     if( pIn1->u.i<0 ){
  5603   5617        pc = pOp->p2 - 1;
  5604   5618     }
  5605   5619     break;
  5606   5620   }
  5607   5621   
  5608         -/* Opcode: IfZero P1 P2 P3 * *
  5609         -** Synopsis: r[P1]+=P3, if r[P1]==0 goto P2
         5622  +/* Opcode: IfNotZero P1 P2 P3 * *
         5623  +** Synopsis: if r[P1]!=0 then r[P1]+=P3, goto P2
  5610   5624   **
  5611         -** The register P1 must contain an integer.  Add literal P3 to the
  5612         -** value in register P1.  If the result is exactly 0, jump to P2. 
         5625  +** Register P1 must contain an integer.  If the content of register P1 is
         5626  +** initially nonzero, then add P3 to P1 and jump to P2.  If register P1 is
         5627  +** initially zero, leave it unchanged and fall through.
  5613   5628   */
  5614         -case OP_IfZero: {        /* jump, in1 */
         5629  +case OP_IfNotZero: {        /* jump, in1 */
         5630  +  pIn1 = &aMem[pOp->p1];
         5631  +  assert( pIn1->flags&MEM_Int );
         5632  +  VdbeBranchTaken(pIn1->u.i<0, 2);
         5633  +  if( pIn1->u.i ){
         5634  +     pIn1->u.i += pOp->p3;
         5635  +     pc = pOp->p2 - 1;
         5636  +  }
         5637  +  break;
         5638  +}
         5639  +
         5640  +/* Opcode: DecrJumpZero P1 P2 * * *
         5641  +** Synopsis: if (--r[P1])==0 goto P2
         5642  +**
         5643  +** Register P1 must hold an integer.  Decrement the value in register P1
         5644  +** then jump to P2 if the new value is exactly zero.
         5645  +*/
         5646  +case OP_DecrJumpZero: {      /* jump, in1 */
  5615   5647     pIn1 = &aMem[pOp->p1];
  5616   5648     assert( pIn1->flags&MEM_Int );
  5617         -  pIn1->u.i += pOp->p3;
         5649  +  pIn1->u.i--;
  5618   5650     VdbeBranchTaken(pIn1->u.i==0, 2);
  5619   5651     if( pIn1->u.i==0 ){
  5620   5652        pc = pOp->p2 - 1;
  5621   5653     }
  5622   5654     break;
  5623   5655   }
  5624   5656   
         5657  +
         5658  +/* Opcode: JumpZeroIncr P1 P2 * * *
         5659  +** Synopsis: if (r[P1]++)==0 ) goto P2
         5660  +**
         5661  +** The register P1 must contain an integer.  If register P1 is initially
         5662  +** zero, then jump to P2.  Increment register P1 regardless of whether or
         5663  +** not the jump is taken.
         5664  +*/
         5665  +case OP_JumpZeroIncr: {        /* jump, in1 */
         5666  +  pIn1 = &aMem[pOp->p1];
         5667  +  assert( pIn1->flags&MEM_Int );
         5668  +  VdbeBranchTaken(pIn1->u.i==0, 2);
         5669  +  if( (pIn1->u.i++)==0 ){
         5670  +     pc = pOp->p2 - 1;
         5671  +  }
         5672  +  break;
         5673  +}
         5674  +
  5625   5675   /* Opcode: AggStep * P2 P3 P4 P5
  5626   5676   ** Synopsis: accum=r[P3] step(r[P2@P5])
  5627   5677   **
  5628   5678   ** Execute the step function for an aggregate.  The
  5629   5679   ** function has P5 arguments.   P4 is a pointer to the FuncDef
  5630   5680   ** structure that specifies the function.  Use register
  5631   5681   ** P3 as the accumulator.

Changes to src/where.c.

   198    198   ** This is true even if this routine fails to allocate a new WhereTerm.
   199    199   **
   200    200   ** WARNING:  This routine might reallocate the space used to store
   201    201   ** WhereTerms.  All pointers to WhereTerms should be invalidated after
   202    202   ** calling this routine.  Such pointers may be reinitialized by referencing
   203    203   ** the pWC->a[] array.
   204    204   */
   205         -static int whereClauseInsert(WhereClause *pWC, Expr *p, u8 wtFlags){
          205  +static int whereClauseInsert(WhereClause *pWC, Expr *p, u16 wtFlags){
   206    206     WhereTerm *pTerm;
   207    207     int idx;
   208    208     testcase( wtFlags & TERM_VIRTUAL );
   209    209     if( pWC->nTerm>=pWC->nSlot ){
   210    210       WhereTerm *pOld = pWC->a;
   211    211       sqlite3 *db = pWC->pWInfo->pParse->db;
   212    212       pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 );
................................................................................
   623    623   #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
   624    624   /*
   625    625   ** Check to see if the given expression is a LIKE or GLOB operator that
   626    626   ** can be optimized using inequality constraints.  Return TRUE if it is
   627    627   ** so and false if not.
   628    628   **
   629    629   ** In order for the operator to be optimizible, the RHS must be a string
   630         -** literal that does not begin with a wildcard.  
          630  +** literal that does not begin with a wildcard.  The LHS must be a column
          631  +** that may only be NULL, a string, or a BLOB, never a number. (This means
          632  +** that virtual tables cannot participate in the LIKE optimization.)  If the
          633  +** collating sequence for the column on the LHS must be appropriate for
          634  +** the operator.
   631    635   */
   632    636   static int isLikeOrGlob(
   633    637     Parse *pParse,    /* Parsing and code generating context */
   634    638     Expr *pExpr,      /* Test this expression */
   635    639     Expr **ppPrefix,  /* Pointer to TK_STRING expression with pattern prefix */
   636    640     int *pisComplete, /* True if the only wildcard is % in the last character */
   637    641     int *pnoCase      /* True if uppercase is equivalent to lowercase */
................................................................................
   652    656   #ifdef SQLITE_EBCDIC
   653    657     if( *pnoCase ) return 0;
   654    658   #endif
   655    659     pList = pExpr->x.pList;
   656    660     pLeft = pList->a[1].pExpr;
   657    661     if( pLeft->op!=TK_COLUMN 
   658    662      || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
   659         -   || IsVirtual(pLeft->pTab)
          663  +   || IsVirtual(pLeft->pTab)  /* Value might be numeric */
   660    664     ){
   661    665       /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
   662    666       ** be the name of an indexed column with TEXT affinity. */
   663    667       return 0;
   664    668     }
   665    669     assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */
   666    670   
................................................................................
  1101   1105     WhereMaskSet *pMaskSet;          /* Set of table index masks */
  1102   1106     Expr *pExpr;                     /* The expression to be analyzed */
  1103   1107     Bitmask prereqLeft;              /* Prerequesites of the pExpr->pLeft */
  1104   1108     Bitmask prereqAll;               /* Prerequesites of pExpr */
  1105   1109     Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
  1106   1110     Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  1107   1111     int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  1108         -  int noCase = 0;                  /* LIKE/GLOB distinguishes case */
         1112  +  int noCase = 0;                  /* uppercase equivalent to lowercase */
  1109   1113     int op;                          /* Top-level operator.  pExpr->op */
  1110   1114     Parse *pParse = pWInfo->pParse;  /* Parsing context */
  1111   1115     sqlite3 *db = pParse->db;        /* Database connection */
  1112   1116   
  1113   1117     if( db->mallocFailed ){
  1114   1118       return;
  1115   1119     }
................................................................................
  1239   1243     }
  1240   1244   #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
  1241   1245   
  1242   1246   #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
  1243   1247     /* Add constraints to reduce the search space on a LIKE or GLOB
  1244   1248     ** operator.
  1245   1249     **
  1246         -  ** A like pattern of the form "x LIKE 'abc%'" is changed into constraints
         1250  +  ** A like pattern of the form "x LIKE 'aBc%'" is changed into constraints
  1247   1251     **
  1248         -  **          x>='abc' AND x<'abd' AND x LIKE 'abc%'
         1252  +  **          x>='ABC' AND x<'abd' AND x LIKE 'aBc%'
  1249   1253     **
  1250   1254     ** The last character of the prefix "abc" is incremented to form the
  1251         -  ** termination condition "abd".
         1255  +  ** termination condition "abd".  If case is not significant (the default
         1256  +  ** for LIKE) then the lower-bound is made all uppercase and the upper-
         1257  +  ** bound is made all lowercase so that the bounds also work when comparing
         1258  +  ** BLOBs.
  1252   1259     */
  1253   1260     if( pWC->op==TK_AND 
  1254   1261      && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase)
  1255   1262     ){
  1256   1263       Expr *pLeft;       /* LHS of LIKE/GLOB operator */
  1257   1264       Expr *pStr2;       /* Copy of pStr1 - RHS of LIKE/GLOB operator */
  1258   1265       Expr *pNewExpr1;
  1259   1266       Expr *pNewExpr2;
  1260   1267       int idxNew1;
  1261   1268       int idxNew2;
  1262   1269       Token sCollSeqName;  /* Name of collating sequence */
         1270  +    const u16 wtFlags = TERM_LIKEOPT | TERM_VIRTUAL | TERM_DYNAMIC;
  1263   1271   
  1264   1272       pLeft = pExpr->x.pList->a[1].pExpr;
  1265   1273       pStr2 = sqlite3ExprDup(db, pStr1, 0);
         1274  +
         1275  +    /* Convert the lower bound to upper-case and the upper bound to
         1276  +    ** lower-case (upper-case is less than lower-case in ASCII) so that
         1277  +    ** the range constraints also work for BLOBs
         1278  +    */
         1279  +    if( noCase && !pParse->db->mallocFailed ){
         1280  +      int i;
         1281  +      char c;
         1282  +      pTerm->wtFlags |= TERM_LIKE;
         1283  +      for(i=0; (c = pStr1->u.zToken[i])!=0; i++){
         1284  +        pStr1->u.zToken[i] = sqlite3Toupper(c);
         1285  +        pStr2->u.zToken[i] = sqlite3Tolower(c);
         1286  +      }
         1287  +    }
         1288  +
  1266   1289       if( !db->mallocFailed ){
  1267   1290         u8 c, *pC;       /* Last character before the first wildcard */
  1268   1291         pC = (u8*)&pStr2->u.zToken[sqlite3Strlen30(pStr2->u.zToken)-1];
  1269   1292         c = *pC;
  1270   1293         if( noCase ){
  1271   1294           /* The point is to increment the last character before the first
  1272   1295           ** wildcard.  But if we increment '@', that will push it into the
................................................................................
  1278   1301           c = sqlite3UpperToLower[c];
  1279   1302         }
  1280   1303         *pC = c + 1;
  1281   1304       }
  1282   1305       sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
  1283   1306       sCollSeqName.n = 6;
  1284   1307       pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
  1285         -    pNewExpr1 = sqlite3PExpr(pParse, TK_GE, 
         1308  +    pNewExpr1 = sqlite3PExpr(pParse, TK_GE,
  1286   1309              sqlite3ExprAddCollateToken(pParse,pNewExpr1,&sCollSeqName),
  1287   1310              pStr1, 0);
  1288   1311       transferJoinMarkings(pNewExpr1, pExpr);
  1289         -    idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
         1312  +    idxNew1 = whereClauseInsert(pWC, pNewExpr1, wtFlags);
  1290   1313       testcase( idxNew1==0 );
  1291   1314       exprAnalyze(pSrc, pWC, idxNew1);
  1292   1315       pNewExpr2 = sqlite3ExprDup(db, pLeft, 0);
  1293   1316       pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
  1294   1317              sqlite3ExprAddCollateToken(pParse,pNewExpr2,&sCollSeqName),
  1295   1318              pStr2, 0);
  1296   1319       transferJoinMarkings(pNewExpr2, pExpr);
  1297         -    idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
         1320  +    idxNew2 = whereClauseInsert(pWC, pNewExpr2, wtFlags);
  1298   1321       testcase( idxNew2==0 );
  1299   1322       exprAnalyze(pSrc, pWC, idxNew2);
  1300   1323       pTerm = &pWC->a[idxTerm];
  1301   1324       if( isComplete ){
  1302   1325         markTermAsChild(pWC, idxNew1, idxTerm);
  1303   1326         markTermAsChild(pWC, idxNew2, idxTerm);
  1304   1327       }
................................................................................
  2465   2488   ** Disabling a term causes that term to not be tested in the inner loop
  2466   2489   ** of the join.  Disabling is an optimization.  When terms are satisfied
  2467   2490   ** by indices, we disable them to prevent redundant tests in the inner
  2468   2491   ** loop.  We would get the correct results if nothing were ever disabled,
  2469   2492   ** but joins might run a little slower.  The trick is to disable as much
  2470   2493   ** as we can without disabling too much.  If we disabled in (1), we'd get
  2471   2494   ** the wrong answer.  See ticket #813.
         2495  +**
         2496  +** If all the children of a term are disabled, then that term is also
         2497  +** automatically disabled.  In this way, terms get disabled if derived
         2498  +** virtual terms are tested first.  For example:
         2499  +**
         2500  +**      x GLOB 'abc*' AND x>='abc' AND x<'acd'
         2501  +**      \___________/     \______/     \_____/
         2502  +**         parent          child1       child2
         2503  +**
         2504  +** Only the parent term was in the original WHERE clause.  The child1
         2505  +** and child2 terms were added by the LIKE optimization.  If both of
         2506  +** the virtual child terms are valid, then testing of the parent can be 
         2507  +** skipped.
         2508  +**
         2509  +** Usually the parent term is marked as TERM_CODED.  But if the parent
         2510  +** term was originally TERM_LIKE, then the parent gets TERM_LIKECOND instead.
         2511  +** The TERM_LIKECOND marking indicates that the term should be coded inside
         2512  +** a conditional such that is only evaluated on the second pass of a
         2513  +** LIKE-optimization loop, when scanning BLOBs instead of strings.
  2472   2514   */
  2473   2515   static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){
  2474         -  if( pTerm
         2516  +  int nLoop = 0;
         2517  +  while( pTerm
  2475   2518         && (pTerm->wtFlags & TERM_CODED)==0
  2476   2519         && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
  2477   2520         && (pLevel->notReady & pTerm->prereqAll)==0
  2478   2521     ){
  2479         -    pTerm->wtFlags |= TERM_CODED;
  2480         -    if( pTerm->iParent>=0 ){
  2481         -      WhereTerm *pOther = &pTerm->pWC->a[pTerm->iParent];
  2482         -      if( (--pOther->nChild)==0 ){
  2483         -        disableTerm(pLevel, pOther);
  2484         -      }
         2522  +    if( nLoop && (pTerm->wtFlags & TERM_LIKE)!=0 ){
         2523  +      pTerm->wtFlags |= TERM_LIKECOND;
         2524  +    }else{
         2525  +      pTerm->wtFlags |= TERM_CODED;
  2485   2526       }
         2527  +    if( pTerm->iParent<0 ) break;
         2528  +    pTerm = &pTerm->pWC->a[pTerm->iParent];
         2529  +    pTerm->nChild--;
         2530  +    if( pTerm->nChild!=0 ) break;
         2531  +    nLoop++;
  2486   2532     }
  2487   2533   }
  2488   2534   
  2489   2535   /*
  2490   2536   ** Code an OP_Affinity opcode to apply the column affinity string zAff
  2491   2537   ** to the n registers starting at base. 
  2492   2538   **
................................................................................
  2962   3008         v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj
  2963   3009     );
  2964   3010   }
  2965   3011   #else
  2966   3012   # define addScanStatus(a, b, c, d) ((void)d)
  2967   3013   #endif
  2968   3014   
  2969         -
         3015  +/*
         3016  +** Look at the last instruction coded.  If that instruction is OP_String8
         3017  +** and if pLoop->iLikeRepCntr is non-zero, then change the P3 to be
         3018  +** pLoop->iLikeRepCntr and set P5.
         3019  +**
         3020  +** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
         3021  +** expression: "x>='ABC' AND x<'abd'".  But this requires that the range
         3022  +** scan loop run twice, once for strings and a second time for BLOBs.
         3023  +** The OP_String opcodes on the second pass convert the upper and lower
         3024  +** bound string contants to blobs.  This routine makes the necessary changes
         3025  +** to the OP_String opcodes for that to happen.
         3026  +*/
         3027  +static void whereLikeOptimizationStringFixup(Vdbe *v, WhereLevel *pLevel){
         3028  +  VdbeOp *pOp;
         3029  +  pOp = sqlite3VdbeGetOp(v, -1);
         3030  +  if( pLevel->iLikeRepCntr && pOp->opcode==OP_String8 ){
         3031  +    pOp->p3 = pLevel->iLikeRepCntr;
         3032  +    pOp->p5 = 1;
         3033  +  }
         3034  +}
  2970   3035   
  2971   3036   /*
  2972   3037   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  2973   3038   ** implementation described by pWInfo.
  2974   3039   */
  2975   3040   static Bitmask codeOneLoopStart(
  2976   3041     WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
................................................................................
  3296   3361       if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
  3297   3362         pRangeStart = pLoop->aLTerm[j++];
  3298   3363         nExtraReg = 1;
  3299   3364       }
  3300   3365       if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
  3301   3366         pRangeEnd = pLoop->aLTerm[j++];
  3302   3367         nExtraReg = 1;
         3368  +      if( pRangeStart
         3369  +       && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0
         3370  +       && (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
         3371  +      ){
         3372  +        pLevel->iLikeRepCntr = ++pParse->nMem;
         3373  +        testcase( bRev );
         3374  +        testcase( pIdx->aSortOrder[nEq]==SQLITE_SO_DESC );
         3375  +        sqlite3VdbeAddOp2(v, OP_Integer,
         3376  +                          bRev ^ (pIdx->aSortOrder[nEq]==SQLITE_SO_DESC),
         3377  +                          pLevel->iLikeRepCntr);
         3378  +        VdbeComment((v, "LIKE loop counter"));
         3379  +        pLevel->addrLikeRep = sqlite3VdbeCurrentAddr(v);
         3380  +      }
  3303   3381         if( pRangeStart==0
  3304   3382          && (j = pIdx->aiColumn[nEq])>=0 
  3305   3383          && pIdx->pTable->aCol[j].notNull==0
  3306   3384         ){
  3307   3385           bSeekPastNull = 1;
  3308   3386         }
  3309   3387       }
................................................................................
  3338   3416       start_constraints = pRangeStart || nEq>0;
  3339   3417   
  3340   3418       /* Seek the index cursor to the start of the range. */
  3341   3419       nConstraint = nEq;
  3342   3420       if( pRangeStart ){
  3343   3421         Expr *pRight = pRangeStart->pExpr->pRight;
  3344   3422         sqlite3ExprCode(pParse, pRight, regBase+nEq);
         3423  +      whereLikeOptimizationStringFixup(v, pLevel);
  3345   3424         if( (pRangeStart->wtFlags & TERM_VNULL)==0
  3346   3425          && sqlite3ExprCanBeNull(pRight)
  3347   3426         ){
  3348   3427           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3349   3428           VdbeCoverage(v);
  3350   3429         }
  3351   3430         if( zStartAff ){
................................................................................
  3383   3462       ** range (if any).
  3384   3463       */
  3385   3464       nConstraint = nEq;
  3386   3465       if( pRangeEnd ){
  3387   3466         Expr *pRight = pRangeEnd->pExpr->pRight;
  3388   3467         sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
  3389   3468         sqlite3ExprCode(pParse, pRight, regBase+nEq);
         3469  +      whereLikeOptimizationStringFixup(v, pLevel);
  3390   3470         if( (pRangeEnd->wtFlags & TERM_VNULL)==0
  3391   3471          && sqlite3ExprCanBeNull(pRight)
  3392   3472         ){
  3393   3473           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3394   3474           VdbeCoverage(v);
  3395   3475         }
  3396   3476         if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
................................................................................
  3773   3853   #endif
  3774   3854   
  3775   3855     /* Insert code to test every subexpression that can be completely
  3776   3856     ** computed using the current set of tables.
  3777   3857     */
  3778   3858     for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  3779   3859       Expr *pE;
         3860  +    int skipLikeAddr = 0;
  3780   3861       testcase( pTerm->wtFlags & TERM_VIRTUAL );
  3781   3862       testcase( pTerm->wtFlags & TERM_CODED );
  3782   3863       if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  3783   3864       if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
  3784   3865         testcase( pWInfo->untestedTerms==0
  3785   3866                  && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
  3786   3867         pWInfo->untestedTerms = 1;
  3787   3868         continue;
  3788   3869       }
  3789   3870       pE = pTerm->pExpr;
  3790   3871       assert( pE!=0 );
  3791   3872       if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
  3792   3873         continue;
         3874  +    }
         3875  +    if( pTerm->wtFlags & TERM_LIKECOND ){
         3876  +      assert( pLevel->iLikeRepCntr>0 );
         3877  +      skipLikeAddr = sqlite3VdbeAddOp1(v, OP_IfNot, pLevel->iLikeRepCntr);
         3878  +      VdbeCoverage(v);
  3793   3879       }
  3794   3880       sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
         3881  +    if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
  3795   3882       pTerm->wtFlags |= TERM_CODED;
  3796   3883     }
  3797   3884   
  3798   3885     /* Insert code to test for implied constraints based on transitivity
  3799   3886     ** of the "==" operator.
  3800   3887     **
  3801   3888     ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
................................................................................
  6590   6677       }
  6591   6678       sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
  6592   6679       if( pLevel->addrSkip ){
  6593   6680         sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrSkip);
  6594   6681         VdbeComment((v, "next skip-scan on %s", pLoop->u.btree.pIndex->zName));
  6595   6682         sqlite3VdbeJumpHere(v, pLevel->addrSkip);
  6596   6683         sqlite3VdbeJumpHere(v, pLevel->addrSkip-2);
         6684  +    }
         6685  +    if( pLevel->addrLikeRep ){
         6686  +      int op;
         6687  +      if( sqlite3VdbeGetOp(v, pLevel->addrLikeRep-1)->p1 ){
         6688  +        op = OP_DecrJumpZero;
         6689  +      }else{
         6690  +        op = OP_JumpZeroIncr;
         6691  +      }
         6692  +      sqlite3VdbeAddOp2(v, op, pLevel->iLikeRepCntr, pLevel->addrLikeRep);
         6693  +      VdbeCoverage(v);
  6597   6694       }
  6598   6695       if( pLevel->iLeftJoin ){
  6599   6696         addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v);
  6600   6697         assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
  6601   6698              || (pLoop->wsFlags & WHERE_INDEXED)!=0 );
  6602   6699         if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 ){
  6603   6700           sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor);

Changes to src/whereInt.h.

    65     65     int iIdxCur;          /* The VDBE cursor used to access pIdx */
    66     66     int addrBrk;          /* Jump here to break out of the loop */
    67     67     int addrNxt;          /* Jump here to start the next IN combination */
    68     68     int addrSkip;         /* Jump here for next iteration of skip-scan */
    69     69     int addrCont;         /* Jump here to continue with the next loop cycle */
    70     70     int addrFirst;        /* First instruction of interior of the loop */
    71     71     int addrBody;         /* Beginning of the body of this loop */
           72  +  int iLikeRepCntr;     /* LIKE range processing counter register */
           73  +  int addrLikeRep;      /* LIKE range processing address */
    72     74     u8 iFrom;             /* Which entry in the FROM clause */
    73     75     u8 op, p3, p5;        /* Opcode, P3 & P5 of the opcode that ends the loop */
    74     76     int p1, p2;           /* Operands of the opcode used to ends the loop */
    75     77     union {               /* Information that depends on pWLoop->wsFlags */
    76     78       struct {
    77     79         int nIn;              /* Number of entries in aInLoop[] */
    78     80         struct InLoop {
................................................................................
   249    251     union {
   250    252       int leftColumn;         /* Column number of X in "X <op> <expr>" */
   251    253       WhereOrInfo *pOrInfo;   /* Extra information if (eOperator & WO_OR)!=0 */
   252    254       WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */
   253    255     } u;
   254    256     LogEst truthProb;       /* Probability of truth for this expression */
   255    257     u16 eOperator;          /* A WO_xx value describing <op> */
   256         -  u8 wtFlags;             /* TERM_xxx bit flags.  See below */
          258  +  u16 wtFlags;            /* TERM_xxx bit flags.  See below */
   257    259     u8 nChild;              /* Number of children that must disable us */
   258    260     WhereClause *pWC;       /* The clause this term is part of */
   259    261     Bitmask prereqRight;    /* Bitmask of tables used by pExpr->pRight */
   260    262     Bitmask prereqAll;      /* Bitmask of tables referenced by pExpr */
   261    263   };
   262    264   
   263    265   /*
................................................................................
   271    273   #define TERM_ANDINFO    0x20   /* Need to free the WhereTerm.u.pAndInfo obj */
   272    274   #define TERM_OR_OK      0x40   /* Used during OR-clause processing */
   273    275   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
   274    276   #  define TERM_VNULL    0x80   /* Manufactured x>NULL or x<=NULL term */
   275    277   #else
   276    278   #  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
   277    279   #endif
          280  +#define TERM_LIKEOPT    0x100  /* Virtual terms from the LIKE optimization */
          281  +#define TERM_LIKECOND   0x200  /* Conditionally this LIKE operator term */
          282  +#define TERM_LIKE       0x400  /* The original LIKE operator */
   278    283   
   279    284   /*
   280    285   ** An instance of the WhereScan object is used as an iterator for locating
   281    286   ** terms in the WHERE clause that are useful to the query planner.
   282    287   */
   283    288   struct WhereScan {
   284    289     WhereClause *pOrigWC;      /* Original, innermost WhereClause */

Changes to test/analyze3.test.

   277    277   } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}}
   278    278   do_eqp_test analyze3-2.3 {
   279    279     SELECT count(a) FROM t1 WHERE b LIKE '%a'
   280    280   } {0 0 0 {SCAN TABLE t1}}
   281    281   
   282    282   do_test analyze3-2.4 {
   283    283     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
   284         -} {101 0 100}
          284  +} {102 0 100}
   285    285   do_test analyze3-2.5 {
   286    286     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
   287    287   } {999 999 100}
   288    288   
   289         -do_test analyze3-2.4 {
          289  +do_test analyze3-2.6 {
   290    290     set like "a%"
   291    291     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
   292         -} {101 0 100}
   293         -do_test analyze3-2.5 {
          292  +} {102 0 100}
          293  +do_test analyze3-2.7 {
   294    294     set like "%a"
   295    295     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
   296    296   } {999 999 100}
   297         -do_test analyze3-2.6 {
          297  +do_test analyze3-2.8 {
   298    298     set like "a"
   299    299     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
   300         -} {101 0 0}
   301         -do_test analyze3-2.7 {
          300  +} {102 0 0}
          301  +do_test analyze3-2.9 {
   302    302     set like "ab"
   303    303     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
   304         -} {11 0 0}
   305         -do_test analyze3-2.8 {
          304  +} {12 0 0}
          305  +do_test analyze3-2.10 {
   306    306     set like "abc"
   307    307     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
   308         -} {2 0 1}
   309         -do_test analyze3-2.9 {
          308  +} {3 0 1}
          309  +do_test analyze3-2.11 {
   310    310     set like "a_c"
   311    311     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
   312         -} {101 0 10}
          312  +} {102 0 10}
   313    313   
   314    314   
   315    315   #-------------------------------------------------------------------------
   316    316   # This block of tests checks that statements are correctly marked as
   317    317   # expired when the values bound to any parameters that may affect the 
   318    318   # query plan are modified.
   319    319   #

Changes to test/like.test.

   745    745         SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a;
   746    746       }
   747    747     } {12 123 scan 5 like 6}
   748    748     do_test like-10.5 {
   749    749       count {
   750    750         SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
   751    751       }
   752         -  } {12 123 scan 3 like 0}
          752  +  } {12 123 scan 4 like 0}
   753    753     do_test like-10.6 {
   754    754       count {
   755    755         SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a;
   756    756       }
   757    757     } {12 123 scan 5 like 6}
   758    758     do_test like-10.10 {
   759    759       execsql {
................................................................................
   786    786         SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a;
   787    787       }
   788    788     } {12 123 scan 5 like 6}
   789    789     do_test like-10.14 {
   790    790       count {
   791    791         SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
   792    792       }
   793         -  } {12 123 scan 3 like 0}
          793  +  } {12 123 scan 4 like 0}
   794    794     do_test like-10.15 {
   795    795       count {
   796    796         SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a;
   797    797       }
   798    798     } {12 123 scan 5 like 6}
   799    799   }
   800    800   

Added test/like3.test.

            1  +# 2015-03-06
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library.  The
           13  +# focus of this file is testing the LIKE and GLOB operators and
           14  +# in particular the optimizations that occur to help those operators
           15  +# run faster and that those optimizations work correctly when there
           16  +# are both strings and blobs being tested.
           17  +#
           18  +# Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following
           19  +# SQL was not working correctly:
           20  +#
           21  +#     CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase);
           22  +#     INSERT INTO t1(x) VALUES(x'616263');
           23  +#     SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%';
           24  +#     SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%';
           25  +#
           26  +# This script verifies that it works right now.
           27  +#
           28  +
           29  +set testdir [file dirname $argv0]
           30  +source $testdir/tester.tcl
           31  +
           32  +do_execsql_test like3-1.1 {
           33  +  PRAGMA encoding=UTF8;
           34  +  CREATE TABLE t1(a,b TEXT COLLATE nocase);
           35  +  INSERT INTO t1(a,b)
           36  +     VALUES(1,'abc'),
           37  +           (2,'ABX'),
           38  +           (3,'BCD'),
           39  +           (4,x'616263'),
           40  +           (5,x'414258'),
           41  +           (6,x'424344');
           42  +  CREATE INDEX t1ba ON t1(b,a);
           43  +
           44  +  SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a;
           45  +} {1 abc 2 ABX 4 abc 5 ABX}
           46  +do_execsql_test like3-1.2 {
           47  +  SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a;
           48  +} {1 abc 2 ABX 4 abc 5 ABX}
           49  +
           50  +do_execsql_test like3-2.0 {
           51  +  CREATE TABLE t2(a, b TEXT);
           52  +  INSERT INTO t2 SELECT a, b FROM t1;
           53  +  CREATE INDEX t2ba ON t2(b,a);
           54  +  SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a;
           55  +} {1 abc 4 abc}
           56  +do_execsql_test like3-2.1 {
           57  +  SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a;
           58  +} {1 abc 4 abc}
           59  +do_execsql_test like3-2.2 {
           60  +  SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*'
           61  +} {4 abc}
           62  +do_execsql_test like3-2.3 {
           63  +  SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*'
           64  +} {4 abc}
           65  +do_execsql_test like3-2.4 {
           66  +  SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162'
           67  +} {4 abc}
           68  +do_execsql_test like3-2.5 {
           69  +  SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162'
           70  +} {4 abc}
           71  +
           72  +do_execsql_test like3-3.0 {
           73  +  CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase);
           74  +  INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz');
           75  +  INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3;
           76  +  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x;
           77  +} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
           78  +do_execsql_test like3-3.1 {
           79  +  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC;
           80  +} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
           81  +do_execsql_test like3-3.1ck {
           82  +  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC;
           83  +} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
           84  +do_execsql_test like3-3.2 {
           85  +  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC;
           86  +} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
           87  +do_execsql_test like3-3.2ck {
           88  +  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC;
           89  +} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
           90  +
           91  +do_execsql_test like3-4.0 {
           92  +  CREATE TABLE t4(x TEXT COLLATE nocase);
           93  +  CREATE INDEX t4x ON t4(x DESC);
           94  +  INSERT INTO t4(x) SELECT x FROM t3;
           95  +  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x;
           96  +} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
           97  +do_execsql_test like3-4.1 {
           98  +  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC;
           99  +} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
          100  +do_execsql_test like3-4.1ck {
          101  +  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC;
          102  +} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
          103  +do_execsql_test like3-4.2 {
          104  +  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC;
          105  +} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
          106  +do_execsql_test like3-4.2ck {
          107  +  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC;
          108  +} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
          109  +
          110  +
          111  +
          112  +finish_test

Changes to test/where8.test.

    62     62   
    63     63   do_test where8-1.3 { 
    64     64     execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }
    65     65   } {IX X II 0 0 6}
    66     66   
    67     67   do_test where8-1.4 { 
    68     68     execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }
    69         -} {IX X III II 0 0 9}
           69  +} {IX X III II 0 0 10}
    70     70   
    71     71   do_test where8-1.5 { 
    72     72     execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }
    73         -} {IX X V IV 0 0 9}
           73  +} {IX X V IV 0 0 10}
    74     74   
    75     75   do_test where8-1.6 { 
    76     76     execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }
    77     77   } {I III 0 1}
    78     78   
    79     79   do_test where8-1.7 { 
    80     80     execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }