Index: src/select.c
==================================================================
--- src/select.c
+++ src/select.c
@@ -10,11 +10,11 @@
 **
 *************************************************************************
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements in SQLite.
 **
-** $Id: select.c,v 1.271 2005/09/19 17:35:53 drh Exp $
+** $Id: select.c,v 1.272 2005/09/19 21:05:49 drh Exp $
 */
 #include "sqliteInt.h"
 
 
 /*
@@ -177,10 +177,11 @@
   const char *zCol,        /* Name of the column */
   const Table *pTab1,      /* First table */
   const char *zAlias1,     /* Alias for first table.  May be NULL */
   const Table *pTab2,      /* Second table */
   const char *zAlias2,     /* Alias for second table.  May be NULL */
+  int iRightJoinTable,     /* VDBE cursor for the right table */
   Expr **ppExpr            /* Add the equality term to this expression */
 ){
   Expr *pE1a, *pE1b, *pE1c;
   Expr *pE2a, *pE2b, *pE2c;
   Expr *pE;
@@ -197,27 +198,45 @@
   pE2b = createIdExpr(zAlias2);
   pE1c = sqlite3Expr(TK_DOT, pE1b, pE1a, 0);
   pE2c = sqlite3Expr(TK_DOT, pE2b, pE2a, 0);
   pE = sqlite3Expr(TK_EQ, pE1c, pE2c, 0);
   ExprSetProperty(pE, EP_FromJoin);
+  pE->iRightJoinTable = iRightJoinTable;
   *ppExpr = sqlite3ExprAnd(*ppExpr, pE);
 }
 
 /*
 ** Set the EP_FromJoin property on all terms of the given expression.
+** And set the Expr.iRightJoinTable to iTable for every term in the
+** expression.
 **
 ** The EP_FromJoin property is used on terms of an expression to tell
 ** the LEFT OUTER JOIN processing logic that this term is part of the
 ** join restriction specified in the ON or USING clause and not a part
 ** of the more general WHERE clause.  These terms are moved over to the
 ** WHERE clause during join processing but we need to remember that they
 ** originated in the ON or USING clause.
+**
+** The Expr.iRightJoinTable tells the WHERE clause processing that the
+** expression depends on table iRightJoinTable even if that table is not
+** explicitly mentioned in the expression.  That information is needed
+** for cases like this:
+**
+**    SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
+**
+** The where clause needs to defer the handling of the t1.x=5
+** term until after the t2 loop of the join.  In that way, a
+** NULL t2 row will be inserted whenever t1.x!=5.  If we do not
+** defer the handling of t1.x=5, it will be processed immediately
+** after the t1 loop and rows with t1.x!=5 will never appear in
+** the output, which is incorrect.
 */
-static void setJoinExpr(Expr *p){
+static void setJoinExpr(Expr *p, int iTable){
   while( p ){
     ExprSetProperty(p, EP_FromJoin);
-    setJoinExpr(p->pLeft);
+    p->iRightJoinTable = iTable;
+    setJoinExpr(p->pLeft, iTable);
     p = p->pRight;
   } 
 }
 
 /*
@@ -260,11 +279,13 @@
       }
       for(j=0; j<pLeftTab->nCol; j++){
         char *zName = pLeftTab->aCol[j].zName;
         if( columnIndex(pRightTab, zName)>=0 ){
           addWhereTerm(zName, pLeftTab, pLeft->zAlias, 
-                              pRightTab, pRight->zAlias, &p->pWhere);
+                              pRightTab, pRight->zAlias,
+                              pRight->iCursor, &p->pWhere);
+          
         }
       }
     }
 
     /* Disallow both ON and USING clauses in the same join
@@ -277,11 +298,11 @@
 
     /* Add the ON clause to the end of the WHERE clause, connected by
     ** an AND operator.
     */
     if( pLeft->pOn ){
-      setJoinExpr(pLeft->pOn);
+      setJoinExpr(pLeft->pOn, pRight->iCursor);
       p->pWhere = sqlite3ExprAnd(p->pWhere, pLeft->pOn);
       pLeft->pOn = 0;
     }
 
     /* Create extra terms on the WHERE clause for each column named
@@ -299,11 +320,12 @@
           sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
             "not present in both tables", zName);
           return 1;
         }
         addWhereTerm(zName, pLeftTab, pLeft->zAlias, 
-                            pRightTab, pRight->zAlias, &p->pWhere);
+                            pRightTab, pRight->zAlias,
+                            pRight->iCursor, &p->pWhere);
       }
     }
   }
   return 0;
 }

Index: src/sqliteInt.h
==================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -9,11 +9,11 @@
 **    May you share freely, never taking more than you give.
 **
 *************************************************************************
 ** Internal interface definitions for SQLite.
 **
-** @(#) $Id: sqliteInt.h,v 1.420 2005/09/19 17:35:53 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.421 2005/09/19 21:05:49 drh Exp $
 */
 #ifndef _SQLITEINT_H_
 #define _SQLITEINT_H_
 
 /*
@@ -901,10 +901,11 @@
   Token span;            /* Complete text of the expression */
   int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                          ** iColumn-th field of the iTable-th table. */
   AggInfo *pAggInfo;     /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */
   int iAgg;              /* Which entry in pAggInfo->aCol[] or ->aFunc[] */
+  int iRightJoinTable;   /* If EP_FromJoin, the right table of the join */
   Select *pSelect;       /* When the expression is a sub-select.  Also the
                          ** right side of "<expr> IN (<select>)" */
   Table *pTab;           /* Table for OP_Column expressions. */
 };
 

