SQLite: Check-in [7d66cd20]

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

Overview

Comment:	Add things to this branch that will be required to support the EXCLUDE clause.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| window-functions
Files:	files \| file ages \| folders
SHA3-256:	7d66cd2013206ebad50c7cdb7dab9211fa8b47f5cb7067dcb314b3e0180875f8
User & Date:	dan 2019-03-14 16:36:20
Wiki:	window-functions

Context

2019-03-14
20:53		Parse EXCLUDE clauses in window frames. They do not yet work. (check-in: d03c7533 user: dan tags: window-functions)
16:36		Add things to this branch that will be required to support the EXCLUDE clause. (check-in: 7d66cd20 user: dan tags: window-functions)
2019-03-13
17:31		Merge latest trunk changes into this branch. (check-in: 0b904517 user: dan tags: window-functions)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  char *zBase;            /* Name of base window for chaining (may be NULL) */
  ExprList *pPartition;   /* PARTITION BY clause */
  ExprList *pOrderBy;     /* ORDER BY clause */
  u8 eType;               /* TK_RANGE or TK_ROWS */
  u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 bImplicitFrame;      /* True if frame was implicitly specified */

  Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
  Window *pNextWin;       /* Next window function belonging to this SELECT */
  Expr *pFilter;          /* The FILTER expression */
  FuncDef *pFunc;         /* The function */
  int iEphCsr;            /* Partition buffer or Peer buffer */
  int regAccum;
................................................................................
  int csrApp;             /* Function cursor (used by min/max) */
  int regApp;             /* Function register (also used by min/max) */
  int regPart;            /* Array of registers for PARTITION BY values */
  Expr *pOwner;           /* Expression object this window is attached to */
  int nBufferCol;         /* Number of columns in buffer table */
  int iArgCol;            /* Offset of first argument for this function */
  int regFirst;



};

#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
void sqlite3WindowAttach(Parse*, Expr*, Window*);

  char *zBase;            /* Name of base window for chaining (may be NULL) */
  ExprList *pPartition;   /* PARTITION BY clause */
  ExprList *pOrderBy;     /* ORDER BY clause */
  u8 eType;               /* TK_RANGE or TK_ROWS */
  u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  u8 bImplicitFrame;      /* True if frame was implicitly specified */
  u8 eExclude;
  Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
  Window *pNextWin;       /* Next window function belonging to this SELECT */
  Expr *pFilter;          /* The FILTER expression */
  FuncDef *pFunc;         /* The function */
  int iEphCsr;            /* Partition buffer or Peer buffer */
  int regAccum;
................................................................................
  int csrApp;             /* Function cursor (used by min/max) */
  int regApp;             /* Function register (also used by min/max) */
  int regPart;            /* Array of registers for PARTITION BY values */
  Expr *pOwner;           /* Expression object this window is attached to */
  int nBufferCol;         /* Number of columns in buffer table */
  int iArgCol;            /* Offset of first argument for this function */
  int regFirst;

  int regStartRowid;
  int regEndRowid;
};

#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
void sqlite3WindowAttach(Parse*, Expr*, Window*);

Changes to src/vdbe.c.

  pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
  if( pCx==0 ) goto no_mem;
  pCx->nullRow = 1;
  pCx->isEphemeral = 1;
  pCx->pKeyInfo = pOrig->pKeyInfo;
  pCx->isTable = pOrig->isTable;
  pCx->pgnoRoot = pOrig->pgnoRoot;

  rc = sqlite3BtreeCursor(pOrig->pBtx, pCx->pgnoRoot, BTREE_WRCSR,
                          pCx->pKeyInfo, pCx->uc.pCursor);
  /* The sqlite3BtreeCursor() routine can only fail for the first cursor
  ** opened for a database.  Since there is already an open cursor when this
  ** opcode is run, the sqlite3BtreeCursor() cannot fail */
  assert( rc==SQLITE_OK );
  break;

  pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
  if( pCx==0 ) goto no_mem;
  pCx->nullRow = 1;
  pCx->isEphemeral = 1;
  pCx->pKeyInfo = pOrig->pKeyInfo;
  pCx->isTable = pOrig->isTable;
  pCx->pgnoRoot = pOrig->pgnoRoot;
  pCx->isOrdered = pOrig->isOrdered;
  rc = sqlite3BtreeCursor(pOrig->pBtx, pCx->pgnoRoot, BTREE_WRCSR,
                          pCx->pKeyInfo, pCx->uc.pCursor);
  /* The sqlite3BtreeCursor() routine can only fail for the first cursor
  ** opened for a database.  Since there is already an open cursor when this
  ** opcode is run, the sqlite3BtreeCursor() cannot fail */
  assert( rc==SQLITE_OK );
  break;

