/ Check-in [c34f31db]
Login

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

Overview
Comment:Fix problems with "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" window frames.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256:c34f31dbd79891249ee9485e91f6ea558ee1db62e04fb0fff2c051612b8fa5e7
User & Date: dan 2018-06-13 20:29:38
Context
2018-06-14
14:27
Improve comments and code legibility in new file window.c. check-in: bb915854 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: c34f31db user: dan tags: exp-window-functions
2018-06-12
20:53
Fix another issue to do with window-functions in aggregate queries. check-in: 6413e38a user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

   822    822     }else{
   823    823       sqlite3WindowDelete(pParse->db, pWin);
   824    824     }
   825    825   }
   826    826   
   827    827   /*
   828    828   ** Return 0 if the two window objects are identical, or non-zero otherwise.
          829  +** Identical window objects can be processed in a single scan.
   829    830   */
   830    831   int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2){
   831    832     if( p1->eType!=p2->eType ) return 1;
   832    833     if( p1->eStart!=p2->eStart ) return 1;
   833    834     if( p1->eEnd!=p2->eEnd ) return 1;
   834    835     if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1;
   835    836     if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1;
   836    837     if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1;
   837    838     if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1;
   838    839     return 0;
   839    840   }
   840    841   
   841         -static void windowAggInit(Parse *pParse, Window *pMWin){
          842  +
          843  +/*
          844  +** This is called by code in select.c before it calls sqlite3WhereBegin()
          845  +** to begin iterating through the sub-query results. It is used to allocate
          846  +** and initialize registers and cursors used by sqlite3WindowCodeStep().
          847  +*/
          848  +void sqlite3WindowCodeInit(Parse *pParse, Window *pMWin){
   842    849     Window *pWin;
          850  +  Vdbe *v = sqlite3GetVdbe(pParse);
          851  +  int nPart = (pMWin->pPartition ? pMWin->pPartition->nExpr : 0);
          852  +  nPart += (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
          853  +  if( nPart ){
          854  +    pMWin->regPart = pParse->nMem+1;
          855  +    pParse->nMem += nPart;
          856  +    sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nPart-1);
          857  +  }
          858  +
   843    859     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   844         -    Vdbe *v = sqlite3GetVdbe(pParse);
   845    860       FuncDef *p = pWin->pFunc;
   846    861       if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){
   847    862         ExprList *pList = pWin->pOwner->x.pList;
   848    863         KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pList, 0, 0);
   849    864         pWin->csrApp = pParse->nTab++;
   850    865         pWin->regApp = pParse->nMem+1;
   851    866         pParse->nMem += 3;
................................................................................
   870    885         assert( pMWin->iEphCsr );
   871    886         pWin->csrApp = pParse->nTab++;
   872    887         sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr);
   873    888       }
   874    889     }
   875    890   }
   876    891   
   877         -void sqlite3WindowCodeInit(Parse *pParse, Window *pWin){
   878         -  Vdbe *v = sqlite3GetVdbe(pParse);
   879         -  int nPart = (pWin->pPartition ? pWin->pPartition->nExpr : 0);
   880         -  nPart += (pWin->pOrderBy ? pWin->pOrderBy->nExpr : 0);
   881         -  if( nPart ){
   882         -    pWin->regPart = pParse->nMem+1;
   883         -    pParse->nMem += nPart;
   884         -    sqlite3VdbeAddOp3(v, OP_Null, 0, pWin->regPart, pWin->regPart+nPart-1);
   885         -  }
   886         -  windowAggInit(pParse, pWin);
   887         -}
   888         -
          892  +/*
          893  +** A "PRECEDING <expr>" (bEnd==0) or "FOLLOWING <expr>" (bEnd==1) has just 
          894  +** been evaluated and the result left in register reg. This function generates
          895  +** VM code to check that the value is a non-negative integer and throws
          896  +** an exception if it is not.
          897  +*/
   889    898   static void windowCheckFrameValue(Parse *pParse, int reg, int bEnd){
   890    899     static const char *azErr[] = {
   891    900       "frame starting offset must be a non-negative integer",
   892    901       "frame ending offset must be a non-negative integer"
   893    902     };
   894    903     Vdbe *v = sqlite3GetVdbe(pParse);
   895         -  int regZero = ++pParse->nMem;
   896         -
          904  +  int regZero = sqlite3GetTempReg(pParse);
   897    905     sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
   898    906     sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
   899    907     sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
   900    908     sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort);
   901    909     sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC);
          910  +  sqlite3ReleaseTempReg(pParse, regZero);
   902    911   }
   903    912   
          913  +/*
          914  +** Return the number of arguments passed to the window-function associated
          915  +** with the object passed as the only argument to this function.
          916  +*/
   904    917   static int windowArgCount(Window *pWin){
   905    918     ExprList *pList = pWin->pOwner->x.pList;
   906    919     return (pList ? pList->nExpr : 0);
   907    920   }
   908    921   
   909    922   /*
   910    923   ** Generate VM code to invoke either xStep() (if bInverse is 0) or 
   911    924   ** xInverse (if bInverse is non-zero) for each window function in the 
   912         -** linked list starting at pMWin.
          925  +** linked list starting at pMWin. Or, for built-in window functions
          926  +** that do not use the standard function API, generate the required
          927  +** inline VM code.
          928  +**
          929  +** If argument csr is greater than or equal to 0, then argument reg is
          930  +** the first register in an array of registers guaranteed to be large
          931  +** enough to hold the array of arguments for each function. In this case
          932  +** the arguments are extracted from the current row of csr into the
          933  +** array of registers before invoking OP_AggStep.
          934  +**
          935  +** Or, if csr is less than zero, then the array of registers at reg is
          936  +** already populated with all columns from the current row of the sub-query.
          937  +**
          938  +** If argument regPartSize is non-zero, then it is a register containing the
          939  +** number of rows in the current partition.
   913    940   */
   914    941   static void windowAggStep(
   915    942     Parse *pParse, 
   916         -  Window *pMWin, 
   917         -  int csr,
   918         -  int bInverse, 
   919         -  int reg,
          943  +  Window *pMWin,                  /* Linked list of window functions */
          944  +  int csr,                        /* Read arguments from this cursor */
          945  +  int bInverse,                   /* True to invoke xInverse instead of xStep */
          946  +  int reg,                        /* Array of registers */
   920    947     int regPartSize                 /* Register containing size of partition */
   921    948   ){
   922    949     Vdbe *v = sqlite3GetVdbe(pParse);
   923    950     Window *pWin;
   924    951     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   925    952       int flags = pWin->pFunc->funcFlags;
   926    953       int regArg;
................................................................................
   993   1020         sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   994   1021         sqlite3VdbeChangeP5(v, (u8)nArg);
   995   1022         if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
   996   1023       }
   997   1024     }
   998   1025   }
   999   1026   
         1027  +/*
         1028  +** Generate VM code to invoke either xValue() (bFinal==0) or xFinalize()
         1029  +** (bFinal==1) for each window function in the linked list starting at
         1030  +** pMWin. Or, for built-in window-functions that do not use the standard
         1031  +** API, generate the equivalent VM code.
         1032  +*/
  1000   1033   static void windowAggFinal(Parse *pParse, Window *pMWin, int bFinal){
  1001   1034     Vdbe *v = sqlite3GetVdbe(pParse);
  1002   1035     Window *pWin;
  1003   1036   
  1004   1037     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1005   1038       if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
  1006   1039        && pWin->eStart!=TK_UNBOUNDED 
................................................................................
  1025   1058         }else{
  1026   1059           sqlite3VdbeChangeP3(v, -1, pWin->regResult);
  1027   1060         }
  1028   1061       }
  1029   1062     }
  1030   1063   }
  1031   1064   
         1065  +/*
         1066  +** This function generates VM code to invoke the sub-routine at address
         1067  +** lblFlushPart once for each partition with the entire partition cached in
         1068  +** the Window.iEphCsr temp table.
         1069  +*/
  1032   1070   static void windowPartitionCache(
  1033   1071     Parse *pParse,
  1034         -  Select *p,
  1035         -  WhereInfo *pWInfo,
  1036         -  int regFlushPart,
  1037         -  int lblFlushPart,
  1038         -  int *pRegSize
         1072  +  Select *p,                      /* The rewritten SELECT statement */
         1073  +  WhereInfo *pWInfo,              /* WhereInfo to call WhereEnd() on */
         1074  +  int regFlushPart,               /* Register to use with Gosub lblFlushPart */
         1075  +  int lblFlushPart,               /* Subroutine to Gosub to */
         1076  +  int *pRegSize                   /* OUT: Register containing partition size */
  1039   1077   ){
  1040   1078     Window *pMWin = p->pWin;
  1041   1079     Vdbe *v = sqlite3GetVdbe(pParse);
  1042   1080     Window *pWin;
  1043   1081     int iSubCsr = p->pSrc->a[0].iCursor;
  1044   1082     int nSub = p->pSrc->a[0].pTab->nCol;
  1045   1083     int k;
................................................................................
  1081   1119     /* End of the input loop */
  1082   1120     sqlite3WhereEnd(pWInfo);
  1083   1121   
  1084   1122     /* Invoke "flush_partition" to deal with the final (or only) partition */
  1085   1123     sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart);
  1086   1124   }
  1087   1125   
         1126  +/*
         1127  +** Invoke the sub-routine at regGosub (generated by code in select.c) to
         1128  +** return the current row of Window.iEphCsr. If all window functions are
         1129  +** aggregate window functions that use the standard API, a single
         1130  +** OP_Gosub instruction is all that this routine generates. Extra VM code
         1131  +** for per-row processing is only generated for the following built-in window
         1132  +** functions:
         1133  +**
         1134  +**   nth_value()
         1135  +**   first_value()
         1136  +**   lag()
         1137  +**   lead()
         1138  +*/
  1088   1139   static void windowReturnOneRow(
  1089   1140     Parse *pParse,
  1090   1141     Window *pMWin,
  1091   1142     int regGosub,
  1092   1143     int addrGosub
  1093   1144   ){
  1094   1145     Vdbe *v = sqlite3GetVdbe(pParse);
................................................................................
  1144   1195         sqlite3VdbeResolveLabel(v, lbl);
  1145   1196         sqlite3ReleaseTempReg(pParse, tmpReg);
  1146   1197       }
  1147   1198     }
  1148   1199     sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  1149   1200   }
  1150   1201   
         1202  +/*
         1203  +** Invoke the code generated by windowReturnOneRow() and, optionally, the
         1204  +** xInverse() function for each window function, for one or more rows
         1205  +** from the Window.iEphCsr temp table. This routine generates VM code
         1206  +** similar to:
         1207  +**
         1208  +**   while( regCtr>0 ){
         1209  +**     regCtr--;
         1210  +**     windowReturnOneRow()
         1211  +**     if( bInverse ){
         1212  +**       AggStep (xInverse)
         1213  +**     }
         1214  +**     Next (Window.iEphCsr)
         1215  +**   }
         1216  +*/
  1151   1217   static void windowReturnRows(
  1152   1218     Parse *pParse,
  1153         -  Window *pMWin,
  1154         -  int regCtr,
  1155         -  int bFinal,
  1156         -  int regGosub,
  1157         -  int addrGosub,
  1158         -  int regInvArg,
  1159         -  int regInvSize
         1219  +  Window *pMWin,                  /* List of window functions */
         1220  +  int regCtr,                     /* Register containing number of rows */
         1221  +  int regGosub,                   /* Register for Gosub addrGosub */
         1222  +  int addrGosub,                  /* Address of sub-routine for ReturnOneRow */
         1223  +  int regInvArg,                  /* Array of registers for xInverse args */
         1224  +  int regInvSize                  /* Register containing size of partition */
  1160   1225   ){
  1161   1226     int addr;
  1162   1227     Vdbe *v = sqlite3GetVdbe(pParse);
  1163   1228     windowAggFinal(pParse, pMWin, 0);
  1164   1229     addr = sqlite3VdbeAddOp3(v, OP_IfPos, regCtr, sqlite3VdbeCurrentAddr(v)+2 ,1);
  1165   1230     sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
  1166   1231     windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
................................................................................
  1345   1410     int addrIfPos1;
  1346   1411     int addrIfPos2;
  1347   1412   
  1348   1413     int regPeer = 0;                 /* Number of peers in current group */
  1349   1414     int regPeerVal = 0;              /* Array of values identifying peer group */
  1350   1415     int iPeer = 0;                   /* Column offset in eph-table of peer vals */
  1351   1416     int nPeerVal;                    /* Number of peer values */
  1352         -  int bRange = 0;
  1353   1417     int regSize = 0;
  1354   1418   
  1355   1419     assert( pMWin->eStart==TK_PRECEDING 
  1356   1420          || pMWin->eStart==TK_CURRENT 
  1357   1421          || pMWin->eStart==TK_FOLLOWING 
  1358   1422          || pMWin->eStart==TK_UNBOUNDED 
  1359   1423     );
  1360   1424     assert( pMWin->eEnd==TK_FOLLOWING 
  1361   1425          || pMWin->eEnd==TK_CURRENT 
  1362   1426          || pMWin->eEnd==TK_UNBOUNDED 
  1363   1427          || pMWin->eEnd==TK_PRECEDING 
  1364   1428     );
  1365   1429   
  1366         -  if( pMWin->eType==TK_RANGE 
  1367         -   && pMWin->eStart==TK_CURRENT 
  1368         -   && pMWin->eEnd==TK_UNBOUNDED
  1369         -  ){
  1370         -    bRange = 1;
  1371         -  }
  1372         -
  1373   1430     /* Allocate register and label for the "flush_partition" sub-routine. */
  1374   1431     regFlushPart = ++pParse->nMem;
  1375   1432     lblFlushPart = sqlite3VdbeMakeLabel(v);
  1376   1433     lblFlushDone = sqlite3VdbeMakeLabel(v);
  1377   1434   
  1378   1435     regStart = ++pParse->nMem;
  1379   1436     regEnd = ++pParse->nMem;
................................................................................
  1453   1510   
  1454   1511     if( pMWin->eEnd==TK_FOLLOWING ){
  1455   1512       addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0 , 1);
  1456   1513     }
  1457   1514     if( pMWin->eStart==TK_FOLLOWING ){
  1458   1515       addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0 , 1);
  1459   1516     }
  1460         -  if( bRange ){
  1461         -    assert( pMWin->eStart==TK_CURRENT && pMWin->pOrderBy );
  1462         -    regPeer = ++pParse->nMem;
  1463         -    regPeerVal = pParse->nMem+1;
  1464         -    iPeer = pMWin->nBufferCol + (pMWin->pPartition?pMWin->pPartition->nExpr:0);
  1465         -    nPeerVal = pMWin->pOrderBy->nExpr;
  1466         -    pParse->nMem += (2 * nPeerVal);
  1467         -    for(k=0; k<nPeerVal; k++){
  1468         -      sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, iPeer+k, regPeerVal+k);
  1469         -    }
  1470         -    sqlite3VdbeAddOp2(v, OP_Integer, 0, regPeer);
  1471         -  }
  1472         -
  1473   1517     windowAggFinal(pParse, pMWin, 0);
  1474         -  if( bRange ){
  1475         -    sqlite3VdbeAddOp2(v, OP_AddImm, regPeer, 1);
  1476         -  }
  1477   1518     windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  1478   1519     sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)+2);
  1479   1520     sqlite3VdbeAddOp2(v, OP_Goto, 0, lblFlushDone);
  1480         -  if( bRange ){
  1481         -    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy,0,0);
  1482         -    int addrJump = sqlite3VdbeCurrentAddr(v)-4;
  1483         -    for(k=0; k<nPeerVal; k++){
  1484         -      int iOut = regPeerVal + nPeerVal + k;
  1485         -      sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, iPeer+k, iOut);
  1486         -    }
  1487         -    sqlite3VdbeAddOp3(v, OP_Compare, regPeerVal, regPeerVal+nPeerVal, nPeerVal);
  1488         -    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  1489         -    addr = sqlite3VdbeCurrentAddr(v)+1;
  1490         -    sqlite3VdbeAddOp3(v, OP_Jump, addr, addrJump, addr);
  1491         -  }
  1492   1521     if( pMWin->eStart==TK_FOLLOWING ){
  1493   1522       sqlite3VdbeJumpHere(v, addrIfPos2);
  1494   1523     }
  1495   1524   
  1496   1525     if( pMWin->eStart==TK_CURRENT 
  1497   1526      || pMWin->eStart==TK_PRECEDING 
  1498   1527      || pMWin->eStart==TK_FOLLOWING 
  1499   1528     ){
  1500   1529       int addrJumpHere = 0;
  1501   1530       if( pMWin->eStart==TK_PRECEDING ){
  1502   1531         addrJumpHere = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0 , 1);
  1503   1532       }
  1504         -    if( bRange ){
  1505         -      sqlite3VdbeAddOp3(v, OP_IfPos, regPeer, sqlite3VdbeCurrentAddr(v)+2, 1);
  1506         -      addrJumpHere = sqlite3VdbeAddOp0(v, OP_Goto);
  1507         -    }
  1508   1533       sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
  1509   1534       windowAggStep(pParse, pMWin, csrStart, 1, regArg, regSize);
  1510         -    if( bRange ){
  1511         -      sqlite3VdbeAddOp2(v, OP_Goto, 0, addrJumpHere-1);
  1512         -    }
  1513   1535       if( addrJumpHere ){
  1514   1536         sqlite3VdbeJumpHere(v, addrJumpHere);
  1515   1537       }
  1516   1538     }
  1517   1539     if( pMWin->eEnd==TK_FOLLOWING ){
  1518   1540       sqlite3VdbeJumpHere(v, addrIfPos1);
  1519   1541     }
