| blob | 139251aa0729fca54adb348248ae451cb2190dd8 |
1 # 2008 December 23
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 focus
12 # is testing of where.c. More specifically, the focus is the optimization
13 # of WHERE clauses that feature the OR operator.
14 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 # Test organization:
20 #
21 # where8-1.*: Tests to demonstrate simple cases work with a single table
22 # in the FROM clause.
23 #
24 # where8-2.*: Tests surrounding virtual tables and the OR optimization.
25 #
26 # where8-3.*: Tests with more than one table in the FROM clause.
27 #
29 proc execsql_status {sql {db db}} {
30 set result [uplevel $db eval [list $sql]]
31 concat $result [db status step] [db status sort]
32 }
34 proc execsql_status2 {sql {db db}} {
35 set ::sqlite_search_count 0
36 set result [uplevel [list execsql_status $sql $db]]
37 concat $result $::sqlite_search_count
38 }
40 do_test where8-1.1 {
41 execsql {
42 CREATE TABLE t1(a, b TEXT, c);
43 CREATE INDEX i1 ON t1(a);
44 CREATE INDEX i2 ON t1(b);
46 INSERT INTO t1 VALUES(1, 'one', 'I');
47 INSERT INTO t1 VALUES(2, 'two', 'II');
48 INSERT INTO t1 VALUES(3, 'three', 'III');
49 INSERT INTO t1 VALUES(4, 'four', 'IV');
50 INSERT INTO t1 VALUES(5, 'five', 'V');
51 INSERT INTO t1 VALUES(6, 'six', 'VI');
52 INSERT INTO t1 VALUES(7, 'seven', 'VII');
53 INSERT INTO t1 VALUES(8, 'eight', 'VIII');
54 INSERT INTO t1 VALUES(9, 'nine', 'IX');
55 INSERT INTO t1 VALUES(10, 'ten', 'X');
56 }
57 } {}
59 do_test where8-1.2 {
60 execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' }
61 } {I IX 0 0 6}
63 do_test where8-1.3 {
64 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }
65 } {IX X II 0 0 6}
67 do_test where8-1.4 {
68 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }
69 } {IX X III II 0 0 9}
71 do_test where8-1.5 {
72 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }
73 } {IX X V IV 0 0 9}
75 do_test where8-1.6 {
76 execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }
77 } {I III 0 1}
79 do_test where8-1.7 {
80 execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }
81 } {I III 0 1}
83 do_test where8-1.8 {
84 # 18 searches. 9 on the index cursor and 9 on the table cursor.
85 execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' }
86 } {II III IV IX 0 0 18}
88 do_test where8-1.9 {
89 execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' }
90 } {IX X VIII 0 0 7}
92 do_test where8-1.10 {
93 execsql_status2 {
94 SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight'
95 }
96 } {IX VIII 0 0 7}
98 do_test where8-1.11 {
99 execsql_status2 {
100 SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine'
101 }
102 } {IV V VI IX 0 0 10}
104 do_test where8-1.12.1 {
105 execsql_status2 {
106 SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5
107 }
108 } {I II III V 0 0 14}
110 do_test where8-1.12.2 {
111 execsql_status2 {
112 SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5
113 }
114 } {I II III V 9 0 9}
116 do_test where8-1.13 {
117 execsql_status2 {
118 SELECT c FROM t1
119 WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6
120 ORDER BY rowid
121 }
122 } {II III IV V VI 0 1 18}
123 do_test where8-1.14 {
124 execsql_status2 {
125 SELECT c FROM t1
126 WHERE
127 a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR
128 b = 'seven' OR a = 8 OR b = 'nine' OR a = 10
129 ORDER BY rowid
130 }
131 } {II III IV V VI VII VIII IX X 0 1 33}
133 do_test where8-1.15 {
134 execsql_status2 {
135 SELECT c FROM t1 WHERE
136 a BETWEEN 2 AND 4 OR b = 'nine'
137 ORDER BY rowid
138 }
139 } {II III IV IX 0 1 12}
142 #--------------------------------------------------------------------------
143 # Tests where8-2.*: Virtual tables
144 #
146 if 0 {
147 ifcapable vtab {
148 # Register the 'echo' module used for testing virtual tables.
149 #
150 register_echo_module [sqlite3_connection_pointer db]
152 do_test where8-2.1 {
153 execsql {
154 CREATE VIRTUAL TABLE e1 USING echo(t1);
155 SELECT b FROM e1;
156 }
157 } {one two three four five six seven eight nine ten}
159 do_test where8-2.2.1 {
160 set echo_module ""
161 execsql {
162 SELECT c FROM e1 WHERE a=1 OR b='three';
163 }
164 } {I III}
165 do_test where8-2.2.2 {
166 set echo_module
167 } {TODO: What should this be?}
168 }
169 }
171 #--------------------------------------------------------------------------
172 # Tests where8-3.*: Cases with multiple tables in the FROM clause.
173 #
174 do_test where8-3.1 {
175 execsql {
176 CREATE TABLE t2(d, e, f);
177 CREATE INDEX i3 ON t2(d);
178 CREATE INDEX i4 ON t2(e);
180 INSERT INTO t2 VALUES(1, NULL, 'I');
181 INSERT INTO t2 VALUES(2, 'four', 'IV');
182 INSERT INTO t2 VALUES(3, NULL, 'IX');
183 INSERT INTO t2 VALUES(4, 'sixteen', 'XVI');
184 INSERT INTO t2 VALUES(5, NULL, 'XXV');
185 INSERT INTO t2 VALUES(6, 'thirtysix', 'XXXVI');
186 INSERT INTO t2 VALUES(7, 'fortynine', 'XLIX');
187 INSERT INTO t2 VALUES(8, 'sixtyeight', 'LXIV');
188 INSERT INTO t2 VALUES(9, 'eightyone', 'LXXXIX');
189 INSERT INTO t2 VALUES(10, NULL, 'C');
190 }
191 } {}
193 do_test where8-3.2 {
194 execsql_status {
195 SELECT a, d FROM t1, t2 WHERE b=e
196 }
197 } {4 2 9 0}
199 do_test where8-3.3 {
200 execsql_status {
201 SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6
202 }
203 } {2 6 3 6 0 0}
205 do_test where8-3.4 {
206 execsql_status {
207 SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a
208 }
209 } {2 2 3 3 0 0}
211 do_test where8-3.5 {
212 execsql_status {
213 SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen')
214 ORDER BY +a, +d;
215 }
216 } {2 2 2 4 3 3 3 4 0 1}
218 do_test where8-3.6 {
219 # The first part of the WHERE clause in this query, (a=2 OR a=3) is
220 # transformed into "a IN (2, 3)". This is why the sort is required.
221 #
222 execsql_status {
223 SELECT a, d
224 FROM t1, t2
225 WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen')
226 ORDER BY t1.rowid
227 }
228 } {2 2 2 4 3 3 3 4 0 1}
229 do_test where8-3.7 {
230 execsql_status {
231 SELECT a, d
232 FROM t1, t2
233 WHERE a = 2 AND (d = a OR e = 'sixteen')
234 ORDER BY t1.rowid
235 }
236 } {/2 2 2 4 0 [01]/}
237 do_test where8-3.8 {
238 execsql_status {
239 SELECT a, d
240 FROM t1, t2
241 WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen')
242 ORDER BY t1.rowid
243 }
244 } {2 2 2 4 3 3 3 4 0 1}
246 do_test where8-3.9 {
247 # The "OR c = 'IX'" term forces a linear scan.
248 execsql_status {
249 SELECT a, d
250 FROM t1, t2
251 WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen')
252 ORDER BY t1.rowid
253 }
254 } {2 2 2 4 3 3 3 4 9 9 9 4 9 0}
256 do_test where8-3.10 {
257 execsql_status {
258 SELECT d FROM t2 WHERE e IS NULL OR e = 'four'
259 }
260 } {1 3 5 10 2 0 0}
262 do_test where8-3.11 {
263 execsql_status {
264 SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
265 }
266 } {1 1 2 2 3 3 4 2 4 4 0 0}
267 do_test where8-3.12 {
268 execsql_status {
269 SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
270 }
271 } {1 1 2 2 3 3 4 2 4 4 9 0}
272 do_test where8-3.13 {
273 execsql_status {
274 SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
275 }
276 } {1 1 2 2 3 3 4 2 4 4 9 0}
278 do_test where8-3.14 {
279 execsql_status {
280 SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5
281 }
282 } {IV V 9 0}
284 do_test where8-3.15 {
285 execsql_status {
286 SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
287 SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
288 )
289 ORDER BY c
290 }
291 } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1}
294 do_test where8-3.21 {
295 execsql_status {
296 SELECT a, d FROM t1, (t2) WHERE (a=d OR b=e) AND a<5 ORDER BY a
297 }
298 } {1 1 2 2 3 3 4 2 4 4 0 0}
299 do_test where8-3.21.1 {
300 execsql_status {
301 SELECT a, d FROM t1, ((t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a
302 }
303 } {1 1 2 2 3 3 4 2 4 4 0 0}
304 if {[permutation] != "no_optimization"} {
305 do_test where8-3.21.2 {
306 execsql_status {
307 SELECT a, d FROM t1, ((SELECT * FROM t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a
308 }
309 } {1 1 2 2 3 3 4 2 4 4 0 0}
310 }
311 do_test where8-3.22 {
312 execsql_status {
313 SELECT a, d FROM ((((((t1))), (((t2))))))
314 WHERE (a=d OR b=e) AND a<5 ORDER BY a
315 }
316 } {1 1 2 2 3 3 4 2 4 4 0 0}
317 if {[permutation] != "no_optimization"} {
318 do_test where8-3.23 {
319 execsql_status {
320 SELECT * FROM ((SELECT * FROM t2)) AS t3;
321 }
322 } {1 {} I 2 four IV 3 {} IX 4 sixteen XVI 5 {} XXV 6 thirtysix XXXVI 7 fortynine XLIX 8 sixtyeight LXIV 9 eightyone LXXXIX 10 {} C 9 0}
323 }
325 #-----------------------------------------------------------------------
326 # The following tests - where8-4.* - verify that adding or removing
327 # indexes does not change the results returned by various queries.
328 #
329 do_test where8-4.1 {
330 execsql {
331 BEGIN;
332 CREATE TABLE t3(a INTEGER, b REAL, c TEXT);
333 CREATE TABLE t4(f INTEGER, g REAL, h TEXT);
334 INSERT INTO t3 VALUES('hills', NULL, 1415926535);
335 INSERT INTO t3 VALUES('and', 'of', NULL);
336 INSERT INTO t3 VALUES('have', 'towering', 53594.08128);
337 INSERT INTO t3 VALUES(NULL, 45.64856692, 'Not');
338 INSERT INTO t3 VALUES('same', 5028841971, NULL);
339 INSERT INTO t3 VALUES('onlookers', 'in', 8214808651);
340 INSERT INTO t3 VALUES(346.0348610, 2643383279, NULL);
341 INSERT INTO t3 VALUES(1415926535, 'of', 'are');
342 INSERT INTO t3 VALUES(NULL, 0.4811174502, 'snapshots');
343 INSERT INTO t3 VALUES('over', 'the', 8628034825);
344 INSERT INTO t3 VALUES(8628034825, 66.59334461, 2847564.823);
345 INSERT INTO t3 VALUES('onlookers', 'same', 'and');
346 INSERT INTO t3 VALUES(NULL, 'light', 6939937510);
347 INSERT INTO t3 VALUES('from', 'their', 'viewed');
348 INSERT INTO t3 VALUES('from', 'Alpine', 'snapshots');
349 INSERT INTO t3 VALUES('from', 'sometimes', 'unalike');
350 INSERT INTO t3 VALUES(1339.360726, 'light', 'have');
351 INSERT INTO t3 VALUES(6939937510, 3282306647, 'other');
352 INSERT INTO t3 VALUES('paintings', 8628034825, 'all');
353 INSERT INTO t3 VALUES('paintings', NULL, 'same');
354 INSERT INTO t3 VALUES('Alpine', 378678316.5, 'unalike');
355 INSERT INTO t3 VALUES('Alpine', NULL, 'same');
356 INSERT INTO t3 VALUES(1339.360726, 2847564.823, 'over');
357 INSERT INTO t3 VALUES('villages', 'their', 'have');
358 INSERT INTO t3 VALUES('unalike', 'remarkably', 'in');
359 INSERT INTO t3 VALUES('and', 8979323846, 'and');
360 INSERT INTO t3 VALUES(NULL, 1415926535, 'an');
361 INSERT INTO t3 VALUES(271.2019091, 8628034825, 0.4811174502);
362 INSERT INTO t3 VALUES('all', 3421170679, 'the');
363 INSERT INTO t3 VALUES('Not', 'and', 1415926535);
364 INSERT INTO t3 VALUES('of', 'other', 'light');
365 INSERT INTO t3 VALUES(NULL, 'towering', 'Not');
366 INSERT INTO t3 VALUES(346.0348610, NULL, 'other');
367 INSERT INTO t3 VALUES('Not', 378678316.5, NULL);
368 INSERT INTO t3 VALUES('snapshots', 8628034825, 'of');
369 INSERT INTO t3 VALUES(3282306647, 271.2019091, 'and');
370 INSERT INTO t3 VALUES(50.58223172, 378678316.5, 5028841971);
371 INSERT INTO t3 VALUES(50.58223172, 2643383279, 'snapshots');
372 INSERT INTO t3 VALUES('writings', 8979323846, 8979323846);
373 INSERT INTO t3 VALUES('onlookers', 'his', 'in');
374 INSERT INTO t3 VALUES('unalike', 8628034825, 1339.360726);
375 INSERT INTO t3 VALUES('of', 'Alpine', 'and');
376 INSERT INTO t3 VALUES('onlookers', NULL, 'from');
377 INSERT INTO t3 VALUES('writings', 'it', 1339.360726);
378 INSERT INTO t3 VALUES('it', 'and', 'villages');
379 INSERT INTO t3 VALUES('an', 'the', 'villages');
380 INSERT INTO t3 VALUES(8214808651, 8214808651, 'same');
381 INSERT INTO t3 VALUES(346.0348610, 'light', 1415926535);
382 INSERT INTO t3 VALUES(NULL, 8979323846, 'and');
383 INSERT INTO t3 VALUES(NULL, 'same', 1339.360726);
384 INSERT INTO t4 VALUES('his', 'from', 'an');
385 INSERT INTO t4 VALUES('snapshots', 'or', NULL);
386 INSERT INTO t4 VALUES('Alpine', 'have', 'it');
387 INSERT INTO t4 VALUES('have', 'peak', 'remarkably');
388 INSERT INTO t4 VALUES('hills', NULL, 'Not');
389 INSERT INTO t4 VALUES('same', 'from', 2643383279);
390 INSERT INTO t4 VALUES('have', 'angle', 8628034825);
391 INSERT INTO t4 VALUES('sometimes', 'it', 2847564.823);
392 INSERT INTO t4 VALUES(0938446095, 'peak', 'of');
393 INSERT INTO t4 VALUES(8628034825, 'and', 'same');
394 INSERT INTO t4 VALUES('and', 271.2019091, 'their');
395 INSERT INTO t4 VALUES('the', 'of', 'remarkably');
396 INSERT INTO t4 VALUES('and', 3421170679, 1415926535);
397 INSERT INTO t4 VALUES('and', 'in', 'all');
398 INSERT INTO t4 VALUES(378678316.5, 0.4811174502, 'snapshots');
399 INSERT INTO t4 VALUES('it', 'are', 'have');
400 INSERT INTO t4 VALUES('angle', 'snapshots', 378678316.5);
401 INSERT INTO t4 VALUES('from', 1415926535, 8628034825);
402 INSERT INTO t4 VALUES('snapshots', 'angle', 'have');
403 INSERT INTO t4 VALUES(3421170679, 0938446095, 'Not');
404 INSERT INTO t4 VALUES('peak', NULL, 0.4811174502);
405 INSERT INTO t4 VALUES('same', 'have', 'Alpine');
406 INSERT INTO t4 VALUES(271.2019091, 66.59334461, 0938446095);
407 INSERT INTO t4 VALUES(8979323846, 'his', 'an');
408 INSERT INTO t4 VALUES(NULL, 'and', 3282306647);
409 INSERT INTO t4 VALUES('remarkably', NULL, 'Not');
410 INSERT INTO t4 VALUES('villages', 4543.266482, 'his');
411 INSERT INTO t4 VALUES(2643383279, 'paintings', 'onlookers');
412 INSERT INTO t4 VALUES(1339.360726, 'of', 'the');
413 INSERT INTO t4 VALUES('peak', 'other', 'peak');
414 INSERT INTO t4 VALUES('it', 'or', 8979323846);
415 INSERT INTO t4 VALUES('onlookers', 'Not', 'towering');
416 INSERT INTO t4 VALUES(NULL, 'peak', 'Not');
417 INSERT INTO t4 VALUES('of', 'have', 6939937510);
418 INSERT INTO t4 VALUES('light', 'hills', 0.4811174502);
419 INSERT INTO t4 VALUES(5028841971, 'Not', 'it');
420 INSERT INTO t4 VALUES('and', 'Not', NULL);
421 INSERT INTO t4 VALUES(346.0348610, 'villages', NULL);
422 INSERT INTO t4 VALUES(8979323846, NULL, 6939937510);
423 INSERT INTO t4 VALUES('an', 'light', 'peak');
424 INSERT INTO t4 VALUES(5028841971, 6939937510, 'light');
425 INSERT INTO t4 VALUES('sometimes', 'peak', 'peak');
426 INSERT INTO t4 VALUES(378678316.5, 5028841971, 'an');
427 INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine');
428 INSERT INTO t4 VALUES('from', 'of', 'all');
429 INSERT INTO t4 VALUES(0938446095, 'same', NULL);
430 INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL);
431 INSERT INTO t4 VALUES('his', 'of', 378678316.5);
432 INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647);
433 INSERT INTO t4 VALUES('hills', 'all', 'peak');
434 CREATE TABLE t5(s);
435 INSERT INTO t5 VALUES('tab-t5');
436 CREATE TABLE t6(t);
437 INSERT INTO t6 VALUES(123456);
438 COMMIT;
439 }
440 } {}
442 catch {unset results}
443 catch {unset A}
444 catch {unset B}
446 set A 2
447 foreach idxsql {
448 {
449 /* No indexes */
450 } {
451 CREATE INDEX i5 ON t3(a);
452 } {
453 CREATE INDEX i5 ON t3(a, b);
454 CREATE INDEX i6 ON t4(f);
455 } {
456 CREATE UNIQUE INDEX i5 ON t3(a, b);
457 CREATE INDEX i7 ON t3(c);
458 CREATE INDEX i6 ON t4(f);
459 CREATE INDEX i8 ON t4(h);
460 } {
461 CREATE INDEX i5 ON t3(a, b, c);
462 CREATE INDEX i6 ON t4(f, g, h);
463 CREATE INDEX i7 ON t3(c, b, a);
464 CREATE INDEX i8 ON t4(h, g, f);
465 }
466 } {
468 execsql {
469 DROP INDEX IF EXISTS i5;
470 DROP INDEX IF EXISTS i6;
471 DROP INDEX IF EXISTS i7;
472 DROP INDEX IF EXISTS i8;
473 }
474 execsql $idxsql
476 foreach {B sql} {
477 1 { SELECT * FROM t3 WHERE c LIKE b }
478 2 { SELECT * FROM t3 WHERE c||'' LIKE 'the%' }
479 3 { SELECT * FROM t3 WHERE rowid LIKE '12%' }
480 4 { SELECT * FROM t3 WHERE +c LIKE 'the%' }
481 5 { SELECT * FROM t3 WHERE c LIKE 'the%' }
482 6 { SELECT * FROM t3 WHERE c GLOB '*llo' }
484 7 { SELECT * FROM t3 WHERE a = 'angle' }
485 8 { SELECT * FROM t3 WHERE a = 'it' OR b = 6939937510 }
486 9 { SELECT * FROM t3, t4 WHERE a = 'painting' OR a = 'are' OR a = f }
487 10 { SELECT * FROM t3, t4 WHERE a = 'all' OR a = 'and' OR a = h }
488 11 { SELECT * FROM t3, t4 WHERE a < 'of' OR b > 346 AND c IS NULL }
489 12 { SELECT * FROM t3, t4 WHERE 'the' > a OR b > 'have' AND c = 1415926535 }
491 13 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a = 3421170679 }
492 14 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a IS NULL }
493 15 { SELECT * FROM t3 WHERE c > 'one' OR c >= 'one' OR c LIKE 'one%' }
494 16 { SELECT * FROM t3 WHERE c > 'one' OR c = c OR c = a }
495 17 { SELECT * FROM t3 WHERE c IS NULL OR a >= 'peak' }
496 18 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') OR a>1 }
497 19 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') AND a>1 }
498 20 { SELECT * FROM t3 WHERE c IS NULL AND a>'one' }
499 21 { SELECT * FROM t3 WHERE c IS NULL OR a>'one' }
500 22 { SELECT * FROM t3 WHERE b = b AND a > 'are' }
501 23 { SELECT * FROM t3 WHERE c <= b OR b < 'snapshots' }
502 24 { SELECT * FROM t3 WHERE 'onlookers' >= c AND a <= b OR b = 'angle' }
503 25 { SELECT * FROM t3 WHERE b = 'from' }
504 26 { SELECT * FROM t3 WHERE b = 4543.266482 }
505 27 { SELECT * FROM t3 WHERE c < 3282306647 }
506 28 { SELECT * FROM t3 WHERE c IS NULL AND b >= c }
507 29 { SELECT * FROM t3 WHERE b > 0.4811174502 AND c = 'other' AND 'viewed' > a }
508 30 { SELECT * FROM t3 WHERE c = 'peak' }
509 31 { SELECT * FROM t3 WHERE c < 53594.08128 OR c <= b }
510 32 { SELECT * FROM t3 WHERE 'writings' <= b }
511 33 { SELECT * FROM t3 WHERE 2643383279 = b OR c < b AND b <= 3282306647 }
512 34 { SELECT * FROM t3 WHERE a IS NULL }
513 35 { SELECT * FROM t3 WHERE 'writings' = a OR b = 378678316.5 }
514 36 { SELECT * FROM t3 WHERE 'and' >= c }
515 37 { SELECT * FROM t3 WHERE c < 'from' }
516 38 { SELECT * FROM t3 WHERE 'his' < c OR b < b }
517 39 { SELECT * FROM t3 WHERE 53594.08128 = b AND c >= b }
518 40 { SELECT * FROM t3 WHERE 'unalike' < c AND 'are' >= c AND a <= b }
519 41 { SELECT * FROM t3 WHERE b >= 4543.266482 OR 'Alpine' > a OR 271.2019091 <= a }
520 42 { SELECT * FROM t3 WHERE b = c }
521 43 { SELECT * FROM t3 WHERE c > a AND b < 'all' }
522 44 { SELECT * FROM t3 WHERE c BETWEEN 'hills' AND 'snapshots' AND c <= 'the' OR c = a }
523 45 { SELECT * FROM t3 WHERE b > c AND c >= 'hills' }
524 46 { SELECT * FROM t3 WHERE b > 'or' OR a <= 'hills' OR c IS NULL }
525 47 { SELECT * FROM t3 WHERE c > b OR b BETWEEN 1339.360726 AND 'onlookers' OR 1415926535 >= b }
526 48 { SELECT * FROM t3 WHERE a IS NULL }
527 49 { SELECT * FROM t3 WHERE a > 'other' }
528 50 { SELECT * FROM t3 WHERE 'the' <= c AND a <= c }
529 51 { SELECT * FROM t3 WHERE 346.0348610 = a AND c = b }
530 52 { SELECT * FROM t3 WHERE c BETWEEN 50.58223172 AND 'same' AND a < b }
531 53 { SELECT * FROM t3 WHERE 'Alpine' <= b AND c >= 'angle' OR b <= 271.2019091 }
532 54 { SELECT * FROM t3 WHERE a < a AND 1415926535 > b }
533 55 { SELECT * FROM t3 WHERE c > a AND 'have' >= c }
534 56 { SELECT * FROM t3 WHERE b <= b AND c > b }
535 57 { SELECT * FROM t3 WHERE a IS NULL AND c <= c }
536 58 { SELECT * FROM t3 WHERE b < c OR b = c }
537 59 { SELECT * FROM t3 WHERE c < b AND b >= 'it' }
538 60 { SELECT * FROM t3 WHERE a = b AND a <= b OR b >= a }
539 61 { SELECT * FROM t3 WHERE b = c }
540 62 { SELECT * FROM t3 WHERE c BETWEEN 'the' AND 271.2019091 OR c <= 3282306647 AND c >= b }
541 63 { SELECT * FROM t3 WHERE c >= c AND c < 'writings' }
542 64 { SELECT * FROM t3 WHERE c <= 3282306647 AND b > a OR 'unalike' <= a }
543 65 { SELECT * FROM t3 WHERE a > c }
544 66 { SELECT * FROM t3 WHERE c = 'it' OR b >= b }
545 67 { SELECT * FROM t3 WHERE c = a OR b < c }
546 68 { SELECT * FROM t3 WHERE b > a }
547 69 { SELECT * FROM t3 WHERE a < b OR a > 4543.266482 OR 'same' = b }
548 70 { SELECT * FROM t3 WHERE c < c OR b <= c OR a <= b }
549 71 { SELECT * FROM t3 WHERE c > a }
550 72 { SELECT * FROM t3 WHERE c > b }
551 73 { SELECT * FROM t3 WHERE b <= a }
552 74 { SELECT * FROM t3 WHERE 3282306647 < b AND a >= 'or' OR a >= 378678316.5 }
553 75 { SELECT * FROM t3 WHERE 50.58223172 <= c OR c = c AND b < b }
554 76 { SELECT * FROM t3 WHERE 'and' < b OR b < c OR c > 1339.360726 }
555 77 { SELECT * FROM t3 WHERE b <= c }
556 78 { SELECT * FROM t3 WHERE 'in' <= c }
557 79 { SELECT * FROM t3 WHERE c <= b AND a > a AND c < b }
558 80 { SELECT * FROM t3 WHERE 'over' < b }
559 81 { SELECT * FROM t3 WHERE b >= b OR b < c OR a < b }
560 82 { SELECT * FROM t3 WHERE 'towering' <= b OR 'towering' = a AND c > b }
561 83 { SELECT * FROM t3 WHERE 'peak' = a OR b BETWEEN 2643383279 AND 'the' }
562 84 { SELECT * FROM t3 WHERE 'an' < c AND c > 'the' AND c IS NULL }
563 85 { SELECT * FROM t3 WHERE a <= 'sometimes' AND a BETWEEN 'unalike' AND 1339.360726 }
564 86 { SELECT * FROM t3 WHERE 1339.360726 < c AND c IS NULL }
565 87 { SELECT * FROM t3 WHERE b > 'the' }
566 88 { SELECT * FROM t3 WHERE 'and' = a }
567 89 { SELECT * FROM t3 WHERE b >= b }
568 90 { SELECT * FROM t3 WHERE b >= 8979323846 }
569 91 { SELECT * FROM t3 WHERE c <= a }
570 92 { SELECT * FROM t3 WHERE a BETWEEN 'have' AND 'light' OR a > b OR a >= 378678316.5 }
571 93 { SELECT * FROM t3 WHERE c > 3282306647 }
572 94 { SELECT * FROM t3 WHERE b > c }
573 95 { SELECT * FROM t3 WHERE b >= a AND 'villages' > a AND b >= c }
574 96 { SELECT * FROM t3 WHERE 'angle' > a }
575 97 { SELECT * FROM t3 WHERE 'paintings' >= a }
576 98 { SELECT * FROM t3 WHERE 'or' >= c }
577 99 { SELECT * FROM t3 WHERE c < b }
580 101 { SELECT * FROM t3, t4 WHERE f < 'sometimes' OR 'over' <= g AND h < 1415926535 }
581 102 { SELECT * FROM t3, t4 WHERE h >= 'from' AND h < 6939937510 OR g > h }
582 103 { SELECT * FROM t3, t4 WHERE c <= h AND g = h AND c >= 'all' }
583 104 { SELECT * FROM t3, t4 WHERE c = a }
584 105 { SELECT * FROM t3, t4 WHERE 'of' >= h }
585 106 { SELECT * FROM t3, t4 WHERE f >= b AND a < g AND h < 'and' }
586 107 { SELECT * FROM t3, t4 WHERE f <= 8628034825 AND 0938446095 >= b }
587 108 { SELECT * FROM t3, t4 WHERE a < 'the' }
588 109 { SELECT * FROM t3, t4 WHERE f = 'sometimes' OR b < 'of' }
589 110 { SELECT * FROM t3, t4 WHERE c IS NULL }
590 111 { SELECT * FROM t3, t4 WHERE 'have' = b OR g <= 346.0348610 }
591 112 { SELECT * FROM t3, t4 WHERE f > b AND b <= h }
592 113 { SELECT * FROM t3, t4 WHERE f > c OR 'the' = a OR 50.58223172 = a }
593 114 { SELECT * FROM t3, t4 WHERE 2643383279 <= a AND c = a }
594 115 { SELECT * FROM t3, t4 WHERE h >= b AND 'it' <= b }
595 116 { SELECT * FROM t3, t4 WHERE g BETWEEN 'from' AND 'peak' }
596 117 { SELECT * FROM t3, t4 WHERE 'their' > a AND g > b AND f <= c }
597 118 { SELECT * FROM t3, t4 WHERE h = 5028841971 AND 'unalike' <= f }
598 119 { SELECT * FROM t3, t4 WHERE c IS NULL AND a = 3282306647 OR a <= 'Alpine' }
599 120 { SELECT * FROM t3, t4 WHERE 'sometimes' <= f OR 8214808651 >= a AND b <= 53594.08128 }
600 121 { SELECT * FROM t3, t4 WHERE 6939937510 <= f OR c < f OR 'sometimes' = c }
601 122 { SELECT * FROM t3, t4 WHERE b < 'onlookers' AND 'paintings' = g AND c <= h }
602 123 { SELECT * FROM t3, t4 WHERE a BETWEEN 'all' AND 'from' OR c > 346.0348610 }
603 124 { SELECT * FROM t3, t4 WHERE 'from' <= b OR a BETWEEN 53594.08128 AND 'their' AND c > a }
604 125 { SELECT * FROM t3, t4 WHERE h = 2643383279 }
605 126 { SELECT * FROM t3, t4 WHERE a <= 'the' }
606 127 { SELECT * FROM t3, t4 WHERE h <= c }
607 128 { SELECT * FROM t3, t4 WHERE g <= 346.0348610 AND 66.59334461 >= f AND f <= f }
608 129 { SELECT * FROM t3, t4 WHERE g >= c OR 'in' < b OR b > g }
609 130 { SELECT * FROM t3, t4 WHERE 'over' > g AND b BETWEEN 'unalike' AND 'remarkably' }
610 131 { SELECT * FROM t3, t4 WHERE h <= 2847564.823 }
611 132 { SELECT * FROM t3, t4 WHERE h <= 'remarkably' AND 4543.266482 > h }
612 133 { SELECT * FROM t3, t4 WHERE a >= c AND 'it' > g AND c < c }
613 134 { SELECT * FROM t3, t4 WHERE h <= 66.59334461 AND b > 3421170679 }
614 135 { SELECT * FROM t3, t4 WHERE h < 'are' OR f BETWEEN 0938446095 AND 'are' OR b = b }
615 136 { SELECT * FROM t3, t4 WHERE h = a OR 66.59334461 <= f }
616 137 { SELECT * FROM t3, t4 WHERE f > 'of' OR h <= h OR a = f }
617 138 { SELECT * FROM t3, t4 WHERE 'other' >= g }
618 139 { SELECT * FROM t3, t4 WHERE b <= 3421170679 }
619 140 { SELECT * FROM t3, t4 WHERE 'all' = f AND 4543.266482 = b OR f BETWEEN 'and' AND 'angle' }
620 141 { SELECT * FROM t3, t4 WHERE 'light' = f OR h BETWEEN 'remarkably' AND 1415926535 }
621 142 { SELECT * FROM t3, t4 WHERE 'hills' = f OR 'the' >= f }
622 143 { SELECT * FROM t3, t4 WHERE a > 346.0348610 }
623 144 { SELECT * FROM t3, t4 WHERE 5028841971 = h }
624 145 { SELECT * FROM t3, t4 WHERE b >= c AND 'the' >= g OR 45.64856692 <= g }
625 146 { SELECT * FROM t3, t4 WHERE c < 5028841971 }
626 147 { SELECT * FROM t3, t4 WHERE a > a }
627 148 { SELECT * FROM t3, t4 WHERE c = 'snapshots' }
628 149 { SELECT * FROM t3, t4 WHERE h > 1339.360726 AND 'and' > c }
629 150 { SELECT * FROM t3, t4 WHERE 'and' > g OR 'sometimes' = c }
630 151 { SELECT * FROM t3, t4 WHERE g >= 'the' AND b >= 'onlookers' }
631 152 { SELECT * FROM t3, t4 WHERE h BETWEEN 'other' AND 2643383279 }
632 153 { SELECT * FROM t3, t4 WHERE 'it' = b }
633 154 { SELECT * FROM t3, t4 WHERE f = c OR c BETWEEN 'and' AND 0.4811174502 }
634 155 { SELECT * FROM t3, t4 WHERE b <= 'sometimes' OR c <= 0938446095 }
635 156 { SELECT * FROM t3, t4 WHERE 'and' <= b }
636 157 { SELECT * FROM t3, t4 WHERE g > a AND f = 'the' AND b < a }
637 158 { SELECT * FROM t3, t4 WHERE a < 'an' }
638 159 { SELECT * FROM t3, t4 WHERE a BETWEEN 'his' AND 'same' OR 8628034825 > f }
639 160 { SELECT * FROM t3, t4 WHERE b = 'peak' }
640 161 { SELECT * FROM t3, t4 WHERE f IS NULL AND a >= h }
641 162 { SELECT * FROM t3, t4 WHERE a IS NULL OR 2643383279 = c }
642 163 { SELECT * FROM t3, t4 WHERE b >= 5028841971 AND f < c AND a IS NULL }
643 164 { SELECT * FROM t3, t4 WHERE a >= g }
644 165 { SELECT * FROM t3, t4 WHERE c IS NULL }
645 166 { SELECT * FROM t3, t4 WHERE h >= h }
646 167 { SELECT * FROM t3, t4 WHERE 'over' <= h }
647 168 { SELECT * FROM t3, t4 WHERE b < 4543.266482 OR b = 2643383279 OR 8628034825 < b }
648 169 { SELECT * FROM t3, t4 WHERE g >= 6939937510 }
649 170 { SELECT * FROM t3, t4 WHERE 'or' < a OR b < g }
650 171 { SELECT * FROM t3, t4 WHERE h < 'hills' OR 'and' > g }
651 172 { SELECT * FROM t3, t4 WHERE 'from' > f OR f <= f }
652 173 { SELECT * FROM t3, t4 WHERE 'viewed' > b AND f < c }
653 174 { SELECT * FROM t3, t4 WHERE 'of' <= a }
654 175 { SELECT * FROM t3, t4 WHERE f > 0938446095 }
655 176 { SELECT * FROM t3, t4 WHERE a = g }
656 177 { SELECT * FROM t3, t4 WHERE g >= b AND f BETWEEN 'peak' AND 'and' }
657 178 { SELECT * FROM t3, t4 WHERE g = a AND 'it' > f }
658 179 { SELECT * FROM t3, t4 WHERE a <= b OR 'from' > f }
659 180 { SELECT * FROM t3, t4 WHERE f < 'and' }
660 181 { SELECT * FROM t3, t4 WHERE 6939937510 < b OR 'sometimes' < h }
661 182 { SELECT * FROM t3, t4 WHERE f > g AND f < 'peak' }
662 183 { SELECT * FROM t3, t4 WHERE a <= 53594.08128 AND c <= f AND f >= c }
663 184 { SELECT * FROM t3, t4 WHERE f = c OR 'it' > b OR g BETWEEN 'the' AND 'all' }
664 185 { SELECT * FROM t3, t4 WHERE c <= g OR a = h }
665 186 { SELECT * FROM t3, t4 WHERE 'same' = b OR c >= 2643383279 }
666 187 { SELECT * FROM t3, t4 WHERE h <= g OR c > 66.59334461 OR a <= f }
667 188 { SELECT * FROM t3, t4 WHERE b < c AND f = 'writings' }
668 189 { SELECT * FROM t3, t4 WHERE b < a }
669 190 { SELECT * FROM t3, t4 WHERE c >= f OR c = 'and' }
670 191 { SELECT * FROM t3, t4 WHERE f >= 'peak' AND g > f AND h > g }
671 192 { SELECT * FROM t3, t4 WHERE a >= 8979323846 AND 'same' > b OR c = 'and' }
672 193 { SELECT * FROM t3, t4 WHERE c >= g OR 'writings' >= c AND b = 'all' }
673 194 { SELECT * FROM t3, t4 WHERE 'remarkably' < g }
674 195 { SELECT * FROM t3, t4 WHERE a BETWEEN 'or' AND 'paintings' AND g <= f }
675 196 { SELECT * FROM t3, t4 WHERE 0938446095 > b OR g <= a OR h > b }
676 197 { SELECT * FROM t3, t4 WHERE g = 2643383279 AND f = g }
677 198 { SELECT * FROM t3, t4 WHERE g < 8979323846 }
678 199 { SELECT * FROM t3, t4 WHERE 'are' <= b }
679 200 { SELECT * FROM t3, t4 WHERE (a=1415926535 AND f=8628034825)
680 OR (a=6939937510 AND f=2643383279) }
681 201 { SELECT * FROM t3, t4, t5, t6
682 WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t!=5)
683 OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t=123456) }
684 202 { SELECT * FROM t3, t4, t5, t6
685 WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t==5)
686 OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t!=123456) }
688 } {
689 do_test where8-4.$A.$B.1 {
690 unset -nocomplain R
691 set R [execsql $sql]
692 if {![info exists results($B)]} {
693 set results($B) $R
694 }
695 list
696 } {}
698 do_test where8-4.$A.$B.2 { lsort $R } [lsort $results($B)]
699 }
700 incr A
701 }
702 catch {unset results}
703 catch {unset A}
704 catch {unset B}
706 # At one point the following tests provoked an invalid write error (writing
707 # to memory that had already been freed). It was not possible to demonstrate
708 # that this bug could cause a query to return bad data.
709 #
710 do_test where8-5.1 {
711 db close
712 sqlite3 db test.db
713 sqlite3_db_config_lookaside db 0 0 0
714 execsql {
715 CREATE TABLE tA(
716 a, b, c, d, e, f, g, h,
717 i, j, k, l, m, n, o, p
718 );
719 }
720 execsql {
721 SELECT * FROM tA WHERE
722 a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
723 i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND
724 (p = 1 OR p = 2 OR p = 3)
725 }
726 } {}
727 do_test where8-5.2 {
728 execsql {
729 SELECT * FROM tA WHERE
730 a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
731 i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND
732 (p = 1 OR p = 2 OR p = 3) AND n=6 AND o=7
733 }
734 } {}
735 do_test where8-5.3 {
736 execsql {
737 INSERT INTO tA VALUES(1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5, 6, 7, 8);
738 CREATE UNIQUE INDEX tAI ON tA(p);
739 CREATE TABLE tB(x);
740 INSERT INTO tB VALUES('x');
741 }
742 execsql {
743 SELECT a, x FROM tA LEFT JOIN tB ON (
744 a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
745 i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND
746 (p = 1 OR p = 2 OR p = 3)
747 )
748 }
749 } {1 {}}
751 # The OR optimization and WITHOUT ROWID
752 #
753 do_execsql_test where8-6.1 {
754 CREATE TABLE t600(a PRIMARY KEY, b) WITHOUT rowid;
755 CREATE INDEX t600b ON t600(b);
756 INSERT INTO t600 VALUES('state','screen'),('exact','dolphin'),('green','mercury');
757 SELECT a, b, '|' FROM t600 WHERE a=='state' OR b='mercury' ORDER BY +a;
758 } {green mercury | state screen |}
760 finish_test