| blob | d073074d439bf447c130b38f9eab29a184b0b50b |
1 # 2008 December 30
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 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the multi-index OR clause optimizer.
13 #
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
18 ifcapable !or_opt||!compound {
19 finish_test
20 return
21 }
23 # Evaluate SQL. Return the result set followed by the
24 # and the number of full-scan steps.
25 #
26 proc count_steps {sql} {
27 set r [db eval $sql]
28 lappend r scan [db status step] sort [db status sort]
29 }
32 # Construct test data.
33 #
34 do_test where9-1.1 {
35 db eval {
36 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
37 INSERT INTO t1 VALUES(1,11,1001,1.001,100.1,'bcdefghij','yxwvuts');
38 INSERT INTO t1 VALUES(2,22,1001,2.002,100.1,'cdefghijk','yxwvuts');
39 INSERT INTO t1 VALUES(3,33,1001,3.003,100.1,'defghijkl','xwvutsr');
40 INSERT INTO t1 VALUES(4,44,2002,4.004,200.2,'efghijklm','xwvutsr');
41 INSERT INTO t1 VALUES(5,55,2002,5.005,200.2,'fghijklmn','xwvutsr');
42 INSERT INTO t1 VALUES(6,66,2002,6.006,200.2,'ghijklmno','xwvutsr');
43 INSERT INTO t1 VALUES(7,77,3003,7.007,300.3,'hijklmnop','xwvutsr');
44 INSERT INTO t1 VALUES(8,88,3003,8.008,300.3,'ijklmnopq','wvutsrq');
45 INSERT INTO t1 VALUES(9,99,3003,9.009,300.3,'jklmnopqr','wvutsrq');
46 INSERT INTO t1 VALUES(10,110,4004,10.01,400.4,'klmnopqrs','wvutsrq');
47 INSERT INTO t1 VALUES(11,121,4004,11.011,400.4,'lmnopqrst','wvutsrq');
48 INSERT INTO t1 VALUES(12,132,4004,12.012,400.4,'mnopqrstu','wvutsrq');
49 INSERT INTO t1 VALUES(13,143,5005,13.013,500.5,'nopqrstuv','vutsrqp');
50 INSERT INTO t1 VALUES(14,154,5005,14.014,500.5,'opqrstuvw','vutsrqp');
51 INSERT INTO t1 VALUES(15,165,5005,15.015,500.5,'pqrstuvwx','vutsrqp');
52 INSERT INTO t1 VALUES(16,176,6006,16.016,600.6,'qrstuvwxy','vutsrqp');
53 INSERT INTO t1 VALUES(17,187,6006,17.017,600.6,'rstuvwxyz','vutsrqp');
54 INSERT INTO t1 VALUES(18,198,6006,18.018,600.6,'stuvwxyza','utsrqpo');
55 INSERT INTO t1 VALUES(19,209,7007,19.019,700.7,'tuvwxyzab','utsrqpo');
56 INSERT INTO t1 VALUES(20,220,7007,20.02,700.7,'uvwxyzabc','utsrqpo');
57 INSERT INTO t1 VALUES(21,231,7007,21.021,700.7,'vwxyzabcd','utsrqpo');
58 INSERT INTO t1 VALUES(22,242,8008,22.022,800.8,'wxyzabcde','utsrqpo');
59 INSERT INTO t1 VALUES(23,253,8008,23.023,800.8,'xyzabcdef','tsrqpon');
60 INSERT INTO t1 VALUES(24,264,8008,24.024,800.8,'yzabcdefg','tsrqpon');
61 INSERT INTO t1 VALUES(25,275,9009,25.025,900.9,'zabcdefgh','tsrqpon');
62 INSERT INTO t1 VALUES(26,286,9009,26.026,900.9,'abcdefghi','tsrqpon');
63 INSERT INTO t1 VALUES(27,297,9009,27.027,900.9,'bcdefghij','tsrqpon');
64 INSERT INTO t1 VALUES(28,308,10010,28.028,1001.0,'cdefghijk','srqponm');
65 INSERT INTO t1 VALUES(29,319,10010,29.029,1001.0,'defghijkl','srqponm');
66 INSERT INTO t1 VALUES(30,330,10010,30.03,1001.0,'efghijklm','srqponm');
67 INSERT INTO t1 VALUES(31,341,11011,31.031,1101.1,'fghijklmn','srqponm');
68 INSERT INTO t1 VALUES(32,352,11011,32.032,1101.1,'ghijklmno','srqponm');
69 INSERT INTO t1 VALUES(33,363,11011,33.033,1101.1,'hijklmnop','rqponml');
70 INSERT INTO t1 VALUES(34,374,12012,34.034,1201.2,'ijklmnopq','rqponml');
71 INSERT INTO t1 VALUES(35,385,12012,35.035,1201.2,'jklmnopqr','rqponml');
72 INSERT INTO t1 VALUES(36,396,12012,36.036,1201.2,'klmnopqrs','rqponml');
73 INSERT INTO t1 VALUES(37,407,13013,37.037,1301.3,'lmnopqrst','rqponml');
74 INSERT INTO t1 VALUES(38,418,13013,38.038,1301.3,'mnopqrstu','qponmlk');
75 INSERT INTO t1 VALUES(39,429,13013,39.039,1301.3,'nopqrstuv','qponmlk');
76 INSERT INTO t1 VALUES(40,440,14014,40.04,1401.4,'opqrstuvw','qponmlk');
77 INSERT INTO t1 VALUES(41,451,14014,41.041,1401.4,'pqrstuvwx','qponmlk');
78 INSERT INTO t1 VALUES(42,462,14014,42.042,1401.4,'qrstuvwxy','qponmlk');
79 INSERT INTO t1 VALUES(43,473,15015,43.043,1501.5,'rstuvwxyz','ponmlkj');
80 INSERT INTO t1 VALUES(44,484,15015,44.044,1501.5,'stuvwxyza','ponmlkj');
81 INSERT INTO t1 VALUES(45,495,15015,45.045,1501.5,'tuvwxyzab','ponmlkj');
82 INSERT INTO t1 VALUES(46,506,16016,46.046,1601.6,'uvwxyzabc','ponmlkj');
83 INSERT INTO t1 VALUES(47,517,16016,47.047,1601.6,'vwxyzabcd','ponmlkj');
84 INSERT INTO t1 VALUES(48,528,16016,48.048,1601.6,'wxyzabcde','onmlkji');
85 INSERT INTO t1 VALUES(49,539,17017,49.049,1701.7,'xyzabcdef','onmlkji');
86 INSERT INTO t1 VALUES(50,550,17017,50.05,1701.7,'yzabcdefg','onmlkji');
87 INSERT INTO t1 VALUES(51,561,17017,51.051,1701.7,'zabcdefgh','onmlkji');
88 INSERT INTO t1 VALUES(52,572,18018,52.052,1801.8,'abcdefghi','onmlkji');
89 INSERT INTO t1 VALUES(53,583,18018,53.053,1801.8,'bcdefghij','nmlkjih');
90 INSERT INTO t1 VALUES(54,594,18018,54.054,1801.8,'cdefghijk','nmlkjih');
91 INSERT INTO t1 VALUES(55,605,19019,55.055,1901.9,'defghijkl','nmlkjih');
92 INSERT INTO t1 VALUES(56,616,19019,56.056,1901.9,'efghijklm','nmlkjih');
93 INSERT INTO t1 VALUES(57,627,19019,57.057,1901.9,'fghijklmn','nmlkjih');
94 INSERT INTO t1 VALUES(58,638,20020,58.058,2002.0,'ghijklmno','mlkjihg');
95 INSERT INTO t1 VALUES(59,649,20020,59.059,2002.0,'hijklmnop','mlkjihg');
96 INSERT INTO t1 VALUES(60,660,20020,60.06,2002.0,'ijklmnopq','mlkjihg');
97 INSERT INTO t1 VALUES(61,671,21021,61.061,2102.1,'jklmnopqr','mlkjihg');
98 INSERT INTO t1 VALUES(62,682,21021,62.062,2102.1,'klmnopqrs','mlkjihg');
99 INSERT INTO t1 VALUES(63,693,21021,63.063,2102.1,'lmnopqrst','lkjihgf');
100 INSERT INTO t1 VALUES(64,704,22022,64.064,2202.2,'mnopqrstu','lkjihgf');
101 INSERT INTO t1 VALUES(65,715,22022,65.065,2202.2,'nopqrstuv','lkjihgf');
102 INSERT INTO t1 VALUES(66,726,22022,66.066,2202.2,'opqrstuvw','lkjihgf');
103 INSERT INTO t1 VALUES(67,737,23023,67.067,2302.3,'pqrstuvwx','lkjihgf');
104 INSERT INTO t1 VALUES(68,748,23023,68.068,2302.3,'qrstuvwxy','kjihgfe');
105 INSERT INTO t1 VALUES(69,759,23023,69.069,2302.3,'rstuvwxyz','kjihgfe');
106 INSERT INTO t1 VALUES(70,770,24024,70.07,2402.4,'stuvwxyza','kjihgfe');
107 INSERT INTO t1 VALUES(71,781,24024,71.071,2402.4,'tuvwxyzab','kjihgfe');
108 INSERT INTO t1 VALUES(72,792,24024,72.072,2402.4,'uvwxyzabc','kjihgfe');
109 INSERT INTO t1 VALUES(73,803,25025,73.073,2502.5,'vwxyzabcd','jihgfed');
110 INSERT INTO t1 VALUES(74,814,25025,74.074,2502.5,'wxyzabcde','jihgfed');
111 INSERT INTO t1 VALUES(75,825,25025,75.075,2502.5,'xyzabcdef','jihgfed');
112 INSERT INTO t1 VALUES(76,836,26026,76.076,2602.6,'yzabcdefg','jihgfed');
113 INSERT INTO t1 VALUES(77,847,26026,77.077,2602.6,'zabcdefgh','jihgfed');
114 INSERT INTO t1 VALUES(78,858,26026,78.078,2602.6,'abcdefghi','ihgfedc');
115 INSERT INTO t1 VALUES(79,869,27027,79.079,2702.7,'bcdefghij','ihgfedc');
116 INSERT INTO t1 VALUES(80,880,27027,80.08,2702.7,'cdefghijk','ihgfedc');
117 INSERT INTO t1 VALUES(81,891,27027,81.081,2702.7,'defghijkl','ihgfedc');
118 INSERT INTO t1 VALUES(82,902,28028,82.082,2802.8,'efghijklm','ihgfedc');
119 INSERT INTO t1 VALUES(83,913,28028,83.083,2802.8,'fghijklmn','hgfedcb');
120 INSERT INTO t1 VALUES(84,924,28028,84.084,2802.8,'ghijklmno','hgfedcb');
121 INSERT INTO t1 VALUES(85,935,29029,85.085,2902.9,'hijklmnop','hgfedcb');
122 INSERT INTO t1 VALUES(86,946,29029,86.086,2902.9,'ijklmnopq','hgfedcb');
123 INSERT INTO t1 VALUES(87,957,29029,87.087,2902.9,'jklmnopqr','hgfedcb');
124 INSERT INTO t1 VALUES(88,968,30030,88.088,3003.0,'klmnopqrs','gfedcba');
125 INSERT INTO t1 VALUES(89,979,30030,89.089,3003.0,'lmnopqrst','gfedcba');
126 INSERT INTO t1 VALUES(90,NULL,30030,90.09,3003.0,'mnopqrstu','gfedcba');
127 INSERT INTO t1 VALUES(91,1001,NULL,91.091,3103.1,'nopqrstuv','gfedcba');
128 INSERT INTO t1 VALUES(92,1012,31031,NULL,3103.1,'opqrstuvw','gfedcba');
129 INSERT INTO t1 VALUES(93,1023,31031,93.093,NULL,'pqrstuvwx','fedcbaz');
130 INSERT INTO t1 VALUES(94,1034,32032,94.094,3203.2,NULL,'fedcbaz');
131 INSERT INTO t1 VALUES(95,1045,32032,95.095,3203.2,'rstuvwxyz',NULL);
132 INSERT INTO t1 VALUES(96,NULL,NULL,96.096,3203.2,'stuvwxyza','fedcbaz');
133 INSERT INTO t1 VALUES(97,1067,33033,NULL,NULL,'tuvwxyzab','fedcbaz');
134 INSERT INTO t1 VALUES(98,1078,33033,98.098,3303.3,NULL,NULL);
135 INSERT INTO t1 VALUES(99,NULL,NULL,NULL,NULL,NULL,NULL);
136 CREATE INDEX t1b ON t1(b);
137 CREATE INDEX t1c ON t1(c);
138 CREATE INDEX t1d ON t1(d);
139 CREATE INDEX t1e ON t1(e);
140 CREATE INDEX t1f ON t1(f);
141 CREATE INDEX t1g ON t1(g);
142 CREATE TABLE t2(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
143 INSERT INTO t2 SELECT * FROM t1;
144 CREATE INDEX t2b ON t2(b,c);
145 CREATE INDEX t2c ON t2(c,e);
146 CREATE INDEX t2d ON t2(d,g);
147 CREATE INDEX t2e ON t2(e,f,g);
148 CREATE INDEX t2f ON t2(f,b,d,c);
149 CREATE INDEX t2g ON t2(g,f);
150 CREATE TABLE t3(x,y);
151 INSERT INTO t3 VALUES(1,80);
152 INSERT INTO t3 VALUES(2,80);
153 CREATE TABLE t4(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
154 INSERT INTO t4 SELECT * FROM t1;
155 CREATE INDEX t4b ON t4(b);
156 CREATE INDEX t4c ON t4(c);
157 }
158 } {}
160 do_test where9-1.2.1 {
161 count_steps {
162 SELECT a FROM t1
163 WHERE b IS NULL
164 OR c IS NULL
165 OR d IS NULL
166 ORDER BY a
167 }
168 } {90 91 92 96 97 99 scan 0 sort 1}
169 do_test where9-1.2.2 {
170 count_steps {
171 SELECT a FROM t1
172 WHERE +b IS NULL
173 OR c IS NULL
174 OR d IS NULL
175 ORDER BY a
176 }
177 } {90 91 92 96 97 99 scan 98 sort 0}
178 do_test where9-1.2.3 {
179 count_steps {
180 SELECT a FROM t1
181 WHERE b IS NULL
182 OR +c IS NULL
183 OR d IS NULL
184 ORDER BY a
185 }
186 } {90 91 92 96 97 99 scan 98 sort 0}
187 do_test where9-1.2.4 {
188 count_steps {
189 SELECT a FROM t1
190 WHERE b IS NULL
191 OR c IS NULL
192 OR +d IS NULL
193 ORDER BY a
194 }
195 } {90 91 92 96 97 99 scan 98 sort 0}
196 do_test where9-1.2.5 {
197 count_steps {
198 SELECT a FROM t4
199 WHERE b IS NULL
200 OR c IS NULL
201 OR d IS NULL
202 ORDER BY a
203 }
204 } {90 91 92 96 97 99 scan 98 sort 0}
206 do_test where9-1.3.1 {
207 count_steps {
208 SELECT a FROM t1
209 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
210 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
211 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
212 ORDER BY a
213 }
214 } {90 91 92 97 scan 0 sort 1}
215 do_test where9-1.3.2 {
216 count_steps {
217 SELECT a FROM t4
218 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
219 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
220 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
221 ORDER BY a
222 }
223 } {90 91 92 97 scan 98 sort 0}
224 do_test where9-1.3.3 {
225 count_steps {
226 SELECT a FROM t4
227 WHERE (b NOT NULL AND c NOT NULL AND d IS NULL)
228 OR (b IS NULL AND c NOT NULL AND d NOT NULL)
229 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
230 ORDER BY a
231 }
232 } {90 91 92 97 scan 98 sort 0}
233 do_test where9-1.3.4 {
234 count_steps {
235 SELECT a FROM (t4)
236 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
237 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
238 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
239 ORDER BY a
240 }
241 } {90 91 92 97 scan 98 sort 0}
243 do_test where9-1.4 {
244 count_steps {
245 SELECT a FROM t1
246 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
247 ORDER BY a
248 }
249 } {87 88 89 90 91 scan 0 sort 1}
250 do_test where9-1.5 {
251 # When this test was originally written, SQLite used a rowset object
252 # to optimize the "ORDER BY a" clause. Now that it is using a rowhash,
253 # this is not possible. So we have to comment out one term of the OR
254 # expression in order to prevent SQLite from deeming a full-table
255 # scan to be a better strategy than using multiple indexes, which would
256 # defeat the point of the test.
257 count_steps {
258 SELECT a FROM t1
259 WHERE a=83
260 OR b=913
261 OR c=28028
262 OR (d>=82 AND d<83)
263 /* OR (e>2802 AND e<2803) */
264 OR f='fghijklmn'
265 OR g='hgfedcb'
266 ORDER BY a
267 }
268 } {5 31 57 82 83 84 85 86 87 scan 0 sort 1}
269 do_test where9-1.6 {
270 count_steps {
271 SELECT a FROM t1
272 WHERE b=1012
273 OR (d IS NULL AND e IS NOT NULL)
274 }
275 } {92 scan 0 sort 0}
276 do_test where9-1.7 {
277 count_steps {
278 SELECT a FROM t1
279 WHERE (b=1012 OR (d IS NULL AND e IS NOT NULL))
280 AND f!=g
281 }
282 } {92 scan 0 sort 0}
283 do_test where9-1.8 {
284 count_steps {
285 SELECT a FROM t1
286 WHERE (b=1012 OR (d IS NULL AND e IS NOT NULL))
287 AND f==g
288 }
289 } {scan 0 sort 0}
291 do_test where9-2.1 {
292 count_steps {
293 SELECT t2.a FROM t1, t2
294 WHERE t1.a=80
295 AND (t1.c=t2.c OR t1.d=t2.d)
296 ORDER BY 1
297 }
298 } {79 80 81 scan 0 sort 1}
299 do_test where9-2.2 {
300 count_steps {
301 SELECT t2.a FROM t1, t2
302 WHERE t1.a=80
303 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
304 ORDER BY 1
305 }
306 } {2 28 54 80 scan 0 sort 1}
307 do_test where9-2.3 {
308 count_steps {
309 SELECT coalesce(t2.a,9999)
310 FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f
311 WHERE t1.a=80
312 ORDER BY 1
313 }
314 } {2 28 54 80 scan 0 sort 1}
315 do_test where9-2.4 {
316 count_steps {
317 SELECT coalesce(t2.a,9999)
318 FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
319 WHERE t1.a=80
320 ORDER BY 1
321 }
322 } {9999 scan 0 sort 1}
323 do_test where9-2.5 {
324 count_steps {
325 SELECT t1.a, coalesce(t2.a,9999)
326 FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f
327 WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80)
328 ORDER BY 1
329 }
330 } {80 80 80 2 80 28 80 54 scan 0 sort 1}
331 do_test where9-2.6 {
332 count_steps {
333 SELECT t1.a, coalesce(t2.a,9999)
334 FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
335 WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80)
336 ORDER BY 1
337 }
338 } {80 9999 scan 0 sort 1}
339 do_test where9-2.7 {
340 count_steps {
341 SELECT t3.x, t1.a, coalesce(t2.a,9999)
342 FROM t3 JOIN
343 t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
344 WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
345 ORDER BY 1, 2
346 }
347 } {1 80 9999 2 80 9999 scan 1 sort 1}
348 do_test where9-2.8 {
349 count_steps {
350 SELECT t3.x, t1.a, coalesce(t2.a,9999)
351 FROM t3 JOIN
352 t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f
353 WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
354 ORDER BY 1, 2, 3
355 }
356 } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}
359 ifcapable explain {
360 do_execsql_test where9-3.1 {
361 EXPLAIN QUERY PLAN
362 SELECT t2.a FROM t1, t2
363 WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
364 } {
365 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)}
366 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)}
367 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
368 }
369 do_execsql_test where9-3.2 {
370 EXPLAIN QUERY PLAN
371 SELECT coalesce(t2.a,9999)
372 FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
373 WHERE t1.a=80
374 } {
375 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)}
376 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)}
377 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
378 }
379 }
381 # Make sure that INDEXED BY and multi-index OR clauses play well with
382 # one another.
383 #
384 do_test where9-4.1 {
385 count_steps {
386 SELECT a FROM t1
387 WHERE b>1000
388 AND (c=31031 OR d IS NULL)
389 ORDER BY +a
390 }
391 } {92 93 97 scan 0 sort 1}
392 do_test where9-4.2 {
393 count_steps {
394 SELECT a FROM t1
395 WHERE b>1000
396 AND (c=31031 OR +d IS NULL)
397 ORDER BY +a
398 }
399 } {92 93 97 scan 0 sort 1}
400 do_test where9-4.3 {
401 count_steps {
402 SELECT a FROM t1
403 WHERE +b>1000
404 AND (c=31031 OR d IS NULL)
405 ORDER BY +a
406 }
407 } {92 93 97 scan 0 sort 1}
408 do_test where9-4.4 {
409 count_steps {
410 SELECT a FROM t1 INDEXED BY t1b
411 WHERE b>1000
412 AND (c=31031 OR d IS NULL)
413 ORDER BY +a
414 }
415 } {92 93 97 scan 0 sort 1}
416 do_test where9-4.5 {
417 catchsql {
418 SELECT a FROM t1 INDEXED BY t1b
419 WHERE +b>1000
420 AND (c=31031 OR d IS NULL)
421 ORDER BY +a
422 }
423 } {1 {no query solution}}
424 do_test where9-4.6 {
425 count_steps {
426 SELECT a FROM t1 NOT INDEXED
427 WHERE b>1000
428 AND (c=31031 OR d IS NULL)
429 ORDER BY +a
430 }
431 } {92 93 97 scan 98 sort 1}
432 do_test where9-4.7 {
433 catchsql {
434 SELECT a FROM t1 INDEXED BY t1c
435 WHERE b>1000
436 AND (c=31031 OR d IS NULL)
437 ORDER BY +a
438 }
439 } {1 {no query solution}}
440 do_test where9-4.8 {
441 catchsql {
442 SELECT a FROM t1 INDEXED BY t1d
443 WHERE b>1000
444 AND (c=31031 OR d IS NULL)
445 ORDER BY +a
446 }
447 } {1 {no query solution}}
449 ifcapable explain {
450 # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
451 # the former is an equality test which is expected to return fewer rows.
452 #
453 do_execsql_test where9-5.1 {
454 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
455 } {
456 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)}
457 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)}
458 }
460 # In contrast, b=1000 is preferred over any OR-clause.
461 #
462 do_execsql_test where9-5.2 {
463 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
464 } {
465 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}
466 }
468 # Likewise, inequalities in an AND are preferred over inequalities in
469 # an OR.
470 #
471 do_execsql_test where9-5.3 {
472 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
473 } {
474 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)}
475 }
476 }
478 ############################################################################
479 # Make sure OR-clauses work correctly on UPDATE and DELETE statements.
481 do_test where9-6.2.1 {
482 db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}
483 } {99 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99}
485 do_test where9-6.2.2 { ;# Deletes entries 90 91 92 96 97 99
486 count_steps {
487 BEGIN;
488 DELETE FROM t1
489 WHERE b IS NULL
490 OR c IS NULL
491 OR d IS NULL
492 }
493 } {scan 0 sort 0}
495 do_test where9-6.2.3 {
496 db eval {
497 SELECT count(*) FROM t1 UNION ALL
498 SELECT a FROM t1 WHERE a>=85;
499 ROLLBACK;
500 }
501 } {93 85 86 87 88 89 93 94 95 98}
503 do_test where9-6.2.4 { ;# Deletes entries 90 91 92 96 97 99
504 count_steps {
505 BEGIN;
506 DELETE FROM t1
507 WHERE +b IS NULL
508 OR c IS NULL
509 OR d IS NULL
510 }
511 } {scan 98 sort 0}
513 do_test where9-6.2.5 {
514 db eval {
515 SELECT count(*) FROM t1 UNION ALL
516 SELECT a FROM t1 WHERE a>=85;
517 ROLLBACK;
518 }
519 } {93 85 86 87 88 89 93 94 95 98}
521 do_test where9-6.2.6 {
522 count_steps {
523 BEGIN;
524 UPDATE t1 SET a=a+100
525 WHERE (b IS NULL
526 OR c IS NULL
527 OR d IS NULL)
528 AND a!=92
529 AND a!=97
530 }
531 } {scan 0 sort 0} ;# Add 100 to entries 90 91 96 99
533 do_test where9-6.2.7 {
534 db eval {
535 SELECT count(*) FROM t1 UNION ALL
536 SELECT a FROM t1 WHERE a>=85;
537 ROLLBACK
538 }
539 } {99 85 86 87 88 89 92 93 94 95 97 98 190 191 196 199}
541 do_test where9-6.2.8 { ;# Deletes entries 90 91 92 97 99
542 count_steps {
543 BEGIN;
544 DELETE FROM t1
545 WHERE (b IS NULL
546 OR c IS NULL
547 OR d IS NULL)
548 AND a!=96
549 }
550 } {scan 0 sort 0}
552 do_test where9-6.2.9 {
553 db eval {
554 SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85;
555 ROLLBACK;
556 }
557 } {94 85 86 87 88 89 93 94 95 96 98}
559 do_test where9-6.3.1 {
560 count_steps {
561 BEGIN;
562 DELETE FROM t1
563 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
564 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
565 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
566 }
567 } {scan 0 sort 0} ;# DELETEs rows 90 91 92 97
568 do_test where9-6.3.2 {
569 db eval {
570 SELECT count(*) FROM t1 UNION ALL
571 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
572 ROLLBACK;
573 }
574 } {95 85 86 87 88 89 93 94 95 96 98 99}
576 do_test where9-6.3.3 {
577 count_steps {
578 BEGIN;
579 UPDATE t1 SET a=a+100
580 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
581 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
582 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
583 }
584 } {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97
585 do_test where9-6.3.4 {
586 db eval {
587 SELECT count(*) FROM t1 UNION ALL
588 SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
589 ROLLBACK;
590 }
591 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
593 do_test where9-6.3.5 {
594 count_steps {
595 BEGIN;
596 DELETE FROM t1
597 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
598 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
599 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
600 }
601 } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97
602 do_test where9-6.3.6 {
603 db eval {
604 SELECT count(*) FROM t1 UNION ALL
605 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
606 ROLLBACK;
607 }
608 } {95 85 86 87 88 89 93 94 95 96 98 99}
610 do_test where9-6.3.7 {
611 count_steps {
612 BEGIN;
613 UPDATE t1 SET a=a+100
614 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
615 OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
616 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
617 }
618 } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97
619 do_test where9-6.3.8 {
620 db eval {
621 SELECT count(*) FROM t1 UNION ALL
622 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
623 ROLLBACK;
624 }
625 } {99 85 86 87 88 89 93 94 95 96 98 99}
628 do_test where9-6.4.1 {
629 count_steps {
630 BEGIN;
631 DELETE FROM t1
632 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
633 }
634 } {scan 0 sort 0} ;# DELETE rows 87 88 89 90 91
635 do_test where9-6.4.2 {
636 db eval {
637 SELECT count(*) FROM t1 UNION ALL
638 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
639 ROLLBACK;
640 }
641 } {94 85 86 92 93 94 95 96 97 98 99}
642 do_test where9-6.4.3 {
643 count_steps {
644 BEGIN;
645 UPDATE t1 SET a=a+100
646 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
647 }
648 } {scan 0 sort 0} ;# Add 100 to rowids 87 88 89 90 91
649 do_test where9-6.4.4 {
650 db eval {
651 SELECT count(*) FROM t1 UNION ALL
652 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
653 ROLLBACK;
654 }
655 } {99 85 86 92 93 94 95 96 97 98 99}
658 do_test where9-6.5.1 {
659 count_steps {
660 BEGIN;
661 DELETE FROM t1
662 WHERE a=83
663 OR b=913
664 OR c=28028
665 OR (d>=82 AND d<83)
666 OR (e>2802 AND e<2803)
667 OR f='fghijklmn'
668 OR g='hgfedcb'
669 }
670 } {scan 0 sort 0} ;# DELETE rows 5 31 57 82 83 84 85 86 87
671 do_test where9-6.5.2 {
672 db eval {
673 SELECT count(*) FROM t1 UNION ALL
674 SELECT a FROM t1 WHERE a IN (5,31,57,82,83,84,85,86,87);
675 ROLLBACK;
676 }
677 } {90}
679 do_test where9-6.5.3 {
680 count_steps {
681 BEGIN;
682 UPDATE t1 SET a=a+100
683 WHERE a=83
684 OR b=913
685 OR c=28028
686 OR (d>=82 AND d<83)
687 OR (e>2802 AND e<2803)
688 OR f='fghijklmn'
689 OR g='hgfedcb'
690 }
691 } {scan 0 sort 0} ;# Add 100 to rowids 5 31 57 82 83 84 85 86 87
692 do_test where9-6.5.4 {
693 db eval {
694 SELECT count(*) FROM t1 UNION ALL
695 SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid;
696 ROLLBACK;
697 }
698 } {99 105 131 157 182 183 184 185 186 187}
700 do_test where9-6.6.1 {
701 count_steps {
702 BEGIN;
703 DELETE FROM t1
704 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
705 OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
706 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
707 }
708 } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97
709 do_test where9-6.6.2 {
710 db eval {
711 SELECT count(*) FROM t1 UNION ALL
712 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
713 ROLLBACK;
714 }
715 } {95 85 86 87 88 89 93 94 95 96 98 99}
717 do_test where9-6.6.3 {
718 count_steps {
719 BEGIN;
720 UPDATE t1 SET a=a+100
721 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
722 OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
723 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
724 }
725 } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97
726 do_test where9-6.6.4 {
727 db eval {
728 SELECT count(*) FROM t1 UNION ALL
729 SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
730 ROLLBACK;
731 }
732 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
734 do_test where9-6.7.1 {
735 count_steps {
736 BEGIN;
737 DELETE FROM t1 NOT INDEXED
738 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
739 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
740 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
741 }
742 } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97
743 do_test where9-6.7.2 {
744 db eval {
745 SELECT count(*) FROM t1 UNION ALL
746 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
747 ROLLBACK;
748 }
749 } {95 85 86 87 88 89 93 94 95 96 98 99}
751 do_test where9-6.7.3 {
752 count_steps {
753 BEGIN;
754 UPDATE t1 NOT INDEXED SET a=a+100
755 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
756 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
757 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
758 }
759 } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97
760 do_test where9-6.7.4 {
761 db eval {
762 SELECT count(*) FROM t1 UNION ALL
763 SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
764 ROLLBACK;
765 }
766 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
768 do_test where9-6.8.1 {
769 catchsql {
770 DELETE FROM t1 INDEXED BY t1b
771 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
772 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
773 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
774 }
775 } {1 {no query solution}}
776 do_test where9-6.8.2 {
777 catchsql {
778 UPDATE t1 INDEXED BY t1b SET a=a+100
779 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
780 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
781 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
782 }
783 } {1 {no query solution}}
785 set solution_possible 0
786 ifcapable stat4||stat3 {
787 if {[permutation] != "no_optimization"} { set solution_possible 1 }
788 }
789 if $solution_possible {
790 # When STAT3 is enabled, the "b NOT NULL" terms get translated
791 # into b>NULL, which can be satified by the index t1b. It is a very
792 # expensive way to do the query, but it works, and so a solution is possible.
793 do_test where9-6.8.3-stat4 {
794 catchsql {
795 UPDATE t1 INDEXED BY t1b SET a=a+100
796 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
797 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
798 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
799 }
800 } {0 {}}
801 do_test where9-6.8.4-stat4 {
802 catchsql {
803 DELETE FROM t1 INDEXED BY t1b
804 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
805 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
806 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
807 }
808 } {0 {}}
809 } else {
810 do_test where9-6.8.3 {
811 catchsql {
812 UPDATE t1 INDEXED BY t1b SET a=a+100
813 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
814 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
815 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
816 }
817 } {1 {no query solution}}
818 do_test where9-6.8.4 {
819 catchsql {
820 DELETE FROM t1 INDEXED BY t1b
821 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
822 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
823 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
824 }
825 } {1 {no query solution}}
826 }
827 ############################################################################
828 # Test cases where terms inside an OR series are combined with AND terms
829 # external to the OR clause. In other words, cases where
830 #
831 # x AND (y OR z)
832 #
833 # is able to use indices on x,y and x,z, or indices y,x and z,x.
834 #
835 do_test where9-7.0 {
836 execsql {
837 CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
838 INSERT INTO t5
839 SELECT a, b, c, e, d, f, g,
840 CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
841 CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
842 FROM t1;
843 CREATE INDEX t5xb ON t5(x, b);
844 CREATE INDEX t5xc ON t5(x, c);
845 CREATE INDEX t5xd ON t5(x, d);
846 CREATE INDEX t5xe ON t5(x, e);
847 CREATE INDEX t5xf ON t5(x, f);
848 CREATE INDEX t5xg ON t5(x, g);
849 CREATE INDEX t5yb ON t5(y, b);
850 CREATE INDEX t5yc ON t5(y, c);
851 CREATE INDEX t5yd ON t5(y, d);
852 CREATE INDEX t5ye ON t5(y, e);
853 CREATE INDEX t5yf ON t5(y, f);
854 CREATE INDEX t5yg ON t5(y, g);
855 CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
856 INSERT INTO t6 SELECT * FROM t5;
857 ANALYZE t5;
858 }
859 ifcapable stat3 {
860 sqlite3 db2 test.db
861 db2 eval { DROP TABLE IF EXISTS sqlite_stat3 }
862 db2 close
863 }
864 } {}
865 do_test where9-7.1.1 {
866 count_steps {
867 SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
868 }
869 } {79 81 83 scan 0 sort 1}
870 do_test where9-7.1.2 {
871 execsql {
872 SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
873 }
874 } {79 81 83}
875 do_test where9-7.1.3 {
876 count_steps {
877 SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
878 }
879 } {80 scan 0 sort 1}
880 do_test where9-7.1.4 {
881 execsql {
882 SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
883 }
884 } {80}
885 do_test where9-7.2.1 {
886 count_steps {
887 SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
888 }
889 } {83 scan 0 sort 1}
890 do_test where9-7.2.2 {
891 execsql {
892 SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
893 }
894 } {83}
895 do_test where9-7.3.1 {
896 count_steps {
897 SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
898 }
899 } {79 81 scan 0 sort 1}
900 do_test where9-7.3.2 {
901 execsql {
902 SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
903 }
904 } {79 81}
906 # Fix for ticket [b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4]
907 # "Incorrect result from LEFT JOIN with OR in the WHERE clause"
908 #
909 do_test where9-8.1 {
910 db eval {
911 CREATE TABLE t81(a INTEGER PRIMARY KEY, b, c, d);
912 CREATE TABLE t82(x INTEGER PRIMARY KEY, y);
913 CREATE TABLE t83(p INTEGER PRIMARY KEY, q);
915 INSERT INTO t81 VALUES(2,3,4,5);
916 INSERT INTO t81 VALUES(3,4,5,6);
917 INSERT INTO t82 VALUES(2,4);
918 INSERT INTO t83 VALUES(5,55);
920 SELECT *
921 FROM t81 LEFT JOIN t82 ON y=b JOIN t83
922 WHERE c==p OR d==p
923 ORDER BY +a;
924 }
925 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
926 do_test where9-8.2 {
927 db eval {
928 SELECT *
929 FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
930 WHERE c==p OR d==p
931 ORDER BY +a;
932 }
933 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
934 do_test where9-8.3 {
935 db eval {
936 SELECT *
937 FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
938 WHERE c==p OR d==p
939 ORDER BY +a;
940 }
941 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
943 # Fix for ticket [f2369304e47167e3e644e2f1fe9736063391d7b7]
944 # Incorrect results when OR is used in the ON clause of a LEFT JOIN
945 #
946 do_test where9-9.1 {
947 db eval {
948 CREATE TABLE t91(x); INSERT INTO t91 VALUES(1);
949 CREATE TABLE t92(y INTEGER PRIMARY KEY,a,b);
950 INSERT INTO t92 VALUES(1,2,3);
951 SELECT 1 FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
952 SELECT 2 FROM t91 LEFT JOIN t92 ON a=2 AND b=3;
953 SELECT 3 FROM t91 LEFT JOIN t92 ON (a=2 OR b=3) AND y IS NULL;
954 SELECT 4 FROM t91 LEFT JOIN t92 ON (a=2 AND b=3) AND y IS NULL;
955 CREATE TEMP TABLE x9 AS SELECT * FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
956 SELECT 5 FROM x9 WHERE y IS NULL;
957 SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
958 SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
959 SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
960 SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
961 }
962 } {1 2 3 4 8 9}
964 # Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27]
965 # Incorrect result when OR is used in a join to the right of a LEFT JOIN
966 #
967 do_test where9-10.1 {
968 db eval {
969 CREATE TABLE t101 (id INTEGER PRIMARY KEY);
970 INSERT INTO t101 VALUES (1);
971 SELECT * FROM t101 AS t0
972 LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20
973 JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
974 }
975 } {1 {} 1}
976 do_test where9-10.2 {
977 db eval {
978 CREATE TABLE t102 (id TEXT UNIQUE NOT NULL);
979 INSERT INTO t102 VALUES ('1');
980 SELECT * FROM t102 AS t0
981 LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%'
982 JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
983 }
984 } {1 {} 1}
988 finish_test