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; jnCol; 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 " IN (