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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | ota-update |
| Files: | files | file ages | folders |
| SHA1: |
4e8796af7d40d6ca423e07c68877035e |
| User & Date: | dan 2015-03-25 15:23:00 |
Context
|
2015-04-08
| ||
| 14:01 | Merge all changes for version 3.8.9 into the ota-update branch. check-in: ec9d907a 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: 4e8796af user: dan tags: ota-update | |
|
2015-03-24
| ||
| 18:21 | Merge the latest trunk changes into this branch. check-in: 9d9b6c88 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 .... 5556 5557 5558 5559 5560 5561 5562 5563 5564 5565 5566 5567 5568 5569 5570 .... 6272 6273 6274 6275 6276 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 .... 6307 6308 6309 6310 6311 6312 6313 6314 6315 6316 6317 6318 6319 6320 6321 6322 6323 6324 6325 .... 6343 6344 6345 6346 6347 6348 6349 6350 6351 6352 6353 6354 6355 6356 .... 6613 6614 6615 6616 6617 6618 6619 6620 6621 6622 6623 6624 6625 6626 6627 |
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:
**
................................................................................
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;
whereLoopInit(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;
................................................................................
** 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(WhereLoopBuilder *pBuilder){
WhereInfo *pWInfo;
struct SrcList_item *pItem;
WhereClause *pWC;
WhereTerm *pTerm;
WhereLoop *pLoop;
int iCur;
int j;
................................................................................
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;
if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ) mask |= WO_IS;
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;
................................................................................
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.
................................................................................
int i;
for(i=0; i<sWLB.pWC->nTerm; i++){
whereTermPrint(&sWLB.pWC->a[i], i);
}
}
#endif
if( nTabList!=1 || whereShortCut(&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;
|
> > > < | > > > > < > | |
4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322 .... 5559 5560 5561 5562 5563 5564 5565 5566 5567 5568 5569 5570 5571 5572 .... 6274 6275 6276 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 6287 6288 .... 6309 6310 6311 6312 6313 6314 6315 6316 6317 6318 6319 6320 6321 6322 6323 6324 6325 6326 6327 6328 6329 6330 .... 6348 6349 6350 6351 6352 6353 6354 6355 6356 6357 6358 6359 6360 6361 6362 .... 6619 6620 6621 6622 6623 6624 6625 6626 6627 6628 6629 6630 6631 6632 6633 |
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:
**
................................................................................
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;
................................................................................
** 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;
................................................................................
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;
................................................................................
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.
................................................................................
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
|