SQLite
Check-in [bb915854d4]
Not logged in
Overview
Comment:Improve comments and code legibility in new file window.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256:bb915854d435bdd78f141d70e23527e97922ec176acd3ed8060c78dffc96bab8
User & Date: dan 2018-06-14 14:27:05
Context
2018-06-14
14:30
Merge latest trunk changes into this branch. check-in: 5cf5f1808a user: dan tags: exp-window-functions
14:27
Improve comments and code legibility in new file window.c. check-in: bb915854d4 user: dan tags: exp-window-functions
2018-06-13
20:29
Fix problems with "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" window frames. check-in: c34f31dbd7 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

  1232   1232     if( regInvArg ){
  1233   1233       windowAggStep(pParse, pMWin, pMWin->iEphCsr, 1, regInvArg, regInvSize);
  1234   1234     }
  1235   1235     sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, addr);
  1236   1236     sqlite3VdbeJumpHere(v, addr+1);   /* The OP_Goto */
  1237   1237   }
  1238   1238   
         1239  +/*
         1240  +** Generate code to set the accumulator register for each window function
         1241  +** in the linked list passed as the second argument to NULL. And perform
         1242  +** any equivalent initialization required by any built-in window functions
         1243  +** in the list.
         1244  +*/
  1239   1245   static int windowInitAccum(Parse *pParse, Window *pMWin){
  1240   1246     Vdbe *v = sqlite3GetVdbe(pParse);
  1241   1247     int regArg;
  1242   1248     int nArg = 0;
  1243   1249     Window *pWin;
  1244   1250     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1245   1251       sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
................................................................................
  1254   1260     regArg = pParse->nMem+1;
  1255   1261     pParse->nMem += nArg;
  1256   1262     return regArg;
  1257   1263   }
  1258   1264   
  1259   1265   
  1260   1266   /*
         1267  +** This function does the work of sqlite3WindowCodeStep() for all "ROWS"
         1268  +** window frame types except for "BETWEEN UNBOUNDED PRECEDING AND CURRENT
         1269  +** ROW". Pseudo-code for each follows.
         1270  +**
  1261   1271   ** ROWS BETWEEN <expr1> PRECEDING AND <expr2> FOLLOWING
  1262         -** ----------------------------------------------------
  1263         -**
  1264         -** Pseudo-code for the implementation of this window frame type is as
  1265         -** follows. sqlite3WhereBegin() has already been called to generate the
  1266         -** top of the main loop when this function is called.
  1267         -**
  1268         -** Each time the sub-routine at addrGosub is invoked, a single output
  1269         -** row is generated based on the current row indicated by Window.iEphCsr.
  1270   1272   **
  1271   1273   **     ...
  1272   1274   **       if( new partition ){
  1273   1275   **         Gosub flush_partition
  1274   1276   **       }
  1275   1277   **       Insert (record in eph-table)
  1276   1278   **     sqlite3WhereEnd()
................................................................................
  1547   1549     sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  1548   1550   
  1549   1551     /* Jump to here to skip over flush_partition */
  1550   1552     sqlite3VdbeJumpHere(v, addrGoto);
  1551   1553   }
  1552   1554   
  1553   1555   /*
         1556  +** This function does the work of sqlite3WindowCodeStep() for cases that
         1557  +** would normally be handled by windowCodeDefaultStep() when there are
         1558  +** one or more built-in window-functions that require the entire partition
         1559  +** to be cached in a temp table before any rows can be returned. Additionally.
         1560  +** "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is always handled by
         1561  +** this function.
         1562  +**
         1563  +** Pseudo-code corresponding to the VM code generated by this function
         1564  +** for each type of window follows.
         1565  +**
  1554   1566   ** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  1555   1567   **
  1556   1568   **   flush_partition:
  1557   1569   **     Once {
  1558   1570   **       OpenDup (iEphCsr -> csrLead)
  1559   1571   **     }
  1560   1572   **     Integer ctr 0
................................................................................
  1577   1589   **       Next iEphCsr
  1578   1590   **     }
  1579   1591   **
  1580   1592   **     ResetSorter (csr)
  1581   1593   **     Return
  1582   1594   **
  1583   1595   ** ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  1584         -** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         1596  +**
         1597  +**   As above, except that the "if( new peer )" branch is always taken.
         1598  +**
  1585   1599   ** RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
         1600  +**
         1601  +**   As above, except that each of the for() loops becomes:
         1602  +**
         1603  +**         for(i=0; i<ctr; i++){
         1604  +**           Gosub addrGosub
         1605  +**           AggStep (xInverse, iEphCsr)
         1606  +**           Next iEphCsr
         1607  +**         }
         1608  +**
         1609  +** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         1610  +**
         1611  +**   flush_partition:
         1612  +**     Once {
         1613  +**       OpenDup (iEphCsr -> csrLead)
         1614  +**     }
         1615  +**     foreach row (csrLead) {
         1616  +**       AggStep (csrLead)
         1617  +**     }
         1618  +**     foreach row (iEphCsr) {
         1619  +**       Gosub addrGosub
         1620  +**     }
         1621  +** 
         1622  +** RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
         1623  +**
         1624  +**   flush_partition:
         1625  +**     Once {
         1626  +**       OpenDup (iEphCsr -> csrLead)
         1627  +**     }
         1628  +**     foreach row (csrLead){
         1629  +**       AggStep (csrLead)
         1630  +**     }
         1631  +**     Rewind (csrLead)
         1632  +**     Integer ctr 0
         1633  +**     foreach row (csrLead){
         1634  +**       if( new peer ){
         1635  +**         AggFinal (xValue)
         1636  +**         for(i=0; i<ctr; i++){
         1637  +**           Gosub addrGosub
         1638  +**           AggStep (xInverse, iEphCsr)
         1639  +**           Next iEphCsr
         1640  +**         }
         1641  +**         Integer ctr 0
         1642  +**       }
         1643  +**       Incr ctr
         1644  +**     }
         1645  +**
         1646  +**     AggFinal (xFinalize)
         1647  +**     for(i=0; i<ctr; i++){
         1648  +**       Gosub addrGosub
         1649  +**       Next iEphCsr
         1650  +**     }
         1651  +**
         1652  +**     ResetSorter (csr)
         1653  +**     Return
  1586   1654   */
  1587   1655   static void windowCodeCacheStep(
  1588   1656     Parse *pParse, 
  1589   1657     Select *p,
  1590   1658     WhereInfo *pWInfo,
  1591   1659     int regGosub, 
  1592   1660     int addrGosub
................................................................................
  1594   1662     Window *pMWin = p->pWin;
  1595   1663     Vdbe *v = sqlite3GetVdbe(pParse);
  1596   1664     Window *pWin;
  1597   1665     int k;
  1598   1666     int addr;
  1599   1667     ExprList *pPart = pMWin->pPartition;
  1600   1668     ExprList *pOrderBy = pMWin->pOrderBy;
  1601         -  int nPeer = pOrderBy->nExpr;
         1669  +  int nPeer = pOrderBy ? pOrderBy->nExpr : 0;
  1602   1670     int regNewPeer;
  1603   1671   
  1604   1672     int addrGoto;                   /* Address of Goto used to jump flush_par.. */
  1605   1673     int addrNext;                   /* Jump here for next iteration of loop */
  1606   1674     int regFlushPart;
  1607   1675     int lblFlushPart;
  1608   1676     int csrLead;
  1609   1677     int regCtr;
  1610   1678     int regArg;                     /* Register array to martial function args */
  1611   1679     int regSize;
  1612   1680     int nArg;
  1613         -  int bReverse;
  1614   1681     int lblEmpty;
         1682  +  int bReverse = pMWin->pOrderBy && pMWin->eStart==TK_CURRENT 
         1683  +          && pMWin->eEnd==TK_UNBOUNDED;
  1615   1684   
  1616   1685     assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT) 
  1617   1686          || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED) 
  1618   1687          || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT) 
  1619   1688          || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED) 
  1620   1689     );
  1621   1690   
  1622   1691     lblEmpty = sqlite3VdbeMakeLabel(v);
  1623         -  bReverse = (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED);
  1624   1692     regNewPeer = pParse->nMem+1;
  1625   1693     pParse->nMem += nPeer;
  1626   1694   
  1627   1695     /* Allocate register and label for the "flush_partition" sub-routine. */
  1628   1696     regFlushPart = ++pParse->nMem;
  1629   1697     lblFlushPart = sqlite3VdbeMakeLabel(v);
  1630   1698   