................................................................................
  1557   1579   **
  1558   1580   **     ResetSorter (csr)
  1559   1581   **     Return
  1560   1582   **
  1561   1583   ** ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  1562   1584   ** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  1563   1585   ** RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
  1564         -**
  1565         -**   TODO.
  1566   1586   */
  1567   1587   static void windowCodeCacheStep(
  1568   1588     Parse *pParse, 
  1569   1589     Select *p,
  1570   1590     WhereInfo *pWInfo,
  1571   1591     int regGosub, 
  1572   1592     int addrGosub
................................................................................
  1578   1598     int addr;
  1579   1599     ExprList *pPart = pMWin->pPartition;
  1580   1600     ExprList *pOrderBy = pMWin->pOrderBy;
  1581   1601     int nPeer = pOrderBy->nExpr;
  1582   1602     int regNewPeer;
  1583   1603   
  1584   1604     int addrGoto;                   /* Address of Goto used to jump flush_par.. */
  1585         -  int addrRewind;                 /* Address of Rewind that starts loop */
         1605  +  int addrNext;                   /* Jump here for next iteration of loop */
  1586   1606     int regFlushPart;
  1587   1607     int lblFlushPart;
  1588   1608     int csrLead;
  1589   1609     int regCtr;
  1590   1610     int regArg;                     /* Register array to martial function args */
  1591   1611     int regSize;
  1592   1612     int nArg;
         1613  +  int bReverse;
         1614  +  int lblEmpty;
  1593   1615   
  1594   1616     assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT) 
  1595   1617          || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED) 
  1596   1618          || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT) 
         1619  +       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED) 
  1597   1620     );
  1598   1621   
         1622  +  lblEmpty = sqlite3VdbeMakeLabel(v);
         1623  +  bReverse = (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED);
  1599   1624     regNewPeer = pParse->nMem+1;
  1600   1625     pParse->nMem += nPeer;
  1601   1626   
  1602   1627     /* Allocate register and label for the "flush_partition" sub-routine. */
  1603   1628     regFlushPart = ++pParse->nMem;
  1604   1629     lblFlushPart = sqlite3VdbeMakeLabel(v);
  1605   1630   
