Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -1203,12 +1203,14 @@ /* ** If the pBase expression originated in the ON or USING clause of ** a join, then transfer the appropriate markings over to derived. */ static void transferJoinMarkings(Expr *pDerived, Expr *pBase){ - pDerived->flags |= pBase->flags & EP_FromJoin; - pDerived->iRightJoinTable = pBase->iRightJoinTable; + if( pDerived ){ + pDerived->flags |= pBase->flags & EP_FromJoin; + pDerived->iRightJoinTable = pBase->iRightJoinTable; + } } #if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY) /* ** Analyze a term that consists of two or more OR-connected @@ -1661,10 +1663,11 @@ Expr *pNewExpr; int idxNew; pNewExpr = sqlite3PExpr(pParse, ops[i], sqlite3ExprDup(db, pExpr->pLeft, 0), sqlite3ExprDup(db, pList->a[i].pExpr, 0), 0); + transferJoinMarkings(pNewExpr, pExpr); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew==0 ); exprAnalyze(pSrc, pWC, idxNew); pTerm = &pWC->a[idxTerm]; pWC->a[idxNew].iParent = idxTerm; @@ -1728,17 +1731,19 @@ sCollSeqName.n = 6; pNewExpr1 = sqlite3ExprDup(db, pLeft, 0); pNewExpr1 = sqlite3PExpr(pParse, TK_GE, sqlite3ExprAddCollateToken(pParse,pNewExpr1,&sCollSeqName), pStr1, 0); + transferJoinMarkings(pNewExpr1, pExpr); idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew1==0 ); exprAnalyze(pSrc, pWC, idxNew1); pNewExpr2 = sqlite3ExprDup(db, pLeft, 0); pNewExpr2 = sqlite3PExpr(pParse, TK_LT, sqlite3ExprAddCollateToken(pParse,pNewExpr2,&sCollSeqName), pStr2, 0); + transferJoinMarkings(pNewExpr2, pExpr); idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew2==0 ); exprAnalyze(pSrc, pWC, idxNew2); pTerm = &pWC->a[idxTerm]; if( isComplete ){ Index: test/where9.test ================================================================== --- test/where9.test +++ test/where9.test @@ -948,7 +948,31 @@ SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL; SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL; SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL; } } {1 2 3 4 8 9} + +# Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27] +# Incorrect result when OR is used in a join to the right of a LEFT JOIN +# +do_test where9-10.1 { + db eval { + CREATE TABLE t101 (id INTEGER PRIMARY KEY); + INSERT INTO t101 VALUES (1); + SELECT * FROM t101 AS t0 + LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20 + JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id)); + } +} {1 {} 1} +do_test where9-10.2 { + db eval { + CREATE TABLE t102 (id TEXT UNIQUE NOT NULL); + INSERT INTO t102 VALUES ('1'); + SELECT * FROM t102 AS t0 + LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%' + JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id)); + } +} {1 {} 1} + + finish_test