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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
984c3fd5261619fb542a5a95dab37707 |
User & Date: | drh 2015-03-09 13:01:02.136 |
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: fbae6bafd7 user: drh tags: trunk) | |
2015-03-09
| ||
13:24 | Merge recent trunk enhancements into the ota-update branch. (check-in: 5489cb6892 user: drh tags: ota-update) | |
13:18 | Merge recent trunk enhancements into the apple-osx branch. (check-in: 5e04eec88e user: drh tags: apple-osx) | |
13:07 | Merge recent trunk enhancements into the sessions branch. (check-in: 68c8937e83 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: 984c3fd526 user: drh tags: trunk) | |
12:11 | Always use LIKE optimization range constraints in pairs. (Closed-Leaf check-in: 0e02dc94fd user: drh tags: like-opt-fix) | |
10:40 | Increase the version number to 3.8.9 (check-in: e5da5e7d5d user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
3009 3010 3011 3012 3013 3014 3015 | ); } #else # define addScanStatus(a, b, c, d) ((void)d) #endif /* | | | | > > > > > > > | > | 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 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 | ); } #else # define addScanStatus(a, b, c, d) ((void)d) #endif /* ** If the most recently coded instruction is a constant range contraint ** that originated from the LIKE optimization, then change the P3 to be ** pLoop->iLikeRepCntr and set P5. ** ** 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; assert( pLevel->iLikeRepCntr>0 ); pOp = sqlite3VdbeGetOp(v, -1); assert( pOp!=0 ); assert( pOp->opcode==OP_String8 || pTerm->pWC->pWInfo->pParse->db->mallocFailed ); pOp->p3 = pLevel->iLikeRepCntr; pOp->p5 = 1; } } /* ** Generate code for the start of the iLevel-th loop in the WHERE clause |
︙ | ︙ | |||
3357 3358 3359 3360 3361 3362 3363 3364 3365 3366 3367 | /* Find any inequality constraint terms for the start and end ** of the range. */ j = nEq; if( pLoop->wsFlags & WHERE_BTM_LIMIT ){ pRangeStart = pLoop->aLTerm[j++]; nExtraReg = 1; } if( pLoop->wsFlags & WHERE_TOP_LIMIT ){ pRangeEnd = pLoop->aLTerm[j++]; nExtraReg = 1; | > > > < | > | < | 3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375 3376 3377 3378 3379 3380 3381 3382 3383 3384 3385 3386 3387 3388 | /* Find any inequality constraint terms for the start and end ** of the range. */ j = nEq; if( pLoop->wsFlags & WHERE_BTM_LIMIT ){ pRangeStart = pLoop->aLTerm[j++]; nExtraReg = 1; /* Like optimization range constraints always occur in pairs */ assert( (pRangeStart->wtFlags & TERM_LIKEOPT)==0 || (pLoop->wsFlags & WHERE_TOP_LIMIT)!=0 ); } if( pLoop->wsFlags & WHERE_TOP_LIMIT ){ pRangeEnd = pLoop->aLTerm[j++]; nExtraReg = 1; if( (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0 ){ assert( pRangeStart!=0 ); /* LIKE opt constraints */ assert( pRangeStart->wtFlags & TERM_LIKEOPT ); /* occur in pairs */ 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); VdbeComment((v, "LIKE loop counter")); |
︙ | ︙ | |||
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); | | | 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 | 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); | | | 3472 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 | ** 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 |
︙ | ︙ | |||
4539 4540 4541 4542 4543 4544 4545 4546 4547 4548 4549 4550 4551 4552 | if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0) && (iCol<0 || pSrc->pTab->aCol[iCol].notNull) ){ continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */ } if( pTerm->prereqRight & pNew->maskSelf ) continue; pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTerm; pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf; | > > > > | 4549 4550 4551 4552 4553 4554 4555 4556 4557 4558 4559 4560 4561 4562 4563 4564 4565 4566 | if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0) && (iCol<0 || pSrc->pTab->aCol[iCol].notNull) ){ continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */ } if( pTerm->prereqRight & pNew->maskSelf ) continue; /* Do not allow the upper bound of a LIKE optimization range constraint ** to mix with a lower range bound from some other source */ if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue; pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTerm; pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf; |
︙ | ︙ | |||
4582 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 | pNew->wsFlags |= WHERE_COLUMN_NULL; }else if( eOp & (WO_GT|WO_GE) ){ testcase( eOp & WO_GT ); testcase( eOp & WO_GE ); pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT; pBtm = pTerm; pTop = 0; }else{ assert( eOp & (WO_LT|WO_LE) ); testcase( eOp & WO_LT ); testcase( eOp & WO_LE ); pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT; pTop = pTerm; pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ? | > > > > > > > > > > > | 4596 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 4611 4612 4613 4614 4615 4616 4617 4618 4619 4620 | pNew->wsFlags |= WHERE_COLUMN_NULL; }else if( eOp & (WO_GT|WO_GE) ){ testcase( eOp & WO_GT ); testcase( eOp & WO_GE ); pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT; pBtm = pTerm; pTop = 0; if( pTerm->wtFlags & TERM_LIKEOPT ){ /* Range contraints that come from the LIKE optimization are ** always used in pairs. */ pTop = &pTerm[1]; assert( (pTop-(pTerm->pWC->a))<pTerm->pWC->nTerm ); assert( pTop->wtFlags & TERM_LIKEOPT ); assert( pTop->eOperator==WO_LT ); if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTop; pNew->wsFlags |= WHERE_TOP_LIMIT; } }else{ assert( eOp & (WO_LT|WO_LE) ); testcase( eOp & WO_LT ); testcase( eOp & WO_LE ); pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT; pTop = pTerm; pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ? |
︙ | ︙ |