| blob | 475788153247495df176ab3c0fb3c232518e2e3d |
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_eqp_test where9-3.1 {
361 SELECT t2.a FROM t1, t2
362 WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
363 } [string map {"\n " \n} {
364 QUERY PLAN
365 |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
366 `--MULTI-INDEX OR
367 |--INDEX 1
368 | `--SEARCH t2 USING INDEX t2d (d=?)
369 `--INDEX 3
370 `--SEARCH t2 USING COVERING INDEX t2f (f=?)
371 }]
372 do_eqp_test where9-3.2 {
373 SELECT coalesce(t2.a,9999)
374 FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
375 WHERE t1.a=80
376 } [string map {"\n " \n} {
377 QUERY PLAN
378 |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
379 `--MULTI-INDEX OR
380 |--INDEX 1
381 | `--SEARCH t2 USING INDEX t2d (d=?) LEFT-JOIN
382 `--INDEX 2
383 `--SEARCH t2 USING COVERING INDEX t2f (f=?) LEFT-JOIN
384 }]
385 }
387 # Make sure that INDEXED BY and multi-index OR clauses play well with
388 # one another.
389 #
390 do_test where9-4.1 {
391 count_steps {
392 SELECT a FROM t1
393 WHERE b>1000
394 AND (c=31031 OR d IS NULL)
395 ORDER BY +a
396 }
397 } {92 93 97 scan 0 sort 1}
398 do_test where9-4.2 {
399 count_steps {
400 SELECT a FROM t1
401 WHERE b>1000
402 AND (c=31031 OR +d IS NULL)
403 ORDER BY +a
404 }
405 } {92 93 97 scan 0 sort 1}
406 do_test where9-4.3 {
407 count_steps {
408 SELECT a FROM t1
409 WHERE +b>1000
410 AND (c=31031 OR d IS NULL)
411 ORDER BY +a
412 }
413 } {92 93 97 scan 0 sort 1}
414 do_test where9-4.4 {
415 count_steps {
416 SELECT a FROM t1 INDEXED BY t1b
417 WHERE b>1000
418 AND (c=31031 OR d IS NULL)
419 ORDER BY +a
420 }
421 } {92 93 97 scan 0 sort 1}
422 do_test where9-4.5 {
423 catchsql {
424 SELECT a FROM t1 INDEXED BY t1b
425 WHERE +b>1000
426 AND (c=31031 OR d IS NULL)
427 ORDER BY +a
428 }
429 } {0 {92 93 97}}
430 do_test where9-4.6 {
431 count_steps {
432 SELECT a FROM t1 NOT INDEXED
433 WHERE b>1000
434 AND (c=31031 OR d IS NULL)
435 ORDER BY +a
436 }
437 } {92 93 97 scan 98 sort 1}
438 do_test where9-4.7 {
439 catchsql {
440 SELECT a FROM t1 INDEXED BY t1c
441 WHERE b>1000
442 AND (c=31031 OR d IS NULL)
443 ORDER BY +a
444 }
445 } {0 {92 93 97}}
446 do_test where9-4.8 {
447 catchsql {
448 SELECT a FROM t1 INDEXED BY t1d
449 WHERE b>1000
450 AND (c=31031 OR d IS NULL)
451 ORDER BY +a
452 }
453 } {0 {92 93 97}}
455 # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
456 # the former is an equality test which is expected to return fewer rows.
457 #
458 do_eqp_test where9-5.1 {
459 SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
460 } {
461 QUERY PLAN
462 `--MULTI-INDEX OR
463 |--INDEX 1
464 | `--SEARCH t1 USING INDEX t1c (c=?)
465 `--INDEX 2
466 `--SEARCH t1 USING INDEX t1d (d=?)
467 }
469 # In contrast, b=1000 is preferred over any OR-clause.
470 #
471 do_eqp_test where9-5.2 {
472 SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
473 } {SEARCH t1 USING INDEX t1b (b=?)}
475 # Likewise, inequalities in an AND are preferred over inequalities in
476 # an OR.
477 #
478 do_eqp_test where9-5.3 {
479 SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
480 } {SEARCH t1 USING INDEX t1b (b>?)}
482 ############################################################################
483 # Make sure OR-clauses work correctly on UPDATE and DELETE statements.
485 do_test where9-6.2.1 {
486 db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}
487 } {99 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99}
489 do_test where9-6.2.2 { ;# Deletes entries 90 91 92 96 97 99
490 count_steps {
491 BEGIN;
492 DELETE FROM t1
493 WHERE b IS NULL
494 OR c IS NULL
495 OR d IS NULL
496 }
497 } {scan 0 sort 0}
499 do_test where9-6.2.3 {
500 db eval {
501 SELECT count(*) FROM t1 UNION ALL
502 SELECT a FROM t1 WHERE a>=85;
503 ROLLBACK;
504 }
505 } {93 85 86 87 88 89 93 94 95 98}
507 do_test where9-6.2.4 { ;# Deletes entries 90 91 92 96 97 99
508 count_steps {
509 BEGIN;
510 DELETE FROM t1
511 WHERE +b IS NULL
512 OR c IS NULL
513 OR d IS NULL
514 }
515 } {scan 98 sort 0}
517 do_test where9-6.2.5 {
518 db eval {
519 SELECT count(*) FROM t1 UNION ALL
520 SELECT a FROM t1 WHERE a>=85;
521 ROLLBACK;
522 }
523 } {93 85 86 87 88 89 93 94 95 98}
525 do_test where9-6.2.6 {
526 count_steps {
527 BEGIN;
528 UPDATE t1 SET a=a+100
529 WHERE (b IS NULL
530 OR c IS NULL
531 OR d IS NULL)
532 AND a!=92
533 AND a!=97
534 }
535 } {scan 0 sort 0} ;# Add 100 to entries 90 91 96 99
537 do_test where9-6.2.7 {
538 db eval {
539 SELECT count(*) FROM t1 UNION ALL
540 SELECT a FROM t1 WHERE a>=85;
541 ROLLBACK
542 }
543 } {99 85 86 87 88 89 92 93 94 95 97 98 190 191 196 199}
545 do_test where9-6.2.8 { ;# Deletes entries 90 91 92 97 99
546 count_steps {
547 BEGIN;
548 DELETE FROM t1
549 WHERE (b IS NULL
550 OR c IS NULL
551 OR d IS NULL)
552 AND a!=96
553 }
554 } {scan 0 sort 0}
556 do_test where9-6.2.9 {
557 db eval {
558 SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85;
559 ROLLBACK;
560 }
561 } {94 85 86 87 88 89 93 94 95 96 98}
563 do_test where9-6.3.1 {
564 count_steps {
565 BEGIN;
566 DELETE FROM t1
567 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
568 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
569 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
570 }
571 } {scan 0 sort 0} ;# DELETEs rows 90 91 92 97
572 do_test where9-6.3.2 {
573 db eval {
574 SELECT count(*) FROM t1 UNION ALL
575 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
576 ROLLBACK;
577 }
578 } {95 85 86 87 88 89 93 94 95 96 98 99}
580 do_test where9-6.3.3 {
581 count_steps {
582 BEGIN;
583 UPDATE t1 SET a=a+100
584 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
585 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
586 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
587 }
588 } {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97
589 do_test where9-6.3.4 {
590 db eval {
591 SELECT count(*) FROM t1 UNION ALL
592 SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
593 ROLLBACK;
594 }
595 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
597 do_test where9-6.3.5 {
598 count_steps {
599 BEGIN;
600 DELETE FROM t1
601 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
602 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
603 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
604 }
605 } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97
606 do_test where9-6.3.6 {
607 db eval {
608 SELECT count(*) FROM t1 UNION ALL
609 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
610 ROLLBACK;
611 }
612 } {95 85 86 87 88 89 93 94 95 96 98 99}
614 do_test where9-6.3.7 {
615 count_steps {
616 BEGIN;
617 UPDATE t1 SET a=a+100
618 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
619 OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
620 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
621 }
622 } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97
623 do_test where9-6.3.8 {
624 db eval {
625 SELECT count(*) FROM t1 UNION ALL
626 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
627 ROLLBACK;
628 }
629 } {99 85 86 87 88 89 93 94 95 96 98 99}
632 do_test where9-6.4.1 {
633 count_steps {
634 BEGIN;
635 DELETE FROM t1
636 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
637 }
638 } {scan 0 sort 0} ;# DELETE rows 87 88 89 90 91
639 do_test where9-6.4.2 {
640 db eval {
641 SELECT count(*) FROM t1 UNION ALL
642 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
643 ROLLBACK;
644 }
645 } {94 85 86 92 93 94 95 96 97 98 99}
646 do_test where9-6.4.3 {
647 count_steps {
648 BEGIN;
649 UPDATE t1 SET a=a+100
650 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
651 }
652 } {scan 0 sort 0} ;# Add 100 to rowids 87 88 89 90 91
653 do_test where9-6.4.4 {
654 db eval {
655 SELECT count(*) FROM t1 UNION ALL
656 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
657 ROLLBACK;
658 }
659 } {99 85 86 92 93 94 95 96 97 98 99}
662 do_test where9-6.5.1 {
663 count_steps {
664 BEGIN;
665 DELETE FROM t1
666 WHERE a=83
667 OR b=913
668 OR c=28028
669 OR (d>=82 AND d<83)
670 OR (e>2802 AND e<2803)
671 OR f='fghijklmn'
672 OR g='hgfedcb'
673 }
674 } {scan 0 sort 0} ;# DELETE rows 5 31 57 82 83 84 85 86 87
675 do_test where9-6.5.2 {
676 db eval {
677 SELECT count(*) FROM t1 UNION ALL
678 SELECT a FROM t1 WHERE a IN (5,31,57,82,83,84,85,86,87);
679 ROLLBACK;
680 }
681 } {90}
683 do_test where9-6.5.3 {
684 count_steps {
685 BEGIN;
686 UPDATE t1 SET a=a+100
687 WHERE a=83
688 OR b=913
689 OR c=28028
690 OR (d>=82 AND d<83)
691 OR (e>2802 AND e<2803)
692 OR f='fghijklmn'
693 OR g='hgfedcb'
694 }
695 } {scan 0 sort 0} ;# Add 100 to rowids 5 31 57 82 83 84 85 86 87
696 do_test where9-6.5.4 {
697 db eval {
698 SELECT count(*) FROM t1 UNION ALL
699 SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid;
700 ROLLBACK;
701 }
702 } {99 105 131 157 182 183 184 185 186 187}
704 do_test where9-6.6.1 {
705 count_steps {
706 BEGIN;
707 DELETE FROM t1
708 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
709 OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
710 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
711 }
712 } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97
713 do_test where9-6.6.2 {
714 db eval {
715 SELECT count(*) FROM t1 UNION ALL
716 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
717 ROLLBACK;
718 }
719 } {95 85 86 87 88 89 93 94 95 96 98 99}
721 do_test where9-6.6.3 {
722 count_steps {
723 BEGIN;
724 UPDATE t1 SET a=a+100
725 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
726 OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
727 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
728 }
729 } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97
730 do_test where9-6.6.4 {
731 db eval {
732 SELECT count(*) FROM t1 UNION ALL
733 SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
734 ROLLBACK;
735 }
736 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
738 do_test where9-6.7.1 {
739 count_steps {
740 BEGIN;
741 DELETE FROM t1 NOT INDEXED
742 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
743 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
744 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
745 }
746 } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97
747 do_test where9-6.7.2 {
748 db eval {
749 SELECT count(*) FROM t1 UNION ALL
750 SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
751 ROLLBACK;
752 }
753 } {95 85 86 87 88 89 93 94 95 96 98 99}
755 do_test where9-6.7.3 {
756 count_steps {
757 BEGIN;
758 UPDATE t1 NOT INDEXED SET a=a+100
759 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
760 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
761 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
762 }
763 } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97
764 do_test where9-6.7.4 {
765 db eval {
766 SELECT count(*) FROM t1 UNION ALL
767 SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
768 ROLLBACK;
769 }
770 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
772 do_test where9-6.8.1 {
773 catchsql {
774 DELETE FROM t1 INDEXED BY t1b
775 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
776 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
777 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
778 }
779 } {0 {}}
780 do_test where9-6.8.2 {
781 catchsql {
782 UPDATE t1 INDEXED BY t1b SET a=a+100
783 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
784 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
785 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
786 }
787 } {0 {}}
789 set solution_possible 0
790 ifcapable stat4 {
791 if {[permutation] != "no_optimization"} { set solution_possible 1 }
792 }
793 if $solution_possible {
794 # When STAT3 is enabled, the "b NOT NULL" terms get translated
795 # into b>NULL, which can be satified by the index t1b. It is a very
796 # expensive way to do the query, but it works, and so a solution is possible.
797 do_test where9-6.8.3-stat4 {
798 catchsql {
799 UPDATE t1 INDEXED BY t1b SET a=a+100
800 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
801 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
802 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
803 }
804 } {0 {}}
805 do_test where9-6.8.4-stat4 {
806 catchsql {
807 DELETE FROM t1 INDEXED BY t1b
808 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
809 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
810 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
811 }
812 } {0 {}}
813 } else {
814 do_test where9-6.8.3 {
815 catchsql {
816 UPDATE t1 INDEXED BY t1b SET a=a+100
817 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
818 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
819 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
820 }
821 } {0 {}}
822 do_test where9-6.8.4 {
823 catchsql {
824 DELETE FROM t1 INDEXED BY t1b
825 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
826 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
827 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
828 }
829 } {0 {}}
830 }
831 ############################################################################
832 # Test cases where terms inside an OR series are combined with AND terms
833 # external to the OR clause. In other words, cases where
834 #
835 # x AND (y OR z)
836 #
837 # is able to use indices on x,y and x,z, or indices y,x and z,x.
838 #
839 do_test where9-7.0 {
840 execsql {
841 CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
842 INSERT INTO t5
843 SELECT a, b, c, e, d, f, g,
844 CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
845 CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
846 FROM t1;
847 CREATE INDEX t5xb ON t5(x, b);
848 CREATE INDEX t5xc ON t5(x, c);
849 CREATE INDEX t5xd ON t5(x, d);
850 CREATE INDEX t5xe ON t5(x, e);
851 CREATE INDEX t5xf ON t5(x, f);
852 CREATE INDEX t5xg ON t5(x, g);
853 CREATE INDEX t5yb ON t5(y, b);
854 CREATE INDEX t5yc ON t5(y, c);
855 CREATE INDEX t5yd ON t5(y, d);
856 CREATE INDEX t5ye ON t5(y, e);
857 CREATE INDEX t5yf ON t5(y, f);
858 CREATE INDEX t5yg ON t5(y, g);
859 CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
860 INSERT INTO t6 SELECT * FROM t5;
861 ANALYZE t5;
862 }
863 } {}
864 do_test where9-7.1.1 {
865 count_steps {
866 SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
867 }
868 } {79 81 83 scan 0 sort 1}
869 do_test where9-7.1.2 {
870 execsql {
871 SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
872 }
873 } {79 81 83}
874 do_test where9-7.1.3 {
875 count_steps {
876 SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
877 }
878 } {80 scan 0 sort 1}
879 do_test where9-7.1.4 {
880 execsql {
881 SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
882 }
883 } {80}
884 do_test where9-7.2.1 {
885 count_steps {
886 SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
887 }
888 } {83 scan 0 sort 1}
889 do_test where9-7.2.2 {
890 execsql {
891 SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
892 }
893 } {83}
894 do_test where9-7.3.1 {
895 count_steps {
896 SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
897 }
898 } {79 81 scan 0 sort 1}
899 do_test where9-7.3.2 {
900 execsql {
901 SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
902 }
903 } {79 81}
905 # Fix for ticket [b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4]
906 # "Incorrect result from LEFT JOIN with OR in the WHERE clause"
907 #
908 do_test where9-8.1 {
909 db eval {
910 CREATE TABLE t81(a INTEGER PRIMARY KEY, b, c, d);
911 CREATE TABLE t82(x INTEGER PRIMARY KEY, y);
912 CREATE TABLE t83(p INTEGER PRIMARY KEY, q);
914 INSERT INTO t81 VALUES(2,3,4,5);
915 INSERT INTO t81 VALUES(3,4,5,6);
916 INSERT INTO t82 VALUES(2,4);
917 INSERT INTO t83 VALUES(5,55);
919 SELECT *
920 FROM t81 LEFT JOIN t82 ON y=b JOIN t83
921 WHERE c==p OR d==p
922 ORDER BY +a;
923 }
924 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
925 do_test where9-8.2 {
926 db eval {
927 SELECT *
928 FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
929 WHERE c==p OR d==p
930 ORDER BY +a;
931 }
932 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
933 do_test where9-8.3 {
934 db eval {
935 SELECT *
936 FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
937 WHERE c==p OR d==p
938 ORDER BY +a;
939 }
940 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
942 # Fix for ticket [f2369304e47167e3e644e2f1fe9736063391d7b7]
943 # Incorrect results when OR is used in the ON clause of a LEFT JOIN
944 #
945 do_test where9-9.1 {
946 db eval {
947 CREATE TABLE t91(x); INSERT INTO t91 VALUES(1);
948 CREATE TABLE t92(y INTEGER PRIMARY KEY,a,b);
949 INSERT INTO t92 VALUES(1,2,3);
950 SELECT 1 FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
951 SELECT 2 FROM t91 LEFT JOIN t92 ON a=2 AND b=3;
952 SELECT 3 FROM t91 LEFT JOIN t92 ON (a=2 OR b=3) AND y IS NULL;
953 SELECT 4 FROM t91 LEFT JOIN t92 ON (a=2 AND b=3) AND y IS NULL;
954 CREATE TEMP TABLE x9 AS SELECT * FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
955 SELECT 5 FROM x9 WHERE y IS NULL;
956 SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
957 SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
958 SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
959 SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
960 }
961 } {1 2 3 4 8 9}
963 # Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27]
964 # Incorrect result when OR is used in a join to the right of a LEFT JOIN
965 #
966 do_test where9-10.1 {
967 db eval {
968 CREATE TABLE t101 (id INTEGER PRIMARY KEY);
969 INSERT INTO t101 VALUES (1);
970 SELECT * FROM t101 AS t0
971 LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20
972 JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
973 }
974 } {1 {} 1}
975 do_test where9-10.2 {
976 db eval {
977 CREATE TABLE t102 (id TEXT UNIQUE NOT NULL);
978 INSERT INTO t102 VALUES ('1');
979 SELECT * FROM t102 AS t0
980 LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%'
981 JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
982 }
983 } {1 {} 1}
985 # dbsqlfuzz 9df1d53c24c4c96af0dae15ee764897af415ac76
986 # The MULTI-INDEX OR processing evaluates the same WHERE-clause sub-expression
987 # twice. But if that sub-expression contains a UNION ALL SELECT statement
988 # subject to query flattening, the sub-expression might be transformed in a
989 # way that it can only be code-generated once. An assert() will fail on
990 # the second attempt to generate code from the same sub-expression.
991 # The solution is to make a copy of sub-expressions used by MULTI-INDEX OR
992 #
993 reset_db
994 do_execsql_test where9-11.1 {
995 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
996 CREATE TABLE t2_a(k INTEGER PRIMARY KEY, v TEXT);
997 CREATE TABLE t2_b(k INTEGER PRIMARY KEY, v TEXT);
998 CREATE VIEW t2 AS SELECT * FROM t2_a UNION ALL SELECT * FROM t2_b;
999 SELECT 1 FROM t1 JOIN t1 USING(a)
1000 WHERE (a=1)
1001 OR (a=2 AND (SELECT 4 FROM t2,(SELECT 5 FROM t1 ORDER BY a) WHERE a));
1002 } {}
1004 finish_test