Home | History | Annotate | Download | only in rtree 
      1 # 2008 Sep 1
      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 # 
     12 #
     13 
     14 if {![info exists testdir]} {
     15   set testdir [file join [file dirname [info script]] .. .. test]
     16 } 
     17 source $testdir/tester.tcl
     18 
     19 ifcapable !rtree {
     20   finish_test
     21   return
     22 }
     23 
     24 #   Operator    Byte Value
     25 #   ----------------------
     26 #      =        0x41 ('A')
     27 #     <=        0x42 ('B')
     28 #      <        0x43 ('C')
     29 #     >=        0x44 ('D')
     30 #      >        0x45 ('E')
     31 #   ----------------------
     32 
     33 proc rtree_strategy {sql} {
     34   set ret [list]
     35   db eval "explain $sql" a {
     36     if {$a(opcode) eq "VFilter"} {
     37       lappend ret $a(p4)
     38     }
     39   }
     40   set ret
     41 }
     42 
     43 proc query_plan {sql} {
     44   set ret [list]
     45   db eval "explain query plan $sql" a {
     46     lappend ret $a(detail)
     47   }
     48   set ret
     49 }
     50 
     51 do_test rtree6-1.1 {
     52   execsql {
     53     CREATE TABLE t2(k INTEGER PRIMARY KEY, v);
     54     CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2);
     55   }
     56 } {}
     57 
     58 do_test rtree6-1.2 {
     59   rtree_strategy {SELECT * FROM t1 WHERE x1>10}
     60 } {Ea}
     61 
     62 do_test rtree6-1.3 {
     63   rtree_strategy {SELECT * FROM t1 WHERE x1<10}
     64 } {Ca}
     65 
     66 do_test rtree6-1.4 {
     67   rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
     68 } {Ca}
     69 
     70 do_test rtree6-1.5 {
     71   rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
     72 } {Ca}
     73 
     74 do_eqp_test rtree6.2.1 {
     75   SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
     76 } {
     77   0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~0 rows)} 
     78   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
     79 }
     80 
     81 do_eqp_test rtree6.2.2 {
     82   SELECT * FROM t1,t2 WHERE k=ii AND x1<10
     83 } {
     84   0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~0 rows)} 
     85   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
     86 }
     87 
     88 do_eqp_test rtree6.2.3 {
     89   SELECT * FROM t1,t2 WHERE k=ii
     90 } {
     91   0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~0 rows)} 
     92   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
     93 }
     94 
     95 do_eqp_test rtree6.2.4 {
     96   SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
     97 } {
     98   0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb (~0 rows)} 
     99   0 1 1 {SCAN TABLE t2 (~100000 rows)}
    100 }
    101 
    102 do_eqp_test rtree6.2.5 {
    103   SELECT * FROM t1,t2 WHERE k=ii AND x1<v
    104 } {
    105   0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~0 rows)} 
    106   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
    107 }
    108 
    109 do_execsql_test rtree6-3.1 {
    110   CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
    111   INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
    112   SELECT * FROM t3 WHERE 
    113     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    114     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    115     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    116     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    117     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    118     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5;
    119 } {1 1.0 1.0 2.0 2.0}
    120 
    121 do_test rtree6.3.2 {
    122   rtree_strategy {
    123     SELECT * FROM t3 WHERE 
    124       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    125       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    126       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    127       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 
    128   }
    129 } {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
    130 do_test rtree6.3.3 {
    131   rtree_strategy {
    132     SELECT * FROM t3 WHERE 
    133       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    134       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    135       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    136       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    137       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    138       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
    139   }
    140 } {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
    141 
    142 do_execsql_test rtree6-3.4 {
    143   SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
    144 } {}
    145 do_execsql_test rtree6-3.5 {
    146   SELECT * FROM t3 WHERE 
    147     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    148     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    149     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    150     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    151     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
    152     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>1.1
    153 } {}
    154 
    155 
    156 finish_test
    157