/ Check-in [710a18ac]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved "wheretrace" capabilities: Show the constraint expression if the wheretrace flag has the 0x100 bit set and if compiled with SQLITE_ENABLE_TREE_EXPLAIN.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 710a18ac7916cb688955505d7d461b461f563155
User & Date: drh 2013-10-28 14:34:35
Context
2013-10-28
19:03
Bug fix and enhancements to the improved wheretrace logic that shows the constraint expressions. check-in: 10f125f5 user: drh tags: trunk
14:34
Improved "wheretrace" capabilities: Show the constraint expression if the wheretrace flag has the 0x100 bit set and if compiled with SQLITE_ENABLE_TREE_EXPLAIN. check-in: 710a18ac user: drh tags: trunk
2013-10-24
15:20
Add the ".open" command to the command-line shell. check-in: 21eccb91 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2546   2546         }else{
  2547   2547           nNew = 10;        assert( 10==sqlite3LogEst(2) );
  2548   2548         }
  2549   2549         if( nNew<nOut ){
  2550   2550           nOut = nNew;
  2551   2551         }
  2552   2552         pLoop->nOut = (LogEst)nOut;
  2553         -      WHERETRACE(0x100, ("range scan regions: %u..%u  est=%d\n",
         2553  +      WHERETRACE(0x10, ("range scan regions: %u..%u  est=%d\n",
  2554   2554                            (u32)iLower, (u32)iUpper, nOut));
  2555   2555         return SQLITE_OK;
  2556   2556       }
  2557   2557     }
  2558   2558   #else
  2559   2559     UNUSED_PARAMETER(pParse);
  2560   2560     UNUSED_PARAMETER(pBuilder);
................................................................................
  2632   2632     rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq-1, &bOk);
  2633   2633     pBuilder->pRec = pRec;
  2634   2634     if( rc!=SQLITE_OK ) return rc;
  2635   2635     if( bOk==0 ) return SQLITE_NOTFOUND;
  2636   2636     pBuilder->nRecValid = nEq;
  2637   2637   
  2638   2638     whereKeyStats(pParse, p, pRec, 0, a);
  2639         -  WHERETRACE(0x100,("equality scan regions: %d\n", (int)a[1]));
         2639  +  WHERETRACE(0x10,("equality scan regions: %d\n", (int)a[1]));
  2640   2640     *pnRow = a[1];
  2641   2641     
  2642   2642     return rc;
  2643   2643   }
  2644   2644   #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
  2645   2645   
  2646   2646   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
