SQLite

      pWin->csrApp = pParse->nTab++;
      sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr);
    }
  }
}

/*
** A "PRECEDING <expr>" (bEnd==0) or "FOLLOWING <expr>" (bEnd==1) has just 
** been evaluated and the result left in register reg. This function generates
** VM code to check that the value is a non-negative integer and throws
** an exception if it is not.
** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the
** value of the second argument to nth_value() (eCond==2) has just been
** evaluated and the result left in register reg. This function generates VM
** code to check that the value is a non-negative integer and throws an
** exception if it is not.
*/
static void windowCheckFrameOffset(Parse *pParse, int reg, int bEnd){
static void windowCheckIntValue(Parse *pParse, int reg, int eCond){
  static const char *azErr[] = {
    "frame starting offset must be a non-negative integer",
    "frame ending offset must be a non-negative integer"
    "frame ending offset must be a non-negative integer",
    "second argument to nth_value must be a positive integer"
  };
  static int aOp[] = { OP_Ge, OP_Ge, OP_Gt };
  Vdbe *v = sqlite3GetVdbe(pParse);
  int regZero = sqlite3GetTempReg(pParse);
  assert( eCond==0 || eCond==1 || eCond==2 );
  sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
  sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
  VdbeCoverage(v);
  sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
  sqlite3VdbeAddOp3(v, aOp[eCond], regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
  VdbeCoverage(v);
  sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort);
  sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC);
  sqlite3VdbeAppendP4(v, (void*)azErr[eCond], P4_STATIC);
  sqlite3ReleaseTempReg(pParse, regZero);
}

/*
** Return the number of arguments passed to the window-function associated
** with the object passed as the only argument to this function.
*/

      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(v);
      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);
        windowCheckIntValue(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);
      VdbeCoverage(v);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);

  sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr);
  sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr);

  /* If either regStart or regEnd are not non-negative integers, throw 
  ** an exception.  */
  if( pMWin->pStart ){
    sqlite3ExprCode(pParse, pMWin->pStart, regStart);
    windowCheckFrameOffset(pParse, regStart, 0);
    windowCheckIntValue(pParse, regStart, 0);
  }
  if( pMWin->pEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckFrameOffset(pParse, regEnd, 1);
    windowCheckIntValue(pParse, regEnd, 1);
  }

  /* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> FOLLOWING", do:
  **
  **   if( regEnd<regStart ){
  **     // The frame always consists of 0 rows
  **     regStart = regSize;

  INSERT INTO t7(id, a, b) VALUES
    (1, 1, 2), (2, 1, NULL), (3, 1, 4),
    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
}
execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
}

execsql_test 10.2 {
  SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}
execsql_test 10.3 {
  SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}



finish_test

    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
} {}

do_execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 2   2 2   3 2   4 {}   5 8   6 1}

do_execsql_test 10.2 {
  SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {}   2 2   3 {}   4 {}   5 {}   6 8}

do_execsql_test 10.3 {
  SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {}   2 4   3 {}   4 8   5 1   6 {}}

finish_test

do_execsql_test 10.0 {
  WITH t1(a,b) AS (VALUES(1,2))
  SELECT count() FILTER (where b<>5) OVER w1
    FROM t1
    WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
} {1}

foreach {tn stmt} {
  1 "SELECT nth_value(b, 0) OVER (ORDER BY a) FROM t1"
  2 "SELECT nth_value(b, -1) OVER (ORDER BY a) FROM t1"
  3 "SELECT nth_value(b, '4ab') OVER (ORDER BY a) FROM t1"
  4 "SELECT nth_value(b, NULL) OVER (ORDER BY a) FROM t1"
  5 "SELECT nth_value(b, 8.5) OVER (ORDER BY a) FROM t1"
} {
  do_catchsql_test 10.1.$tn "
    WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
    $stmt
  " {1 {second argument to nth_value must be a positive integer}}
}

foreach {tn stmt res} {
  1 "SELECT nth_value(b, 1) OVER (ORDER BY a) FROM t1"         {2 2 2}
  2 "SELECT nth_value(b, 2) OVER (ORDER BY a) FROM t1"         {{} 3 3}
  3 "SELECT nth_value(b, '2') OVER (ORDER BY a) FROM t1"       {{} 3 3}
  4 "SELECT nth_value(b, 2.0) OVER (ORDER BY a) FROM t1"       {{} 3 3}
  5 "SELECT nth_value(b, '2.0') OVER (ORDER BY a) FROM t1"     {{} 3 3}
  6 "SELECT nth_value(b, 10000000) OVER (ORDER BY a) FROM t1"  {{} {} {}}
} {
  do_execsql_test 10.2.$tn "
    WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
    $stmt
  " $res
}

finish_test