diff options
| -rw-r--r-- | manifest | 21 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/test_bestindex.c | 4 | ||||
| -rw-r--r-- | src/where.c | 154 | ||||
| -rw-r--r-- | test/bestindexC.test | 75 |
5 files changed, 179 insertions, 77 deletions
@@ -1,5 +1,5 @@ -C Remove\sthe\sunconditional\sSQLITE_OMIT_WAL\swhen\sbuilding\sin\swasi-sdk\smode,\sper\s[forum:80003e91a7a6cb4d|requests\sin\sthe\sforum]. -D 2024-05-30T17:56:26.243 +C Better\shandle\sWHERE\sterms\sthat\sare\scommon\sto\stwo\sor\smore\sOR\sbranches\swhen\splanning\svirtual\stable\squeries. +D 2024-05-31T19:26:22.693 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -776,7 +776,7 @@ F src/test9.c 12e5ba554d2d1cbe0158f6ab3f7ffcd7a86ee4e5 F src/test_async.c 195ab49da082053fdb0f949c114b806a49ca770a F src/test_autoext.c 915d245e736652a219a907909bb6710f0d587871 F src/test_backup.c bf5da90c9926df0a4b941f2d92825a01bbe090a0 -F src/test_bestindex.c 770429c434221afe6216ec81fe4c00ad3bbdad1d5e64576aa613ffb7c5a984f0 +F src/test_bestindex.c e8ae36817864ca80bd4608d109634cbc35cca099234313ff9f3c061a22783bd8 F src/test_blob.c ae4a0620b478548afb67963095a7417cd06a4ec0a56adb453542203bfdcb31ce F src/test_btree.c 8b2dc8b8848cf3a4db93f11578f075e82252a274 F src/test_config.c 5fa77ee6064ba546e144c4fea870c5ede2c54314616f81485c6a9c4192100c75 @@ -840,7 +840,7 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9 F src/wal.c 887fc4ca3f020ebb2e376f222069570834ac63bf50111ef0cbf3ae417048ed89 F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452 F src/walker.c 7c7ea0115345851c3da4e04e2e239a29983b61fb5b038b94eede6aba462640e2 -F src/where.c 5ddff7d4e0c0f7a2616efc126e4b8224387ce5fa4f5adf0737c8daa3e83824cf +F src/where.c ddffcd89b1b794a3d7ce56ac31315ae7a5625313046f6aff3942a682b3fa0d48 F src/whereInt.h 002adc3aa2cc10733b9b27958fdbe893987cd989fab25a9853941c1f9b9b0a65 F src/wherecode.c d5184620bcb5265d59072cb66e1386bfe0331a9ce7614286f9ab79a4fcd00fb8 F src/whereexpr.c 67d15caf88a1a9528283d68ff578e024cf9fe810b517bb0343e5aaf695ad97dd @@ -940,7 +940,7 @@ F test/bestindex8.test b63a4f171a2c83d481bb14c431a8b72e85d27b2ffdaa0435a95d58ca9 F test/bestindex9.test 1a4b93db117fd8abe74ae9be982f86aa72f01e60cd4ac541e6ede39673a451a0 F test/bestindexA.test e1b5def6b190797cacf008e6815ffb78fb30261999030d60a728d572eef44c7f F test/bestindexB.test 328b97b69cd1a20928d5997f9ecb04d2e00f1d18e19ab27f9e9adb44d7bc51ce -F test/bestindexC.test 9e6f184be080fd9c4605a7e5c7097eed1a259372f9af78151c37b072a9086f86 +F test/bestindexC.test ae34a3ceb64b117cd20aba71ea8e9fadd639b021cf232e15a57fe2eefe1f29ea F test/between.test b9a65fb065391980119e8a781a7409d3fcf059d89968279c750e190a9a1d5263 F test/bigfile.test aa74f4e5db51c8e54a1d9de9fa65d01d1eb20b59 F test/bigfile2.test 1b489a3a39ae90c7f027b79110d6b4e1dbc71bfc @@ -2194,8 +2194,11 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P a47c644fef71f3ab3dc584ea917eaab9a8e5b4c9dcb57bdd29747ba32108e85f -R dbf8da7df3d6710109e3f50f2572dd2b -U stephan -Z d943edf9ee101c09eebc6bdb1635bbc9 +P dcc2bb2c562e97e090174d4d0970bfa1551e5eb4db022e6d232c4dd786818e45 +R 586dbe3b8a77baf191835ccb84865021 +T *branch * xbestindex-or-terms +T *sym-xbestindex-or-terms * +T -sym-trunk * +U dan +Z cae40e4fc9a5b7b19f7dc2f2cabe22f0 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 33a986d62..4309f282d 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -dcc2bb2c562e97e090174d4d0970bfa1551e5eb4db022e6d232c4dd786818e45
\ No newline at end of file +4edd9b29f58621335b8a562280c991c34804bbba090f90c951261d043cff1965
\ No newline at end of file diff --git a/src/test_bestindex.c b/src/test_bestindex.c index 0e1e86a81..072bff2e7 100644 --- a/src/test_bestindex.c +++ b/src/test_bestindex.c @@ -700,6 +700,10 @@ static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ pIdxInfo->aConstraintUsage[iCons].omit = bOmit; } } + }else + if( sqlite3_stricmp("constraint", zCmd)==0 ){ + rc = SQLITE_CONSTRAINT; + pTab->base.zErrMsg = sqlite3_mprintf("%s", Tcl_GetString(p)); }else{ rc = SQLITE_ERROR; pTab->base.zErrMsg = sqlite3_mprintf("unexpected: %s", zCmd); diff --git a/src/where.c b/src/where.c index ed095fa3b..53be42888 100644 --- a/src/where.c +++ b/src/where.c @@ -1347,6 +1347,20 @@ static SQLITE_NOINLINE void sqlite3ConstructBloomFilter( #ifndef SQLITE_OMIT_VIRTUALTABLE /* +** Return term iTerm of the WhereClause passed as the first argument. Terms +** are numbered from 0 upwards, starting with the terms in pWC->a[], then +** those in pWC->pOuter->a[] (if any), and so on. +*/ +static WhereTerm *termFromWhereClause(WhereClause *pWC, int iTerm){ + WhereClause *p; + for(p=pWC; p; p=p->pOuter){ + if( iTerm<p->nTerm ) return &p->a[iTerm]; + iTerm -= p->nTerm; + } + return 0; +} + +/* ** Allocate and populate an sqlite3_index_info structure. It is the ** responsibility of the caller to eventually release the structure ** by passing the pointer returned by this function to freeIndexInfo(). @@ -1372,6 +1386,7 @@ static sqlite3_index_info *allocateIndexInfo( const Table *pTab; int eDistinct = 0; ExprList *pOrderBy = pWInfo->pOrderBy; + WhereClause *p; assert( pSrc!=0 ); pTab = pSrc->pTab; @@ -1382,28 +1397,30 @@ static sqlite3_index_info *allocateIndexInfo( ** Mark each term with the TERM_OK flag. Set nTerm to the number of ** terms found. */ - for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ - pTerm->wtFlags &= ~TERM_OK; - if( pTerm->leftCursor != pSrc->iCursor ) continue; - if( pTerm->prereqRight & mUnusable ) continue; - assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); - testcase( pTerm->eOperator & WO_IN ); - testcase( pTerm->eOperator & WO_ISNULL ); - testcase( pTerm->eOperator & WO_IS ); - testcase( pTerm->eOperator & WO_ALL ); - if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; - if( pTerm->wtFlags & TERM_VNULL ) continue; + for(p=pWC, nTerm=0; p; p=p->pOuter){ + for(i=0, pTerm=p->a; i<p->nTerm; i++, pTerm++){ + pTerm->wtFlags &= ~TERM_OK; + if( pTerm->leftCursor != pSrc->iCursor ) continue; + if( pTerm->prereqRight & mUnusable ) continue; + assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); + testcase( pTerm->eOperator & WO_IN ); + testcase( pTerm->eOperator & WO_ISNULL ); + testcase( pTerm->eOperator & WO_IS ); + testcase( pTerm->eOperator & WO_ALL ); + if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; + if( pTerm->wtFlags & TERM_VNULL ) continue; - assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 ); - assert( pTerm->u.x.leftColumn>=XN_ROWID ); - assert( pTerm->u.x.leftColumn<pTab->nCol ); - if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0 - && !constraintCompatibleWithOuterJoin(pTerm,pSrc) - ){ - continue; + assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 ); + assert( pTerm->u.x.leftColumn>=XN_ROWID ); + assert( pTerm->u.x.leftColumn<pTab->nCol ); + if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0 + && !constraintCompatibleWithOuterJoin(pTerm,pSrc) + ){ + continue; + } + nTerm++; + pTerm->wtFlags |= TERM_OK; } - nTerm++; - pTerm->wtFlags |= TERM_OK; } /* If the ORDER BY clause contains only columns in the current @@ -1482,49 +1499,52 @@ static sqlite3_index_info *allocateIndexInfo( pHidden->pParse = pParse; pHidden->eDistinct = eDistinct; pHidden->mIn = 0; - for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ - u16 op; - if( (pTerm->wtFlags & TERM_OK)==0 ) continue; - pIdxCons[j].iColumn = pTerm->u.x.leftColumn; - pIdxCons[j].iTermOffset = i; - op = pTerm->eOperator & WO_ALL; - if( op==WO_IN ){ - if( (pTerm->wtFlags & TERM_SLICE)==0 ){ - pHidden->mIn |= SMASKBIT32(j); + for(p=pWC, i=j=0; p; p=p->pOuter){ + int nLast = i+p->nTerm;; + for(pTerm=p->a; i<nLast; i++, pTerm++){ + u16 op; + if( (pTerm->wtFlags & TERM_OK)==0 ) continue; + pIdxCons[j].iColumn = pTerm->u.x.leftColumn; + pIdxCons[j].iTermOffset = i; + op = pTerm->eOperator & WO_ALL; + if( op==WO_IN ){ + if( (pTerm->wtFlags & TERM_SLICE)==0 ){ + pHidden->mIn |= SMASKBIT32(j); + } + op = WO_EQ; } - op = WO_EQ; - } - if( op==WO_AUX ){ - pIdxCons[j].op = pTerm->eMatchOp; - }else if( op & (WO_ISNULL|WO_IS) ){ - if( op==WO_ISNULL ){ - pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL; + if( op==WO_AUX ){ + pIdxCons[j].op = pTerm->eMatchOp; + }else if( op & (WO_ISNULL|WO_IS) ){ + if( op==WO_ISNULL ){ + pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL; + }else{ + pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS; + } }else{ - pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS; - } - }else{ - pIdxCons[j].op = (u8)op; - /* The direct assignment in the previous line is possible only because - ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The - ** following asserts verify this fact. */ - assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); - assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); - assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); - assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); - assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); - assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_AUX) ); - - if( op & (WO_LT|WO_LE|WO_GT|WO_GE) - && sqlite3ExprIsVector(pTerm->pExpr->pRight) - ){ - testcase( j!=i ); - if( j<16 ) mNoOmit |= (1 << j); - if( op==WO_LT ) pIdxCons[j].op = WO_LE; - if( op==WO_GT ) pIdxCons[j].op = WO_GE; + pIdxCons[j].op = (u8)op; + /* The direct assignment in the previous line is possible only because + ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The + ** following asserts verify this fact. */ + assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); + assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); + assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); + assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); + assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); + assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_AUX) ); + + if( op & (WO_LT|WO_LE|WO_GT|WO_GE) + && sqlite3ExprIsVector(pTerm->pExpr->pRight) + ){ + testcase( j!=i ); + if( j<16 ) mNoOmit |= (1 << j); + if( op==WO_LT ) pIdxCons[j].op = WO_LE; + if( op==WO_GT ) pIdxCons[j].op = WO_GE; + } } - } - j++; + j++; + } } assert( j==nTerm ); pIdxInfo->nConstraint = j; @@ -4159,7 +4179,7 @@ static int whereLoopAddVirtualOne( ** arguments mUsable and mExclude. */ pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; for(i=0; i<nConstraint; i++, pIdxCons++){ - WhereTerm *pTerm = &pWC->a[pIdxCons->iTermOffset]; + WhereTerm *pTerm = termFromWhereClause(pWC, pIdxCons->iTermOffset); pIdxCons->usable = 0; if( (pTerm->prereqRight & mUsable)==pTerm->prereqRight && (pTerm->eOperator & mExclude)==0 @@ -4207,7 +4227,7 @@ static int whereLoopAddVirtualOne( int j = pIdxCons->iTermOffset; if( iTerm>=nConstraint || j<0 - || j>=pWC->nTerm + || (pTerm = termFromWhereClause(pWC, j))==0 || pNew->aLTerm[iTerm]!=0 || pIdxCons->usable==0 ){ @@ -4218,7 +4238,6 @@ static int whereLoopAddVirtualOne( testcase( iTerm==nConstraint-1 ); testcase( j==0 ); testcase( j==pWC->nTerm-1 ); - pTerm = &pWC->a[j]; pNew->prereq |= pTerm->prereqRight; assert( iTerm<pNew->nLSlot ); pNew->aLTerm[iTerm] = pTerm; @@ -4335,7 +4354,7 @@ const char *sqlite3_vtab_collation(sqlite3_index_info *pIdxInfo, int iCons){ if( iCons>=0 && iCons<pIdxInfo->nConstraint ){ CollSeq *pC = 0; int iTerm = pIdxInfo->aConstraint[iCons].iTermOffset; - Expr *pX = pHidden->pWC->a[iTerm].pExpr; + Expr *pX = termFromWhereClause(pHidden->pWC, iTerm)->pExpr; if( pX->pLeft ){ pC = sqlite3ExprCompareCollSeq(pHidden->pParse, pX); } @@ -4381,7 +4400,9 @@ int sqlite3_vtab_rhs_value( rc = SQLITE_MISUSE_BKPT; /* EV: R-30545-25046 */ }else{ if( pH->aRhs[iCons]==0 ){ - WhereTerm *pTerm = &pH->pWC->a[pIdxInfo->aConstraint[iCons].iTermOffset]; + WhereTerm *pTerm = termFromWhereClause( + pH->pWC, pIdxInfo->aConstraint[iCons].iTermOffset + ); rc = sqlite3ValueFromExpr( pH->pParse->db, pTerm->pExpr->pRight, ENC(pH->pParse->db), SQLITE_AFF_BLOB, &pH->aRhs[iCons] @@ -4537,9 +4558,8 @@ static int whereLoopAddVirtual( Bitmask mNext = ALLBITS; assert( mNext>0 ); for(i=0; i<nConstraint; i++){ - Bitmask mThis = ( - pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mPrereq - ); + int iTerm = p->aConstraint[i].iTermOffset; + Bitmask mThis = termFromWhereClause(pWC, iTerm)->prereqRight & ~mPrereq; if( mThis>mPrev && mThis<mNext ) mNext = mThis; } mPrev = mNext; diff --git a/test/bestindexC.test b/test/bestindexC.test index c6ddf3061..a6f420cc2 100644 --- a/test/bestindexC.test +++ b/test/bestindexC.test @@ -210,4 +210,79 @@ do_catchsql_test 4.4 { CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1(insert)"); } {1 {declare_vtab: near "insert": syntax error}} +#------------------------------------------------------------------------- +reset_db +register_tcl_module db + +proc vtab_command {lVal method args} { + switch -- $method { + xConnect { + return "CREATE TABLE t1(a, b, c, d)" + } + + xBestIndex { + set hdl [lindex $args 0] + set clist [$hdl constraints] + + set res [list] + set idx 0 + set idxnum 0 + + set cols(0) a + set cols(1) b + set cols(2) c + + #puts "xBestIndex: $clist" + + foreach c $clist { + array set a $c + if {$a(usable)==0} continue + + if {$a(op)=="eq" || $a(op)=="is"} { + lappend res use $idx + catch { unset cols($a(column)) } + set idxnum [expr {$idx + (1 << $a(column))}] + } + + incr idx + } + + if {[llength [array names cols]]>0} { + set missing [list] + for {set i 0} {$i < 3} {incr i} { + catch { lappend missing $cols($i) } + } + set msg "missing required constraints: [join $missing ,]" + return [list constraint $msg] + } + + return "cost 1000 rows 1000 idxnum $idxnum $res" + } + + xFilter { + return [list sql "SELECT 1, '', '', '' WHERE 0"] + } + } + + return {} +} + +do_execsql_test 5.1 { + CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1); +} + +foreach {tn where ok} { + 1 "WHERE a=? AND b=? AND c=?" 1 + 2 "WHERE a=? AND b=? AND (c=? OR c=?)" 1 + 3 "WHERE a=? AND b=? AND (c=? OR c=? OR c=?)" 1 + 4 "WHERE a=? AND b=? AND (c IS ? OR c IS ?)" 1 + 5 "WHERE a=? AND ((b=? AND c=?) OR (c=? AND b=?))" 1 + 6 "WHERE a=? AND ((b=? AND c=?) OR (c=?))" 0 +} { + do_test 5.2.$tn { + catch { execsql "SELECT * FROM x1 $::where" } + } [expr !$ok] +} + + finish_test |