Debian Codegraph @

ext/rtree/rtree6.test

raw
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 set testprefix rtree6
19
20 ifcapable {!rtree || rtree_int_only} {
21 finish_test
22 return
23 }
24
25 # Operator Byte Value
26 # ----------------------
27 # = 0x41 ('A')
28 # <= 0x42 ('B')
29 # < 0x43 ('C')
30 # >= 0x44 ('D')
31 # > 0x45 ('E')
32 # ----------------------
33
34 proc rtree_strategy {sql} {
35 set ret [list]
36 db eval "explain $sql" a {
37 if {$a(opcode) eq "VFilter"} {
38 lappend ret $a(p4)
39 }
40 }
41 set ret
42 }
43
44 proc query_plan {sql} {
45 set ret [list]
46 db eval "explain query plan $sql" a {
47 lappend ret $a(detail)
48 }
49 set ret
50 }
51
52 do_test rtree6-1.1 {
53 execsql {
54 CREATE TABLE t2(k INTEGER PRIMARY KEY, v);
55 CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2);
56 }
57 } {}
58
59 do_test rtree6-1.2 {
60 rtree_strategy {SELECT * FROM t1 WHERE x1>10}
61 } {E0}
62 do_test rtree6-1.2.1 {
63 rtree_strategy {SELECT * FROM t1 WHERE x1>10 AND x2 LIKE '%x%'}
64 } {E0}
65
66 do_test rtree6-1.3 {
67 rtree_strategy {SELECT * FROM t1 WHERE x1<10}
68 } {C0}
69
70 do_test rtree6-1.4 {
71 rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
72 } {C0}
73
74 do_test rtree6-1.5 {
75 rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
76 } {C0}
77
78 do_eqp_test rtree6.2.1 {
79 SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
80 } {
81 QUERY PLAN
82 |--SCAN t1 VIRTUAL TABLE INDEX 2:C0
83 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
84 }
85
86 do_eqp_test rtree6.2.2 {
87 SELECT * FROM t1,t2 WHERE k=ii AND x1<10
88 } {
89 QUERY PLAN
90 |--SCAN t1 VIRTUAL TABLE INDEX 2:C0
91 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
92 }
93
94 do_eqp_test rtree6.2.3 {
95 SELECT * FROM t1,t2 WHERE k=ii
96 } {
97 QUERY PLAN
98 |--SCAN t1 VIRTUAL TABLE INDEX 2:
99 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
100 }
101
102 do_eqp_test rtree6.2.4.1 {
103 SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
104 } {
105 QUERY PLAN
106 |--SCAN t1 VIRTUAL TABLE INDEX 2:C0E1
107 |--BLOOM FILTER ON t2 (v=?)
108 `--SEARCH t2 USING AUTOMATIC COVERING INDEX (v=?)
109 }
110 do_eqp_test rtree6.2.4.2 {
111 SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
112 } {
113 QUERY PLAN
114 |--SCAN t1 VIRTUAL TABLE INDEX 2:C0E1
115 |--BLOOM FILTER ON t2 (v=?)
116 `--SEARCH t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)
117 }
118
119 do_eqp_test rtree6.2.5 {
120 SELECT * FROM t1,t2 WHERE k=ii AND x1<v
121 } {
122 QUERY PLAN
123 |--SCAN t1 VIRTUAL TABLE INDEX 2:
124 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
125 }
126
127 do_execsql_test rtree6-3.1 {
128 CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
129 INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
130 SELECT * FROM t3 WHERE
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 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;
137 } {1 1.0 1.0 2.0 2.0}
138
139 do_test rtree6.3.2 {
140 rtree_strategy {
141 SELECT * FROM t3 WHERE
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 AND x1>0.5 AND
145 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
146 }
147 } {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
148 do_test rtree6.3.3 {
149 rtree_strategy {
150 SELECT * FROM t3 WHERE
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>0.5 AND x1>0.5 AND
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
157 }
158 } {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
159
160 do_execsql_test rtree6-3.4 {
161 SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
162 } {}
163 do_execsql_test rtree6-3.5 {
164 SELECT * FROM t3 WHERE
165 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
166 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
167 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
168 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
169 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
170 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>1.1
171 } {}
172
173 #-------------------------------------------------------------------------
174 reset_db
175 do_execsql_test 4.0 {
176 CREATE VIRTUAL TABLE t1 USING rtree(id,x0,x1,y0,y1);
177 }
178 do_execsql_test 4.1 {
179 DELETE FROM t1 WHERE x0>1 AND x1<2 OR y0<92;
180 }
181
182 expand_all_sql db
183 finish_test