/ Check-in [670993eb]
Login

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

Overview
Comment:Further enhancements to the "wheretrace" debugging output.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 670993eb8113f386cb2cb8b1507917f6da3b4d98
User & Date: drh 2014-09-30 17:03:35
Context
2014-09-30
17:31
Enable the query planner to deal with WHERE clauses that have OR terms nested within AND terms that are nested within OR terms. Also remove an unused function declaration. check-in: b6b28918 user: drh tags: trunk
17:03
Further enhancements to the "wheretrace" debugging output. check-in: 670993eb user: drh tags: trunk
14:14
Enhanced debug output for OR-logic in the query loop optimizer. check-in: 2e375eae user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3533   3533           Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
  3534   3534           int j1 = 0;                     /* Address of jump operation */
  3535   3535           if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){
  3536   3536             pAndExpr->pLeft = pOrExpr;
  3537   3537             pOrExpr = pAndExpr;
  3538   3538           }
  3539   3539           /* Loop through table entries that match term pOrTerm. */
         3540  +        WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
  3540   3541           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
  3541   3542                                         wctrlFlags, iCovCur);
  3542   3543           assert( pSubWInfo || pParse->nErr || db->mallocFailed );
  3543   3544           if( pSubWInfo ){
  3544   3545             WhereLoop *pSubLoop;
  3545   3546             explainOneScan(
  3546   3547                 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
................................................................................
  3757   3758   }
  3758   3759   
  3759   3760   #ifdef WHERETRACE_ENABLED
  3760   3761   /*
  3761   3762   ** Print the content of a WhereTerm object
  3762   3763   */
  3763   3764   static void whereTermPrint(WhereTerm *pTerm, int iTerm){
  3764         -  char zType[4];
  3765         -  memcpy(zType, "...", 4);
  3766         -  if( pTerm->wtFlags & TERM_VIRTUAL ) zType[0] = 'V';
  3767         -  if( pTerm->eOperator & WO_EQUIV  ) zType[1] = 'E';
  3768         -  if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) zType[2] = 'L';
  3769         -  sqlite3DebugPrintf("TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x\n", iTerm,
  3770         -                     pTerm, zType, pTerm->leftCursor, pTerm->truthProb,
  3771         -                     pTerm->eOperator);
  3772         -  sqlite3TreeViewExpr(0, pTerm->pExpr, 0);
         3765  +  if( pTerm==0 ){
         3766  +    sqlite3DebugPrintf("TERM-%-3d NULL\n", iTerm);
         3767  +  }else{
         3768  +    char zType[4];
         3769  +    memcpy(zType, "...", 4);
         3770  +    if( pTerm->wtFlags & TERM_VIRTUAL ) zType[0] = 'V';
         3771  +    if( pTerm->eOperator & WO_EQUIV  ) zType[1] = 'E';
         3772  +    if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) zType[2] = 'L';
         3773  +    sqlite3DebugPrintf("TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x\n",
         3774  +                       iTerm, pTerm, zType, pTerm->leftCursor, pTerm->truthProb,
         3775  +                       pTerm->eOperator);
         3776  +    sqlite3TreeViewExpr(0, pTerm->pExpr, 0);
         3777  +  }
  3773   3778   }
  3774   3779   #endif
  3775   3780   
  3776   3781   #ifdef WHERETRACE_ENABLED
  3777   3782   /*
  3778   3783   ** Print a WhereLoop object for debugging purposes
  3779   3784   */
................................................................................
  3814   3819     }else{
  3815   3820       sqlite3DebugPrintf(" f %05x N %d", p->wsFlags, p->nLTerm);
  3816   3821     }
  3817   3822     sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut);
  3818   3823     if( p->nLTerm && (sqlite3WhereTrace & 0x100)!=0 ){
  3819   3824       int i;
  3820   3825       for(i=0; i<p->nLTerm; i++){
  3821         -      WhereTerm *pTerm = p->aLTerm[i];
  3822         -      if( pTerm==0 ) continue;
  3823         -      whereTermPrint(pTerm, i);
         3826  +      whereTermPrint(p->aLTerm[i], i);
  3824   3827       }
  3825   3828     }
  3826   3829   }
  3827   3830   #endif
  3828   3831   
  3829   3832   /*
  3830   3833   ** Convert bulk memory into a valid WhereLoop that can be passed
................................................................................
  5019   5022         int once = 1;
  5020   5023         int i, j;
  5021   5024       
  5022   5025         sSubBuild = *pBuilder;
  5023   5026         sSubBuild.pOrderBy = 0;
  5024   5027         sSubBuild.pOrSet = &sCur;
  5025   5028   
         5029  +      WHERETRACE(0x200, ("Begin processing OR-clause %p\n", pTerm));
  5026   5030         for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){
  5027   5031           if( (pOrTerm->eOperator & WO_AND)!=0 ){
  5028   5032             sSubBuild.pWC = &pOrTerm->u.pAndInfo->wc;
  5029   5033           }else if( pOrTerm->leftCursor==iCur ){
  5030   5034             tempWC.pWInfo = pWC->pWInfo;
  5031   5035             tempWC.pOuter = pWC;
  5032   5036             tempWC.op = TK_AND;
................................................................................
  5034   5038             tempWC.a = pOrTerm;
  5035   5039             sSubBuild.pWC = &tempWC;
  5036   5040           }else{
  5037   5041             continue;
  5038   5042           }
  5039   5043           sCur.n = 0;
  5040   5044   #ifdef WHERETRACE_ENABLED
  5041         -        if( sqlite3WhereTrace & 0x200 ){
  5042         -          sqlite3DebugPrintf("OR-term %d:\n",(int)(pOrTerm-pOrWC->a));
  5043         -          sqlite3TreeViewExpr(0, pOrTerm->pExpr, 0);
         5045  +        WHERETRACE(0x200, ("OR-term %d of %p has %d subterms:\n", 
         5046  +                   (int)(pOrTerm-pOrWC->a), pTerm, sSubBuild.pWC->nTerm));
         5047  +        if( sqlite3WhereTrace & 0x400 ){
         5048  +          for(i=0; i<sSubBuild.pWC->nTerm; i++){
         5049  +            whereTermPrint(&sSubBuild.pWC->a[i], i);
         5050  +          }
  5044   5051           }
  5045   5052   #endif
  5046   5053   #ifndef SQLITE_OMIT_VIRTUALTABLE
  5047   5054           if( IsVirtual(pItem->pTab) ){
  5048   5055             rc = whereLoopAddVirtual(&sSubBuild, mExtra);
  5049   5056           }else
  5050   5057   #endif
................................................................................
  5091   5098           ** the planner may elect to "OR" together a full-table scan and an
  5092   5099           ** index lookup. And other similarly odd results.  */
  5093   5100           pNew->rRun = sSum.a[i].rRun + 1;
  5094   5101           pNew->nOut = sSum.a[i].nOut;
  5095   5102           pNew->prereq = sSum.a[i].prereq;
  5096   5103           rc = whereLoopInsert(pBuilder, pNew);
  5097   5104         }
         5105  +      WHERETRACE(0x200, ("End processing OR-clause %p\n", pTerm));
  5098   5106       }
  5099   5107     }
  5100   5108     return rc;
  5101   5109   }
  5102   5110   
  5103   5111   /*
  5104   5112   ** Add all WhereLoop objects for all tables