SQLite

** table other than iCur.
*/
int sqlite3ExprIsTableConstant(Expr *p, int iCur){
  return exprIsConst(p, 3, iCur);
}

/*
** Check pExpr to see if it is an constraint on the single data source
** pSrc = &pSrcList->a[iSrc].  In other words, check to see if pExpr
** constrains pSrc but does not depend on any other tables or data
** sources anywhere else in the query.  Return true (non-zero) if pExpr
** is a constraint on pSrc only.
**
** This is an optimization.  False negatives will perhaps cause slower
** queries, but false positives will yield incorrect answers.  So when in
** doubt, return 0.
**
** To be an single-source constraint, the following must be true:
**
**   (1)  pExpr cannot refer to any table other than pSrc->iCursor.
**
**   (2)  pExpr cannot use subqueries or non-deterministic functions.
**
**   (3)  pSrc cannot be part of the left operand for a RIGHT JOIN.
**        (Is there some way to relax this constraint?)
**
**   (4)  If pSrc is the right operand of a LEFT JOIN, then...
**         (4a)  pExpr must come from an ON clause..
**         (4b)  and specifically the ON clause associated with the LEFT JOIN.
**
**   (5)  If pSrc is not the right operand of a LEFT JOIN or the left
**        operand of a RIGHT JOIN, then pExpr must be from the WHERE
**        clause, not an ON clause.
**
**   (6) Either:
**
**       (6a) pExpr does not originate in an ON or USING clause, or
**
**       (6b) The ON or USING clause from which pExpr is derived is
**            not to the left of a RIGHT JOIN (or FULL JOIN).
**
**       Without this restriction, accepting pExpr as a single-table
**       constraint might move the the ON/USING filter expression
**       from the left side of a RIGHT JOIN over to the right side,
**       which leads to incorrect answers.  See also restriction (9)
**       on push-down.
*/
int sqlite3ExprIsSingleTableConstraint(
  Expr *pExpr,                 /* The constraint */
  const SrcList *pSrcList,     /* Complete FROM clause */
  int iSrc                     /* Which element of pSrcList to use */
){
  const SrcItem *pSrc = &pSrcList->a[iSrc];
  if( pSrc->fg.jointype & JT_LTORJ ){
    return 0;  /* rule (3) */
  }
  if( pSrc->fg.jointype & JT_LEFT ){
    if( !ExprHasProperty(pExpr, EP_OuterON) ) return 0;   /* rule (4a) */
    if( pExpr->w.iJoin!=pSrc->iCursor ) return 0;         /* rule (4b) */
  }else{
    if( ExprHasProperty(pExpr, EP_OuterON) ) return 0;    /* rule (5) */
  }
  if( ExprHasProperty(pExpr, EP_OuterON|EP_InnerON)  /* (6a) */
   && (pSrcList->a[0].fg.jointype & JT_LTORJ)!=0     /* Fast pre-test of (6b) */
  ){
    int jj;
    for(jj=0; jj<iSrc; jj++){
      if( pExpr->w.iJoin==pSrcList->a[jj].iCursor ){
        if( (pSrcList->a[jj].fg.jointype & JT_LTORJ)!=0 ){
          return 0;  /* restriction (6) */
        }
        break;
      }
    }
  }
  return sqlite3ExprIsTableConstant(pExpr, pSrc->iCursor); /* rules (1), (2) */
}


/*
** sqlite3WalkExpr() callback used by sqlite3ExprIsConstantOrGroupBy().

**       (9b) The subquery is to the right of the ON/USING clause
**
**       (9c) There is a RIGHT JOIN (or FULL JOIN) in between the ON/USING
**            clause and the subquery.
**
**       Without this restriction, the push-down optimization might move
**       the ON/USING filter expression from the left side of a RIGHT JOIN
**       over to the right side, which leads to incorrect answers.  See
**       also restriction (6) in sqlite3ExprIsSingleTableConstraint().
**
**  (10) The inner query is not the right-hand table of a RIGHT JOIN.
**
**  (11) The subquery is not a VALUES clause
**
** Return 0 if no changes are made and non-zero if one or more WHERE clause
** terms are duplicated into the subquery.

    return 0; /* restriction (3) */
  }
  while( pWhere->op==TK_AND ){
    nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, pSrcList, iSrc);
    pWhere = pWhere->pLeft;
  }

