/ Check-in [954bf369]
Login

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

Overview
Comment:Finish consolidation of window frame code. Add untested support for GROUPS frames.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256:954bf369935083c188c3b14e77ed89fc5ec4323cc5b0c67e4a2e48fcc278df45
User & Date: dan 2019-03-08 20:02:52
Wiki:window-functions
Context
2019-03-08
20:57
Add simple tests for GROUPS window frames. check-in: 2872702d user: dan tags: window-functions
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1695   1695   }
  1696   1696   frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z). { 
  1697   1697     A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
  1698   1698   }
  1699   1699   
  1700   1700   range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
  1701   1701   range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }
         1702  +range_or_rows(A) ::= GROUPS.  { A = TK_GROUPS;}
  1702   1703   
  1703   1704   
  1704   1705   frame_bound_s(A) ::= frame_bound(X). { A = X; }
  1705   1706   frame_bound_s(A) ::= UNBOUNDED PRECEDING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
  1706   1707   frame_bound_e(A) ::= frame_bound(X). { A = X; }
  1707   1708   frame_bound_e(A) ::= UNBOUNDED FOLLOWING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
  1708   1709   

Changes to src/window.c.

   910    910     int eEnd,         /* End type: CURRENT, FOLLOWING, TK_UNBOUNDED, PRECEDING */
   911    911     Expr *pEnd        /* End window size if TK_FOLLOWING or PRECEDING */
   912    912   ){
   913    913     Window *pWin = 0;
   914    914     int bImplicitFrame = 0;
   915    915   
   916    916     /* Parser assures the following: */
   917         -  assert( eType==0 || eType==TK_RANGE || eType==TK_ROWS );
          917  +  assert( eType==0 || eType==TK_RANGE || eType==TK_ROWS || eType==TK_GROUPS );
   918    918     assert( eStart==TK_CURRENT || eStart==TK_PRECEDING
   919    919              || eStart==TK_UNBOUNDED || eStart==TK_FOLLOWING );
   920    920     assert( eEnd==TK_CURRENT || eEnd==TK_FOLLOWING
   921    921              || eEnd==TK_UNBOUNDED || eEnd==TK_PRECEDING );
   922    922     assert( (eStart==TK_PRECEDING || eStart==TK_FOLLOWING)==(pStart!=0) );
   923    923     assert( (eEnd==TK_FOLLOWING || eEnd==TK_PRECEDING)==(pEnd!=0) );
   924    924   
................................................................................
  1323   1323                                pWin->regResult);
  1324   1324           sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1325   1325         }
  1326   1326       }
  1327   1327     }
  1328   1328   }
  1329   1329   
  1330         -/*
  1331         -** This function generates VM code to invoke the sub-routine at address
  1332         -** lblFlushPart once for each partition with the entire partition cached in
  1333         -** the Window.iEphCsr temp table.
  1334         -*/
  1335         -static void windowPartitionCache(
  1336         -  Parse *pParse,
  1337         -  Select *p,                      /* The rewritten SELECT statement */
  1338         -  WhereInfo *pWInfo,              /* WhereInfo to call WhereEnd() on */
  1339         -  int regFlushPart,               /* Register to use with Gosub lblFlushPart */
  1340         -  int lblFlushPart,               /* Subroutine to Gosub to */
  1341         -  int *pRegSize                   /* OUT: Register containing partition size */
  1342         -){
  1343         -  Window *pMWin = p->pWin;
  1344         -  Vdbe *v = sqlite3GetVdbe(pParse);
  1345         -  int iSubCsr = p->pSrc->a[0].iCursor;
  1346         -  int nSub = p->pSrc->a[0].pTab->nCol;
  1347         -  int k;
  1348         -
  1349         -  int reg = pParse->nMem+1;
  1350         -  int regRecord = reg+nSub;
  1351         -  int regRowid = regRecord+1;
  1352         -
  1353         -  *pRegSize = regRowid;
  1354         -  pParse->nMem += nSub + 2;
  1355         -
  1356         -  /* Load the column values for the row returned by the sub-select
  1357         -  ** into an array of registers starting at reg. */
  1358         -  for(k=0; k<nSub; k++){
  1359         -    sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
  1360         -  }
  1361         -  sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord);
  1362         -
  1363         -  /* Check if this is the start of a new partition. If so, call the
  1364         -  ** flush_partition sub-routine.  */
  1365         -  if( pMWin->pPartition ){
  1366         -    int addr;
  1367         -    ExprList *pPart = pMWin->pPartition;
  1368         -    int nPart = pPart->nExpr;
  1369         -    int regNewPart = reg + pMWin->nBufferCol;
  1370         -    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
  1371         -
  1372         -    addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
  1373         -    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  1374         -    sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2);
  1375         -    VdbeCoverageEqNe(v);
  1376         -    sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
  1377         -    sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart);
  1378         -    VdbeComment((v, "call flush_partition"));
  1379         -  }
  1380         -
  1381         -  /* Buffer the current row in the ephemeral table. */
  1382         -  sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
  1383         -  sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);
  1384         -
  1385         -  /* End of the input loop */
  1386         -  sqlite3WhereEnd(pWInfo);
  1387         -
  1388         -  /* Invoke "flush_partition" to deal with the final (or only) partition */
  1389         -  sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart);
  1390         -  VdbeComment((v, "call flush_partition"));
  1391         -}
  1392         -
  1393   1330   /*
  1394   1331   ** Invoke the sub-routine at regGosub (generated by code in select.c) to
  1395   1332   ** return the current row of Window.iEphCsr. If all window functions are
  1396   1333   ** aggregate window functions that use the standard API, a single
  1397   1334   ** OP_Gosub instruction is all that this routine generates. Extra VM code
  1398   1335   ** for per-row processing is only generated for the following built-in window
  1399   1336   ** functions:
................................................................................
  1466   1403         sqlite3VdbeResolveLabel(v, lbl);
  1467   1404         sqlite3ReleaseTempReg(pParse, tmpReg);
  1468   1405       }
  1469   1406     }
  1470   1407     sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  1471   1408   }
  1472   1409   
  1473         -/*
  1474         -** Invoke the code generated by windowReturnOneRow() and, optionally, the
  1475         -** xInverse() function for each window function, for one or more rows
  1476         -** from the Window.iEphCsr temp table. This routine generates VM code
  1477         -** similar to:
  1478         -**
  1479         -**   while( regCtr>0 ){
  1480         -**     regCtr--;
  1481         -**     windowReturnOneRow()
  1482         -**     if( bInverse ){
  1483         -**       AggInverse
  1484         -**     }
  1485         -**     Next (Window.iEphCsr)
  1486         -**   }
  1487         -*/
  1488         -static void windowReturnRows(
  1489         -  Parse *pParse,
  1490         -  Window *pMWin,                  /* List of window functions */
  1491         -  int regCtr,                     /* Register containing number of rows */
  1492         -  int regGosub,                   /* Register for Gosub addrGosub */
  1493         -  int addrGosub,                  /* Address of sub-routine for ReturnOneRow */
  1494         -  int regInvArg,                  /* Array of registers for xInverse args */
  1495         -  int regInvSize                  /* Register containing size of partition */
  1496         -){
  1497         -  int addr;
  1498         -  Vdbe *v = sqlite3GetVdbe(pParse);
  1499         -  windowAggFinal(pParse, pMWin, 0);
  1500         -  addr = sqlite3VdbeAddOp3(v, OP_IfPos, regCtr, sqlite3VdbeCurrentAddr(v)+2 ,1);
  1501         -  VdbeCoverage(v);
  1502         -  sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
  1503         -  windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  1504         -  if( regInvArg ){
  1505         -    windowAggStep(pParse, pMWin, pMWin->iEphCsr, 1, regInvArg, regInvSize);
  1506         -  }
  1507         -  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, addr);
  1508         -  VdbeCoverage(v);
  1509         -  sqlite3VdbeJumpHere(v, addr+1);   /* The OP_Goto */
  1510         -}
  1511         -
  1512   1410   /*
  1513   1411   ** Generate code to set the accumulator register for each window function
  1514   1412   ** in the linked list passed as the second argument to NULL. And perform
  1515   1413   ** any equivalent initialization required by any built-in window functions
  1516   1414   ** in the list.
  1517   1415   */
  1518   1416   static int windowInitAccum(Parse *pParse, Window *pMWin){
................................................................................
  1557   1455        || (pFunc->zName==lagName)
  1558   1456       ){
  1559   1457         return 1;
  1560   1458       }
  1561   1459     }
  1562   1460     return 0;
  1563   1461   }
         1462  +
         1463  +/*
         1464  +** regOld and regNew are each the first register in an array of size
         1465  +** pOrderBy->nExpr. This function generates code to compare the two
         1466  +** arrays of registers using the collation sequences and other comparison
         1467  +** parameters specified by pOrderBy. 
         1468  +**
         1469  +** If the two arrays are not equal, the contents of regNew is copied to 
         1470  +** regOld and control falls through. Otherwise, if the contents of the arrays
         1471  +** are equal, an OP_Goto is executed. The address of the OP_Goto is returned.
         1472  +*/
         1473  +static int windowIfNewPeer(
         1474  +  Parse *pParse,
         1475  +  ExprList *pOrderBy,
         1476  +  int regNew,                     /* First in array of new values */
         1477  +  int regOld                      /* First in array of old values */
         1478  +){
         1479  +  Vdbe *v = sqlite3GetVdbe(pParse);
         1480  +  int addr;
         1481  +  if( pOrderBy ){
         1482  +    int nVal = pOrderBy->nExpr;
         1483  +    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
         1484  +    sqlite3VdbeAddOp3(v, OP_Compare, regOld, regNew, nVal);
         1485  +    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
         1486  +    addr = sqlite3VdbeAddOp3(
         1487  +        v, OP_Jump, sqlite3VdbeCurrentAddr(v)+1, 0, sqlite3VdbeCurrentAddr(v)+1
         1488  +        );
         1489  +    VdbeCoverageEqNe(v);
         1490  +    sqlite3VdbeAddOp3(v, OP_Copy, regNew, regOld, nVal-1);
         1491  +  }else{
         1492  +    addr = sqlite3VdbeAddOp0(v, OP_Goto);
         1493  +  }
         1494  +  return addr;
         1495  +}
  1564   1496   
  1565   1497   typedef struct WindowCodeArg WindowCodeArg;
         1498  +typedef struct WindowCsrAndReg WindowCsrAndReg;
         1499  +struct WindowCsrAndReg {
         1500  +  int csr;
         1501  +  int reg;
         1502  +};
  1566   1503   struct WindowCodeArg {
  1567   1504     Parse *pParse;
  1568   1505     Window *pMWin;
  1569   1506     Vdbe *pVdbe;
  1570   1507     int regGosub;
  1571   1508     int addrGosub;
  1572   1509     int regArg;
         1510  +
         1511  +  WindowCsrAndReg start;
         1512  +  WindowCsrAndReg current;
         1513  +  WindowCsrAndReg end;
  1573   1514   };
  1574   1515   
  1575   1516   #define WINDOW_RETURN_ROW 1
  1576   1517   #define WINDOW_AGGINVERSE 2
  1577   1518   #define WINDOW_AGGSTEP    3
         1519  +
         1520  +/*
         1521  +** Generate VM code to read the window frames peer values from cursor csr into
         1522  +** an array of registers starting at reg.
         1523  +*/
         1524  +static void windowReadPeerValues(
         1525  +  WindowCodeArg *p,
         1526  +  int csr,
         1527  +  int reg
         1528  +){
         1529  +  Window *pMWin = p->pMWin;
         1530  +  ExprList *pOrderBy = pMWin->pOrderBy;
         1531  +  if( pOrderBy ){
         1532  +    Vdbe *v = sqlite3GetVdbe(p->pParse);
         1533  +    ExprList *pPart = pMWin->pPartition;
         1534  +    int iColOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0);
         1535  +    int i;
         1536  +    for(i=0; i<pOrderBy->nExpr; i++){
         1537  +      sqlite3VdbeAddOp3(v, OP_Column, csr, iColOff+i, reg+i);
         1538  +    }
         1539  +  }
         1540  +}
  1578   1541   
  1579   1542   static int windowCodeOp(
  1580   1543    WindowCodeArg *p,
  1581   1544    int op,
  1582         - int csr,
  1583   1545    int regCountdown,
  1584   1546    int jumpOnEof
  1585   1547   ){
         1548  +  int csr, reg;
         1549  +  Parse *pParse = p->pParse;
  1586   1550     Window *pMWin = p->pMWin;
  1587   1551     int ret = 0;
  1588   1552     Vdbe *v = p->pVdbe;
  1589   1553     int addrIf = 0; 
         1554  +  int addrContinue = 0;
         1555  +  int addrGoto = 0;
         1556  +  int bPeer = (pMWin->eType!=TK_ROWS);
  1590   1557   
  1591   1558     /* Special case - WINDOW_AGGINVERSE is always a no-op if the frame
  1592   1559     ** starts with UNBOUNDED PRECEDING. */
  1593   1560     if( op==WINDOW_AGGINVERSE && pMWin->eStart==TK_UNBOUNDED ){
  1594   1561       assert( regCountdown==0 && jumpOnEof==0 );
  1595   1562       return 0;
  1596   1563     }
  1597   1564   
  1598   1565     if( regCountdown>0 ){
  1599   1566       addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
  1600   1567     }
  1601   1568   
         1569  +  if( op==WINDOW_RETURN_ROW ){
         1570  +    windowAggFinal(pParse, pMWin, 0);
         1571  +  }
         1572  +  addrContinue = sqlite3VdbeCurrentAddr(v);
  1602   1573     switch( op ){
  1603   1574       case WINDOW_RETURN_ROW:
  1604         -      windowAggFinal(p->pParse, pMWin, 0);
  1605         -      windowReturnOneRow(p->pParse, pMWin, p->regGosub, p->addrGosub);
         1575  +      csr = p->current.csr;
         1576  +      reg = p->current.reg;
         1577  +      windowReturnOneRow(pParse, pMWin, p->regGosub, p->addrGosub);
  1606   1578         break;
  1607   1579   
  1608   1580       case WINDOW_AGGINVERSE:
  1609         -      windowAggStep(p->pParse, pMWin, csr, 1, p->regArg, pMWin->regSize);
         1581  +      csr = p->start.csr;
         1582  +      reg = p->start.reg;
         1583  +      windowAggStep(pParse, pMWin, csr, 1, p->regArg, pMWin->regSize);
  1610   1584         break;
  1611   1585   
  1612   1586       case WINDOW_AGGSTEP:
  1613         -      windowAggStep(p->pParse, pMWin, csr, 0, p->regArg, pMWin->regSize);
         1587  +      csr = p->end.csr;
         1588  +      reg = p->end.reg;
         1589  +      windowAggStep(pParse, pMWin, csr, 0, p->regArg, pMWin->regSize);
  1614   1590         break;
  1615   1591     }
  1616   1592   
  1617   1593     if( jumpOnEof ){
  1618   1594       sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
  1619   1595       ret = sqlite3VdbeAddOp0(v, OP_Goto);
  1620   1596     }else{
  1621         -    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1);
         1597  +    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1+bPeer);
         1598  +    if( bPeer ){
         1599  +      addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
         1600  +    }
  1622   1601     }
  1623   1602   
  1624         -  if( regCountdown>0 ){
  1625         -    sqlite3VdbeJumpHere(v, addrIf);
         1603  +  if( bPeer ){
         1604  +    int addr;
         1605  +    int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
         1606  +    int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
         1607  +    windowReadPeerValues(p, csr, regTmp);
         1608  +    addr = windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg);
         1609  +    sqlite3VdbeChangeP2(v, addr, addrContinue);
         1610  +    sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  1626   1611     }
         1612  +
         1613  +  if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
         1614  +  if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
  1627   1615     return ret;
  1628   1616   }
  1629         -
  1630         -
  1631   1617   
  1632   1618   /*
  1633   1619   ** This function - windowCodeStep() - generates the VM code that reads data
  1634   1620   ** from the sub-select and returns rows to the consumer. For the simplest
  1635   1621   ** case:
  1636   1622   **
  1637   1623   **     ROWS BETWEEN <expr1> PRECEDING AND <expr2> FOLLOWING
................................................................................
  1642   1628   **       if( new partition ){
  1643   1629   **         Gosub flush
  1644   1630   **       }    
  1645   1631   **       Insert new row into eph table.
  1646   1632   **     
  1647   1633   **       if( first row of partition ){
  1648   1634   **         Rewind(csrEnd, skipNext=1)
  1649         -**         Rewind(csrStart, skipNext=1)
         1635  +**         Rewind(start.csr, skipNext=1)
  1650   1636   **         Rewind(csrCurrent, skipNext=1)
  1651   1637   **     
  1652   1638   **         regEnd = <expr2>          // FOLLOWING expression
  1653   1639   **         regStart = <expr1>        // PRECEDING expression
  1654   1640   **       }else{
  1655   1641   **         if( (regEnd--)<=0 ){
  1656   1642   **           Next(csrCurrent)
  1657   1643   **           Return one row.
  1658   1644   **           if( (regStart--)<0 ){
  1659         -**             Next(csrStart)
  1660         -**             AggInverse(csrStart)
         1645  +**             Next(start.csr)
         1646  +**             AggInverse(start.csr)
  1661   1647   **           }
  1662   1648   **         }
  1663   1649   **       }    
  1664   1650   **     
  1665   1651   **       Next(csrEnd)
  1666   1652   **       AggStep(csrEnd)
  1667   1653   **     }    
  1668   1654   **     flush:
  1669   1655   **       while( 1 ){ 
  1670   1656   **         Next(csrCurrent)
  1671   1657   **         if( eof ) break
  1672   1658   **         Return one row.
  1673   1659   **         if( (regStart--)<0 ){
  1674         -**           Next(csrStart)
  1675         -**           AggInverse(csrStart)
         1660  +**           Next(start.csr)
         1661  +**           AggInverse(start.csr)
  1676   1662   **         }
  1677   1663   **       }    
  1678   1664   **       Empty eph table.
  1679   1665   **
  1680   1666   ** More generally, the pattern used for all window types is:
  1681   1667   **
  1682   1668   **     while( !eof ){
................................................................................
  1700   1686     Parse *pParse, 
  1701   1687     Select *p,
  1702   1688     WhereInfo *pWInfo,
  1703   1689     int regGosub, 
  1704   1690     int addrGosub
  1705   1691   ){
  1706   1692     Window *pMWin = p->pWin;
         1693  +  ExprList *pOrderBy = pMWin->pOrderBy;
  1707   1694     Vdbe *v = sqlite3GetVdbe(pParse);
  1708   1695     int regFlushPart;               /* Register for "Gosub flush_partition" */
  1709   1696   
  1710   1697     int regArg;
  1711         -  int csrCurrent = pMWin->iEphCsr;
  1712         -  int csrWrite = csrCurrent+1;
  1713         -  int csrStart = csrCurrent+2;
  1714         -  int csrEnd = csrCurrent+3;
  1715         -
         1698  +  int csrWrite = pMWin->iEphCsr+1;
  1716   1699   
  1717   1700     int iSubCsr = p->pSrc->a[0].iCursor;      /* Cursor of sub-select */
  1718   1701     int nSub = p->pSrc->a[0].pTab->nCol;      /* Number of cols returned by sub */
  1719   1702     int iCol;                                 /* To iterate through sub cols */
  1720   1703   
  1721   1704     int addrGoto;
  1722   1705     int addrIf;
