SQLite

}
frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
}

range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }



frame_bound_s(A) ::= frame_bound(X). { A = X; }
frame_bound_s(A) ::= UNBOUNDED PRECEDING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
frame_bound_e(A) ::= frame_bound(X). { A = X; }
frame_bound_e(A) ::= UNBOUNDED FOLLOWING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}

  int eEnd,         /* End type: CURRENT, FOLLOWING, TK_UNBOUNDED, PRECEDING */
  Expr *pEnd        /* End window size if TK_FOLLOWING or PRECEDING */
){
  Window *pWin = 0;
  int bImplicitFrame = 0;

  /* Parser assures the following: */
  assert( eType==0 || eType==TK_RANGE || eType==TK_ROWS );
  assert( eStart==TK_CURRENT || eStart==TK_PRECEDING
           || eStart==TK_UNBOUNDED || eStart==TK_FOLLOWING );
  assert( eEnd==TK_CURRENT || eEnd==TK_FOLLOWING
           || eEnd==TK_UNBOUNDED || eEnd==TK_PRECEDING );
  assert( (eStart==TK_PRECEDING || eStart==TK_FOLLOWING)==(pStart!=0) );
  assert( (eEnd==TK_FOLLOWING || eEnd==TK_PRECEDING)==(pEnd!=0) );

                             pWin->regResult);
        sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
      }
    }
  }
}

/*
** This function generates VM code to invoke the sub-routine at address
** lblFlushPart once for each partition with the entire partition cached in
** the Window.iEphCsr temp table.
*/
static void windowPartitionCache(
  Parse *pParse,
  Select *p,                      /* The rewritten SELECT statement */
  WhereInfo *pWInfo,              /* WhereInfo to call WhereEnd() on */
  int regFlushPart,               /* Register to use with Gosub lblFlushPart */
  int lblFlushPart,               /* Subroutine to Gosub to */
  int *pRegSize                   /* OUT: Register containing partition size */
){
  Window *pMWin = p->pWin;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int iSubCsr = p->pSrc->a[0].iCursor;
  int nSub = p->pSrc->a[0].pTab->nCol;
  int k;

  int reg = pParse->nMem+1;
  int regRecord = reg+nSub;
  int regRowid = regRecord+1;

  *pRegSize = regRowid;
  pParse->nMem += nSub + 2;

  /* Load the column values for the row returned by the sub-select
  ** into an array of registers starting at reg. */
  for(k=0; k<nSub; k++){
    sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
  }
  sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord);

  /* Check if this is the start of a new partition. If so, call the
  ** flush_partition sub-routine.  */
  if( pMWin->pPartition ){
    int addr;
    ExprList *pPart = pMWin->pPartition;
    int nPart = pPart->nExpr;
    int regNewPart = reg + pMWin->nBufferCol;
    KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);

    addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2);
    VdbeCoverageEqNe(v);
    sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
    sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart);
    VdbeComment((v, "call flush_partition"));
  }

  /* Buffer the current row in the ephemeral table. */
  sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
  sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);

  /* End of the input loop */
  sqlite3WhereEnd(pWInfo);

  /* Invoke "flush_partition" to deal with the final (or only) partition */
  sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart);
  VdbeComment((v, "call flush_partition"));
}

/*
** Invoke the sub-routine at regGosub (generated by code in select.c) to
** return the current row of Window.iEphCsr. If all window functions are
** aggregate window functions that use the standard API, a single
** OP_Gosub instruction is all that this routine generates. Extra VM code
** for per-row processing is only generated for the following built-in window
** functions:

      sqlite3VdbeResolveLabel(v, lbl);
      sqlite3ReleaseTempReg(pParse, tmpReg);
    }
  }
  sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
}

/*
** Invoke the code generated by windowReturnOneRow() and, optionally, the
** xInverse() function for each window function, for one or more rows
** from the Window.iEphCsr temp table. This routine generates VM code
** similar to:
**
**   while( regCtr>0 ){
**     regCtr--;
**     windowReturnOneRow()
**     if( bInverse ){
**       AggInverse
**     }
**     Next (Window.iEphCsr)
**   }
*/
static void windowReturnRows(
  Parse *pParse,
  Window *pMWin,                  /* List of window functions */
  int regCtr,                     /* Register containing number of rows */
  int regGosub,                   /* Register for Gosub addrGosub */
  int addrGosub,                  /* Address of sub-routine for ReturnOneRow */
  int regInvArg,                  /* Array of registers for xInverse args */
  int regInvSize                  /* Register containing size of partition */
){
  int addr;
  Vdbe *v = sqlite3GetVdbe(pParse);
  windowAggFinal(pParse, pMWin, 0);
  addr = sqlite3VdbeAddOp3(v, OP_IfPos, regCtr, sqlite3VdbeCurrentAddr(v)+2 ,1);
  VdbeCoverage(v);
  sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
  windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
  if( regInvArg ){
    windowAggStep(pParse, pMWin, pMWin->iEphCsr, 1, regInvArg, regInvSize);
  }
  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, addr);
  VdbeCoverage(v);
  sqlite3VdbeJumpHere(v, addr+1);   /* The OP_Goto */
}