................................................................................
  1874   1942     Parse *pParse,                  /* Parse context */
  1875   1943     Select *p,                      /* Rewritten SELECT statement */
  1876   1944     WhereInfo *pWInfo,              /* Context returned by sqlite3WhereBegin() */
  1877   1945     int regGosub,                   /* Register for OP_Gosub */
  1878   1946     int addrGosub                   /* OP_Gosub here to return each row */
  1879   1947   ){
  1880   1948     Window *pMWin = p->pWin;
  1881         -  ExprList *pOrderBy = pMWin->pOrderBy;
  1882   1949   
  1883         -  /* Call windowCodeRowExprStep() for all "ROWS" window modes except:
         1950  +  /* There are three different functions that may be used to do the work
         1951  +  ** of this one, depending on the window frame and the specific built-in
         1952  +  ** window functions used (if any).
         1953  +  **
         1954  +  ** windowCodeRowExprStep() handles all "ROWS" window frames, except for:
         1955  +  **
         1956  +  **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         1957  +  **
         1958  +  ** The exception is because windowCodeRowExprStep() implements all window
         1959  +  ** frame types by caching the entire partition in a temp table, and
         1960  +  ** "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is easy enough to
         1961  +  ** implement without such a cache.
         1962  +  **
         1963  +  ** windowCodeCacheStep() is used for:
         1964  +  **
         1965  +  **   RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
         1966  +  **
         1967  +  ** It is also used for anything not handled by windowCodeRowExprStep() 
         1968  +  ** that invokes a built-in window function that requires the entire 
         1969  +  ** partition to be cached in a temp table before any rows are returned
         1970  +  ** (e.g. nth_value() or percent_rank()).
         1971  +  **
         1972  +  ** Finally, assuming there is no built-in window function that requires
         1973  +  ** the partition to be cached, windowCodeDefaultStep() is used for:
  1884   1974     **
         1975  +  **   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
         1976  +  **   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         1977  +  **   RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
  1885   1978     **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         1979  +  **
         1980  +  ** windowCodeDefaultStep() is the only one of the three functions that
         1981  +  ** does not cache each partition in a temp table before beginning to
         1982  +  ** return rows.
  1886   1983     */
  1887         -  if( (pMWin->eType==TK_ROWS 
  1888         -   && (pMWin->eStart!=TK_UNBOUNDED || pMWin->eEnd!=TK_CURRENT || !pOrderBy))
         1984  +  if( pMWin->eType==TK_ROWS 
         1985  +   && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy)
  1889   1986     ){
  1890   1987       windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
  1891   1988     }else{
  1892   1989       Window *pWin;
  1893         -    int bCache = 0;
         1990  +    int bCache = 0;               /* True to use CacheStep() */
  1894   1991   
  1895         -    if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED && pOrderBy ){
         1992  +    if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED ){
  1896   1993         bCache = 1;
  1897   1994       }else{
  1898         -      /* Call windowCodeCacheStep() if there is a window function that requires
  1899         -      ** that the entire partition be cached in a temp table before any rows
  1900         -      ** are returned.  */
  1901   1995         for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1902   1996           FuncDef *pFunc = pWin->pFunc;
  1903   1997           if( (pFunc->funcFlags & SQLITE_FUNC_WINDOW_SIZE)
  1904         -            || (pFunc->xSFunc==nth_valueStepFunc)
  1905         -            || (pFunc->xSFunc==first_valueStepFunc)
  1906         -            || (pFunc->xSFunc==leadStepFunc)
  1907         -            || (pFunc->xSFunc==lagStepFunc)
  1908         -          ){
         1998  +         || (pFunc->xSFunc==nth_valueStepFunc)
         1999  +         || (pFunc->xSFunc==first_valueStepFunc)
         2000  +         || (pFunc->xSFunc==leadStepFunc)
         2001  +         || (pFunc->xSFunc==lagStepFunc)
         2002  +        ){
  1909   2003             bCache = 1;
  1910   2004             break;
  1911   2005           }
  1912   2006         }
  1913   2007       }
  1914   2008   
  1915   2009       /* Otherwise, call windowCodeDefaultStep().  */