Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Continuing improvements to the multi-index OR-clause optimizer. Added a few simple test cases. (CVS 6062) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
55d4f493e7df8515574a75caec9967d6 |
User & Date: | drh 2008-12-23 23:56:22.000 |
Context
2008-12-24
| ||
09:30 | Fix a couple of problems in test file test_journal.c that were causing segfaults when running all.test. (CVS 6063) (check-in: 416c9efb49 user: danielk1977 tags: trunk) | |
2008-12-23
| ||
23:56 | Continuing improvements to the multi-index OR-clause optimizer. Added a few simple test cases. (CVS 6062) (check-in: 55d4f493e7 user: drh tags: trunk) | |
19:15 | Fix a problem with the savepoint code and in-memory journals. (CVS 6061) (check-in: 26ceebf38e user: danielk1977 tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** in order to generate code for DELETE FROM statements. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** in order to generate code for DELETE FROM statements. ** ** $Id: delete.c,v 1.191 2008/12/23 23:56:22 drh Exp $ */ #include "sqliteInt.h" /* ** Look up every table that is named in pSrc. If any table is not found, ** add an error message to pParse->zErrMsg and return NULL. If all tables ** are found, return a pointer to the last table. |
︙ | ︙ | |||
388 389 390 391 392 393 394 | /* The usual case: There is a WHERE clause so we have to scan through ** the table and pick which records to delete. */ { int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int iRowSet = ++pParse->nMem; /* Register for rowset of rows to delete */ | | > | > < < < < < < < < | 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 | /* The usual case: There is a WHERE clause so we have to scan through ** the table and pick which records to delete. */ { int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int iRowSet = ++pParse->nMem; /* Register for rowset of rows to delete */ /* Collect rowids of every row to be deleted. */ sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, WHERE_FILL_ROWSET, iRowSet); if( pWInfo==0 ) goto delete_from_cleanup; if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); } sqlite3WhereEnd(pWInfo); /* Open the pseudo-table used to store OLD if there are triggers. */ if( triggers_exist ){ sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pTab->nCol); sqlite3VdbeAddOp1(v, OP_OpenPseudo, oldIdx); |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.495 2008/12/23 23:56:22 drh Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
3701 3702 3703 3704 3705 3706 3707 | } /* Aggregate and non-aggregate queries are handled differently */ if( !isAgg && pGroupBy==0 ){ /* This case is for non-aggregate queries ** Begin the database scan */ | | | 3701 3702 3703 3704 3705 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 | } /* Aggregate and non-aggregate queries are handled differently */ if( !isAgg && pGroupBy==0 ){ /* This case is for non-aggregate queries ** Begin the database scan */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0, 0); if( pWInfo==0 ) goto select_end; /* If sorting index that was created by a prior OP_OpenEphemeral ** instruction ended up not being needed, then change the OP_OpenEphemeral ** into an OP_Noop. */ if( addrSortIndex>=0 && pOrderBy==0 ){ |
︙ | ︙ | |||
3822 3823 3824 3825 3826 3827 3828 | /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); | | | 3822 3823 3824 3825 3826 3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 | /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0); if( pWInfo==0 ) goto select_end; if( pGroupBy==0 ){ /* The optimizer is able to deliver rows in group by order so ** we do not have to sort. The OP_OpenEphemeral table will be ** cancelled later because we still need to use the pKeyInfo */ pGroupBy = p->pGroupBy; |
︙ | ︙ | |||
4020 4021 4022 4023 4024 4025 4026 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); | | | 4020 4021 4022 4023 4024 4025 4026 4027 4028 4029 4030 4031 4032 4033 4034 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax, flag, 0); if( pWInfo==0 ){ sqlite3ExprListDelete(db, pDel); goto select_end; } updateAccumulator(pParse, &sAggInfo); if( !pMinMax && flag ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.815 2008/12/23 23:56:22 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* ** Include the configuration header output by 'configure' if we're using the ** autoconf-based build |
︙ | ︙ | |||
1591 1592 1593 1594 1595 1596 1597 | */ sqlite3_index_info *pIdxInfo; /* Index info for n-th source table */ }; /* ** Flags appropriate for the wctrlFlags parameter of sqlite3WhereBegin(). */ | | | | | > > < | 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 | */ sqlite3_index_info *pIdxInfo; /* Index info for n-th source table */ }; /* ** Flags appropriate for the wctrlFlags parameter of sqlite3WhereBegin(). */ #define WHERE_ORDERBY_NORMAL 0x000 /* No-op */ #define WHERE_ORDERBY_MIN 0x001 /* ORDER BY processing for min() func */ #define WHERE_ORDERBY_MAX 0x002 /* ORDER BY processing for max() func */ #define WHERE_ONEPASS_DESIRED 0x004 /* Want to do one-pass UPDATE/DELETE */ #define WHERE_FILL_ROWSET 0x008 /* Save results in a RowSet object */ /* ** The WHERE clause processing routine has two halves. The ** first part does the start of the WHERE loop and the second ** half does the tail of the WHERE loop. An instance of ** this structure is returned by the first half and passed ** into the second half to give some continuity. */ struct WhereInfo { Parse *pParse; /* Parsing and code generating context */ u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE or DELETE */ int regRowSet; /* Store rowids in this rowset if >=0 */ SrcList *pTabList; /* List of tables in the join */ 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 nLevel; /* Number of nested loop */ struct WhereClause *pWC; /* Decomposition of the WHERE clause */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; /* ** A NameContext defines a context in which to resolve table and column ** names. The context consists of a list of tables (the pSrcList) field and ** a list of named expression (pEList). The named expression list may |
︙ | ︙ | |||
2256 2257 2258 2259 2260 2261 2262 | int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); | | | 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 | int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u8, int); void sqlite3WhereEnd(WhereInfo*); int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, int); void sqlite3ExprCodeMove(Parse*, int, int, int); void sqlite3ExprCodeCopy(Parse*, int, int, int); void sqlite3ExprClearColumnCache(Parse*, int); void sqlite3ExprCacheAffinityChange(Parse*, int, int); int sqlite3ExprWritableRegister(Parse*,int,int); |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle UPDATE statements. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle UPDATE statements. ** ** $Id: update.c,v 1.191 2008/12/23 23:56:22 drh Exp $ */ #include "sqliteInt.h" #ifndef SQLITE_OMIT_VIRTUALTABLE /* Forward declaration */ static void updateVirtualTable( Parse *pParse, /* The parsing context */ |
︙ | ︙ | |||
342 343 344 345 346 347 348 | goto update_cleanup; } /* Begin the database scan */ sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, | | | 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 | goto update_cleanup; } /* Begin the database scan */ sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, WHERE_ONEPASS_DESIRED, 0); if( pWInfo==0 ) goto update_cleanup; okOnePass = pWInfo->okOnePass; /* Remember the rowid of every item to be updated. */ sqlite3VdbeAddOp2(v, IsVirtual(pTab)?OP_VRowid:OP_Rowid, iCur, regOldRowid); if( !okOnePass ){ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.343 2008/12/23 23:56:22 drh Exp $ */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) |
︙ | ︙ | |||
1810 1811 1812 1813 1814 1815 1816 | if( cost<pCost->rCost ){ pCost->rCost = cost; pCost->nRow = nRow; pCost->plan.wsFlags = wsFlags; } } | | | 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 | if( cost<pCost->rCost ){ pCost->rCost = cost; pCost->nRow = nRow; pCost->plan.wsFlags = wsFlags; } } #ifndef SQLITE_OMIT_OR_OPTIMIZATION /* Search for an OR-clause that can be used to look up the table. */ maskSrc = getMask(pWC->pMaskSet, iCur); for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ WhereClause tempWC; tempWC = *pWC; tempWC.nSlot = 1; |
︙ | ︙ | |||
1846 1847 1848 1849 1850 1851 1852 | pCost->rCost = rTotal; pCost->nRow = nRow; pCost->plan.wsFlags = WHERE_MULTI_OR; pCost->plan.u.pTerm = pTerm; } } } | | | 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 | pCost->rCost = rTotal; pCost->nRow = nRow; pCost->plan.wsFlags = WHERE_MULTI_OR; pCost->plan.u.pTerm = pTerm; } } } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ /* If the pSrc table is the right table of a LEFT JOIN then we may not ** use an index to satisfy IS NULL constraints on that table. This is ** because columns might end up being NULL if the table does not match - ** a circumstance which the index cannot help us discover. Ticket #2177. */ if( (pSrc->jointype & JT_LEFT)!=0 ){ |
︙ | ︙ | |||
2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 | testcase( pTerm->eOperator & WO_IN ); if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk); } } return regBase; } /* ** 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 */ | > > > > > > > > > > > > > > > > > > > | 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 | testcase( pTerm->eOperator & WO_IN ); if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk); } } return regBase; } /* ** Return TRUE if the WhereClause pWC contains no terms that ** are not virtual and which have not been coded. ** ** To put it another way, return TRUE if no additional WHERE clauses ** tests are required in order to establish that the current row ** should go to output and return FALSE if there are some terms of ** the WHERE clause that need to be validated before outputing the row. */ static int whereRowReadyForOutput(WhereClause *pWC){ WhereTerm *pTerm; int j; for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ if( (pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED))==0 ) return 0; } return 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 */ |
︙ | ︙ | |||
2191 2192 2193 2194 2195 2196 2197 | int bRev; /* True if we need to scan in reverse order */ WhereLevel *pLevel; /* The where level to be coded */ WhereClause *pWC; /* Decomposition of the entire WHERE clause */ WhereTerm *pTerm; /* A WHERE clause term */ Parse *pParse; /* Parsing context */ Vdbe *v; /* The prepared stmt under constructions */ struct SrcList_item *pTabItem; /* FROM clause term being coded */ | | | > > > > | 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 | int bRev; /* True if we need to scan in reverse order */ WhereLevel *pLevel; /* The where level to be coded */ WhereClause *pWC; /* Decomposition of the entire WHERE clause */ WhereTerm *pTerm; /* A WHERE clause term */ Parse *pParse; /* Parsing context */ Vdbe *v; /* The prepared stmt under constructions */ struct SrcList_item *pTabItem; /* FROM clause term being coded */ int addrBrk; /* Jump here to break out of the loop */ int addrCont; /* Jump here to continue with next cycle */ int regRowSet; /* Write rowids to this RowSet if non-negative */ int codeRowSetEarly; /* True if index fully constrains the search */ pParse = pWInfo->pParse; v = pParse->pVdbe; pWC = pWInfo->pWC; pLevel = &pWInfo->a[iLevel]; pTabItem = &pWInfo->pTabList->a[pLevel->iFrom]; iCur = pTabItem->iCursor; bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0; omitTable = (pLevel->plan.wsFlags & WHERE_IDX_ONLY)!=0; regRowSet = pWInfo->regRowSet; codeRowSetEarly = 0; /* Create labels for the "break" and "continue" instructions ** for the current loop. Jump to addrBrk to break out of a loop. ** Jump to cont to go immediately to the next iteration of the ** loop. ** ** When there is an IN operator, we also have a "addrNxt" label that |
︙ | ︙ | |||
2259 2260 2261 2262 2263 2264 2265 | } assert( pParse->disableColCache ); pParse->disableColCache--; sqlite3VdbeAddOp2(v, OP_Integer, pVtabIdx->idxNum, iReg); sqlite3VdbeAddOp2(v, OP_Integer, j-1, iReg+1); sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrBrk, iReg, pVtabIdx->idxStr, pVtabIdx->needToFreeIdxStr ? P4_MPRINTF : P4_STATIC); | < > > > > > > > > > > | 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 | } assert( pParse->disableColCache ); pParse->disableColCache--; sqlite3VdbeAddOp2(v, OP_Integer, pVtabIdx->idxNum, iReg); sqlite3VdbeAddOp2(v, OP_Integer, j-1, iReg+1); sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrBrk, iReg, pVtabIdx->idxStr, pVtabIdx->needToFreeIdxStr ? P4_MPRINTF : P4_STATIC); pVtabIdx->needToFreeIdxStr = 0; for(j=0; j<nConstraint; j++){ if( aUsage[j].omit ){ int iTerm = aConstraint[j].iTermOffset; disableTerm(pLevel, &pWC->a[iTerm]); } } pLevel->op = OP_VNext; pLevel->p1 = iCur; pLevel->p2 = sqlite3VdbeCurrentAddr(v); codeRowSetEarly = regRowSet>=0 ? whereRowReadyForOutput(pWC) : 0; if( codeRowSetEarly ){ sqlite3VdbeAddOp2(v, OP_VRowid, iCur, iReg); sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, iReg); } sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2); }else #endif /* SQLITE_OMIT_VIRTUALTABLE */ if( pLevel->plan.wsFlags & WHERE_ROWID_EQ ){ /* Case 1: We can directly reference a single row using an ** equality comparison against the ROWID field. Or ** we reference multiple rows using a "rowid IN (...)" ** construct. */ int r1; int rtmp = sqlite3GetTempReg(pParse); pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0); assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( pTerm->leftCursor==iCur ); assert( omitTable==0 ); r1 = codeEqualityTerm(pParse, pTerm, pLevel, rtmp); addrNxt = pLevel->addrNxt; sqlite3VdbeAddOp2(v, OP_MustBeInt, r1, addrNxt); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, r1); codeRowSetEarly = (pWC->nTerm==1 && regRowSet>=0) ?1:0; if( codeRowSetEarly ){ sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, r1); } sqlite3ReleaseTempReg(pParse, rtmp); VdbeComment((v, "pk")); pLevel->op = OP_Noop; }else if( pLevel->plan.wsFlags & WHERE_ROWID_RANGE ){ /* Case 2: We have an inequality comparison against the ROWID field. */ int testOp = OP_Noop; |
︙ | ︙ | |||
2356 2357 2358 2359 2360 2361 2362 | } disableTerm(pLevel, pEnd); } start = sqlite3VdbeCurrentAddr(v); pLevel->op = bRev ? OP_Prev : OP_Next; pLevel->p1 = iCur; pLevel->p2 = start; | > | > | | > > > > | 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 | } disableTerm(pLevel, pEnd); } start = sqlite3VdbeCurrentAddr(v); pLevel->op = bRev ? OP_Prev : OP_Next; pLevel->p1 = iCur; pLevel->p2 = start; codeRowSetEarly = regRowSet>=0 ? whereRowReadyForOutput(pWC) : 0; if( codeRowSetEarly || testOp!=OP_Noop ){ int r1 = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_Rowid, iCur, r1); if( testOp!=OP_Noop ){ sqlite3VdbeAddOp3(v, testOp, memEndValue, addrBrk, r1); sqlite3VdbeChangeP5(v, SQLITE_AFF_NUMERIC | SQLITE_JUMPIFNULL); } if( codeRowSetEarly ){ sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, r1); } sqlite3ReleaseTempReg(pParse, r1); } }else if( pLevel->plan.wsFlags & (WHERE_COLUMN_RANGE|WHERE_COLUMN_EQ) ){ /* Case 3: A scan using an index. ** ** The WHERE clause may contain zero or more equality ** terms ("==" or "IN" operators) that refer to the N |
︙ | ︙ | |||
2543 2544 2545 2546 2547 2548 2549 | testcase( pLevel->plan.wsFlags & WHERE_TOP_LIMIT ); if( pLevel->plan.wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT) ){ sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, nEq, r1); sqlite3VdbeAddOp2(v, OP_IsNull, r1, addrCont); } /* Seek the table cursor, if required */ | > > > | > > > | > < < | | 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 | testcase( pLevel->plan.wsFlags & WHERE_TOP_LIMIT ); if( pLevel->plan.wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT) ){ sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, nEq, r1); sqlite3VdbeAddOp2(v, OP_IsNull, r1, addrCont); } /* Seek the table cursor, if required */ disableTerm(pLevel, pRangeStart); disableTerm(pLevel, pRangeEnd); codeRowSetEarly = regRowSet>=0 ? whereRowReadyForOutput(pWC) : 0; if( !omitTable || codeRowSetEarly ){ sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, r1); if( codeRowSetEarly ){ sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, r1); }else{ sqlite3VdbeAddOp2(v, OP_Seek, iCur, r1); /* Deferred seek */ } } sqlite3ReleaseTempReg(pParse, r1); /* Record the instruction used to terminate the loop. Disable ** WHERE clause terms made redundant by the index range scan. */ pLevel->op = bRev ? OP_Prev : OP_Next; pLevel->p1 = iIdxCur; }else #ifndef SQLITE_OMIT_OR_OPTIMIZATION if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){ /* Case 4: Two or more separately indexed terms connected by OR ** ** Example: ** ** CREATE TABLE t1(a,b,c,d); ** CREATE INDEX i1 ON t1(a); |
︙ | ︙ | |||
2587 2588 2589 2590 2591 2592 2593 | ** Seek i, 2 ** ** The bottom of the loop looks like this: ** ** Goto 0, A ** B: */ | | > | > | > | | > | > < < > > | | | | > | | > > | > | 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 | ** Seek i, 2 ** ** The bottom of the loop looks like this: ** ** Goto 0, A ** B: */ int regOrRowset; /* Register holding the RowSet object */ int regNextRowid; /* Register holding next rowid */ WhereTerm *pTerm; /* The complete OR-clause */ WhereClause *pOrWc; /* The OR-clause broken out into subterms */ WhereTerm *pOrTerm; /* A single subterm within the OR-clause */ SrcList oneTab; /* Shortened table list */ pTerm = pLevel->plan.u.pTerm; assert( pTerm!=0 ); assert( pTerm->eOperator==WO_OR ); assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); pOrWc = &pTerm->u.pOrInfo->wc; codeRowSetEarly = (regRowSet>=0 && pWC->nTerm==1) ?1:0; if( codeRowSetEarly ){ regOrRowset = regRowSet; }else{ regOrRowset = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_Null, 0, regOrRowset); } oneTab.nSrc = 1; oneTab.nAlloc = 1; oneTab.a[0] = *pTabItem; for(j=0, pOrTerm=pOrWc->a; j<pOrWc->nTerm; j++, pOrTerm++){ WhereInfo *pSubWInfo; if( pOrTerm->leftCursor!=iCur ) continue; pSubWInfo = sqlite3WhereBegin(pParse, &oneTab, pOrTerm->pExpr, 0, WHERE_FILL_ROWSET, regOrRowset); if( pSubWInfo ){ pSubWInfo->a[0].plan.wsFlags |= WHERE_IDX_ONLY; sqlite3WhereEnd(pSubWInfo); } } sqlite3VdbeResolveLabel(v, addrCont); if( !codeRowSetEarly ){ regNextRowid = sqlite3GetTempReg(pParse); addrCont = sqlite3VdbeAddOp3(v, OP_RowSetRead, regOrRowset,addrBrk,regNextRowid); sqlite3VdbeAddOp2(v, OP_Seek, iCur, regNextRowid); sqlite3ReleaseTempReg(pParse, regNextRowid); /* sqlite3ReleaseTempReg(pParse, regOrRowset); // Preserve the RowSet */ pLevel->op = OP_Goto; pLevel->p2 = addrCont; } disableTerm(pLevel, pTerm); }else #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ { /* Case 5: There is no usable index. We must do a complete ** scan of the entire table. */ assert( omitTable==0 ); assert( bRev==0 ); pLevel->op = OP_Next; pLevel->p1 = iCur; pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, OP_Rewind, iCur, addrBrk); pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; codeRowSetEarly = 0; } notReady &= ~getMask(pWC->pMaskSet, iCur); /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. */ k = 0; |
︙ | ︙ | |||
2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 | if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; if( (pTerm->prereqAll & notReady)!=0 ) continue; assert( pTerm->pExpr ); sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL); pTerm->wtFlags |= TERM_CODED; } } return notReady; } #if defined(SQLITE_TEST) /* ** The following variable holds a text description of query plan generated ** by the most recent call to sqlite3WhereBegin(). Each call to WhereBegin | > > > > > > > > > > > > > > > > > > > | 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 | if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; if( (pTerm->prereqAll & notReady)!=0 ) continue; assert( pTerm->pExpr ); sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL); pTerm->wtFlags |= TERM_CODED; } } /* ** If it was requested to store the results in a rowset and that has ** not already been do, then do so now. */ if( regRowSet>=0 && !codeRowSetEarly ){ int r1 = sqlite3GetTempReg(pParse); #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ sqlite3VdbeAddOp2(v, OP_VRowid, iCur, r1); }else #endif { sqlite3VdbeAddOp2(v, OP_Rowid, iCur, r1); } sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, r1); sqlite3ReleaseTempReg(pParse, r1); } return notReady; } #if defined(SQLITE_TEST) /* ** The following variable holds a text description of query plan generated ** by the most recent call to sqlite3WhereBegin(). Each call to WhereBegin |
︙ | ︙ | |||
2809 2810 2811 2812 2813 2814 2815 | ** output order, then the *ppOrderBy is unchanged. */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */ | | > | 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 | ** output order, then the *ppOrderBy is unchanged. */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */ u8 wctrlFlags, /* One of the WHERE_* flags defined in sqliteInt.h */ int regRowSet /* Register hold RowSet if WHERE_FILL_ROWSET is set */ ){ int i; /* Loop counter */ WhereInfo *pWInfo; /* Will become the return value of this function */ Vdbe *v = pParse->pVdbe; /* The virtual database engine */ Bitmask notReady; /* Cursors that are not yet positioned */ WhereMaskSet *pMaskSet; /* The expression mask set */ //WhereClause wc; /* The WHERE clause is divided into these terms */ |
︙ | ︙ | |||
2854 2855 2856 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 | if( db->mallocFailed ){ goto whereBeginError; } pWInfo->nLevel = pTabList->nSrc; pWInfo->pParse = pParse; pWInfo->pTabList = pTabList; pWInfo->iBreak = sqlite3VdbeMakeLabel(v); pWInfo->pWC = pWC = (WhereClause*)&pWInfo->a[pWInfo->nLevel]; pMaskSet = (WhereMaskSet*)&pWC[1]; /* Split the WHERE clause into separate subexpressions where each ** subexpression is separated by an AND operator. */ initMaskSet(pMaskSet); | > | 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 | if( db->mallocFailed ){ goto whereBeginError; } pWInfo->nLevel = pTabList->nSrc; pWInfo->pParse = pParse; pWInfo->pTabList = pTabList; pWInfo->iBreak = sqlite3VdbeMakeLabel(v); pWInfo->regRowSet = (wctrlFlags & WHERE_FILL_ROWSET) ? regRowSet : -1; pWInfo->pWC = pWC = (WhereClause*)&pWInfo->a[pWInfo->nLevel]; pMaskSet = (WhereMaskSet*)&pWC[1]; /* Split the WHERE clause into separate subexpressions where each ** subexpression is separated by an AND operator. */ initMaskSet(pMaskSet); |
︙ | ︙ |
Added test/where7.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 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 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | # 2008 December 23 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. # # $Id: where7.test,v 1.1 2008/12/23 23:56:22 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !or_opt { finish_test return } # Evaluate SQL. Return the result set followed by the # and the number of full-scan steps. # proc count_steps {sql} { set r [db eval $sql] lappend r scan [db status step] } # Build some test data # do_test where7-1.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); INSERT INTO t1 VALUES(1,2,3,4); INSERT INTO t1 VALUES(2,3,4,5); INSERT INTO t1 VALUES(3,4,6,8); INSERT INTO t1 VALUES(4,5,10,15); INSERT INTO t1 VALUES(5,10,100,1000); CREATE INDEX t1b ON t1(b); CREATE INDEX t1c ON t1(c); SELECT * FROM t1; } } {1 2 3 4 2 3 4 5 3 4 6 8 4 5 10 15 5 10 100 1000} do_test where7-1.2 { count_steps { SELECT a FROM t1 WHERE b=3 OR c=6 } } {2 3 scan 0} do_test where7-1.3 { count_steps { SELECT a FROM t1 WHERE b=3 OR +c=6 } } {2 3 scan 4} do_test where7-1.4 { count_steps { SELECT a FROM t1 WHERE +b=3 OR c=6 } } {2 3 scan 4} do_test where7-1.5 { count_steps { SELECT a FROM t1 WHERE 3=b OR c=6 } } {2 3 scan 0} do_test where7-1.6 { count_steps { SELECT a FROM t1 WHERE (3=b OR c=6) AND +a>0 } } {2 3 scan 0} do_test where7-1.7 { count_steps { SELECT a FROM t1 WHERE (b=3 OR c>10) } } {2 5 scan 0} do_test where7-1.8 { count_steps { SELECT a FROM t1 WHERE (b=3 OR c>=10) } } {2 4 5 scan 0} do_test where7-1.9 { count_steps { SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4) } } {2 4 5 scan 0} do_test where7-1.10 { count_steps { SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4 OR b>10) } } {2 4 5 scan 0} finish_test |