SQLite

/*
** We already know that pExpr is a binary operator where both operands are
** column references.  This routine checks to see if pExpr is an equivalence
** relation:
**   1.  The SQLITE_Transitive optimization must be enabled
**   2.  Must be either an == or an IS operator
**   3.  Not originating in the ON clause of an OUTER JOIN
**   4.  The operator is not IS or else the query does not contain RIGHT JOIN
**   5.  The affinities of A and B must be compatible
**   6a. Both operands use the same collating sequence OR
**   6b. The overall collating sequence is BINARY
** If this routine returns TRUE, that means that the RHS can be substituted
** for the LHS anyplace else in the WHERE clause where the LHS column occurs.
** This is an optimization.  No harm comes from returning 0.  But if 1 is
** returned when it should not be, then incorrect answers might result.
*/
static int termIsEquivalence(Parse *pParse, Expr *pExpr, SrcList *pSrc){
  char aff1, aff2;
  CollSeq *pColl;
  if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;  /* (1) */
  if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;                 /* (2) */
  if( ExprHasProperty(pExpr, EP_OuterON) ) return 0;                   /* (3) */
  if( pExpr->op==TK_IS && (pSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){
    return 0;                                                          /* (4) */
  }
  aff1 = sqlite3ExprAffinity(pExpr->pLeft);
  aff2 = sqlite3ExprAffinity(pExpr->pRight);
  if( aff1!=aff2
   && (!sqlite3IsNumericAffinity(aff1) || !sqlite3IsNumericAffinity(aff2))
  ){
    return 0;                                                          /* (5) */
  }
  pColl = sqlite3ExprCompareCollSeq(pParse, pExpr);
  if( !sqlite3IsBinary(pColl)
   && !sqlite3ExprCollSeqMatch(pParse, pExpr->pLeft, pExpr->pRight)
  ){
    return 0;                                                          /* (6) */
  }
  return 1;
}

/*
** Recursively walk the expressions of a SELECT statement and generate
** a bitmask indicating which tables are used in that expression
** tree.
*/

        idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
        if( idxNew==0 ) return;
        pNew = &pWC->a[idxNew];
        markTermAsChild(pWC, idxNew, idxTerm);
        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
        pTerm = &pWC->a[idxTerm];
        pTerm->wtFlags |= TERM_COPIED;
        assert( pWInfo->pTabList!=0 );
        if( termIsEquivalence(pParse, pDup, pWInfo->pTabList) ){
          pTerm->eOperator |= WO_EQUIV;
          eExtraOp = WO_EQUIV;
        }
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }

} {3 NULL NULL}
do_execsql_test join-31.7 {
  SELECT * FROM t3 LEFT JOIN t2 ON true NATURAL LEFT JOIN t1;
} {3 NULL NULL}
do_execsql_test join-31.8 {
  SELECT * FROM t3 LEFT JOIN t2 ON true JOIN t4 ON true NATURAL LEFT JOIN t1;
} {3 NULL 4 NULL}

# 2025-06-16 https://sqlite.org/forum/forumpost/68f29a2005
#
# The transitive-constraint optimization was not working for RIGHT JOIN.
#
reset_db
db null NULL
do_execsql_test join-32.1 {
  CREATE TABLE t0(w INT);
  CREATE TABLE t1(x INT);
  CREATE TABLE t2(y INT UNIQUE);
  CREATE VIEW v0(z) AS SELECT CAST(x AS INT) FROM t1 LEFT JOIN t2 ON true;
  INSERT INTO t1(x) VALUES(123);
  INSERT INTO t2(y) VALUES(NULL);
}
do_execsql_test join-32.2 {
  SELECT *
  FROM t0 JOIN v0 ON w=z
          RIGHT JOIN t1 ON true
          INNER JOIN t2 ON y IS z;
} {NULL NULL 123 NULL}
do_execsql_test join-32.3 {
  SELECT *
  FROM t0 JOIN v0 ON w=z
          RIGHT JOIN t1 ON true
          INNER JOIN t2 ON +y IS z;
} {NULL NULL 123 NULL}

finish_test