Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Change the EQP output for the min/max optimization from "SCAN" to "SEARCH". Other changes in where.c in support of full branch coverage testing. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d52b593978aa1776af7aeb957c4f8df0 |
User & Date: | drh 2010-11-15 21:50:20.000 |
Context
2010-11-16
| ||
02:49 | Use the estimated number of rows computed for subqueries in the cost computations for outer queries. (check-in: 56bbc53924 user: drh tags: trunk) | |
2010-11-15
| ||
21:50 | Change the EQP output for the min/max optimization from "SCAN" to "SEARCH". Other changes in where.c in support of full branch coverage testing. (check-in: d52b593978 user: drh tags: trunk) | |
16:29 | Fix the EQP logic so that it correctly reports OOM errors while formatting "detail" text. (check-in: 136c2ac24e user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
234 235 236 237 238 239 240 241 242 243 244 245 246 247 | #define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */ #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ #define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */ #define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */ #define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */ #define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */ #define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */ #define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x02000000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */ #define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */ #define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */ #define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */ | > | 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | #define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */ #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ #define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */ #define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */ #define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */ #define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */ #define WHERE_BOTH_LIMIT 0x00300000 /* Both x>EXPR and x<EXPR */ #define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */ #define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x02000000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */ #define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */ #define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */ #define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */ |
︙ | ︙ | |||
3222 3223 3224 3225 3226 3227 3228 | char *zMsg; /* Text to add to EQP output */ sqlite3_int64 nRow; /* Expected number of rows visited by scan */ int iId = pParse->iSelectId; /* Select id (left-most output column) */ int isSearch; /* True for a SEARCH. False for SCAN. */ if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return; | | > > | 3223 3224 3225 3226 3227 3228 3229 3230 3231 3232 3233 3234 3235 3236 3237 3238 3239 | char *zMsg; /* Text to add to EQP output */ sqlite3_int64 nRow; /* Expected number of rows visited by scan */ int iId = pParse->iSelectId; /* Select id (left-most output column) */ int isSearch; /* True for a SEARCH. False for SCAN. */ if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return; isSearch = (pLevel->plan.nEq>0) || (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX)); zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN"); if( pItem->pSelect ){ zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId); }else{ zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName); } |
︙ | ︙ | |||
3249 3250 3251 3252 3253 3254 3255 | ); sqlite3DbFree(db, zWhere); }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); if( flags&WHERE_ROWID_EQ ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); | | > | 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 | ); sqlite3DbFree(db, zWhere); }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); if( flags&WHERE_ROWID_EQ ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg); }else if( flags&WHERE_BTM_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg); }else if( flags&WHERE_TOP_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg); } } #ifndef SQLITE_OMIT_VIRTUALTABLE else if( (flags & WHERE_VIRTUALTABLE)!=0 ){ sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, pVtabIdx->idxNum, pVtabIdx->idxStr); } #endif if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){ testcase( wctrlFlags & WHERE_ORDERBY_MIN ); nRow = 1; }else{ nRow = (sqlite3_int64)pLevel->plan.nRow; } zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow); sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC); } |
︙ | ︙ | |||
3685 3686 3687 3688 3689 3690 3691 | /* If there are inequality constraints, check that the value ** of the table column that the inequality contrains is not NULL. ** If it is, jump to the next iteration of the loop. */ r1 = sqlite3GetTempReg(pParse); testcase( pLevel->plan.wsFlags & WHERE_BTM_LIMIT ); testcase( pLevel->plan.wsFlags & WHERE_TOP_LIMIT ); | | | 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 3700 3701 3702 3703 | /* If there are inequality constraints, check that the value ** of the table column that the inequality contrains is not NULL. ** If it is, jump to the next iteration of the loop. */ r1 = sqlite3GetTempReg(pParse); testcase( pLevel->plan.wsFlags & WHERE_BTM_LIMIT ); testcase( pLevel->plan.wsFlags & WHERE_TOP_LIMIT ); if( (pLevel->plan.wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 ){ sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, nEq, r1); sqlite3VdbeAddOp2(v, OP_IsNull, r1, addrCont); } sqlite3ReleaseTempReg(pParse, r1); /* Seek the table cursor, if required */ disableTerm(pLevel, pRangeStart); |
︙ | ︙ |
Changes to test/eqp.test.
︙ | ︙ | |||
110 111 112 113 114 115 116 | } det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 0 1 0 {SCAN TABLE t1 (~1000000 rows)} } det 2.3.1 "SELECT max(x) FROM t2" { | | | | 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | } det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 0 1 0 {SCAN TABLE t1 (~1000000 rows)} } det 2.3.1 "SELECT max(x) FROM t2" { 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} } det 2.3.2 "SELECT min(x) FROM t2" { 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} } det 2.3.3 "SELECT min(x), max(x) FROM t2" { 0 0 0 {SCAN TABLE t2 (~1000000 rows)} } det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} |
︙ | ︙ | |||
490 491 492 493 494 495 496 | 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) }] finish_test | < | 490 491 492 493 494 495 496 | 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) }] finish_test |