Changes to src/window.c.

      p->nValue++;
      p->nStep = 0;
    }
    sqlite3_result_int64(pCtx, p->nValue);
  }
}













































/*
** Implementation of built-in window function rank(). Assumes that
** the window frame has been set to:
**
**   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
*/
static void rankStepFunc(
................................................................................
** Register those built-in window functions that are not also aggregates.
*/
void sqlite3WindowFunctions(void){
  static FuncDef aWindowFuncs[] = {
    WINDOWFUNCX(row_number, 0, 0),
    WINDOWFUNCX(dense_rank, 0, 0),
    WINDOWFUNCX(rank, 0, 0),
    // WINDOWFUNCX(percent_rank, 0, SQLITE_FUNC_WINDOW_SIZE),
    WINDOWFUNCALL(percent_rank, 0, 0),
    WINDOWFUNCALL(cume_dist, 0, 0),
    WINDOWFUNCALL(ntile, 1, 0),
    // WINDOWFUNCX(cume_dist, 0, SQLITE_FUNC_WINDOW_SIZE),
    // WINDOWFUNCX(ntile, 1, SQLITE_FUNC_WINDOW_SIZE),
    WINDOWFUNCALL(last_value, 1, 0),
    WINDOWFUNCNOOP(nth_value, 2, 0),
    WINDOWFUNCNOOP(first_value, 1, 0),
    WINDOWFUNCNOOP(lead, 1, 0),
    WINDOWFUNCNOOP(lead, 2, 0),
    WINDOWFUNCNOOP(lead, 3, 0),
    WINDOWFUNCNOOP(lag, 1, 0),
    WINDOWFUNCNOOP(lag, 2, 0),
    WINDOWFUNCNOOP(lag, 3, 0),
................................................................................
        if( pFunc->zName==aUp[i].zFunc ){
          sqlite3ExprDelete(db, pWin->pStart);
          sqlite3ExprDelete(db, pWin->pEnd);
          pWin->pEnd = pWin->pStart = 0;
          pWin->eType = aUp[i].eType;
          pWin->eStart = aUp[i].eStart;
          pWin->eEnd = aUp[i].eEnd;

          if( pWin->eStart==TK_FOLLOWING ){
            pWin->pStart = sqlite3Expr(db, TK_INTEGER, "1");
          }
          break;
        }
      }
    }
................................................................................
  if( pWin==0 ) goto windowAllocErr;
  pWin->eType = eType;
  pWin->eStart = eStart;
  pWin->eEnd = eEnd;
  pWin->bImplicitFrame = bImplicitFrame;
  pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);

  return pWin;

windowAllocErr:
  sqlite3ExprDelete(pParse->db, pEnd);
  sqlite3ExprDelete(pParse->db, pStart);
  return 0;
}
................................................................................
** Return 0 if the two window objects are identical, or non-zero otherwise.
** Identical window objects can be processed in a single scan.
*/
int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2){
  if( p1->eType!=p2->eType ) return 1;
  if( p1->eStart!=p2->eStart ) return 1;
  if( p1->eEnd!=p2->eEnd ) return 1;

  if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1;
  if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1;
  if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1;
  if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1;
  return 0;
}

