xref: /sqlite-3.40.0/ext/rtree/rtree6.test (revision b80bb6ce) 
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  QUERY PLAN
78  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0
79  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
80}
81
82do_eqp_test rtree6.2.2 {
83  SELECT * FROM t1,t2 WHERE k=ii AND x1<10
84} {
85  QUERY PLAN
86  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0
87  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
88}
89
90do_eqp_test rtree6.2.3 {
91  SELECT * FROM t1,t2 WHERE k=ii
92} {
93  QUERY PLAN
94  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:
95  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
96}
97
98do_eqp_test rtree6.2.4.1 {
99  SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
100} {
101  QUERY PLAN
102  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1
103  `--SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)
104}
105do_eqp_test rtree6.2.4.2 {
106  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
107} {
108  QUERY PLAN
109  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1
110  `--SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)
111}
112
113do_eqp_test rtree6.2.5 {
114  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
115} {
116  QUERY PLAN
117  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:
118  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
119}
120
121do_execsql_test rtree6-3.1 {
122  CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
123  INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
124  SELECT * FROM t3 WHERE
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 AND
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;
131} {1 1.0 1.0 2.0 2.0}
132
133do_test rtree6.3.2 {
134  rtree_strategy {
135    SELECT * FROM t3 WHERE
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 AND x1>0.5 AND
139      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
140  }
141} {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
142do_test rtree6.3.3 {
143  rtree_strategy {
144    SELECT * FROM t3 WHERE
145      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
146      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
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
151  }
152} {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
153
154do_execsql_test rtree6-3.4 {
155  SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
156} {}
157do_execsql_test rtree6-3.5 {
158  SELECT * FROM t3 WHERE
159    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
160    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
161    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
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>1.1
165} {}
166
167expand_all_sql db
168finish_test
169