Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Extend [52e73eec] so that the IS optimization may be used on primary keys with more than 3 columns. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | ota-update |
Files: | files | file ages | folders |
SHA1: |
4e8796af7d40d6ca423e07c68877035e |
User & Date: | dan 2015-03-25 15:23:00.648 |
Context
2015-04-08
| ||
14:01 | Merge all changes for version 3.8.9 into the ota-update branch. (check-in: ec9d907a57 user: drh tags: ota-update) | |
2015-03-25
| ||
15:23 | Extend [52e73eec] so that the IS optimization may be used on primary keys with more than 3 columns. (check-in: 4e8796af7d user: dan tags: ota-update) | |
2015-03-24
| ||
18:21 | Merge the latest trunk changes into this branch. (check-in: 9d9b6c883b user: dan tags: ota-update) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 | if( ALWAYS(pWInfo) ){ whereClauseClear(&pWInfo->sWC); while( pWInfo->pLoops ){ WhereLoop *p = pWInfo->pLoops; pWInfo->pLoops = p->pNextLoop; whereLoopDelete(db, p); } sqlite3DbFree(db, pWInfo); } } /* ** Return TRUE if all of the following are true: ** | > > > | 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322 | if( ALWAYS(pWInfo) ){ whereClauseClear(&pWInfo->sWC); while( pWInfo->pLoops ){ WhereLoop *p = pWInfo->pLoops; pWInfo->pLoops = p->pNextLoop; whereLoopDelete(db, p); } if( pWInfo->bShortcut ){ whereLoopClear(db, pWInfo->a[0].pWLoop); } sqlite3DbFree(db, pWInfo); } } /* ** Return TRUE if all of the following are true: ** |
︙ | ︙ | |||
5556 5557 5558 5559 5560 5561 5562 | int nTabList = pWInfo->nLevel; int rc = SQLITE_OK; u8 priorJoinType = 0; WhereLoop *pNew; /* Loop over the tables in the join, from left to right */ pNew = pBuilder->pNew; | < | 5559 5560 5561 5562 5563 5564 5565 5566 5567 5568 5569 5570 5571 5572 | int nTabList = pWInfo->nLevel; int rc = SQLITE_OK; u8 priorJoinType = 0; WhereLoop *pNew; /* Loop over the tables in the join, from left to right */ pNew = pBuilder->pNew; for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){ pNew->iTab = iTab; pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor); if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){ mExtra = mPrior; } priorJoinType = pItem->jointype; |
︙ | ︙ | |||
6272 6273 6274 6275 6276 6277 6278 | ** general-purpose query planner, and thereby yield faster sqlite3_prepare() ** times for the common case. ** ** Return non-zero on success, if this query can be handled by this ** no-frills query planner. Return zero if this query needs the ** general-purpose query planner. */ | | | 6274 6275 6276 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 6287 6288 | ** general-purpose query planner, and thereby yield faster sqlite3_prepare() ** times for the common case. ** ** Return non-zero on success, if this query can be handled by this ** no-frills query planner. Return zero if this query needs the ** general-purpose query planner. */ static int whereShortCut(sqlite3 *db, WhereLoopBuilder *pBuilder){ WhereInfo *pWInfo; struct SrcList_item *pItem; WhereClause *pWC; WhereTerm *pTerm; WhereLoop *pLoop; int iCur; int j; |
︙ | ︙ | |||
6307 6308 6309 6310 6311 6312 6313 6314 6315 6316 6317 | pLoop->u.btree.nEq = 1; /* TUNING: Cost of a rowid lookup is 10 */ pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */ }else{ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ u32 mask = WO_EQ; assert( pLoop->aLTermSpace==pLoop->aLTerm ); if( !IsUniqueIndex(pIdx) || pIdx->pPartIdxWhere!=0 || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) ) continue; | > > > > < | 6309 6310 6311 6312 6313 6314 6315 6316 6317 6318 6319 6320 6321 6322 6323 6324 6325 6326 6327 6328 6329 6330 | pLoop->u.btree.nEq = 1; /* TUNING: Cost of a rowid lookup is 10 */ pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */ }else{ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ u32 mask = WO_EQ; assert( pLoop->aLTermSpace==pLoop->aLTerm ); if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ){ mask |= WO_IS; if( whereLoopResize(db, pLoop, pIdx->nKeyCol) ) return 1; }else if( !IsUniqueIndex(pIdx) || pIdx->pPartIdxWhere!=0 || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) ) continue; for(j=0; j<pIdx->nKeyCol; j++){ pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, mask, pIdx); if( pTerm==0 ) break; pLoop->aLTerm[j] = pTerm; } if( j!=pIdx->nKeyCol ) continue; pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED; |
︙ | ︙ | |||
6343 6344 6345 6346 6347 6348 6349 6350 6351 6352 6353 6354 6355 6356 | if( pWInfo->pOrderBy ) pWInfo->nOBSat = pWInfo->pOrderBy->nExpr; if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } #ifdef SQLITE_DEBUG pLoop->cId = '0'; #endif return 1; } return 0; } /* ** Generate the beginning of the loop used for WHERE clause processing. | > | 6348 6349 6350 6351 6352 6353 6354 6355 6356 6357 6358 6359 6360 6361 6362 | if( pWInfo->pOrderBy ) pWInfo->nOBSat = pWInfo->pOrderBy->nExpr; if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } #ifdef SQLITE_DEBUG pLoop->cId = '0'; #endif pWInfo->bShortcut = 1; return 1; } return 0; } /* ** Generate the beginning of the loop used for WHERE clause processing. |
︙ | ︙ | |||
6613 6614 6615 6616 6617 6618 6619 | int i; for(i=0; i<sWLB.pWC->nTerm; i++){ whereTermPrint(&sWLB.pWC->a[i], i); } } #endif | | | 6619 6620 6621 6622 6623 6624 6625 6626 6627 6628 6629 6630 6631 6632 6633 | int i; for(i=0; i<sWLB.pWC->nTerm; i++){ whereTermPrint(&sWLB.pWC->a[i], i); } } #endif if( nTabList!=1 || whereShortCut(db, &sWLB)==0 ){ rc = whereLoopAddAll(&sWLB); if( rc ) goto whereBeginError; /* Display all of the WhereLoop objects if wheretrace is enabled */ #ifdef WHERETRACE_ENABLED /* !=0 */ if( sqlite3WhereTrace ){ WhereLoop *p; |
︙ | ︙ |
Changes to src/whereInt.h.
︙ | ︙ | |||
408 409 410 411 412 413 414 415 416 417 418 419 420 421 | u16 wctrlFlags; /* Flags originally passed to sqlite3WhereBegin() */ i8 nOBSat; /* Number of ORDER BY terms satisfied by indices */ u8 sorted; /* True if really sorted (not just grouped) */ u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE/DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ u8 eDistinct; /* One of the WHERE_DISTINCT_* values below */ u8 nLevel; /* Number of nested loop */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ int aiCurOnePass[2]; /* OP_OpenWrite cursors for the ONEPASS opt */ WhereMaskSet sMaskSet; /* Map cursor numbers to bitmasks */ WhereClause sWC; /* Decomposition of the WHERE clause */ | > | 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 | u16 wctrlFlags; /* Flags originally passed to sqlite3WhereBegin() */ i8 nOBSat; /* Number of ORDER BY terms satisfied by indices */ u8 sorted; /* True if really sorted (not just grouped) */ u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE/DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ u8 eDistinct; /* One of the WHERE_DISTINCT_* values below */ u8 nLevel; /* Number of nested loop */ u8 bShortcut; /* Plan generated by whereShortCut() */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ int aiCurOnePass[2]; /* OP_OpenWrite cursors for the ONEPASS opt */ WhereMaskSet sMaskSet; /* Map cursor numbers to bitmasks */ WhereClause sWC; /* Decomposition of the WHERE clause */ |
︙ | ︙ |
Changes to test/whereK.test.
︙ | ︙ | |||
53 54 55 56 57 58 59 60 61 62 63 | } {/SCAN TABLE t1/} do_execsql_test 1.4.1 { SELECT v FROM t1 WHERE w IS 3 AND x = 3 } {C} do_execsql_test 1.4.2 { SELECT v FROM t1 WHERE w = '4' AND x IS 4 } {d} do_eqp_test 1.5.1 { SELECT b FROM t3 WHERE a IS ? } {/SCAN TABLE/} do_eqp_test 1.5.2 { SELECT b FROM t3 WHERE a = ? } {/SEARCH TABLE/} finish_test | > > > > > > > > > > > > > > | 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | } {/SCAN TABLE t1/} do_execsql_test 1.4.1 { SELECT v FROM t1 WHERE w IS 3 AND x = 3 } {C} do_execsql_test 1.4.2 { SELECT v FROM t1 WHERE w = '4' AND x IS 4 } {d} do_eqp_test 1.5.1 { SELECT b FROM t3 WHERE a IS ? } {/SCAN TABLE/} do_eqp_test 1.5.2 { SELECT b FROM t3 WHERE a = ? } {/SEARCH TABLE/} #------------------------------------------------------------------------- # Check that this works with PRIMARY KEY indexes that have large numbers # of columns. do_execsql_test 2.1.1 { CREATE TABLE x1(a, b, c, d, e, f, g, h, i, PRIMARY KEY(a, b, c, d, e, f, g) ) WITHOUT ROWID; } do_eqp_test 2.1.2 { SELECT i FROM x1 WHERE a IS ? AND b IS ? AND c IS ? AND d IS ? AND e IS ? AND f IS ? AND g IS ? } {/SEARCH TABLE/} finish_test |