SQLite

      if( pX->iParent>=0 && (&pWC->a[pX->iParent])==pTerm ) break;
    }
    if( j<0 ){
      if( pLoop->maskSelf==pTerm->prereqAll ){
        /* If there are extra terms in the WHERE clause not used by an index
        ** that depend only on the table being scanned, and that will tend to
        ** cause many rows to be omitted, then mark that table as
        ** "self-culling".
        **
        ** 2022-03-24:  Self-culling only applies if either the extra terms
        ** are straight comparison operators that are non-true with NULL
        ** operand, or if the loop is not a LEFT JOIN.
        */
        if( (pTerm->eOperator & 0x3f)!=0
         || (pWC->pWInfo->pTabList->a[pLoop->iTab].fg.jointype & JT_LEFT)==0
        ){
          pLoop->wsFlags |= WHERE_SELFCULL;
        }
      }
      if( pTerm->truthProb<=0 ){
        /* If a truth probability is specified using the likelihood() hints,
        ** then use the probability provided by the application. */
        pLoop->nOut += pTerm->truthProb;
      }else{
        /* In the absence of explicit truth probabilities, use heuristics to

  ANALYZE;
}

do_eqp_test 7.4 {
  SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?);
} {
  QUERY PLAN
  |--SCAN t3
  `--SEARCH t4 USING INDEX t4xz (x=?)
} 
do_eqp_test 7.4b {
  SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?);
} {
  QUERY PLAN
  |--SCAN t3
  |--BLOOM FILTER ON t4 (x=?)
  `--SEARCH t4 USING INDEX t4xz (x=?)
} 

reset_db

  DELETE FROM t1;
  INSERT INTO t1 VALUES(0),(0);
  CREATE VIEW v1 AS SELECT * FROM t1 NATURAL JOIN t1 WHERE (x BETWEEN 0 AND 10) OR true;
  CREATE VIEW v2 AS SELECT * FROM v1 NATURAL JOIN v1;
  CREATE VIEW v3 AS SELECT * FROM v2, v1 USING (x) GROUP BY x;
  SELECT x FROM v3; 
} {0}

# 2022-03-24 https://sqlite.org/forum/forumpost/031e262a89b6a9d2
# Bloom-filter on a LEFT JOIN with NULL-based WHERE constraints.
#
reset_db
do_execsql_test 11.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
  CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8)
  INSERT INTO t1(a,b) SELECT x, 10*x FROM c;
  INSERT INTO t2(c,d) SELECT b*2, 100*a FROM t1;
  ANALYZE;
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES
    ('t1',NULL,150105),('t2',NULL,98747);
  ANALYZE sqlite_schema;
} {}
do_execsql_test 11.2 {
  SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d IS NULL;
} {4}
do_execsql_test 11.3 {
  SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d=100;
} {1}
do_execsql_test 11.4 {
  SELECT count(*) FROM t1 LEFT JOIN t2 ON c=b WHERE d>=300;
} {2}



finish_test