Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -628,12 +628,12 @@ ** ** In the previous sentence and in the diagram, "slot[]" refers to ** the WhereClause.a[] array. The slot[] array grows as needed to contain ** all terms of the WHERE clause. */ -static void whereSplit(WhereClause *pWC, Expr *pExpr, int op){ - pWC->op = (u8)op; +static void whereSplit(WhereClause *pWC, Expr *pExpr, u8 op){ + pWC->op = op; if( pExpr==0 ) return; if( pExpr->op!=op ){ whereClauseInsert(pWC, pExpr, 0); }else{ whereSplit(pWC, pExpr->pLeft, op); @@ -4514,10 +4514,11 @@ pNew->u.btree.nEq = 0; pNew->nLTerm = 0; pNew->iSortIdx = 0; pNew->rSetup = 0; pNew->prereq = mExtra; + pNew->nOut = rSize; pNew->u.btree.pIndex = pProbe; b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor); /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */ assert( (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || b==0 ); if( pProbe->tnum<=0 ){ @@ -4524,11 +4525,10 @@ /* Integer primary key index */ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; - pNew->nOut = rSize; /* TUNING: Cost of full table scan is 3*(N + log2(N)). ** + The extra 3 factor is to encourage the use of indexed lookups ** over full scans. A smaller constant 2 is used for covering ** index scans so that a covering index scan will be favored over ** a table scan. */ @@ -4547,11 +4547,10 @@ && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) ) ){ pNew->iSortIdx = b ? iSortIdx : 0; - pNew->nOut = rSize; if( m==0 ){ /* TUNING: Cost of a covering index scan is 2*(N + log2(N)). ** + The extra 2 factor is to encourage the use of indexed lookups ** over index scans. A table scan uses a factor of 3 so that ** index scans are favored over table scans. @@ -4822,11 +4821,12 @@ if( sBest.maskSelf ){ pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; pNew->wsFlags = WHERE_MULTI_OR; pNew->rSetup = 0; - pNew->rRun = rTotal; + /* TUNING: Multiple by 3.5 for the secondary table lookup */ + pNew->rRun = rTotal + 18; assert( 18==whereCost(7)-whereCost(2) ); pNew->nOut = nRow; pNew->prereq = prereq; memset(&pNew->u, 0, sizeof(pNew->u)); rc = whereLoopInsert(pBuilder, pNew); } Index: test/where9.test ================================================================== --- test/where9.test +++ test/where9.test @@ -596,11 +596,11 @@ DELETE FROM t1 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } -} {scan 0 sort 0} ;# DELETEs rows 90 91 92 97 +} {scan 98 sort 0} ;# DELETEs rows 90 91 92 97 do_test where9-6.3.6 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; @@ -613,11 +613,11 @@ UPDATE t1 SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } -} {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97 +} {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97 do_test where9-6.3.8 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; @@ -703,11 +703,11 @@ DELETE FROM t1 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } -} {scan 0 sort 0} ;# DELETEs rows 90 91 92 97 +} {scan 98 sort 0} ;# DELETEs rows 90 91 92 97 do_test where9-6.6.2 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; @@ -720,11 +720,11 @@ UPDATE t1 SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } -} {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97 +} {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97 do_test where9-6.6.4 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 200; ROLLBACK; @@ -779,27 +779,48 @@ WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {1 {no query solution}} -do_test where9-6.8.3 { - catchsql { - UPDATE t1 INDEXED BY t1b SET a=a+100 - WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) - OR (b NOT NULL AND c IS NULL AND d NOT NULL) - OR (b NOT NULL AND c NOT NULL AND d IS NULL) - } -} {0 {}} -do_test where9-6.8.4 { - catchsql { - DELETE FROM t1 INDEXED BY t1b - WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) - OR (b NOT NULL AND c IS NULL AND d NOT NULL) - OR (b NOT NULL AND c NOT NULL AND d IS NULL) - } -} {0 {}} - +ifcapable stat3 { + # When STAT3 is enabled, the "b NOT NULL" terms get translated + # into b>NULL, which can be satified by the index t1b. It is a very + # expensive way to do the query, but it works, and so a solution is possible. + do_test where9-6.8.3-stat3 { + catchsql { + UPDATE t1 INDEXED BY t1b SET a=a+100 + WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) + OR (b NOT NULL AND c IS NULL AND d NOT NULL) + OR (b NOT NULL AND c NOT NULL AND d IS NULL) + } + } {0 {}} + do_test where9-6.8.4-stat3 { + catchsql { + DELETE FROM t1 INDEXED BY t1b + WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) + OR (b NOT NULL AND c IS NULL AND d NOT NULL) + OR (b NOT NULL AND c NOT NULL AND d IS NULL) + } + } {0 {}} +} else { + do_test where9-6.8.3 { + catchsql { + UPDATE t1 INDEXED BY t1b SET a=a+100 + WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) + OR (b NOT NULL AND c IS NULL AND d NOT NULL) + OR (b NOT NULL AND c NOT NULL AND d IS NULL) + } + } {1 {no query solution}} + do_test where9-6.8.4 { + catchsql { + DELETE FROM t1 INDEXED BY t1b + WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) + OR (b NOT NULL AND c IS NULL AND d NOT NULL) + OR (b NOT NULL AND c NOT NULL AND d IS NULL) + } + } {1 {no query solution}} +} ############################################################################ # Test cases where terms inside an OR series are combined with AND terms # external to the OR clause. In other words, cases where # # x AND (y OR z)