SQLite

*/
static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
  if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
  switch( pExpr->op ){
    case TK_ISNULL:
    case TK_IS:
    case TK_OR:
    case TK_CASE:
    case TK_FUNCTION:
    case TK_AGG_FUNCTION:
      return WRC_Prune;
    case TK_COLUMN:
    case TK_AGG_COLUMN:
      if( pWalker->u.iCur==pExpr->iTable ){
        pWalker->eCode = 1;

  INSERT INTO t3(id) VALUES(1),(2);
  SELECT t1.id, x2.id, x3.id
  FROM t1
  LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
  LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
} {456 {} {}}

# 2018-03-24.
# E.Pasma discovered that the LEFT JOIN strength reduction optimization
# was misbehaving.  The problem turned out to be that the
# sqlite3ExprImpliesNotNull() routine was saying that CASE expressions
# like
#
#     CASE WHEN true THEN true ELSE x=0 END
#
# could never be true if x is NULL.  The following test cases verify
# that this error has been resolved.
#
db close
sqlite3 db :memory:
do_execsql_test join-15.100 {
  CREATE TABLE t1(a INT, b INT);
  INSERT INTO t1 VALUES(1,2),(3,4);
  CREATE TABLE t2(x INT, y INT);
  SELECT *, 'x'
    FROM t1 LEFT JOIN t2
   WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;
} {1 2 {} {} x 3 4 {} {} x}
do_execsql_test join-15.110 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
  INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
  CREATE INDEX t1b ON t1(b);
  CREATE TABLE t2(x INTEGER PRIMARY KEY);
  INSERT INTO t2(x) VALUES(0),(1);
  SELECT  a1, a2, a3, a4, a5
   FROM (SELECT a AS a1 FROM t1 WHERE b=0)
        JOIN (SELECT x AS x1 FROM t2)
        LEFT JOIN (SELECT a AS a2, b AS b2 FROM t1)
          ON x1 IS TRUE AND b2=a1
        JOIN (SELECT x AS x2 FROM t2)
          ON x2<=CASE WHEN x1 THEN CASE WHEN a2 THEN 1 ELSE -1 END ELSE 0 END
        LEFT JOIN (SELECT a AS a3, b AS b3 FROM t1)
          ON x2 IS TRUE AND b3=a2
        JOIN (SELECT x AS x3 FROM t2)
          ON x3<=CASE WHEN x2 THEN CASE WHEN a3 THEN 1 ELSE -1 END ELSE 0 END
        LEFT JOIN (SELECT a AS a4, b AS b4 FROM t1)
          ON x3 IS TRUE AND b4=a3
        JOIN (SELECT x AS x4 FROM t2)
          ON x4<=CASE WHEN x3 THEN CASE WHEN a4 THEN 1 ELSE -1 END ELSE 0 END
        LEFT JOIN (SELECT a AS a5, b AS b5 FROM t1)
          ON x4 IS TRUE AND b5=a4
   ORDER BY a1, a2, a3, a4, a5;
} {1 {} {} {} {} 1 11 {} {} {} 1 12 {} {} {} 1 12 121 {} {} 1 13 {} {} {}}

finish_test