When generating individual loops for each ORed term of an OR scan, move any
constant WHERE expressions outside of the loop, as is done for top-level
loops.
FossilOrigin-Name: e4a022be4b069b08cfdfda5295461676b99d28e17bbbedfbcb362dec69de59bd
diff --git a/manifest b/manifest
index 24b1886..f564f14 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Enable\spragma\svirtual\stables\sfor\sthe\sintegrity_check,\squick_check,\sand\nforeign_key_check\spragmas.
-D 2017-06-21T01:36:30.804
+C When\sgenerating\sindividual\sloops\sfor\seach\sORed\sterm\sof\san\sOR\sscan,\smove\sany\nconstant\sWHERE\sexpressions\soutside\sof\sthe\sloop,\sas\sis\sdone\sfor\stop-level\nloops.
+D 2017-06-22T16:51:16.239
F Makefile.in 1cc758ce3374a32425e4d130c2fe7b026b20de5b8843243de75f087c0a2661fb
F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434
F Makefile.msc 8eeb80162074004e906b53d7340a12a14c471a83743aab975947e95ce061efcc
@@ -360,7 +360,7 @@
F src/delete.c 3213547e97b676c6fa79948b7a9ede4801ea04a01a2043241deafedf132ecf5d
F src/expr.c 452c6f3aa656aabf3eefe96bb5f316b2c987fbc12c647964e4ed880f193ca31f
F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
-F src/fkey.c db65492ae549c3b548c9ef1f279ce1684f1c473b116e1c56a90878cd5dcf968d
+F src/fkey.c 5ff2c895fe087756d8085dc1a9bc229b5670e2a65c3929dd87c71e43649af333
F src/func.c 9d52522cc8ae7f5cdadfe14594262f1618bc1f86083c4cd6da861b4cf5af6174
F src/global.c 8a6ab6b4d91effb96ffa81b39f0d70c862abca157f8aaa194600a4a8b7923344
F src/hash.c 63d0ee752a3b92d4695b2b1f5259c4621b2cfebd
@@ -414,7 +414,7 @@
F src/sqliteLimit.h 1513bfb7b20378aa0041e7022d04acb73525de35b80b252f1b83fedb4de6a76b
F src/status.c a9e66593dfb28a9e746cba7153f84d49c1ddc4b1
F src/table.c b46ad567748f24a326d9de40e5b9659f96ffff34
-F src/tclsqlite.c c8cf60d0c5411d5e70e7c136470d29dbe760d250f55198b71682c67086524e4a
+F src/tclsqlite.c 1ac29f18b1b3787a30b45dbbdf6fdc4aa4f1a2f8c7c8fe586beba1b177eba97d
F src/test1.c c99f0442918a7a5d5b68a95d6024c211989e6c782c15ced5a558994baaf76a5e
F src/test2.c 3efb99ab7f1fc8d154933e02ae1378bac9637da5
F src/test3.c b8434949dfb8aff8dfa082c8b592109e77844c2135ed3c492113839b6956255b
@@ -486,7 +486,7 @@
F src/wal.c 40c543f0a2195d1b0dc88ef12142bea690009344
F src/wal.h 06b2a0b599cc0f53ea97f497cf8c6b758c999f71
F src/walker.c d46044e7a5842560dfe7122d93ff5145dd4a96f4d0bf5ba5910a7731b8c01e79
-F src/where.c aa213e1b1c29eb8946a9f25108a18666a745ae5bac41b58d0be98730937a7785
+F src/where.c d4f329d9055dbdb475d697f205db1104af886aad4400168fb9b957f6251ea926
F src/whereInt.h 2a4b634d63ce488b46d4b0da8f2eaa8f9aeab202bc25ef76f007de5e3fba1f20
F src/wherecode.c 339ee802d9d311acf0cba8b5a9a092e167ef71c3a777d4b3e57de25d193251c7
F src/whereexpr.c a2fe3811d45af45a5c6667caabc15e01054fe6228c64e86e1f7d2ba5ef5284f9
@@ -695,7 +695,7 @@
F test/enc2.test 83437a79ba1545a55fb549309175c683fb334473
F test/enc3.test 6807f7a7740a00361ca8d0ccd66bc60c8dc5f2b6
F test/enc4.test c8f1ce3618508fd0909945beb8b8831feef2c020
-F test/eqp.test 3fe051af50921284189d1970eb653f9fcf5117d2
+F test/eqp.test 3f9ba0b2594837c7beaa3ba824e2137cfe857308f020ec5a0c7a62b444e837b0
F test/errmsg.test f31592a594b44ee121371d25ddd5d63497bb3401
F test/eval.test a64c9105d6ff163df7cf09d6ac29cdad5922078c
F test/exclusive.test 9a57bd66e39144b888ca75c309914fcdefb4e3f9
@@ -1465,7 +1465,7 @@
F test/whereC.test cae295158703cb3fc23bf1a108a9ab730efff0f6
F test/whereD.test 711d4df58d6d4fb9b3f5ce040b818564198be002
F test/whereE.test b3a055eef928c992b0a33198a7b8dc10eea5ad2f
-F test/whereF.test 5b2ba0dbe8074aa13e416b37c753991f0a2492d7
+F test/whereF.test 97a86ecdfa4c21684fdff501dbd2cb7397689be8676d0dbad1f5a0892c6b56a3
F test/whereG.test dde4c52a97385a55be6a7cd46be8373f0cf35501
F test/whereH.test e4b07f7a3c2f5d31195cd33710054c78667573b2
F test/whereI.test eab5b226bbc344ac70d7dc09b963a064860ae6d7
@@ -1583,7 +1583,10 @@
F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 94e420ecfb4ec047eb7d1d3125ca8487c90d466760b7f7183759ff922bd868d1
-R bb441c04952674b0d2cabd8a1e6870d3
-U drh
-Z cb37bb49734166773486fcb21706d402
+P 118f7bb33a6f78951bbffa957f48015d1bce5aaf9246a99262a90bc8ad52e5a3
+R b4aae75660dcec44ff13144cfab52224
+T *branch * or-optimization
+T *sym-or-optimization *
+T -sym-trunk *
+U dan
+Z 7d390f8e00d309abb459da345f7f0ca9
diff --git a/manifest.uuid b/manifest.uuid
index 82f0dac..03b32f8 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-118f7bb33a6f78951bbffa957f48015d1bce5aaf9246a99262a90bc8ad52e5a3
\ No newline at end of file
+e4a022be4b069b08cfdfda5295461676b99d28e17bbbedfbcb362dec69de59bd
\ No newline at end of file
diff --git a/src/fkey.c b/src/fkey.c
index f91a6de..0012768 100644
--- a/src/fkey.c
+++ b/src/fkey.c
@@ -633,10 +633,12 @@
/* Create VDBE to loop through the entries in pSrc that match the WHERE
** clause. For each row found, increment either the deferred or immediate
** foreign key constraint counter. */
- pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
- sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
- if( pWInfo ){
- sqlite3WhereEnd(pWInfo);
+ if( pParse->nErr==0 ){
+ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
+ sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
+ if( pWInfo ){
+ sqlite3WhereEnd(pWInfo);
+ }
}
/* Clean up the WHERE clause constructed above. */
diff --git a/src/tclsqlite.c b/src/tclsqlite.c
index 754775e..bb7c9e5 100644
--- a/src/tclsqlite.c
+++ b/src/tclsqlite.c
@@ -161,6 +161,7 @@
int nStmt; /* Number of statements in stmtList */
IncrblobChannel *pIncrblob;/* Linked list of open incrblob channels */
int nStep, nSort, nIndex; /* Statistics for most recent operation */
+ int nVMStep; /* Another statistic for most recent operation */
int nTransaction; /* Number of nested [transaction] methods */
int openFlags; /* Flags used to open. (SQLITE_OPEN_URI) */
#ifdef SQLITE_TEST
@@ -1588,6 +1589,7 @@
pDb->nStep = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_FULLSCAN_STEP,1);
pDb->nSort = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_SORT,1);
pDb->nIndex = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_AUTOINDEX,1);
+ pDb->nVMStep = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_VM_STEP,1);
dbReleaseColumnNames(p);
p->pPreStmt = 0;
@@ -2855,7 +2857,7 @@
}
/*
- ** $db status (step|sort|autoindex)
+ ** $db status (step|sort|autoindex|vmstep)
**
** Display SQLITE_STMTSTATUS_FULLSCAN_STEP or
** SQLITE_STMTSTATUS_SORT for the most recent eval.
@@ -2874,9 +2876,11 @@
v = pDb->nSort;
}else if( strcmp(zOp, "autoindex")==0 ){
v = pDb->nIndex;
+ }else if( strcmp(zOp, "vmstep")==0 ){
+ v = pDb->nVMStep;
}else{
Tcl_AppendResult(interp,
- "bad argument: should be autoindex, step, or sort",
+ "bad argument: should be autoindex, step, sort or vmstep",
(char*)0);
return TCL_ERROR;
}
diff --git a/src/where.c b/src/where.c
index 99b0df4..1cd5d70 100644
--- a/src/where.c
+++ b/src/where.c
@@ -4295,6 +4295,31 @@
}
/*
+** Helper function for exprIsDeterministic().
+*/
+static int exprNodeIsDeterministic(Walker *pWalker, Expr *pExpr){
+ if( pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_ConstFunc)==0 ){
+ pWalker->eCode = 0;
+ return WRC_Abort;
+ }
+ return WRC_Continue;
+}
+
+/*
+** Return true if the expression contains no non-deterministic SQL
+** functions. Do not consider non-deterministic SQL functions that are
+** part of sub-select statements.
+*/
+static int exprIsDeterministic(Expr *p){
+ Walker w;
+ memset(&w, 0, sizeof(w));
+ w.eCode = 1;
+ w.xExprCallback = exprNodeIsDeterministic;
+ sqlite3WalkExpr(&w, p);
+ return w.eCode;
+}
+
+/*
** Generate the beginning of the loop used for WHERE clause processing.
** The return value is a pointer to an opaque structure that contains
** information needed to terminate the loop. Later, the calling routine
@@ -4492,17 +4517,6 @@
sqlite3WhereClauseInit(&pWInfo->sWC, pWInfo);
sqlite3WhereSplit(&pWInfo->sWC, pWhere, TK_AND);
- /* Special case: a WHERE clause that is constant. Evaluate the
- ** expression and either jump over all of the code or fall thru.
- */
- for(ii=0; ii<sWLB.pWC->nTerm; ii++){
- if( nTabList==0 || sqlite3ExprIsConstantNotJoin(sWLB.pWC->a[ii].pExpr) ){
- sqlite3ExprIfFalse(pParse, sWLB.pWC->a[ii].pExpr, pWInfo->iBreak,
- SQLITE_JUMPIFNULL);
- sWLB.pWC->a[ii].wtFlags |= TERM_CODED;
- }
- }
-
/* Special case: No FROM clause
*/
if( nTabList==0 ){
@@ -4541,6 +4555,25 @@
sqlite3WhereExprAnalyze(pTabList, &pWInfo->sWC);
if( db->mallocFailed ) goto whereBeginError;
+ /* Special case: WHERE terms that do not refer to any tables in the join
+ ** (constant expressions). Evaluate each such term, and jump over all the
+ ** generated code if the result is not true.
+ **
+ ** Do not do this if the expression contains non-deterministic functions
+ ** that are not within a sub-select. This is not strictly required, but
+ ** preserves SQLite's legacy behaviour in the following two cases:
+ **
+ ** FROM ... WHERE random()>0; -- eval random() once per row
+ ** FROM ... WHERE (SELECT random())>0; -- eval random() once overall
+ */
+ for(ii=0; ii<sWLB.pWC->nTerm; ii++){
+ WhereTerm *pT = &sWLB.pWC->a[ii];
+ if( pT->prereqAll==0 && (nTabList==0 || exprIsDeterministic(pT->pExpr)) ){
+ sqlite3ExprIfFalse(pParse, pT->pExpr, pWInfo->iBreak, SQLITE_JUMPIFNULL);
+ pT->wtFlags |= TERM_CODED;
+ }
+ }
+
if( wctrlFlags & WHERE_WANT_DISTINCT ){
if( isDistinctRedundant(pParse, pTabList, &pWInfo->sWC, pResultSet) ){
/* The DISTINCT marking is pointless. Ignore it. */
diff --git a/test/eqp.test b/test/eqp.test
index c955a80..30fcdf2 100644
--- a/test/eqp.test
+++ b/test/eqp.test
@@ -188,24 +188,24 @@
do_eqp_test 3.1.2 {
SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
} {
- 0 0 0 {SCAN TABLE t1}
0 0 0 {EXECUTE SCALAR SUBQUERY 1}
1 0 0 {SCAN TABLE t1 AS sub}
+ 0 0 0 {SCAN TABLE t1}
}
do_eqp_test 3.1.3 {
SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
} {
- 0 0 0 {SCAN TABLE t1}
0 0 0 {EXECUTE SCALAR SUBQUERY 1}
1 0 0 {SCAN TABLE t1 AS sub}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+ 0 0 0 {SCAN TABLE t1}
}
do_eqp_test 3.1.4 {
SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
} {
- 0 0 0 {SCAN TABLE t1}
0 0 0 {EXECUTE SCALAR SUBQUERY 1}
1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
+ 0 0 0 {SCAN TABLE t1}
}
det 3.2.1 {
diff --git a/test/whereF.test b/test/whereF.test
index b9580bb..3b938aa 100644
--- a/test/whereF.test
+++ b/test/whereF.test
@@ -119,4 +119,61 @@
EXPLAIN QUERY PLAN SELECT rowid FROM t4 WHERE a=? AND b=?;
} {/a=. AND b=./}
+#-------------------------------------------------------------------------
+# Test the following case:
+#
+# ... FROM t1, t2 WHERE (
+# t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
+# )
+#
+# where there is an index on t2(f2). The planner should use "t1" as the
+# outer loop. The inner loop, on "t2", is an OR optimization. One pass
+# for:
+#
+# t2.rowid = $1
+#
+# and another for:
+#
+# t2.f2=$1 AND $1!=-1
+#
+# the test is to ensure that on the second pass, the ($1!=-1) condition
+# is tested before any seek operations are performed - i.e. outside of
+# the loop through the f2=$1 range of the t2(f2) index.
+#
+reset_db
+do_execsql_test 5.0 {
+ CREATE TABLE t1(f1);
+ CREATE TABLE t2(f2);
+ CREATE INDEX t2f ON t2(f2);
+
+ INSERT INTO t1 VALUES(-1);
+ INSERT INTO t1 VALUES(-1);
+ INSERT INTO t1 VALUES(-1);
+ INSERT INTO t1 VALUES(-1);
+
+ WITH w(i) AS (
+ SELECT 1 UNION ALL SELECT i+1 FROM w WHERE i<1000
+ )
+ INSERT INTO t2 SELECT -1 FROM w;
+}
+
+do_execsql_test 5.1 {
+ SELECT count(*) FROM t1, t2 WHERE t2.rowid = +t1.rowid
+} {4}
+do_test 5.2 { expr [db status vmstep]<200 } 1
+
+do_execsql_test 5.3 {
+ SELECT count(*) FROM t1, t2 WHERE (
+ t2.rowid = +t1.rowid OR t2.f2 = t1.f1
+ )
+} {4000}
+do_test 5.4 { expr [db status vmstep]>1000 } 1
+
+do_execsql_test 5.5 {
+ SELECT count(*) FROM t1, t2 WHERE (
+ t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
+ )
+} {4}
+do_test 5.6 { expr [db status vmstep]<200 } 1
+
finish_test