................................................................................
  2680   2680       nRowEst += nEst;
  2681   2681       pBuilder->nRecValid = nRecValid;
  2682   2682     }
  2683   2683   
  2684   2684     if( rc==SQLITE_OK ){
  2685   2685       if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
  2686   2686       *pnRow = nRowEst;
  2687         -    WHERETRACE(0x100,("IN row estimate: est=%g\n", nRowEst));
         2687  +    WHERETRACE(0x10,("IN row estimate: est=%g\n", nRowEst));
  2688   2688     }
  2689   2689     assert( pBuilder->nRecValid==nRecValid );
  2690   2690     return rc;
  2691   2691   }
  2692   2692   #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
  2693   2693   
  2694   2694   /*
................................................................................
  3889   3889     return pLevel->notReady;
  3890   3890   }
  3891   3891   
  3892   3892   #ifdef WHERETRACE_ENABLED
  3893   3893   /*
  3894   3894   ** Print a WhereLoop object for debugging purposes
  3895   3895   */
  3896         -static void whereLoopPrint(WhereLoop *p, SrcList *pTabList){
  3897         -  int nb = 1+(pTabList->nSrc+7)/8;
  3898         -  struct SrcList_item *pItem = pTabList->a + p->iTab;
         3896  +static void whereLoopPrint(WhereLoop *p, WhereInfo *pWInfo){
         3897  +  int nb = 1+(pWInfo->pTabList->nSrc+7)/8;
         3898  +  struct SrcList_item *pItem = pWInfo->pTabList->a + p->iTab;
  3899   3899     Table *pTab = pItem->pTab;
  3900   3900     sqlite3DebugPrintf("%c%2d.%0*llx.%0*llx", p->cId,
  3901   3901                        p->iTab, nb, p->maskSelf, nb, p->prereq);
  3902   3902     sqlite3DebugPrintf(" %12s",
  3903   3903                        pItem->zAlias ? pItem->zAlias : pTab->zName);
  3904   3904     if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
  3905   3905       if( p->u.btree.pIndex ){
................................................................................
  3923   3923         z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask);
  3924   3924       }
  3925   3925       sqlite3DebugPrintf(" %-19s", z);
  3926   3926       sqlite3_free(z);
  3927   3927     }
  3928   3928     sqlite3DebugPrintf(" f %04x N %d", p->wsFlags, p->nLTerm);
  3929   3929     sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut);
         3930  +#ifdef SQLITE_ENABLE_TREE_EXPLAIN
         3931  +  /* If the 0x100 bit of wheretracing is set, then show all of the constraint
         3932  +  ** expressions in the WhereLoop.aLTerm[] array.
         3933  +  */
         3934  +  if( p->nLTerm && (sqlite3WhereTrace & 0x100)!=0 ){  /* WHERETRACE 0x100 */
         3935  +    int i;
         3936  +    Vdbe *v = pWInfo->pParse->pVdbe;
         3937  +    sqlite3ExplainBegin(v);
         3938  +    for(i=0; i<p->nLTerm; i++){
         3939  +      sqlite3ExplainPrintf(v, "  (%d) ", i+1);
         3940  +      sqlite3ExplainPush(v);
         3941  +      sqlite3ExplainExpr(v, p->aLTerm[i]->pExpr);
         3942  +      sqlite3ExplainPop(v);
         3943  +      sqlite3ExplainNL(v);
         3944  +    }
         3945  +    sqlite3DebugPrintf("%s", sqlite3VdbeExplanation(v));
         3946  +    sqlite3ExplainFinish(v);
         3947  +  }
         3948  +#endif
  3930   3949   }
  3931   3950   #endif
  3932   3951   
  3933   3952   /*
  3934   3953   ** Convert bulk memory into a valid WhereLoop that can be passed
  3935   3954   ** to whereLoopClear harmlessly.
  3936   3955   */
................................................................................
  4062   4081     if( pBuilder->pOrSet!=0 ){
  4063   4082   #if WHERETRACE_ENABLED
  4064   4083       u16 n = pBuilder->pOrSet->n;
  4065   4084       int x =
  4066   4085   #endif
  4067   4086       whereOrInsert(pBuilder->pOrSet, pTemplate->prereq, pTemplate->rRun,
  4068   4087                                       pTemplate->nOut);
  4069         -#if WHERETRACE_ENABLED
         4088  +#if WHERETRACE_ENABLED /* 0x8 */
  4070   4089       if( sqlite3WhereTrace & 0x8 ){
  4071   4090         sqlite3DebugPrintf(x?"   or-%d:  ":"   or-X:  ", n);
  4072         -      whereLoopPrint(pTemplate, pWInfo->pTabList);
         4091  +      whereLoopPrint(pTemplate, pWInfo);
  4073   4092       }
  4074   4093   #endif
  4075   4094       return SQLITE_OK;
  4076   4095     }
  4077   4096   
  4078   4097     /* Search for an existing WhereLoop to overwrite, or which takes
  4079   4098     ** priority over pTemplate.
................................................................................
  4135   4154       }
  4136   4155     }
  4137   4156   
  4138   4157     /* If we reach this point it means that either p[] should be overwritten
  4139   4158     ** with pTemplate[] if p[] exists, or if p==NULL then allocate a new
  4140   4159     ** WhereLoop and insert it.
  4141   4160     */
  4142         -#if WHERETRACE_ENABLED
         4161  +#if WHERETRACE_ENABLED /* 0x8 */
  4143   4162     if( sqlite3WhereTrace & 0x8 ){
  4144   4163       if( p!=0 ){
  4145   4164         sqlite3DebugPrintf("ins-del:  ");
  4146         -      whereLoopPrint(p, pWInfo->pTabList);
         4165  +      whereLoopPrint(p, pWInfo);
  4147   4166       }
  4148   4167       sqlite3DebugPrintf("ins-new:  ");
  4149         -    whereLoopPrint(pTemplate, pWInfo->pTabList);
         4168  +    whereLoopPrint(pTemplate, pWInfo);
  4150   4169     }
  4151   4170   #endif
  4152   4171     if( p==0 ){
  4153   4172       p = sqlite3DbMallocRaw(db, sizeof(WhereLoop));
  4154   4173       if( p==0 ) return SQLITE_NOMEM;
  4155   4174       whereLoopInit(p);
  4156   4175     }