................................................................................
    pMWin->regPart = pParse->nMem+1;
    pParse->nMem += nExpr;
    sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nExpr-1);
  }

  pMWin->regFirst = ++pParse->nMem;
  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);











  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *p = pWin->pFunc;
    if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){
      /* The inline versions of min() and max() require a single ephemeral
      ** table and 3 registers. The registers are used as follows:
      **
................................................................................
** number of rows in the current partition.
*/
static void windowAggStep(
  Parse *pParse, 
  Window *pMWin,                  /* Linked list of window functions */
  int csr,                        /* Read arguments from this cursor */
  int bInverse,                   /* True to invoke xInverse instead of xStep */
  int reg,                        /* Array of registers */
  int regPartSize                 /* Register containing size of partition */
){
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){

    int regArg;
    int nArg = windowArgCount(pWin);

    if( csr>=0 ){
      int i;

      for(i=0; i<nArg; i++){
        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
      }
      regArg = reg;
    }else{
      regArg = reg + pWin->iArgCol;
    }


    if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
      && pWin->eStart!=TK_UNBOUNDED 
    ){
      int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
      VdbeCoverage(v);
      if( bInverse==0 ){
        sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
        sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
        sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
................................................................................
        sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
        VdbeCoverageNeverTaken(v);
        sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
        sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
      }
      sqlite3VdbeJumpHere(v, addrIsNull);
    }else if( pWin->regApp ){
      assert( pWin->pFunc->zName==nth_valueName
           || pWin->pFunc->zName==first_valueName
      );
      assert( bInverse==0 || bInverse==1 );
      sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
    }else if( pWin->pFunc->zName==leadName
           || pWin->pFunc->zName==lagName
    ){
      /* no-op */
    }else{

      int addrIf = 0;
      if( pWin->pFilter ){
        int regTmp;
        assert( nArg==0 || nArg==pWin->pOwner->x.pList->nExpr );
        assert( nArg || pWin->pOwner->x.pList==0 );
        if( csr>0 ){
          regTmp = sqlite3GetTempReg(pParse);
          sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp);
        }else{
          regTmp = regArg + nArg;
        }
        addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1);
        VdbeCoverage(v);
        if( csr>0 ){
          sqlite3ReleaseTempReg(pParse, regTmp);
        }
      }
      if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
        CollSeq *pColl;
        assert( nArg>0 );
        pColl = sqlite3ExprNNCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr);
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ);
      }
      sqlite3VdbeAddOp3(v, bInverse? OP_AggInverse : OP_AggStep, 
                        bInverse, regArg, pWin->regAccum);
      sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, (u8)nArg);
      if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
    }
  }
}

/*
** Generate VM code to invoke either xValue() (bFinal==0) or xFinalize()
** (bFinal==1) for each window function in the linked list starting at
** pMWin. Or, for built-in window-functions that do not use the standard
** API, generate the equivalent VM code.
*/
static void windowAggFinal(Parse *pParse, Window *pMWin, int bFinal){
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;





  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){















    if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
     && pWin->eStart!=TK_UNBOUNDED 
    ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
      sqlite3VdbeAddOp1(v, OP_Last, pWin->csrApp);
      VdbeCoverage(v);
      sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult);
      sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
      if( bFinal ){
        sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
      }
    }else if( pWin->regApp ){

    }else{

      if( bFinal ){
        sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, windowArgCount(pWin));
        sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
        sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
        sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
      }else{
        sqlite3VdbeAddOp3(v, OP_AggValue, pWin->regAccum, windowArgCount(pWin),
                             pWin->regResult);
        sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
      }
    }
  }
}

/*
................................................................................
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    if( pFunc->zName==nth_valueName
     || pFunc->zName==first_valueName
    ){
      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(pParse);
      int tmpReg = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);

      if( pFunc->zName==nth_valueName ){
        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
        windowCheckValue(pParse, tmpReg, 2);
      }else{
        sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
      }


      sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
      sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
      VdbeCoverageNeverNull(v);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg);
      VdbeCoverageNeverTaken(v);
      sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);














      sqlite3VdbeResolveLabel(v, lbl);
      sqlite3ReleaseTempReg(pParse, tmpReg);
    }
    else if( pFunc->zName==leadName || pFunc->zName==lagName ){
      int nArg = pWin->pOwner->x.pList->nExpr;
      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(pParse);
................................................................................
  int regArg;
  int nArg = 0;
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
    nArg = MAX(nArg, windowArgCount(pWin));
    if( pFunc->zName==nth_valueName
     || pFunc->zName==first_valueName
    ){
      sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp);
      sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
    }

    if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){
      assert( pWin->eStart!=TK_UNBOUNDED );
      sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
      sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);

    }
  }
  regArg = pParse->nMem+1;
  pParse->nMem += nArg;
  return regArg;
}

/* 
** Return true if the current frame should be cached in the ephemeral table,
** even if there are no xInverse() calls required.
*/
static int windowCacheFrame(Window *pMWin){
  Window *pWin;

  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    if( (pFunc->zName==nth_valueName)
     || (pFunc->zName==first_valueName)
     || (pFunc->zName==leadName)
     || (pFunc->zName==lagName)
    ){
................................................................................

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

  if( op==WINDOW_RETURN_ROW ){
    windowAggFinal(pParse, pMWin, 0);
  }
  addrContinue = sqlite3VdbeCurrentAddr(v);
  switch( op ){
    case WINDOW_RETURN_ROW:
      csr = p->current.csr;
      reg = p->current.reg;
      windowReturnOneRow(pParse, pMWin, p->regGosub, p->addrGosub);
      break;

    case WINDOW_AGGINVERSE:
      csr = p->start.csr;
      reg = p->start.reg;




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

      break;

    case WINDOW_AGGSTEP:
      csr = p->end.csr;
      reg = p->end.reg;




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

      break;
  }

  if( op==p->eDelete ){
    sqlite3VdbeAddOp1(v, OP_Delete, csr);
    sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION);
  }
................................................................................
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0));
  }

  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);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
    windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
    sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING && pMWin->eType!=TK_RANGE && regEnd ){
................................................................................
    sqlite3VdbeJumpHere(v, addrBreak);
  }
  sqlite3VdbeJumpHere(v, addrEmpty);

  sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  if( pMWin->pPartition ){




    sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
    sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  }
}