................................................................................
  1614   1639     sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+2);
  1615   1640     sqlite3VdbeAddOp2(v, OP_OpenDup, csrLead, pMWin->iEphCsr);
  1616   1641   
  1617   1642     /* Initialize the accumulator register for each window function to NULL */
  1618   1643     regArg = windowInitAccum(pParse, pMWin);
  1619   1644   
  1620   1645     sqlite3VdbeAddOp2(v, OP_Integer, 0, regCtr);
  1621         -  addrRewind = sqlite3VdbeAddOp1(v, OP_Rewind, csrLead);
  1622         -  sqlite3VdbeAddOp1(v, OP_Rewind, pMWin->iEphCsr);
         1646  +  sqlite3VdbeAddOp2(v, OP_Rewind, csrLead, lblEmpty);
         1647  +  sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, lblEmpty);
  1623   1648   
  1624         -  if( pOrderBy && pMWin->eEnd==TK_CURRENT ){
  1625         -    int bCurrent = (pMWin->eEnd==TK_CURRENT && pMWin->eStart==TK_CURRENT);
         1649  +  if( bReverse ){
         1650  +    int addr = sqlite3VdbeCurrentAddr(v);
         1651  +    windowAggStep(pParse, pMWin, csrLead, 0, regArg, regSize);
         1652  +    sqlite3VdbeAddOp2(v, OP_Next, csrLead, addr);
         1653  +    sqlite3VdbeAddOp2(v, OP_Rewind, csrLead, lblEmpty);
         1654  +  }
         1655  +  addrNext = sqlite3VdbeCurrentAddr(v);
         1656  +
         1657  +  if( pOrderBy && (pMWin->eEnd==TK_CURRENT || pMWin->eStart==TK_CURRENT) ){
         1658  +    int bCurrent = (pMWin->eStart==TK_CURRENT);
  1626   1659       int addrJump = 0;             /* Address of OP_Jump below */
  1627   1660       if( pMWin->eType==TK_RANGE ){
  1628   1661         int iOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0);
  1629   1662         int regPeer = pMWin->regPart + (pPart ? pPart->nExpr : 0);
  1630   1663         KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
  1631   1664         for(k=0; k<nPeer; k++){
  1632   1665           sqlite3VdbeAddOp3(v, OP_Column, csrLead, iOff+k, regNewPeer+k);
................................................................................
  1633   1666         }
  1634   1667         addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
  1635   1668         sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  1636   1669         addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
  1637   1670         sqlite3VdbeAddOp3(v, OP_Copy, regNewPeer, regPeer, nPeer-1);
  1638   1671       }
  1639   1672   
  1640         -    windowReturnRows(pParse, pMWin, regCtr, 0, regGosub, addrGosub, 
         1673  +    windowReturnRows(pParse, pMWin, regCtr, regGosub, addrGosub, 
  1641   1674           (bCurrent ? regArg : 0), (bCurrent ? regSize : 0)
  1642   1675       );
  1643   1676       if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
  1644   1677     }
  1645   1678   
  1646         -  windowAggStep(pParse, pMWin, csrLead, 0, regArg, regSize);
         1679  +  if( bReverse==0 ){
         1680  +    windowAggStep(pParse, pMWin, csrLead, 0, regArg, regSize);
         1681  +  }
  1647   1682     sqlite3VdbeAddOp2(v, OP_AddImm, regCtr, 1);
  1648         -  sqlite3VdbeAddOp2(v, OP_Next, csrLead, addrRewind+2);
         1683  +  sqlite3VdbeAddOp2(v, OP_Next, csrLead, addrNext);
  1649   1684   
  1650         -  windowReturnRows(pParse, pMWin, regCtr, 1, regGosub, addrGosub, 0, 0);
         1685  +  windowReturnRows(pParse, pMWin, regCtr, regGosub, addrGosub, 0, 0);
  1651   1686   
  1652         -  sqlite3VdbeJumpHere(v, addrRewind);
  1653         -  sqlite3VdbeJumpHere(v, addrRewind+1);
         1687  +  sqlite3VdbeResolveLabel(v, lblEmpty);
  1654   1688     sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
  1655   1689     sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  1656   1690   
  1657   1691     /* Jump to here to skip over flush_partition */
  1658   1692     sqlite3VdbeJumpHere(v, addrGoto);
  1659   1693   }
  1660   1694   
