/ Check-in [f783e8e6]
Login

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: f783e8e6b10de44029c7c5f57e4648a4a677ca1b
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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:"