#endif /* SQLITE_OMIT_WINDOWFUNC */








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







<



<
<

|







 







>







 







>







 







>







 







>
>
>
>
>
>
>
>
>
>







 







|
<




>


<
<
|
>
|
|
|
|
<
<
|
<
>
|
|







 







|
|



<
|
<

<
>





<
|
|
<
<
<


<
|
|
<
|







|







|
|



|



>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|






|



>

>
|
|




|
<







 







<





|




>
>
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
|
<
|
|
|

|
|
|
|
>













>







 







|












>
>
>
>
|
>





>
>
>
>
|
>







 







|







 







>
>
>
>

      p->nValue++;
      p->nStep = 0;
    }
    sqlite3_result_int64(pCtx, p->nValue);
  }
}

/*
** Implementation of built-in window function nth_value(). This
** implementation is used in "slow mode" only - when the EXCLUDE clause
** is not set to the default value "NO OTHERS".
*/
struct NthValueCtx {
  i64 nStep;
  sqlite3_value *pValue;
};
static void nth_valueStepFunc(
  sqlite3_context *pCtx, 
  int nArg,
  sqlite3_value **apArg
){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p ){
    i64 iVal = sqlite3_value_int64(apArg[1]);
    p->nStep++;
    if( iVal==p->nStep ){
      p->pValue = sqlite3_value_dup(apArg[0]);
    }
  }
  UNUSED_PARAMETER(nArg);
  UNUSED_PARAMETER(apArg);
}
static void nth_valueValueFunc(sqlite3_context *pCtx){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->pValue ){
    sqlite3_result_value(pCtx, p->pValue);
  }
}
static void nth_valueFinalizeFunc(sqlite3_context *pCtx){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->pValue ){
    sqlite3_result_value(pCtx, p->pValue);
    sqlite3_value_free(p->pValue);
    p->pValue = 0;
  }
}
#define nth_valueInvFunc noopStepFunc

/*
** Implementation of built-in window function rank(). Assumes that
** the window frame has been set to:
**
**   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
*/
static void rankStepFunc(
................................................................................
** Register those built-in window functions that are not also aggregates.
*/
void sqlite3WindowFunctions(void){
  static FuncDef aWindowFuncs[] = {
    WINDOWFUNCX(row_number, 0, 0),
    WINDOWFUNCX(dense_rank, 0, 0),
    WINDOWFUNCX(rank, 0, 0),

    WINDOWFUNCALL(percent_rank, 0, 0),
    WINDOWFUNCALL(cume_dist, 0, 0),
    WINDOWFUNCALL(ntile, 1, 0),


    WINDOWFUNCALL(last_value, 1, 0),
    WINDOWFUNCALL(nth_value, 2, 0),
    WINDOWFUNCNOOP(first_value, 1, 0),
    WINDOWFUNCNOOP(lead, 1, 0),
    WINDOWFUNCNOOP(lead, 2, 0),
    WINDOWFUNCNOOP(lead, 3, 0),
    WINDOWFUNCNOOP(lag, 1, 0),
    WINDOWFUNCNOOP(lag, 2, 0),
    WINDOWFUNCNOOP(lag, 3, 0),
................................................................................
        if( pFunc->zName==aUp[i].zFunc ){
          sqlite3ExprDelete(db, pWin->pStart);
          sqlite3ExprDelete(db, pWin->pEnd);
          pWin->pEnd = pWin->pStart = 0;
          pWin->eType = aUp[i].eType;
          pWin->eStart = aUp[i].eStart;
          pWin->eEnd = aUp[i].eEnd;
          pWin->eExclude = 0;
          if( pWin->eStart==TK_FOLLOWING ){
            pWin->pStart = sqlite3Expr(db, TK_INTEGER, "1");
          }
          break;
        }
      }
    }
................................................................................
  if( pWin==0 ) goto windowAllocErr;
  pWin->eType = eType;
  pWin->eStart = eStart;
  pWin->eEnd = eEnd;
  pWin->bImplicitFrame = bImplicitFrame;
  pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  /* pWin->eExclude = 1; */
  return pWin;

windowAllocErr:
  sqlite3ExprDelete(pParse->db, pEnd);
  sqlite3ExprDelete(pParse->db, pStart);
  return 0;
}
................................................................................
** Return 0 if the two window objects are identical, or non-zero otherwise.
** Identical window objects can be processed in a single scan.
*/
int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2){
  if( p1->eType!=p2->eType ) return 1;
  if( p1->eStart!=p2->eStart ) return 1;
  if( p1->eEnd!=p2->eEnd ) return 1;
  if( p1->eExclude!=p2->eExclude ) return 1;
  if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1;
  if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1;
  if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1;
  if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1;
  return 0;
}

