/ Check-in [b5897bc0]
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:Provide hints for all terms in a range constraint if there are any equality terms anywhere in the constraint. Range constraint terms are only omitted for a pure range constraint with no equality prefix.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: b5897bc0f003c470eeb2a75e0a2b2bb202681531
User & Date: drh 2015-08-18 15:58:05
Context
2015-08-20
23:45
Merge recent enhancements from trunk, including table-valued expressions. check-in: b9927c87 user: drh tags: cursor-hints
2015-08-18
15:58
Provide hints for all terms in a range constraint if there are any equality terms anywhere in the constraint. Range constraint terms are only omitted for a pure range constraint with no equality prefix. check-in: b5897bc0 user: drh tags: cursor-hints
2015-08-17
17:19
Avoid generating hints using constraints that are also used to initialize the cursor, since presumably the cursor already knows about those constraints. check-in: 142b048a user: drh tags: cursor-hints
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

   677    677     sqlite3 *db = pParse->db;
   678    678     Vdbe *v = pParse->pVdbe;
   679    679     Expr *pExpr = 0;
   680    680     WhereLoop *pLoop = pLevel->pWLoop;
   681    681     int iCur;
   682    682     WhereClause *pWC;
   683    683     WhereTerm *pTerm;
   684         -  WhereLoop *pWLoop;
   685    684     int i, j;
   686    685     struct CCurHint sHint;
   687    686     Walker sWalker;
   688    687   
   689    688     if( OptimizationDisabled(db, SQLITE_CursorHints) ) return;
   690    689     iCur = pLevel->iTabCur;
   691    690     assert( iCur==pWInfo->pTabList->a[pLevel->iFrom].iCursor );
