Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | The optimization of check-in [b67a6e33f2] does not work (it generates incorrect VDBE code) if an OR term is AND-ed with a constant expression. So back that optimization out and add a test case to make sure it does not get added back in. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
eed754fe93644f4df082eac0c0a7ffc5 |
| User & Date: | drh 2014-07-22 19:14:42 |
|
2014-07-22
| ||
| 20:02 | Add the OP_ReopenIdx opcode that works like OP_OpenRead except that it becomes a no-op if the cursor is already open on the same index. Update the OR-optimization logic to make use of OP_ReopenIdx in order to avoid unnecessary cursor open requests sent to the B-Tree layer. (check-in: 77f412ca user: drh tags: trunk) | |
| 19:14 | The optimization of check-in [b67a6e33f2] does not work (it generates incorrect VDBE code) if an OR term is AND-ed with a constant expression. So back that optimization out and add a test case to make sure it does not get added back in. (check-in: eed754fe user: drh tags: trunk) | |
| 16:00 | Fix the index name for the shadow tables in the spellfix1 extension so that multiple instances of the spellfix1 virtual table can each have their own index. (check-in: 438c348a user: drh tags: trunk) | |
| 00:40 | For the OR-optimization, avoid generating OP_OpenRead opcodes that reopen exactly the same index. (check-in: b67a6e33 user: drh tags: trunk) | |
Changes to src/sqliteInt.h.
2132 2132 #define WHERE_FORCE_TABLE 0x0020 /* Do not use an index-only search */ 2133 2133 #define WHERE_ONETABLE_ONLY 0x0040 /* Only code the 1st table in pTabList */ 2134 2134 #define WHERE_AND_ONLY 0x0080 /* Don't use indices for OR terms */ 2135 2135 #define WHERE_GROUPBY 0x0100 /* pOrderBy is really a GROUP BY */ 2136 2136 #define WHERE_DISTINCTBY 0x0200 /* pOrderby is really a DISTINCT clause */ 2137 2137 #define WHERE_WANT_DISTINCT 0x0400 /* All output needs to be distinct */ 2138 2138 #define WHERE_SORTBYGROUP 0x0800 /* Support sqlite3WhereIsSorted() */ 2139 -#define WHERE_OR_INDEX_OPEN 0x1000 /* OP_OpenRead for the OR index exists */ 2140 2139 2141 2140 /* Allowed return values from sqlite3WhereIsDistinct() 2142 2141 */ 2143 2142 #define WHERE_DISTINCT_NOOP 0 /* DISTINCT keyword not used */ 2144 2143 #define WHERE_DISTINCT_UNIQUE 1 /* No duplicates */ 2145 2144 #define WHERE_DISTINCT_ORDERED 2 /* All duplicates are adjacent */ 2146 2145 #define WHERE_DISTINCT_UNORDERED 3 /* Duplicates are scattered */
Changes to src/where.c.
3418 3418 int regReturn = ++pParse->nMem; /* Register used with OP_Gosub */ 3419 3419 int regRowset = 0; /* Register for RowSet object */ 3420 3420 int regRowid = 0; /* Register holding rowid */ 3421 3421 int iLoopBody = sqlite3VdbeMakeLabel(v); /* Start of loop body */ 3422 3422 int iRetInit; /* Address of regReturn init */ 3423 3423 int untestedTerms = 0; /* Some terms not completely tested */ 3424 3424 int ii; /* Loop counter */ 3425 - int subWctrlFlags; /* wctrlFlags for sub-queries */ 3426 3425 Expr *pAndExpr = 0; /* An ".. AND (...)" expression */ 3427 3426 Table *pTab = pTabItem->pTab; 3428 3427 3429 3428 pTerm = pLoop->aLTerm[0]; 3430 3429 assert( pTerm!=0 ); 3431 3430 assert( pTerm->eOperator & WO_OR ); 3432 3431 assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); ................................................................................ 3514 3513 } 3515 3514 } 3516 3515 3517 3516 /* Run a separate WHERE clause for each term of the OR clause. After 3518 3517 ** eliminating duplicates from other WHERE clauses, the action for each 3519 3518 ** sub-WHERE clause is to to invoke the main loop body as a subroutine. 3520 3519 */ 3521 - subWctrlFlags = WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY | 3522 - WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY; 3523 3520 for(ii=0; ii<pOrWc->nTerm; ii++){ 3524 3521 WhereTerm *pOrTerm = &pOrWc->a[ii]; 3525 3522 if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){ 3526 3523 WhereInfo *pSubWInfo; /* Info for single OR-term scan */ 3527 3524 Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */ 3528 3525 int j1 = 0; /* Address of jump operation */ 3529 3526 if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){ 3530 3527 pAndExpr->pLeft = pOrExpr; 3531 3528 pOrExpr = pAndExpr; 3532 3529 } 3533 3530 /* Loop through table entries that match term pOrTerm. */ 3534 3531 pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0, 3535 - subWctrlFlags, iCovCur); 3532 + WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY | 3533 + WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY, iCovCur); 3536 3534 assert( pSubWInfo || pParse->nErr || db->mallocFailed ); 3537 3535 if( pSubWInfo ){ 3538 3536 WhereLoop *pSubLoop; 3539 3537 explainOneScan( 3540 3538 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 3541 3539 ); 3542 3540 /* This is the sub-WHERE clause body. First skip over ................................................................................ 3619 3617 assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 ); 3620 3618 if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0 3621 3619 && (ii==0 || pSubLoop->u.btree.pIndex==pCov) 3622 3620 && (HasRowid(pTab) || !IsPrimaryKeyIndex(pSubLoop->u.btree.pIndex)) 3623 3621 ){ 3624 3622 assert( pSubWInfo->a[0].iIdxCur==iCovCur ); 3625 3623 pCov = pSubLoop->u.btree.pIndex; 3626 - subWctrlFlags |= WHERE_OR_INDEX_OPEN; 3627 3624 }else{ 3628 3625 pCov = 0; 3629 3626 } 3630 3627 3631 3628 /* Finish the loop through table entries that match term pOrTerm. */ 3632 3629 sqlite3WhereEnd(pSubWInfo); 3633 3630 } ................................................................................ 6218 6215 iIndexCur++; 6219 6216 pJ = pJ->pNext; 6220 6217 } 6221 6218 op = OP_OpenWrite; 6222 6219 pWInfo->aiCurOnePass[1] = iIndexCur; 6223 6220 }else if( iIdxCur && (wctrlFlags & WHERE_ONETABLE_ONLY)!=0 ){ 6224 6221 iIndexCur = iIdxCur; 6225 - if( (wctrlFlags & WHERE_OR_INDEX_OPEN)!=0 ){ 6226 - /* For 2nd and subsequent subqueries for processing OR terms, 6227 - ** try to reuse the previous OP_OpenRead, if there is one. The 6228 - ** WHERE_OR_INDEX_OPEN bit will only be set if there is a prior 6229 - ** OP_OpenRead opcode on cursor iIndexCur, so the while() loop 6230 - ** below is guaranteed to terminate. 6231 - */ 6232 - VdbeOp *pOp = sqlite3VdbeGetOp(v, -1); 6233 - while( pOp->opcode!=OP_OpenRead || pOp->p1!=iIndexCur ) pOp--; 6234 - assert( pOp->p3==iDb ); 6235 - if( pOp->p2==pIx->tnum ) op = 0; 6236 - } 6237 6222 }else{ 6238 6223 iIndexCur = pParse->nTab++; 6239 6224 } 6240 6225 pLevel->iIdxCur = iIndexCur; 6241 6226 assert( pIx->pSchema==pTab->pSchema ); 6242 6227 assert( iIndexCur>=0 ); 6243 6228 if( op ){
Changes to test/where2.test.
747 747 EXPLAIN QUERY PLAN 748 748 SELECT a.x, b.x 749 749 FROM t12 AS a JOIN t12 AS b ON a.y=b.x 750 750 WHERE (b.x=$abc OR b.y=$abc); 751 751 } {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/} 752 752 } 753 753 754 +# Verify that all necessary OP_OpenRead opcodes occur in the OR optimization. 755 +# 756 +do_execsql_test where2-13.1 { 757 + CREATE TABLE t13(a,b); 758 + CREATE INDEX t13a ON t13(a); 759 + INSERT INTO t13 VALUES(4,5); 760 + SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4; 761 +} {4 5} 754 762 755 763 finish_test