/*
** Generate code to set the accumulator register for each window function
** in the linked list passed as the second argument to NULL. And perform
** any equivalent initialization required by any built-in window functions
** in the list.
*/
static int windowInitAccum(Parse *pParse, Window *pMWin){

     || (pFunc->zName==lagName)
    ){
      return 1;
    }
  }
  return 0;
}



































typedef struct WindowCodeArg WindowCodeArg;





struct WindowCodeArg {
  Parse *pParse;
  Window *pMWin;
  Vdbe *pVdbe;
  int regGosub;
  int addrGosub;
  int regArg;




};

#define WINDOW_RETURN_ROW 1
#define WINDOW_AGGINVERSE 2
#define WINDOW_AGGSTEP    3























static int windowCodeOp(
 WindowCodeArg *p,
 int op,
 int csr,
 int regCountdown,
 int jumpOnEof
){


  Window *pMWin = p->pMWin;
  int ret = 0;
  Vdbe *v = p->pVdbe;
  int addrIf = 0; 




  /* Special case - WINDOW_AGGINVERSE is always a no-op if the frame
  ** starts with UNBOUNDED PRECEDING. */
  if( op==WINDOW_AGGINVERSE && pMWin->eStart==TK_UNBOUNDED ){
    assert( regCountdown==0 && jumpOnEof==0 );
    return 0;
  }

  if( regCountdown>0 ){
    addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
  }





  switch( op ){
    case WINDOW_RETURN_ROW:
      windowAggFinal(p->pParse, pMWin, 0);

      windowReturnOneRow(p->pParse, pMWin, p->regGosub, p->addrGosub);
      break;

    case WINDOW_AGGINVERSE:


      windowAggStep(p->pParse, pMWin, csr, 1, p->regArg, pMWin->regSize);
      break;

    case WINDOW_AGGSTEP:


      windowAggStep(p->pParse, pMWin, csr, 0, p->regArg, pMWin->regSize);
      break;
  }

  if( jumpOnEof ){
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
    ret = sqlite3VdbeAddOp0(v, OP_Goto);
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1);


  }

  if( regCountdown>0 ){
    sqlite3VdbeJumpHere(v, addrIf);
  }







  return ret;
}






