Annotation of embedaddon/sqlite3/ext/rtree/rtree6.test, revision 1.1.1.1
1.1 misho 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:
14: if {![info exists testdir]} {
15: set testdir [file join [file dirname [info script]] .. .. test]
16: }
17: source $testdir/tester.tcl
18:
19: ifcapable !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:
33: proc 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:
43: proc 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:
51: do_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:
58: do_test rtree6-1.2 {
59: rtree_strategy {SELECT * FROM t1 WHERE x1>10}
60: } {Ea}
61:
62: do_test rtree6-1.3 {
63: rtree_strategy {SELECT * FROM t1 WHERE x1<10}
64: } {Ca}
65:
66: do_test rtree6-1.4 {
67: rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
68: } {Ca}
69:
70: do_test rtree6-1.5 {
71: rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
72: } {Ca}
73:
74: do_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:
81: do_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:
88: do_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:
95: do_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:
102: do_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:
109: do_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:
121: do_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}
130: do_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:
142: do_execsql_test rtree6-3.4 {
143: SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
144: } {}
145: do_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:
156: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to rtree6.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / ext / rtree