................................................................................
  1801   1835   
  1802   1836     windowAggFinal(pParse, pMWin, 1);
  1803   1837     sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3);
  1804   1838     sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  1805   1839     sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1);
  1806   1840   }
  1807   1841   
         1842  +/*
         1843  +** Allocate and return a duplicate of the Window object indicated by the
         1844  +** third argument. Set the Window.pOwner field of the new object to
         1845  +** pOwner.
         1846  +*/
  1808   1847   Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p){
  1809   1848     Window *pNew = 0;
  1810   1849     if( p ){
  1811   1850       pNew = sqlite3DbMallocZero(db, sizeof(Window));
  1812   1851       if( pNew ){
  1813   1852         pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0);
  1814   1853         pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0);
................................................................................
  1835   1874     Parse *pParse,                  /* Parse context */
  1836   1875     Select *p,                      /* Rewritten SELECT statement */
  1837   1876     WhereInfo *pWInfo,              /* Context returned by sqlite3WhereBegin() */
  1838   1877     int regGosub,                   /* Register for OP_Gosub */
  1839   1878     int addrGosub                   /* OP_Gosub here to return each row */
  1840   1879   ){
  1841   1880     Window *pMWin = p->pWin;
  1842         -  Window *pWin;
         1881  +  ExprList *pOrderBy = pMWin->pOrderBy;
  1843   1882   
  1844         -  /* Call windowCodeRowExprStep() for all window modes *except*:
         1883  +  /* Call windowCodeRowExprStep() for all "ROWS" window modes except:
  1845   1884     **
  1846         -  **   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  1847         -  **   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  1848         -  **   RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  1849         -  **   ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         1885  +  **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  1850   1886     */
  1851   1887     if( (pMWin->eType==TK_ROWS 
  1852         -   && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy))
  1853         -   || (pMWin->eStart==TK_CURRENT&&pMWin->eEnd==TK_UNBOUNDED&&pMWin->pOrderBy)
         1888  +   && (pMWin->eStart!=TK_UNBOUNDED || pMWin->eEnd!=TK_CURRENT || !pOrderBy))
  1854   1889     ){
  1855   1890       windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
  1856         -    return;
  1857         -  }
         1891  +  }else{
         1892  +    Window *pWin;
         1893  +    int bCache = 0;
         1894  +
         1895  +    if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED && pOrderBy ){
         1896  +      bCache = 1;
         1897  +    }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  +      for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
         1902  +        FuncDef *pFunc = pWin->pFunc;
         1903  +        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  +          ){
         1909  +          bCache = 1;
         1910  +          break;
         1911  +        }
         1912  +      }
         1913  +    }
  1858   1914   
  1859         -  /* Call windowCodeCacheStep() if there is a window function that requires
  1860         -  ** that the entire partition be cached in a temp table before any rows
  1861         -  ** are returned.  */
  1862         -  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
  1863         -    FuncDef *pFunc = pWin->pFunc;
  1864         -    if( (pFunc->funcFlags & SQLITE_FUNC_WINDOW_SIZE)
  1865         -     || (pFunc->xSFunc==nth_valueStepFunc)
  1866         -     || (pFunc->xSFunc==first_valueStepFunc)
  1867         -     || (pFunc->xSFunc==leadStepFunc)
  1868         -     || (pFunc->xSFunc==lagStepFunc)
  1869         -    ){
         1915  +    /* Otherwise, call windowCodeDefaultStep().  */
         1916  +    if( bCache ){
  1870   1917         windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub);
  1871         -      return;
         1918  +    }else{
         1919  +      windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
  1872   1920       }
  1873   1921     }
  1874         -
  1875         -  /* Otherwise, call windowCodeDefaultStep().  */
  1876         -  windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
  1877   1922   }
  1878   1923   

Changes to test/window4.tcl.

   152    152   execsql_test 4.2 {
   153    153     SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
   154    154   }
   155    155   
   156    156   execsql_test 4.3 {
   157    157     SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
   158    158   }
          159  +
          160  +execsql_test 4.4 {
          161  +  SELECT sum(b) OVER (
          162  +    ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          163  +  ) FROM ttt;
          164  +}
   159    165   
   160    166   
   161    167   finish_test
   162    168   

Changes to test/window4.test.

   235    235     SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
   236    236   } {1   2   3}
   237    237   
   238    238   do_execsql_test 4.3 {
   239    239     SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
   240    240   } {1   2   3}
   241    241   
          242  +do_execsql_test 4.4 {
          243  +  SELECT sum(b) OVER (
          244  +    ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          245  +  ) FROM ttt;
          246  +} {18   17   15   12   11   9   6   5   3}
          247  +
   242    248   finish_test