/ Check-in [b9e45596]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Have r-tree prefer to use the constraint "col MATCH ?" over "rowid = ?". drh added later: All changes on this branch have been cherrypicked to trunk and will be merged into sessions from trunk. So this branch can close.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | sessions-value-dup
Files: files | file ages | folders
SHA1: b9e45596d823a6659f4ce2450afcd703feb788d8
User & Date: dan 2015-05-21 20:26:37
Original Comment: Have r-tree prefer to use the constraint "col MATCH ?" over "rowid = ?".
Context
2015-05-22
16:08
Have r-tree prefer to use the constraint "col MATCH ?" over "rowid = ?". check-in: b9fb9519 user: dan tags: trunk
2015-05-21
20:26
Have r-tree prefer to use the constraint "col MATCH ?" over "rowid = ?". drh added later: All changes on this branch have been cherrypicked to trunk and will be merged into sessions from trunk. So this branch can close. Closed-Leaf check-in: b9e45596 user: dan tags: sessions-value-dup
2015-05-20
22:01
Add the sqlite3_value_dup() and sqlite3_value_free() interfaces and use those to add the sqlite3_rtree_query_info.apSqlParam field to the query callback in R-Tree. check-in: 2007391c user: drh tags: sessions-value-dup
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree.c.

  1668   1668   ** to which the constraint applies. The leftmost coordinate column
  1669   1669   ** is 'a', the second from the left 'b' etc.
  1670   1670   */
  1671   1671   static int rtreeBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  1672   1672     Rtree *pRtree = (Rtree*)tab;
  1673   1673     int rc = SQLITE_OK;
  1674   1674     int ii;
         1675  +  int bMatch = 0;                 /* True if there exists a MATCH constraint */
  1675   1676     i64 nRow;                       /* Estimated rows returned by this scan */
  1676   1677   
  1677   1678     int iIdx = 0;
  1678   1679     char zIdxStr[RTREE_MAX_DIMENSIONS*8+1];
  1679   1680     memset(zIdxStr, 0, sizeof(zIdxStr));
         1681  +
         1682  +  /* Check if there exists a MATCH constraint - even an unusable one. If there
         1683  +  ** is, do not consider the lookup-by-rowid plan as using such a plan would
         1684  +  ** require the VDBE to evaluate the MATCH constraint, which is not currently
         1685  +  ** possible. */
         1686  +  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
         1687  +    if( pIdxInfo->aConstraint[ii].op==SQLITE_INDEX_CONSTRAINT_MATCH ){
         1688  +      bMatch = 1;
         1689  +    }
         1690  +  }
  1680   1691   
  1681   1692     assert( pIdxInfo->idxStr==0 );
  1682   1693     for(ii=0; ii<pIdxInfo->nConstraint && iIdx<(int)(sizeof(zIdxStr)-1); ii++){
  1683   1694       struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[ii];
  1684   1695   
  1685         -    if( p->usable && p->iColumn==0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){
         1696  +    if( bMatch==0 && p->usable 
         1697  +     && p->iColumn==0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ 
         1698  +    ){
  1686   1699         /* We have an equality constraint on the rowid. Use strategy 1. */
  1687   1700         int jj;
  1688   1701         for(jj=0; jj<ii; jj++){
  1689   1702           pIdxInfo->aConstraintUsage[jj].argvIndex = 0;
  1690   1703           pIdxInfo->aConstraintUsage[jj].omit = 0;
  1691   1704         }
  1692   1705         pIdxInfo->idxNum = 1;

Changes to ext/rtree/rtreeE.test.

    75     75     SELECT id FROM rt1 WHERE id MATCH Qcircle('r:1000 e:4') ORDER BY +id
    76     76   } {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}
    77     77   
    78     78   # Exclude odd rowids on a breadth-first search.
    79     79   do_execsql_test rtreeE-1.6 {
    80     80     SELECT id FROM rt1 WHERE id MATCH Qcircle(0,0,1000,5) ORDER BY +id
    81     81   } {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}
           82  +
           83  +# Test that rtree prefers MATCH to lookup-by-rowid.
           84  +#
           85  +do_execsql_test rtreeE-1.7 {
           86  +  SELECT id FROM rt1 WHERE id=18 AND id MATCH Qcircle(0,0,1000,5)
           87  +} {18}
           88  +
    82     89   
    83     90   # Construct a large 2-D RTree with thousands of random entries.
    84     91   #
    85     92   do_test rtreeE-2.1 {
    86     93     db eval {
    87     94       CREATE TABLE t2(id,x0,x1,y0,y1);
    88     95       CREATE VIRTUAL TABLE rt2 USING rtree(id,x0,x1,y0,y1);
................................................................................
   124    131   do_execsql_test rtreeE-2.3 {
   125    132     SELECT id FROM rt2 WHERE id MATCH breadthfirstsearch(0,5000,0,5000) ORDER BY id
   126    133   } $ans
   127    134   set ans [db eval {SELECT id FROM t2 WHERE x1>=0 AND x0<=10000 AND y1>=0 AND y0<=10000 ORDER BY id}]
   128    135   do_execsql_test rtreeE-2.4 {
   129    136     SELECT id FROM rt2 WHERE id MATCH breadthfirstsearch(0,10000,0,10000) ORDER BY id
   130    137   } $ans
          138  +
   131    139   
   132    140   finish_test