aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--ext/fts5/fts5_main.c34
-rw-r--r--ext/fts5/test/fts5colset.test2
-rw-r--r--ext/fts5/test/fts5misc.test31
-rw-r--r--manifest23
-rw-r--r--manifest.uuid2
-rw-r--r--src/test_bestindex.c24
-rw-r--r--src/where.c178
-rw-r--r--test/bestindexC.test139
8 files changed, 323 insertions, 110 deletions
diff --git a/ext/fts5/fts5_main.c b/ext/fts5/fts5_main.c
index eca6db572..0a272ccca 100644
--- a/ext/fts5/fts5_main.c
+++ b/ext/fts5/fts5_main.c
@@ -533,10 +533,10 @@ static int fts5UsePatternMatch(
** This function ensures that there is at most one "r" or "=". And that if
** there exists an "=" then there is no "<" or ">".
**
-** Costs are assigned as follows:
+** If an unusable MATCH operator is present in the WHERE clause, then
+** SQLITE_CONSTRAINT is returned.
**
-** a) If an unusable MATCH operator is present in the WHERE clause, the
-** cost is unconditionally set to 1e50 (a really big number).
+** Costs are assigned as follows:
**
** a) If a MATCH operator is present, the cost depends on the other
** constraints also present. As follows:
@@ -569,7 +569,7 @@ static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
int bSeenEq = 0;
int bSeenGt = 0;
int bSeenLt = 0;
- int bSeenMatch = 0;
+ int nSeenMatch = 0;
int bSeenRank = 0;
@@ -600,18 +600,15 @@ static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
/* A MATCH operator or equivalent */
if( p->usable==0 || iCol<0 ){
/* As there exists an unusable MATCH constraint this is an
- ** unusable plan. Set a prohibitively high cost. */
- pInfo->estimatedCost = 1e50;
- assert( iIdxStr < pInfo->nConstraint*6 + 1 );
- idxStr[iIdxStr] = 0;
- return SQLITE_OK;
+ ** unusable plan. Return SQLITE_CONSTRAINT. */
+ return SQLITE_CONSTRAINT;
}else{
if( iCol==nCol+1 ){
if( bSeenRank ) continue;
idxStr[iIdxStr++] = 'r';
bSeenRank = 1;
}else if( iCol>=0 ){
- bSeenMatch = 1;
+ nSeenMatch++;
idxStr[iIdxStr++] = 'M';
sqlite3_snprintf(6, &idxStr[iIdxStr], "%d", iCol);
idxStr += strlen(&idxStr[iIdxStr]);
@@ -628,7 +625,7 @@ static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
idxStr += strlen(&idxStr[iIdxStr]);
pInfo->aConstraintUsage[i].argvIndex = ++iCons;
assert( idxStr[iIdxStr]=='\0' );
- bSeenMatch = 1;
+ nSeenMatch++;
}else if( bSeenEq==0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ && iCol<0 ){
idxStr[iIdxStr++] = '=';
bSeenEq = 1;
@@ -665,7 +662,7 @@ static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
*/
if( pInfo->nOrderBy==1 ){
int iSort = pInfo->aOrderBy[0].iColumn;
- if( iSort==(pConfig->nCol+1) && bSeenMatch ){
+ if( iSort==(pConfig->nCol+1) && nSeenMatch>0 ){
idxFlags |= FTS5_BI_ORDER_RANK;
}else if( iSort==-1 && (!pInfo->aOrderBy[0].desc || !pConfig->bTokendata) ){
idxFlags |= FTS5_BI_ORDER_ROWID;
@@ -680,14 +677,17 @@ static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
/* Calculate the estimated cost based on the flags set in idxFlags. */
if( bSeenEq ){
- pInfo->estimatedCost = bSeenMatch ? 100.0 : 10.0;
- if( bSeenMatch==0 ) fts5SetUniqueFlag(pInfo);
+ pInfo->estimatedCost = nSeenMatch ? 1000.0 : 10.0;
+ if( nSeenMatch==0 ) fts5SetUniqueFlag(pInfo);
}else if( bSeenLt && bSeenGt ){
- pInfo->estimatedCost = bSeenMatch ? 500.0 : 250000.0;
+ pInfo->estimatedCost = nSeenMatch ? 5000.0 : 250000.0;
}else if( bSeenLt || bSeenGt ){
- pInfo->estimatedCost = bSeenMatch ? 750.0 : 750000.0;
+ pInfo->estimatedCost = nSeenMatch ? 7500.0 : 750000.0;
}else{
- pInfo->estimatedCost = bSeenMatch ? 1000.0 : 1000000.0;
+ pInfo->estimatedCost = nSeenMatch ? 10000.0 : 1000000.0;
+ }
+ for(i=1; i<nSeenMatch; i++){
+ pInfo->estimatedCost *= 0.4;
}
pInfo->idxNum = idxFlags;
diff --git a/ext/fts5/test/fts5colset.test b/ext/fts5/test/fts5colset.test
index 7243743b5..e5429572c 100644
--- a/ext/fts5/test/fts5colset.test
+++ b/ext/fts5/test/fts5colset.test
@@ -79,7 +79,7 @@ foreach_detail_mode $::testprefix {
do_catchsql_test 4.1 {
SELECT * FROM t1 WHERE rowid MATCH 'a'
- } {1 {unable to use function MATCH in the requested context}}
+ } {1 {no query solution}}
}
#-------------------------------------------------------------------------
diff --git a/ext/fts5/test/fts5misc.test b/ext/fts5/test/fts5misc.test
index 4e4fe4ba2..e4903d067 100644
--- a/ext/fts5/test/fts5misc.test
+++ b/ext/fts5/test/fts5misc.test
@@ -535,5 +535,36 @@ do_execsql_test 19.0 {
COMMIT;
}
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 20.0 {
+ CREATE VIRTUAL TABLE x1 USING fts5(a);
+ INSERT INTO x1(rowid, a) VALUES
+ (1, 'a b c d'),
+ (2, 'x b c d'),
+ (3, 'x y z d'),
+ (4, 'a y c x');
+}
+
+do_execsql_test 20.1 {
+ SELECT rowid FROM x1 WHERE x1 MATCH 'a' AND x1 MATCH 'b';
+} {1}
+
+do_execsql_test 20.2 {
+ SELECT rowid FROM x1 WHERE x1 MATCH 'a' AND x1 MATCH 'y';
+} {4}
+
+do_execsql_test 20.3 {
+ SELECT rowid FROM x1 WHERE x1 MATCH 'a' OR x1 MATCH 'y';
+} {1 4 3}
+
+do_execsql_test 20.4 {
+ SELECT rowid FROM x1 WHERE x1 MATCH 'a' OR (x1 MATCH 'y' AND x1 MATCH 'd');
+} {1 4 3}
+
+do_execsql_test 20.5 {
+ SELECT rowid FROM x1 WHERE x1 MATCH 'z' OR (x1 MATCH 'a' AND x1 MATCH 'd');
+} {3 1}
+
finish_test
diff --git a/manifest b/manifest
index 05ecb1a2f..54e6ae721 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\sa\scouple\sof\smemory\sleaks\sin\sthe\sshell\stool\scode\sthat\scould\soccur\swhen\sprocessing\serrors.
-D 2024-06-04T15:07:38.217
+C Better\shandle\sWHERE\sterms\sthat\sare\scommon\sto\stwo\sor\smore\sOR\sbranches\swhen\splanning\svirtual\stable\squeries.
+D 2024-06-04T17:26:15.006
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -100,7 +100,7 @@ F ext/fts5/fts5_config.c 1ae512e7374caca6ab250055b3b29e46b007a27b098eafcc7ff4d13
F ext/fts5/fts5_expr.c 85789f6fb01995f2578b60a360057ed754335a890b1ab2e57e238b3670a9ae6c
F ext/fts5/fts5_hash.c adda4272be401566a6e0ba1acbe70ee5cb97fce944bc2e04dc707152a0ec91b1
F ext/fts5/fts5_index.c ee0f4d50bc0c58a7c5ef7d645e7e38e1e59315b8ea9d722ae00c5f949ee65379
-F ext/fts5/fts5_main.c ac3aaf0c885cf4e274c0c09ece632e17ff2fce01f2c9c0f4c50ffbbb3e267bde
+F ext/fts5/fts5_main.c b1538b39182be8f2b6b1807c1ad426e0e5710e3ee2dcaeb7d0ae2b751103c237
F ext/fts5/fts5_storage.c f9e31b0d155e9b2c92d5d3a09ad7a56b937fbf1c7f962e10f4ca6281349f3934
F ext/fts5/fts5_tcl.c fdf7e2bb9a9186cfcaf2d2ce11d338309342b7a7593c2812bc54455db53da5d2
F ext/fts5/fts5_test_mi.c 08c11ec968148d4cb4119d96d819f8c1f329812c568bac3684f5464be177d3ee
@@ -133,7 +133,7 @@ F ext/fts5/test/fts5bigpl.test 6466c89b38439f0aba26ac09e232a6b963f29b1cbe1304f6a
F ext/fts5/test/fts5bigtok.test 541119e616c637caea925a8c028c37c2c29e94383e00aa2f9198d530724b6e36
F ext/fts5/test/fts5cat.test daba0b80659460b0cb60bd1f40b402478a761fe7ea414c3c94c2be25568cc33a
F ext/fts5/test/fts5circref.test f880dfd0d99f6fb73b88ccacb0927d18e833672fd906cc47d6b4e529419eaa62
-F ext/fts5/test/fts5colset.test 7031ce84fb4d312df5a99fc4e7b324e660ccb513c97eccdef469bfd52d3d0f8f
+F ext/fts5/test/fts5colset.test 544f4998cdbfe06a3123887fc0221612e8aa8192cdaff152872f1aadb10e6897
F ext/fts5/test/fts5columnsize.test 45459ce4dd9fd853b6044cdc9674921bff89e3d840f348ca8c1630f9edbf5482
F ext/fts5/test/fts5config.test 60094712debc59286c59aef0e6cf511c37d866802776a825ce437d26afe0817f
F ext/fts5/test/fts5conflict.test bf6030a77dbb1bedfcc42e589ed7980846c995765d77460551e448b56d741244
@@ -186,7 +186,7 @@ F ext/fts5/test/fts5limits.test 8ab67cf5d311c124b6ceb0062d0297767176df4572d955fc
F ext/fts5/test/fts5matchinfo.test 10c9a6f7fe61fb132299c4183c012770b10c4d5c2f2edb6df0b6607f683d737a
F ext/fts5/test/fts5merge.test e92a8db28b45931e7a9c7b1bbd36101692759d00274df74d83fd29d25d53b3a6
F ext/fts5/test/fts5merge2.test 3ebad1a59d6ad3fb66eff6523a09e95dc6367cbefb3cd73196801dea0425c8e2
-F ext/fts5/test/fts5misc.test 89dc46e37951b7f6653809f4abf6b1ca2f1fa62259efaf719339288f76fb6be9
+F ext/fts5/test/fts5misc.test b88aa28ff20238b394495b0795cddca1a62b98fa09b99e462a8abc572d04ee88
F ext/fts5/test/fts5multi.test a15bc91cdb717492e6e1b66fec1c356cb57386b980c7ba5af1915f97fe878581
F ext/fts5/test/fts5multiclient.test 5ff811c028d6108045ffef737f1e9f05028af2458e456c0937c1d1b8dea56d45
F ext/fts5/test/fts5near.test 211477940142d733ac04fad97cb24095513ab2507073a99c2765c3ddd2ef58bd
@@ -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 1b5a1407b66c5caa67cfe1d93d96de5ec5d9d516bc69eb512482f85c037858c3
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 593d28d3776985733da0f09639100003b444a43db7b6f9a35139f544fdbea358
+F src/where.c 343e74d65856665f2aac59a9fcefecfc988e9af4aafa0bd1b8332a89c6c725b4
F src/whereInt.h 002adc3aa2cc10733b9b27958fdbe893987cd989fab25a9853941c1f9b9b0a65
F src/wherecode.c 5ad509221ebb4d3b35a8a6ef361f2c5b54129b8c273aa434dd3053d2e25d1794
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 2df6ada16d8f00d9bb6a9664d9c323560aeed0e0ebc7a32b99d85d70037fd250
F test/between.test b9a65fb065391980119e8a781a7409d3fcf059d89968279c750e190a9a1d5263
F test/bigfile.test aa74f4e5db51c8e54a1d9de9fa65d01d1eb20b59
F test/bigfile2.test 1b489a3a39ae90c7f027b79110d6b4e1dbc71bfc
@@ -2194,8 +2194,9 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P afa45c4f5afc9248ca4a1e775404a460280bb9a58a92eae508ae00fb2f675dc6
-R 332c194faf67e4e5d499e21ef0877039
+P e84f09d469ee76a5b5e44baf6a69b90c69d4160fa4c32de04a96f868643acd96 85dcd0a8479a658203833cfd75f22813faa26d4793ebfbb8843035d683bee105
+R ef252a4e30665afc837737e2dd6e6acf
+T +closed 85dcd0a8479a658203833cfd75f22813faa26d4793ebfbb8843035d683bee105
U dan
-Z 25f8afeca77d9ed28a678ff29d2afdb8
+Z 63031e840e155842bdee98d1a8498c9b
# Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index b9aace032..1764cb4c8 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-e84f09d469ee76a5b5e44baf6a69b90c69d4160fa4c32de04a96f868643acd96 \ No newline at end of file
+1976c3f7e1fe77cf3367710e8ada230a3672ed374e316425164e42b2622526c7 \ No newline at end of file
diff --git a/src/test_bestindex.c b/src/test_bestindex.c
index 0e1e86a81..c89cab2e3 100644
--- a/src/test_bestindex.c
+++ b/src/test_bestindex.c
@@ -305,11 +305,9 @@ static int tclFilter(
Tcl_IncrRefCount(pScript);
Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xFilter", -1));
Tcl_ListObjAppendElement(interp, pScript, Tcl_NewIntObj(idxNum));
- if( idxStr ){
- Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj(idxStr, -1));
- }else{
- Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("", -1));
- }
+ Tcl_ListObjAppendElement(
+ interp, pScript, Tcl_NewStringObj(idxStr ? idxStr : "", -1)
+ );
pArg = Tcl_NewObj();
Tcl_IncrRefCount(pArg);
@@ -530,6 +528,7 @@ static int SQLITE_TCLAPI testBestIndexObj(
"distinct", /* 3 */
"in", /* 4 */
"rhs_value", /* 5 */
+ "collation", /* 6 */
0
};
int ii;
@@ -610,6 +609,17 @@ static int SQLITE_TCLAPI testBestIndexObj(
Tcl_SetObjResult(interp, Tcl_NewStringObj(zVal, -1));
break;
}
+
+ case 6: assert( sqlite3_stricmp(azSub[ii], "collation")==0 ); {
+ int iCons = 0;
+ const char *zColl = "";
+ if( Tcl_GetIntFromObj(interp, objv[2], &iCons) ){
+ return TCL_ERROR;
+ }
+ zColl = sqlite3_vtab_collation(pIdxInfo, iCons);
+ Tcl_SetObjResult(interp, Tcl_NewStringObj(zColl, -1));
+ break;
+ }
}
return TCL_OK;
@@ -700,6 +710,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 2418b4156..71a8241e3 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;
@@ -1545,6 +1565,17 @@ static sqlite3_index_info *allocateIndexInfo(
}
/*
+** Free and zero the sqlite3_index_info.idxStr value if needed.
+*/
+static void freeIdxStr(sqlite3_index_info *pIdxInfo){
+ if( pIdxInfo->needToFreeIdxStr ){
+ sqlite3_free(pIdxInfo->idxStr);
+ pIdxInfo->idxStr = 0;
+ pIdxInfo->needToFreeIdxStr = 0;
+ }
+}
+
+/*
** Free an sqlite3_index_info structure allocated by allocateIndexInfo()
** and possibly modified by xBestIndex methods.
*/
@@ -1559,6 +1590,7 @@ static void freeIndexInfo(sqlite3 *db, sqlite3_index_info *pIdxInfo){
sqlite3ValueFree(pHidden->aRhs[i]); /* IMP: R-14553-25174 */
pHidden->aRhs[i] = 0;
}
+ freeIdxStr(pIdxInfo);
sqlite3DbFree(db, pIdxInfo);
}
@@ -4159,7 +4191,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
@@ -4190,6 +4222,7 @@ static int whereLoopAddVirtualOne(
** Make no entries in the loop table.
*/
WHERETRACE(0xffffffff, (" ^^^^--- non-viable plan rejected!\n"));
+ freeIdxStr(pIdxInfo);
return SQLITE_OK;
}
return rc;
@@ -4207,18 +4240,17 @@ 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
){
sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pTab->zName);
- testcase( pIdxInfo->needToFreeIdxStr );
+ freeIdxStr(pIdxInfo);
return SQLITE_ERROR;
}
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;
@@ -4263,11 +4295,7 @@ static int whereLoopAddVirtualOne(
** the plan cannot be used. In these cases set variable *pbRetryLimit
** to true to tell the caller to retry with LIMIT and OFFSET
** disabled. */
- if( pIdxInfo->needToFreeIdxStr ){
- sqlite3_free(pIdxInfo->idxStr);
- pIdxInfo->idxStr = 0;
- pIdxInfo->needToFreeIdxStr = 0;
- }
+ freeIdxStr(pIdxInfo);
*pbRetryLimit = 1;
return SQLITE_OK;
}
@@ -4280,7 +4308,7 @@ static int whereLoopAddVirtualOne(
/* The non-zero argvIdx values must be contiguous. Raise an
** error if they are not */
sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pTab->zName);
- testcase( pIdxInfo->needToFreeIdxStr );
+ freeIdxStr(pIdxInfo);
return SQLITE_ERROR;
}
}
@@ -4335,7 +4363,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 +4409,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 +4567,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;
@@ -4575,7 +4604,6 @@ static int whereLoopAddVirtual(
}
}
- if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr);
freeIndexInfo(pParse->db, p);
WHERETRACE(0x800, ("END %s.addVirtual(), rc=%d\n", pSrc->pTab->zName, rc));
return rc;
diff --git a/test/bestindexC.test b/test/bestindexC.test
index c6ddf3061..48f3a2765 100644
--- a/test/bestindexC.test
+++ b/test/bestindexC.test
@@ -210,4 +210,143 @@ 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 quote {str} {
+ return "'[string map {' ''} $str]'"
+}
+
+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
+
+ set lCons [list]
+
+ foreach c $clist {
+ array set a $c
+ if {$a(usable)==0} continue
+
+ if {($a(op)=="eq" || $a(op)=="is") && [info exists cols($a(column))]} {
+ lappend res omit $idx
+ set coll [$hdl collation $idx]
+ lappend lCons "$cols($a(column)) = %[llength $lCons]% COLLATE $coll"
+
+ set idxnum [expr {$idx + (1 << $a(column))}]
+ catch { unset cols($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]
+ }
+
+ set idxstr [join $lCons " AND "]
+ return "cost 1000 rows 1000 idxnum $idxnum $res idxstr {$idxstr}"
+ }
+
+ xFilter {
+ foreach {idxnum idxstr lArg} $args {}
+ set i 0
+ set where $idxstr
+ foreach a $lArg {
+ set where [string map [list %$i% [quote $a]] $where]
+ incr i
+ }
+ # puts $where
+ return [list sql "SELECT rowid, * FROM $lVal WHERE $where"]
+ }
+ }
+
+ return {}
+}
+
+do_execsql_test 5.1 {
+ CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1);
+ CREATE TABLE t1(a, b, c, d);
+}
+
+foreach {tn where ok} {
+ 0 "WHERE a=? AND b=? AND c=? AND c=?" 1
+ 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" } msg
+# if {$tn==0 || $tn==2 || $tn==3} { puts "MSG: $msg" }
+ } [expr !$ok]
+}
+
+do_execsql_test 5.3 {
+ SELECT * FROM x1 WHERE (a, b, c) = (?, ?, ?);
+}
+
+do_execsql_test 5.4 {
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(1, 'x', 'y', 'z', 'one');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(2, 'X', 'Y', 'Z', 'two');
+ SELECT * FROM x1 WHERE (a, b, c) = ('X', 'Y', 'Z');
+} {X Y Z two}
+do_execsql_test 5.5 {
+ SELECT * FROM x1 WHERE a='x' AND b='y' AND c='z';
+} {x y z one}
+do_execsql_test 5.6 {
+ SELECT * FROM x1
+ WHERE a='x' COLLATE nocase AND b='y' COLLATE nocase AND c='z'COLLATE nocase;
+} {x y z one X Y Z two}
+
+do_execsql_test 5.7 {
+ DELETE FROM t1;
+
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(0, 'x', 'y', 'z', 'zero');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(1, 'x', 'y', 'Z', 'one');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(2, 'x', 'Y', 'z', 'two');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(3, 'x', 'Y', 'Z', 'three');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(4, 'X', 'y', 'z', 'four');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(5, 'X', 'y', 'Z', 'five');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(6, 'X', 'Y', 'z', 'six');
+ INSERT INTO t1(rowid, a, b, c, d) VALUES(7, 'X', 'Y', 'z', 'seven');
+}
+
+do_execsql_test 5.8 {
+ SELECT d FROM x1
+ WHERE a='x' AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))
+} {
+ zero two three
+}
+
+do_execsql_test 5.9 {
+ SELECT d FROM x1
+ WHERE a='x' COLLATE nocase
+ AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))
+} {
+ zero four two
+ three six seven
+}
+
finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 17:39:56 +0000