SQLite

**          all window-functions used by the sub-query. It is safe to
**          filter out entire partitions, as this does not change the 
**          window over which any window-function is calculated.
**
**   (7) The inner query is a Common Table Expression (CTE) that should
**       be materialized.  (This restriction is implemented in the calling
**       routine.)
**
**   (8) The subquery may not be a compound that uses UNION, INTERSECT,
**       or EXCEPT.  (We could, perhaps, relax this restriction to allow
**       this case if none of the comparisons operators between left and
**       right arms of the compound use a collation other than BINARY.
**       But it is a lot of work to check that case for an obscure and
**       minor optimization, so we omit it for now.)
**
** Return 0 if no changes are made and non-zero if one or more WHERE clause
** terms are duplicated into the subquery.
*/
static int pushDownWhereTerms(
  Parse *pParse,        /* Parse context (for malloc() and error reporting) */
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  Expr *pWhere,         /* The WHERE clause of the outer query */
  SrcItem *pSrc         /* The subquery term of the outer FROM clause */
){
  Expr *pNew;
  int nChng = 0;
  if( pWhere==0 ) return 0;
  if( pSubq->selFlags & (SF_Recursive|SF_MultiPart) ) return 0;
  if( pSrc->fg.jointype & (JT_LTORJ|JT_RIGHT) ) return 0;

#ifndef SQLITE_OMIT_WINDOWFUNC
  if( pSubq->pPrior ){
    Select *pSel;
    for(pSel=pSubq; pSel; pSel=pSel->pPrior){
      u8 op = pSel->op;
      assert( op==TK_ALL || op==TK_SELECT 
           || op==TK_UNION || op==TK_INTERSECT || op==TK_EXCEPT );
      if( op!=TK_ALL && op!=TK_SELECT ) return 0;  /* restriction (8) */
      if( pSel->pWin ) return 0;    /* restriction (6b) */
    }
  }else{
    if( pSubq->pWin && pSubq->pWin->pPartition==0 ) return 0;
  }
#endif

}

do_execsql_test 8.1 {
      SELECT 'ABCD' FROM t1 
      WHERE (a=? OR b=?) 
      AND (0 OR (SELECT 'xyz' INTERSECT SELECT a ORDER BY 1))
} {}

#-------------------------------------------------------------------------
# dbsqlfuzz a34f455c91ad75a0cf8cd9476841903f42930a7a
#
reset_db
do_execsql_test 9.0 {
  CREATE TABLE t1(a COLLATE nocase);
  CREATE TABLE t2(b COLLATE nocase);

  INSERT INTO t1 VALUES('ABC');
  INSERT INTO t2 VALUES('abc');
}

do_execsql_test 9.1 {
  SELECT a FROM t1 INTERSECT SELECT b FROM t2;
} {ABC}

do_execsql_test 9.2 {
  SELECT * FROM (
      SELECT a FROM t1 INTERSECT SELECT b FROM t2
  ) WHERE a||'' = 'ABC';
} {ABC}



finish_test