aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--manifest35
-rw-r--r--manifest.uuid2
-rw-r--r--src/expr.c30
-rw-r--r--src/resolve.c6
-rw-r--r--src/select.c4
-rw-r--r--src/shell.c.in2
-rw-r--r--src/sqliteInt.h2
-rw-r--r--src/where.c2
-rw-r--r--src/whereexpr.c2
-rw-r--r--src/window.c2
-rw-r--r--test/wherelimit2.test31
-rw-r--r--test/wherelimit3.test67
12 files changed, 156 insertions, 29 deletions
diff --git a/manifest b/manifest
index 6f0e3724b..994b8d39f 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Re-enable\s[dcc2bb2c562e97e090174],\sas\s[296eeb26c816bc73]\scorrects\sthe\swasi-sdk\sbuild\sproblem\sthe\sformer\scheck-in\striggered.\sReported\sin\s[forum:143e40d7f4e79c66|forum\spost\s143e40d7f4e79c66].
-D 2024-06-06T12:22:19.122
+C Better\soptimize\squeries\sthat\suse\sparameters\sin\sthe\sLIMIT\sclause.
+D 2024-06-06T15:03:16.778
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -705,7 +705,7 @@ F src/date.c 126ba2ab10aeb2e7ba6e089b5f07b747c0625b8287f78b60da346eda8d23c875
F src/dbpage.c 80e46e1df623ec40486da7a5086cb723b0275a6e2a7b01d9f9b5da0f04ba2782
F src/dbstat.c 3b677254d512fcafd4d0b341bf267b38b235ccfddbef24f9154e19360fa22e43
F src/delete.c cb766727c78e715f9fb7ec8a7d03658ed2a3016343ca687acfcec9083cdca500
-F src/expr.c 585109ab97149b2d484697e7469f28c91d495cd1330cc760d24711b7be8d22fb
+F src/expr.c af9c9242be0df17280faf36c9810339de9df3d7a64ac8d33a5190a1400086ee5
F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
F src/fkey.c 852f93c0ef995e0c2b8983059a2b97151c194cc8259e21f5bc2b7ac508348c2a
F src/func.c f1f57c6863c1380f31ecf3d61732495bfff847a8e35a832c7e306e310db5a799
@@ -753,14 +753,14 @@ F src/pragma.h e690a356c18e98414d2e870ea791c1be1545a714ba623719deb63f7f226d8bb7
F src/prepare.c d99931f45416652895e502328ca49fe782cfc4e1ebdcda13b3736d991ebf42ce
F src/printf.c 8b250972305e14b365561be5117ed0fd364e4fd58968776df1ce64c6280b90f9
F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c
-F src/resolve.c ae341d04d39de308e024649d6777dd015b6a94ddbfbe74d7fca2ee122fb1aea9
+F src/resolve.c 7e8d23ce7cdbfedf351a47e759f2722e8182ca10fd7580be43f4ce1f1a228145
F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97
-F src/select.c 3b818d1b476c7108fdbbf6853c92fe4a933940474c77546f812e14f8aa2be03d
-F src/shell.c.in 134db38064e038b6d3aec968a4a3eb302141b043808922961e34b333de1cff21
+F src/select.c ea0b75fce45e1f2c22f50ed2b6e2ddd7f66640948d0fc79a397917b4236a74af
+F src/shell.c.in 77d12a0dab8724819e64a14d5fbaad91a934be2b22ad329708fba9ba78993f04
F src/sqlite.h.in cbd3e4177791a61c056fd81e37a5b21bb6c8cb2ea8cac558c625974673f50acf
F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
F src/sqlite3ext.h 3f046c04ea3595d6bfda99b781926b17e672fd6d27da2ba6d8d8fc39981dcb54
-F src/sqliteInt.h d66a1eab63dea318ad3f56df2b4d8c70ee9143aff25fbb1423c8234d6cf37787
+F src/sqliteInt.h 0fec1cf17d9e4c93baf29bf74b7d03de9425299443d4abd6989a004d6eb53b60
F src/sqliteLimit.h 6878ab64bdeb8c24a1d762d45635e34b96da21132179023338c93f820eee6728
F src/status.c cb11f8589a6912af2da3bb1ec509a94dd8ef27df4d4c1a97e0bcf2309ece972b
F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1
@@ -840,11 +840,11 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9
F src/wal.c 887fc4ca3f020ebb2e376f222069570834ac63bf50111ef0cbf3ae417048ed89
F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452
F src/walker.c 7c7ea0115345851c3da4e04e2e239a29983b61fb5b038b94eede6aba462640e2
-F src/where.c 343e74d65856665f2aac59a9fcefecfc988e9af4aafa0bd1b8332a89c6c725b4
+F src/where.c c93a38b97ea1a953500c383040228d3b2a89fcf69afd0be2365e01bdbc0699bd
F src/whereInt.h 002adc3aa2cc10733b9b27958fdbe893987cd989fab25a9853941c1f9b9b0a65
F src/wherecode.c c9cac0b0b8e809c5e7e79d7796918907fb685ad99be2aaa9737f9787aa47349c
-F src/whereexpr.c 66179e471119281a9073f836371ba3126de8d88f4c6bb71dec977058cc18d401
-F src/window.c 5d95122dd330bfaebd732358c8ef067c5a9394a53ac249470d611d0ce2c52be2
+F src/whereexpr.c 7d0d34b42b9edfd8e8ca66beb3a6ef63fe211c001af54caf2ccbcd989b783290
+F src/window.c 1e40ffc509bae21e466f6106382d238e91eb73edd4ba10e66ca4fd7af2b96896
F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2
F test/affinity2.test ce1aafc86e110685b324e9a763eab4f2a73f737842ec3b687bd965867de90627
F test/affinity3.test f094773025eddf31135c7ad4cde722b7696f8eb07b97511f98585addf2a510a9
@@ -2031,7 +2031,8 @@ F test/whereN.test 63a3584b71acfb6963416de82f26c6b1644abc5ca6080c76546b9246734c8
F test/wherefault.test 6cf2a9c5712952d463d3f45ebee7f6caf400984df51a195d884cfb7eb0e837a7
F test/wherelfault.test 9012e4ef5259058b771606616bd007af5d154e64cc25fa9fd4170f6411db44e3
F test/wherelimit.test afb46397c6d7e964e6e294ba3569864a0c570fe3807afc634236c2b752372f31
-F test/wherelimit2.test b9e4bfe7b4d7c2f85f99cf2bd2c51369378d04b1f3d1b60557423752003bfd90
+F test/wherelimit2.test 45051faca87204e00d999a3282eeea4263d7a7eb7a063c2fb9096e1ef359e9c5
+F test/wherelimit3.test 22d73e046870cf8bbe15573eda6b432b07ebe64a88711f9f849c6b3667c1fae6
F test/widetab1.test c296a98e123762de79917350e45fa33fdf88577a2571eb3a64c8bf7e44ef74d1
F test/win32heap.test 10fd891266bd00af68671e702317726375e5407561d859be1aa04696f2aeee74
F test/win32lock.test e0924eb8daac02bf80e9da88930747bd44dd9b230b7759fed927b1655b467c9c
@@ -2194,9 +2195,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 34f05c3d89b2dd15e4b0d1ba292df7de3dfc54b505c0ba145cc3db52cf020845
-Q +dcc2bb2c562e97e090174d4d0970bfa1551e5eb4db022e6d232c4dd786818e45
-R f53e51c0f1aa91444bc8abb22e670610
-U stephan
-Z eae0132379a01c18b0ddde39b41ec6d7
+P f66608bd356efe492d1003663c2e1ccd7cfbf2d40393d256f8720149904ad2d5
+R 925869cddd08d7fffc1c9ed1ac68c360
+T *branch * var-in-limit
+T *sym-var-in-limit *
+T -sym-trunk *
+U drh
+Z 69f66afa464bc68dae7849bd1467273b
# Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index ae3efac03..be3cbd526 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-f66608bd356efe492d1003663c2e1ccd7cfbf2d40393d256f8720149904ad2d5 \ No newline at end of file
+e58cb304d1e0ec6e30260a165aaccc2cf096ce6c999efb06683c4ef600ee12ef \ No newline at end of file
diff --git a/src/expr.c b/src/expr.c
index 4bab38363..bda91df83 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -2789,8 +2789,12 @@ int sqlite3ExprContainsSubquery(Expr *p){
** to fit in a 32-bit integer, return 1 and put the value of the integer
** in *pValue. If the expression is not an integer or if it is too big
** to fit in a signed 32-bit integer, return 0 and leave *pValue unchanged.
+**
+** If the pParse pointer is provided, then allow the expression p to be
+** a parameter (TK_VARIABLE) that is bound to an integer.
+** But if pParse is NULL, then p must be a pure integer literal.
*/
-int sqlite3ExprIsInteger(const Expr *p, int *pValue){
+int sqlite3ExprIsInteger(const Expr *p, int *pValue, Parse *pParse){
int rc = 0;
if( NEVER(p==0) ) return 0; /* Used to only happen following on OOM */
@@ -2805,18 +2809,38 @@ int sqlite3ExprIsInteger(const Expr *p, int *pValue){
}
switch( p->op ){
case TK_UPLUS: {
- rc = sqlite3ExprIsInteger(p->pLeft, pValue);
+ rc = sqlite3ExprIsInteger(p->pLeft, pValue, 0);
break;
}
case TK_UMINUS: {
int v = 0;
- if( sqlite3ExprIsInteger(p->pLeft, &v) ){
+ if( sqlite3ExprIsInteger(p->pLeft, &v, 0) ){
assert( ((unsigned int)v)!=0x80000000 );
*pValue = -v;
rc = 1;
}
break;
}
+ case TK_VARIABLE: {
+ sqlite3_value *pVal;
+ if( pParse==0 ) break;
+ if( NEVER(pParse->pVdbe==0) ) break;
+ if( (pParse->db->flags & SQLITE_EnableQPSG)!=0 ) break;
+ sqlite3VdbeSetVarmask(pParse->pVdbe, p->iColumn);
+ pVal = sqlite3VdbeGetBoundValue(pParse->pReprepare, p->iColumn,
+ SQLITE_AFF_BLOB);
+ if( pVal ){
+ if( sqlite3_value_type(pVal)==SQLITE_INTEGER ){
+ sqlite3_int64 vv = sqlite3_value_int64(pVal);
+ if( vv == (vv & 0x7fffffff) ){ /* non-negative numbers only */
+ *pValue = (int)vv;
+ rc = 1;
+ }
+ }
+ sqlite3ValueFree(pVal);
+ }
+ break;
+ }
default: break;
}
return rc;
diff --git a/src/resolve.c b/src/resolve.c
index 40044a220..d5c1515a7 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -1505,7 +1505,7 @@ static int resolveOrderByTermToExprList(
int rc; /* Return code from subprocedures */
u8 savedSuppErr; /* Saved value of db->suppressErr */
- assert( sqlite3ExprIsInteger(pE, &i)==0 );
+ assert( sqlite3ExprIsInteger(pE, &i, 0)==0 );
pEList = pSelect->pEList;
/* Resolve all names in the ORDER BY term expression
@@ -1604,7 +1604,7 @@ static int resolveCompoundOrderBy(
if( pItem->fg.done ) continue;
pE = sqlite3ExprSkipCollateAndLikely(pItem->pExpr);
if( NEVER(pE==0) ) continue;
- if( sqlite3ExprIsInteger(pE, &iCol) ){
+ if( sqlite3ExprIsInteger(pE, &iCol, 0) ){
if( iCol<=0 || iCol>pEList->nExpr ){
resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr, pE);
return 1;
@@ -1789,7 +1789,7 @@ static int resolveOrderGroupBy(
continue;
}
}
- if( sqlite3ExprIsInteger(pE2, &iCol) ){
+ if( sqlite3ExprIsInteger(pE2, &iCol, 0) ){
/* The ORDER BY term is an integer constant. Again, set the column
** number so that sqlite3ResolveOrderGroupBy() will convert the
** order-by term to a copy of the result-set expression */
diff --git a/src/select.c b/src/select.c
index 4671dfa6d..9a0f2e40f 100644
--- a/src/select.c
+++ b/src/select.c
@@ -2492,7 +2492,7 @@ static void computeLimitRegisters(Parse *pParse, Select *p, int iBreak){
p->iLimit = iLimit = ++pParse->nMem;
v = sqlite3GetVdbe(pParse);
assert( v!=0 );
- if( sqlite3ExprIsInteger(pLimit->pLeft, &n) ){
+ if( sqlite3ExprIsInteger(pLimit->pLeft, &n, pParse) ){
sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit);
VdbeComment((v, "LIMIT counter"));
if( n==0 ){
@@ -2972,7 +2972,7 @@ static int multiSelect(
p->pPrior = pPrior;
p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow);
if( p->pLimit
- && sqlite3ExprIsInteger(p->pLimit->pLeft, &nLimit)
+ && sqlite3ExprIsInteger(p->pLimit->pLeft, &nLimit, pParse)
&& nLimit>0 && p->nSelectRow > sqlite3LogEst((u64)nLimit)
){
p->nSelectRow = sqlite3LogEst((u64)nLimit);
diff --git a/src/shell.c.in b/src/shell.c.in
index 546107138..a664e2cbc 100644
--- a/src/shell.c.in
+++ b/src/shell.c.in
@@ -4253,6 +4253,7 @@ static int shell_exec(
sqlite3_reset(pExplain);
rc = sqlite3_stmt_explain(pExplain, 2);
if( rc==SQLITE_OK ){
+ bind_prepared_stmt(pArg, pExplain);
while( sqlite3_step(pExplain)==SQLITE_ROW ){
const char *zEQPLine = (const char*)sqlite3_column_text(pExplain,3);
int iEqpId = sqlite3_column_int(pExplain, 0);
@@ -4270,6 +4271,7 @@ static int shell_exec(
if( rc==SQLITE_OK ){
pArg->cMode = MODE_Explain;
assert( sqlite3_stmt_isexplain(pExplain)==1 );
+ bind_prepared_stmt(pArg, pExplain);
explain_data_prepare(pArg, pExplain);
exec_prepared_stmt(pArg, pExplain);
explain_data_delete(pArg);
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 7d313e408..8866b69ea 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -5088,7 +5088,7 @@ int sqlite3ExprIsSingleTableConstraint(Expr*,const SrcList*,int,int);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
int sqlite3ExprContainsSubquery(Expr*);
#endif
-int sqlite3ExprIsInteger(const Expr*, int*);
+int sqlite3ExprIsInteger(const Expr*, int*, Parse*);
int sqlite3ExprCanBeNull(const Expr*);
int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
int sqlite3IsRowid(const char*);
diff --git a/src/where.c b/src/where.c
index 71a8241e3..a5cf63d71 100644
--- a/src/where.c
+++ b/src/where.c
@@ -2942,7 +2942,7 @@ static void whereLoopOutputAdjust(
Expr *pRight = pTerm->pExpr->pRight;
int k = 0;
testcase( pTerm->pExpr->op==TK_IS );
- if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){
+ if( sqlite3ExprIsInteger(pRight, &k, 0) && k>=(-1) && k<=1 ){
k = 10;
}else{
k = 20;
diff --git a/src/whereexpr.c b/src/whereexpr.c
index ae1c838c0..dcda75d26 100644
--- a/src/whereexpr.c
+++ b/src/whereexpr.c
@@ -1583,7 +1583,7 @@ static void whereAddLimitExpr(
Expr *pNew;
int iVal = 0;
- if( sqlite3ExprIsInteger(pExpr, &iVal) && iVal>=0 ){
+ if( sqlite3ExprIsInteger(pExpr, &iVal, pParse) && iVal>=0 ){
Expr *pVal = sqlite3Expr(db, TK_INTEGER, 0);
if( pVal==0 ) return;
ExprSetProperty(pVal, EP_IntValue);
diff --git a/src/window.c b/src/window.c
index bcee65d92..e5a78e37d 100644
--- a/src/window.c
+++ b/src/window.c
@@ -909,7 +909,7 @@ static ExprList *exprListAppendList(
int iDummy;
Expr *pSub;
pSub = sqlite3ExprSkipCollateAndLikely(pDup);
- if( sqlite3ExprIsInteger(pSub, &iDummy) ){
+ if( sqlite3ExprIsInteger(pSub, &iDummy, 0) ){
pSub->op = TK_NULL;
pSub->flags &= ~(EP_IntValue|EP_IsTrue|EP_IsFalse);
pSub->u.zToken = 0;
diff --git a/test/wherelimit2.test b/test/wherelimit2.test
index 57288bf64..a8017f3c9 100644
--- a/test/wherelimit2.test
+++ b/test/wherelimit2.test
@@ -330,4 +330,35 @@ do_execsql_test 7.2 {
SELECT * FROM t1;
} {3}
+# 2024-06-06
+# Use LIMIT if the argument is a variable.
+#
+reset_db
+do_execsql_test 8.0 {
+ CREATE TABLE t1(a INT, b INT);
+ WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<1000)
+ INSERT INTO t1 SELECT n, n FROM c;
+ CREATE INDEX t1a ON t1(a);
+ CREATE INDEX t1b ON t1(b);
+ ANALYZE;
+}
+do_eqp_test 8.1 {
+ SELECT * FROM t1 WHERE a>=100 AND b<300 ORDER BY b LIMIT 5;
+} {
+ QUERY PLAN
+ |--SEARCH t1 USING INDEX t1a (a>? AND a<?)
+ `--USE TEMP B-TREE FOR ORDER BY
+}
+do_eqp_test 8.2 {
+ SELECT * FROM t1 WHERE a>=100 AND b<300 ORDER BY b LIMIT -1;
+} {
+ QUERY PLAN
+ |--SEARCH t1 USING INDEX t1a (a>? AND a<?)
+ `--USE TEMP B-TREE FOR ORDER BY
+}
+
+
+
+
+
finish_test
diff --git a/test/wherelimit3.test b/test/wherelimit3.test
new file mode 100644
index 000000000..dea3e97d8
--- /dev/null
+++ b/test/wherelimit3.test
@@ -0,0 +1,67 @@
+# 2024-06-06
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# Test cases for query plans using LIMIT
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix wherelimit3
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a INT, b INT);
+ WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<1000)
+ INSERT INTO t1 SELECT n, n FROM c;
+ CREATE INDEX t1a ON t1(a);
+ CREATE INDEX t1b ON t1(b);
+ ANALYZE;
+}
+
+do_eqp_test 1.1 {
+ SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT 5;
+} {
+ QUERY PLAN
+ |--SEARCH t1 USING INDEX t1a (a>? AND a<?)
+ `--USE TEMP B-TREE FOR ORDER BY
+}
+ifcapable stat4 {
+ do_eqp_test 1.2 {
+ SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT -1;
+ } {
+ QUERY PLAN
+ `--SCAN t1 USING INDEX t1b
+ }
+}
+
+set N [expr 5]
+do_eqp_test 1.3 {
+ SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT $::N;
+} {
+ QUERY PLAN
+ |--SEARCH t1 USING INDEX t1a (a>? AND a<?)
+ `--USE TEMP B-TREE FOR ORDER BY
+}
+
+ifcapable stat4 {
+ set N [expr -1]
+ do_eqp_test 1.4 {
+ SELECT * FROM t1 WHERE a>=100 AND a<300 ORDER BY b LIMIT $::N;
+ } {
+ QUERY PLAN
+ `--SCAN t1 USING INDEX t1b
+ }
+}
+
+
+
+
+
+finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-03 01:17:18 +0000