#if 0 /* These checks now done by sqlite3ExprIsSingleTableConstraint() */
  if( ExprHasProperty(pWhere, EP_OuterON|EP_InnerON) /* (9a) */
   && (pSrcList->a[0].fg.jointype & JT_LTORJ)!=0     /* Fast pre-test of (9c) */
  ){
    int jj;
    for(jj=0; jj<iSrc; jj++){
      if( pWhere->w.iJoin==pSrcList->a[jj].iCursor ){
        /* If we reach this point, both (9a) and (9b) are satisfied.
        ** The following loop checks (9c):
        */
        for(jj++; jj<iSrc; jj++){
          if( (pSrcList->a[jj].fg.jointype & JT_RIGHT)!=0 ){
            return 0;  /* restriction (9) */
          }
        }
      }
    }
  }


  if( isLeftJoin
   && (ExprHasProperty(pWhere,EP_OuterON)==0
         || pWhere->w.iJoin!=iCursor)
  ){
    return 0; /* restriction (4) */
  }
  if( ExprHasProperty(pWhere,EP_OuterON)
   && pWhere->w.iJoin!=iCursor 
  ){
    return 0; /* restriction (5) */
  }
#endif

  if( sqlite3ExprIsSingleTableConstraint(pWhere, pSrcList, iSrc) ){
    nChng++;
    pSubq->selFlags |= SF_PushDown;
    while( pSubq ){
      SubstContext x;
      pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
      unsetJoinExpr(pNew, -1, 1);
      x.pParse = pParse;

int sqlite3ExprIdToTrueFalse(Expr*);
int sqlite3ExprTruthValue(const Expr*);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsConstantNotJoin(Expr*);
int sqlite3ExprIsConstantOrFunction(Expr*, u8);
int sqlite3ExprIsConstantOrGroupBy(Parse*, Expr*, ExprList*);
int sqlite3ExprIsTableConstant(Expr*,int);
int sqlite3ExprIsSingleTableConstraint(Expr*,const SrcList*,int);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
int sqlite3ExprContainsSubquery(Expr*);
#endif
int sqlite3ExprIsInteger(const Expr*, int*);
int sqlite3ExprCanBeNull(const Expr*);
int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
int sqlite3IsRowid(const char*);

  char *zNotUsed;             /* Extra space on the end of pIdx */
  Bitmask idxCols;            /* Bitmap of columns used for indexing */
  Bitmask extraCols;          /* Bitmap of additional columns */
  u8 sentWarning = 0;         /* True if a warning has been issued */
  u8 useBloomFilter = 0;      /* True to also add a Bloom filter */
  Expr *pPartial = 0;         /* Partial Index Expression */
  int iContinue = 0;          /* Jump here to skip excluded rows */
  SrcList *pTabList;          /* The complete FROM clause */
  SrcItem *pSrc;              /* The FROM clause term to get the next index */
  int addrCounter = 0;        /* Address where integer counter is initialized */
  int regBase;                /* Array of registers where record is assembled */
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  int addrExp = 0;            /* Address of OP_Explain */
#endif

  /* Generate code to skip over the creation and initialization of the
  ** transient index on 2nd and subsequent iterations of the loop. */
  v = pParse->pVdbe;
  assert( v!=0 );
  addrInit = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);

  /* Count the number of columns that will be added to the index
  ** and used to match WHERE clause constraints */
  nKeyCol = 0;
  pTabList = pWC->pWInfo->pTabList;
  pSrc = &pTabList->a[pLevel->iFrom];
  pTable = pSrc->pTab;
  pWCEnd = &pWC->a[pWC->nTerm];
  pLoop = pLevel->pWLoop;
  idxCols = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    Expr *pExpr = pTerm->pExpr;
    /* Make the automatic index a partial index if there are terms in the
    ** WHERE clause (or the ON clause of a LEFT join) that constrain which
    ** rows of the target table (pSrc) that can be used. */
    if( (pTerm->wtFlags & TERM_VIRTUAL)==0
     && sqlite3ExprIsSingleTableConstraint(pExpr, pTabList, pLevel->iFrom)
    ){
      pPartial = sqlite3ExprAnd(pParse, pPartial,
                                sqlite3ExprDup(pParse->db, pExpr, 0));
    }
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol;
      Bitmask cMask;

  assert( pLoop!=0 );
  assert( v!=0 );
  assert( pLoop->wsFlags & WHERE_BLOOMFILTER );

  addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
  do{
    const SrcList *pTabList;
    const SrcItem *pItem;
    const Table *pTab;
    u64 sz;
    int iSrc;
    sqlite3WhereExplainBloomFilter(pParse, pWInfo, pLevel);
    addrCont = sqlite3VdbeMakeLabel(pParse);
    iCur = pLevel->iTabCur;
    pLevel->regFilter = ++pParse->nMem;

    /* The Bloom filter is a Blob held in a register.  Initialize it
    ** to zero-filled blob of at least 80K bits, but maybe more if the
    ** estimated size of the table is larger.  We could actually
    ** measure the size of the table at run-time using OP_Count with
    ** P3==1 and use that value to initialize the blob.  But that makes
    ** testing complicated.  By basing the blob size on the value in the
    ** sqlite_stat1 table, testing is much easier.
    */
    pTabList = pWInfo->pTabList;
    iSrc = pLevel->iFrom;
    pItem = &pTabList->a[iSrc];
    assert( pItem!=0 );
    pTab = pItem->pTab;
    assert( pTab!=0 );
    sz = sqlite3LogEstToInt(pTab->nRowLogEst);
    if( sz<10000 ){
      sz = 10000;
    }else if( sz>10000000 ){
      sz = 10000000;
    }
    sqlite3VdbeAddOp2(v, OP_Blob, (int)sz, pLevel->regFilter);

    addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, iCur); VdbeCoverage(v);
    pWCEnd = &pWInfo->sWC.a[pWInfo->sWC.nTerm];
    for(pTerm=pWInfo->sWC.a; pTerm<pWCEnd; pTerm++){
      Expr *pExpr = pTerm->pExpr;
      if( (pTerm->wtFlags & TERM_VIRTUAL)==0
       && sqlite3ExprIsSingleTableConstraint(pExpr, pTabList, iSrc)
      ){
        sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL);
      }
    }
    if( pLoop->wsFlags & WHERE_IPK ){
      int r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, r1);

} {- - 3 - 5}
do_execsql_test 4.2 {
  SELECT * FROM v6 JOIN t5 ON false RIGHT JOIN t3 ON true;
} {- - - 3}
do_execsql_test 4.3 {
  SELECT * FROM t1 JOIN t2 ON false JOIN v6 ON true RIGHT JOIN t3 ON true;
} {- - - - 3}

# 2023-05-15 https://sqlite.org/forum/forumpost/f3f546025a
# This is restriction (6) on sqlite3ExprIsSingleTableConstraint().
# That restriction (now) used to implement restriction (9) on push-down.
# It is used for other things too, so it is not purely a push-down
# restriction.  But it seems convenient to put it here.
#
reset_db
db null -
do_execsql_test 5.0 {
  CREATE TABLE t1(a INT);  INSERT INTO t1 VALUES(1);
  CREATE TABLE t2(b INT);  INSERT INTO t2 VALUES(2);
  CREATE TABLE t3(c INT);  INSERT INTO t3 VALUES(3);
  CREATE TABLE t4(d INT);  INSERT INTO t4 VALUES(4);
  CREATE TABLE t5(e INT);  INSERT INTO t5 VALUES(5);
  SELECT *
    FROM t1 JOIN t2 ON null RIGHT JOIN t3 ON true
          LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4
   WHERE e>0;
} {- - 3 4 5}

finish_test