• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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 {
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} {Ea}
61
62do_test rtree6-1.3 {
63  rtree_strategy {SELECT * FROM t1 WHERE x1<10}
64} {Ca}
65
66do_test rtree6-1.4 {
67  rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
68} {Ca}
69
70do_test rtree6-1.5 {
71  rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
72} {Ca}
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:Ca (~0 rows)}
78  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
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:Ca (~0 rows)}
85  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
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: (~0 rows)}
92  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
93}
94
95do_eqp_test rtree6.2.4 {
96  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
97} {
98  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb (~0 rows)}
99  0 1 1 {SCAN TABLE t2 (~100000 rows)}
100}
101
102do_eqp_test rtree6.2.5 {
103  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
104} {
105  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~0 rows)}
106  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
107}
108
109do_execsql_test rtree6-3.1 {
110  CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
111  INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
112  SELECT * FROM t3 WHERE
113    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
114    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
115    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
116    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
117    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
118    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5;
119} {1 1.0 1.0 2.0 2.0}
120
121do_test rtree6.3.2 {
122  rtree_strategy {
123    SELECT * FROM t3 WHERE
124      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
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
128  }
129} {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
130do_test rtree6.3.3 {
131  rtree_strategy {
132    SELECT * FROM t3 WHERE
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 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
139  }
140} {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
141
142do_execsql_test rtree6-3.4 {
143  SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
144} {}
145do_execsql_test rtree6-3.5 {
146  SELECT * FROM t3 WHERE
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 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>1.1
153} {}
154
155
156finish_test
157