................................................................................
    pMWin->regPart = pParse->nMem+1;
    pParse->nMem += nExpr;
    sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nExpr-1);
  }

  pMWin->regFirst = ++pParse->nMem;
  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);

  if( pMWin->eExclude ){
    pMWin->regStartRowid = ++pParse->nMem;
    pMWin->regEndRowid = ++pParse->nMem;
    pMWin->csrApp = pParse->nTab++;
    sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
    sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->csrApp, pMWin->iEphCsr);
    return;
  }

  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *p = pWin->pFunc;
    if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){
      /* The inline versions of min() and max() require a single ephemeral
      ** table and 3 registers. The registers are used as follows:
      **
................................................................................
** number of rows in the current partition.
*/
static void windowAggStep(
  Parse *pParse, 
  Window *pMWin,                  /* Linked list of window functions */
  int csr,                        /* Read arguments from this cursor */
  int bInverse,                   /* True to invoke xInverse instead of xStep */
  int reg                         /* Array of registers */

){
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    int regArg;
    int nArg = windowArgCount(pWin);


    int i;

    for(i=0; i<nArg; i++){
      sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
    }
    regArg = reg;




    if( pMWin->regStartRowid==0
     && (pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
     && (pWin->eStart!=TK_UNBOUNDED)
    ){
      int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
      VdbeCoverage(v);
      if( bInverse==0 ){
        sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
        sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
        sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
................................................................................
        sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
        VdbeCoverageNeverTaken(v);
        sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
        sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
      }
      sqlite3VdbeJumpHere(v, addrIsNull);
    }else if( pWin->regApp ){
      assert( pFunc->zName==nth_valueName
           || pFunc->zName==first_valueName
      );
      assert( bInverse==0 || bInverse==1 );
      sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);

    }else if( pFunc->zName==leadName || pFunc->zName==lagName ){

      /* no-op */

    }else if( pFunc->xSFunc!=noopStepFunc ){
      int addrIf = 0;
      if( pWin->pFilter ){
        int regTmp;
        assert( nArg==0 || nArg==pWin->pOwner->x.pList->nExpr );
        assert( nArg || pWin->pOwner->x.pList==0 );

        regTmp = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp);



        addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1);
        VdbeCoverage(v);

        sqlite3ReleaseTempReg(pParse, regTmp);
      }

      if( pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
        CollSeq *pColl;
        assert( nArg>0 );
        pColl = sqlite3ExprNNCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr);
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ);
      }
      sqlite3VdbeAddOp3(v, bInverse? OP_AggInverse : OP_AggStep, 
                        bInverse, regArg, pWin->regAccum);
      sqlite3VdbeAppendP4(v, pFunc, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, (u8)nArg);
      if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
    }
  }
}

/*
** Generate VM code to invoke either xValue() (bFin==0) or xFinalize()
** (bFin==1) for each window function in the linked list starting at
** pMWin. Or, for built-in window-functions that do not use the standard
** API, generate the equivalent VM code.
*/
static void windowAggFinal(Parse *pParse, Window *pMWin, int regArg, int bFin){
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;

  if( pMWin->regStartRowid ){
    int addrNext;
    int regRowid = sqlite3GetTempReg(pParse);
    assert( pMWin->csrApp );
    for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
    }
    sqlite3VdbeAddOp3(v, OP_SeekGE, pMWin->csrApp, 0, pMWin->regStartRowid);
    addrNext = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp2(v, OP_Rowid, pMWin->csrApp, regRowid);
    sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, 0, regRowid);
    windowAggStep(pParse, pMWin, pMWin->csrApp, 0, regArg);
    sqlite3VdbeAddOp2(v, OP_Next, pMWin->csrApp, addrNext);
    sqlite3VdbeJumpHere(v, addrNext-1);
    sqlite3VdbeJumpHere(v, addrNext+1);
    sqlite3ReleaseTempReg(pParse, regRowid);
  }

  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    if( pMWin->regStartRowid==0
     && (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
     && (pWin->eStart!=TK_UNBOUNDED)
    ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
      sqlite3VdbeAddOp1(v, OP_Last, pWin->csrApp);
      VdbeCoverage(v);
      sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult);
      sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
      if( bFin ){
        sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
      }
    }else if( pWin->regApp ){
      assert( pMWin->regStartRowid==0 );
    }else{
      int nArg = windowArgCount(pWin);
      if( bFin ){
        sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, nArg);
        sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
        sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
        sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
      }else{
        sqlite3VdbeAddOp3(v, OP_AggValue,pWin->regAccum,nArg,pWin->regResult);

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

/*
................................................................................
  Vdbe *v = sqlite3GetVdbe(pParse);
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    if( pFunc->zName==nth_valueName
     || pFunc->zName==first_valueName
    ){

      int lbl = sqlite3VdbeMakeLabel(pParse);
      int tmpReg = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);

      if( pFunc->zName==nth_valueName ){
        sqlite3VdbeAddOp3(v, OP_Column,pMWin->iEphCsr,pWin->iArgCol+1,tmpReg);
        windowCheckValue(pParse, tmpReg, 2);
      }else{
        sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
      }
      if( pWin->eExclude==0 ){
        int csr = pWin->csrApp;
        sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
        sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
        VdbeCoverageNeverNull(v);
        sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg);
        VdbeCoverageNeverTaken(v);
        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
      }else{
        int regRowid = sqlite3GetTempReg(pParse);
        int csr = pMWin->csrApp;
        int addrNext;
        sqlite3VdbeAddOp3(v, OP_IfPos, tmpReg, sqlite3VdbeCurrentAddr(v)+1, 1);
        sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lbl, pMWin->regStartRowid);
        addrNext = sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid);
        sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lbl, regRowid);
        sqlite3VdbeAddOp3(v, OP_IfPos, tmpReg, sqlite3VdbeCurrentAddr(v)+3, 1);
        sqlite3VdbeAddOp3(v, OP_Column, csr, pMWin->iArgCol, pWin->regResult);
        sqlite3VdbeAddOp2(v, OP_Goto, 0, lbl);
        sqlite3VdbeAddOp2(v, OP_Next, csr, addrNext);
        sqlite3ReleaseTempReg(pParse, regRowid);
      }
      sqlite3VdbeResolveLabel(v, lbl);
      sqlite3ReleaseTempReg(pParse, tmpReg);
    }
    else if( pFunc->zName==leadName || pFunc->zName==lagName ){
      int nArg = pWin->pOwner->x.pList->nExpr;
      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(pParse);
................................................................................
  int regArg;
  int nArg = 0;
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
    nArg = MAX(nArg, windowArgCount(pWin));
    if( pWin->eExclude==0 ){
      if( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ){

        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp);
        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
      }

      if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){
        assert( pWin->eStart!=TK_UNBOUNDED );
        sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
        sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1);
      }
    }
  }
  regArg = pParse->nMem+1;
  pParse->nMem += nArg;
  return regArg;
}

/* 
** Return true if the current frame should be cached in the ephemeral table,
** even if there are no xInverse() calls required.
*/
static int windowCacheFrame(Window *pMWin){
  Window *pWin;
  if( pMWin->regStartRowid ) return 1;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    if( (pFunc->zName==nth_valueName)
     || (pFunc->zName==first_valueName)
     || (pFunc->zName==leadName)
     || (pFunc->zName==lagName)
    ){
................................................................................

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

  if( op==WINDOW_RETURN_ROW ){
    windowAggFinal(pParse, pMWin, p->regArg, 0);
  }
  addrContinue = sqlite3VdbeCurrentAddr(v);
  switch( op ){
    case WINDOW_RETURN_ROW:
      csr = p->current.csr;
      reg = p->current.reg;
      windowReturnOneRow(pParse, pMWin, p->regGosub, p->addrGosub);
      break;

    case WINDOW_AGGINVERSE:
      csr = p->start.csr;
      reg = p->start.reg;
      if( pMWin->regStartRowid ){
        assert( pMWin->regEndRowid );
        sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regStartRowid, 1);
      }else{
        windowAggStep(pParse, pMWin, csr, 1, p->regArg);
      }
      break;

    case WINDOW_AGGSTEP:
      csr = p->end.csr;
      reg = p->end.reg;
      if( pMWin->regStartRowid ){
        assert( pMWin->regEndRowid );
        sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regEndRowid, 1);
      }else{
        windowAggStep(pParse, pMWin, csr, 0, p->regArg);
      }
      break;
  }

  if( op==p->eDelete ){
    sqlite3VdbeAddOp1(v, OP_Delete, csr);
    sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION);
  }
................................................................................
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0));
  }

  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, s.regArg, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
    windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
    sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING && pMWin->eType!=TK_RANGE && regEnd ){
................................................................................
    sqlite3VdbeJumpHere(v, addrBreak);
  }
  sqlite3VdbeJumpHere(v, addrEmpty);

  sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
  if( pMWin->pPartition ){
    if( pMWin->regStartRowid ){
      sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
      sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
    }
    sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
    sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
  }
}

#endif /* SQLITE_OMIT_WINDOWFUNC */

Changes to test/window8.tcl.

    SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
  execsql_test 1.$tn.5 "
    SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
}
























==========

execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a REAL, b INTEGER);
  INSERT INTO t1 VALUES
      (5, 10), (10, 20), (13, 26), (13, 26), 
      (15, 30), (20, 40), (22,80), (30, 90);
}

................................................................................
  7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
  8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
  9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
  10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
  11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
  12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
} {
  execsql_test 2.$tn "
    SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
  "
}


finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


|







 







|

    SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
  execsql_test 1.$tn.5 "
    SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  "
}


foreach {tn ex} {
  1  { EXCLUDE NO OTHERS }
  2  { EXCLUDE CURRENT ROW }
  3  { EXCLUDE GROUP }
  4  { EXCLUDE TIES }
} {
  execsql_test 2.$tn.1 "
    SELECT row_number() OVER win 
    FROM t3
    WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex
    )
  "

  execsql_test 2.$tn.2 "
    SELECT nth_value(c, 14) OVER win 
    FROM t3
    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex)
  "
}

==========

execsql_test 3.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a REAL, b INTEGER);
  INSERT INTO t1 VALUES
      (5, 10), (10, 20), (13, 26), (13, 26), 
      (15, 30), (20, 40), (22,80), (30, 90);
}

................................................................................
  7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
  8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
  9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
  10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
  11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
  12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
} {
  execsql_test 3.$tn "
    SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
  "
}


finish_test

Changes to test/window8.test.

  SELECT a, b, max(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 979   AA aa 979   AA aa 979   AA aa 979   AA bb 979   AA bb 979   AA bb 979   AA bb 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   CC aa 979   CC aa 979   CC aa 979   CC aa 979   CC bb 979   CC bb 979   DD aa 979   DD aa 979   DD aa 979   DD bb 979   DD bb 979   DD bb 979   DD bb 979   EE aa 979   EE aa 979   EE bb 979   EE bb 979   EE bb 979   FF aa 979   FF aa 979   FF aa 979   FF aa 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   GG aa 979   GG aa 979   GG aa 979   GG aa 979   GG bb 979   GG bb 979   GG bb 979   GG bb 979   HH aa 963   HH aa 963   HH aa 963   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   II aa 899   II aa 899   II bb 899   II bb 899   II bb 899   II bb 899   II bb 899   JJ aa 839   JJ aa 839   JJ aa 839   JJ aa 839   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}

do_execsql_test 1.19.5 {
  SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}





























































#==========================================================================

do_execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a REAL, b INTEGER);
  INSERT INTO t1 VALUES
      (5, 10), (10, 20), (13, 26), (13, 26), 
      (15, 30), (20, 40), (22,80), (30, 90);
} {}

do_execsql_test 2.1 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}

do_execsql_test 2.2 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {5 {}   10 10   13 10   13 10   15 30   20 102   22 82   30 120}

do_execsql_test 2.3 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {5 {}   10 52   13 30   13 30   15 {}   20 80   22 {}   30 {}}

do_execsql_test 2.4 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}

do_execsql_test 2.5 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {30 {}   22 90   20 90   15 120   13 120   13 120   10 70   5 102}

do_execsql_test 2.6 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {30 {}   22 40   20 {}   15 52   13 20   13 20   10 {}   5 {}}

