/ Check-in [6ad55319]
Login

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

Overview
Comment:Remove rows from the ephemeral table used by window functions once they are no longer required.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: 6ad553192051eaa0c6d929baacde2de07b93c6d09de861028bbce55a2c9bfdd3
User & Date: dan 2019-03-13 15:29:14
Wiki:window-functions
Context
2019-03-13
17:20
Avoid allocating excessive registers for the PARTITION BY expressions when processing window functions. check-in: 180be266 user: dan tags: window-functions
15:29
Remove rows from the ephemeral table used by window functions once they are no longer required. check-in: 6ad55319 user: dan tags: window-functions
08:28
Minor optimization in sqlite3WindowCodeStep(). check-in: b1322ffb user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  8824   8824   
  8825   8825     assert( cursorOwnsBtShared(pCur) );
  8826   8826     assert( pBt->inTransaction==TRANS_WRITE );
  8827   8827     assert( (pBt->btsFlags & BTS_READ_ONLY)==0 );
  8828   8828     assert( pCur->curFlags & BTCF_WriteFlag );
  8829   8829     assert( hasSharedCacheTableLock(p, pCur->pgnoRoot, pCur->pKeyInfo!=0, 2) );
  8830   8830     assert( !hasReadConflicts(p, pCur->pgnoRoot) );
  8831         -  assert( pCur->ix<pCur->pPage->nCell );
  8832         -  assert( pCur->eState==CURSOR_VALID );
  8833   8831     assert( (flags & ~(BTREE_SAVEPOSITION | BTREE_AUXDELETE))==0 );
         8832  +  if( pCur->eState==CURSOR_REQUIRESEEK ){
         8833  +    rc = btreeRestoreCursorPosition(pCur);
         8834  +    if( rc ) return rc;
         8835  +  }
         8836  +  assert( pCur->eState==CURSOR_VALID );
         8837  +  assert( pCur->ix<pCur->pPage->nCell );
  8834   8838   
  8835   8839     iCellDepth = pCur->iPage;
  8836   8840     iCellIdx = pCur->ix;
  8837   8841     pPage = pCur->pPage;
  8838   8842     pCell = findCell(pPage, iCellIdx);
  8839   8843     if( pPage->nFree<0 && btreeComputeFreeSpace(pPage) ) return SQLITE_CORRUPT;
  8840   8844   

