Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Use the NGQP plan for EXPLAIN QUERY PLAN output. This change causes 207 errors in veryquick.test, many of which are benign. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
| Files: | files | file ages | folders |
| SHA1: |
f783e8e6b10de44029c7c5f57e4648a4 |
| User & Date: | drh 2013-05-22 20:49:02 |
Context
|
2013-05-24
| ||
| 13:55 | Merge the latest trunk changes into the NGQP branch. (check-in: 7c8f992c user: drh tags: nextgen-query-plan-exp) | |
|
2013-05-22
| ||
| 20:49 | Use the NGQP plan for EXPLAIN QUERY PLAN output. This change causes 207 errors in veryquick.test, many of which are benign. (check-in: f783e8e6 user: drh tags: nextgen-query-plan-exp) | |
| 17:01 | Allow the rowid at the end of an index to be used in a constraint on that index. (check-in: 9bf0524d user: drh tags: nextgen-query-plan-exp) | |
Changes
Changes to src/where.c.
4164 4164 ** 4165 4165 ** "a=? AND b>?" 4166 4166 ** 4167 4167 ** The returned pointer points to memory obtained from sqlite3DbMalloc(). 4168 4168 ** It is the responsibility of the caller to free the buffer when it is 4169 4169 ** no longer required. 4170 4170 */ 4171 -static char *explainIndexRange(sqlite3 *db, WhereLevel *pLevel, Table *pTab){ 4172 - WherePlan *pPlan = &pLevel->plan; 4173 - Index *pIndex = pPlan->u.pIdx; 4174 - int nEq = pPlan->nEq; 4171 +static char *explainIndexRange(sqlite3 *db, WhereLoop *pLoop, Table *pTab){ 4172 + Index *pIndex = pLoop->u.btree.pIndex; 4173 + int nEq = pLoop->u.btree.nEq; 4175 4174 int i, j; 4176 4175 Column *aCol = pTab->aCol; 4177 4176 int *aiColumn = pIndex->aiColumn; 4178 4177 StrAccum txt; 4179 4178 4180 - if( nEq==0 && (pPlan->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){ 4179 + if( pIndex==0 ) return 0; 4180 + if( nEq==0 && (pLoop->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){ 4181 4181 return 0; 4182 4182 } 4183 4183 sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH); 4184 4184 txt.db = db; 4185 4185 sqlite3StrAccumAppend(&txt, " (", 2); 4186 4186 for(i=0; i<nEq; i++){ 4187 4187 explainAppendTerm(&txt, i, aCol[aiColumn[i]].zName, "="); 4188 4188 } 4189 4189 4190 4190 j = i; 4191 - if( pPlan->wsFlags&WHERE_BTM_LIMIT ){ 4191 + if( pLoop->wsFlags&WHERE_BTM_LIMIT ){ 4192 4192 char *z = (j==pIndex->nColumn ) ? "rowid" : aCol[aiColumn[j]].zName; 4193 4193 explainAppendTerm(&txt, i++, z, ">"); 4194 4194 } 4195 - if( pPlan->wsFlags&WHERE_TOP_LIMIT ){ 4195 + if( pLoop->wsFlags&WHERE_TOP_LIMIT ){ 4196 4196 char *z = (j==pIndex->nColumn ) ? "rowid" : aCol[aiColumn[j]].zName; 4197 4197 explainAppendTerm(&txt, i, z, "<"); 4198 4198 } 4199 4199 sqlite3StrAccumAppend(&txt, ")", 1); 4200 4200 return sqlite3StrAccumFinish(&txt); 4201 4201 } 4202 4202 ................................................................................ 4211 4211 SrcList *pTabList, /* Table list this loop refers to */ 4212 4212 WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */ 4213 4213 int iLevel, /* Value for "level" column of output */ 4214 4214 int iFrom, /* Value for "from" column of output */ 4215 4215 u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */ 4216 4216 ){ 4217 4217 if( pParse->explain==2 ){ 4218 - u32 flags = pLevel->plan.wsFlags; 4219 4218 struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; 4220 4219 Vdbe *v = pParse->pVdbe; /* VM being constructed */ 4221 4220 sqlite3 *db = pParse->db; /* Database handle */ 4222 4221 char *zMsg; /* Text to add to EQP output */ 4223 4222 sqlite3_int64 nRow; /* Expected number of rows visited by scan */ 4224 4223 int iId = pParse->iSelectId; /* Select id (left-most output column) */ 4225 4224 int isSearch; /* True for a SEARCH. False for SCAN. */ 4225 + WhereLoop *pLoop; /* The controlling WhereLoop object */ 4226 + u32 flags; /* Flags that describe this loop */ 4226 4227 4228 + pLoop = pLevel->pWLoop; 4229 + flags = pLoop->wsFlags; 4227 4230 if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return; 4228 4231 4229 - isSearch = (pLevel->plan.nEq>0) 4230 - || (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 4231 - || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX)); 4232 + isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 4233 + || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0)) 4234 + || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX)); 4232 4235 4233 4236 zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN"); 4234 4237 if( pItem->pSelect ){ 4235 4238 zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId); 4236 4239 }else{ 4237 4240 zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName); 4238 4241 } 4239 4242 4240 4243 if( pItem->zAlias ){ 4241 4244 zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias); 4242 4245 } 4243 - if( (flags & WHERE_INDEXED)!=0 ){ 4244 - char *zWhere = explainIndexRange(db, pLevel, pItem->pTab); 4246 + if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 4247 + && pLoop->u.btree.pIndex!=0 4248 + ){ 4249 + char *zWhere = explainIndexRange(db, pLoop, pItem->pTab); 4245 4250 zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, 4246 4251 ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""), 4247 4252 ((flags & WHERE_IDX_ONLY)?"COVERING ":""), 4248 4253 ((flags & WHERE_TEMP_INDEX)?"":" "), 4249 - ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName), 4254 + ((flags & WHERE_TEMP_INDEX)?"": pLoop->u.btree.pIndex->zName), 4250 4255 zWhere 4251 4256 ); 4252 4257 sqlite3DbFree(db, zWhere); 4253 - }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ 4258 + }else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_INDEXED)!=0 ){ 4254 4259 zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); 4255 4260 4256 - if( flags&WHERE_ROWID_EQ ){ 4261 + if( flags&WHERE_COLUMN_EQ ){ 4257 4262 zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); 4258 4263 }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){ 4259 4264 zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg); 4260 4265 }else if( flags&WHERE_BTM_LIMIT ){ 4261 4266 zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg); 4262 4267 }else if( flags&WHERE_TOP_LIMIT ){ 4263 4268 zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg); 4264 4269 } 4265 4270 } 4266 4271 #ifndef SQLITE_OMIT_VIRTUALTABLE 4267 4272 else if( (flags & WHERE_VIRTUALTABLE)!=0 ){ 4268 - sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; 4269 4273 zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, 4270 - pVtabIdx->idxNum, pVtabIdx->idxStr); 4274 + pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr); 4271 4275 } 4272 4276 #endif 4273 4277 if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){ 4274 4278 testcase( wctrlFlags & WHERE_ORDERBY_MIN ); 4275 4279 nRow = 1; 4276 4280 }else{ 4277 - nRow = (sqlite3_int64)pLevel->plan.nRow; 4281 + nRow = (sqlite3_int64)pLoop->nOut; 4278 4282 } 4279 4283 zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow); 4280 4284 sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC); 4281 4285 } 4282 4286 } 4283 4287 #else 4284 4288 # define explainOneScan(u,v,w,x,y,z) ................................................................................ 5259 5263 p->pNextLoop = pNext; 5260 5264 *ppPrev = p; 5261 5265 p->aTerm = paTerm; 5262 5266 if( p->nTerm ){ 5263 5267 memcpy(p->aTerm, pTemplate->aTerm, p->nTerm*sizeof(p->aTerm[0])); 5264 5268 } 5265 5269 if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){ 5266 - if( p->u.btree.pIndex && p->u.btree.pIndex && p->u.btree.pIndex->tnum==0 ){ 5270 + Index *pIndex = p->u.btree.pIndex; 5271 + if( pIndex && pIndex->tnum==0 ){ 5267 5272 p->u.btree.pIndex = 0; 5268 5273 } 5269 5274 }else{ 5270 5275 pTemplate->u.vtab.needFree = 0; 5271 5276 } 5272 5277 return SQLITE_OK; 5273 5278 } ................................................................................ 5306 5311 if( pNew->wsFlags & WHERE_BTM_LIMIT ){ 5307 5312 opMask = WO_LT|WO_LE; 5308 5313 }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){ 5309 5314 opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE; 5310 5315 }else{ 5311 5316 opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE; 5312 5317 } 5318 + if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); 5313 5319 5314 5320 if( pNew->u.btree.nEq < pProbe->nColumn ){ 5315 5321 iCol = pProbe->aiColumn[pNew->u.btree.nEq]; 5316 5322 iRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1]; 5317 5323 }else{ 5318 5324 iCol = -1; 5319 5325 iRowEst = 1; ................................................................................ 5468 5474 /* Generate auto-index WhereLoops */ 5469 5475 WhereClause *pWC = pBuilder->pWC; 5470 5476 WhereTerm *pTerm; 5471 5477 WhereTerm *pWCEnd = pWC->a + pWC->nTerm; 5472 5478 for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){ 5473 5479 if( termCanDriveIndex(pTerm, pSrc, 0) ){ 5474 5480 pNew->u.btree.nEq = 1; 5481 + pNew->u.btree.pIndex = 0; 5475 5482 pNew->nTerm = 1; 5476 5483 pNew->aTerm[0] = pTerm; 5477 5484 pNew->rSetup = 2*rLogSize*pSrc->pTab->nRowEst; 5478 5485 pNew->nOut = (double)10; 5479 5486 pNew->rRun = rLogSize + pNew->nOut; 5480 5487 pNew->wsFlags = WHERE_TEMP_INDEX; 5481 5488 pNew->prereq = mExtra | pTerm->prereqRight; ................................................................................ 5511 5518 if( x<BMS-1 ){ 5512 5519 m &= ~(((Bitmask)1)<<x); 5513 5520 } 5514 5521 } 5515 5522 pNew->wsFlags = (m==0) ? WHERE_IDX_ONLY : 0; 5516 5523 5517 5524 /* Full scan via index */ 5518 - if( m==0 || b ){ 5525 + if( (m==0 || b) && pProbe->bUnordered==0 ){ 5519 5526 pNew->iSortIdx = b ? iSortIdx : 0; 5520 5527 pNew->nOut = rSize; 5521 5528 pNew->rRun = (m==0) ? (rSize + rLogSize)*(1+b) : (rSize*rLogSize); 5522 5529 rc = whereLoopInsert(pBuilder, pNew); 5523 5530 if( rc ) break; 5524 5531 } 5525 5532 } ................................................................................ 5888 5895 assert( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ); 5889 5896 isUnique = 1; 5890 5897 if( pLoop->wsFlags & WHERE_IPK ){ 5891 5898 if( (pLoop->wsFlags & WHERE_COLUMN_IN)!=0 ) isUnique = 0; 5892 5899 if( pLoop->u.btree.nEq!=1 ) isUnique = 0; 5893 5900 pIndex = 0; 5894 5901 nColumn = 1; 5895 - }else if( pLoop->u.btree.pIndex==0 ){ 5902 + }else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){ 5896 5903 return 0; 5897 5904 }else{ 5898 - pIndex = pLoop->u.btree.pIndex; 5899 5905 nColumn = pIndex->nColumn; 5900 5906 if( pIndex->onError==OE_None ){ 5901 5907 isUnique = 0; 5902 5908 }else if( (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_RANGE 5903 5909 |WHERE_COLUMN_NULL))!=0 ){ 5904 5910 isUnique = 0; 5905 5911 }else if( pLoop->u.btree.nEq < pIndex->nColumn ){
Changes to test/tester.tcl.
789 789 db close 790 790 sqlite3_reset_auto_extension 791 791 792 792 sqlite3_soft_heap_limit 0 793 793 set nTest [incr_ntest] 794 794 set nErr [set_test_counter errors] 795 795 796 - puts "$nErr errors out of $nTest tests" 797 - if {$nErr>0} { 798 - puts "Failures on these tests: [set_test_counter fail_list]" 796 + set nKnown 0 797 + if {[file readable known-problems.txt]} { 798 + set fd [open known-problems.txt] 799 + set content [read $fd] 800 + close $fd 801 + foreach x $content {set known_error($x) 1} 802 + foreach x [set_test_counter fail_list] { 803 + if {[info exists known_error($x)]} {incr nKnown} 804 + } 805 + } 806 + if {$nKnown>0} { 807 + puts "[expr {$nErr-$nKnown}] new errors and $nKnown known errors\ 808 + out of $nTest tests" 809 + } else { 810 + puts "$nErr errors out of $nTest tests" 811 + } 812 + if {$nErr>$nKnown} { 813 + puts -nonewline "Failures on these tests:" 814 + foreach x [set_test_counter fail_list] { 815 + if {![info exists known_error($x)]} {puts -nonewline " $x"} 816 + } 817 + puts "" 799 818 } 800 819 foreach warning [set_test_counter warn_list] { 801 820 puts "Warning: $warning" 802 821 } 803 822 run_thread_tests 1 804 823 if {[llength $omitList]>0} { 805 824 puts "Omitted test cases:"