................................................................................
  1723   1706     int addrGosubFlush;
  1724   1707     int addrInteger;
  1725   1708     int addrCacheRewind;
  1726   1709     int addrCacheNext;
  1727   1710   
  1728   1711     int addrShortcut = 0;
  1729   1712     int addrEmpty = 0;
         1713  +  int addrPeerJump = 0;
  1730   1714   
  1731   1715     int bCache = windowCachePartition(pMWin);
  1732   1716   
  1733   1717     int regStart = 0;               /* Value of <expr> PRECEDING */
  1734   1718     int regEnd = 0;                 /* Value of <expr> FOLLOWING */
  1735   1719   
  1736   1720     int reg = pParse->nMem+1;
  1737   1721     int regRecord = reg+nSub;
  1738   1722     int regRowid = regRecord+1;
         1723  +  int regPeer = 0;
         1724  +  int regNewPeer = 0;
  1739   1725     WindowCodeArg s;
  1740   1726   
  1741   1727     memset(&s, 0, sizeof(WindowCodeArg));
  1742   1728     s.pParse = pParse;
  1743   1729     s.pMWin = pMWin;
  1744   1730     s.pVdbe = v;
  1745   1731     s.regGosub = regGosub;
  1746   1732     s.addrGosub = addrGosub;
         1733  +  s.current.csr = pMWin->iEphCsr;
         1734  +  s.start.csr = s.current.csr+2;
         1735  +  s.end.csr = s.current.csr+3;
  1747   1736   
  1748   1737     pParse->nMem += 1 + nSub + 1;
  1749   1738   
  1750   1739     regFlushPart = ++pParse->nMem;
  1751   1740   
  1752   1741     if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
  1753   1742       regStart = ++pParse->nMem;
  1754   1743     }
  1755   1744     if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
  1756   1745       regEnd = ++pParse->nMem;
  1757   1746     }
         1747  +
         1748  +  /* If this is not a "ROWS BETWEEN ..." frame, then allocate registers to
         1749  +  ** store a copy of the current ORDER BY expressions. */
         1750  +  if( pMWin->eType!=TK_ROWS ){
         1751  +    int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
         1752  +    regNewPeer = reg + pMWin->nBufferCol;
         1753  +    if( pMWin->pPartition ) regNewPeer += pMWin->pPartition->nExpr;
         1754  +
         1755  +    regPeer = pParse->nMem+1;       pParse->nMem += nPeer;
         1756  +    s.start.reg = pParse->nMem+1;   pParse->nMem += nPeer;
         1757  +    s.current.reg = pParse->nMem+1; pParse->nMem += nPeer;
         1758  +    s.end.reg = pParse->nMem+1;     pParse->nMem += nPeer;
         1759  +  }
  1758   1760   
  1759   1761     assert( pMWin->eStart==TK_PRECEDING 
  1760   1762          || pMWin->eStart==TK_CURRENT 
  1761   1763          || pMWin->eStart==TK_FOLLOWING 
  1762   1764          || pMWin->eStart==TK_UNBOUNDED 
  1763   1765     );
  1764   1766     assert( pMWin->eEnd==TK_FOLLOWING 
................................................................................
  1831   1833   
  1832   1834     if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
  1833   1835       int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
  1834   1836       int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
  1835   1837       windowAggFinal(pParse, pMWin, 0);
  1836   1838       if( bCache ){
  1837   1839         sqlite3VdbeAddOp2(v, OP_Rowid, csrWrite, regRowid);
  1838         -      sqlite3VdbeAddOp3(v, OP_NotExists, csrCurrent, 0, regRowid);
         1840  +      sqlite3VdbeAddOp3(v, OP_NotExists, s.current.csr, 0, regRowid);
  1839   1841         windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  1840   1842         sqlite3VdbeAddOp2(v, OP_Next, csrWrite, addrCacheRewind+1);
  1841   1843       }else{
  1842         -      sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
         1844  +      sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
  1843   1845         windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  1844         -      sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
         1846  +      sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  1845   1847       }
  1846   1848       addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
  1847   1849       sqlite3VdbeJumpHere(v, addrGe);
  1848   1850     }
  1849   1851     if( pMWin->eStart==TK_FOLLOWING && regEnd ){
  1850   1852       assert( pMWin->eEnd==TK_FOLLOWING );
  1851   1853       sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  1852   1854     }
  1853   1855   
  1854   1856     if( pMWin->eStart!=TK_UNBOUNDED ){
  1855         -    sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1);
         1857  +    sqlite3VdbeAddOp2(v, OP_Rewind, s.start.csr, 1);
  1856   1858     }
  1857         -  sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
  1858         -  sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1);
         1859  +  sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
         1860  +  sqlite3VdbeAddOp2(v, OP_Rewind, s.end.csr, 1);
         1861  +  if( regPeer && pOrderBy ){
         1862  +    if( bCache ){
         1863  +      windowReadPeerValues(&s, csrWrite, regPeer);
         1864  +    }else{
         1865  +      sqlite3VdbeAddOp3(v, OP_Copy, regNewPeer, regPeer, pOrderBy->nExpr-1);
         1866  +    }
         1867  +    sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.start.reg, pOrderBy->nExpr-1);
         1868  +    sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
         1869  +    sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
         1870  +  }
  1859   1871   
  1860   1872     sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  1861   1873     addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
  1862   1874   
  1863   1875     /* Begin generating SECOND_ROW_CODE */
  1864   1876     VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.SECOND_ROW_CODE"));
  1865   1877     if( bCache ){
  1866   1878       addrCacheNext = sqlite3VdbeCurrentAddr(v);
         1879  +    if( pMWin->eType!=TK_ROWS ){
         1880  +      windowReadPeerValues(&s, csrWrite, regNewPeer);
         1881  +    }
  1867   1882     }else{
  1868   1883       sqlite3VdbeJumpHere(v, addrIf);
  1869   1884     }
         1885  +  if( regPeer ){
         1886  +    addrPeerJump = windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer);
         1887  +  }
  1870   1888     if( pMWin->eStart==TK_FOLLOWING ){
  1871         -    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
         1889  +    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  1872   1890       if( pMWin->eEnd!=TK_UNBOUNDED ){
  1873         -      windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 0);
  1874         -      windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
         1891  +      windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
         1892  +      windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  1875   1893       }
  1876   1894     }else
  1877   1895     if( pMWin->eEnd==TK_PRECEDING ){
  1878         -    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
  1879         -    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
  1880         -    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
         1896  +    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
         1897  +    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
         1898  +    windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  1881   1899     }else{
  1882   1900       int addr;
  1883         -    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
         1901  +    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  1884   1902       if( pMWin->eEnd!=TK_UNBOUNDED ){
  1885   1903         if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
  1886         -      windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
  1887         -      windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
         1904  +      windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
         1905  +      windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  1888   1906         if( regEnd ) sqlite3VdbeJumpHere(v, addr);
  1889   1907       }
  1890   1908     }
         1909  +  if( addrPeerJump ){
         1910  +    sqlite3VdbeJumpHere(v, addrPeerJump);
         1911  +  }
  1891   1912     VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));
  1892   1913   
  1893   1914     /* End of the main input loop */
  1894   1915     sqlite3VdbeJumpHere(v, addrGoto);
  1895   1916     if( bCache ){
  1896   1917       sqlite3VdbeAddOp2(v, OP_Next, csrWrite, addrCacheNext);
  1897   1918       sqlite3VdbeJumpHere(v, addrCacheRewind); 
................................................................................
  1905   1926       addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
  1906   1927       sqlite3VdbeJumpHere(v, addrGosubFlush);
  1907   1928     }
  1908   1929   
  1909   1930     VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.FLUSH_CODE"));
  1910   1931     addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  1911   1932     if( pMWin->eEnd==TK_PRECEDING ){
  1912         -    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
  1913         -    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
         1933  +    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
         1934  +    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  1914   1935     }else if( pMWin->eStart==TK_FOLLOWING ){
  1915   1936       int addrStart;
  1916   1937       int addrBreak1;
  1917   1938       int addrBreak2;
  1918   1939       int addrBreak3;
  1919         -    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
         1940  +    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  1920   1941       if( pMWin->eEnd==TK_UNBOUNDED ){
  1921   1942         addrStart = sqlite3VdbeCurrentAddr(v);
  1922         -      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regStart, 1);
  1923         -      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, 0, 1);
         1943  +      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regStart, 1);
         1944  +      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1);
  1924   1945       }else{
  1925   1946         assert( pMWin->eEnd==TK_FOLLOWING );
  1926   1947         addrStart = sqlite3VdbeCurrentAddr(v);
  1927         -      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 1);
  1928         -      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 1);
         1948  +      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 1);
         1949  +      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1);
  1929   1950       }
  1930   1951       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
  1931   1952       sqlite3VdbeJumpHere(v, addrBreak2);
  1932   1953       addrStart = sqlite3VdbeCurrentAddr(v);
  1933         -    addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
         1954  +    addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
  1934   1955       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
  1935   1956       sqlite3VdbeJumpHere(v, addrBreak1);
  1936   1957       sqlite3VdbeJumpHere(v, addrBreak3);
  1937   1958     }else{
  1938   1959       int addrBreak;
  1939   1960       int addrStart;
  1940         -    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
         1961  +    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  1941   1962       addrStart = sqlite3VdbeCurrentAddr(v);
  1942         -    addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
  1943         -    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
         1963  +    addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
         1964  +    windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  1944   1965       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
  1945   1966       sqlite3VdbeJumpHere(v, addrBreak);
  1946   1967     }
  1947   1968   
  1948   1969     sqlite3VdbeJumpHere(v, addrEmpty);
  1949   1970   
  1950   1971     if( bCache && addrShortcut>0 ) sqlite3VdbeJumpHere(v, addrShortcut);
  1951         -  sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
         1972  +  sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  1952   1973     sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regSize);
  1953   1974     if( bCache==0 ) sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  1954   1975     VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.FLUSH_CODE"));
  1955   1976     if( pMWin->pPartition ){
  1956   1977       sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
  1957   1978       sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  1958   1979     }
  1959   1980   }
  1960   1981   
  1961         -/*
  1962         -** This function does the work of sqlite3WindowCodeStep() for cases that
  1963         -** would normally be handled by windowCodeDefaultStep() when there are
  1964         -** one or more built-in window-functions that require the entire partition
  1965         -** to be cached in a temp table before any rows can be returned. Additionally.
  1966         -** "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is always handled by
  1967         -** this function.
  1968         -**
  1969         -** Pseudo-code corresponding to the VM code generated by this function
  1970         -** for each type of window follows.
  1971         -**
  1972         -** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  1973         -**
  1974         -**   flush_partition:
  1975         -**     Once {
  1976         -**       OpenDup (iEphCsr -> csrLead)
  1977         -**     }
  1978         -**     Integer ctr 0
  1979         -**     foreach row (csrLead){
  1980         -**       if( new peer ){
  1981         -**         AggFinal (xValue)
  1982         -**         for(i=0; i<ctr; i++){
  1983         -**           Gosub addrGosub
  1984         -**           Next iEphCsr
  1985         -**         }
  1986         -**         Integer ctr 0
  1987         -**       }
  1988         -**       AggStep (csrLead)
  1989         -**       Incr ctr
  1990         -**     }
  1991         -**
  1992         -**     AggFinal (xFinalize)
  1993         -**     for(i=0; i<ctr; i++){
  1994         -**       Gosub addrGosub
  1995         -**       Next iEphCsr
  1996         -**     }
  1997         -**
  1998         -**     ResetSorter (csr)
  1999         -**     Return
  2000         -**
  2001         -** ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2002         -**
  2003         -**   As above, except that the "if( new peer )" branch is always taken.
  2004         -**
  2005         -** RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
  2006         -**
  2007         -**   As above, except that each of the for() loops becomes:
  2008         -**
  2009         -**         for(i=0; i<ctr; i++){
  2010         -**           Gosub addrGosub
  2011         -**           AggInverse (iEphCsr)
  2012         -**           Next iEphCsr
  2013         -**         }
  2014         -**
  2015         -** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  2016         -**
  2017         -**   flush_partition:
  2018         -**     Once {
  2019         -**       OpenDup (iEphCsr -> csrLead)
  2020         -**     }
  2021         -**     foreach row (csrLead) {
  2022         -**       AggStep (csrLead)
  2023         -**     }
  2024         -**     foreach row (iEphCsr) {
  2025         -**       Gosub addrGosub
  2026         -**     }
  2027         -** 
  2028         -** RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  2029         -**
  2030         -**   flush_partition:
  2031         -**     Once {
  2032         -**       OpenDup (iEphCsr -> csrLead)
  2033         -**     }
  2034         -**     foreach row (csrLead){
  2035         -**       AggStep (csrLead)
  2036         -**     }
  2037         -**     Rewind (csrLead)
  2038         -**     Integer ctr 0
  2039         -**     foreach row (csrLead){
  2040         -**       if( new peer ){
  2041         -**         AggFinal (xValue)
  2042         -**         for(i=0; i<ctr; i++){
  2043         -**           Gosub addrGosub
  2044         -**           AggInverse (iEphCsr)
  2045         -**           Next iEphCsr
  2046         -**         }
  2047         -**         Integer ctr 0
  2048         -**       }
  2049         -**       Incr ctr
  2050         -**     }
  2051         -**
  2052         -**     AggFinal (xFinalize)
  2053         -**     for(i=0; i<ctr; i++){
  2054         -**       Gosub addrGosub
  2055         -**       Next iEphCsr
  2056         -**     }
  2057         -**
  2058         -**     ResetSorter (csr)
  2059         -**     Return
  2060         -*/
  2061         -static void windowCodeCacheStep(
  2062         -  Parse *pParse, 
  2063         -  Select *p,
  2064         -  WhereInfo *pWInfo,
  2065         -  int regGosub, 
  2066         -  int addrGosub
  2067         -){
  2068         -  Window *pMWin = p->pWin;
  2069         -  Vdbe *v = sqlite3GetVdbe(pParse);
  2070         -  int k;
  2071         -  int addr;
  2072         -  ExprList *pPart = pMWin->pPartition;
  2073         -  ExprList *pOrderBy = pMWin->pOrderBy;
  2074         -  int nPeer = pOrderBy ? pOrderBy->nExpr : 0;
  2075         -  int regNewPeer;
  2076         -
  2077         -  int addrGoto;                   /* Address of Goto used to jump flush_par.. */
  2078         -  int addrNext;                   /* Jump here for next iteration of loop */
  2079         -  int regFlushPart;
  2080         -  int lblFlushPart;
  2081         -  int csrLead;
  2082         -  int regCtr;
  2083         -  int regArg;                     /* Register array to martial function args */
  2084         -  int regSize;
  2085         -  int lblEmpty;
  2086         -  int bReverse = pMWin->pOrderBy && pMWin->eStart==TK_CURRENT 
  2087         -          && pMWin->eEnd==TK_UNBOUNDED;
  2088         -
  2089         -  assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT) 
  2090         -       || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED) 
  2091         -       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT) 
  2092         -       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED) 
  2093         -  );
  2094         -
  2095         -  lblEmpty = sqlite3VdbeMakeLabel(pParse);
  2096         -  regNewPeer = pParse->nMem+1;
  2097         -  pParse->nMem += nPeer;
  2098         -
  2099         -  /* Allocate register and label for the "flush_partition" sub-routine. */
  2100         -  regFlushPart = ++pParse->nMem;
  2101         -  lblFlushPart = sqlite3VdbeMakeLabel(pParse);
  2102         -
  2103         -  csrLead = pParse->nTab++;
  2104         -  regCtr = ++pParse->nMem;
  2105         -
  2106         -  windowPartitionCache(pParse, p, pWInfo, regFlushPart, lblFlushPart, &regSize);
  2107         -  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
  2108         -
  2109         -  /* Start of "flush_partition" */
  2110         -  sqlite3VdbeResolveLabel(v, lblFlushPart);
  2111         -  sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+2);
  2112         -  VdbeCoverage(v);
  2113         -  sqlite3VdbeAddOp2(v, OP_OpenDup, csrLead, pMWin->iEphCsr);
  2114         -
  2115         -  /* Initialize the accumulator register for each window function to NULL */
  2116         -  regArg = windowInitAccum(pParse, pMWin);
  2117         -
  2118         -  sqlite3VdbeAddOp2(v, OP_Integer, 0, regCtr);
  2119         -  sqlite3VdbeAddOp2(v, OP_Rewind, csrLead, lblEmpty);
  2120         -  VdbeCoverage(v);
  2121         -  sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, lblEmpty);
  2122         -  VdbeCoverageNeverTaken(v);
  2123         -
  2124         -  if( bReverse ){
  2125         -    int addr2 = sqlite3VdbeCurrentAddr(v);
  2126         -    windowAggStep(pParse, pMWin, csrLead, 0, regArg, regSize);
  2127         -    sqlite3VdbeAddOp2(v, OP_Next, csrLead, addr2);
  2128         -    VdbeCoverage(v);
  2129         -    sqlite3VdbeAddOp2(v, OP_Rewind, csrLead, lblEmpty);
  2130         -    VdbeCoverageNeverTaken(v);
  2131         -  }
  2132         -  addrNext = sqlite3VdbeCurrentAddr(v);
  2133         -
  2134         -  if( pOrderBy && (pMWin->eEnd==TK_CURRENT || pMWin->eStart==TK_CURRENT) ){
  2135         -    int bCurrent = (pMWin->eStart==TK_CURRENT);
  2136         -    int addrJump = 0;             /* Address of OP_Jump below */
  2137         -    if( pMWin->eType==TK_RANGE ){
  2138         -      int iOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0);
  2139         -      int regPeer = pMWin->regPart + (pPart ? pPart->nExpr : 0);
  2140         -      KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
  2141         -      for(k=0; k<nPeer; k++){
  2142         -        sqlite3VdbeAddOp3(v, OP_Column, csrLead, iOff+k, regNewPeer+k);
  2143         -      }
  2144         -      addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
  2145         -      sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  2146         -      addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
  2147         -      VdbeCoverage(v);
  2148         -      sqlite3VdbeAddOp3(v, OP_Copy, regNewPeer, regPeer, nPeer-1);
  2149         -    }
  2150         -
  2151         -    windowReturnRows(pParse, pMWin, regCtr, regGosub, addrGosub, 
  2152         -        (bCurrent ? regArg : 0), (bCurrent ? regSize : 0)
  2153         -    );
  2154         -    if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
  2155         -  }
  2156         -
  2157         -  if( bReverse==0 ){
  2158         -    windowAggStep(pParse, pMWin, csrLead, 0, regArg, regSize);
  2159         -  }
  2160         -  sqlite3VdbeAddOp2(v, OP_AddImm, regCtr, 1);
  2161         -  sqlite3VdbeAddOp2(v, OP_Next, csrLead, addrNext);
  2162         -  VdbeCoverage(v);
  2163         -
  2164         -  windowReturnRows(pParse, pMWin, regCtr, regGosub, addrGosub, 0, 0);
  2165         -
  2166         -  sqlite3VdbeResolveLabel(v, lblEmpty);
  2167         -  sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
  2168         -  sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  2169         -
  2170         -  /* Jump to here to skip over flush_partition */
  2171         -  sqlite3VdbeJumpHere(v, addrGoto);
  2172         -}
  2173         -
  2174         -
  2175         -/*
  2176         -** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2177         -**
  2178         -**   ...
  2179         -**     if( new partition ){
  2180         -**       AggFinal (xFinalize)
  2181         -**       Gosub addrGosub
  2182         -**       ResetSorter eph-table
  2183         -**     }
  2184         -**     else if( new peer ){
  2185         -**       AggFinal (xValue)
  2186         -**       Gosub addrGosub
  2187         -**       ResetSorter eph-table
  2188         -**     }
  2189         -**     AggStep
  2190         -**     Insert (record into eph-table)
  2191         -**   sqlite3WhereEnd()
  2192         -**   AggFinal (xFinalize)
  2193         -**   Gosub addrGosub
  2194         -**
  2195         -** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  2196         -**
  2197         -**   As above, except take no action for a "new peer". Invoke
  2198         -**   the sub-routine once only for each partition.
  2199         -**
  2200         -** RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  2201         -**
  2202         -**   As above, except that the "new peer" condition is handled in the
  2203         -**   same way as "new partition" (so there is no "else if" block).
  2204         -**
  2205         -** ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2206         -** 
  2207         -**   As above, except assume every row is a "new peer".
  2208         -*/
  2209         -static void windowCodeDefaultStep(
  2210         -  Parse *pParse, 
  2211         -  Select *p,
  2212         -  WhereInfo *pWInfo,
  2213         -  int regGosub, 
  2214         -  int addrGosub
  2215         -){
  2216         -  Window *pMWin = p->pWin;
  2217         -  Vdbe *v = sqlite3GetVdbe(pParse);
  2218         -  int k;
  2219         -  int iSubCsr = p->pSrc->a[0].iCursor;
  2220         -  int nSub = p->pSrc->a[0].pTab->nCol;
  2221         -  int reg = pParse->nMem+1;
  2222         -  int regRecord = reg+nSub;
  2223         -  int regRowid = regRecord+1;
  2224         -  int addr;
  2225         -  ExprList *pPart = pMWin->pPartition;
  2226         -  ExprList *pOrderBy = pMWin->pOrderBy;
  2227         -
  2228         -  assert( pMWin->eType==TK_RANGE 
  2229         -      || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT)
  2230         -  );
  2231         -
  2232         -  assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT)
  2233         -       || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED)
  2234         -       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT)
  2235         -       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED && !pOrderBy)
  2236         -  );
  2237         -
  2238         -  if( pMWin->eEnd==TK_UNBOUNDED ){
  2239         -    pOrderBy = 0;
  2240         -  }
  2241         -
  2242         -  pParse->nMem += nSub + 2;
  2243         -
  2244         -  /* Load the individual column values of the row returned by
  2245         -  ** the sub-select into an array of registers. */
  2246         -  for(k=0; k<nSub; k++){
  2247         -    sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
  2248         -  }
  2249         -
  2250         -  /* Check if this is the start of a new partition or peer group. */
  2251         -  if( pPart || pOrderBy ){
  2252         -    int nPart = (pPart ? pPart->nExpr : 0);
  2253         -    int addrGoto = 0;
  2254         -    int addrJump = 0;
  2255         -    int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
  2256         -
  2257         -    if( pPart ){
  2258         -      int regNewPart = reg + pMWin->nBufferCol;
  2259         -      KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
  2260         -      addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
  2261         -      sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  2262         -      addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
  2263         -      VdbeCoverageEqNe(v);
  2264         -      windowAggFinal(pParse, pMWin, 1);
  2265         -      if( pOrderBy ){
  2266         -        addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
  2267         -      }
  2268         -    }
  2269         -
  2270         -    if( pOrderBy ){
  2271         -      int regNewPeer = reg + pMWin->nBufferCol + nPart;
  2272         -      int regPeer = pMWin->regPart + nPart;
  2273         -
  2274         -      if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
  2275         -      if( pMWin->eType==TK_RANGE ){
  2276         -        KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
  2277         -        addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
  2278         -        sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  2279         -        addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
  2280         -        VdbeCoverage(v);
  2281         -      }else{
  2282         -        addrJump = 0;
  2283         -      }
  2284         -      windowAggFinal(pParse, pMWin, pMWin->eStart==TK_CURRENT);
  2285         -      if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
  2286         -    }
  2287         -
  2288         -    sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3);
  2289         -    VdbeCoverage(v);
  2290         -    sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  2291         -    sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1);
  2292         -    VdbeCoverage(v);
  2293         -
  2294         -    sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
  2295         -    sqlite3VdbeAddOp3(
  2296         -        v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
  2297         -    );
  2298         -
  2299         -    if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
  2300         -  }
  2301         -
  2302         -  /* Invoke step function for window functions */
  2303         -  windowAggStep(pParse, pMWin, -1, 0, reg, 0);
  2304         -
  2305         -  /* Buffer the current row in the ephemeral table. */
  2306         -  if( pMWin->nBufferCol>0 ){
  2307         -    sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, pMWin->nBufferCol, regRecord);
  2308         -  }else{
  2309         -    sqlite3VdbeAddOp2(v, OP_Blob, 0, regRecord);
  2310         -    sqlite3VdbeAppendP4(v, (void*)"", 0);
  2311         -  }
  2312         -  sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
  2313         -  sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);
  2314         -
  2315         -  /* End the database scan loop. */
  2316         -  sqlite3WhereEnd(pWInfo);
  2317         -
  2318         -  windowAggFinal(pParse, pMWin, 1);
  2319         -  sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3);
  2320         -  VdbeCoverage(v);
  2321         -  sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  2322         -  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1);
  2323         -  VdbeCoverage(v);
  2324         -}
  2325   1982   
  2326   1983   /*
  2327   1984   ** Allocate and return a duplicate of the Window object indicated by the
  2328   1985   ** third argument. Set the Window.pOwner field of the new object to
  2329   1986   ** pOwner.
  2330   1987   */
  2331   1988   Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p){
................................................................................
  2377   2034   void sqlite3WindowCodeStep(
  2378   2035     Parse *pParse,                  /* Parse context */
  2379   2036     Select *p,                      /* Rewritten SELECT statement */
  2380   2037     WhereInfo *pWInfo,              /* Context returned by sqlite3WhereBegin() */
  2381   2038     int regGosub,                   /* Register for OP_Gosub */
  2382   2039     int addrGosub                   /* OP_Gosub here to return each row */
  2383   2040   ){
  2384         -  Window *pMWin = p->pWin;
  2385         -
  2386         -  /* There are three different functions that may be used to do the work
  2387         -  ** of this one, depending on the window frame and the specific built-in
  2388         -  ** window functions used (if any).
  2389         -  **
  2390         -  ** windowCodeRowExprStep() handles all "ROWS" window frames, except for:
  2391         -  **
  2392         -  **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2393         -  **
  2394         -  ** The exception is because windowCodeRowExprStep() implements all window
  2395         -  ** frame types by caching the entire partition in a temp table, and
  2396         -  ** "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is easy enough to
  2397         -  ** implement without such a cache.
  2398         -  **
  2399         -  ** windowCodeCacheStep() is used for:
  2400         -  **
  2401         -  **   RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  2402         -  **
  2403         -  ** It is also used for anything not handled by windowCodeRowExprStep() 
  2404         -  ** that invokes a built-in window function that requires the entire 
  2405         -  ** partition to be cached in a temp table before any rows are returned
  2406         -  ** (e.g. nth_value() or percent_rank()).
  2407         -  **
  2408         -  ** Finally, assuming there is no built-in window function that requires
  2409         -  ** the partition to be cached, windowCodeDefaultStep() is used for:
  2410         -  **
  2411         -  **   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
  2412         -  **   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  2413         -  **   RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
  2414         -  **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2415         -  **
  2416         -  ** windowCodeDefaultStep() is the only one of the three functions that
  2417         -  ** does not cache each partition in a temp table before beginning to
  2418         -  ** return rows.
  2419         -  */
  2420         -  if( pMWin->eType==TK_ROWS ){
  2421         -    VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()"));
  2422         -    windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
  2423         -    VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()"));
  2424         -  }else{
  2425         -    Window *pWin;
  2426         -    int bCache = 0;               /* True to use CacheStep() */
  2427         -
  2428         -    if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED ){
  2429         -      bCache = 1;
  2430         -    }else{
  2431         -      for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  2432         -        FuncDef *pFunc = pWin->pFunc;
  2433         -        if( (pFunc->funcFlags & SQLITE_FUNC_WINDOW_SIZE)
  2434         -         || (pFunc->zName==nth_valueName)
  2435         -         || (pFunc->zName==first_valueName)
  2436         -         || (pFunc->zName==leadName)
  2437         -         || (pFunc->zName==lagName)
  2438         -        ){
  2439         -          bCache = 1;
  2440         -          break;
  2441         -        }
  2442         -      }
  2443         -    }
  2444         -
  2445         -    /* Otherwise, call windowCodeDefaultStep().  */
  2446         -    if( bCache ){
  2447         -      VdbeModuleComment((pParse->pVdbe, "Begin CacheStep()"));
  2448         -      windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub);
  2449         -      VdbeModuleComment((pParse->pVdbe, "End CacheStep()"));
  2450         -    }else{
  2451         -      VdbeModuleComment((pParse->pVdbe, "Begin DefaultStep()"));
  2452         -      windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
  2453         -      VdbeModuleComment((pParse->pVdbe, "End DefaultStep()"));
  2454         -    }
  2455         -  }
         2041  +  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()"));
         2042  +  windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
         2043  +  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()"));
  2456   2044   }
  2457   2045   
  2458   2046   #endif /* SQLITE_OMIT_WINDOWFUNC */

