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