................................................................................
  4163   4182         p->u.btree.pIndex = 0;
  4164   4183       }
  4165   4184     }
  4166   4185     return SQLITE_OK;
  4167   4186   
  4168   4187     /* Jump here if the insert is a no-op */
  4169   4188   whereLoopInsert_noop:
  4170         -#if WHERETRACE_ENABLED
         4189  +#if WHERETRACE_ENABLED /* 0x8 */
  4171   4190     if( sqlite3WhereTrace & 0x8 ){
  4172   4191       sqlite3DebugPrintf("ins-noop: ");
  4173         -    whereLoopPrint(pTemplate, pWInfo->pTabList);
         4192  +    whereLoopPrint(pTemplate, pWInfo);
  4174   4193     }
  4175   4194   #endif
  4176   4195     return SQLITE_OK;  
  4177   4196   }
  4178   4197   
  4179   4198   /*
  4180   4199   ** Adjust the WhereLoop.nOut value downward to account for terms of the
................................................................................
  5324   5343             ){
  5325   5344               testcase( jj==nTo-1 );
  5326   5345               break;
  5327   5346             }
  5328   5347           }
  5329   5348           if( jj>=nTo ){
  5330   5349             if( nTo>=mxChoice && rCost>=mxCost ){
  5331         -#ifdef WHERETRACE_ENABLED
         5350  +#ifdef WHERETRACE_ENABLED /* 0x4 */
  5332   5351               if( sqlite3WhereTrace&0x4 ){
  5333   5352                 sqlite3DebugPrintf("Skip   %s cost=%-3d,%3d order=%c\n",
  5334   5353                     wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5335   5354                     isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5336   5355               }
  5337   5356   #endif
  5338   5357               continue;
................................................................................
  5342   5361               /* Increase the size of the aTo set by one */
  5343   5362               jj = nTo++;
  5344   5363             }else{
  5345   5364               /* New path replaces the prior worst to keep count below mxChoice */
  5346   5365               jj = mxI;
  5347   5366             }
  5348   5367             pTo = &aTo[jj];
  5349         -#ifdef WHERETRACE_ENABLED
         5368  +#ifdef WHERETRACE_ENABLED /* 0x4 */
  5350   5369             if( sqlite3WhereTrace&0x4 ){
  5351   5370               sqlite3DebugPrintf("New    %s cost=%-3d,%3d order=%c\n",
  5352   5371                   wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5353   5372                   isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5354   5373             }
  5355   5374   #endif
  5356   5375           }else{
  5357   5376             if( pTo->rCost<=rCost && pTo->nRow<=nOut ){
  5358         -#ifdef WHERETRACE_ENABLED
         5377  +#ifdef WHERETRACE_ENABLED /* 0x4 */
  5359   5378               if( sqlite3WhereTrace&0x4 ){
  5360   5379                 sqlite3DebugPrintf(
  5361   5380                     "Skip   %s cost=%-3d,%3d order=%c",
  5362   5381                     wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5363   5382                     isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5364   5383                 sqlite3DebugPrintf("   vs %s cost=%-3d,%d order=%c\n",
  5365   5384                     wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
................................................................................
  5367   5386               }
  5368   5387   #endif
  5369   5388               testcase( pTo->rCost==rCost );
  5370   5389               continue;
  5371   5390             }
  5372   5391             testcase( pTo->rCost==rCost+1 );
  5373   5392             /* A new and better score for a previously created equivalent path */
  5374         -#ifdef WHERETRACE_ENABLED
         5393  +#ifdef WHERETRACE_ENABLED /* 0x4 */
  5375   5394             if( sqlite3WhereTrace&0x4 ){
  5376   5395               sqlite3DebugPrintf(
  5377   5396                   "Update %s cost=%-3d,%3d order=%c",
  5378   5397                   wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5379   5398                   isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5380   5399               sqlite3DebugPrintf("  was %s cost=%-3d,%3d order=%c\n",
  5381   5400                   wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
................................................................................
  5403   5422                 mxI = jj;
  5404   5423               }
  5405   5424             }
  5406   5425           }
  5407   5426         }
  5408   5427       }
  5409   5428   
  5410         -#ifdef WHERETRACE_ENABLED
         5429  +#ifdef WHERETRACE_ENABLED  /* >=2 */
  5411   5430       if( sqlite3WhereTrace>=2 ){
  5412   5431         sqlite3DebugPrintf("---- after round %d ----\n", iLoop);
  5413   5432         for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){
  5414   5433           sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c",
  5415   5434              wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  5416   5435              pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
  5417   5436           if( pTo->isOrderedValid && pTo->isOrdered ){
................................................................................
  5817   5836     /* Construct the WhereLoop objects */
  5818   5837     WHERETRACE(0xffff,("*** Optimizer Start ***\n"));
  5819   5838     if( nTabList!=1 || whereShortCut(&sWLB)==0 ){
  5820   5839       rc = whereLoopAddAll(&sWLB);
  5821   5840       if( rc ) goto whereBeginError;
  5822   5841     
  5823   5842       /* Display all of the WhereLoop objects if wheretrace is enabled */
  5824         -#ifdef WHERETRACE_ENABLED
         5843  +#ifdef WHERETRACE_ENABLED /* !=0 */
  5825   5844       if( sqlite3WhereTrace ){
  5826   5845         WhereLoop *p;
  5827   5846         int i;
  5828   5847         static char zLabel[] = "0123456789abcdefghijklmnopqrstuvwyxz"
  5829   5848                                          "ABCDEFGHIJKLMNOPQRSTUVWYXZ";
  5830   5849         for(p=pWInfo->pLoops, i=0; p; p=p->pNextLoop, i++){
  5831   5850           p->cId = zLabel[i%sizeof(zLabel)];
  5832         -        whereLoopPrint(p, pTabList);
         5851  +        whereLoopPrint(p, pWInfo);
  5833   5852         }
  5834   5853       }
  5835   5854   #endif
  5836   5855     
  5837   5856       wherePathSolver(pWInfo, 0);
  5838   5857       if( db->mallocFailed ) goto whereBeginError;
  5839   5858       if( pWInfo->pOrderBy ){
................................................................................
  5843   5862     }
  5844   5863     if( pWInfo->pOrderBy==0 && (db->flags & SQLITE_ReverseOrder)!=0 ){
  5845   5864        pWInfo->revMask = (Bitmask)(-1);
  5846   5865     }
  5847   5866     if( pParse->nErr || NEVER(db->mallocFailed) ){
  5848   5867       goto whereBeginError;
  5849   5868     }
  5850         -#ifdef WHERETRACE_ENABLED
         5869  +#ifdef WHERETRACE_ENABLED /* !=0 */
  5851   5870     if( sqlite3WhereTrace ){
  5852   5871       int ii;
  5853   5872       sqlite3DebugPrintf("---- Solution nRow=%d", pWInfo->nRowOut);
  5854   5873       if( pWInfo->bOBSat ){
  5855   5874         sqlite3DebugPrintf(" ORDERBY=0x%llx", pWInfo->revMask);
  5856   5875       }
  5857   5876       switch( pWInfo->eDistinct ){
................................................................................
  5866   5885         case WHERE_DISTINCT_UNORDERED: {
  5867   5886           sqlite3DebugPrintf("  DISTINCT=unordered");
  5868   5887           break;
  5869   5888         }
  5870   5889       }
  5871   5890       sqlite3DebugPrintf("\n");
  5872   5891       for(ii=0; ii<pWInfo->nLevel; ii++){
  5873         -      whereLoopPrint(pWInfo->a[ii].pWLoop, pTabList);
         5892  +      whereLoopPrint(pWInfo->a[ii].pWLoop, pWInfo);
  5874   5893       }
  5875   5894     }
  5876   5895   #endif
  5877   5896     /* Attempt to omit tables from the join that do not effect the result */
  5878   5897     if( pWInfo->nLevel>=2
  5879   5898      && pResultSet!=0
  5880   5899      && OptimizationEnabled(db, SQLITE_OmitNoopJoin)