/*
** This function - windowCodeStep() - generates the VM code that reads data
** from the sub-select and returns rows to the consumer. For the simplest
** case:
**
**     ROWS BETWEEN <expr1> PRECEDING AND <expr2> FOLLOWING
**
** The VM code generated is equivalent in spirit to the following:
**
**     while( !eof ){
**       if( new partition ){
**         Gosub flush
**       }    
**       Insert new row into eph table.
**     
**       if( first row of partition ){
**         Rewind(csrEnd, skipNext=1)
**         Rewind(csrStart, skipNext=1)
**         Rewind(csrCurrent, skipNext=1)
**     
**         regEnd = <expr2>          // FOLLOWING expression
**         regStart = <expr1>        // PRECEDING expression
**       }else{
**         if( (regEnd--)<=0 ){
**           Next(csrCurrent)
**           Return one row.
**           if( (regStart--)<0 ){
**             Next(csrStart)
**             AggInverse(csrStart)
**           }
**         }
**       }    
**     
**       Next(csrEnd)
**       AggStep(csrEnd)
**     }    
**     flush:
**       while( 1 ){ 
**         Next(csrCurrent)
**         if( eof ) break
**         Return one row.
**         if( (regStart--)<0 ){
**           Next(csrStart)
**           AggInverse(csrStart)
**         }
**       }    
**       Empty eph table.
**
** More generally, the pattern used for all window types is:
**
**     while( !eof ){

  Parse *pParse, 
  Select *p,
  WhereInfo *pWInfo,
  int regGosub, 
  int addrGosub
){
  Window *pMWin = p->pWin;

  Vdbe *v = sqlite3GetVdbe(pParse);
  int regFlushPart;               /* Register for "Gosub flush_partition" */

  int regArg;
  int csrCurrent = pMWin->iEphCsr;
  int csrWrite = csrCurrent+1;
  int csrStart = csrCurrent+2;
  int csrEnd = csrCurrent+3;


  int iSubCsr = p->pSrc->a[0].iCursor;      /* Cursor of sub-select */
  int nSub = p->pSrc->a[0].pTab->nCol;      /* Number of cols returned by sub */
  int iCol;                                 /* To iterate through sub cols */

  int addrGoto;
  int addrIf;
  int addrGosubFlush;
  int addrInteger;
  int addrCacheRewind;
  int addrCacheNext;

  int addrShortcut = 0;
  int addrEmpty = 0;


  int bCache = windowCachePartition(pMWin);

  int regStart = 0;               /* Value of <expr> PRECEDING */
  int regEnd = 0;                 /* Value of <expr> FOLLOWING */

  int reg = pParse->nMem+1;
  int regRecord = reg+nSub;
  int regRowid = regRecord+1;


  WindowCodeArg s;

  memset(&s, 0, sizeof(WindowCodeArg));
  s.pParse = pParse;
  s.pMWin = pMWin;
  s.pVdbe = v;
  s.regGosub = regGosub;
  s.addrGosub = addrGosub;




  pParse->nMem += 1 + nSub + 1;

  regFlushPart = ++pParse->nMem;

  if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
    regStart = ++pParse->nMem;
  }
  if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
    regEnd = ++pParse->nMem;
  }














  assert( pMWin->eStart==TK_PRECEDING 
       || pMWin->eStart==TK_CURRENT 
       || pMWin->eStart==TK_FOLLOWING 
       || pMWin->eStart==TK_UNBOUNDED 
  );
  assert( pMWin->eEnd==TK_FOLLOWING 

  if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
    windowAggFinal(pParse, pMWin, 0);
    if( bCache ){
      sqlite3VdbeAddOp2(v, OP_Rowid, csrWrite, regRowid);
      sqlite3VdbeAddOp3(v, OP_NotExists, csrCurrent, 0, regRowid);
      windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
      sqlite3VdbeAddOp2(v, OP_Next, csrWrite, addrCacheRewind+1);
    }else{
      sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
      windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
      sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
    }
    addrShortcut = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING && regEnd ){
    assert( pMWin->eEnd==TK_FOLLOWING );
    sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  }

  if( pMWin->eStart!=TK_UNBOUNDED ){
    sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1);
  }
  sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1);











  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);

  /* Begin generating SECOND_ROW_CODE */
  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.SECOND_ROW_CODE"));
  if( bCache ){
    addrCacheNext = sqlite3VdbeCurrentAddr(v);



  }else{
    sqlite3VdbeJumpHere(v, addrIf);
  }



  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 0);
      windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
    }
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
  }else{
    int addr;
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( regEnd ) addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
      windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
      windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
      if( regEnd ) sqlite3VdbeJumpHere(v, addr);
    }
  }



  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.SECOND_ROW_CODE"));

  /* End of the main input loop */
  sqlite3VdbeJumpHere(v, addrGoto);
  if( bCache ){
    sqlite3VdbeAddOp2(v, OP_Next, csrWrite, addrCacheNext);
    sqlite3VdbeJumpHere(v, addrCacheRewind); 

    addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
    sqlite3VdbeJumpHere(v, addrGosubFlush);
  }

  VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep.FLUSH_CODE"));
  addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  if( pMWin->eEnd==TK_PRECEDING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, regEnd, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    if( pMWin->eEnd==TK_UNBOUNDED ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regStart, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, 0, 1);
    }else{
      assert( pMWin->eEnd==TK_FOLLOWING );
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, regEnd, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 1);
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak2);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak1);
    sqlite3VdbeJumpHere(v, addrBreak3);
  }else{
    int addrBreak;
    int addrStart;
    windowCodeOp(&s, WINDOW_AGGSTEP, csrEnd, 0, 0);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, csrCurrent, 0, 1);
    windowCodeOp(&s, WINDOW_AGGINVERSE, csrStart, regStart, 0);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak);
  }

  sqlite3VdbeJumpHere(v, addrEmpty);

  if( bCache && addrShortcut>0 ) sqlite3VdbeJumpHere(v, addrShortcut);
  sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
  sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regSize);
  if( bCache==0 ) sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  VdbeModuleComment((pParse->pVdbe, "End windowCodeStep.FLUSH_CODE"));
  if( pMWin->pPartition ){
    sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
    sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  }
}

