SQLite: Check-in [465bfc72]

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

Overview

Comment:	Fix another problem with the LIKE optimization.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| like-opt-fix
Files:	files \| file ages \| folders
SHA1:	465bfc72d252f94778248253142faeba78ceea02
User & Date:	drh 2015-03-07 20:32:49

Context

2015-03-09
12:11		Always use LIKE optimization range constraints in pairs. (Closed-Leaf check-in: 0e02dc94 user: drh tags: like-opt-fix)
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)

Changes

Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3020   3020   ** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
  3021   3021   ** expression: "x>='ABC' AND x<'abd'".  But this requires that the range
  3022   3022   ** scan loop run twice, once for strings and a second time for BLOBs.
  3023   3023   ** The OP_String opcodes on the second pass convert the upper and lower
  3024   3024   ** bound string contants to blobs.  This routine makes the necessary changes
  3025   3025   ** to the OP_String opcodes for that to happen.
  3026   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;
         3027  +static void whereLikeOptimizationStringFixup(
         3028  +  Vdbe *v,                /* prepared statement under construction */
         3029  +  WhereLevel *pLevel,     /* The loop that contains the LIKE operator */
         3030  +  WhereTerm *pTerm        /* The upper or lower bound just coded */
         3031  +){
         3032  +  if( pTerm->wtFlags & TERM_LIKEOPT ){
         3033  +    VdbeOp *pOp = sqlite3VdbeGetOp(v, -1);
         3034  +    if( pLevel->iLikeRepCntr && pOp->opcode==OP_String8 ){
         3035  +      pOp->p3 = pLevel->iLikeRepCntr;
         3036  +      pOp->p5 = 1;
         3037  +    }
  3033   3038     }
  3034   3039   }
  3035   3040   
  3036   3041   /*
  3037   3042   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  3038   3043   ** implementation described by pWInfo.
  3039   3044   */
................................................................................
  3361   3366       if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
  3362   3367         pRangeStart = pLoop->aLTerm[j++];
  3363   3368         nExtraReg = 1;
  3364   3369       }
  3365   3370       if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
  3366   3371         pRangeEnd = pLoop->aLTerm[j++];
  3367   3372         nExtraReg = 1;
  3368         -      if( pRangeStart
  3369         -       && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0
  3370         -       && (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
         3373  +      if( (pRangeStart && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0)
         3374  +       || (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
  3371   3375         ){
  3372   3376           pLevel->iLikeRepCntr = ++pParse->nMem;
  3373   3377           testcase( bRev );
  3374   3378           testcase( pIdx->aSortOrder[nEq]==SQLITE_SO_DESC );
  3375   3379           sqlite3VdbeAddOp2(v, OP_Integer,
  3376   3380                             bRev ^ (pIdx->aSortOrder[nEq]==SQLITE_SO_DESC),
  3377   3381                             pLevel->iLikeRepCntr);
................................................................................
  3416   3420       start_constraints = pRangeStart || nEq>0;
  3417   3421   
  3418   3422       /* Seek the index cursor to the start of the range. */
  3419   3423       nConstraint = nEq;
  3420   3424       if( pRangeStart ){
  3421   3425         Expr *pRight = pRangeStart->pExpr->pRight;
  3422   3426         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3423         -      whereLikeOptimizationStringFixup(v, pLevel);
         3427  +      whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
  3424   3428         if( (pRangeStart->wtFlags & TERM_VNULL)==0
  3425   3429          && sqlite3ExprCanBeNull(pRight)
  3426   3430         ){
  3427   3431           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3428   3432           VdbeCoverage(v);
  3429   3433         }
  3430   3434         if( zStartAff ){
................................................................................
  3462   3466       ** range (if any).
  3463   3467       */
  3464   3468       nConstraint = nEq;
  3465   3469       if( pRangeEnd ){
  3466   3470         Expr *pRight = pRangeEnd->pExpr->pRight;
  3467   3471         sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
  3468   3472         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3469         -      whereLikeOptimizationStringFixup(v, pLevel);
         3473  +      whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
  3470   3474         if( (pRangeEnd->wtFlags & TERM_VNULL)==0
  3471   3475          && sqlite3ExprCanBeNull(pRight)
  3472   3476         ){
  3473   3477           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3474   3478           VdbeCoverage(v);
  3475   3479         }
  3476   3480         if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE