SQLite

  return;

 error_out:
  sqlite3_result_error(
      pCtx, "second argument to nth_value must be a positive integer", -1
  );
}
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));
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, 0);
  if( p && p->pValue ){
    sqlite3_result_value(pCtx, p->pValue);
    sqlite3_value_free(p->pValue);
    p->pValue = 0;
  }
}
#define nth_valueInvFunc noopStepFunc
#define nth_valueValueFunc noopValueFunc

static void first_valueStepFunc(
  sqlite3_context *pCtx, 
  int nArg,
  sqlite3_value **apArg
){
  struct NthValueCtx *p;
  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->pValue==0 ){
    p->pValue = sqlite3_value_dup(apArg[0]);
    if( !p->pValue ){
      sqlite3_result_error_nomem(pCtx);
    }
  }
  UNUSED_PARAMETER(nArg);
  UNUSED_PARAMETER(apArg);
}
static void first_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 first_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 first_valueInvFunc noopStepFunc
#define first_valueValueFunc noopValueFunc

/*
** Implementation of built-in window function rank(). Assumes that
** the window frame has been set to:
**
**   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
*/

  struct CallCount *p;
  UNUSED_PARAMETER(nArg); assert( nArg==0 );
  p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  p->nStep++;
}
static void cume_distValueFunc(sqlite3_context *pCtx){
  struct CallCount *p;
  p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p && p->nTotal ){
  p = (struct CallCount*)sqlite3_aggregate_context(pCtx, 0);
  if( p ){
    double r = (double)(p->nStep) / (double)(p->nTotal);
    sqlite3_result_double(pCtx, r);
  }
}
#define cume_distFinalizeFunc cume_distValueFunc

/*

      sqlite3_value_free(p->pVal);
      p->pVal = 0;
    }
  }
}
static void last_valueValueFunc(sqlite3_context *pCtx){
  struct LastValueCtx *p;
  p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, 0);
  if( p && p->pVal ){
    sqlite3_result_value(pCtx, p->pVal);
  }
}
static void last_valueFinalizeFunc(sqlite3_context *pCtx){
  struct LastValueCtx *p;
  p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));

      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);

     && (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);
      }
    }
  }
}

/*
** Generate code to calculate the current values of all window functions in the
** p->pMWin list by doing a full scan of the current window frame. Store the
** results in the Window.regResult registers, ready to return the upper
** layer.
*/
static void windowFullScan(WindowCodeArg *p){
  Window *pWin;
  Parse *pParse = p->pParse;
  Window *pMWin = p->pMWin;
  Vdbe *v = p->pVdbe;

  int regCRowid = 0;              /* Current rowid value */

  sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
  sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);

  sqlite3ReleaseTempReg(pParse, reg1);
  sqlite3ReleaseTempReg(pParse, reg2);
}

/*
** Helper function for sqlite3WindowCodeStep(). Each call to this function
** generates VM code for a single RETURN_ROW, AGGSTEP or AGGINVERSE 
** operation. Refer to the header comment for sqlite3WindowCodeStep() for
** details.
*/
static int windowCodeOp(
 WindowCodeArg *p,
 int op,
 int regCountdown,
 int jumpOnEof
 WindowCodeArg *p,                /* Context object */
 int op,                          /* WINDOW_RETURN_ROW, AGGSTEP or AGGINVERSE */
 int regCountdown,                /* Register for OP_IfPos countdown */
 int jumpOnEof                    /* Jump here if stepped cursor reaches EOF */
){
  int csr, reg;
  Parse *pParse = p->pParse;
  Window *pMWin = p->pMWin;
  int ret = 0;
  Vdbe *v = p->pVdbe;
  int addrIf = 0; 

    assert( regCountdown==0 && jumpOnEof==0 );
    return 0;
  }

  if( regCountdown>0 ){
    if( pMWin->eType==TK_RANGE ){
      addrNextRange = sqlite3VdbeCurrentAddr(v);

      switch( op ){
        case WINDOW_RETURN_ROW: {
          assert( 0 );
      assert( op==WINDOW_AGGINVERSE || op==WINDOW_AGGSTEP );
          break;
        }

        case WINDOW_AGGINVERSE: {
          if( pMWin->eStart==TK_FOLLOWING ){
            windowCodeRangeTest(
                p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone
            );
          }else{
            windowCodeRangeTest(
                p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone
            );
          }
      if( op==WINDOW_AGGINVERSE ){
        if( pMWin->eStart==TK_FOLLOWING ){
          windowCodeRangeTest(
              p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone
          );
        }else{
          windowCodeRangeTest(
              p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone
          );
        }
          break;
        }
      }else{

        case WINDOW_AGGSTEP: {
          windowCodeRangeTest(
        windowCodeRangeTest(
            p, OP_Gt, p->end.csr, regCountdown, p->current.csr, lblDone
          );
        );
          break;
        }
      }
      }

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

  if( op==WINDOW_RETURN_ROW ){
  if( op==WINDOW_RETURN_ROW && pMWin->regStartRowid==0 ){
    windowAggFinal(p, 0);
  }
  addrContinue = sqlite3VdbeCurrentAddr(v);
  switch( op ){
    case WINDOW_RETURN_ROW:
      csr = p->current.csr;
      reg = p->current.reg;

        assert( pMWin->regEndRowid );
        sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regStartRowid, 1);
      }else{
        windowAggStep(pParse, pMWin, csr, 1, p->regArg);
      }
      break;

    default:
    case WINDOW_AGGSTEP:
      assert( op==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;
    default:
      s.eDelete = WINDOW_AGGINVERSE;
      break;
  }

  s.eDelete = 0;

  /* Allocate registers for the array of values from the sub-query, the
  ** samve values in record form, and the rowid used to insert said record
  ** into the ephemeral table.  */
  regNew = pParse->nMem+1;
  pParse->nMem += nInput;
  regRecord = ++pParse->nMem;
  regRowid = ++pParse->nMem;

    windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0));
  }
  if( regEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0));
  }

  if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
  if( pMWin->eStart==pMWin->eEnd && regStart ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
    windowAggFinal(&s, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
    windowReturnOneRow(&s);
    sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);

  3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  4 "ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING"

  5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
  6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"

  7 "ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"

  8 "PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
} {
  errorsql_test 1.$tn "
  SELECT a, sum(b) OVER (
    $frame
  ) FROM t1 ORDER BY 1
  "
}

# PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
do_test 1.7 { catch { execsql {
  SELECT a, sum(b) OVER (
    ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

# PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
do_test 1.8 { catch { execsql {
  SELECT a, sum(b) OVER (
    PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1
} } } 1

finish_test