/*
** This function does the work of sqlite3WindowCodeStep() for cases that
** would normally be handled by windowCodeDefaultStep() when there are
** one or more built-in window-functions that require the entire partition
** to be cached in a temp table before any rows can be returned. Additionally.
** "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is always handled by
** this function.
**
** Pseudo-code corresponding to the VM code generated by this function
** for each type of window follows.
**
** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
**
**   flush_partition:
**     Once {
**       OpenDup (iEphCsr -> csrLead)
**     }
**     Integer ctr 0
**     foreach row (csrLead){
**       if( new peer ){
**         AggFinal (xValue)
**         for(i=0; i<ctr; i++){
**           Gosub addrGosub
**           Next iEphCsr
**         }
**         Integer ctr 0
**       }
**       AggStep (csrLead)
**       Incr ctr
**     }
**
**     AggFinal (xFinalize)
**     for(i=0; i<ctr; i++){
**       Gosub addrGosub
**       Next iEphCsr
**     }
**
**     ResetSorter (csr)
**     Return
**
** ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
**
**   As above, except that the "if( new peer )" branch is always taken.
**
** RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
**
**   As above, except that each of the for() loops becomes:
**
**         for(i=0; i<ctr; i++){
**           Gosub addrGosub
**           AggInverse (iEphCsr)
**           Next iEphCsr
**         }
**
** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
**
**   flush_partition:
**     Once {
**       OpenDup (iEphCsr -> csrLead)
**     }
**     foreach row (csrLead) {
**       AggStep (csrLead)
**     }
**     foreach row (iEphCsr) {
**       Gosub addrGosub
**     }
** 
** RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
**
**   flush_partition:
**     Once {
**       OpenDup (iEphCsr -> csrLead)
**     }
**     foreach row (csrLead){
**       AggStep (csrLead)
**     }
**     Rewind (csrLead)
**     Integer ctr 0
**     foreach row (csrLead){
**       if( new peer ){
**         AggFinal (xValue)
**         for(i=0; i<ctr; i++){
**           Gosub addrGosub
**           AggInverse (iEphCsr)
**           Next iEphCsr
**         }
**         Integer ctr 0
**       }
**       Incr ctr
**     }
**
**     AggFinal (xFinalize)
**     for(i=0; i<ctr; i++){
**       Gosub addrGosub
**       Next iEphCsr
**     }
**
**     ResetSorter (csr)
**     Return
*/
static void windowCodeCacheStep(
  Parse *pParse, 
  Select *p,
  WhereInfo *pWInfo,
  int regGosub, 
  int addrGosub
){
  Window *pMWin = p->pWin;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int k;
  int addr;
  ExprList *pPart = pMWin->pPartition;
  ExprList *pOrderBy = pMWin->pOrderBy;
  int nPeer = pOrderBy ? pOrderBy->nExpr : 0;
  int regNewPeer;

  int addrGoto;                   /* Address of Goto used to jump flush_par.. */
  int addrNext;                   /* Jump here for next iteration of loop */
  int regFlushPart;
  int lblFlushPart;
  int csrLead;
  int regCtr;
  int regArg;                     /* Register array to martial function args */
  int regSize;
  int lblEmpty;
  int bReverse = pMWin->pOrderBy && pMWin->eStart==TK_CURRENT 
          && pMWin->eEnd==TK_UNBOUNDED;

  assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT) 
       || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED) 
       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT) 
       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED) 
  );

  lblEmpty = sqlite3VdbeMakeLabel(pParse);
  regNewPeer = pParse->nMem+1;
  pParse->nMem += nPeer;

  /* Allocate register and label for the "flush_partition" sub-routine. */
  regFlushPart = ++pParse->nMem;
  lblFlushPart = sqlite3VdbeMakeLabel(pParse);

  csrLead = pParse->nTab++;
  regCtr = ++pParse->nMem;

  windowPartitionCache(pParse, p, pWInfo, regFlushPart, lblFlushPart, &regSize);
  addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);

  /* Start of "flush_partition" */
  sqlite3VdbeResolveLabel(v, lblFlushPart);
  sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+2);
  VdbeCoverage(v);
  sqlite3VdbeAddOp2(v, OP_OpenDup, csrLead, pMWin->iEphCsr);

  /* Initialize the accumulator register for each window function to NULL */
  regArg = windowInitAccum(pParse, pMWin);

  sqlite3VdbeAddOp2(v, OP_Integer, 0, regCtr);
  sqlite3VdbeAddOp2(v, OP_Rewind, csrLead, lblEmpty);
  VdbeCoverage(v);
  sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, lblEmpty);
  VdbeCoverageNeverTaken(v);

  if( bReverse ){
    int addr2 = sqlite3VdbeCurrentAddr(v);
    windowAggStep(pParse, pMWin, csrLead, 0, regArg, regSize);
    sqlite3VdbeAddOp2(v, OP_Next, csrLead, addr2);
    VdbeCoverage(v);
    sqlite3VdbeAddOp2(v, OP_Rewind, csrLead, lblEmpty);
    VdbeCoverageNeverTaken(v);
  }
  addrNext = sqlite3VdbeCurrentAddr(v);

  if( pOrderBy && (pMWin->eEnd==TK_CURRENT || pMWin->eStart==TK_CURRENT) ){
    int bCurrent = (pMWin->eStart==TK_CURRENT);
    int addrJump = 0;             /* Address of OP_Jump below */
    if( pMWin->eType==TK_RANGE ){
      int iOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0);
      int regPeer = pMWin->regPart + (pPart ? pPart->nExpr : 0);
      KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
      for(k=0; k<nPeer; k++){
        sqlite3VdbeAddOp3(v, OP_Column, csrLead, iOff+k, regNewPeer+k);
      }
      addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
      sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
      addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
      VdbeCoverage(v);
      sqlite3VdbeAddOp3(v, OP_Copy, regNewPeer, regPeer, nPeer-1);
    }

    windowReturnRows(pParse, pMWin, regCtr, regGosub, addrGosub, 
        (bCurrent ? regArg : 0), (bCurrent ? regSize : 0)
    );
    if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
  }

  if( bReverse==0 ){
    windowAggStep(pParse, pMWin, csrLead, 0, regArg, regSize);
  }
  sqlite3VdbeAddOp2(v, OP_AddImm, regCtr, 1);
  sqlite3VdbeAddOp2(v, OP_Next, csrLead, addrNext);
  VdbeCoverage(v);

  windowReturnRows(pParse, pMWin, regCtr, regGosub, addrGosub, 0, 0);

  sqlite3VdbeResolveLabel(v, lblEmpty);
  sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
  sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);

  /* Jump to here to skip over flush_partition */
  sqlite3VdbeJumpHere(v, addrGoto);
}


