diff options
| -rw-r--r-- | ext/misc/vtablog.c | 28 | ||||
| -rw-r--r-- | manifest | 21 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/expr.c | 28 | ||||
| -rw-r--r-- | src/whereexpr.c | 20 | ||||
| -rw-r--r-- | test/bestindexC.test | 74 |
6 files changed, 129 insertions, 44 deletions
diff --git a/ext/misc/vtablog.c b/ext/misc/vtablog.c index 8e4631595..e8e29a86e 100644 --- a/ext/misc/vtablog.c +++ b/ext/misc/vtablog.c @@ -485,32 +485,22 @@ static int vtablogBestIndex( printf(" nConstraint: %d\n", p->nConstraint); for(i=0; i<p->nConstraint; i++){ sqlite3_value *pVal = 0; - int rc; - char zRhs[50]; - rc = sqlite3_vtab_rhs_value(p, i, &pVal); - if( rc==SQLITE_OK ){ - switch( sqlite3_value_type(pVal) ){ - case SQLITE_NULL: sqlite3_snprintf(50,zRhs,"NULL"); break; - case SQLITE_INTEGER: sqlite3_snprintf(50,zRhs,"%lld", - sqlite3_value_int64(pVal)); break; - case SQLITE_FLOAT: sqlite3_snprintf(50,zRhs,"%g", - sqlite3_value_double(pVal)); break; - case SQLITE_TEXT: sqlite3_snprintf(50,zRhs,"TEXT"); break; - default: sqlite3_snprintf(50,zRhs,"BLOB"); break; - } - }else{ - sqlite3_snprintf(50,zRhs,"N/A"); - } + int rc = sqlite3_vtab_rhs_value(p, i, &pVal); printf( - " constraint[%d]: col=%d termid=%d op=%s usabled=%d coll=%s rhs=%s\n", + " constraint[%d]: col=%d termid=%d op=%s usabled=%d coll=%s rhs=", i, p->aConstraint[i].iColumn, p->aConstraint[i].iTermOffset, vtablogOpName(p->aConstraint[i].op), p->aConstraint[i].usable, - sqlite3_vtab_collation(p,i), - zRhs + sqlite3_vtab_collation(p,i) ); + if( rc==SQLITE_OK ){ + vtablogQuote(pVal); + printf("\n"); + }else{ + printf("N/A\n"); + } } printf(" nOrderBy: %d\n", p->nOrderBy); for(i=0; i<p->nOrderBy; i++){ @@ -1,5 +1,5 @@ -C Enhancements\sto\sthe\sxBestIndex\soutput\sfrom\sthe\sext/misc/vtablog.c\nextension. -D 2025-07-03T14:10:03.834 +C Fix\sa\sfew\scases\swhere\sLIMIT\sclauses\sthat\swere\spart\sof\sscalar\ssub-queries\son\svirtual\stables\swere\snot\sbeing\spassed\sto\sxBestIndex\smethods\scorrectly. +D 2025-07-03T16:05:41.252 F .fossil-settings/binary-glob 61195414528fb3ea9693577e1980230d78a1f8b0a54c78cf1b9b24d0a409ed6a x F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea @@ -456,7 +456,7 @@ F ext/misc/uuid.c 5bb2264c1b64d163efa46509544fd7500cb8769cb7c16dd52052da8d961505 F ext/misc/vfslog.c 3932ab932eeb2601dbc4447cb14d445aaa9fbe43b863ef5f014401c3420afd20 F ext/misc/vfsstat.c 0b23c0a69a2b63dc0ef0af44f9c1fc977300c480a1f7a9814500369d8211f56e F ext/misc/vfstrace.c 0e4b8b17ac0675ea90f6d168d8214687e06ca3efbc0060aad4814994d82b41fb -F ext/misc/vtablog.c ee27ec6595751db0ce4b8c2a2b72f6eca4d018d9d33e139dbd477e8d173f7b0e +F ext/misc/vtablog.c 62d54fb0f4ff9df72ffa44f4a226451a964e1d96ac550f2b8906feebdad00b58 F ext/misc/vtshim.c e5bce24ab8c532f4fdc600148718fe1802cb6ed57417f1c1032d8961f72b0e8f F ext/misc/wholenumber.c 0fa0c082676b7868bf2fa918e911133f2b349bcdceabd1198bba5f65b4fc0668 F ext/misc/windirent.h 02211ce51f3034c675f2dbf4d228194d51b3ee05734678bad5106fff6292e60c @@ -736,7 +736,7 @@ F src/date.c 9db4d604e699a73e10b8e85a44db074a1f04c0591a77e2abfd77703f50dce1e9 F src/dbpage.c b3e218f8ed74fcbb7fa805df8ca669a3718d397617b3d8a8aac3307dc315c4d6 F src/dbstat.c 73362c0df0f40ad5523a6f5501224959d0976757b511299bf892313e79d14f5c F src/delete.c 03a77ba20e54f0f42ebd8eddf15411ed6bdb06a2c472ac4b6b336521bf7cea42 -F src/expr.c e3d3b6e60b86fac772bd4d4f0c6fe1d01bef3851754a9c1215d2f8574296cbd2 +F src/expr.c 3f460c3d98b33b57e7c4665b05542f1eff62f9a524108df6d47d2ffe19473dcf F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007 F src/fkey.c 928ed2517e8732113d2b9821aa37af639688d752f4ea9ac6e0e393d713eeb76f F src/func.c de47a8295503aa130baae5e6d9868ecf4f7c4dbffa65d83ad1f70bdbac0ee2d6 @@ -870,7 +870,7 @@ F src/walker.c d5006d6b005e4ea7302ad390957a8d41ed83faa177e412f89bc5600a7462a014 F src/where.c f58d41d0923eeb21cab8e4fc87a0b36c0724ff4f279ce95ab2731b4696b8e75a F src/whereInt.h 8d94cb116c9e06205c3d5ac87af065fc044f8cf08bfdccd94b6ea1c1308e65da F src/wherecode.c 504f3c1270c3ffd51ebcdf7a31de08aa51a63b33a2ccdf8f5736afe3dfa73d45 -F src/whereexpr.c 566ca4382e07a4ba1fd86c97ae0781cdf84004c7d9c59466bf5db75733548807 +F src/whereexpr.c c3ff4d8f1ae5cb9fb41460f9d960b1f519b6115585375790c53833e5642fc1f4 F src/window.c d01227141f622f24fbe36ca105fbe6ef023f9fd98f1ccd65da95f88886565db5 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/affinity2.test 4d7a34d328e58ca2a2d78fd76c27614a41ca7ddf4312ded9c68c04f430b3b47d @@ -962,7 +962,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 2df6ada16d8f00d9bb6a9664d9c323560aeed0e0ebc7a32b99d85d70037fd250 +F test/bestindexC.test 95b4a527b1a5d07951d731604a6d4cf7e5a806b39cea0e7819d4c9667e11c3fc F test/bestindexD.test 6a8f6f84990bcf17dfa59652a1f935beddb7afd96f8302830fbc86b0a13df3c3 F test/between.test b9a65fb065391980119e8a781a7409d3fcf059d89968279c750e190a9a1d5263 F test/bigfile.test aa74f4e5db51c8e54a1d9de9fa65d01d1eb20b59 @@ -2208,8 +2208,9 @@ F tool/version-info.c 3b36468a90faf1bbd59c65fd0eb66522d9f941eedd364fabccd7227350 F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee87c1b31a7 F tool/warnings.sh 1ad0169b022b280bcaaf94a7fa231591be96b514230ab5c98fbf15cd7df842dd F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 6db4703f1178fc808f3a75c355fb6638fb12c88f6e1ce7f579e200ced8089114 -R 50c4d2dc6029c61dab20b65ad02b720b -U drh -Z 46c80e4b7ddf6a82d5b12bfb66b8a7fe +P 8b31acc0b18f38eb4af6efebd1ea25cd65a146651101579aee20afd9ec6dc2de 25131ee84f53dab1191e02c19cefd256aed2828c7edea325fcc0ba3b0a668583 +R 03da53ec2c5bbfaf5954c2ff4dc81144 +T +closed 25131ee84f53dab1191e02c19cefd256aed2828c7edea325fcc0ba3b0a668583 +U dan +Z 169d778787b3864e08d84b0f7a4d552a # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 24b6ee8c9..5e9ea7fa4 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -8b31acc0b18f38eb4af6efebd1ea25cd65a146651101579aee20afd9ec6dc2de +960a8e6fc91f47add3a089dc6cef013109deadf809994c5149ad3bdfb3884de0 diff --git a/src/expr.c b/src/expr.c index c8dfd3af3..ebac654a2 100644 --- a/src/expr.c +++ b/src/expr.c @@ -3896,17 +3896,23 @@ int sqlite3CodeSubselect(Parse *pParse, Expr *pExpr){ VdbeComment((v, "Init EXISTS result")); } if( pSel->pLimit ){ - /* The subquery already has a limit. If the pre-existing limit is X - ** then make the new limit X<>0 so that the new limit is either 1 or 0 */ - sqlite3 *db = pParse->db; - pLimit = sqlite3Expr(db, TK_INTEGER, "0"); - if( pLimit ){ - pLimit->affExpr = SQLITE_AFF_NUMERIC; - pLimit = sqlite3PExpr(pParse, TK_NE, - sqlite3ExprDup(db, pSel->pLimit->pLeft, 0), pLimit); - } - sqlite3ExprDeferredDelete(pParse, pSel->pLimit->pLeft); - pSel->pLimit->pLeft = pLimit; + /* The subquery already has a limit. If the pre-existing limit X is + ** not already integer value 1 or 0, then make the new limit X<>0 so that + ** the new limit is either 1 or 0 */ + Expr *pLeft = pSel->pLimit->pLeft; + if( ExprHasProperty(pLeft, EP_IntValue)==0 + || (pLeft->u.iValue!=1 && pLeft->u.iValue!=0) + ){ + sqlite3 *db = pParse->db; + pLimit = sqlite3Expr(db, TK_INTEGER, "0"); + if( pLimit ){ + pLimit->affExpr = SQLITE_AFF_NUMERIC; + pLimit = sqlite3PExpr(pParse, TK_NE, + sqlite3ExprDup(db, pLeft, 0), pLimit); + } + sqlite3ExprDeferredDelete(pParse, pLeft); + pSel->pLimit->pLeft = pLimit; + } }else{ /* If there is no pre-existing limit add a limit of 1 */ pLimit = sqlite3Expr(pParse->db, TK_INTEGER, "1"); diff --git a/src/whereexpr.c b/src/whereexpr.c index 53c8508e5..fc7a30a42 100644 --- a/src/whereexpr.c +++ b/src/whereexpr.c @@ -1652,7 +1652,7 @@ static void whereAddLimitExpr( ** ** 1. The SELECT statement has a LIMIT clause, and ** 2. The SELECT statement is not an aggregate or DISTINCT query, and -** 3. The SELECT statement has exactly one object in its from clause, and +** 3. The SELECT statement has exactly one object in its FROM clause, and ** that object is a virtual table, and ** 4. There are no terms in the WHERE clause that will not be passed ** to the virtual table xBestIndex method. @@ -1689,8 +1689,22 @@ void SQLITE_NOINLINE sqlite3WhereAddLimit(WhereClause *pWC, Select *p){ ** (leftCursor==iCsr) test below. */ continue; } - if( pWC->a[ii].leftCursor!=iCsr ) return; - if( pWC->a[ii].prereqRight!=0 ) return; + if( pWC->a[ii].leftCursor==iCsr && pWC->a[ii].prereqRight==0 ) continue; + + /* If this term has a parent with exactly one child, and the parent will + ** be passed through to xBestIndex, then this term can be ignored. */ + if( pWC->a[ii].iParent>=0 ){ + WhereTerm *pParent = &pWC->a[ pWC->a[ii].iParent ]; + if( pParent->leftCursor==iCsr + && pParent->prereqRight==0 + && ALWAYS(pParent->nChild==1) + ){ + continue; + } + } + + /* This term will not be passed through. Do not add a LIMIT clause. */ + return; } /* Check condition (5). Return early if it is not met. */ diff --git a/test/bestindexC.test b/test/bestindexC.test index 48f3a2765..8b96a19e6 100644 --- a/test/bestindexC.test +++ b/test/bestindexC.test @@ -349,4 +349,78 @@ do_execsql_test 5.9 { three six seven } +#-------------------------------------------------------------------------- + +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 idx 0 + set idxnum 0 + + foreach c $clist { + array set a $c + if {$a(usable)==0} continue + + if {$a(op)=="limit"} { + set idxnum [$hdl rhs_value $idx 555] + } + + incr idx + } + + return "cost 1000 rows 1000 idxnum $idxnum" + + } + + xFilter { + foreach {idxnum idxstr lArg} $args {} + return [list sql "SELECT 0, $idxnum, $idxnum, $idxnum, $idxnum"] + } + } + + return {} +} + +do_execsql_test 6.0 { + CREATE TABLE t1(x, y); + INSERT INTO t1 VALUES(2, 2); + CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1); +} + +do_execsql_test 6.1 { SELECT * FROM x1 LIMIT 50 } {50 50 50 50} + +do_execsql_test 6.2 { SELECT * FROM x1 WHERE b=c LIMIT 5 } {0 0 0 0} + +do_execsql_test 6.3 { + SELECT (SELECT a FROM x1 WHERE t1.x=t1.y LIMIT 10) FROM t1 +} {0} + +do_execsql_test 6.4 { + SELECT (SELECT a FROM x1 WHERE x1.a=1) FROM t1 +} {1} + +do_execsql_test 6.5 { + SELECT (SELECT a FROM x1 WHERE x1.a=1 LIMIT 1) FROM t1 +} {1} + +do_execsql_test 6.6 { + SELECT (SELECT a FROM x1 WHERE x1.a=555 LIMIT 2) FROM t1 +} {555} + finish_test + + |