................................................................................
   692    691     sHint.iTabCur = iCur;
   693    692     sHint.iIdxCur = pLevel->iIdxCur;
   694    693     sHint.pIdx = pLoop->u.btree.pIndex;
   695    694     memset(&sWalker, 0, sizeof(sWalker));
   696    695     sWalker.pParse = pParse;
   697    696     sWalker.u.pCCurHint = &sHint;
   698    697     pWC = &pWInfo->sWC;
   699         -  pWLoop = pLevel->pWLoop;
   700    698     for(i=0; i<pWC->nTerm; i++){
   701    699       pTerm = &pWC->a[i];
   702    700       if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
   703    701       if( pTerm->prereqAll & pLevel->notReady ) continue;
   704    702       if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue;
   705    703   
   706    704       /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize
   707         -    ** the cursor.  No need to hint initialization terms. */
   708         -    if( pTerm!=pEndRange ){
   709         -      for(j=0; j<pWLoop->nLTerm && pWLoop->aLTerm[j]!=pTerm; j++){}
   710         -      if( j<pWLoop->nLTerm ) continue;
          705  +    ** the cursor.  These terms are not needed as hints for a pure range
          706  +    ** scan (that has no == terms) so omit them. */
          707  +    if( pLoop->u.btree.nEq==0 && pTerm!=pEndRange ){
          708  +      for(j=0; j<pLoop->nLTerm && pLoop->aLTerm[j]!=pTerm; j++){}
          709  +      if( j<pLoop->nLTerm ) continue;
   711    710       }
   712    711   
   713    712       /* No subqueries or non-deterministic functions allowed */
   714    713       if( sqlite3ExprContainsSubquery(pTerm->pExpr) ) continue;
   715    714   
   716    715       /* For an index scan, make sure referenced columns are actually in
   717    716       ** the index. */
................................................................................
  1090   1089          && pIdx->pTable->aCol[j].notNull==0
  1091   1090         ){
  1092   1091           bSeekPastNull = 1;
  1093   1092         }
  1094   1093       }
  1095   1094       assert( pRangeEnd==0 || (pRangeEnd->wtFlags & TERM_VNULL)==0 );
  1096   1095   
  1097         -    /* Generate code to evaluate all constraint terms using == or IN
  1098         -    ** and store the values of those terms in an array of registers
  1099         -    ** starting at regBase.
  1100         -    */
  1101         -    regBase = codeAllEqualityTerms(pParse,pLevel,bRev,nExtraReg,&zStartAff);
  1102         -    assert( zStartAff==0 || sqlite3Strlen30(zStartAff)>=nEq );
  1103         -    if( zStartAff ) cEndAff = zStartAff[nEq];
  1104         -    addrNxt = pLevel->addrNxt;
  1105         -
  1106   1096       /* If we are doing a reverse order scan on an ascending index, or
  1107   1097       ** a forward order scan on a descending index, interchange the 
  1108   1098       ** start and end terms (pRangeStart and pRangeEnd).
  1109   1099       */
  1110   1100       if( (nEq<pIdx->nKeyCol && bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC))
  1111   1101        || (bRev && pIdx->nKeyCol==nEq)
  1112   1102       ){
  1113   1103         SWAP(WhereTerm *, pRangeEnd, pRangeStart);
  1114   1104         SWAP(u8, bSeekPastNull, bStopAtNull);
  1115   1105       }
         1106  +
         1107  +    /* Generate code to evaluate all constraint terms using == or IN
         1108  +    ** and store the values of those terms in an array of registers
         1109  +    ** starting at regBase.
         1110  +    */
         1111  +    codeCursorHint(pWInfo, pLevel, pRangeEnd);
         1112  +    regBase = codeAllEqualityTerms(pParse,pLevel,bRev,nExtraReg,&zStartAff);
         1113  +    assert( zStartAff==0 || sqlite3Strlen30(zStartAff)>=nEq );
         1114  +    if( zStartAff ) cEndAff = zStartAff[nEq];
         1115  +    addrNxt = pLevel->addrNxt;
  1116   1116   
  1117   1117       testcase( pRangeStart && (pRangeStart->eOperator & WO_LE)!=0 );
  1118   1118       testcase( pRangeStart && (pRangeStart->eOperator & WO_GE)!=0 );
  1119   1119       testcase( pRangeEnd && (pRangeEnd->eOperator & WO_LE)!=0 );
  1120   1120       testcase( pRangeEnd && (pRangeEnd->eOperator & WO_GE)!=0 );
  1121   1121       startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE);
  1122   1122       endEq =   !pRangeEnd || pRangeEnd->eOperator & (WO_LE|WO_GE);
  1123   1123       start_constraints = pRangeStart || nEq>0;
  1124   1124   
  1125   1125       /* Seek the index cursor to the start of the range. */
  1126         -    codeCursorHint(pWInfo, pLevel, pRangeEnd);
  1127   1126       nConstraint = nEq;
  1128   1127       if( pRangeStart ){
  1129   1128         Expr *pRight = pRangeStart->pExpr->pRight;
  1130   1129         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  1131   1130         whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
  1132   1131         if( (pRangeStart->wtFlags & TERM_VNULL)==0
  1133   1132          && sqlite3ExprCanBeNull(pRight)

Changes to test/cursorhint.test.

   135    135     }
   136    136   } {LE(c0,20)}
   137    137   do_test 4.5desc {
   138    138     p4_of_opcode db CursorHint {
   139    139       SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b DESC;
   140    140     }
   141    141   } {GE(c0,10)}
          142  +
          143  +# If there are any equality terms used in the constraint, then all terms
          144  +# should be hinted.
          145  +#
   142    146   do_test 4.6asc {
   143    147     p4_of_opcode db CursorHint {
   144    148       SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c ASC;
   145    149     }
   146         -} {LE(c1,20)}
          150  +} {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))}
   147    151   do_test 4.6desc {
   148    152     p4_of_opcode db CursorHint {
   149    153       SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c DESC;
   150    154     }
   151         -} {GE(c1,10)}
          155  +} {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))}
   152    156   
   153    157   finish_test