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