/*
** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
**
**   ...
**     if( new partition ){
**       AggFinal (xFinalize)
**       Gosub addrGosub
**       ResetSorter eph-table
**     }
**     else if( new peer ){
**       AggFinal (xValue)
**       Gosub addrGosub
**       ResetSorter eph-table
**     }
**     AggStep
**     Insert (record into eph-table)
**   sqlite3WhereEnd()
**   AggFinal (xFinalize)
**   Gosub addrGosub
**
** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
**
**   As above, except take no action for a "new peer". Invoke
**   the sub-routine once only for each partition.
**
** RANGE BETWEEN CURRENT ROW AND CURRENT ROW
**
**   As above, except that the "new peer" condition is handled in the
**   same way as "new partition" (so there is no "else if" block).
**
** ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
** 
**   As above, except assume every row is a "new peer".
*/
static void windowCodeDefaultStep(
  Parse *pParse, 
  Select *p,
  WhereInfo *pWInfo,
  int regGosub, 
  int addrGosub
){
  Window *pMWin = p->pWin;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int k;
  int iSubCsr = p->pSrc->a[0].iCursor;
  int nSub = p->pSrc->a[0].pTab->nCol;
  int reg = pParse->nMem+1;
  int regRecord = reg+nSub;
  int regRowid = regRecord+1;
  int addr;
  ExprList *pPart = pMWin->pPartition;
  ExprList *pOrderBy = pMWin->pOrderBy;

  assert( pMWin->eType==TK_RANGE 
      || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT)
  );

  assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT)
       || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED)
       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT)
       || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED && !pOrderBy)
  );

  if( pMWin->eEnd==TK_UNBOUNDED ){
    pOrderBy = 0;
  }

  pParse->nMem += nSub + 2;

  /* Load the individual column values of the row returned by
  ** the sub-select into an array of registers. */
  for(k=0; k<nSub; k++){
    sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
  }

  /* Check if this is the start of a new partition or peer group. */
  if( pPart || pOrderBy ){
    int nPart = (pPart ? pPart->nExpr : 0);
    int addrGoto = 0;
    int addrJump = 0;
    int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);

    if( pPart ){
      int regNewPart = reg + pMWin->nBufferCol;
      KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
      addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
      sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
      addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
      VdbeCoverageEqNe(v);
      windowAggFinal(pParse, pMWin, 1);
      if( pOrderBy ){
        addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
      }
    }

    if( pOrderBy ){
      int regNewPeer = reg + pMWin->nBufferCol + nPart;
      int regPeer = pMWin->regPart + nPart;

      if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
      if( pMWin->eType==TK_RANGE ){
        KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
        addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
        sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
        addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
        VdbeCoverage(v);
      }else{
        addrJump = 0;
      }
      windowAggFinal(pParse, pMWin, pMWin->eStart==TK_CURRENT);
      if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
    }

    sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3);
    VdbeCoverage(v);
    sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
    sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1);
    VdbeCoverage(v);

    sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
    sqlite3VdbeAddOp3(
        v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
    );

    if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
  }

  /* Invoke step function for window functions */
  windowAggStep(pParse, pMWin, -1, 0, reg, 0);

  /* Buffer the current row in the ephemeral table. */
  if( pMWin->nBufferCol>0 ){
    sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, pMWin->nBufferCol, regRecord);
  }else{
    sqlite3VdbeAddOp2(v, OP_Blob, 0, regRecord);
    sqlite3VdbeAppendP4(v, (void*)"", 0);
  }
  sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
  sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);

  /* End the database scan loop. */
  sqlite3WhereEnd(pWInfo);

  windowAggFinal(pParse, pMWin, 1);
  sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr,sqlite3VdbeCurrentAddr(v)+3);
  VdbeCoverage(v);
  sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
  sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)-1);
  VdbeCoverage(v);
}