Changes to test/pg_common.tcl.

    97     97   puts $::fd [subst -nocommands {
    98     98   do_test $tn {
    99     99     set myres {}
   100    100     foreach r [db eval {$sql}] {
   101    101       lappend myres [format $F [set r]]
   102    102     }
   103    103     set res2 {$res2}
          104  +  set i 0
   104    105     foreach r [set myres] r2 [set res2] {
   105    106       if {[set r]<([set r2]-$T) || [set r]>([set r2]+$T)} {
   106    107         error "list element [set i] does not match: got=[set r] expected=[set r2]"
   107    108       }
          109  +    incr i
   108    110     }
   109    111     set {} {}
   110    112   } {}
   111    113   }]
   112    114   }
   113    115   
   114    116   proc start_test {name date} {

Changes to test/window3.test.

cannot compute difference between binary files

Changes to test/window4.test.

  1220   1220   
  1221   1221   do_test 9.3 {
  1222   1222     set myres {}
  1223   1223     foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
  1224   1224       lappend myres [format %.4f [set r]]
  1225   1225     }
  1226   1226     set res2 {1.0000 0.0000 1.0000 0.0000 1.0000 0.0000 4.0000 0.0000 4.0000 0.0000 6.0000 0.0000 7.0000 0.0000}
         1227  +  set i 0
  1227   1228     foreach r [set myres] r2 [set res2] {
  1228   1229       if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
  1229   1230         error "list element [set i] does not match: got=[set r] expected=[set r2]"
  1230   1231       }
         1232  +    incr i
  1231   1233     }
  1232   1234     set {} {}
  1233   1235   } {}
  1234   1236   
  1235   1237   do_execsql_test 9.4 {
  1236   1238     SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
  1237   1239   } {1 1   1 1   1 1   4 4   4 4   6 6   7 7}