Changes to src/window.c.

   607    607           { row_numberName,   TK_ROWS,   TK_UNBOUNDED, TK_CURRENT }, 
   608    608           { dense_rankName,   TK_RANGE,  TK_UNBOUNDED, TK_CURRENT }, 
   609    609           { rankName,         TK_RANGE,  TK_UNBOUNDED, TK_CURRENT }, 
   610    610           { percent_rankName, TK_GROUPS, TK_CURRENT,   TK_UNBOUNDED }, 
   611    611           { cume_distName,    TK_GROUPS, TK_FOLLOWING, TK_UNBOUNDED }, 
   612    612           { ntileName,        TK_ROWS,   TK_CURRENT,   TK_UNBOUNDED }, 
   613    613           { leadName,         TK_ROWS,   TK_UNBOUNDED, TK_UNBOUNDED }, 
          614  +        { lagName,          TK_ROWS,   TK_UNBOUNDED, TK_CURRENT }, 
   614    615         };
   615    616         int i;
   616    617         for(i=0; i<ArraySize(aUp); i++){
   617    618           if( pFunc->zName==aUp[i].zFunc ){
   618    619             sqlite3ExprDelete(db, pWin->pStart);
   619    620             sqlite3ExprDelete(db, pWin->pEnd);
   620    621             pWin->pEnd = pWin->pStart = 0;
................................................................................
  1468   1469       }
  1469   1470     }
  1470   1471     regArg = pParse->nMem+1;
  1471   1472     pParse->nMem += nArg;
  1472   1473     return regArg;
  1473   1474   }
  1474   1475   
  1475         -#if 0
  1476   1476   /* 
  1477   1477   ** Return true if the current frame should be cached in the ephemeral table,
  1478   1478   ** even if there are no xInverse() calls required.
  1479   1479   */
  1480   1480   static int windowCacheFrame(Window *pMWin){
  1481   1481     Window *pWin;
  1482   1482     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1483   1483       FuncDef *pFunc = pWin->pFunc;
  1484   1484       if( (pFunc->zName==nth_valueName)
  1485   1485        || (pFunc->zName==first_valueName)
  1486         -     || (pFunc->zName==leadName) */
         1486  +     || (pFunc->zName==leadName)
  1487   1487        || (pFunc->zName==lagName)
  1488   1488       ){
  1489   1489         return 1;
  1490   1490       }
  1491   1491     }
  1492   1492     return 0;
  1493   1493   }
  1494         -#endif
  1495   1494   
  1496   1495   /*
  1497   1496   ** regOld and regNew are each the first register in an array of size
  1498   1497   ** pOrderBy->nExpr. This function generates code to compare the two
  1499   1498   ** arrays of registers using the collation sequences and other comparison
  1500   1499   ** parameters specified by pOrderBy. 
  1501   1500   **
................................................................................
  1528   1527   
  1529   1528   typedef struct WindowCodeArg WindowCodeArg;
  1530   1529   typedef struct WindowCsrAndReg WindowCsrAndReg;
  1531   1530   struct WindowCsrAndReg {
  1532   1531     int csr;
  1533   1532     int reg;
  1534   1533   };
         1534  +
  1535   1535   struct WindowCodeArg {
  1536   1536     Parse *pParse;
  1537   1537     Window *pMWin;
  1538   1538     Vdbe *pVdbe;
  1539   1539     int regGosub;
  1540   1540     int addrGosub;
  1541   1541     int regArg;
         1542  +  int eDelete;
  1542   1543   
  1543   1544     WindowCsrAndReg start;
  1544   1545     WindowCsrAndReg current;
  1545   1546     WindowCsrAndReg end;
  1546   1547   };
  1547   1548   
         1549  +/*
         1550  +** Values that may be passed as the second argument to windowCodeOp().
         1551  +*/
  1548   1552   #define WINDOW_RETURN_ROW 1
  1549   1553   #define WINDOW_AGGINVERSE 2
  1550   1554   #define WINDOW_AGGSTEP    3
  1551   1555   
  1552   1556   /*
  1553   1557   ** Generate VM code to read the window frames peer values from cursor csr into
  1554   1558   ** an array of registers starting at reg.
................................................................................
  1696   1700   
  1697   1701       case WINDOW_AGGSTEP:
  1698   1702         csr = p->end.csr;
  1699   1703         reg = p->end.reg;
  1700   1704         windowAggStep(pParse, pMWin, csr, 0, p->regArg, 0);
  1701   1705         break;
  1702   1706     }
         1707  +
         1708  +  if( op==p->eDelete ){
         1709  +    sqlite3VdbeAddOp1(v, OP_Delete, csr);
         1710  +    sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION);
         1711  +  }
  1703   1712   
  1704   1713     if( jumpOnEof ){
  1705   1714       sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
  1706   1715       ret = sqlite3VdbeAddOp0(v, OP_Goto);
  1707   1716     }else{
  1708   1717       sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1+bPeer);
  1709   1718       if( bPeer ){
  1710   1719         addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
  1711   1720       }
  1712   1721     }
  1713   1722   
  1714   1723     if( bPeer ){
  1715         -    int addr;
  1716   1724       int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
  1717   1725       int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
  1718   1726       windowReadPeerValues(p, csr, regTmp);
  1719   1727       windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg, addrContinue);
  1720   1728       sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  1721   1729     }
  1722   1730   
................................................................................
  2149   2157     s.pVdbe = v;
  2150   2158     s.regGosub = regGosub;
  2151   2159     s.addrGosub = addrGosub;
  2152   2160     s.current.csr = pMWin->iEphCsr;
  2153   2161     csrWrite = s.current.csr+1;
  2154   2162     s.start.csr = s.current.csr+2;
  2155   2163     s.end.csr = s.current.csr+3;
         2164  +
         2165  +  /* Figure out when rows may be deleted from the ephemeral table. There
         2166  +  ** are four options - they may never be deleted (eDelete==0), they may 
         2167  +  ** be deleted as soon as they are no longer part of the window frame
         2168  +  ** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row 
         2169  +  ** has been returned to the caller (WINDOW_RETURN_ROW), or they may
         2170  +  ** be deleted after they enter the frame (WINDOW_AGGSTEP). */
         2171  +  switch( pMWin->eStart ){
         2172  +    case TK_FOLLOWING: {
         2173  +      sqlite3 *db = pParse->db;
         2174  +      sqlite3_value *pVal = 0;
         2175  +      sqlite3ValueFromExpr(db, pMWin->pStart, db->enc,SQLITE_AFF_NUMERIC,&pVal);
         2176  +      if( pVal && sqlite3_value_int(pVal)>0 ){
         2177  +        s.eDelete = WINDOW_RETURN_ROW;
         2178  +      }
         2179  +      sqlite3ValueFree(pVal);
         2180  +      break;
         2181  +    }
         2182  +    case TK_UNBOUNDED:
         2183  +      if( windowCacheFrame(pMWin)==0 ){
         2184  +        if( pMWin->eEnd==TK_PRECEDING ){
         2185  +          s.eDelete = WINDOW_AGGSTEP;
         2186  +        }else{
         2187  +          s.eDelete = WINDOW_RETURN_ROW;
         2188  +        }
         2189  +      }
         2190  +      break;
         2191  +    default:
         2192  +      s.eDelete = WINDOW_AGGINVERSE;
         2193  +      break;
         2194  +  }
  2156   2195   
  2157   2196     /* Allocate registers for the array of values from the sub-query, the
  2158   2197     ** samve values in record form, and the rowid used to insert said record
  2159   2198     ** into the ephemeral table.  */
  2160   2199     regNew = pParse->nMem+1;
  2161   2200     pParse->nMem += nInput;
  2162   2201     regRecord = ++pParse->nMem;