Debian Codegraph @

test/where2.test

raw
1 # 2005 July 28
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 use of indices in WHERE clauses
13 # based on recent changes to the optimizer.
14 #
15 # $Id: where2.test,v 1.15 2009/02/02 01:50:40 drh Exp $
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
20 # Build some test data
21 #
22 do_test where2-1.0 {
23 execsql {
24 BEGIN;
25 CREATE TABLE t1(w int, x int, y int, z int);
26 }
27 for {set i 1} {$i<=100} {incr i} {
28 set w $i
29 set x [expr {int(log($i)/log(2))}]
30 set y [expr {$i*$i + 2*$i + 1}]
31 set z [expr {$x+$y}]
32 ifcapable tclvar {
33 execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
34 } else {
35 execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
36 }
37 }
38 execsql {
39 CREATE UNIQUE INDEX i1w ON t1(w);
40 CREATE INDEX i1xy ON t1(x,y);
41 CREATE INDEX i1zyx ON t1(z,y,x);
42 COMMIT;
43 }
44 } {}
45
46 # Do an SQL statement. Append the search count to the end of the result.
47 #
48 proc count sql {
49 set ::sqlite_search_count 0
50 return [concat [execsql $sql] $::sqlite_search_count]
51 }
52
53 # This procedure executes the SQL. Then it checks to see if the OP_Sort
54 # opcode was executed. If an OP_Sort did occur, then "sort" is appended
55 # to the result. If no OP_Sort happened, then "nosort" is appended.
56 #
57 # This procedure is used to check to make sure sorting is or is not
58 # occurring as expected.
59 #
60 proc cksort {sql} {
61 set data [execsql $sql]
62 if {[db status sort]} {set x sort} {set x nosort}
63 lappend data $x
64 return $data
65 }
66
67 # This procedure executes the SQL. Then it appends to the result the
68 # "sort" or "nosort" keyword (as in the cksort procedure above) then
69 # it appends the name of the table and index used.
70 #
71 proc queryplan {sql} {
72 set ::sqlite_sort_count 0
73 set data [execsql $sql]
74 if {$::sqlite_sort_count} {set x sort} {set x nosort}
75 lappend data $x
76 set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
77 # puts eqp=$eqp
78 foreach {a b c x} $eqp {
79 if {[regexp {SCAN CONSTANT} $x]} {
80 # noop
81 } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
82 $x all ss as tab idx]} {
83 lappend data $tab $idx
84 } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+)\y} $x all ss as tab]} {
85 lappend data $tab *
86 }
87 }
88 return $data
89 }
90
91
92 # Prefer a UNIQUE index over another index.
93 #
94 do_test where2-1.1 {
95 queryplan {
96 SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396
97 }
98 } {85 6 7396 7402 nosort t1 i1w}
99
100 # Always prefer a rowid== constraint over any other index.
101 #
102 do_test where2-1.3 {
103 queryplan {
104 SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 AND rowid=85
105 }
106 } {85 6 7396 7402 nosort t1 *}
107
108 # When constrained by a UNIQUE index, the ORDER BY clause is always ignored.
109 #
110 do_test where2-2.1 {
111 queryplan {
112 SELECT * FROM t1 WHERE w=85 ORDER BY random();
113 }
114 } {85 6 7396 7402 nosort t1 i1w}
115 do_test where2-2.2 {
116 queryplan {
117 SELECT * FROM t1 WHERE x=6 AND y=7396 ORDER BY random();
118 }
119 } {85 6 7396 7402 sort t1 i1xy}
120 do_test where2-2.3 {
121 queryplan {
122 SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random();
123 }
124 } {85 6 7396 7402 nosort t1 *}
125
126 # Ticket [65bdeb9739605cc22966f49208452996ff29a640] 2014-02-26
127 # Make sure "ORDER BY random" does not gets optimized out.
128 #
129 do_test where2-2.4 {
130 db eval {
131 CREATE TABLE x1(a INTEGER PRIMARY KEY, b DEFAULT 1);
132 WITH RECURSIVE
133 cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<50)
134 INSERT INTO x1 SELECT x, 1 FROM cnt;
135 CREATE TABLE x2(x INTEGER PRIMARY KEY);
136 INSERT INTO x2 VALUES(1);
137 }
138 set sql {SELECT * FROM x1, x2 WHERE x=1 ORDER BY random()}
139 set out1 [db eval $sql]
140 set out2 [db eval $sql]
141 set out3 [db eval $sql]
142 expr {$out1!=$out2 && $out2!=$out3}
143 } {1}
144 do_execsql_test where2-2.5 {
145 -- random() is not optimized out
146 EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY random();
147 } {/ random/}
148 do_execsql_test where2-2.5b {
149 -- random() is not optimized out
150 EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY random();
151 } {/ SorterOpen /}
152 do_execsql_test where2-2.6 {
153 -- other constant functions are optimized out
154 EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY abs(5);
155 } {~/ abs/}
156 do_execsql_test where2-2.6b {
157 -- other constant functions are optimized out
158 EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY abs(5);
159 } {~/ SorterOpen /}
160
161
162
163 # Efficient handling of forward and reverse table scans.
164 #
165 do_test where2-3.1 {
166 queryplan {
167 SELECT * FROM t1 ORDER BY rowid LIMIT 2
168 }
169 } {1 0 4 4 2 1 9 10 nosort t1 *}
170 do_test where2-3.2 {
171 queryplan {
172 SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2
173 }
174 } {100 6 10201 10207 99 6 10000 10006 nosort t1 *}
175
176 # The IN operator can be used by indices at multiple layers
177 #
178 ifcapable subquery {
179 do_test where2-4.1 {
180 queryplan {
181 SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
182 AND x>0 AND x<10
183 ORDER BY w
184 }
185 } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
186 do_test where2-4.2 {
187 queryplan {
188 SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
189 AND x>0 AND x<10
190 ORDER BY w
191 }
192 } {99 6 10000 10006 sort t1 i1zyx}
193 do_test where2-4.3 {
194 queryplan {
195 SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
196 AND x>0 AND x<10
197 ORDER BY w
198 }
199 } {99 6 10000 10006 sort t1 i1zyx}
200 ifcapable compound {
201 do_test where2-4.4 {
202 queryplan {
203 SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
204 AND y IN (10000,10201)
205 AND x>0 AND x<10
206 ORDER BY w
207 }
208 } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
209 do_test where2-4.5 {
210 queryplan {
211 SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
212 AND y IN (SELECT 10000 UNION SELECT 10201)
213 AND x>0 AND x<10
214 ORDER BY w
215 }
216 } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
217 }
218 do_test where2-4.6a {
219 queryplan {
220 SELECT * FROM t1
221 WHERE x IN (1,2,3,4,5,6,7,8)
222 AND y IN (10000,10001,10002,10003,10004,10005)
223 ORDER BY x
224 }
225 } {99 6 10000 10006 nosort t1 i1xy}
226 do_test where2-4.6b {
227 queryplan {
228 SELECT * FROM t1
229 WHERE x IN (1,2,3,4,5,6,7,8)
230 AND y IN (10000,10001,10002,10003,10004,10005)
231 ORDER BY x DESC
232 }
233 } {99 6 10000 10006 nosort t1 i1xy}
234 do_test where2-4.6c {
235 queryplan {
236 SELECT * FROM t1
237 WHERE x IN (1,2,3,4,5,6,7,8)
238 AND y IN (10000,10001,10002,10003,10004,10005)
239 ORDER BY x, y
240 }
241 } {99 6 10000 10006 nosort t1 i1xy}
242 do_test where2-4.6d {
243 queryplan {
244 SELECT * FROM t1
245 WHERE x IN (1,2,3,4,5,6,7,8)
246 AND y IN (10000,10001,10002,10003,10004,10005)
247 ORDER BY x, y DESC
248 }
249 } {99 6 10000 10006 sort t1 i1xy}
250
251 # Duplicate entires on the RHS of an IN operator do not cause duplicate
252 # output rows.
253 #
254 do_test where2-4.6x {
255 queryplan {
256 SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
257 ORDER BY w
258 }
259 } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
260 do_test where2-4.6y {
261 queryplan {
262 SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
263 ORDER BY w DESC
264 }
265 } {100 6 10201 10207 99 6 10000 10006 sort t1 i1zyx}
266 ifcapable compound {
267 do_test where2-4.7 {
268 queryplan {
269 SELECT * FROM t1 WHERE z IN (
270 SELECT 10207 UNION ALL SELECT 10006
271 UNION ALL SELECT 10006 UNION ALL SELECT 10207)
272 ORDER BY w
273 }
274 } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
275 }
276
277 } ;# ifcapable subquery
278
279 # The use of an IN operator disables the index as a sorter.
280 #
281 do_test where2-5.1 {
282 queryplan {
283 SELECT * FROM t1 WHERE w=99 ORDER BY w
284 }
285 } {99 6 10000 10006 nosort t1 i1w}
286
287 ifcapable subquery {
288 do_test where2-5.2a {
289 queryplan {
290 SELECT * FROM t1 WHERE w IN (99) ORDER BY w
291 }
292 } {99 6 10000 10006 nosort t1 i1w}
293 do_test where2-5.2b {
294 queryplan {
295 SELECT * FROM t1 WHERE w IN (99) ORDER BY w DESC
296 }
297 } {99 6 10000 10006 nosort t1 i1w}
298 }
299
300 # Verify that OR clauses get translated into IN operators.
301 #
302 set ::idx {}
303 ifcapable subquery {set ::idx i1w}
304 do_test where2-6.1.1 {
305 queryplan {
306 SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w
307 }
308 } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
309 do_test where2-6.1.2 {
310 queryplan {
311 SELECT * FROM t1 WHERE 99=w OR 100=w ORDER BY +w
312 }
313 } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
314 do_test where2-6.2 {
315 queryplan {
316 SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w
317 }
318 } [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
319
320 do_test where2-6.3 {
321 queryplan {
322 SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w
323 }
324 } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 *}
325 do_test where2-6.4 {
326 queryplan {
327 SELECT *, '|' FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w
328 }
329 } {6 2 49 51 | 99 6 10000 10006 | 100 6 10201 10207 | sort t1 *}
330 do_test where2-6.5 {
331 queryplan {
332 SELECT *, '|' FROM t1 WHERE w=99 OR y=10201 OR 6=w ORDER BY +w
333 }
334 } {6 2 49 51 | 99 6 10000 10006 | 100 6 10201 10207 | sort t1 *}
335
336 set ::idx {}
337 ifcapable subquery {set ::idx i1zyx}
338 do_test where2-6.5 {
339 queryplan {
340 SELECT b.* FROM t1 a, t1 b
341 WHERE a.w=1 AND (a.y=b.z OR b.z=10)
342 ORDER BY +b.w
343 }
344 } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
345 do_test where2-6.6 {
346 queryplan {
347 SELECT b.* FROM t1 a, t1 b
348 WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10)
349 ORDER BY +b.w
350 }
351 } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
352
353 if {[permutation] != "no_optimization"} {
354
355 # Ticket #2249. Make sure the OR optimization is not attempted if
356 # comparisons between columns of different affinities are needed.
357 #
358 do_test where2-6.7 {
359 execsql {
360 CREATE TABLE t2249a(a TEXT UNIQUE, x CHAR(100));
361 CREATE TABLE t2249b(b INTEGER);
362 INSERT INTO t2249a(a) VALUES('0123');
363 INSERT INTO t2249b VALUES(123);
364 }
365 queryplan {
366 -- Because a is type TEXT and b is type INTEGER, both a and b
367 -- will attempt to convert to NUMERIC before the comparison.
368 -- They will thus compare equal.
369 --
370 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b;
371 }
372 } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
373 do_test where2-6.9 {
374 queryplan {
375 -- The + operator removes affinity from the rhs. No conversions
376 -- occur and the comparison is false. The result is an empty set.
377 --
378 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b;
379 }
380 } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
381 do_test where2-6.9.2 {
382 # The same thing but with the expression flipped around.
383 queryplan {
384 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a
385 }
386 } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
387 do_test where2-6.10 {
388 queryplan {
389 -- Use + on both sides of the comparison to disable indices
390 -- completely. Make sure we get the same result.
391 --
392 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
393 }
394 } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
395 do_test where2-6.11 {
396 # This will not attempt the OR optimization because of the a=b
397 # comparison.
398 queryplan {
399 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
400 }
401 } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
402 do_test where2-6.11.2 {
403 # Permutations of the expression terms.
404 queryplan {
405 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
406 }
407 } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
408 do_test where2-6.11.3 {
409 # Permutations of the expression terms.
410 queryplan {
411 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
412 }
413 } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
414 do_test where2-6.11.4 {
415 # Permutations of the expression terms.
416 queryplan {
417 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
418 }
419 } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
420 ifcapable explain&&subquery {
421 # These tests are not run if subquery support is not included in the
422 # build. This is because these tests test the "a = 1 OR a = 2" to
423 # "a IN (1, 2)" optimisation transformation, which is not enabled if
424 # subqueries and the IN operator is not available.
425 #
426 do_test where2-6.12 {
427 # In this case, the +b disables the affinity conflict and allows
428 # the OR optimization to be used again. The result is now an empty
429 # set, the same as in where2-6.9.
430 queryplan {
431 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
432 }
433 } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
434 do_test where2-6.12.2 {
435 # In this case, the +b disables the affinity conflict and allows
436 # the OR optimization to be used again. The result is now an empty
437 # set, the same as in where2-6.9.
438 queryplan {
439 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
440 }
441 } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
442 do_test where2-6.12.3 {
443 # In this case, the +b disables the affinity conflict and allows
444 # the OR optimization to be used again. The result is now an empty
445 # set, the same as in where2-6.9.
446 queryplan {
447 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
448 }
449 } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
450 do_test where2-6.13 {
451 # The addition of +a on the second term disabled the OR optimization.
452 # But we should still get the same empty-set result as in where2-6.9.
453 queryplan {
454 SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
455 }
456 } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1}
457 }
458
459 # Variations on the order of terms in a WHERE clause in order
460 # to make sure the OR optimizer can recognize them all.
461 do_test where2-6.20 {
462 queryplan {
463 SELECT x.a, y.a FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
464 }
465 } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
466 ifcapable explain&&subquery {
467 # These tests are not run if subquery support is not included in the
468 # build. This is because these tests test the "a = 1 OR a = 2" to
469 # "a IN (1, 2)" optimisation transformation, which is not enabled if
470 # subqueries and the IN operator is not available.
471 #
472 do_test where2-6.21 {
473 queryplan {
474 SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y
475 WHERE x.a=y.a OR y.a='hello'
476 }
477 } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
478 do_test where2-6.22 {
479 queryplan {
480 SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y
481 WHERE y.a=x.a OR y.a='hello'
482 }
483 } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
484 do_test where2-6.23 {
485 queryplan {
486 SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y
487 WHERE y.a='hello' OR x.a=y.a
488 }
489 } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1}
490 }
491
492 # Unique queries (queries that are guaranteed to return only a single
493 # row of result) do not call the sorter. But all tables must give
494 # a unique result. If any one table in the join does not give a unique
495 # result then sorting is necessary.
496 #
497 do_test where2-7.1 {
498 cksort {
499 create table t8(a unique, b, c);
500 insert into t8 values(1,2,3);
501 insert into t8 values(2,3,4);
502 create table t9(x,y);
503 insert into t9 values(2,4);
504 insert into t9 values(2,3);
505 select y from t8, t9 where a=1 order by a, y;
506 }
507 } {3 4 sort}
508 do_test where2-7.2 {
509 cksort {
510 select * from t8 where a=1 order by b, c
511 }
512 } {1 2 3 nosort}
513 do_test where2-7.3 {
514 cksort {
515 select * from t8, t9 where a=1 and y=3 order by b, x
516 }
517 } {1 2 3 2 3 sort}
518 do_test where2-7.4 {
519 cksort {
520 create unique index i9y on t9(y);
521 select * from t8, t9 where a=1 and y=3 order by b, x
522 }
523 } {1 2 3 2 3 nosort}
524
525 } ;# if {[permutation] != "no_optimization"}
526
527 # Ticket #1807. Using IN constrains on multiple columns of
528 # a multi-column index.
529 #
530 ifcapable subquery {
531 do_test where2-8.1 {
532 execsql {
533 SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2)
534 }
535 } {}
536 do_test where2-8.2 {
537 execsql {
538 SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6)
539 }
540 } {}
541 execsql {CREATE TABLE tx AS SELECT * FROM t1}
542 do_test where2-8.3 {
543 execsql {
544 SELECT w FROM t1
545 WHERE x IN (SELECT x FROM tx WHERE rowid<0)
546 AND +y IN (SELECT y FROM tx WHERE rowid=1)
547 }
548 } {}
549 do_test where2-8.4 {
550 execsql {
551 SELECT w FROM t1
552 WHERE x IN (SELECT x FROM tx WHERE rowid=1)
553 AND y IN (SELECT y FROM tx WHERE rowid<0)
554 }
555 } {}
556 #set sqlite_where_trace 1
557 do_test where2-8.5 {
558 execsql {
559 CREATE INDEX tx_xyz ON tx(x, y, z, w);
560 SELECT w FROM tx
561 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
562 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
563 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14)
564 }
565 } {12 13 14}
566 do_test where2-8.6 {
567 execsql {
568 SELECT w FROM tx
569 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
570 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14)
571 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
572 }
573 } {12 13 14}
574 do_test where2-8.7 {
575 execsql {
576 SELECT w FROM tx
577 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14)
578 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
579 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
580 }
581 } {10 11 12 13 14 15}
582 do_test where2-8.8 {
583 execsql {
584 SELECT w FROM tx
585 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
586 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
587 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
588 }
589 } {10 11 12 13 14 15 16 17 18 19 20}
590 do_test where2-8.9 {
591 execsql {
592 SELECT w FROM tx
593 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
594 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
595 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4)
596 }
597 } {}
598 do_test where2-8.10 {
599 execsql {
600 SELECT w FROM tx
601 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
602 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4)
603 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
604 }
605 } {}
606 do_test where2-8.11 {
607 execsql {
608 SELECT w FROM tx
609 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4)
610 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
611 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
612 }
613 } {}
614 do_test where2-8.12 {
615 execsql {
616 SELECT w FROM tx
617 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
618 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
619 AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2)
620 }
621 } {}
622 do_test where2-8.13 {
623 execsql {
624 SELECT w FROM tx
625 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
626 AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2)
627 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
628 }
629 } {}
630 do_test where2-8.14 {
631 execsql {
632 SELECT w FROM tx
633 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2)
634 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
635 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
636 }
637 } {}
638 do_test where2-8.15 {
639 execsql {
640 SELECT w FROM tx
641 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
642 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
643 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300)
644 }
645 } {}
646 do_test where2-8.16 {
647 execsql {
648 SELECT w FROM tx
649 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
650 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300)
651 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
652 }
653 } {}
654 do_test where2-8.17 {
655 execsql {
656 SELECT w FROM tx
657 WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300)
658 AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
659 AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
660 }
661 } {}
662 do_test where2-8.18 {
663 execsql {
664 SELECT w FROM tx
665 WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
666 AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
667 AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300)
668 }
669 } {}
670 do_test where2-8.19 {
671 execsql {
672 SELECT w FROM tx
673 WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
674 AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300)
675 AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
676 }
677 } {}
678 do_test where2-8.20 {
679 execsql {
680 SELECT w FROM tx
681 WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300)
682 AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
683 AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
684 }
685 } {}
686 }
687
688 # Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized
689 # when we have an index on A and B.
690 #
691 ifcapable or_opt&&tclvar {
692 do_test where2-9.1 {
693 execsql {
694 BEGIN;
695 CREATE TABLE t10(a,b,c);
696 INSERT INTO t10 VALUES(1,1,1);
697 INSERT INTO t10 VALUES(1,2,2);
698 INSERT INTO t10 VALUES(1,3,3);
699 }
700 for {set i 4} {$i<=1000} {incr i} {
701 execsql {INSERT INTO t10 VALUES(1,$i,$i)}
702 }
703 execsql {
704 CREATE INDEX i10 ON t10(a,b);
705 COMMIT;
706 SELECT count(*) FROM t10;
707 }
708 } 1000
709 ifcapable subquery {
710 do_test where2-9.2 {
711 count {
712 SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
713 }
714 } {1 2 2 1 3 3 7}
715 }
716 }
717
718 # Indices with redundant columns
719 #
720 do_test where2-11.1 {
721 execsql {
722 CREATE TABLE t11(a,b,c,d);
723 CREATE INDEX i11aba ON t11(a,b,a,c); -- column A occurs twice.
724 INSERT INTO t11 VALUES(1,2,3,4);
725 INSERT INTO t11 VALUES(5,6,7,8);
726 INSERT INTO t11 VALUES(1,2,9,10);
727 INSERT INTO t11 VALUES(5,11,12,13);
728 SELECT c FROM t11 WHERE a=1 AND b=2 ORDER BY c;
729 }
730 } {3 9}
731 do_test where2-11.2 {
732 execsql {
733 CREATE INDEX i11cccccccc ON t11(c,c,c,c,c,c,c,c); -- repeated column
734 SELECT d FROM t11 WHERE c=9;
735 }
736 } {10}
737 do_test where2-11.3 {
738 execsql {
739 SELECT d FROM t11 WHERE c IN (1,2,3,4,5);
740 }
741 } {4}
742 do_test where2-11.4 {
743 execsql {
744 SELECT d FROM t11 WHERE c=7 OR (a=1 AND b=2) ORDER BY d;
745 }
746 } {4 8 10}
747
748 # Verify that the OR clause is used in an outer loop even when
749 # the OR clause scores slightly better on an inner loop.
750 if {[permutation] != "no_optimization"} {
751 do_execsql_test where2-12.1 {
752 CREATE TABLE t12(x INTEGER PRIMARY KEY, y INT, z CHAR(100));
753 CREATE INDEX t12y ON t12(y);
754 EXPLAIN QUERY PLAN
755 SELECT a.x, b.x
756 FROM t12 AS a JOIN t12 AS b ON a.y=b.x
757 WHERE (b.x=$abc OR b.y=$abc);
758 } {/SEARCH b .*SEARCH b /}
759 }
760
761 # Verify that all necessary OP_OpenRead opcodes occur in the OR optimization.
762 #
763 do_execsql_test where2-13.1 {
764 CREATE TABLE t13(a,b);
765 CREATE INDEX t13a ON t13(a);
766 INSERT INTO t13 VALUES(4,5);
767 SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4;
768 } {4 5}
769
770 # https://sqlite.org/src/info/5e3c886796e5512e (2016-03-09)
771 # Correlated subquery on the RHS of an IN operator
772 #
773 do_execsql_test where2-14.1 {
774 CREATE TABLE t14a(x INTEGER PRIMARY KEY);
775 INSERT INTO t14a(x) VALUES(1),(2),(3),(4);
776 CREATE TABLE t14b(y INTEGER PRIMARY KEY);
777 INSERT INTO t14b(y) VALUES(1);
778 SELECT x FROM t14a WHERE x NOT IN (SELECT x FROM t14b);
779 } {}
780
781 # Demonstrate that the code removed by
782 # https://sqlite.org/src/info/2025-09-17T17:09:07Z is in fact
783 # necessary. Answer confirmed by PostgreSQL
784 #
785 do_execsql_test where2-15.1 {
786 DROP TABLE IF EXISTS t1;
787 CREATE TABLE t1(a INTEGER PRIMARY KEY,b INT);
788 INSERT INTO t1 VALUES(12,34),(56,78),(90,12);
789 DROP TABLE IF EXISTS t2;
790 CREATE TABLE t2(x INT UNIQUE);
791 INSERT INTO t2 VALUES(78),(12);
792 SELECT a,b,quote(x),'|'
793 FROM t1 LEFT JOIN (SELECT x FROM t2) AS s1 ON t1.b=s1.x
794 ORDER BY a,EXISTS(SELECT 1 FROM t1 LEFT JOIN (SELECT x AS y FROM t2) AS s2 ON t1.b=s2.y),x;
795 } {12 34 NULL | 56 78 78 | 90 12 12 |}
796
797 # Demonstrate that CROSS JOIN is a join reordering barrier.
798 #
799 reset_db
800 do_execsql_test where2-16.1 {
801 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
802 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
803 CREATE TABLE t3(e INTEGER PRIMARY KEY, f INT);
804 CREATE TABLE t4(g INTEGER PRIMARY KEY, h INT);
805 }
806
807 # Here the query planner wants to move t4 forward so that it is in front of
808 # t1. Ensure that does not happen.
809 do_execsql_test where2-16.2 {
810 EXPLAIN QUERY PLAN
811 SELECT *
812 FROM t1, t2 CROSS JOIN t3, t4
813 WHERE t4.g=1
814 AND t1.a=t4.h
815 AND t2.c=t1.b
816 AND t3.e=t2.d;
817 } {~/.* t4 .* t[12] .*/}
818
819 # In this case the planner wants to move t1 to come after t4. Ensure that
820 # does not happen.
821 do_execsql_test where2-16.2 {
822 EXPLAIN QUERY PLAN
823 SELECT *
824 FROM t1, t2 CROSS JOIN t3, t4
825 WHERE t2.c=1
826 AND t3.e=t2.d
827 AND t4.g=t3.f
828 AND t1.a=t4.h;
829 } {~/.* t[34] .* t1 .*/}
830
831 # 2026-05-18 bug report 2026-05-17T11:46:40Z
832 #
833 do_execsql_test where2-17.0 {
834 DROP TABLE IF EXISTS t1;
835 CREATE TABLE t1(a TEXT);
836 INSERT INTO t1 VALUES('A'),('b');
837 SELECT * FROM t1 WHERE (a='a' COLLATE nocase) OR a='b' ORDER BY +a;
838 } {A b}
839 do_execsql_test where2-17.1 {
840 CREATE INDEX t1_a ON t1(a);
841 SELECT * FROM t1 WHERE (a='a' COLLATE nocase) OR a='b' ORDER BY +a;
842 } {A b}
843
844 finish_test