xref: /sqlite-3.40.0/ext/rtree/rtree6.test (revision 7aa3ebee) 
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
14if {![info exists testdir]} {
15  set testdir [file join [file dirname [info script]] .. .. test]
16}
17source $testdir/tester.tcl
18
19ifcapable {!rtree || rtree_int_only} {
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
33proc 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
43proc 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
51do_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
58do_test rtree6-1.2 {
59  rtree_strategy {SELECT * FROM t1 WHERE x1>10}
60} {E0}
61
62do_test rtree6-1.3 {
63  rtree_strategy {SELECT * FROM t1 WHERE x1<10}
64} {C0}
65
66do_test rtree6-1.4 {
67  rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
68} {C0}
69
70do_test rtree6-1.5 {
71  rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
72} {C0}
73
74do_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:C0}
78  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
79}
80
81do_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:C0}
85  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
86}
87
88do_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:}
92  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
93}
94
95do_eqp_test rtree6.2.4.1 {
96  SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
97} {
98  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
99  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
100}
101do_eqp_test rtree6.2.4.2 {
102  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
103} {
104  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
105  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)}
106}
107
108do_eqp_test rtree6.2.5 {
109  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
110} {
111  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:}
112  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
113}
114
115do_execsql_test rtree6-3.1 {
116  CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
117  INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
118  SELECT * FROM t3 WHERE
119    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
120    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
121    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
122    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
123    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
124    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5;
125} {1 1.0 1.0 2.0 2.0}
126
127do_test rtree6.3.2 {
128  rtree_strategy {
129    SELECT * FROM t3 WHERE
130      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
131      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
132      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
133      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
134  }
135} {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
136do_test rtree6.3.3 {
137  rtree_strategy {
138    SELECT * FROM t3 WHERE
139      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
140      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
141      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
142      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
143      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
144      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
145  }
146} {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
147
148do_execsql_test rtree6-3.4 {
149  SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
150} {}
151do_execsql_test rtree6-3.5 {
152  SELECT * FROM t3 WHERE
153    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
154    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
155    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
156    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
157    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
158    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>1.1
159} {}
160
161
162finish_test
163