From 74ad49c02e318075f37e7992a12630f9fa2638f4 Mon Sep 17 00:00:00 2001
From: dan <dan@noemail.net>
Date: Thu, 21 May 2015 20:26:37 +0000
Subject: [PATCH] Have r-tree prefer to use the constraint "col MATCH ?" over
 "rowid = ?".

FossilOrigin-Name: b9e45596d823a6659f4ce2450afcd703feb788d8
---
 ext/rtree/rtree.c     | 15 ++++++++++++++-
 ext/rtree/rtreeE.test |  8 ++++++++
 manifest              | 19 ++++++++-----------
 manifest.uuid         |  2 +-
 4 files changed, 31 insertions(+), 13 deletions(-)

diff --git a/ext/rtree/rtree.c b/ext/rtree/rtree.c
index aed7240dff..3521352b78 100644
--- a/ext/rtree/rtree.c
+++ b/ext/rtree/rtree.c
@@ -1672,17 +1672,30 @@ static int rtreeBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
   Rtree *pRtree = (Rtree*)tab;
   int rc = SQLITE_OK;
   int ii;
+  int bMatch = 0;                 /* True if there exists a MATCH constraint */
   i64 nRow;                       /* Estimated rows returned by this scan */
 
   int iIdx = 0;
   char zIdxStr[RTREE_MAX_DIMENSIONS*8+1];
   memset(zIdxStr, 0, sizeof(zIdxStr));
 
+  /* Check if there exists a MATCH constraint - even an unusable one. If there
+  ** is, do not consider the lookup-by-rowid plan as using such a plan would
+  ** require the VDBE to evaluate the MATCH constraint, which is not currently
+  ** possible. */
+  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
+    if( pIdxInfo->aConstraint[ii].op==SQLITE_INDEX_CONSTRAINT_MATCH ){
+      bMatch = 1;
+    }
+  }
+
   assert( pIdxInfo->idxStr==0 );
   for(ii=0; ii<pIdxInfo->nConstraint && iIdx<(int)(sizeof(zIdxStr)-1); ii++){
     struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[ii];
 
-    if( p->usable && p->iColumn==0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){
+    if( bMatch==0 && p->usable 
+     && p->iColumn==0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ 
+    ){
       /* We have an equality constraint on the rowid. Use strategy 1. */
       int jj;
       for(jj=0; jj<ii; jj++){
diff --git a/ext/rtree/rtreeE.test b/ext/rtree/rtreeE.test
index b95b76457e..3e5ba3a67f 100644
--- a/ext/rtree/rtreeE.test
+++ b/ext/rtree/rtreeE.test
@@ -80,6 +80,13 @@ do_execsql_test rtreeE-1.6 {
   SELECT id FROM rt1 WHERE id MATCH Qcircle(0,0,1000,5) ORDER BY +id
 } {0 2 4 6 8 10 12 14 16 18 20 22 24 100 102 104 106 108 110 112 114 116 118 120 122 124 200 202 204 206 208 210 212 214 216 218 220 222 224}
 
+# Test that rtree prefers MATCH to lookup-by-rowid.
+#
+do_execsql_test rtreeE-1.7 {
+  SELECT id FROM rt1 WHERE id=18 AND id MATCH Qcircle(0,0,1000,5)
+} {18}
+
+
 # Construct a large 2-D RTree with thousands of random entries.
 #
 do_test rtreeE-2.1 {
@@ -129,4 +136,5 @@ do_execsql_test rtreeE-2.4 {
   SELECT id FROM rt2 WHERE id MATCH breadthfirstsearch(0,10000,0,10000) ORDER BY id
 } $ans
 
+
 finish_test
diff --git a/manifest b/manifest
index 32381a57b3..a785800360 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Add\sthe\ssqlite3_value_dup()\sand\ssqlite3_value_free()\sinterfaces\sand\suse\sthose\nto\sadd\sthe\ssqlite3_rtree_query_info.apSqlParam\sfield\sto\sthe\squery\scallback\nin\sR-Tree.
-D 2015-05-20T22:01:31.064
+C Have\sr-tree\sprefer\sto\suse\sthe\sconstraint\s"col\sMATCH\s?"\sover\s"rowid\s=\s?".
+D 2015-05-21T20:26:37.539
 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f
 F Makefile.in 66db4ec2c6cd63d0f3e29d366aaaab769aba175b
 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23
@@ -143,7 +143,7 @@ F ext/ota/sqlite3ota.c 2246b779f46ab20d5e7876f5b96c378c601d20f4
 F ext/ota/sqlite3ota.h 00028de37eede471ff1947d455cc3f33d3a911c6
 F ext/ota/test_ota.c a876f88550d7d59a3ef62d4c1a5c04c4c2f1ebe1
 F ext/rtree/README 6315c0d73ebf0ec40dedb5aa0e942bc8b54e3761
-F ext/rtree/rtree.c 50d9df06d2b7ca946646531c20b3b627514ad832
+F ext/rtree/rtree.c 00c953695d8ae174c0f2947795e1e8a17ebb50cb
 F ext/rtree/rtree.h 834dbcb82dc85b2481cde6a07cdadfddc99e9b9e
 F ext/rtree/rtree1.test 541bbcab74613907fea08b2ecdcdd5b7aa724cc9
 F ext/rtree/rtree2.test acbb3a4ce0f4fbc2c304d2b4b784cfa161856bba
@@ -158,7 +158,7 @@ F ext/rtree/rtreeA.test ace05e729a36e342d40cf94e9efc7b4723d9dcdf
 F ext/rtree/rtreeB.test c85f9ce78766c4e68b8b89fbf2979ee9cfa82b4e
 F ext/rtree/rtreeC.test df158dcc81f1a43ce7eef361af03c48ec91f1e06
 F ext/rtree/rtreeD.test 636630357638f5983701550b37f0f5867130d2ca
-F ext/rtree/rtreeE.test b268eed6a621613fbc0a44157a8d6fe10cff3436
+F ext/rtree/rtreeE.test 45a147a64a76306172819562309681d8e90f94bb
 F ext/rtree/rtreeF.test 66deb9fd1611c7ca2e374adba63debdc2dbb12b4
 F ext/rtree/rtree_perf.tcl 6c18c1f23cd48e0f948930c98dfdd37dfccb5195
 F ext/rtree/rtree_util.tcl 06aab2ed5b826545bf215fff90ecb9255a8647ea
@@ -1297,10 +1297,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1
 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4
 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32
 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f
-P 7530e1bf557083ef91447a02f05b019dbe60fa70 a7ee40c4fc62843ac5b96ba47ca14a66e8cd6961
-R d2163e09200f9f143d5de18a03f83bdf
-T *branch * sessions-value-dup
-T *sym-sessions-value-dup *
-T -sym-sessions *
-U drh
-Z c4ab43ad24ffbf6a36ab01db9a273da1
+P 2007391cec8989de6d6aa0ffc5789c32d48e4c08
+R 729d877498d773539ac708b3730baa16
+U dan
+Z d7c4582f04dc6868ff7244804fb99956
diff --git a/manifest.uuid b/manifest.uuid
index 79d6a34a5b..220dc74392 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-2007391cec8989de6d6aa0ffc5789c32d48e4c08
\ No newline at end of file
+b9e45596d823a6659f4ce2450afcd703feb788d8
\ No newline at end of file
-- 
2.39.5