Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -3333,14 +3333,15 @@ }; assert( TK_LE==TK_GT+1 ); /* Make sure the ordering.. */ assert( TK_LT==TK_GT+2 ); /* ... of the TK_xx values... */ assert( TK_GE==TK_GT+3 ); /* ... is correcct. */ + assert( (pStart->wtFlags & TERM_VNULL)==0 ); testcase( pStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ pX = pStart->pExpr; assert( pX!=0 ); - assert( pStart->leftCursor==iCur ); + testcase( pStart->leftCursor!=iCur ); /* transitive constraints */ r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp); sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1); VdbeComment((v, "pk")); sqlite3ExprCacheAffinityChange(pParse, r1, 1); sqlite3ReleaseTempReg(pParse, rTemp); @@ -3350,11 +3351,12 @@ } if( pEnd ){ Expr *pX; pX = pEnd->pExpr; assert( pX!=0 ); - assert( pEnd->leftCursor==iCur ); + assert( (pEnd->wtFlags & TERM_VNULL)==0 ); + testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */ testcase( pEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ memEndValue = ++pParse->nMem; sqlite3ExprCode(pParse, pX->pRight, memEndValue); if( pX->op==TK_LT || pX->op==TK_GT ){ testOp = bRev ? OP_Le : OP_Ge; @@ -4280,10 +4282,15 @@ pNew->rSetup = 0; rLogSize = estLog(whereCost(pProbe->aiRowEst[0])); for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ int nIn = 0; if( pTerm->prereqRight & pNew->maskSelf ) continue; +#ifdef SQLITE_ENABLE_STAT3 + if( (pTerm->wtFlags & TERM_VNULL)!=0 && pSrc->pTab->aCol[iCol].notNull ){ + continue; /* skip IS NOT NULL constraints on a NOT NULL column */ + } +#endif pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTerm; Index: test/transitive1.test ================================================================== --- test/transitive1.test +++ test/transitive1.test @@ -44,7 +44,62 @@ SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a; } {3 3 3 20 20 20} do_execsql_test transitive1-220 { SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a; } {20 20 20 100 100 100} + +# Test cases for ticket [[d805526eae253103] 2013-07-08 +# "Incorrect join result or assertion fault due to transitive constraints" +# +do_execsql_test transitive1-300 { + CREATE TABLE t301(w INTEGER PRIMARY KEY, x); + CREATE TABLE t302(y INTEGER UNIQUE, z); + INSERT INTO t301 VALUES(1,2),(3,4),(5,6); + INSERT INTO t302 VALUES(1,3),(3,6),(5,7); + SELECT * + FROM t301 CROSS JOIN t302 + WHERE w=y AND y IS NOT NULL + ORDER BY +w; +} {1 2 1 3 3 4 3 6 5 6 5 7} +do_execsql_test transitive1-301 { + SELECT * + FROM t301 CROSS JOIN t302 + WHERE w=y AND y IS NOT NULL + ORDER BY w; +} {1 2 1 3 3 4 3 6 5 6 5 7} +do_execsql_test transitive1-310 { + SELECT * + FROM t301 CROSS JOIN t302 ON w=y + WHERE y>1 + ORDER BY +w +} {3 4 3 6 5 6 5 7} +do_execsql_test transitive1-311 { + SELECT * + FROM t301 CROSS JOIN t302 ON w=y + WHERE y>1 + ORDER BY w +} {3 4 3 6 5 6 5 7} +do_execsql_test transitive1-312 { + SELECT * + FROM t301 CROSS JOIN t302 ON w=y + WHERE y>1 + ORDER BY w DESC +} {5 6 5 7 3 4 3 6} +do_execsql_test transitive1-320 { + SELECT * + FROM t301 CROSS JOIN t302 ON w=y + WHERE y BETWEEN 2 AND 4; +} {3 4 3 6} +do_execsql_test transitive1-331 { + SELECT * + FROM t301 CROSS JOIN t302 ON w=y + WHERE y BETWEEN 1 AND 4 + ORDER BY w; +} {1 2 1 3 3 4 3 6} +do_execsql_test transitive1-332 { + SELECT * + FROM t301 CROSS JOIN t302 ON w=y + WHERE y BETWEEN 1 AND 4 + ORDER BY w DESC; +} {3 4 3 6 1 2 1 3} finish_test