/ Check-in [a5f68f66]
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:Fix other "ROWS BETWEEN" cases on this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: a5f68f66472610b5beb4fe28669fbbfe83a32742be73cecad9b2ae28f8a17b30
User & Date: dan 2019-03-07 20:47:46
Wiki:window-functions
Context
2019-03-08
20:02
Finish consolidation of window frame code. Add untested support for GROUPS frames. check-in: 954bf369 user: dan tags: window-functions
2019-03-07
20:47
Fix other "ROWS BETWEEN" cases on this branch. check-in: a5f68f66 user: dan tags: window-functions
19:26
Modify new window functions function so that cursors are stepped immediately after each operation, instead of immediately before. check-in: 093d2b25 user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/window.c.

  1538   1538       }
  1539   1539     }
  1540   1540     regArg = pParse->nMem+1;
  1541   1541     pParse->nMem += nArg;
  1542   1542     return regArg;
  1543   1543   }
  1544   1544   
  1545         -
  1546         -/*
  1547         -** This function does the work of sqlite3WindowCodeStep() for all "ROWS"
  1548         -** window frame types except for "BETWEEN UNBOUNDED PRECEDING AND CURRENT
  1549         -** ROW". Pseudo-code for each follows.
  1550         -**
  1551         -** ROWS BETWEEN <expr1> PRECEDING AND <expr2> FOLLOWING
  1552         -**
  1553         -**     ...
  1554         -**       if( new partition ){
  1555         -**         Gosub flush_partition
  1556         -**       }
  1557         -**       Insert (record in eph-table)
  1558         -**     sqlite3WhereEnd()
  1559         -**     Gosub flush_partition
  1560         -**  
  1561         -**   flush_partition:
  1562         -**     Once {
  1563         -**       OpenDup (iEphCsr -> csrStart)
  1564         -**       OpenDup (iEphCsr -> csrEnd)
  1565         -**     }
  1566         -**     regStart = <expr1>                // PRECEDING expression
  1567         -**     regEnd = <expr2>                  // FOLLOWING expression
  1568         -**     if( regStart<0 || regEnd<0 ){ error! }
  1569         -**     Rewind (csr,csrStart,csrEnd)      // if EOF goto flush_partition_done
  1570         -**       Next(csrEnd)                    // if EOF skip Aggstep
  1571         -**       Aggstep (csrEnd)
  1572         -**       if( (regEnd--)<=0 ){
  1573         -**         AggFinal (xValue)
  1574         -**         Gosub addrGosub
  1575         -**         Next(csr)                // if EOF goto flush_partition_done
  1576         -**         if( (regStart--)<=0 ){
  1577         -**           AggInverse (csrStart)
  1578         -**           Next(csrStart)
  1579         -**         }
  1580         -**       }
  1581         -**   flush_partition_done:
  1582         -**     ResetSorter (csr)
  1583         -**     Return
  1584         -**
  1585         -** ROWS BETWEEN <expr> PRECEDING    AND CURRENT ROW
  1586         -** ROWS BETWEEN CURRENT ROW         AND <expr> FOLLOWING
  1587         -** ROWS BETWEEN UNBOUNDED PRECEDING AND <expr> FOLLOWING
  1588         -**
  1589         -**   These are similar to the above. For "CURRENT ROW", intialize the
  1590         -**   register to 0. For "UNBOUNDED PRECEDING" to infinity.
  1591         -**
  1592         -** ROWS BETWEEN <expr> PRECEDING    AND UNBOUNDED FOLLOWING
  1593         -** ROWS BETWEEN CURRENT ROW         AND UNBOUNDED FOLLOWING
  1594         -**
  1595         -**     Rewind (csr,csrStart,csrEnd)    // if EOF goto flush_partition_done
  1596         -**     while( 1 ){
  1597         -**       Next(csrEnd)                  // Exit while(1) at EOF
  1598         -**       Aggstep (csrEnd)
  1599         -**     }
  1600         -**     while( 1 ){
  1601         -**       AggFinal (xValue)
  1602         -**       Gosub addrGosub
  1603         -**       Next(csr)                     // if EOF goto flush_partition_done
  1604         -**       if( (regStart--)<=0 ){
  1605         -**         AggInverse (csrStart)
  1606         -**         Next(csrStart)
  1607         -**       }
  1608         -**     }
  1609         -**
  1610         -**   For the "CURRENT ROW AND UNBOUNDED FOLLOWING" case, the final if() 
  1611         -**   condition is always true (as if regStart were initialized to 0).
  1612         -**
  1613         -** RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  1614         -** 
  1615         -**   This is the only RANGE case handled by this routine. It modifies the
  1616         -**   second while( 1 ) loop in "ROWS BETWEEN CURRENT ... UNBOUNDED..." to
  1617         -**   be:
  1618         -**
  1619         -**     while( 1 ){
  1620         -**       AggFinal (xValue)
  1621         -**       while( 1 ){
  1622         -**         regPeer++
  1623         -**         Gosub addrGosub
  1624         -**         Next(csr)                     // if EOF goto flush_partition_done
  1625         -**         if( new peer ) break;
  1626         -**       }
  1627         -**       while( (regPeer--)>0 ){
  1628         -**         AggInverse (csrStart)
  1629         -**         Next(csrStart)
  1630         -**       }
  1631         -**     }
  1632         -**
  1633         -** ROWS BETWEEN <expr> FOLLOWING    AND <expr> FOLLOWING
  1634         -**
  1635         -**   regEnd = regEnd - regStart
  1636         -**   Rewind (csr,csrStart,csrEnd)   // if EOF goto flush_partition_done
  1637         -**     Aggstep (csrEnd)
  1638         -**     Next(csrEnd)                 // if EOF fall-through
  1639         -**     if( (regEnd--)<=0 ){
  1640         -**       if( (regStart--)<=0 ){
  1641         -**         AggFinal (xValue)
  1642         -**         Gosub addrGosub
  1643         -**         Next(csr)              // if EOF goto flush_partition_done
  1644         -**       }
  1645         -**       AggInverse (csrStart)
  1646         -**       Next (csrStart)
  1647         -**     }
  1648         -**
  1649         -** ROWS BETWEEN <expr> PRECEDING    AND <expr> PRECEDING
  1650         -**
  1651         -**   Replace the bit after "Rewind" in the above with:
  1652         -**
  1653         -**     if( (regEnd--)<=0 ){
  1654         -**       AggStep (csrEnd)
  1655         -**       Next (csrEnd)
  1656         -**     }
  1657         -**     AggFinal (xValue)
  1658         -**     Gosub addrGosub
  1659         -**     Next(csr)                  // if EOF goto flush_partition_done
  1660         -**     if( (regStart--)<=0 ){
  1661         -**       AggInverse (csr2)
  1662         -**       Next (csr2)
  1663         -**     }
  1664         -**
  1665         -*/
  1666         -static void windowCodeRowExprStep(
  1667         -  Parse *pParse, 
  1668         -  Select *p,
  1669         -  WhereInfo *pWInfo,
  1670         -  int regGosub, 
  1671         -  int addrGosub
  1672         -){
  1673         -  Window *pMWin = p->pWin;
  1674         -  Vdbe *v = sqlite3GetVdbe(pParse);
  1675         -  int regFlushPart;               /* Register for "Gosub flush_partition" */
  1676         -  int lblFlushPart;               /* Label for "Gosub flush_partition" */
  1677         -  int lblFlushDone;               /* Label for "Gosub flush_partition_done" */
  1678         -
  1679         -  int regArg;
  1680         -  int addr;
  1681         -  int csrStart = pParse->nTab++;
  1682         -  int csrEnd = pParse->nTab++;
  1683         -  int regStart;                    /* Value of <expr> PRECEDING */
  1684         -  int regEnd;                      /* Value of <expr> FOLLOWING */
  1685         -  int addrGoto;
  1686         -  int addrTop;
  1687         -  int addrIfPos1 = 0;
  1688         -  int addrIfPos2 = 0;
  1689         -  int regSize = 0;
  1690         -
  1691         -  assert( pMWin->eStart==TK_PRECEDING 
  1692         -       || pMWin->eStart==TK_CURRENT 
  1693         -       || pMWin->eStart==TK_FOLLOWING 
  1694         -       || pMWin->eStart==TK_UNBOUNDED 
  1695         -  );
  1696         -  assert( pMWin->eEnd==TK_FOLLOWING 
  1697         -       || pMWin->eEnd==TK_CURRENT 
  1698         -       || pMWin->eEnd==TK_UNBOUNDED 
  1699         -       || pMWin->eEnd==TK_PRECEDING 
  1700         -  );
  1701         -
  1702         -  /* Allocate register and label for the "flush_partition" sub-routine. */
  1703         -  regFlushPart = ++pParse->nMem;
  1704         -  lblFlushPart = sqlite3VdbeMakeLabel(pParse);
  1705         -  lblFlushDone = sqlite3VdbeMakeLabel(pParse);
  1706         -
  1707         -  regStart = ++pParse->nMem;
  1708         -  regEnd = ++pParse->nMem;
  1709         -
  1710         -  windowPartitionCache(pParse, p, pWInfo, regFlushPart, lblFlushPart, &regSize);
  1711         -
  1712         -  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
  1713         -
  1714         -  /* Start of "flush_partition" */
  1715         -  sqlite3VdbeResolveLabel(v, lblFlushPart);
  1716         -  sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3);
  1717         -  VdbeCoverage(v);
  1718         -  VdbeComment((v, "Flush_partition subroutine"));
  1719         -  sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr);
  1720         -  sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr);
  1721         -
  1722         -  /* If either regStart or regEnd are not non-negative integers, throw 
  1723         -  ** an exception.  */
  1724         -  if( pMWin->pStart ){
  1725         -    sqlite3ExprCode(pParse, pMWin->pStart, regStart);
  1726         -    windowCheckIntValue(pParse, regStart, 0);
  1727         -  }
  1728         -  if( pMWin->pEnd ){
  1729         -    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  1730         -    windowCheckIntValue(pParse, regEnd, 1);
  1731         -  }
  1732         -
  1733         -  /* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> FOLLOWING", do:
  1734         -  **
  1735         -  **   if( regEnd<regStart ){
  1736         -  **     // The frame always consists of 0 rows
  1737         -  **     regStart = regSize;
  1738         -  **   }
  1739         -  **   regEnd = regEnd - regStart;
  1740         -  */
  1741         -  if( pMWin->pEnd && pMWin->eStart==TK_FOLLOWING ){
  1742         -    assert( pMWin->pStart!=0 );
  1743         -    assert( pMWin->eEnd==TK_FOLLOWING );
  1744         -    sqlite3VdbeAddOp3(v, OP_Ge, regStart, sqlite3VdbeCurrentAddr(v)+2, regEnd);
  1745         -    VdbeCoverageNeverNull(v);
  1746         -    sqlite3VdbeAddOp2(v, OP_Copy, regSize, regStart);
  1747         -    sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regEnd);
  1748         -  }
  1749         -
  1750         -  if( pMWin->pStart && pMWin->eEnd==TK_PRECEDING ){
  1751         -    assert( pMWin->pEnd!=0 );
  1752         -    assert( pMWin->eStart==TK_PRECEDING );
  1753         -    sqlite3VdbeAddOp3(v, OP_Le, regStart, sqlite3VdbeCurrentAddr(v)+3, regEnd);
  1754         -    VdbeCoverageNeverNull(v);
  1755         -    sqlite3VdbeAddOp2(v, OP_Copy, regSize, regStart);
  1756         -    sqlite3VdbeAddOp2(v, OP_Copy, regSize, regEnd);
  1757         -  }
  1758         -
  1759         -  /* Initialize the accumulator register for each window function to NULL */
  1760         -  regArg = windowInitAccum(pParse, pMWin);
  1761         -
  1762         -  sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, lblFlushDone);
  1763         -  VdbeCoverage(v);
  1764         -  sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, lblFlushDone);
  1765         -  VdbeCoverageNeverTaken(v);
  1766         -  sqlite3VdbeChangeP5(v, 1);
  1767         -  sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, lblFlushDone);
  1768         -  VdbeCoverageNeverTaken(v);
  1769         -  sqlite3VdbeChangeP5(v, 1);
  1770         -
  1771         -  /* Invoke AggStep function for each window function using the row that
  1772         -  ** csrEnd currently points to. Or, if csrEnd is already at EOF,
  1773         -  ** do nothing.  */
  1774         -  addrTop = sqlite3VdbeCurrentAddr(v);
  1775         -  if( pMWin->eEnd==TK_PRECEDING ){
  1776         -    addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0 , 1);
  1777         -    VdbeCoverage(v);
  1778         -  }
  1779         -  sqlite3VdbeAddOp2(v, OP_Next, csrEnd, sqlite3VdbeCurrentAddr(v)+2);
  1780         -  VdbeCoverage(v);
  1781         -  addr = sqlite3VdbeAddOp0(v, OP_Goto);
  1782         -  windowAggStep(pParse, pMWin, csrEnd, 0, regArg, regSize);
  1783         -  if( pMWin->eEnd==TK_UNBOUNDED ){
  1784         -    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);
  1785         -    sqlite3VdbeJumpHere(v, addr);
  1786         -    addrTop = sqlite3VdbeCurrentAddr(v);
  1787         -  }else{
  1788         -    sqlite3VdbeJumpHere(v, addr);
  1789         -    if( pMWin->eEnd==TK_PRECEDING ){
  1790         -      sqlite3VdbeJumpHere(v, addrIfPos1);
  1791         -    }
  1792         -  }
  1793         -
  1794         -  if( pMWin->eEnd==TK_FOLLOWING ){
  1795         -    addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0 , 1);
  1796         -    VdbeCoverage(v);
  1797         -  }
  1798         -  if( pMWin->eStart==TK_FOLLOWING ){
  1799         -    addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0 , 1);
  1800         -    VdbeCoverage(v);
  1801         -  }
  1802         -  windowAggFinal(pParse, pMWin, 0);
  1803         -  windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  1804         -  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)+2);
  1805         -  VdbeCoverage(v);
  1806         -  sqlite3VdbeAddOp2(v, OP_Goto, 0, lblFlushDone);
  1807         -  if( pMWin->eStart==TK_FOLLOWING ){
  1808         -    sqlite3VdbeJumpHere(v, addrIfPos2);
  1809         -  }
  1810         -
  1811         -  if( pMWin->eStart==TK_CURRENT 
  1812         -   || pMWin->eStart==TK_PRECEDING 
  1813         -   || pMWin->eStart==TK_FOLLOWING 
  1814         -  ){
  1815         -    int lblSkipInverse = sqlite3VdbeMakeLabel(pParse);;
  1816         -    if( pMWin->eStart==TK_PRECEDING ){
  1817         -      sqlite3VdbeAddOp3(v, OP_IfPos, regStart, lblSkipInverse, 1);
  1818         -      VdbeCoverage(v);
  1819         -    }
  1820         -    if( pMWin->eStart==TK_FOLLOWING ){
  1821         -      sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+2);
  1822         -      VdbeCoverage(v);
  1823         -      sqlite3VdbeAddOp2(v, OP_Goto, 0, lblSkipInverse);
  1824         -    }else{
  1825         -      sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
  1826         -      VdbeCoverageAlwaysTaken(v);
  1827         -    }
  1828         -    windowAggStep(pParse, pMWin, csrStart, 1, regArg, regSize);
  1829         -    sqlite3VdbeResolveLabel(v, lblSkipInverse);
  1830         -  }
  1831         -  if( pMWin->eEnd==TK_FOLLOWING ){
  1832         -    sqlite3VdbeJumpHere(v, addrIfPos1);
  1833         -  }
  1834         -  sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);
  1835         -
  1836         -  /* flush_partition_done: */
  1837         -  sqlite3VdbeResolveLabel(v, lblFlushDone);
  1838         -  sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
  1839         -  sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  1840         -  VdbeComment((v, "end flush_partition subroutine"));
  1841         -
  1842         -  /* Jump to here to skip over flush_partition */
  1843         -  sqlite3VdbeJumpHere(v, addrGoto);
  1844         -}
  1845         -
  1846   1545   /* 
  1847   1546   ** Return true if the entire partition should be cached in the ephemeral
  1848   1547   ** table before processing any rows.
  1849   1548   */
  1850   1549   static int windowCachePartition(Window *pMWin){
  1851   1550     Window *pWin;
  1852   1551     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
................................................................................
  1880   1579   static int windowCodeOp(
  1881   1580    WindowCodeArg *p,
  1882   1581    int op,
  1883   1582    int csr,
  1884   1583    int regCountdown,
  1885   1584    int jumpOnEof
  1886   1585   ){
         1586  +  Window *pMWin = p->pMWin;
  1887   1587     int ret = 0;
  1888   1588     Vdbe *v = p->pVdbe;
  1889   1589     int addrIf = 0; 
         1590  +
         1591  +  /* Special case - WINDOW_AGGINVERSE is always a no-op if the frame
         1592  +  ** starts with UNBOUNDED PRECEDING. */
         1593  +  if( op==WINDOW_AGGINVERSE && pMWin->eStart==TK_UNBOUNDED ){
         1594  +    assert( regCountdown==0 && jumpOnEof==0 );
         1595  +    return 0;
         1596  +  }
  1890   1597   
  1891   1598     if( regCountdown>0 ){
  1892   1599       addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
  1893   1600     }
  1894   1601   
  1895   1602     switch( op ){
  1896   1603       case WINDOW_RETURN_ROW:
  1897         -      windowAggFinal(p->pParse, p->pMWin, 0);
  1898         -      windowReturnOneRow(p->pParse, p->pMWin, p->regGosub, p->addrGosub);
         1604  +      windowAggFinal(p->pParse, pMWin, 0);
         1605  +      windowReturnOneRow(p->pParse, pMWin, p->regGosub, p->addrGosub);
  1899   1606         break;
  1900   1607   
  1901   1608       case WINDOW_AGGINVERSE:
  1902         -      windowAggStep(p->pParse, p->pMWin, csr, 1, p->regArg, p->pMWin->regSize);
         1609  +      windowAggStep(p->pParse, pMWin, csr, 1, p->regArg, pMWin->regSize);
  1903   1610         break;
  1904   1611   
  1905   1612       case WINDOW_AGGSTEP:
  1906         -      windowAggStep(p->pParse, p->pMWin, csr, 0, p->regArg, p->pMWin->regSize);
         1613  +      windowAggStep(p->pParse, pMWin, csr, 0, p->regArg, pMWin->regSize);
  1907   1614         break;
  1908   1615     }
  1909   1616   
  1910   1617     if( jumpOnEof ){
  1911   1618       sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
  1912   1619       ret = sqlite3VdbeAddOp0(v, OP_Goto);
  1913   1620     }else{
................................................................................
  2002   1709   
  2003   1710     int regArg;
  2004   1711     int csrCurrent = pMWin->iEphCsr;
  2005   1712     int csrWrite = csrCurrent+1;
  2006   1713     int csrStart = csrCurrent+2;
  2007   1714     int csrEnd = csrCurrent+3;
  2008   1715   
  2009         -  int regStart;                    /* Value of <expr> PRECEDING */
  2010         -  int regEnd;                      /* Value of <expr> FOLLOWING */
  2011   1716   
  2012   1717     int iSubCsr = p->pSrc->a[0].iCursor;      /* Cursor of sub-select */
  2013   1718     int nSub = p->pSrc->a[0].pTab->nCol;      /* Number of cols returned by sub */
  2014   1719     int iCol;                                 /* To iterate through sub cols */
  2015   1720   
  2016   1721     int addrGoto;
  2017   1722     int addrIf;
................................................................................
  2021   1726     int addrCacheNext;
  2022   1727   
  2023   1728     int addrShortcut = 0;
  2024   1729     int addrEmpty = 0;
  2025   1730   
  2026   1731     int bCache = windowCachePartition(pMWin);
  2027   1732   
         1733  +  int regStart = 0;               /* Value of <expr> PRECEDING */
         1734  +  int regEnd = 0;                 /* Value of <expr> FOLLOWING */
         1735  +
  2028   1736     int reg = pParse->nMem+1;
  2029   1737     int regRecord = reg+nSub;
  2030   1738     int regRowid = regRecord+1;
  2031   1739     WindowCodeArg s;
  2032   1740   
  2033   1741     memset(&s, 0, sizeof(WindowCodeArg));
  2034   1742     s.pParse = pParse;
................................................................................
  2036   1744     s.pVdbe = v;
  2037   1745     s.regGosub = regGosub;
  2038   1746     s.addrGosub = addrGosub;
  2039   1747   
  2040   1748     pParse->nMem += 1 + nSub + 1;
  2041   1749   
  2042   1750     regFlushPart = ++pParse->nMem;
         1751  +
         1752  +  if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
  2043   1753     regStart = ++pParse->nMem;
         1754  +  }
         1755  +  if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
  2044   1756     regEnd = ++pParse->nMem;
         1757  +  }
  2045   1758   
  2046   1759     assert( pMWin->eStart==TK_PRECEDING 
  2047   1760          || pMWin->eStart==TK_CURRENT 
  2048   1761          || pMWin->eStart==TK_FOLLOWING 
  2049   1762          || pMWin->eStart==TK_UNBOUNDED 
  2050   1763     );
  2051   1764     assert( pMWin->eEnd==TK_FOLLOWING 
................................................................................
  2103   1816     }else{
  2104   1817       addrIf = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);
  2105   1818     }
  2106   1819   
  2107   1820     /* This block is run for the first row of each partition */
  2108   1821     s.regArg = regArg = windowInitAccum(pParse, pMWin);
  2109   1822   
         1823  +  if( regStart ){
  2110   1824     sqlite3ExprCode(pParse, pMWin->pStart, regStart);
  2111   1825     windowCheckIntValue(pParse, regStart, 0);
         1826  +  }
         1827  +  if( regEnd ){
  2112   1828     sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  2113   1829     windowCheckIntValue(pParse, regEnd, 1);
         1830  +  }
  2114   1831   
  2115         -  if( pMWin->eStart==pMWin->eEnd 
  2116         -   && pMWin->eStart!=TK_CURRENT && pMWin->eStart!=TK_UNBOUNDED 
  2117         -  ){
         1832  +  if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
  2118   1833       int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
  2119   1834       int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
  2120   1835       windowAggFinal(pParse, pMWin, 0);
  2121   1836       if( bCache ){
  2122   1837         sqlite3VdbeAddOp2(v, OP_Rowid, csrWrite, regRowid);
  2123   1838         sqlite3VdbeAddOp3(v, OP_NotExists, csrCurrent, 0, regRowid);
  2124   1839         windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
................................................................................
  2127   1842         sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
  2128   1843         windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  2129   1844         sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
  2130   1845       }
  2131   1846       addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
  2132   1847       sqlite3VdbeJumpHere(v, addrGe);
  2133   1848     }
  2134         -  if( pMWin->eStart==TK_FOLLOWING ){
         1849  +  if( pMWin->eStart==TK_FOLLOWING && regEnd ){
         1850  +    assert( pMWin->eEnd==TK_FOLLOWING );
  2135   1851       sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  2136   1852     }
  2137   1853   
         1854  +  if( pMWin->eStart!=TK_UNBOUNDED ){
  2138   1855     sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1);
         1856  +  }
  2139   1857     sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
  2140   1858     sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1);
  2141   1859   
  2142   1860     sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  2143   1861     addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
  2144   1862   
  2145   1863     /* Begin generating SECOND_ROW_CODE */
