xref: /sqlite-3.40.0/ext/rtree/rtree6.test (revision bd462bcc) 
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}
61do_test rtree6-1.2.1 {
62  rtree_strategy {SELECT * FROM t1 WHERE x1>10 AND x2 LIKE '%x%'}
63} {E0}
64
65do_test rtree6-1.3 {
66  rtree_strategy {SELECT * FROM t1 WHERE x1<10}
67} {C0}
68
69do_test rtree6-1.4 {
70  rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
71} {C0}
72
73do_test rtree6-1.5 {
74  rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
75} {C0}
76
77do_eqp_test rtree6.2.1 {
78  SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
79} {
80  QUERY PLAN
81  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0
82  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
83}
84
85do_eqp_test rtree6.2.2 {
86  SELECT * FROM t1,t2 WHERE k=ii AND x1<10
87} {
88  QUERY PLAN
89  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0
90  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
91}
92
93do_eqp_test rtree6.2.3 {
94  SELECT * FROM t1,t2 WHERE k=ii
95} {
96  QUERY PLAN
97  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:
98  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
99}
100
101do_eqp_test rtree6.2.4.1 {
102  SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
103} {
104  QUERY PLAN
105  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1
106  `--SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)
107}
108do_eqp_test rtree6.2.4.2 {
109  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
110} {
111  QUERY PLAN
112  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1
113  `--SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)
114}
115
116do_eqp_test rtree6.2.5 {
117  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
118} {
119  QUERY PLAN
120  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:
121  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
122}
123
124do_execsql_test rtree6-3.1 {
125  CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
126  INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
127  SELECT * FROM t3 WHERE
128    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
129    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
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;
134} {1 1.0 1.0 2.0 2.0}
135
136do_test rtree6.3.2 {
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
143  }
144} {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
145do_test rtree6.3.3 {
146  rtree_strategy {
147    SELECT * FROM t3 WHERE
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>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
154  }
155} {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
156
157do_execsql_test rtree6-3.4 {
158  SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
159} {}
160do_execsql_test rtree6-3.5 {
161  SELECT * FROM t3 WHERE
162    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
163    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
164    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
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>1.1
168} {}
169
170expand_all_sql db
171finish_test
172