do_execsql_test 2.7 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING )
} {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}

do_execsql_test 2.8 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING )
} {5 {}   10 {}   13 10   13 10   15 10   20 72   22 82   30 120}

do_execsql_test 2.9 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING )
} {5 {}   10 52   13 {}   13 {}   15 {}   20 {}   22 {}   30 {}}

do_execsql_test 2.10 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING )
} {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}

do_execsql_test 2.11 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
} {30 {}   22 90   20 90   15 170   13 210   13 210   10 210   5 292}

do_execsql_test 2.12 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
} {30 232   22 112   20 112   15 30   13 30   13 30   10 10   5 {}}

finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


|







|



|



|



|



|



|



|



|



|



|



|



|

  SELECT a, b, max(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 979   AA aa 979   AA aa 979   AA aa 979   AA bb 979   AA bb 979   AA bb 979   AA bb 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB aa 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   BB bb 979   CC aa 979   CC aa 979   CC aa 979   CC aa 979   CC bb 979   CC bb 979   DD aa 979   DD aa 979   DD aa 979   DD bb 979   DD bb 979   DD bb 979   DD bb 979   EE aa 979   EE aa 979   EE bb 979   EE bb 979   EE bb 979   FF aa 979   FF aa 979   FF aa 979   FF aa 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   FF bb 979   GG aa 979   GG aa 979   GG aa 979   GG aa 979   GG bb 979   GG bb 979   GG bb 979   GG bb 979   HH aa 963   HH aa 963   HH aa 963   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   HH bb 899   II aa 899   II aa 899   II bb 899   II bb 899   II bb 899   II bb 899   II bb 899   JJ aa 839   JJ aa 839   JJ aa 839   JJ aa 839   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}

do_execsql_test 1.19.5 {
  SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}

do_execsql_test 2.1.1 {
  SELECT row_number() OVER win 
    FROM t3
    WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS 
    )
} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}

do_execsql_test 2.1.2 {
  SELECT nth_value(c, 14) OVER win 
    FROM t3
    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS )
} {938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938}

do_execsql_test 2.2.1 {
  SELECT row_number() OVER win 
    FROM t3
    WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW 
    )
} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}

do_execsql_test 2.2.2 {
  SELECT nth_value(c, 14) OVER win 
    FROM t3
    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW )
} {660   660   660   660   660   660   660   660   660   660   660   660   660   660   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938   938}

do_execsql_test 2.3.1 {
  SELECT row_number() OVER win 
    FROM t3
    WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP 
    )
} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}

do_execsql_test 2.3.2 {
  SELECT nth_value(c, 14) OVER win 
    FROM t3
    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP )
} {{}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}}

do_execsql_test 2.4.1 {
  SELECT row_number() OVER win 
    FROM t3
    WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES 
    )
} {1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80}

do_execsql_test 2.4.2 {
  SELECT nth_value(c, 14) OVER win 
    FROM t3
    WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES )
} {{}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}}

#==========================================================================

do_execsql_test 3.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a REAL, b INTEGER);
  INSERT INTO t1 VALUES
      (5, 10), (10, 20), (13, 26), (13, 26), 
      (15, 30), (20, 40), (22,80), (30, 90);
} {}

do_execsql_test 3.1 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}

do_execsql_test 3.2 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {5 {}   10 10   13 10   13 10   15 30   20 102   22 82   30 120}

do_execsql_test 3.3 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {5 {}   10 52   13 30   13 30   15 {}   20 80   22 {}   30 {}}

do_execsql_test 3.4 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}

do_execsql_test 3.5 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {30 {}   22 90   20 90   15 120   13 120   13 120   10 70   5 102}

do_execsql_test 3.6 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {30 {}   22 40   20 {}   15 52   13 20   13 20   10 {}   5 {}}

do_execsql_test 3.7 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING )
} {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}

do_execsql_test 3.8 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING )
} {5 {}   10 {}   13 10   13 10   15 10   20 72   22 82   30 120}

do_execsql_test 3.9 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING )
} {5 {}   10 52   13 {}   13 {}   15 {}   20 {}   22 {}   30 {}}

do_execsql_test 3.10 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING )
} {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}

do_execsql_test 3.11 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
} {30 {}   22 90   20 90   15 170   13 210   13 210   10 210   5 292}

do_execsql_test 3.12 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
} {30 232   22 112   20 112   15 30   13 30   13 30   10 10   5 {}}

finish_test