/*
** Allocate and return a duplicate of the Window object indicated by the
** third argument. Set the Window.pOwner field of the new object to
** pOwner.
*/
Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p){

void sqlite3WindowCodeStep(
  Parse *pParse,                  /* Parse context */
  Select *p,                      /* Rewritten SELECT statement */
  WhereInfo *pWInfo,              /* Context returned by sqlite3WhereBegin() */
  int regGosub,                   /* Register for OP_Gosub */
  int addrGosub                   /* OP_Gosub here to return each row */
){
  Window *pMWin = p->pWin;

  /* There are three different functions that may be used to do the work
  ** of this one, depending on the window frame and the specific built-in
  ** window functions used (if any).
  **
  ** windowCodeRowExprStep() handles all "ROWS" window frames, except for:
  **
  **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  **
  ** The exception is because windowCodeRowExprStep() implements all window
  ** frame types by caching the entire partition in a temp table, and
  ** "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" is easy enough to
  ** implement without such a cache.
  **
  ** windowCodeCacheStep() is used for:
  **
  **   RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  **
  ** It is also used for anything not handled by windowCodeRowExprStep() 
  ** that invokes a built-in window function that requires the entire 
  ** partition to be cached in a temp table before any rows are returned
  ** (e.g. nth_value() or percent_rank()).
  **
  ** Finally, assuming there is no built-in window function that requires
  ** the partition to be cached, windowCodeDefaultStep() is used for:
  **
  **   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
  **   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  **   RANGE BETWEEN CURRENT ROW AND CURRENT ROW 
  **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  **
  ** windowCodeDefaultStep() is the only one of the three functions that
  ** does not cache each partition in a temp table before beginning to
  ** return rows.
  */
  if( pMWin->eType==TK_ROWS ){
    VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()"));
    windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
    VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()"));
  }else{
    Window *pWin;
    int bCache = 0;               /* True to use CacheStep() */

    if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED ){
      bCache = 1;
    }else{
      for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
        FuncDef *pFunc = pWin->pFunc;
        if( (pFunc->funcFlags & SQLITE_FUNC_WINDOW_SIZE)
         || (pFunc->zName==nth_valueName)
         || (pFunc->zName==first_valueName)
         || (pFunc->zName==leadName)
         || (pFunc->zName==lagName)
        ){
          bCache = 1;
          break;
        }
      }
    }

    /* Otherwise, call windowCodeDefaultStep().  */
    if( bCache ){
      VdbeModuleComment((pParse->pVdbe, "Begin CacheStep()"));
      windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub);
      VdbeModuleComment((pParse->pVdbe, "End CacheStep()"));
    }else{
      VdbeModuleComment((pParse->pVdbe, "Begin DefaultStep()"));
      windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
      VdbeModuleComment((pParse->pVdbe, "End DefaultStep()"));
    }
  }
}

