xref: /sqlite-3.40.0/ext/rtree/rtree6.test (revision f2fcd075) 
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_test rtree6.2.1 {
75  query_plan {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
76} [list \
77  {TABLE t1 VIRTUAL TABLE INDEX 2:Ca} \
78  {TABLE t2 USING PRIMARY KEY}        \
79]
80
81do_test rtree6.2.2 {
82  query_plan {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
83} [list \
84  {TABLE t1 VIRTUAL TABLE INDEX 2:Ca} \
85  {TABLE t2 USING PRIMARY KEY}        \
86]
87
88do_test rtree6.2.3 {
89  query_plan {SELECT * FROM t1,t2 WHERE k=ii}
90} [list \
91  {TABLE t1 VIRTUAL TABLE INDEX 2:}   \
92  {TABLE t2 USING PRIMARY KEY}        \
93]
94
95do_test rtree6.2.4 {
96  query_plan {SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10}
97} [list \
98  {TABLE t1 VIRTUAL TABLE INDEX 2:CaEb}   \
99  {TABLE t2}                              \
100]
101
102do_test rtree6.2.5 {
103  query_plan {SELECT * FROM t1,t2 WHERE k=ii AND x1<v}
104} [list \
105  {TABLE t1 VIRTUAL TABLE INDEX 2:}   \
106  {TABLE t2 USING PRIMARY KEY}        \
107]
108
109finish_test
110