| blob | 0a8cfd572b77a235796bd381f9e907b76efdd1cb |
1 # 2001 September 15
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 clases.
13 #
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
18 # Build some test data
19 #
20 do_test where-1.0 {
21 execsql {
22 CREATE TABLE t1(w int, x int, y int);
23 CREATE TABLE t2(p int, q int, r int, s int);
24 }
25 for {set i 1} {$i<=100} {incr i} {
26 set w $i
27 set x [expr {int(log($i)/log(2))}]
28 set y [expr {$i*$i + 2*$i + 1}]
29 execsql "INSERT INTO t1 VALUES($w,$x,$y)"
30 }
32 ifcapable subquery {
33 execsql {
34 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
35 }
36 } else {
37 set maxy [execsql {select max(y) from t1}]
38 execsql "
39 INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
40 "
41 }
43 execsql {
44 CREATE INDEX i1w ON t1("w"); -- Verify quoted identifier names
45 CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility
46 CREATE INDEX i2p ON t2(p);
47 CREATE INDEX i2r ON t2(r);
48 CREATE INDEX i2qs ON t2(q, s);
49 }
50 } {}
52 # Do an SQL statement. Append the search count to the end of the result.
53 #
54 proc count sql {
55 set ::sqlite_search_count 0
56 return [concat [execsql $sql] $::sqlite_search_count]
57 }
59 # Verify that queries use an index. We are using the special variable
60 # "sqlite_search_count" which tallys the number of executions of MoveTo
61 # and Next operators in the VDBE. By verifing that the search count is
62 # small we can be assured that indices are being used properly.
63 #
64 do_test where-1.1.1 {
65 count {SELECT x, y, w FROM t1 WHERE w=10}
66 } {3 121 10 3}
67 do_test where-1.1.1b {
68 count {SELECT x, y, w FROM t1 WHERE w IS 10}
69 } {3 121 10 3}
70 do_eqp_test where-1.1.2 {
71 SELECT x, y, w FROM t1 WHERE w=10
72 } {*SEARCH t1 USING INDEX i1w (w=?)*}
73 do_eqp_test where-1.1.2b {
74 SELECT x, y, w FROM t1 WHERE w IS 10
75 } {*SEARCH t1 USING INDEX i1w (w=?)*}
76 do_test where-1.1.3 {
77 db status step
78 } {0}
79 do_test where-1.1.4 {
80 db eval {SELECT x, y, w FROM t1 WHERE +w=10}
81 } {3 121 10}
82 do_test where-1.1.5 {
83 db status step
84 } {99}
85 do_eqp_test where-1.1.6 {
86 SELECT x, y, w FROM t1 WHERE +w=10
87 } {*SCAN t1*}
88 do_test where-1.1.7 {
89 count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
90 } {3 121 10 3}
91 do_eqp_test where-1.1.8 {
92 SELECT x, y, w AS abc FROM t1 WHERE abc=10
93 } {*SEARCH t1 USING INDEX i1w (w=?)*}
94 do_test where-1.1.9 {
95 db status step
96 } {0}
97 do_test where-1.2.1 {
98 count {SELECT x, y, w FROM t1 WHERE w=11}
99 } {3 144 11 3}
100 do_test where-1.2.2 {
101 count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
102 } {3 144 11 3}
103 do_test where-1.3.1 {
104 count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
105 } {3 144 11 3}
106 do_test where-1.3.2 {
107 count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
108 } {3 144 11 3}
109 do_test where-1.3.3 {
110 count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc}
111 } {3 144 11 3}
112 do_test where-1.4.1 {
113 count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
114 } {11 3 144 3}
115 do_test where-1.4.1b {
116 count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2}
117 } {11 3 144 3}
118 do_eqp_test where-1.4.2 {
119 SELECT w, x, y FROM t1 WHERE 11=w AND x>2
120 } {*SEARCH t1 USING INDEX i1w (w=?)*}
121 do_eqp_test where-1.4.2b {
122 SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2
123 } {*SEARCH t1 USING INDEX i1w (w=?)*}
124 do_test where-1.4.3 {
125 count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
126 } {11 3 144 3}
127 do_eqp_test where-1.4.4 {
128 SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
129 } {*SEARCH t1 USING INDEX i1w (w=?)*}
130 do_test where-1.5 {
131 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
132 } {3 144 3}
133 do_eqp_test where-1.5.2 {
134 SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
135 } {*SEARCH t1 USING INDEX i1w (w=?)*}
136 do_test where-1.6 {
137 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
138 } {3 144 3}
139 do_test where-1.7 {
140 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
141 } {3 144 3}
142 do_test where-1.8 {
143 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
144 } {3 144 3}
145 do_eqp_test where-1.8.2 {
146 SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
147 } {*SEARCH t1 USING INDEX i1xy (x=? AND y=?)*}
148 do_eqp_test where-1.8.3 {
149 SELECT x, y FROM t1 WHERE y=144 AND x=3
150 } {*SEARCH t1 USING COVERING INDEX i1xy (x=? AND y=?)*}
151 do_test where-1.9 {
152 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
153 } {3 144 3}
154 do_test where-1.10 {
155 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
156 } {3 121 3}
157 do_test where-1.11 {
158 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
159 } {3 100 3}
160 do_test where-1.11b {
161 count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10}
162 } {3 100 3}
164 # New for SQLite version 2.1: Verify that that inequality constraints
165 # are used correctly.
166 #
167 do_test where-1.12 {
168 count {SELECT w FROM t1 WHERE x=3 AND y<100}
169 } {8 3}
170 do_test where-1.12b {
171 count {SELECT w FROM t1 WHERE x IS 3 AND y<100}
172 } {8 3}
173 do_test where-1.13 {
174 count {SELECT w FROM t1 WHERE x=3 AND 100>y}
175 } {8 3}
176 do_test where-1.14 {
177 count {SELECT w FROM t1 WHERE 3=x AND y<100}
178 } {8 3}
179 do_test where-1.14b {
180 count {SELECT w FROM t1 WHERE 3 IS x AND y<100}
181 } {8 3}
182 do_test where-1.15 {
183 count {SELECT w FROM t1 WHERE 3=x AND 100>y}
184 } {8 3}
185 do_test where-1.16 {
186 count {SELECT w FROM t1 WHERE x=3 AND y<=100}
187 } {8 9 5}
188 do_test where-1.17 {
189 count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
190 } {8 9 5}
191 do_test where-1.18 {
192 count {SELECT w FROM t1 WHERE x=3 AND y>225}
193 } {15 3}
194 do_test where-1.18b {
195 count {SELECT w FROM t1 WHERE x IS 3 AND y>225}
196 } {15 3}
197 do_test where-1.19 {
198 count {SELECT w FROM t1 WHERE x=3 AND 225<y}
199 } {15 3}
200 do_test where-1.20 {
201 count {SELECT w FROM t1 WHERE x=3 AND y>=225}
202 } {14 15 5}
203 do_test where-1.21 {
204 count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
205 } {14 15 5}
206 do_test where-1.22 {
207 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
208 } {11 12 5}
209 do_test where-1.22b {
210 count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196}
211 } {11 12 5}
212 do_test where-1.23 {
213 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
214 } {10 11 12 13 9}
215 do_test where-1.24 {
216 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
217 } {11 12 5}
218 do_test where-1.25 {
219 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
220 } {10 11 12 13 9}
222 # Need to work on optimizing the BETWEEN operator.
223 #
224 # do_test where-1.26 {
225 # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
226 # } {10 11 12 13 9}
228 do_test where-1.27 {
229 count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
230 } {10 10}
232 do_test where-1.28 {
233 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
234 } {10 99}
235 do_test where-1.29 {
236 count {SELECT w FROM t1 WHERE y==121}
237 } {10 99}
240 do_test where-1.30 {
241 count {SELECT w FROM t1 WHERE w>97}
242 } {98 99 100 3}
243 do_test where-1.31 {
244 count {SELECT w FROM t1 WHERE w>=97}
245 } {97 98 99 100 4}
246 do_test where-1.33 {
247 count {SELECT w FROM t1 WHERE w==97}
248 } {97 2}
249 do_test where-1.33.1 {
250 count {SELECT w FROM t1 WHERE w<=97 AND w==97}
251 } {97 2}
252 do_test where-1.33.2 {
253 count {SELECT w FROM t1 WHERE w<98 AND w==97}
254 } {97 2}
255 do_test where-1.33.3 {
256 count {SELECT w FROM t1 WHERE w>=97 AND w==97}
257 } {97 2}
258 do_test where-1.33.4 {
259 count {SELECT w FROM t1 WHERE w>96 AND w==97}
260 } {97 2}
261 do_test where-1.33.5 {
262 count {SELECT w FROM t1 WHERE w==97 AND w==97}
263 } {97 2}
264 do_test where-1.34 {
265 count {SELECT w FROM t1 WHERE w+1==98}
266 } {97 99}
267 do_test where-1.35 {
268 count {SELECT w FROM t1 WHERE w<3}
269 } {1 2 3}
270 do_test where-1.36 {
271 count {SELECT w FROM t1 WHERE w<=3}
272 } {1 2 3 4}
273 do_test where-1.37 {
274 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
275 } {1 2 3 99}
277 do_test where-1.38 {
278 count {SELECT (w) FROM t1 WHERE (w)>(97)}
279 } {98 99 100 3}
280 do_test where-1.39 {
281 count {SELECT (w) FROM t1 WHERE (w)>=(97)}
282 } {97 98 99 100 4}
283 do_test where-1.40 {
284 count {SELECT (w) FROM t1 WHERE (w)==(97)}
285 } {97 2}
286 do_test where-1.41 {
287 count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
288 } {97 99}
291 # Do the same kind of thing except use a join as the data source.
292 #
293 do_test where-2.1 {
294 count {
295 SELECT w, p FROM t2, t1
296 WHERE x=q AND y=s AND r=8977
297 }
298 } {34 67 6}
299 do_test where-2.2 {
300 count {
301 SELECT w, p FROM t2, t1
302 WHERE x=q AND s=y AND r=8977
303 }
304 } {34 67 6}
305 do_test where-2.3 {
306 count {
307 SELECT w, p FROM t2, t1
308 WHERE x=q AND s=y AND r=8977 AND w>10
309 }
310 } {34 67 6}
311 do_test where-2.4 {
312 count {
313 SELECT w, p FROM t2, t1
314 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
315 }
316 } {34 67 6}
317 do_test where-2.5 {
318 count {
319 SELECT w, p FROM t2, t1
320 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
321 }
322 } {34 67 6}
323 do_test where-2.6 {
324 count {
325 SELECT w, p FROM t2, t1
326 WHERE x=q AND p=77 AND s=y AND w>5
327 }
328 } {24 77 6}
329 do_test where-2.7 {
330 count {
331 SELECT w, p FROM t1, t2
332 WHERE x=q AND p>77 AND s=y AND w=5
333 }
334 } {5 96 6}
336 # Lets do a 3-way join.
337 #
338 do_test where-3.1 {
339 count {
340 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
341 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
342 }
343 } {11 90 11 8}
344 do_test where-3.2 {
345 count {
346 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
347 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
348 }
349 } {12 89 12 8}
350 do_test where-3.3 {
351 count {
352 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
353 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
354 }
355 } {15 86 86 8}
357 # Test to see that the special case of a constant WHERE clause is
358 # handled.
359 #
360 do_test where-4.1 {
361 count {
362 SELECT * FROM t1 WHERE 0
363 }
364 } {0}
365 do_test where-4.2 {
366 count {
367 SELECT * FROM t1 WHERE 1 LIMIT 1
368 }
369 } {1 0 4 0}
370 do_test where-4.3 {
371 execsql {
372 SELECT 99 WHERE 0
373 }
374 } {}
375 do_test where-4.4 {
376 execsql {
377 SELECT 99 WHERE 1
378 }
379 } {99}
380 do_test where-4.5 {
381 execsql {
382 SELECT 99 WHERE 0.1
383 }
384 } {99}
385 do_test where-4.6 {
386 execsql {
387 SELECT 99 WHERE 0.0
388 }
389 } {}
390 do_test where-4.7 {
391 execsql {
392 SELECT count(*) FROM t1 WHERE t1.w
393 }
394 } {100}
396 # Verify that IN operators in a WHERE clause are handled correctly.
397 # Omit these tests if the build is not capable of sub-queries.
398 #
399 ifcapable subquery {
400 do_test where-5.1 {
401 count {
402 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
403 }
404 } {1 0 4 2 1 9 3 1 16 4}
405 do_test where-5.2 {
406 count {
407 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
408 }
409 } {1 0 4 2 1 9 3 1 16 102}
410 do_test where-5.3a {
411 count {
412 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
413 }
414 } {1 0 4 2 1 9 3 1 16 12}
415 do_test where-5.3b {
416 count {
417 SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1;
418 }
419 } {1 0 4 2 1 9 3 1 16 12}
420 do_test where-5.3c {
421 count {
422 SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1;
423 }
424 } {1 0 4 2 1 9 3 1 16 12}
425 do_test where-5.3d {
426 count {
427 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC;
428 }
429 } {3 1 16 2 1 9 1 0 4 11}
430 do_test where-5.4 {
431 count {
432 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
433 }
434 } {1 0 4 2 1 9 3 1 16 102}
435 do_test where-5.5 {
436 count {
437 SELECT * FROM t1 WHERE rowid IN
438 (select rowid from t1 where rowid IN (-1,2,4))
439 ORDER BY 1;
440 }
441 } {2 1 9 4 2 25 3}
442 do_test where-5.6 {
443 count {
444 SELECT * FROM t1 WHERE rowid+0 IN
445 (select rowid from t1 where rowid IN (-1,2,4))
446 ORDER BY 1;
447 }
448 } {2 1 9 4 2 25 103}
449 do_test where-5.7 {
450 count {
451 SELECT * FROM t1 WHERE w IN
452 (select rowid from t1 where rowid IN (-1,2,4))
453 ORDER BY 1;
454 }
455 } {2 1 9 4 2 25 9}
456 do_test where-5.8 {
457 count {
458 SELECT * FROM t1 WHERE w+0 IN
459 (select rowid from t1 where rowid IN (-1,2,4))
460 ORDER BY 1;
461 }
462 } {2 1 9 4 2 25 103}
463 do_test where-5.9 {
464 count {
465 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
466 }
467 } {2 1 9 3 1 16 6}
468 do_test where-5.10 {
469 count {
470 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
471 }
472 } {2 1 9 3 1 16 199}
473 do_test where-5.11 {
474 count {
475 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
476 }
477 } {79 6 6400 89 6 8100 199}
478 do_test where-5.12 {
479 count {
480 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
481 }
482 } {79 6 6400 89 6 8100 7}
483 do_test where-5.13 {
484 count {
485 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
486 }
487 } {2 1 9 3 1 16 6}
488 do_test where-5.14 {
489 count {
490 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
491 }
492 } {2 1 9 5}
493 do_test where-5.15 {
494 count {
495 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
496 }
497 } {2 1 9 3 1 16 9}
498 do_test where-5.100 {
499 db eval {
500 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
501 ORDER BY x, y
502 }
503 } {2 1 9 54 5 3025 62 5 3969}
504 do_test where-5.101 {
505 db eval {
506 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
507 ORDER BY x DESC, y DESC
508 }
509 } {62 5 3969 54 5 3025 2 1 9}
510 do_test where-5.102 {
511 db eval {
512 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
513 ORDER BY x DESC, y
514 }
515 } {54 5 3025 62 5 3969 2 1 9}
516 do_test where-5.103 {
517 db eval {
518 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
519 ORDER BY x, y DESC
520 }
521 } {2 1 9 62 5 3969 54 5 3025}
522 }
524 # This procedure executes the SQL. Then it checks to see if the OP_Sort
525 # opcode was executed. If an OP_Sort did occur, then "sort" is appended
526 # to the result. If no OP_Sort happened, then "nosort" is appended.
527 #
528 # This procedure is used to check to make sure sorting is or is not
529 # occurring as expected.
530 #
531 proc cksort {sql} {
532 set data [execsql $sql]
533 if {[db status sort]} {set x sort} {set x nosort}
534 lappend data $x
535 return $data
536 }
537 # Check out the logic that attempts to implement the ORDER BY clause
538 # using an index rather than by sorting.
539 #
540 do_test where-6.1 {
541 execsql {
542 CREATE TABLE t3(a,b,c);
543 CREATE INDEX t3a ON t3(a);
544 CREATE INDEX t3bc ON t3(b,c);
545 CREATE INDEX t3acb ON t3(a,c,b);
546 INSERT INTO t3 SELECT w, 101-w, y FROM t1;
547 SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
548 ANALYZE;
549 }
550 } {100 5050 5050 348550}
551 do_test where-6.2 {
552 cksort {
553 SELECT * FROM t3 ORDER BY a LIMIT 3
554 }
555 } {1 100 4 2 99 9 3 98 16 nosort}
556 do_test where-6.3 {
557 cksort {
558 SELECT * FROM t3 ORDER BY a+1 LIMIT 3
559 }
560 } {1 100 4 2 99 9 3 98 16 sort}
561 do_test where-6.4 {
562 cksort {
563 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
564 }
565 } {1 100 4 2 99 9 3 98 16 nosort}
566 do_test where-6.5 {
567 cksort {
568 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
569 }
570 } {1 100 4 2 99 9 3 98 16 nosort}
571 do_test where-6.6 {
572 cksort {
573 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
574 }
575 } {1 100 4 2 99 9 3 98 16 nosort}
576 do_test where-6.7.1 {
577 cksort {
578 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10
579 }
580 } {/1 100 4 2 99 9 3 98 16 .* nosort/}
581 do_test where-6.7.2 {
582 cksort {
583 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1
584 }
585 } {1 100 4 nosort}
586 ifcapable subquery {
587 do_test where-6.8a {
588 cksort {
589 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
590 }
591 } {1 100 4 2 99 9 3 98 16 nosort}
592 do_test where-6.8b {
593 cksort {
594 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a DESC LIMIT 3
595 }
596 } {9 92 100 7 94 64 5 96 36 nosort}
597 }
598 do_test where-6.9.1 {
599 cksort {
600 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
601 }
602 } {1 100 4 nosort}
603 do_test where-6.9.1.1 {
604 cksort {
605 SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3
606 }
607 } {1 100 4 nosort}
608 do_test where-6.9.1.2 {
609 cksort {
610 SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3
611 }
612 } {1 100 4 nosort}
613 do_test where-6.9.2 {
614 cksort {
615 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
616 }
617 } {1 100 4 nosort}
618 do_test where-6.9.3 {
619 cksort {
620 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
621 }
622 } {1 100 4 nosort}
623 do_test where-6.9.4 {
624 cksort {
625 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
626 }
627 } {1 100 4 nosort}
628 do_test where-6.9.5 {
629 cksort {
630 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
631 }
632 } {1 100 4 nosort}
633 do_test where-6.9.6 {
634 cksort {
635 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
636 }
637 } {1 100 4 nosort}
638 do_test where-6.9.7 {
639 cksort {
640 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
641 }
642 } {1 100 4 nosort}
643 do_test where-6.9.8 {
644 cksort {
645 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
646 }
647 } {1 100 4 nosort}
648 do_test where-6.9.9 {
649 cksort {
650 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
651 }
652 } {1 100 4 nosort}
653 do_test where-6.10 {
654 cksort {
655 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
656 }
657 } {1 100 4 nosort}
658 do_test where-6.11 {
659 cksort {
660 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
661 }
662 } {1 100 4 nosort}
663 do_test where-6.12 {
664 cksort {
665 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
666 }
667 } {1 100 4 nosort}
668 do_test where-6.13 {
669 cksort {
670 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
671 }
672 } {100 1 10201 99 2 10000 98 3 9801 nosort}
673 do_test where-6.13.1 {
674 cksort {
675 SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
676 }
677 } {100 1 10201 99 2 10000 98 3 9801 sort}
678 do_test where-6.14 {
679 cksort {
680 SELECT * FROM t3 ORDER BY b LIMIT 3
681 }
682 } {100 1 10201 99 2 10000 98 3 9801 nosort}
683 do_test where-6.15 {
684 cksort {
685 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
686 }
687 } {1 0 2 1 3 1 nosort}
688 do_test where-6.16 {
689 cksort {
690 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
691 }
692 } {1 0 2 1 3 1 sort}
693 do_test where-6.19 {
694 cksort {
695 SELECT y FROM t1 ORDER BY w LIMIT 3;
696 }
697 } {4 9 16 nosort}
698 do_test where-6.20 {
699 cksort {
700 SELECT y FROM t1 ORDER BY rowid LIMIT 3;
701 }
702 } {4 9 16 nosort}
703 do_test where-6.21 {
704 cksort {
705 SELECT y FROM t1 ORDER BY rowid, y LIMIT 3;
706 }
707 } {4 9 16 nosort}
708 do_test where-6.22 {
709 cksort {
710 SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3;
711 }
712 } {4 9 16 nosort}
713 do_test where-6.23 {
714 cksort {
715 SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3;
716 }
717 } {9 16 25 nosort}
718 do_test where-6.24 {
719 cksort {
720 SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3;
721 }
722 } {9 16 25 nosort}
723 do_test where-6.25 {
724 cksort {
725 SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid;
726 }
727 } {9 16 nosort}
728 do_test where-6.26 {
729 cksort {
730 SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid;
731 }
732 } {4 9 16 25 nosort}
733 do_test where-6.27 {
734 cksort {
735 SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y;
736 }
737 } {4 9 16 25 nosort}
740 # Tests for reverse-order sorting.
741 #
742 do_test where-7.1 {
743 cksort {
744 SELECT w FROM t1 WHERE x=3 ORDER BY y;
745 }
746 } {8 9 10 11 12 13 14 15 nosort}
747 do_test where-7.2 {
748 cksort {
749 SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
750 }
751 } {15 14 13 12 11 10 9 8 nosort}
752 do_test where-7.3 {
753 cksort {
754 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
755 }
756 } {10 11 12 nosort}
757 do_test where-7.4 {
758 cksort {
759 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
760 }
761 } {15 14 13 nosort}
762 do_test where-7.5 {
763 cksort {
764 SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
765 }
766 } {15 14 13 12 11 nosort}
767 do_test where-7.6 {
768 cksort {
769 SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
770 }
771 } {15 14 13 12 11 10 nosort}
772 do_test where-7.7 {
773 cksort {
774 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
775 }
776 } {12 11 10 nosort}
777 do_test where-7.8 {
778 cksort {
779 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
780 }
781 } {13 12 11 10 nosort}
782 do_test where-7.9 {
783 cksort {
784 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
785 }
786 } {13 12 11 nosort}
787 do_test where-7.10 {
788 cksort {
789 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
790 }
791 } {12 11 10 nosort}
792 do_test where-7.11 {
793 cksort {
794 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
795 }
796 } {10 11 12 nosort}
797 do_test where-7.12 {
798 cksort {
799 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
800 }
801 } {10 11 12 13 nosort}
802 do_test where-7.13 {
803 cksort {
804 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
805 }
806 } {11 12 13 nosort}
807 do_test where-7.14 {
808 cksort {
809 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
810 }
811 } {10 11 12 nosort}
812 do_test where-7.15 {
813 cksort {
814 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
815 }
816 } {nosort}
817 do_test where-7.16 {
818 cksort {
819 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
820 }
821 } {8 nosort}
822 do_test where-7.17 {
823 cksort {
824 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
825 }
826 } {nosort}
827 do_test where-7.18 {
828 cksort {
829 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
830 }
831 } {15 nosort}
832 do_test where-7.19 {
833 cksort {
834 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
835 }
836 } {nosort}
837 do_test where-7.20 {
838 cksort {
839 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
840 }
841 } {8 nosort}
842 do_test where-7.21 {
843 cksort {
844 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
845 }
846 } {nosort}
847 do_test where-7.22 {
848 cksort {
849 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
850 }
851 } {15 nosort}
852 do_test where-7.23 {
853 cksort {
854 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
855 }
856 } {nosort}
857 do_test where-7.24 {
858 cksort {
859 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
860 }
861 } {1 nosort}
862 do_test where-7.25 {
863 cksort {
864 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
865 }
866 } {nosort}
867 do_test where-7.26 {
868 cksort {
869 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
870 }
871 } {100 nosort}
872 do_test where-7.27 {
873 cksort {
874 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
875 }
876 } {nosort}
877 do_test where-7.28 {
878 cksort {
879 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
880 }
881 } {1 nosort}
882 do_test where-7.29 {
883 cksort {
884 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
885 }
886 } {nosort}
887 do_test where-7.30 {
888 cksort {
889 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
890 }
891 } {100 nosort}
892 do_test where-7.31 {
893 cksort {
894 SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3
895 }
896 } {10201 10000 9801 nosort}
897 do_test where-7.32 {
898 cksort {
899 SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC
900 }
901 } {16 9 4 nosort}
902 do_test where-7.33 {
903 cksort {
904 SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC
905 }
906 } {25 16 9 4 nosort}
907 do_test where-7.34 {
908 cksort {
909 SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC
910 }
911 } {16 9 nosort}
912 do_test where-7.35 {
913 cksort {
914 SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC
915 }
916 } {16 9 4 nosort}
918 do_test where-8.1 {
919 execsql {
920 CREATE TABLE t4 AS SELECT * FROM t1;
921 CREATE INDEX i4xy ON t4(x,y);
922 }
923 cksort {
924 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
925 }
926 } {30 29 28 nosort}
927 do_test where-8.2 {
928 execsql {
929 DELETE FROM t4;
930 }
931 cksort {
932 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
933 }
934 } {nosort}
936 # Make sure searches with an index work with an empty table.
937 #
938 do_test where-9.1 {
939 execsql {
940 CREATE TABLE t5(x PRIMARY KEY);
941 SELECT * FROM t5 WHERE x<10;
942 }
943 } {}
944 do_test where-9.2 {
945 execsql {
946 SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
947 }
948 } {}
949 do_test where-9.3 {
950 execsql {
951 SELECT * FROM t5 WHERE x=10;
952 }
953 } {}
955 do_test where-10.1 {
956 execsql {
957 SELECT 1 WHERE abs(random())<0
958 }
959 } {}
960 do_test where-10.2 {
961 proc tclvar_func {vname} {return [set ::$vname]}
962 db function tclvar tclvar_func
963 set ::v1 0
964 execsql {
965 SELECT count(*) FROM t1 WHERE tclvar('v1');
966 }
967 } {0}
968 do_test where-10.3 {
969 set ::v1 1
970 execsql {
971 SELECT count(*) FROM t1 WHERE tclvar('v1');
972 }
973 } {100}
974 do_test where-10.4 {
975 set ::v1 1
976 proc tclvar_func {vname} {
977 upvar #0 $vname v
978 set v [expr {!$v}]
979 return $v
980 }
981 execsql {
982 SELECT count(*) FROM t1 WHERE tclvar('v1');
983 }
984 } {50}
986 # Ticket #1376. The query below was causing a segfault.
987 # The problem was the age-old error of calling realloc() on an
988 # array while there are still pointers to individual elements of
989 # that array.
990 #
991 do_test where-11.1 {
992 execsql {
993 CREATE TABLE t99(Dte INT, X INT);
994 DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR
995 (Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR
996 (Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR
997 (Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR
998 (Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR
999 (Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR
1000 (Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR
1001 (Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR
1002 (Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR
1003 (Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR
1004 (Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR
1005 (Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR
1006 (Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR
1007 (Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR
1008 (Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR
1009 (Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR
1010 (Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR
1011 (Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR
1012 (Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR
1013 (Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR
1014 (Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR
1015 (Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611);
1016 }
1017 } {}
1019 # Ticket #2116: Make sure sorting by index works well with nn INTEGER PRIMARY
1020 # KEY.
1021 #
1022 do_test where-12.1 {
1023 execsql {
1024 CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
1025 INSERT INTO t6 VALUES(1,'one');
1026 INSERT INTO t6 VALUES(4,'four');
1027 CREATE INDEX t6i1 ON t6(b);
1028 }
1029 cksort {
1030 SELECT * FROM t6 ORDER BY b;
1031 }
1032 } {4 four 1 one nosort}
1033 do_test where-12.2 {
1034 cksort {
1035 SELECT * FROM t6 ORDER BY b, a;
1036 }
1037 } {4 four 1 one nosort}
1038 do_test where-12.3 {
1039 cksort {
1040 SELECT * FROM t6 ORDER BY a;
1041 }
1042 } {1 one 4 four nosort}
1043 do_test where-12.4 {
1044 cksort {
1045 SELECT * FROM t6 ORDER BY a, b;
1046 }
1047 } {1 one 4 four nosort}
1048 do_test where-12.5 {
1049 cksort {
1050 SELECT * FROM t6 ORDER BY b DESC;
1051 }
1052 } {1 one 4 four nosort}
1053 do_test where-12.6 {
1054 cksort {
1055 SELECT * FROM t6 ORDER BY b DESC, a DESC;
1056 }
1057 } {1 one 4 four nosort}
1058 do_test where-12.7 {
1059 cksort {
1060 SELECT * FROM t6 ORDER BY b DESC, a ASC;
1061 }
1062 } {1 one 4 four sort}
1063 do_test where-12.8 {
1064 cksort {
1065 SELECT * FROM t6 ORDER BY b ASC, a DESC;
1066 }
1067 } {4 four 1 one sort}
1068 do_test where-12.9 {
1069 cksort {
1070 SELECT * FROM t6 ORDER BY a DESC;
1071 }
1072 } {4 four 1 one nosort}
1073 do_test where-12.10 {
1074 cksort {
1075 SELECT * FROM t6 ORDER BY a DESC, b DESC;
1076 }
1077 } {4 four 1 one nosort}
1078 do_test where-12.11 {
1079 cksort {
1080 SELECT * FROM t6 ORDER BY a DESC, b ASC;
1081 }
1082 } {4 four 1 one nosort}
1083 do_test where-12.12 {
1084 cksort {
1085 SELECT * FROM t6 ORDER BY a ASC, b DESC;
1086 }
1087 } {1 one 4 four nosort}
1088 do_test where-13.1 {
1089 execsql {
1090 CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT);
1091 INSERT INTO t7 VALUES(1,'one');
1092 INSERT INTO t7 VALUES(4,'four');
1093 CREATE INDEX t7i1 ON t7(b);
1094 }
1095 cksort {
1096 SELECT * FROM t7 ORDER BY b;
1097 }
1098 } {4 four 1 one nosort}
1099 do_test where-13.2 {
1100 cksort {
1101 SELECT * FROM t7 ORDER BY b, a;
1102 }
1103 } {4 four 1 one nosort}
1104 do_test where-13.3 {
1105 cksort {
1106 SELECT * FROM t7 ORDER BY a;
1107 }
1108 } {1 one 4 four nosort}
1109 do_test where-13.4 {
1110 cksort {
1111 SELECT * FROM t7 ORDER BY a, b;
1112 }
1113 } {1 one 4 four nosort}
1114 do_test where-13.5 {
1115 cksort {
1116 SELECT * FROM t7 ORDER BY b DESC;
1117 }
1118 } {1 one 4 four nosort}
1119 do_test where-13.6 {
1120 cksort {
1121 SELECT * FROM t7 ORDER BY b DESC, a DESC;
1122 }
1123 } {1 one 4 four nosort}
1124 do_test where-13.7 {
1125 cksort {
1126 SELECT * FROM t7 ORDER BY b DESC, a ASC;
1127 }
1128 } {1 one 4 four sort}
1129 do_test where-13.8 {
1130 cksort {
1131 SELECT * FROM t7 ORDER BY b ASC, a DESC;
1132 }
1133 } {4 four 1 one sort}
1134 do_test where-13.9 {
1135 cksort {
1136 SELECT * FROM t7 ORDER BY a DESC;
1137 }
1138 } {4 four 1 one nosort}
1139 do_test where-13.10 {
1140 cksort {
1141 SELECT * FROM t7 ORDER BY a DESC, b DESC;
1142 }
1143 } {4 four 1 one nosort}
1144 do_test where-13.11 {
1145 cksort {
1146 SELECT * FROM t7 ORDER BY a DESC, b ASC;
1147 }
1148 } {4 four 1 one nosort}
1149 do_test where-13.12 {
1150 cksort {
1151 SELECT * FROM t7 ORDER BY a ASC, b DESC;
1152 }
1153 } {1 one 4 four nosort}
1155 # Ticket #2211.
1156 #
1157 # When optimizing out ORDER BY clauses, make sure that trailing terms
1158 # of the ORDER BY clause do not reference other tables in a join.
1159 #
1160 if {[permutation] != "no_optimization"} {
1161 do_test where-14.1 {
1162 execsql {
1163 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c CHAR(100));
1164 INSERT INTO t8(a,b) VALUES(1,'one');
1165 INSERT INTO t8(a,b) VALUES(4,'four');
1166 }
1167 cksort {
1168 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
1169 }
1170 } {1/4 1/1 4/4 4/1 nosort}
1171 do_test where-14.2 {
1172 cksort {
1173 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
1174 }
1175 } {1/1 1/4 4/1 4/4 nosort}
1176 do_test where-14.3 {
1177 cksort {
1178 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
1179 }
1180 } {1/4 1/1 4/4 4/1 nosort}
1181 do_test where-14.4 {
1182 cksort {
1183 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
1184 }
1185 } {1/4 1/1 4/4 4/1 nosort}
1186 do_test where-14.5 {
1187 # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
1188 cksort {
1189 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
1190 }
1191 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
1192 do_test where-14.6 {
1193 # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
1194 cksort {
1195 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
1196 }
1197 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
1198 do_test where-14.7 {
1199 cksort {
1200 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
1201 }
1202 } {4/1 4/4 1/1 1/4 sort}
1203 do_test where-14.7.1 {
1204 cksort {
1205 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
1206 }
1207 } {4/1 4/4 1/1 1/4 sort}
1208 do_test where-14.7.2 {
1209 cksort {
1210 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
1211 }
1212 } {4/4 4/1 1/4 1/1 nosort}
1213 do_test where-14.8 {
1214 cksort {
1215 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
1216 }
1217 } {4/4 4/1 1/4 1/1 sort}
1218 do_test where-14.9 {
1219 cksort {
1220 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b
1221 }
1222 } {4/4 4/1 1/4 1/1 sort}
1223 do_test where-14.10 {
1224 cksort {
1225 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC
1226 }
1227 } {4/1 4/4 1/1 1/4 sort}
1228 do_test where-14.11 {
1229 cksort {
1230 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b
1231 }
1232 } {4/1 4/4 1/1 1/4 sort}
1233 do_test where-14.12 {
1234 cksort {
1235 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC
1236 }
1237 } {4/4 4/1 1/4 1/1 sort}
1238 } ;# {permutation != "no_optimization"}
1240 # Ticket #2445.
1241 #
1242 # There was a crash that could occur when a where clause contains an
1243 # alias for an expression in the result set, and that expression retrieves
1244 # a column of the second or subsequent table in a join.
1245 #
1246 do_test where-15.1 {
1247 execsql {
1248 CREATE TEMP TABLE t1 (a, b, c, d, e);
1249 CREATE TEMP TABLE t2 (f);
1250 SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ;
1251 }
1252 } {}
1254 # Ticket #3408.
1255 #
1256 # The branch of code in where.c that generated rowid lookups was
1257 # incorrectly deallocating a constant register, meaning that if the
1258 # vdbe code ran more than once, the second time around the constant
1259 # value may have been clobbered by some other value.
1260 #
1261 do_test where-16.1 {
1262 execsql {
1263 CREATE TABLE a1(id INTEGER PRIMARY KEY, v);
1264 CREATE TABLE a2(id INTEGER PRIMARY KEY, v);
1265 INSERT INTO a1 VALUES(1, 'one');
1266 INSERT INTO a1 VALUES(2, 'two');
1267 INSERT INTO a2 VALUES(1, 'one');
1268 INSERT INTO a2 VALUES(2, 'two');
1269 }
1270 } {}
1271 do_test where-16.2 {
1272 execsql {
1273 SELECT * FROM a2 CROSS JOIN a1 WHERE a1.id=1 AND a1.v='one';
1274 }
1275 } {1 one 1 one 2 two 1 one}
1277 # The actual problem reported in #3408.
1278 do_test where-16.3 {
1279 execsql {
1280 CREATE TEMP TABLE foo(idx INTEGER);
1281 INSERT INTO foo VALUES(1);
1282 INSERT INTO foo VALUES(1);
1283 INSERT INTO foo VALUES(1);
1284 INSERT INTO foo VALUES(2);
1285 INSERT INTO foo VALUES(2);
1286 CREATE TEMP TABLE bar(stuff INTEGER);
1287 INSERT INTO bar VALUES(100);
1288 INSERT INTO bar VALUES(200);
1289 INSERT INTO bar VALUES(300);
1290 }
1291 } {}
1292 do_test where-16.4 {
1293 execsql {
1294 SELECT bar.RowID id FROM foo, bar WHERE foo.idx = bar.RowID AND id = 2;
1295 }
1296 } {2 2}
1298 integrity_check {where-99.0}
1300 #---------------------------------------------------------------------
1301 # These tests test that a bug surrounding the use of ForceInt has been
1302 # fixed in where.c.
1303 #
1304 do_test where-17.1 {
1305 execsql {
1306 CREATE TABLE tbooking (
1307 id INTEGER PRIMARY KEY,
1308 eventtype INTEGER NOT NULL
1309 );
1310 INSERT INTO tbooking VALUES(42, 3);
1311 INSERT INTO tbooking VALUES(43, 4);
1312 }
1313 } {}
1314 do_test where-17.2 {
1315 execsql {
1316 SELECT a.id
1317 FROM tbooking AS a
1318 WHERE a.eventtype=3;
1319 }
1320 } {42}
1321 do_test where-17.3 {
1322 execsql {
1323 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id)
1324 FROM tbooking AS a
1325 WHERE a.eventtype=3;
1326 }
1327 } {42 43}
1328 do_test where-17.4 {
1329 execsql {
1330 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id)
1331 FROM (SELECT 1.5 AS id) AS a
1332 }
1333 } {1.5 42}
1334 do_test where-17.5 {
1335 execsql {
1336 CREATE TABLE tother(a, b);
1337 INSERT INTO tother VALUES(1, 3.7);
1338 SELECT id, a FROM tbooking, tother WHERE id>a;
1339 }
1340 } {42 1 43 1}
1342 # Ticket [be84e357c035d068135f20bcfe82761bbf95006b] 2013-09-03
1343 # Segfault during query involving LEFT JOIN column in the ORDER BY clause.
1344 #
1345 do_execsql_test where-18.1 {
1346 CREATE TABLE t181(a);
1347 CREATE TABLE t182(b,c);
1348 INSERT INTO t181 VALUES(1);
1349 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
1350 } {1}
1351 do_execsql_test where-18.1rj {
1352 SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c IS NULL;
1353 } {1}
1354 do_execsql_test where-18.2 {
1355 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
1356 } {1}
1357 do_execsql_test where-18.3 {
1358 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
1359 } {1}
1360 do_execsql_test where-18.3rj {
1361 SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c;
1362 } {1}
1363 do_execsql_test where-18.4 {
1364 INSERT INTO t181 VALUES(1),(1),(1),(1);
1365 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
1366 } {1}
1367 do_execsql_test where-18.4rj {
1368 SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY +c;
1369 } {1}
1370 do_execsql_test where-18.5 {
1371 INSERT INTO t181 VALUES(2);
1372 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
1373 } {1 2}
1374 do_execsql_test where-18.6 {
1375 INSERT INTO t181 VALUES(2);
1376 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
1377 } {1 2}
1379 # Make sure the OR optimization works on a JOIN
1380 #
1381 do_execsql_test where-19.0 {
1382 CREATE TABLE t191(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d);
1383 CREATE INDEX t191a ON t1(a);
1384 CREATE INDEX t191b ON t1(b);
1385 CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT);
1387 EXPLAIN QUERY PLAN
1388 SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1;
1389 } {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/}
1391 # 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a]
1392 # Index on expressions leads to an incorrect answer for a LEFT JOIN
1393 #
1394 do_execsql_test where-20.0 {
1395 CREATE TABLE t201(x);
1396 CREATE TABLE t202(y, z);
1397 INSERT INTO t201 VALUES('key');
1398 INSERT INTO t202 VALUES('key', -1);
1399 CREATE INDEX t202i ON t202(y, ifnull(z, 0));
1400 SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0;
1401 } {0}
1403 do_execsql_test where-21.0 {
1404 CREATE TABLE t12(a, b, c);
1405 CREATE TABLE t13(x);
1406 CREATE INDEX t12ab ON t12(b, a);
1407 CREATE INDEX t12ac ON t12(c, a);
1409 INSERT INTO t12 VALUES(4, 0, 1);
1410 INSERT INTO t12 VALUES(4, 1, 0);
1411 INSERT INTO t12 VALUES(5, 0, 1);
1412 INSERT INTO t12 VALUES(5, 1, 0);
1414 INSERT INTO t13 VALUES(1), (2), (3), (4);
1415 }
1416 do_execsql_test where-21.1 {
1417 SELECT * FROM t12 WHERE
1418 a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10)
1419 AND (b=1 OR c=1);
1420 } {
1421 4 1 0
1422 4 0 1
1423 }
1425 # 2018-11-05: ticket [https://www.sqlite.org/src/tktview/65eb38f6e46de8c75e188a]
1426 # Incorrect result in LEFT JOIN when STAT4 is enabled.
1427 #
1428 sqlite3 db :memory:
1429 do_execsql_test where-22.1 {
1430 CREATE TABLE t1(a INT);
1431 CREATE INDEX t1a ON t1(a);
1432 INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL);
1433 CREATE TABLE t2(dummy INT);
1434 SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL;
1435 } {5}
1437 # 20190-02-22: A bug introduced by checkin
1438 # https://www.sqlite.org/src/info/fa792714ae62fa98.
1439 #
1440 do_execsql_test where-23.0 {
1441 DROP TABLE IF EXISTS t1;
1442 DROP TABLE IF EXISTS t2;
1443 CREATE TABLE t1(a INTEGER PRIMARY KEY);
1444 INSERT INTO t1(a) VALUES(1),(2),(3);
1445 CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT);
1446 INSERT INTO t2(y) VALUES(2),(3);
1447 SELECT * FROM t1, t2 WHERE a=y AND y=3;
1448 } {3 2 3}
1450 #-------------------------------------------------------------------------
1451 #
1452 reset_db
1453 do_execsql_test where-24.0 {
1454 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
1455 INSERT INTO t1 VALUES(1, 'one');
1456 INSERT INTO t1 VALUES(2, 'two');
1457 INSERT INTO t1 VALUES(3, 'three');
1458 INSERT INTO t1 VALUES(4, 'four');
1459 }
1461 foreach {tn sql res} {
1462 1 "SELECT b FROM t1" {one two three four}
1463 2 "SELECT b FROM t1 WHERE a<4" {one two three}
1464 3 "SELECT b FROM t1 WHERE a>1" {two three four}
1465 4 "SELECT b FROM t1 WHERE a>1 AND a<4" {two three}
1467 5 "SELECT b FROM t1 WHERE a>? AND a<4" {}
1468 6 "SELECT b FROM t1 WHERE a>1 AND a<?" {}
1469 7 "SELECT b FROM t1 WHERE a>? AND a<?" {}
1471 7 "SELECT b FROM t1 WHERE a>=? AND a<=4" {}
1472 8 "SELECT b FROM t1 WHERE a>=1 AND a<=?" {}
1473 9 "SELECT b FROM t1 WHERE a>=? AND a<=?" {}
1474 } {
1475 set rev [list]
1476 foreach r $res { set rev [concat $r $rev] }
1478 do_execsql_test where-24.$tn.1 "$sql" $res
1479 do_execsql_test where-24.$tn.2 "$sql ORDER BY rowid" $res
1480 do_execsql_test where-24.$tn.3 "$sql ORDER BY rowid DESC" $rev
1482 do_execsql_test where-24-$tn.4 "
1483 BEGIN;
1484 DELETE FROM t1;
1485 $sql;
1486 $sql ORDER BY rowid;
1487 $sql ORDER BY rowid DESC;
1488 ROLLBACK;
1489 "
1490 }
1492 #-------------------------------------------------------------------------
1493 #
1494 reset_db
1495 do_execsql_test where-25.0 {
1496 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
1497 CREATE UNIQUE INDEX i1 ON t1(c);
1498 INSERT INTO t1 VALUES(1, 'one', 'i');
1499 INSERT INTO t1 VALUES(2, 'two', 'ii');
1501 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
1502 CREATE UNIQUE INDEX i2 ON t2(c);
1503 INSERT INTO t2 VALUES(1, 'one', 'i');
1504 INSERT INTO t2 VALUES(2, 'two', 'ii');
1505 INSERT INTO t2 VALUES(3, 'three', 'iii');
1507 PRAGMA writable_schema = 1;
1508 UPDATE sqlite_schema SET rootpage = (
1509 SELECT rootpage FROM sqlite_schema WHERE name = 'i2'
1510 ) WHERE name = 'i1';
1511 }
1512 db close
1513 sqlite3 db test.db
1514 do_catchsql_test where-25.1 {
1515 DELETE FROM t1 WHERE c='iii'
1516 } {1 {database disk image is malformed}}
1517 do_catchsql_test where-25.2 {
1518 INSERT INTO t1 VALUES(4, 'four', 'iii')
1519 ON CONFLICT(c) DO UPDATE SET b=NULL
1520 } {1 {database disk image is malformed}}
1522 reset_db
1523 do_execsql_test where-25.3 {
1524 CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
1525 CREATE UNIQUE INDEX i1 ON t1(c);
1526 INSERT INTO t1 VALUES(1, 'one', 'i');
1527 INSERT INTO t1 VALUES(2, 'two', 'ii');
1529 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
1530 CREATE UNIQUE INDEX i2 ON t2(c);
1531 INSERT INTO t2 VALUES(1, 'one', 'i');
1532 INSERT INTO t2 VALUES(2, 'two', 'ii');
1533 INSERT INTO t2 VALUES(3, 'three', 'iii');
1535 PRAGMA writable_schema = 1;
1536 UPDATE sqlite_schema SET rootpage = (
1537 SELECT rootpage FROM sqlite_schema WHERE name = 'i2'
1538 ) WHERE name = 'i1';
1539 }
1540 db close
1541 sqlite3 db test.db
1542 do_catchsql_test where-25.4 {
1543 SELECT * FROM t1 WHERE c='iii'
1544 } {0 {}}
1545 do_catchsql_test where-25.5 {
1546 INSERT INTO t1 VALUES(4, 'four', 'iii')
1547 ON CONFLICT(c) DO UPDATE SET b=NULL
1548 } {1 {corrupt database}}
1550 # 2019-08-21 Ticket https://www.sqlite.org/src/info/d9f584e936c7a8d0
1551 #
1552 db close
1553 sqlite3 db :memory:
1554 do_execsql_test where-26.1 {
1555 CREATE TABLE t0(c0 INTEGER PRIMARY KEY, c1 TEXT);
1556 INSERT INTO t0(c0, c1) VALUES (1, 'a');
1557 CREATE TABLE t1(c0 INT PRIMARY KEY, c1 TEXT);
1558 INSERT INTO t1(c0, c1) VALUES (1, 'a');
1559 SELECT * FROM t0 WHERE '-1' BETWEEN 0 AND t0.c0;
1560 } {1 a}
1561 do_execsql_test where-26.2 {
1562 SELECT * FROM t1 WHERE '-1' BETWEEN 0 AND t1.c0;
1563 } {1 a}
1564 do_execsql_test where-26.3 {
1565 SELECT * FROM t0 WHERE '-1'>=0 AND '-1'<=t0.c0;
1566 } {1 a}
1567 do_execsql_test where-26.4 {
1568 SELECT * FROM t1 WHERE '-1'>=0 AND '-1'<=t1.c0;
1569 } {1 a}
1570 do_execsql_test where-26.5 {
1571 SELECT '-1' BETWEEN 0 AND t0.c0 FROM t0;
1572 } {1}
1573 do_execsql_test where-26.6 {
1574 SELECT '-1' BETWEEN 0 AND t1.c0 FROM t1;
1575 } {1}
1576 do_execsql_test where-26.7 {
1577 SELECT '-1'>=0 AND '-1'<=t0.c0 FROM t0;
1578 } {1}
1579 do_execsql_test where-26.8 {
1580 SELECT '-1'>=0 AND '-1'<=t1.c0 FROM t1;
1581 } {1}
1583 # 2021-07-19 https://sqlite.org/forum/forumpost/2bdb86a068
1584 # Lose of precision when doing comparisons between integer and
1585 # floating point values that are near 9223372036854775807 in the
1586 # OP_SeekGE opcode (and similar).
1587 #
1588 # Valgrind documentation acknowledges that under valgrind, FP calculations
1589 # may not be as accurate as on x86/amd64 hardware. This seems to be causing
1590 # these tests to fail.
1591 #
1592 # https://valgrind.org/docs/manual/manual-core.html#manual-core.limits
1593 #
1594 if {[permutation]!="valgrind"} {
1595 reset_db
1596 do_execsql_test where-27.1 {
1597 CREATE TABLE t1(a INTEGER PRIMARY KEY);
1598 INSERT INTO t1(a) VALUES(9223372036854775807);
1599 SELECT 1 FROM t1 WHERE a>=(9223372036854775807+1);
1600 } {}
1601 do_execsql_test where-27.2 {
1602 SELECT a>=9223372036854775807+1 FROM t1;
1603 } {0}
1604 }
1606 # 2022-05-10 dbsqlfuzz 4c5e3e89bc251d28378be88233f531b84ec66901
1607 #
1608 reset_db
1609 do_execsql_test where-28.1 {
1610 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
1611 CREATE INDEX t1b ON t1(b,b,b,b,b,b,b,b,b,b,b,b,b);
1612 INSERT INTO t1(a,b) VALUES(1,1),(15,2),(19,5);
1613 UPDATE t1 SET b=999 WHERE a IN (SELECT 15) AND b IN (1,2);
1614 SELECT * FROM t1;
1615 } {
1616 1 1
1617 15 999
1618 19 5
1619 }
1621 # 2022-12-07 Yong Heng [https://sqlite.org/forum/forumpost/dfe8084751]
1622 #
1623 ifcapable vtab {
1624 do_execsql_test where-29.1 {
1625 SELECT DISTINCT 'xyz' FROM pragma_cache_size
1626 WHERE rowid OR abs(0)
1627 ORDER BY
1628 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1629 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1630 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1631 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1632 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1633 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1634 1, 1, 1, 1;
1635 } {xyz}
1636 }
1638 # 2023-01-30
1639 # Tests case for the query planner performance issue reported by
1640 # https://sqlite.org/forum/forumpost/1d571c0296
1641 #
1642 # The fix was to adjust the cost of computing an automatic index for
1643 # ephemeral tables, to help ensure that they are generated if they are
1644 # needed. The test case below only looks at the query plan. But 12x
1645 # improved performance has been verified by populating the "raw" table
1646 # with 100K rows of random data and running actual speed tests.
1647 #
1648 do_test where-30.1 {
1649 unset -nocomplain res
1650 set res {}
1651 db eval {CREATE TABLE raw(country,date,total,delta, UNIQUE(country,date));}
1652 db eval {
1653 EXPLAIN QUERY PLAN
1654 WITH
1655 -- Find the country and min/max date
1656 init(country, date, fin) AS (SELECT country, min(date), max(date)
1657 FROM raw WHERE total > 0 GROUP BY country),
1659 -- Generate the date stream for each country
1660 src(country, date) AS (SELECT raw.country, raw.date
1661 FROM raw JOIN init i on raw.country = i.country AND raw.date > i.date
1662 ORDER BY raw.country, raw.date),
1664 -- Generate the x & y for each entry in the country/date stream
1665 vals(country, date, x, y) AS (SELECT src.country, src.date,
1666 julianday(raw.date) - julianday(src.date), log(delta+1)
1667 FROM src JOIN raw on raw.country = src.country
1668 AND raw.date > date(src.date,'-7 days')
1669 AND raw.date <= src.date AND delta >= 0),
1671 -- Accumulate the data we need
1672 sums(country, date, x2, x, n, xy, y) AS (SELECT country, date,
1673 sum(x*x*1.0), sum(x*1.0), sum(1.0), sum(x*y*1.0), sum(y*1.0)
1674 FROM vals GROUP BY 1, 2),
1676 -- use these to calculate to divisor for the inverse matrix
1677 mult(country, date, m) AS (SELECT country, date, 1.0/(x2 * n - x * x)
1678 FROM sums),
1680 -- Build the inverse matrix
1681 inv(country, date, a,b,c,d) AS (SELECT mult.country, mult.date, n * m,
1682 -x * m, -x * m, x2 * m
1683 FROM mult JOIN sums on sums.country=mult.country
1684 AND mult.date=sums.date),
1686 -- Calculate the coefficients for the least squares fit
1687 fit(country, date, a, b) AS (SELECT inv.country, inv.date,
1688 a * xy + b * y, c * xy + d * y
1689 FROM inv
1690 JOIN mult on mult.country = inv.country AND mult.date = inv.date
1691 JOIN sums on sums.country = mult.country AND sums.date = mult.date
1692 )
1693 SELECT *, nFin/nPrev - 1 AS growth, log(2)/log(nFin/nPrev) AS doubling
1694 FROM (SELECT f.*, exp(b) - 1 AS nFin, exp(a* (-1) + b) - 1 AS nPrev
1695 FROM fit f JOIN init i on i.country = f.country
1696 AND f.date <= date(i.fin,'-3 days'))
1697 WHERE nPrev > 0 AND nFin > 0;
1698 } {
1699 if {$parent!=0} continue
1700 if {![string match SCAN* $detail]} continue
1701 lappend res SCAN
1702 }
1703 set res
1704 } {SCAN}
1705 # ^^^^^^-- there should only be one top-level table scan in the query plan.
1707 finish_test