/ Check-in [7ce03c1b]
Login

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

Overview
Comment:Prevent the rtree module from reading sqlite_stat1 data from the wrong database.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7ce03c1b5552d830300575c5b41a874db7a2ec77
User & Date: dan 2014-03-12 12:44:46
Context
2014-03-12
19:20
Changes to FTS to ensure that it does not access the database from within the xConnect method. check-in: c67a52c3 user: dan tags: trunk
12:44
Prevent the rtree module from reading sqlite_stat1 data from the wrong database. check-in: 7ce03c1b user: dan tags: trunk
2014-03-11
15:27
Version 3.8.4.1 check-in: 018d317b user: drh tags: trunk, release, version-3.8.4.1
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree.c.

  2943   2943   
  2944   2944   /*
  2945   2945   ** This function populates the pRtree->nRowEst variable with an estimate
  2946   2946   ** of the number of rows in the virtual table. If possible, this is based
  2947   2947   ** on sqlite_stat1 data. Otherwise, use RTREE_DEFAULT_ROWEST.
  2948   2948   */
  2949   2949   static int rtreeQueryStat1(sqlite3 *db, Rtree *pRtree){
  2950         -  const char *zSql = "SELECT stat FROM sqlite_stat1 WHERE tbl= ? || '_rowid'";
         2950  +  const char *zFmt = "SELECT stat FROM %Q.sqlite_stat1 WHERE tbl = '%q_rowid'";
         2951  +  char *zSql;
  2951   2952     sqlite3_stmt *p;
  2952   2953     int rc;
  2953   2954     i64 nRow = 0;
  2954   2955   
  2955         -  rc = sqlite3_prepare_v2(db, zSql, -1, &p, 0);
  2956         -  if( rc==SQLITE_OK ){
  2957         -    sqlite3_bind_text(p, 1, pRtree->zName, -1, SQLITE_STATIC);
  2958         -    if( sqlite3_step(p)==SQLITE_ROW ) nRow = sqlite3_column_int64(p, 0);
  2959         -    rc = sqlite3_finalize(p);
  2960         -  }else if( rc!=SQLITE_NOMEM ){
  2961         -    rc = SQLITE_OK;
  2962         -  }
         2956  +  zSql = sqlite3_mprintf(zFmt, pRtree->zDb, pRtree->zName);
         2957  +  if( zSql==0 ){
         2958  +    rc = SQLITE_NOMEM;
         2959  +  }else{
         2960  +    rc = sqlite3_prepare_v2(db, zSql, -1, &p, 0);
         2961  +    if( rc==SQLITE_OK ){
         2962  +      if( sqlite3_step(p)==SQLITE_ROW ) nRow = sqlite3_column_int64(p, 0);
         2963  +      rc = sqlite3_finalize(p);
         2964  +    }else if( rc!=SQLITE_NOMEM ){
         2965  +      rc = SQLITE_OK;
         2966  +    }
  2963   2967   
  2964         -  if( rc==SQLITE_OK ){
  2965         -    if( nRow==0 ){
  2966         -      pRtree->nRowEst = RTREE_DEFAULT_ROWEST;
  2967         -    }else{
  2968         -      pRtree->nRowEst = MAX(nRow, RTREE_MIN_ROWEST);
         2968  +    if( rc==SQLITE_OK ){
         2969  +      if( nRow==0 ){
         2970  +        pRtree->nRowEst = RTREE_DEFAULT_ROWEST;
         2971  +      }else{
         2972  +        pRtree->nRowEst = MAX(nRow, RTREE_MIN_ROWEST);
         2973  +      }
  2969   2974       }
         2975  +    sqlite3_free(zSql);
  2970   2976     }
  2971   2977   
  2972   2978     return rc;
  2973   2979   }
  2974   2980   
  2975   2981   static sqlite3_module rtreeModule = {
  2976   2982     0,                          /* iVersion */

Changes to ext/rtree/rtreeC.test.

   153    153   do_execsql_test 4.2 {
   154    154     SELECT a, b FROM t1 LEFT JOIN t2 ON (+a = +b);
   155    155   } {1 1 2 {}}
   156    156   
   157    157   do_execsql_test 4.3 {
   158    158     SELECT b, a FROM t2 LEFT JOIN t1 ON (+a = +b);
   159    159   } {1 1 3 {}}
          160  +
          161  +#--------------------------------------------------------------------
          162  +# Test that the sqlite_stat1 data is used correctly.
          163  +#
          164  +reset_db
          165  +do_execsql_test 5.1 {
          166  +  CREATE TABLE t1(x PRIMARY KEY, y);
          167  +  CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
          168  +
          169  +  INSERT INTO t1(x) VALUES(1);
          170  +  INSERT INTO t1(x) SELECT x+1 FROM t1;   --   2
          171  +  INSERT INTO t1(x) SELECT x+2 FROM t1;   --   4
          172  +  INSERT INTO t1(x) SELECT x+4 FROM t1;   --   8
          173  +  INSERT INTO t1(x) SELECT x+8 FROM t1;   --  16
          174  +  INSERT INTO t1(x) SELECT x+16 FROM t1;  --  32
          175  +  INSERT INTO t1(x) SELECT x+32 FROM t1;  --  64
          176  +  INSERT INTO t1(x) SELECT x+64 FROM t1;  -- 128
          177  +  INSERT INTO t1(x) SELECT x+128 FROM t1; -- 256
          178  +  INSERT INTO t1(x) SELECT x+256 FROM t1; -- 512
          179  +  INSERT INTO t1(x) SELECT x+512 FROM t1; --1024
          180  +
          181  +  INSERT INTO rt SELECT x, x, x+1 FROM t1 WHERE x<=5;
          182  +}
          183  +
          184  +# First test a query with no ANALYZE data at all. The outer loop is
          185  +# real table "t1".
          186  +#
          187  +do_eqp_test 5.2 {
          188  +  SELECT * FROM t1, rt WHERE x==id;
          189  +} {
          190  +  0 0 0 {SCAN TABLE t1} 
          191  +  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
          192  +}
          193  +
          194  +# Now create enough ANALYZE data to tell SQLite that virtual table "rt"
          195  +# contains very few rows. This causes it to move "rt" to the outer loop.
          196  +#
          197  +do_execsql_test 5.3 {
          198  +  ANALYZE;
          199  +  DELETE FROM sqlite_stat1 WHERE tbl='t1';
          200  +}
          201  +db close
          202  +sqlite3 db test.db
          203  +do_eqp_test 5.4 {
          204  +  SELECT * FROM t1, rt WHERE x==id;
          205  +} {
          206  +  0 0 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:} 
          207  +  0 1 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (x=?)}
          208  +}
          209  +
          210  +# Delete the ANALYZE data. "t1" should be the outer loop again.
          211  +#
          212  +do_execsql_test 5.5 { DROP TABLE sqlite_stat1; }
          213  +db close
          214  +sqlite3 db test.db
          215  +do_eqp_test 5.6 {
          216  +  SELECT * FROM t1, rt WHERE x==id;
          217  +} {
          218  +  0 0 0 {SCAN TABLE t1} 
          219  +  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
          220  +}
          221  +
          222  +# This time create and attach a database that contains ANALYZE data for
          223  +# tables of the same names as those used internally by virtual table
          224  +# "rt". Check that the rtree module is not fooled into using this data.
          225  +# Table "t1" should remain the outer loop.
          226  +#
          227  +do_test 5.7 {
          228  +  db backup test.db2
          229  +  sqlite3 db2 test.db2
          230  +  db2 eval {
          231  +    ANALYZE;
          232  +    DELETE FROM sqlite_stat1 WHERE tbl='t1';
          233  +  }
          234  +  db2 close
          235  +  db close
          236  +  sqlite3 db test.db
          237  +  execsql { ATTACH 'test.db2' AS aux; }
          238  +} {}
          239  +do_eqp_test 5.8 {
          240  +  SELECT * FROM t1, rt WHERE x==id;
          241  +} {
          242  +  0 0 0 {SCAN TABLE t1} 
          243  +  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
          244  +}
          245  +
   160    246   
   161    247   finish_test
   162    248