SQLite

static Expr *removeUnindexableInClauseTerms(
  Parse *pParse,        /* The parsing context */
  int iEq,              /* Look at loop terms starting here */
  WhereLoop *pLoop,     /* The current loop */
  Expr *pX              /* The IN expression to be reduced */
){
  sqlite3 *db = pParse->db;
  Expr *pNew;
#ifndef SQLITE_OMIT_WINDOWFUNC
  /* The SELECT statement at pX->x.pSelect has already been resolved. This
  ** means that its window functions have already been identified and 
  ** linked into the Select.pWin list. However, if there are multiple 
  ** window functions and they do not all use the same window frame, only
  ** those that use the same window frame as the first resolved are listed
  ** in Select.pWin. Any others are delegated to sub-selects created by the 
  ** call to sqlite3WindowRewrite() made when coding the SELECT statement.
  ** But - sqlite3WindowRewrite() is a no-op if Select.pWin==0. And if 
  ** removing the unindexable terms happens to remove all window functions
  ** in the Select.pWin list, then Select.pWin ends up set to NULL - meaning
  ** that the other window functions are never processed. To work around
  ** this, ensure that sqlite3WindowRewrite() has been called to create the
  ** required sub-selects before the unindexable terms are removed. See
  ** ticket #f00d096ca.  */
  if( sqlite3WindowRewrite(pParse, pX->x.pSelect) ) return 0;
#endif
  pNew = sqlite3ExprDup(db, pX, 0);
  if( db->mallocFailed==0 ){
    ExprList *pOrigRhs = pNew->x.pSelect->pEList;  /* Original unmodified RHS */
    ExprList *pOrigLhs = pNew->pLeft->x.pList;     /* Original unmodified LHS */
    ExprList *pRhs = 0;         /* New RHS after modifications */
    ExprList *pLhs = 0;         /* New LHS after mods */
    int i;                      /* Loop counter */
    Select *pSelect;            /* Pointer to the SELECT on the RHS */

  INSERT INTO t1 VALUES(2, 4);
  INSERT INTO t1 VALUES(1, 5);
}

execsql_float_test 5.1 {
  SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z;
}

==========

execsql_test 6.0 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0 INTEGER UNIQUE);
  INSERT INTO t0 VALUES(0);
}
execsql_test 6.1 {
  SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0;
}
execsql_test 6.2 {
  SELECT * FROM t0 WHERE 
      (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
} 

finish_test

    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]"
    }
    incr i
  }
  set {} {}
} {}

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

do_execsql_test 6.0 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0 INTEGER UNIQUE);
  INSERT INTO t0 VALUES(0);
} {}

do_execsql_test 6.1 {
  SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0;
} {1 {}}

do_execsql_test 6.2 {
  SELECT * FROM t0 WHERE 
      (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
} {}

finish_test

reset_db
do_execsql_test 10.0 {
  CREATE TABLE t1(a, b, c, d);
  CREATE TABLE t2(a, b, c, d);
}

do_faultsim_test 10 -faults oom* -prep {
} -body {
  execsql {
    SELECT row_number() OVER win
    FROM t1
    WINDOW win AS (
      ORDER BY (
        SELECT percent_rank() OVER win2 FROM t2
        WINDOW win2 AS (ORDER BY a)
      )
    )
  }
} -test {
  faultsim_test_result {0 {}}
}

reset_db
do_execsql_test 11.0 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0 INTEGER UNIQUE);
  INSERT INTO t0 VALUES(0);
} {}

do_faultsim_test 11 -faults oom* -prep {
} -body {
  execsql {
    SELECT * FROM t0 WHERE 
      (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
  }
} -test {
  faultsim_test_result {0 {}}
}

finish_test