Index: src/where.c
==================================================================
--- src/where.c
+++ src/where.c
@@ -14,11 +14,11 @@
 ** generating the code that loops through a table looking for applicable
 ** rows.  Indices are selected and used to speed the search when doing
 ** so is applicable.  Because this module is responsible for selecting
 ** indices, you might also think of this module as the "query optimizer".
 **
-** $Id: where.c,v 1.176 2005/09/19 13:15:23 drh Exp $
+** $Id: where.c,v 1.177 2005/09/19 21:05:49 drh Exp $
 */
 #include "sqliteInt.h"
 
 /*
 ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
@@ -554,18 +554,21 @@
     pTerm->prereqRight = exprListTableUsage(pMaskSet, pExpr->pList)
                           | exprSelectTableUsage(pMaskSet, pExpr->pSelect);
   }else{
     pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
   }
-  pTerm->prereqAll = prereqAll = exprTableUsage(pMaskSet, pExpr);
+  prereqAll = exprTableUsage(pMaskSet, pExpr);
+  if( ExprHasProperty(pExpr, EP_FromJoin) ){
+    prereqAll |= getMask(pMaskSet, pExpr->iRightJoinTable);
+  }
+  pTerm->prereqAll = prereqAll;
   pTerm->leftCursor = -1;
   pTerm->iParent = -1;
   pTerm->operator = 0;
   if( allowedOp(pExpr->op) && (pTerm->prereqRight & prereqLeft)==0 ){
     Expr *pLeft = pExpr->pLeft;
     Expr *pRight = pExpr->pRight;
-    assert( prereqAll == (pTerm->prereqRight | prereqLeft) ); /* ticket 1433 */
     if( pLeft->op==TK_COLUMN ){
       pTerm->leftCursor = pLeft->iTable;
       pTerm->leftColumn = pLeft->iColumn;
       pTerm->operator = operatorMask(pExpr->op);
     }

ADDED   test/join5.test
Index: test/join5.test
==================================================================
--- test/join5.test
+++ test/join5.test
@@ -0,0 +1,62 @@
+# 2005 September 19
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+# This file implements tests for left outer joins containing ON
+# clauses that restrict the scope of the left term of the join.
+#
+# $Id: join5.test,v 1.1 2005/09/19 21:05:50 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+
+do_test join5-1.1 {
+  execsql {
+    BEGIN;
+    CREATE TABLE t1(a integer primary key, b integer, c integer);
+    CREATE TABLE t2(x integer primary key, y);
+    CREATE TABLE t3(p integer primary key, q);
+    INSERT INTO t3 VALUES(11,'t3-11');
+    INSERT INTO t3 VALUES(12,'t3-12');
+    INSERT INTO t2 VALUES(11,'t2-11');
+    INSERT INTO t2 VALUES(12,'t2-12');
+    INSERT INTO t1 VALUES(1, 5, 0);
+    INSERT INTO t1 VALUES(2, 11, 2);
+    INSERT INTO t1 VALUES(3, 12, 1);
+    COMMIT;
+  }
+} {}
+do_test join5-1.2 {
+  execsql {
+    select * from t1 left join t2 on t1.b=t2.x and t1.c=1
+  }
+} {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12}
+do_test join5-1.3 {
+  execsql {
+    select * from t1 left join t2 on t1.b=t2.x where t1.c=1
+  }
+} {3 12 1 12 t2-12}
+do_test join5-1.4 {
+  execsql {
+    select * from t1 left join t2 on t1.b=t2.x and t1.c=1
+                     left join t3 on t1.b=t3.p and t1.c=2
+  }
+} {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}}
+do_test join5-1.5 {
+  execsql {
+    select * from t1 left join t2 on t1.b=t2.x and t1.c=1
+                     left join t3 on t1.b=t3.p where t1.c=2
+  }
+} {2 11 2 {} {} 11 t3-11}
+
+
+finish_test