/ Check-in [522f1eac]
Login

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

Overview
Comment:Add the WHERE_IN_EARLYOUT flag and use it to clarify the logic of this optimization.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | multikey-opt-idea
Files: files | file ages | folders
SHA3-256: 522f1eacc20f11002cad58232a7c2610f369568653510e54f46088f579f778dc
User & Date: drh 2018-06-07 14:59:22
Context
2018-06-07
15:28
Merge the ".stat/.eqp" CLI fix from trunk. check-in: a91cad33 user: drh tags: multikey-opt-idea
14:59
Add the WHERE_IN_EARLYOUT flag and use it to clarify the logic of this optimization. check-in: 522f1eac user: drh tags: multikey-opt-idea
14:32
Merge recent trunk enhancements. check-in: e9d7bf4f user: drh tags: multikey-opt-idea
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  5080   5080         struct InLoop *pIn;
  5081   5081         int j;
  5082   5082         sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
  5083   5083         for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
  5084   5084           sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
  5085   5085           if( pIn->eEndLoopOp!=OP_Noop ){
  5086   5086             if( pIn->nPrefix ){
         5087  +            assert( pLoop->wsFlags & WHERE_IN_EARLYOUT );
  5087   5088               sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur,
  5088   5089                                 sqlite3VdbeCurrentAddr(v)+2,
  5089   5090                                 pIn->iBase, pIn->nPrefix);
  5090   5091               VdbeCoverage(v);
  5091   5092             }
  5092   5093             sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
  5093   5094             VdbeCoverage(v);

Changes to src/whereInt.h.

   553    553   #define WHERE_IN_ABLE      0x00000800  /* Able to support an IN operator */
   554    554   #define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
   555    555   #define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
   556    556   #define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */
   557    557   #define WHERE_SKIPSCAN     0x00008000  /* Uses the skip-scan algorithm */
   558    558   #define WHERE_UNQ_WANTED   0x00010000  /* WHERE_ONEROW would have been helpful*/
   559    559   #define WHERE_PARTIALIDX   0x00020000  /* The automatic index is partial */
          560  +#define WHERE_IN_EARLYOUT  0x00040000  /* Perhaps quit IN loops early */

Changes to src/wherecode.c.

   591    591             sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
   592    592             if( i==iEq ){
   593    593               pIn->iCur = iTab;
   594    594               pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
   595    595               if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){
   596    596                 pIn->iBase = iReg - i;
   597    597                 pIn->nPrefix = i;
          598  +              pLoop->wsFlags |= WHERE_IN_EARLYOUT;
   598    599               }else{
   599    600                 pIn->nPrefix = 0;
   600    601               }
   601    602             }else{
   602    603               pIn->eEndLoopOp = OP_Noop;
   603    604             }
   604    605             pIn++;
................................................................................
  1660   1661       }
  1661   1662       codeApplyAffinity(pParse, regBase, nConstraint - bSeekPastNull, zStartAff);
  1662   1663       if( pLoop->nSkip>0 && nConstraint==pLoop->nSkip ){
  1663   1664         /* The skip-scan logic inside the call to codeAllEqualityConstraints()
  1664   1665         ** above has already left the cursor sitting on the correct row,
  1665   1666         ** so no further seeking is needed */
  1666   1667       }else{
  1667         -      if( pLoop->wsFlags & WHERE_IN_ABLE ){
         1668  +      if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
  1668   1669           sqlite3VdbeAddOp1(v, OP_SeekHit, iIdxCur);
  1669   1670         }
  1670   1671         op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
  1671   1672         assert( op!=0 );
  1672   1673         sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
  1673   1674         VdbeCoverage(v);
  1674   1675         VdbeCoverageIf(v, op==OP_Rewind);  testcase( op==OP_Rewind );
................................................................................
  1726   1727         sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
  1727   1728         testcase( op==OP_IdxGT );  VdbeCoverageIf(v, op==OP_IdxGT );
  1728   1729         testcase( op==OP_IdxGE );  VdbeCoverageIf(v, op==OP_IdxGE );
  1729   1730         testcase( op==OP_IdxLT );  VdbeCoverageIf(v, op==OP_IdxLT );
  1730   1731         testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
  1731   1732       }
  1732   1733   
  1733         -    if( pLoop->wsFlags & WHERE_IN_ABLE ){
         1734  +    if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
  1734   1735         sqlite3VdbeAddOp2(v, OP_SeekHit, iIdxCur, 1);
  1735   1736       }
  1736   1737   
  1737   1738       /* Seek the table cursor, if required */
  1738   1739       if( omitTable ){
  1739   1740         /* pIdx is a covering index.  No need to access the main table. */
  1740   1741       }else if( HasRowid(pIdx->pTable) ){