SQLite

   && sqlite3ColumnOfIndex(pHint->pIdx, pExpr->iColumn)<0
  ){
    pWalker->eCode = 1;
  }
  return WRC_Continue;
}

/*
** Test whether or not expression pExpr, which was part of a WHERE clause,
** should be included in the cursor-hint for a table that is on the rhs
** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the 
** expression is not suitable.
**
** An expression is unsuitable if it might evaluate to non NULL even if
** a TK_COLUMN node that does affect the value of the expression is set
** to NULL. For example:
**
**   col IS NULL
**   col IS NOT NULL
**   coalesce(col, 1)
**   CASE WHEN col THEN 0 ELSE 1 END
*/
static int codeCursorHintIsOrFunction(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_IS || pExpr->op==TK_FUNCTION 
   || pExpr->op==TK_ISNULL || pExpr->op==TK_ISNOT 
   || pExpr->op==TK_NOTNULL || pExpr->op==TK_CASE 
  ){
    pWalker->eCode = 1;
  }
  return WRC_Continue;
}


/*
** This function is called on every node of an expression tree used as an
** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
** that accesses any table other than the one identified by
** CCurHint.iTabCur, then do the following:
**

    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( pTerm->prereqAll & pLevel->notReady ) continue;

    /* Any terms specified as part of the ON(...) clause for any LEFT 
    ** JOIN for which the current table is not the rhs are omitted
    ** from the cursor-hint. 
    **
    ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms 
    ** that were specified as part of the WHERE clause must be excluded.
    ** This is to address the following:
    **
    **   SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;
    **
    ** Say there is a single row in t2 that matches (t1.a=t2.b), but its
    ** t2.c values is not NULL. If the (t2.c IS NULL) constraint is 

    ** pushed down to the cursor, this row is filtered out, causing
    ** SQLite to synthesize a row of NULL values. Which does match the

    ** WHERE clause, and so the query returns a row. Which is incorrect.
    **
    ** For the same reason, WHERE terms such as:
    **
    **   WHERE 1 = (t2.c IS NULL)
    **
    ** are also excluded. See codeCursorHintIsOrFunction() for details.
    */
    if( pTabItem->fg.jointype & JT_LEFT ){
      Expr *pExpr = pTerm->pExpr;
      if( !ExprHasProperty(pExpr, EP_FromJoin) 
       || pExpr->iRightJoinTable!=pTabItem->iCursor
      ){
        sWalker.eCode = 0;
        sWalker.xExprCallback = codeCursorHintIsOrFunction;
        sqlite3WalkExpr(&sWalker, pTerm->pExpr);
        if( sWalker.eCode ) continue;
      }
    }else{
      if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue;
    }

    /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize
    ** the cursor.  These terms are not needed as hints for a pure range

} {
  t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
}

do_extract_hints_test 1.7 {
  SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f);
} {
  t2 {EQ(r[2],c0)} t3 {AND(EQ(r[6],c0),EQ(r[7],c1))}
}

#-------------------------------------------------------------------------
#
do_execsql_test 2.0 {
  CREATE TABLE x1(x, y);
  CREATE TABLE x2(a, b);
}

do_extract_hints_test 2.1 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NULL;
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.2 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS +NULL;
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.3 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = (b IS NULL)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.4 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.5 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.6 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.7 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.8 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.9 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.10 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
} {
  x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
}

finish_test