#endif /* SQLITE_OMIT_WINDOWFUNC */

puts $::fd [subst -nocommands {
do_test $tn {
  set myres {}
  foreach r [db eval {$sql}] {
    lappend myres [format $F [set r]]
  }
  set res2 {$res2}

  foreach r [set myres] r2 [set res2] {
    if {[set r]<([set r2]-$T) || [set r]>([set r2]+$T)} {
      error "list element [set i] does not match: got=[set r] expected=[set r2]"
    }

  }
  set {} {}
} {}
}]
}

proc start_test {name date} {

do_test 9.3 {
  set myres {}
  foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
    lappend myres [format %.4f [set r]]
  }
  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}

  foreach r [set myres] r2 [set res2] {
    if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
      error "list element [set i] does not match: got=[set r] expected=[set r2]"
    }

  }
  set {} {}
} {}

do_execsql_test 9.4 {
  SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1   1 1   1 1   4 4   4 4   6 6   7 7}

do_execsql_test 9.5 {
  SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1   4 4   6 6   7 7}


do_test 9.6 {
  set myres {}
  foreach r [db eval {SELECT percent_rank() OVER () FROM t1}] {
    lappend myres [format %.4f [set r]]
  }
  set res2 {0.0000 0.0000 0.0000}

  foreach r [set myres] r2 [set res2] {
    if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
      error "list element [set i] does not match: got=[set r] expected=[set r2]"
    }

  }
  set {} {}
} {}


do_test 9.7 {
  set myres {}
  foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
    lappend myres [format %.4f [set r]]
  }
  set res2 {1.0000 1.0000 1.0000}

  foreach r [set myres] r2 [set res2] {
    if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
      error "list element [set i] does not match: got=[set r] expected=[set r2]"
    }

  }
  set {} {}
} {}

do_execsql_test 10.0 {
  DROP TABLE IF EXISTS t7;
  CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);

  { "FOLLOWING",        "TK_FOLLOWING",    WINDOWFUNC             },
  { "FOR",              "TK_FOR",          TRIGGER                },
  { "FOREIGN",          "TK_FOREIGN",      FKEY                   },
  { "FROM",             "TK_FROM",         ALWAYS                 },
  { "FULL",             "TK_JOIN_KW",      ALWAYS                 },
  { "GLOB",             "TK_LIKE_KW",      ALWAYS                 },
  { "GROUP",            "TK_GROUP",        ALWAYS                 },

  { "HAVING",           "TK_HAVING",       ALWAYS                 },
  { "IF",               "TK_IF",           ALWAYS                 },
  { "IGNORE",           "TK_IGNORE",       CONFLICT|TRIGGER       },
  { "IMMEDIATE",        "TK_IMMEDIATE",    ALWAYS                 },
  { "IN",               "TK_IN",           ALWAYS                 },
  { "INDEX",            "TK_INDEX",        ALWAYS                 },
  { "INDEXED",          "TK_INDEXED",      ALWAYS                 },