................................................................................
  2147   1865     if( bCache ){
  2148   1866       addrCacheNext = sqlite3VdbeCurrentAddr(v);
  2149   1867     }else{
  2150   1868       sqlite3VdbeJumpHere(v, addrIf);
  2151   1869     }
  2152   1870     if( pMWin->eStart==TK_FOLLOWING ){
  2153   1871       windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
         1872  +    if( pMWin->eEnd!=TK_UNBOUNDED ){
  2154   1873       windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 0);
  2155   1874       windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
         1875  +    }
  2156   1876     }else
  2157   1877     if( pMWin->eEnd==TK_PRECEDING ){
  2158   1878       windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
  2159   1879       windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
  2160   1880       windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
  2161   1881     }else{
  2162   1882       int addr;
  2163   1883       windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
  2164         -    addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
         1884  +    if( pMWin->eEnd!=TK_UNBOUNDED ){
         1885  +      if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
  2165   1886       windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
  2166   1887       windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
  2167         -    sqlite3VdbeJumpHere(v, addr);
         1888  +      if( regEnd ) sqlite3VdbeJumpHere(v, addr);
         1889  +    }
  2168   1890     }
  2169   1891     VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));
  2170   1892   
  2171   1893     /* End of the main input loop */
  2172   1894     sqlite3VdbeJumpHere(v, addrGoto);
  2173   1895     if( bCache ){
  2174   1896       sqlite3VdbeAddOp2(v, OP_Next, csrWrite, addrCacheNext);
................................................................................
  2191   1913       windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
  2192   1914     }else if( pMWin->eStart==TK_FOLLOWING ){
  2193   1915       int addrStart;
  2194   1916       int addrBreak1;
  2195   1917       int addrBreak2;
  2196   1918       int addrBreak3;
  2197   1919       windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
         1920  +    if( pMWin->eEnd==TK_UNBOUNDED ){
         1921  +      addrStart = sqlite3VdbeCurrentAddr(v);
         1922  +      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regStart, 1);
         1923  +      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, 0, 1);
         1924  +    }else{
         1925  +      assert( pMWin->eEnd==TK_FOLLOWING );
  2198   1926       addrStart = sqlite3VdbeCurrentAddr(v);
  2199   1927       addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 1);
  2200   1928       addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 1);
         1929  +    }
  2201   1930       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
  2202   1931       sqlite3VdbeJumpHere(v, addrBreak2);
  2203   1932       addrStart = sqlite3VdbeCurrentAddr(v);
  2204   1933       addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
  2205   1934       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
  2206   1935       sqlite3VdbeJumpHere(v, addrBreak1);
  2207   1936       sqlite3VdbeJumpHere(v, addrBreak3);
