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: |
465bfc72d252f94778248253142faeba |
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
Changes to src/where.c.
︙ | ︙ | |||
3020 3021 3022 3023 3024 3025 3026 | ** 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. */ | | | > > > > | > | 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 3045 3046 | ** 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, /* 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. */ static Bitmask codeOneLoopStart( WhereInfo *pWInfo, /* Complete information about the WHERE clause */ |
︙ | ︙ | |||
3361 3362 3363 3364 3365 3366 3367 | if( pLoop->wsFlags & WHERE_BTM_LIMIT ){ pRangeStart = pLoop->aLTerm[j++]; nExtraReg = 1; } if( pLoop->wsFlags & WHERE_TOP_LIMIT ){ pRangeEnd = pLoop->aLTerm[j++]; nExtraReg = 1; | < | | | 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 ){ 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 | 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); | | | 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, 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 | ** range (if any). */ nConstraint = nEq; if( pRangeEnd ){ Expr *pRight = pRangeEnd->pExpr->pRight; sqlite3ExprCacheRemove(pParse, regBase+nEq, 1); sqlite3ExprCode(pParse, pRight, regBase+nEq); | | | 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, 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 |
︙ | ︙ |