................................................................................
  1243   1245   
  1244   1246   do_test 9.6 {
  1245   1247     set myres {}
  1246   1248     foreach r [db eval {SELECT percent_rank() OVER () FROM t1}] {
  1247   1249       lappend myres [format %.4f [set r]]
  1248   1250     }
  1249   1251     set res2 {0.0000 0.0000 0.0000}
         1252  +  set i 0
  1250   1253     foreach r [set myres] r2 [set res2] {
  1251   1254       if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
  1252   1255         error "list element [set i] does not match: got=[set r] expected=[set r2]"
  1253   1256       }
         1257  +    incr i
  1254   1258     }
  1255   1259     set {} {}
  1256   1260   } {}
  1257   1261   
  1258   1262   
  1259   1263   do_test 9.7 {
  1260   1264     set myres {}
  1261   1265     foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
  1262   1266       lappend myres [format %.4f [set r]]
  1263   1267     }
  1264   1268     set res2 {1.0000 1.0000 1.0000}
         1269  +  set i 0
  1265   1270     foreach r [set myres] r2 [set res2] {
  1266   1271       if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
  1267   1272         error "list element [set i] does not match: got=[set r] expected=[set r2]"
  1268   1273       }
         1274  +    incr i
  1269   1275     }
  1270   1276     set {} {}
  1271   1277   } {}
  1272   1278   
  1273   1279   do_execsql_test 10.0 {
  1274   1280     DROP TABLE IF EXISTS t7;
  1275   1281     CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);

