ext/rtree/rtreeC.test

   1 # 2011 March 2
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 # Make sure the rtreenode() testing function can handle entries with
  12 # 64-bit rowids.
  13 #
  14
  15 if {![info exists testdir]} {
  16   set testdir [file join [file dirname [info script]] .. .. test]
  17 }
  18 source [file join [file dirname [info script]] rtree_util.tcl]
  19 source $testdir/tester.tcl
  20 ifcapable !rtree { finish_test ; return }
  21 set testprefix rtreeC
  22
  23 do_execsql_test 1.0 {
  24   CREATE VIRTUAL TABLE r_tree USING rtree(id, min_x, max_x, min_y, max_y);
  25   CREATE TABLE t(x, y);
  26 }
  27
  28 do_eqp_test 1.1 {
  29   SELECT * FROM r_tree, t
  30   WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
  31 } {
  32   QUERY PLAN
  33   |--SCAN TABLE t
  34   `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
  35 }
  36
  37 do_eqp_test 1.2 {
  38   SELECT * FROM t, r_tree
  39   WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
  40 } {
  41   QUERY PLAN
  42   |--SCAN TABLE t
  43   `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
  44 }
  45
  46 do_eqp_test 1.3 {
  47   SELECT * FROM t, r_tree
  48   WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
  49 } {
  50   QUERY PLAN
  51   |--SCAN TABLE t
  52   `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
  53 }
  54
  55 do_eqp_test 1.5 {
  56   SELECT * FROM t, r_tree
  57 } {
  58   QUERY PLAN
  59   |--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:
  60   `--SCAN TABLE t
  61 }
  62
  63 do_execsql_test 2.0 {
  64   INSERT INTO t VALUES(0, 0);
  65   INSERT INTO t VALUES(0, 1);
  66   INSERT INTO t VALUES(0, 2);
  67   INSERT INTO t VALUES(0, 3);
  68   INSERT INTO t VALUES(0, 4);
  69   INSERT INTO t VALUES(0, 5);
  70   INSERT INTO t VALUES(0, 6);
  71   INSERT INTO t VALUES(0, 7);
  72   INSERT INTO t VALUES(0, 8);
  73   INSERT INTO t VALUES(0, 9);
  74
  75   INSERT INTO t SELECT x+1, y FROM t;
  76   INSERT INTO t SELECT x+2, y FROM t;
  77   INSERT INTO t SELECT x+4, y FROM t;
  78   INSERT INTO r_tree SELECT NULL, x-1, x+1, y-1, y+1 FROM t;
  79   ANALYZE;
  80 }
  81
  82 db close
  83 sqlite3 db test.db
  84
  85 do_eqp_test 2.1 {
  86   SELECT * FROM r_tree, t
  87   WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
  88 } {
  89   QUERY PLAN
  90   |--SCAN TABLE t
  91   `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
  92 }
  93
  94 do_eqp_test 2.2 {
  95   SELECT * FROM t, r_tree
  96   WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
  97 } {
  98   QUERY PLAN
  99   |--SCAN TABLE t
 100   `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
 101 }
 102
 103 do_eqp_test 2.3 {
 104   SELECT * FROM t, r_tree
 105   WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
 106 } {
 107   QUERY PLAN
 108   |--SCAN TABLE t
 109   `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
 110 }
 111
 112 do_eqp_test 2.5 {
 113   SELECT * FROM t, r_tree
 114 } {
 115   QUERY PLAN
 116   |--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:
 117   `--SCAN TABLE t
 118 }
 119
 120 #-------------------------------------------------------------------------
 121 # Test that the special CROSS JOIN handling works with rtree tables.
 122 #
 123 do_execsql_test 3.1 {
 124   CREATE TABLE t1(x);
 125   CREATE TABLE t2(y);
 126   CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2);
 127 }
 128
 129 do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } {
 130   QUERY PLAN
 131   |--SCAN TABLE t1
 132   `--SCAN TABLE t2
 133 }
 134 do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } {
 135   QUERY PLAN
 136   |--SCAN TABLE t2
 137   `--SCAN TABLE t1
 138 }
 139
 140 do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } {
 141   QUERY PLAN
 142   |--SCAN TABLE t1
 143   `--SCAN TABLE t3 VIRTUAL TABLE INDEX 2:
 144 }
 145 do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } {
 146   QUERY PLAN
 147   |--SCAN TABLE t3 VIRTUAL TABLE INDEX 2:
 148   `--SCAN TABLE t1
 149 }
 150
 151 #--------------------------------------------------------------------
 152 # Test that LEFT JOINs are not reordered if the right-hand-side is
 153 # a virtual table.
 154 #
 155 reset_db
 156 do_execsql_test 4.1 {
 157   CREATE TABLE t1(a);
 158   CREATE VIRTUAL TABLE t2 USING rtree(b, x1,x2);
 159
 160   INSERT INTO t1 VALUES(1);
 161   INSERT INTO t1 VALUES(2);
 162
 163   INSERT INTO t2 VALUES(1, 0.0, 0.1);
 164   INSERT INTO t2 VALUES(3, 0.0, 0.1);
 165 }
 166
 167 do_execsql_test 4.2 {
 168   SELECT a, b FROM t1 LEFT JOIN t2 ON (+a = +b);
 169 } {1 1 2 {}}
 170
 171 do_execsql_test 4.3 {
 172   SELECT b, a FROM t2 LEFT JOIN t1 ON (+a = +b);
 173 } {1 1 3 {}}
 174
 175 #--------------------------------------------------------------------
 176 # Test that the sqlite_stat1 data is used correctly.
 177 #
 178 reset_db
 179 do_execsql_test 5.1 {
 180   CREATE TABLE t1(x PRIMARY KEY, y);
 181   CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
 182
 183   INSERT INTO t1(x) VALUES(1);
 184   INSERT INTO t1(x) SELECT x+1 FROM t1;   --   2
 185   INSERT INTO t1(x) SELECT x+2 FROM t1;   --   4
 186   INSERT INTO t1(x) SELECT x+4 FROM t1;   --   8
 187   INSERT INTO t1(x) SELECT x+8 FROM t1;   --  16
 188   INSERT INTO t1(x) SELECT x+16 FROM t1;  --  32
 189   INSERT INTO t1(x) SELECT x+32 FROM t1;  --  64
 190   INSERT INTO t1(x) SELECT x+64 FROM t1;  -- 128
 191   INSERT INTO t1(x) SELECT x+128 FROM t1; -- 256
 192   INSERT INTO t1(x) SELECT x+256 FROM t1; -- 512
 193   INSERT INTO t1(x) SELECT x+512 FROM t1; --1024
 194
 195   INSERT INTO rt SELECT x, x, x+1 FROM t1 WHERE x<=5;
 196 }
 197 do_rtree_integrity_test 5.1.1 rt
 198
 199 # First test a query with no ANALYZE data at all. The outer loop is
 200 # real table "t1".
 201 #
 202 do_eqp_test 5.2 {
 203   SELECT * FROM t1, rt WHERE x==id;
 204 } {
 205   QUERY PLAN
 206   |--SCAN TABLE t1
 207   `--SCAN TABLE rt VIRTUAL TABLE INDEX 1:
 208 }
 209
 210 # Now create enough ANALYZE data to tell SQLite that virtual table "rt"
 211 # contains very few rows. This causes it to move "rt" to the outer loop.
 212 #
 213 do_execsql_test 5.3 {
 214   ANALYZE;
 215   DELETE FROM sqlite_stat1 WHERE tbl='t1';
 216 }
 217 db close
 218 sqlite3 db test.db
 219 do_eqp_test 5.4 {
 220   SELECT * FROM t1, rt WHERE x==id;
 221 } {
 222   QUERY PLAN
 223   |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:
 224   `--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (x=?)
 225 }
 226
 227 # Delete the ANALYZE data. "t1" should be the outer loop again.
 228 #
 229 do_execsql_test 5.5 { DROP TABLE sqlite_stat1; }
 230 db close
 231 sqlite3 db test.db
 232 do_eqp_test 5.6 {
 233   SELECT * FROM t1, rt WHERE x==id;
 234 } {
 235   QUERY PLAN
 236   |--SCAN TABLE t1
 237   `--SCAN TABLE rt VIRTUAL TABLE INDEX 1:
 238 }
 239
 240 # This time create and attach a database that contains ANALYZE data for
 241 # tables of the same names as those used internally by virtual table
 242 # "rt". Check that the rtree module is not fooled into using this data.
 243 # Table "t1" should remain the outer loop.
 244 #
 245 do_test 5.7 {
 246   db backup test.db2
 247   sqlite3 db2 test.db2
 248   db2 eval {
 249     ANALYZE;
 250     DELETE FROM sqlite_stat1 WHERE tbl='t1';
 251   }
 252   db2 close
 253   db close
 254   sqlite3 db test.db
 255   execsql { ATTACH 'test.db2' AS aux; }
 256 } {}
 257 do_eqp_test 5.8 {
 258   SELECT * FROM t1, rt WHERE x==id;
 259 } {
 260   QUERY PLAN
 261   |--SCAN TABLE t1
 262   `--SCAN TABLE rt VIRTUAL TABLE INDEX 1:
 263 }
 264
 265 #--------------------------------------------------------------------
 266 # Test that having a second connection drop the sqlite_stat1 table
 267 # before it is required by rtreeConnect() does not cause problems.
 268 #
 269 ifcapable rtree {
 270   reset_db
 271   do_execsql_test 6.1 {
 272     CREATE TABLE t1(x);
 273     CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
 274     INSERT INTO t1 VALUES(1);
 275     INSERT INTO rt VALUES(1,2,3);
 276     ANALYZE;
 277   }
 278   db close
 279   sqlite3 db test.db
 280   do_execsql_test 6.2 { SELECT * FROM t1 } {1}
 281
 282   do_test 6.3 {
 283     sqlite3 db2 test.db
 284     db2 eval { DROP TABLE sqlite_stat1 }
 285     db2 close
 286     execsql { SELECT * FROM rt }
 287   } {1 2.0 3.0}
 288   db close
 289 }
 290
 291 #--------------------------------------------------------------------
 292 # Test that queries featuring LEFT or CROSS JOINS are handled correctly.
 293 # Handled correctly in this case means:
 294 #
 295 #   * Terms with prereqs that appear to the left of a LEFT JOIN against
 296 #     the virtual table are always available to xBestIndex.
 297 #
 298 #   * Terms with prereqs that appear to the right of a LEFT JOIN against
 299 #     the virtual table are never available to xBestIndex.
 300 #
 301 # And the same behaviour for CROSS joins.
 302 #
 303 reset_db
 304 do_execsql_test 7.0 {
 305   CREATE TABLE xdir(x1);
 306   CREATE TABLE ydir(y1);
 307   CREATE VIRTUAL TABLE rt USING rtree_i32(id, xmin, xmax, ymin, ymax);
 308
 309   INSERT INTO xdir VALUES(5);
 310   INSERT INTO ydir VALUES(10);
 311
 312   INSERT INTO rt VALUES(1, 2, 7, 12, 14);      -- Not a hit
 313   INSERT INTO rt VALUES(2, 2, 7, 8, 12);       -- A hit!
 314   INSERT INTO rt VALUES(3, 7, 11, 8, 12);      -- Not a hit!
 315   INSERT INTO rt VALUES(4, 5, 5, 10, 10);      -- A hit!
 316
 317 }
 318
 319 proc do_eqp_execsql_test {tn sql res1 res2} {
 320   do_eqp_test $tn.1 $sql $res1
 321   do_execsql_test $tn.2 $sql $res2
 322 }
 323
 324 do_eqp_execsql_test 7.1 {
 325   SELECT id FROM xdir, rt, ydir
 326   ON (y1 BETWEEN ymin AND ymax)
 327   WHERE (x1 BETWEEN xmin AND xmax);
 328 } {
 329   QUERY PLAN
 330   |--SCAN TABLE xdir
 331   |--SCAN TABLE ydir
 332   `--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B2D3B0D1
 333 } {
 334   2 4
 335 }
 336
 337 do_eqp_execsql_test 7.2 {
 338   SELECT * FROM xdir, rt LEFT JOIN ydir
 339   ON (y1 BETWEEN ymin AND ymax)
 340   WHERE (x1 BETWEEN xmin AND xmax);
 341 } {
 342   QUERY PLAN
 343   |--SCAN TABLE xdir
 344   |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1
 345   `--SCAN TABLE ydir
 346 } {
 347   5 1 2 7 12 14 {}
 348   5 2 2 7  8 12 10
 349   5 4 5 5 10 10 10
 350 }
 351
 352 do_eqp_execsql_test 7.3 {
 353   SELECT id FROM xdir, rt CROSS JOIN ydir
 354   ON (y1 BETWEEN ymin AND ymax)
 355   WHERE (x1 BETWEEN xmin AND xmax);
 356 } {
 357   QUERY PLAN
 358   |--SCAN TABLE xdir
 359   |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1
 360   `--SCAN TABLE ydir
 361 } {
 362   2 4
 363 }
 364
 365 do_eqp_execsql_test 7.4 {
 366   SELECT id FROM rt, xdir CROSS JOIN ydir
 367   ON (y1 BETWEEN ymin AND ymax)
 368   WHERE (x1 BETWEEN xmin AND xmax);
 369 } {
 370   QUERY PLAN
 371   |--SCAN TABLE xdir
 372   |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1
 373   `--SCAN TABLE ydir
 374 } {
 375   2 4
 376 }
 377
 378 finish_test