Index: Makefile.in ================================================================== --- Makefile.in +++ Makefile.in @@ -1048,10 +1048,11 @@ # hidden when the library is built via the amalgamation). # TESTFIXTURE_FLAGS = -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 TESTFIXTURE_FLAGS += -DSQLITE_SERVER=1 -DSQLITE_PRIVATE="" -DSQLITE_CORE TESTFIXTURE_FLAGS += -DBUILD_sqlite +TESTFIXTURE_FLAGS += -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 TESTFIXTURE_SRC0 = $(TESTSRC2) libsqlite3.la TESTFIXTURE_SRC1 = sqlite3.c TESTFIXTURE_SRC = $(TESTSRC) $(TOP)/src/tclsqlite.c TESTFIXTURE_SRC += $(TESTFIXTURE_SRC$(USE_AMALGAMATION)) Index: Makefile.msc ================================================================== --- Makefile.msc +++ Makefile.msc @@ -1923,10 +1923,11 @@ # hidden when the library is built via the amalgamation). # TESTFIXTURE_FLAGS = -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 TESTFIXTURE_FLAGS = $(TESTFIXTURE_FLAGS) -DSQLITE_SERVER=1 -DSQLITE_PRIVATE="" TESTFIXTURE_FLAGS = $(TESTFIXTURE_FLAGS) -DSQLITE_CORE $(NO_WARN) +TESTFIXTURE_FLAGS = $(TESTFIXTURE_FLAGS) -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 TESTFIXTURE_SRC0 = $(TESTEXT) $(TESTSRC2) TESTFIXTURE_SRC1 = $(TESTEXT) $(SQLITE3C) !IF $(USE_AMALGAMATION)==0 TESTFIXTURE_SRC = $(TESTSRC) $(TOP)\src\tclsqlite.c $(TESTFIXTURE_SRC0) Index: ext/misc/series.c ================================================================== --- ext/misc/series.c +++ ext/misc/series.c @@ -215,16 +215,16 @@ }else{ return pCur->iValue > pCur->mxValue; } } -/* True to omit run-time checking of the start=, stop=, and/or step= -** parameters. The only reason to not omit these is for testing the +/* True to cause run-time checking of the start=, stop=, and/or step= +** parameters. The only reason to do this is for testing the ** constraint checking logic for virtual tables in the SQLite core. */ -#ifndef SERIES_OMIT_CONSTRAINT_VERIFY -# define SERIES_OMIT_CONSTRAINT_VERIFY 1 +#ifndef SQLITE_SERIES_CONSTRAINT_VERIFY +# define SQLITE_SERIES_CONSTRAINT_VERIFY 0 #endif /* ** This method is called to "rewind" the series_cursor object back ** to the first row of output. This method is always called at least @@ -330,19 +330,19 @@ break; } } if( startIdx>=0 ){ pIdxInfo->aConstraintUsage[startIdx].argvIndex = ++nArg; - pIdxInfo->aConstraintUsage[startIdx].omit = SERIES_OMIT_CONSTRAINT_VERIFY; + pIdxInfo->aConstraintUsage[startIdx].omit= !SQLITE_SERIES_CONSTRAINT_VERIFY; } if( stopIdx>=0 ){ pIdxInfo->aConstraintUsage[stopIdx].argvIndex = ++nArg; - pIdxInfo->aConstraintUsage[stopIdx].omit = SERIES_OMIT_CONSTRAINT_VERIFY; + pIdxInfo->aConstraintUsage[stopIdx].omit = !SQLITE_SERIES_CONSTRAINT_VERIFY; } if( stepIdx>=0 ){ pIdxInfo->aConstraintUsage[stepIdx].argvIndex = ++nArg; - pIdxInfo->aConstraintUsage[stepIdx].omit = SERIES_OMIT_CONSTRAINT_VERIFY; + pIdxInfo->aConstraintUsage[stepIdx].omit = !SQLITE_SERIES_CONSTRAINT_VERIFY; } if( (idxNum & 3)==3 ){ /* Both start= and stop= boundaries are available. This is the ** the preferred case */ pIdxInfo->estimatedCost = (double)(2 - ((idxNum&4)!=0)); Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -724,11 +724,12 @@ $(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) # Rules to build the 'testfixture' application. # TESTFIXTURE_FLAGS = -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 -TESTFIXTURE_FLAGS += -DSQLITE_SERVER=1 -DSQLITE_PRIVATE="" -DSQLITE_CORE +TESTFIXTURE_FLAGS += -DSQLITE_SERVER=1 -DSQLITE_PRIVATE="" -DSQLITE_CORE +TESTFIXTURE_FLAGS += -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 testfixture$(EXE): $(TESTSRC2) libsqlite3.a $(TESTSRC) $(TOP)/src/tclsqlite.c $(TCCX) $(TCL_FLAGS) -DTCLSH=1 $(TESTFIXTURE_FLAGS) \ $(TESTSRC) $(TESTSRC2) $(TOP)/src/tclsqlite.c \ -o testfixture$(EXE) $(LIBTCL) libsqlite3.a $(THREADLIB) Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -2896,17 +2896,10 @@ if( iTerm>mxTerm ) mxTerm = iTerm; testcase( iTerm==15 ); testcase( iTerm==16 ); if( iTerm<16 && pUsage[i].omit ) pNew->u.vtab.omitMask |= 1<eOperator & WO_IN)!=0 ){ - if( pUsage[i].omit==0 ){ - /* Do not attempt to use an IN constraint if the virtual table - ** says that the equivalent EQ constraint cannot be safely omitted. - ** If we do attempt to use such a constraint, some rows might be - ** repeated in the output. */ - break; - } /* A virtual table that is constrained by an IN clause may not ** consume the ORDER BY clause because (1) the order of IN terms ** is not necessarily related to the order of output terms and ** (2) Multiple outputs from a single IN value will not merge ** together. */ Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -872,10 +872,11 @@ ** to access the data. */ int iReg; /* P3 Value for OP_VFilter */ int addrNotFound; int nConstraint = pLoop->nLTerm; + int iIn; /* Counter for IN constraints */ sqlite3ExprCachePush(pParse); iReg = sqlite3GetTempRange(pParse, nConstraint+2); addrNotFound = pLevel->addrBrk; for(j=0; ju.vtab.idxStr, pLoop->u.vtab.needFree ? P4_MPRINTF : P4_STATIC); VdbeCoverage(v); pLoop->u.vtab.needFree = 0; - for(j=0; ju.vtab.omitMask>>j)&1 ){ - disableTerm(pLevel, pLoop->aLTerm[j]); - } - } pLevel->p1 = iCur; pLevel->op = pWInfo->eOnePass ? OP_Noop : OP_VNext; pLevel->p2 = sqlite3VdbeCurrentAddr(v); + iIn = pLevel->u.in.nIn; + for(j=nConstraint-1; j>=0; j--){ + pTerm = pLoop->aLTerm[j]; + if( j<16 && (pLoop->u.vtab.omitMask>>j)&1 ){ + disableTerm(pLevel, pTerm); + }else if( (pTerm->eOperator & WO_IN)!=0 ){ + Expr *pCompare; /* The comparison operator */ + Expr *pRight; /* RHS of the comparison */ + VdbeOp *pOp; /* Opcode to access the value of the IN constraint */ + + /* Reload the constraint value into reg[iReg+j+2]. The same value + ** was loaded into the same register prior to the OP_VFilter, but + ** the xFilter implementation might have changed the datatype or + ** encoding of the value in the register, so it *must* be reloaded. */ + assert( pLevel->u.in.aInLoop!=0 || db->mallocFailed ); + if( pLevel->u.in.aInLoop!=0 ){ + assert( iIn>0 ); + pOp = sqlite3VdbeGetOp(v, pLevel->u.in.aInLoop[--iIn].addrInTop); + assert( pOp->opcode==OP_Column || pOp->opcode==OP_Rowid ); + assert( pOp->opcode!=OP_Column || pOp->p3==iReg+j+2 ); + assert( pOp->opcode!=OP_Rowid || pOp->p2==iReg+j+2 ); + testcase( pOp->opcode==OP_Rowid ); + sqlite3VdbeAddOp3(v, pOp->opcode, pOp->p1, pOp->p2, pOp->p3); + } + + /* Generate code that will continue to the next row if + ** the IN constraint is not satisfied */ + pCompare = sqlite3PExpr(pParse, TK_EQ, 0, 0, 0); + assert( pCompare!=0 || db->mallocFailed ); + if( pCompare ){ + pCompare->pLeft = pTerm->pExpr->pLeft; + pCompare->pRight = pRight = sqlite3Expr(db, TK_REGISTER, 0); + if( pRight ) pRight->iTable = iReg+j+2; + sqlite3ExprIfFalse(pParse, pCompare, pLevel->addrCont, 0); + pCompare->pLeft = 0; + sqlite3ExprDelete(db, pCompare); + } + } + } sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2); sqlite3ExprCachePop(pParse); }else #endif /* SQLITE_OMIT_VIRTUALTABLE */ Index: test/bestindex1.test ================================================================== --- test/bestindex1.test +++ test/bestindex1.test @@ -137,20 +137,29 @@ do_execsql_test 2.2.$mode.5 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } {1 4} - set plan(use) {0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}} - set plan(use2) {0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}} + set plan(use) { + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} + 0 0 0 {EXECUTE LIST SUBQUERY 1} + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + } set plan(omit) { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} 0 0 0 {EXECUTE LIST SUBQUERY 1} + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + } + set plan(use2) { + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x} + 0 0 0 {EXECUTE LIST SUBQUERY 1} + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 2.2.$mode.6 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid - } [concat $plan($mode) {0 0 0 {USE TEMP B-TREE FOR ORDER BY}}] + } $plan($mode) } finish_test Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -610,10 +610,11 @@ select3.test select4.test select5.test select6.test sort.test subselect.test tableapi.test table.test temptable.test trace.test trigger1.test trigger2.test trigger3.test trigger4.test types2.test types.test unique.test update.test vacuum.test view.test where.test + bestindex1.test } # Run some tests in exclusive locking mode. # test_suite "exclusive" -description { Index: test/tabfunc01.test ================================================================== --- test/tabfunc01.test +++ test/tabfunc01.test @@ -117,7 +117,22 @@ do_catchsql_test tabfunc01-4.3 { ATTACH ':memory:' AS aux1; CREATE TABLE aux1.t1(a,b,c); SELECT * FROM aux1.generate_series(1,4) } {1 {no such table: aux1.generate_series}} + +# The next series of tests is verifying that virtual table are able +# to optimize the IN operator, even on terms that are not marked "omit". +# When the generate_series virtual table is compiled for the testfixture, +# the special -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 option is used, which +# causes the xBestIndex method of generate_series to leave the +# sqlite3_index_constraint_usage.omit flag set to 0, which should cause +# the SQLite core to verify the start=, stop=, and step= constraints on +# each step of output. At one point, the IN operator could not be used +# by virtual tables unless omit was set. +# +do_execsql_test tabfunc01-500 { + SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10 + ORDER BY +1; +} {1 7 11 17} finish_test