/ Check-in [984c3fd5]
Login

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

Overview
Comment:Fix the LIKE optimization so that it works even if there are additional range contraints on the column that is subject to the LIKE or GLOB.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 984c3fd5261619fb542a5a95dab37707b5d79dbf
User & Date: drh 2015-03-09 13:01:02
Context
2015-03-10
13:50
Revise test cases in malloc5.test to accommodate varying allocation sizes returned by some system malloc() implementations. check-in: fbae6baf user: drh tags: trunk
2015-03-09
13:24
Merge recent trunk enhancements into the ota-update branch. check-in: 5489cb68 user: drh tags: ota-update
13:18
Merge recent trunk enhancements into the apple-osx branch. check-in: 5e04eec8 user: drh tags: apple-osx
13:07
Merge recent trunk enhancements into the sessions branch. check-in: 68c8937e user: drh tags: sessions
13:01
Fix the LIKE optimization so that it works even if there are additional range contraints on the column that is subject to the LIKE or GLOB. check-in: 984c3fd5 user: drh tags: trunk
12:11
Always use LIKE optimization range constraints in pairs. Closed-Leaf check-in: 0e02dc94 user: drh tags: like-opt-fix
10:40
Increase the version number to 3.8.9 check-in: e5da5e7d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3009   3009     );
  3010   3010   }
  3011   3011   #else
  3012   3012   # define addScanStatus(a, b, c, d) ((void)d)
  3013   3013   #endif
  3014   3014   
  3015   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
         3016  +** If the most recently coded instruction is a constant range contraint
         3017  +** that originated from the LIKE optimization, then change the P3 to be
  3018   3018   ** pLoop->iLikeRepCntr and set P5.
  3019   3019   **
  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 ){
         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;
         3034  +    assert( pLevel->iLikeRepCntr>0 );
         3035  +    pOp = sqlite3VdbeGetOp(v, -1);
         3036  +    assert( pOp!=0 );
         3037  +    assert( pOp->opcode==OP_String8 
         3038  +            || pTerm->pWC->pWInfo->pParse->db->mallocFailed );
  3031   3039       pOp->p3 = pLevel->iLikeRepCntr;
  3032   3040       pOp->p5 = 1;
  3033   3041     }
  3034   3042   }
  3035   3043   
  3036   3044   /*
  3037   3045   ** Generate code for the start of the iLevel-th loop in the WHERE clause
................................................................................
  3357   3365       /* Find any inequality constraint terms for the start and end 
  3358   3366       ** of the range. 
  3359   3367       */
  3360   3368       j = nEq;
  3361   3369       if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
  3362   3370         pRangeStart = pLoop->aLTerm[j++];
  3363   3371         nExtraReg = 1;
         3372  +      /* Like optimization range constraints always occur in pairs */
         3373  +      assert( (pRangeStart->wtFlags & TERM_LIKEOPT)==0 || 
         3374  +              (pLoop->wsFlags & WHERE_TOP_LIMIT)!=0 );
  3364   3375       }
  3365   3376       if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
  3366   3377         pRangeEnd = pLoop->aLTerm[j++];
  3367   3378         nExtraReg = 1;
  3368         -      if( pRangeStart
  3369         -       && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0
  3370         -       && (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
  3371         -      ){
         3379  +      if( (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0 ){
         3380  +        assert( pRangeStart!=0 );                     /* LIKE opt constraints */
         3381  +        assert( pRangeStart->wtFlags & TERM_LIKEOPT );   /* occur in pairs */
  3372   3382           pLevel->iLikeRepCntr = ++pParse->nMem;
  3373   3383           testcase( bRev );
  3374   3384           testcase( pIdx->aSortOrder[nEq]==SQLITE_SO_DESC );
  3375   3385           sqlite3VdbeAddOp2(v, OP_Integer,
  3376   3386                             bRev ^ (pIdx->aSortOrder[nEq]==SQLITE_SO_DESC),
  3377   3387                             pLevel->iLikeRepCntr);
  3378   3388           VdbeComment((v, "LIKE loop counter"));
................................................................................
  3416   3426       start_constraints = pRangeStart || nEq>0;
  3417   3427   
  3418   3428       /* Seek the index cursor to the start of the range. */
  3419   3429       nConstraint = nEq;
  3420   3430       if( pRangeStart ){
  3421   3431         Expr *pRight = pRangeStart->pExpr->pRight;
  3422   3432         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3423         -      whereLikeOptimizationStringFixup(v, pLevel);
         3433  +      whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
  3424   3434         if( (pRangeStart->wtFlags & TERM_VNULL)==0
  3425   3435          && sqlite3ExprCanBeNull(pRight)
  3426   3436         ){
  3427   3437           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3428   3438           VdbeCoverage(v);
  3429   3439         }
  3430   3440         if( zStartAff ){
................................................................................
  3462   3472       ** range (if any).
  3463   3473       */
  3464   3474       nConstraint = nEq;
  3465   3475       if( pRangeEnd ){
  3466   3476         Expr *pRight = pRangeEnd->pExpr->pRight;
  3467   3477         sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
  3468   3478         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3469         -      whereLikeOptimizationStringFixup(v, pLevel);
         3479  +      whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
  3470   3480         if( (pRangeEnd->wtFlags & TERM_VNULL)==0
  3471   3481          && sqlite3ExprCanBeNull(pRight)
  3472   3482         ){
  3473   3483           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3474   3484           VdbeCoverage(v);
  3475   3485         }
  3476   3486         if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
................................................................................
  4539   4549       if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0)
  4540   4550        && (iCol<0 || pSrc->pTab->aCol[iCol].notNull)
  4541   4551       ){
  4542   4552         continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
  4543   4553       }
  4544   4554       if( pTerm->prereqRight & pNew->maskSelf ) continue;
  4545   4555   
         4556  +    /* Do not allow the upper bound of a LIKE optimization range constraint
         4557  +    ** to mix with a lower range bound from some other source */
         4558  +    if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
         4559  +
  4546   4560       pNew->wsFlags = saved_wsFlags;
  4547   4561       pNew->u.btree.nEq = saved_nEq;
  4548   4562       pNew->nLTerm = saved_nLTerm;
  4549   4563       if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
  4550   4564       pNew->aLTerm[pNew->nLTerm++] = pTerm;
  4551   4565       pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
  4552   4566   
................................................................................
  4582   4596         pNew->wsFlags |= WHERE_COLUMN_NULL;
  4583   4597       }else if( eOp & (WO_GT|WO_GE) ){
  4584   4598         testcase( eOp & WO_GT );
  4585   4599         testcase( eOp & WO_GE );
  4586   4600         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
  4587   4601         pBtm = pTerm;
  4588   4602         pTop = 0;
         4603  +      if( pTerm->wtFlags & TERM_LIKEOPT ){
         4604  +        /* Range contraints that come from the LIKE optimization are
         4605  +        ** always used in pairs. */
         4606  +        pTop = &pTerm[1];
         4607  +        assert( (pTop-(pTerm->pWC->a))<pTerm->pWC->nTerm );
         4608  +        assert( pTop->wtFlags & TERM_LIKEOPT );
         4609  +        assert( pTop->eOperator==WO_LT );
         4610  +        if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
         4611  +        pNew->aLTerm[pNew->nLTerm++] = pTop;
         4612  +        pNew->wsFlags |= WHERE_TOP_LIMIT;
         4613  +      }
  4589   4614       }else{
  4590   4615         assert( eOp & (WO_LT|WO_LE) );
  4591   4616         testcase( eOp & WO_LT );
  4592   4617         testcase( eOp & WO_LE );
  4593   4618         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
  4594   4619         pTop = pTerm;
  4595   4620         pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ?