SQLite

**              (17d2) DISTINCT
**        (17e) the subquery may not contain window functions, and
**        (17f) the subquery must not be the RHS of a LEFT JOIN.
**        (17g) either the subquery is the first element of the outer
**              query or there are no RIGHT or FULL JOINs in any arm
**              of the subquery.  (This is a duplicate of condition (27b).)
**        (17h) The corresponding result set expressions in all arms of the
**              compound must have the same affinity.

**
**        The parent and sub-query may contain WHERE clauses. Subject to
**        rules (11), (13) and (14), they may also contain ORDER BY,
**        LIMIT and OFFSET clauses.  The subquery cannot use any compound
**        operator other than UNION ALL because all the other compound
**        operators have an implied DISTINCT which is disallowed by
**        restriction (4).

**       be materialized.  (This restriction is implemented in the calling
**       routine.)
**
**   (8) If the subquery is a compound that uses UNION, INTERSECT,
**       or EXCEPT, then all of the result set columns for all arms of
**       the compound must use the BINARY collating sequence.
**




**
** 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 */

      if( op!=TK_ALL && op!=TK_SELECT ){
        notUnionAll = 1;
      }
#ifndef SQLITE_OMIT_WINDOWFUNC
      if( pSel->pWin ) return 0;    /* restriction (6b) */
#endif
    }



    if( notUnionAll ){
      /* If any of the compound arms are connected using UNION, INTERSECT,
      ** or EXCEPT, then we must ensure that none of the columns use a
      ** non-BINARY collating sequence. */
      for(pSel=pSubq; pSel; pSel=pSel->pPrior){
        int ii;
        const ExprList *pList = pSel->pEList;

do_execsql_test 3.4 {
  SELECT a, quote(b), cd FROM (
    SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
  ) WHERE a=2 AND b=0 AND cd=0;
} {
  2 0     0
}

# 2023-02-22 https://sqlite.org/forum/forumpost/bcc4375032
# Performance regression caused by check-in [1ad41840c5e0fa70] from 2022-11-25.
# That check-in added a new restriction on push-down.  The new restriction is
# no longer necessary after check-in [27655c9353620aa5] from 2022-12-14.
#
do_execsql_test 3.5 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES
    (1,100,'abc'),
    (2,200,'def'),
    (3,300,'abc');
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
  INSERT INTO t2(a,b,c) VALUES
    (1,110,'efg'),
    (2,200,'hij'),
    (3,330,'klm');
  CREATE VIEW v3 AS
    SELECT a, b, c FROM t1
    UNION ALL
    SELECT a, b, 'xyz' FROM t2;
  SELECT * FROM v3 WHERE a=2 AND b=200;
} {2 200 def 2 200 xyz}
do_eqp_test 3.6 {
  SELECT * FROM v3 WHERE a=2 AND b=200;
} {
  QUERY PLAN
  |--CO-ROUTINE v3
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  `--SEARCH t1 USING PRIMARY KEY (a=? AND b=?)
  |     `--UNION ALL
  |        `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?)
  `--SCAN v3
}
#                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# We want both arms of the compound subquery to use the
# primary key.

finish_test