SQLite

}

/* Undo the work of sqlite3SetJoinExpr(). In the expression p, convert every
** term that is marked with EP_OuterON and w.iJoin==iTable into
** an ordinary term that omits the EP_OuterON mark.
**
** This happens when a LEFT JOIN is simplified into an ordinary JOIN.
**
** If nullable is true, that means that Expr p might evaluate to NULL even
** if it is a reference to a NOT NULL column.  This can happen, for example,
** if the table that p references is on the left side of a RIGHT JOIN.
** If nullable is true, then take care to not remove the EP_CanBeNull bit.
** See forum thread https://sqlite.org/forum/forumpost/b40696f50145d21c
*/
static void unsetJoinExpr(Expr *p, int iTable, int nullable){
  while( p ){
    if( ExprHasProperty(p, EP_OuterON)
     && (iTable<0 || p->w.iJoin==iTable) ){
      ExprClearProperty(p, EP_OuterON);
      ExprSetProperty(p, EP_InnerON);
    }
    if( p->op==TK_COLUMN && p->iTable==iTable && !nullable ){
      ExprClearProperty(p, EP_CanBeNull);
    }
    if( p->op==TK_FUNCTION ){
      assert( ExprUseXList(p) );
      if( p->x.pList ){
        int i;
        for(i=0; i<p->x.pList->nExpr; i++){
          unsetJoinExpr(p->x.pList->a[i].pExpr, iTable, nullable);
        }
      }
    }
    unsetJoinExpr(p->pLeft, iTable, nullable);
    p = p->pRight;
  } 
}

/*
** This routine processes the join information for a SELECT statement.
**

  if( sqlite3ExprIsTableConstraint(pWhere, pSrc) ){
    nChng++;
    pSubq->selFlags |= SF_PushDown;
    while( pSubq ){
      SubstContext x;
      pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
      unsetJoinExpr(pNew, -1, 1);
      x.pParse = pParse;
      x.iTable = pSrc->iCursor;
      x.iNewTable = pSrc->iCursor;
      x.isOuterJoin = 0;
      x.pEList = pSubq->pEList;
      pNew = substExpr(&x, pNew);
#ifndef SQLITE_OMIT_WINDOWFUNC

    if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))==JT_LEFT
     && sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor)
     && OptimizationEnabled(db, SQLITE_SimplifyJoin)
    ){
      SELECTTRACE(0x100,pParse,p,
                ("LEFT-JOIN simplifies to JOIN on term %d\n",i));
      pItem->fg.jointype &= ~(JT_LEFT|JT_OUTER);
      unsetJoinExpr(p->pWhere, pItem->iCursor,
                    pTabList->a[0].fg.jointype & JT_LTORJ);
    }

    /* No futher action if this term of the FROM clause is no a subquery */
    if( pSub==0 ) continue;

    /* Catch mismatch in the declared columns of a view and the number of
    ** columns in the SELECT on the RHS */

  SELECT 99
    FROM t1
         LEFT JOIN t2 ON true
         RIGHT JOIN t3 ON true
         RIGHT JOIN t4 ON true
   WHERE a=b;
} {}

# 2022-06-13
# https://sqlite.org/forum/forumpost/b40696f501
#
# This optimization that converts "x ISNULL" into "FALSE" when column "x" has a
# NOT NULL constraint is too aggresive if the query contains RIGHT JOIN.
#
reset_db
db null -
do_execsql_test join8-23000 {
  CREATE TABLE t1(a TEXT);
  INSERT INTO t1 VALUES('c');
  CREATE TABLE t2(b TEXT, c TEXT NOT NULL);
  INSERT INTO t2 VALUES('a', 'b');
  CREATE TABLE t3(d TEXT);
  INSERT INTO t3 VALUES('x');
  CREATE TABLE t4(e TEXT);
  INSERT INTO t4 VALUES('y');
}
do_execsql_test join8-23010 {
  SELECT *
    FROM t1
         LEFT JOIN t2 ON TRUE
         JOIN t3 ON c=''
         RIGHT JOIN t4 ON b='';
} {- - - - y}
do_execsql_test join8-23020 {
  SELECT *
    FROM t1
         LEFT JOIN t2 ON TRUE
         JOIN t3 ON c=''
         RIGHT JOIN t4 ON b=''
   WHERE d ISNULL
} {- - - - y}

finish_test