Debian Codegraph @

test/whereF.test

raw
1 # 2012 November 9
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 # Test cases for query planning decisions.
13
14
15 #
16 # The tests in this file demonstrate the behaviour of the query planner
17 # in determining the order in which joined tables are scanned.
18 #
19 # Assume there are two tables being joined - t1 and t2. Each has a cost
20 # if it is the outer loop, and a cost if it is the inner loop. As follows:
21 #
22 # t1(outer) - cost of scanning t1 as the outer loop.
23 # t1(inner) - cost of scanning t1 as the inner loop.
24 # t2(outer) - cost of scanning t2 as the outer loop.
25 # t2(inner) - cost of scanning t2 as the inner loop.
26 #
27 # Depending on the order in which the planner nests the scans, the total
28 # cost of the join query is one of:
29 #
30 # t1(outer) * t2(inner)
31 # t2(outer) * t1(inner)
32 #
33 # The tests in this file attempt to verify that the planner nests joins in
34 # the correct order when the following are true:
35 #
36 # + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer)
37 # + t1(outer) < t2(outer)
38 #
39 # In other words, when the best overall query plan has t2 as the outer loop,
40 # but when the outer loop is considered independent of the inner, t1 is the
41 # most efficient choice.
42 #
43 # In order to make them more predictable, automatic indexes are turned off for
44 # the tests in this file.
45 #
46
47 set testdir [file dirname $argv0]
48 source $testdir/tester.tcl
49 set testprefix whereF
50
51 do_execsql_test 1.0 {
52 PRAGMA automatic_index = 0;
53 CREATE TABLE t1(a, b, c);
54 CREATE TABLE t2(d, e, f);
55 CREATE UNIQUE INDEX i1 ON t1(a);
56 CREATE UNIQUE INDEX i2 ON t2(d);
57 } {}
58
59 foreach {tn sql} {
60 1 "SELECT * FROM t1, t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
61 2 "SELECT * FROM t2, t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
62 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
63 } {
64 do_test 1.$tn {
65 db eval "EXPLAIN QUERY PLAN $sql"
66 } {/.*SCAN t2\y.*SEARCH t1\y.*/}
67 }
68
69 do_execsql_test 2.0 {
70 DROP TABLE t1;
71 DROP TABLE t2;
72 CREATE TABLE t1(a, b, c);
73 CREATE TABLE t2(d, e, f);
74
75 CREATE UNIQUE INDEX i1 ON t1(a);
76 CREATE UNIQUE INDEX i2 ON t1(b);
77 CREATE UNIQUE INDEX i3 ON t2(d);
78 } {}
79
80 foreach {tn sql} {
81 1 "SELECT * FROM t1, t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
82 2 "SELECT * FROM t2, t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
83 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
84 } {
85 do_test 2.$tn {
86 db eval "EXPLAIN QUERY PLAN $sql"
87 } {/.*SCAN t2\y.*SEARCH t1\y.*/}
88 }
89
90 do_execsql_test 3.0 {
91 DROP TABLE t1;
92 DROP TABLE t2;
93 CREATE TABLE t1(a, b, c);
94 CREATE TABLE t2(d, e, f);
95
96 CREATE UNIQUE INDEX i1 ON t1(a, b);
97 CREATE INDEX i2 ON t2(d);
98 } {}
99
100 foreach {tn sql} {
101 1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2
102 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
103
104 2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1
105 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
106
107 3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1
108 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
109 } {
110 do_test 3.$tn {
111 db eval "EXPLAIN QUERY PLAN $sql"
112 } {/.*SCAN t2\y.*SEARCH t1\y.*/}
113 }
114
115 do_execsql_test 4.0 {
116 CREATE TABLE t4(a,b,c,d,e, PRIMARY KEY(a,b,c));
117 CREATE INDEX t4adc ON t4(a,d,c);
118 CREATE UNIQUE INDEX t4aebc ON t4(a,e,b,c);
119 EXPLAIN QUERY PLAN SELECT rowid FROM t4 WHERE a=? AND b=?;
120 } {/a=. AND b=./}
121
122 #-------------------------------------------------------------------------
123 # Test the following case:
124 #
125 # ... FROM t1, t2 WHERE (
126 # t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
127 # )
128 #
129 # where there is an index on t2(f2). The planner should use "t1" as the
130 # outer loop. The inner loop, on "t2", is an OR optimization. One pass
131 # for:
132 #
133 # t2.rowid = $1
134 #
135 # and another for:
136 #
137 # t2.f2=$1 AND $1!=-1
138 #
139 # the test is to ensure that on the second pass, the ($1!=-1) condition
140 # is tested before any seek operations are performed - i.e. outside of
141 # the loop through the f2=$1 range of the t2(f2) index.
142 #
143 reset_db
144 do_execsql_test 5.0 {
145 CREATE TABLE t1(f1);
146 CREATE TABLE t2(f2);
147 CREATE INDEX t2f ON t2(f2);
148
149 INSERT INTO t1 VALUES(-1);
150 INSERT INTO t1 VALUES(-1);
151 INSERT INTO t1 VALUES(-1);
152 INSERT INTO t1 VALUES(-1);
153
154 WITH w(i) AS (
155 SELECT 1 UNION ALL SELECT i+1 FROM w WHERE i<1000
156 )
157 INSERT INTO t2 SELECT -1 FROM w;
158 }
159
160 do_execsql_test 5.1 {
161 SELECT count(*) FROM t1, t2 WHERE t2.rowid = +t1.rowid
162 } {4}
163 do_test 5.2 { expr [db status vmstep]<200 } 1
164
165 do_execsql_test 5.3 {
166 SELECT count(*) FROM t1, t2 WHERE (
167 t2.rowid = +t1.rowid OR t2.f2 = t1.f1
168 )
169 } {4000}
170 do_test 5.4 { expr [db status vmstep]>1000 } 1
171
172 do_execsql_test 5.5 {
173 SELECT count(*) FROM t1, t2 WHERE (
174 t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
175 )
176 } {4}
177 do_test 5.6 { expr [db status vmstep]<200 } 1
178
179 # 2017-09-04 ticket b899b6042f97f52d
180 # Segfault on correlated subquery...
181 #
182 ifcapable json1&&vtab {
183 do_execsql_test 6.1 {
184 CREATE TABLE t6(x);
185 SELECT * FROM t6 WHERE 1 IN (SELECT value FROM json_each(x));
186 } {}
187
188 do_execsql_test 6.2 {
189 DROP TABLE t6;
190 CREATE TABLE t6(a,b,c);
191 INSERT INTO t6 VALUES
192 (0,null,'{"a":0,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'),
193 (1,null,'{"a":1,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'),
194 (2,null,'{"a":9,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}');
195 SELECT * FROM t6
196 WHERE (EXISTS (SELECT 1 FROM json_each(t6.c) AS x WHERE x.value=1));
197 } {1 {} {{"a":1,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}}}
198
199 # Another test case derived from a posting by Wout Mertens on the
200 # sqlite-users mailing list on 2017-10-04.
201 do_execsql_test 6.3 {
202 DROP TABLE IF EXISTS t;
203 CREATE TABLE t(json JSON);
204 SELECT * FROM t
205 WHERE(EXISTS(SELECT 1 FROM json_each(t.json,"$.foo") j
206 WHERE j.value = 'meep'));
207 } {}
208 do_execsql_test 6.4 {
209 INSERT INTO t VALUES('{"xyzzy":null}');
210 INSERT INTO t VALUES('{"foo":"meep","other":12345}');
211 INSERT INTO t VALUES('{"foo":"bingo","alt":5.25}');
212 SELECT * FROM t
213 WHERE(EXISTS(SELECT 1 FROM json_each(t.json,"$.foo") j
214 WHERE j.value = 'meep'));
215 } {{{"foo":"meep","other":12345}}}
216 }
217
218 # 2018-01-27
219 # Ticket https://sqlite.org/src/tktview/ec32177c99ccac2b180fd3ea2083
220 # Incorrect result when using the new OR clause factoring optimization
221 #
222 # This is the original test case as reported on the sqlite-users mailing
223 # list
224 #
225 do_execsql_test 7.1 {
226 DROP TABLE IF EXISTS cd;
227 CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer );
228 CREATE INDEX cd_idx_genreid ON cd (genreid);
229 INSERT INTO cd ( cdid, genreid ) VALUES
230 ( 1, 1 ),
231 ( 2, NULL ),
232 ( 3, NULL ),
233 ( 4, NULL ),
234 ( 5, NULL );
235
236 SELECT cdid
237 FROM cd me
238 WHERE 2 > (
239 SELECT COUNT( * )
240 FROM cd rownum__emulation
241 WHERE
242 (
243 me.genreid IS NOT NULL
244 AND
245 rownum__emulation.genreid IS NULL
246 )
247 OR
248 (
249 me.genreid IS NOT NULL
250 AND
251 rownum__emulation.genreid IS NOT NULL
252 AND
253 rownum__emulation.genreid < me.genreid
254 )
255 OR
256 (
257 ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL
258 AND rownum__emulation.genreid IS NULL ) )
259 AND
260 rownum__emulation.cdid > me.cdid
261 )
262 );
263 } {4 5}
264
265 # Simplified test cases from the ticket
266 #
267 do_execsql_test 7.2 {
268 DROP TABLE IF EXISTS t1;
269 DROP TABLE IF EXISTS t2;
270 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
271 INSERT INTO t1(a,b) VALUES(1,1);
272 CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb);
273 INSERT INTO t2(aa,bb) VALUES(1,1),(2,NULL),(3,NULL);
274 SELECT (
275 SELECT COUNT(*) FROM t2
276 WHERE ( t1.b IS NOT NULL AND t2.bb IS NULL )
277 OR ( t2.bb < t1.b )
278 OR ( t1.b IS t2.bb AND t2.aa > t1.a )
279 )
280 FROM t1;
281 } {2}
282
283 # The fix for ticket ec32177c99ccac2b180fd3ea2083 only makes a difference
284 # in the output when there is a TERM_VNULL entry in the WhereClause array.
285 # And TERM_VNULL entries are only generated when compiling with
286 # SQLITE_ENABLE_STAT4. Nevertheless, it is correct that TERM_VIRTUAL terms
287 # should not participate in the factoring optimization. In all cases other
288 # than TERM_VNULL, participation is harmless, but it does consume a few
289 # extra CPU cycles.
290 #
291 # The following test verifies that the TERM_VIRTUAL terms resulting from
292 # a GLOB operator do not appear anywhere in the generated code. This
293 # confirms that the problem is fixed, even on builds that omit STAT4.
294 #
295 do_execsql_test 7.3 {
296 DROP TABLE IF EXISTS t1;
297 DROP TABLE IF EXISTS t2;
298 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
299 INSERT INTO t1(a,b) VALUES(1,'abcxyz');
300 CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb TEXT);
301 INSERT INTO t2(aa,bb) VALUES(1,'abc'),(2,'wxyz'),(3,'xyz');
302 CREATE INDEX t2bb ON t2(bb);
303 EXPLAIN SELECT (
304 SELECT COUNT(*) FROM t2
305 WHERE ( t1.b GLOB 'a*z' AND t2.bb='xyz' )
306 OR ( t2.bb = t1.b )
307 OR ( t2.aa = t1.a )
308 )
309 FROM t1;
310 } {~/ (Lt|Ge) /}
311
312 finish_test