................................................................................
  2684   2413     **   RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
  2685   2414     **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2686   2415     **
  2687   2416     ** windowCodeDefaultStep() is the only one of the three functions that
  2688   2417     ** does not cache each partition in a temp table before beginning to
  2689   2418     ** return rows.
  2690   2419     */
  2691         -  if( pMWin->eType==TK_ROWS 
  2692         -   && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy)
  2693         -  ){
  2694         -    int bCache = windowCachePartition(pMWin);
  2695         -    if( (pMWin->eEnd!=TK_FOLLOWING   && pMWin->eEnd!=TK_PRECEDING) 
  2696         -     || (pMWin->eStart!=TK_FOLLOWING && pMWin->eStart!=TK_PRECEDING) 
  2697         -    ){
  2698         -      VdbeModuleComment((pParse->pVdbe, "Begin RowExprStep()"));
  2699         -      windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
  2700         -      VdbeModuleComment((pParse->pVdbe, "End RowExprStep()"));
  2701         -    }else{
         2420  +  if( pMWin->eType==TK_ROWS ){
  2702   2421         VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()"));
  2703   2422         windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
  2704   2423         VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()"));
  2705         -    }
  2706   2424     }else{
  2707   2425       Window *pWin;
  2708   2426       int bCache = 0;               /* True to use CacheStep() */
  2709   2427   
  2710   2428       if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED ){
  2711   2429         bCache = 1;
  2712   2430       }else{