Changes to tool/mkkeywordhash.c.

   212    212     { "FOLLOWING",        "TK_FOLLOWING",    WINDOWFUNC             },
   213    213     { "FOR",              "TK_FOR",          TRIGGER                },
   214    214     { "FOREIGN",          "TK_FOREIGN",      FKEY                   },
   215    215     { "FROM",             "TK_FROM",         ALWAYS                 },
   216    216     { "FULL",             "TK_JOIN_KW",      ALWAYS                 },
   217    217     { "GLOB",             "TK_LIKE_KW",      ALWAYS                 },
   218    218     { "GROUP",            "TK_GROUP",        ALWAYS                 },
          219  +  { "GROUPS",           "TK_GROUPS",       WINDOWFUNC             },
   219    220     { "HAVING",           "TK_HAVING",       ALWAYS                 },
   220    221     { "IF",               "TK_IF",           ALWAYS                 },
   221    222     { "IGNORE",           "TK_IGNORE",       CONFLICT|TRIGGER       },
   222    223     { "IMMEDIATE",        "TK_IMMEDIATE",    ALWAYS                 },
   223    224     { "IN",               "TK_IN",           ALWAYS                 },
   224    225     { "INDEX",            "TK_INDEX",        ALWAYS                 },
   225    226     { "INDEXED",          "TK_INDEXED",      ALWAYS                 },