SQLite

Check-in [465bfc72d2]
Login

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

Overview
Comment:Fix another problem with the LIKE optimization.
Downloads: Tarball | ZIP 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.790
Context
2015-03-09
12:11
Always use LIKE optimization range constraints in pairs. (Closed-Leaf check-in: 0e02dc94fd user: drh tags: like-opt-fix)
2015-03-07
20:32
Fix another problem with the LIKE optimization. (check-in: 465bfc72d2 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: 74cb0b032f user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
︙
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032











3033
3034
3035
3036
3037
3038
3039

3020
3021
3022
3023
3024
3025
3026






3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044








-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+








** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
** expression: "x>='ABC' AND x<'abd'".  But this requires that the range
** scan loop run twice, once for strings and a second time for BLOBs.
** The OP_String opcodes on the second pass convert the upper and lower
** bound string contants to blobs.  This routine makes the necessary changes
** to the OP_String opcodes for that to happen.
*/
static void whereLikeOptimizationStringFixup(Vdbe *v, WhereLevel *pLevel){
  VdbeOp *pOp;
  pOp = sqlite3VdbeGetOp(v, -1);
  if( pLevel->iLikeRepCntr && pOp->opcode==OP_String8 ){
    pOp->p3 = pLevel->iLikeRepCntr;
    pOp->p5 = 1;
static void whereLikeOptimizationStringFixup(
  Vdbe *v,                /* prepared statement under construction */
  WhereLevel *pLevel,     /* The loop that contains the LIKE operator */
  WhereTerm *pTerm        /* The upper or lower bound just coded */
){
  if( pTerm->wtFlags & TERM_LIKEOPT ){
    VdbeOp *pOp = sqlite3VdbeGetOp(v, -1);
    if( pLevel->iLikeRepCntr && pOp->opcode==OP_String8 ){
      pOp->p3 = pLevel->iLikeRepCntr;
      pOp->p5 = 1;
    }
  }
}

/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
︙
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370


3371
3372
3373
3374
3375
3376
3377

3366
3367
3368
3369
3370
3371
3372



3373
3374
3375
3376
3377
3378
3379
3380
3381








-
-
-
+
+








    if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
      pRangeStart = pLoop->aLTerm[j++];
      nExtraReg = 1;
    }
    if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
      pRangeEnd = pLoop->aLTerm[j++];
      nExtraReg = 1;
      if( pRangeStart
       && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0
       && (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
      if( (pRangeStart && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0)
       || (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
      ){
        pLevel->iLikeRepCntr = ++pParse->nMem;
        testcase( bRev );
        testcase( pIdx->aSortOrder[nEq]==SQLITE_SO_DESC );
        sqlite3VdbeAddOp2(v, OP_Integer,
                          bRev ^ (pIdx->aSortOrder[nEq]==SQLITE_SO_DESC),
                          pLevel->iLikeRepCntr);
︙
3416
3417
3418
3419
3420
3421
3422
3423

3424
3425
3426
3427
3428
3429
3430

3420
3421
3422
3423
3424
3425
3426

3427
3428
3429
3430
3431
3432
3433
3434








-
+








    start_constraints = pRangeStart || nEq>0;

    /* Seek the index cursor to the start of the range. */
    nConstraint = nEq;
    if( pRangeStart ){
      Expr *pRight = pRangeStart->pExpr->pRight;
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      whereLikeOptimizationStringFixup(v, pLevel);
      whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
      if( (pRangeStart->wtFlags & TERM_VNULL)==0
       && sqlite3ExprCanBeNull(pRight)
      ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
        VdbeCoverage(v);
      }
      if( zStartAff ){
︙
3462
3463
3464
3465
3466
3467
3468
3469

3470
3471
3472
3473
3474
3475
3476

3466
3467
3468
3469
3470
3471
3472

3473
3474
3475
3476
3477
3478
3479
3480








-
+








    ** range (if any).
    */
    nConstraint = nEq;
    if( pRangeEnd ){
      Expr *pRight = pRangeEnd->pExpr->pRight;
      sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      whereLikeOptimizationStringFixup(v, pLevel);
      whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
      if( (pRangeEnd->wtFlags & TERM_VNULL)==0
       && sqlite3ExprCanBeNull(pRight)
      ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
        VdbeCoverage(v);
      }
      if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
︙