wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 #
14 # $Id: where.test,v 1.50 2008/11/03 09:06:06 danielk1977 Exp $
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18  
19 # Build some test data
20 #
21 do_test where-1.0 {
22 execsql {
23 CREATE TABLE t1(w int, x int, y int);
24 CREATE TABLE t2(p int, q int, r int, s int);
25 }
26 for {set i 1} {$i<=100} {incr i} {
27 set w $i
28 set x [expr {int(log($i)/log(2))}]
29 set y [expr {$i*$i + 2*$i + 1}]
30 execsql "INSERT INTO t1 VALUES($w,$x,$y)"
31 }
32  
33 ifcapable subquery {
34 execsql {
35 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
36 }
37 } else {
38 set maxy [execsql {select max(y) from t1}]
39 execsql "
40 INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
41 "
42 }
43  
44 execsql {
45 CREATE INDEX i1w ON t1(w);
46 CREATE INDEX i1xy ON t1(x,y);
47 CREATE INDEX i2p ON t2(p);
48 CREATE INDEX i2r ON t2(r);
49 CREATE INDEX i2qs ON t2(q, s);
50 }
51 } {}
52  
53 # Do an SQL statement. Append the search count to the end of the result.
54 #
55 proc count sql {
56 set ::sqlite_search_count 0
57 return [concat [execsql $sql] $::sqlite_search_count]
58 }
59  
60 # Verify that queries use an index. We are using the special variable
61 # "sqlite_search_count" which tallys the number of executions of MoveTo
62 # and Next operators in the VDBE. By verifing that the search count is
63 # small we can be assured that indices are being used properly.
64 #
65 do_test where-1.1.1 {
66 count {SELECT x, y, w FROM t1 WHERE w=10}
67 } {3 121 10 3}
68 do_test where-1.1.2 {
69 set sqlite_query_plan
70 } {t1 i1w}
71 do_test where-1.1.3 {
72 db status step
73 } {0}
74 do_test where-1.1.4 {
75 db eval {SELECT x, y, w FROM t1 WHERE +w=10}
76 } {3 121 10}
77 do_test where-1.1.5 {
78 db status step
79 } {99}
80 do_test where-1.1.6 {
81 set sqlite_query_plan
82 } {t1 {}}
83 do_test where-1.1.7 {
84 count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
85 } {3 121 10 3}
86 do_test where-1.1.8 {
87 set sqlite_query_plan
88 } {t1 i1w}
89 do_test where-1.1.9 {
90 db status step
91 } {0}
92 do_test where-1.2.1 {
93 count {SELECT x, y, w FROM t1 WHERE w=11}
94 } {3 144 11 3}
95 do_test where-1.2.2 {
96 count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
97 } {3 144 11 3}
98 do_test where-1.3.1 {
99 count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
100 } {3 144 11 3}
101 do_test where-1.3.2 {
102 count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
103 } {3 144 11 3}
104 do_test where-1.4.1 {
105 count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
106 } {11 3 144 3}
107 do_test where-1.4.2 {
108 set sqlite_query_plan
109 } {t1 i1w}
110 do_test where-1.4.3 {
111 count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
112 } {11 3 144 3}
113 do_test where-1.4.4 {
114 set sqlite_query_plan
115 } {t1 i1w}
116 do_test where-1.5 {
117 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
118 } {3 144 3}
119 do_test where-1.5.2 {
120 set sqlite_query_plan
121 } {t1 i1w}
122 do_test where-1.6 {
123 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
124 } {3 144 3}
125 do_test where-1.7 {
126 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
127 } {3 144 3}
128 do_test where-1.8 {
129 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
130 } {3 144 3}
131 do_test where-1.8.2 {
132 set sqlite_query_plan
133 } {t1 i1xy}
134 do_test where-1.8.3 {
135 count {SELECT x, y FROM t1 WHERE y=144 AND x=3}
136 set sqlite_query_plan
137 } {{} i1xy}
138 do_test where-1.9 {
139 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
140 } {3 144 3}
141 do_test where-1.10 {
142 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
143 } {3 121 3}
144 do_test where-1.11 {
145 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
146 } {3 100 3}
147  
148 # New for SQLite version 2.1: Verify that that inequality constraints
149 # are used correctly.
150 #
151 do_test where-1.12 {
152 count {SELECT w FROM t1 WHERE x=3 AND y<100}
153 } {8 3}
154 do_test where-1.13 {
155 count {SELECT w FROM t1 WHERE x=3 AND 100>y}
156 } {8 3}
157 do_test where-1.14 {
158 count {SELECT w FROM t1 WHERE 3=x AND y<100}
159 } {8 3}
160 do_test where-1.15 {
161 count {SELECT w FROM t1 WHERE 3=x AND 100>y}
162 } {8 3}
163 do_test where-1.16 {
164 count {SELECT w FROM t1 WHERE x=3 AND y<=100}
165 } {8 9 5}
166 do_test where-1.17 {
167 count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
168 } {8 9 5}
169 do_test where-1.18 {
170 count {SELECT w FROM t1 WHERE x=3 AND y>225}
171 } {15 3}
172 do_test where-1.19 {
173 count {SELECT w FROM t1 WHERE x=3 AND 225<y}
174 } {15 3}
175 do_test where-1.20 {
176 count {SELECT w FROM t1 WHERE x=3 AND y>=225}
177 } {14 15 5}
178 do_test where-1.21 {
179 count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
180 } {14 15 5}
181 do_test where-1.22 {
182 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
183 } {11 12 5}
184 do_test where-1.23 {
185 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
186 } {10 11 12 13 9}
187 do_test where-1.24 {
188 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
189 } {11 12 5}
190 do_test where-1.25 {
191 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
192 } {10 11 12 13 9}
193  
194 # Need to work on optimizing the BETWEEN operator.
195 #
196 # do_test where-1.26 {
197 # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
198 # } {10 11 12 13 9}
199  
200 do_test where-1.27 {
201 count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
202 } {10 10}
203  
204 do_test where-1.28 {
205 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
206 } {10 99}
207 do_test where-1.29 {
208 count {SELECT w FROM t1 WHERE y==121}
209 } {10 99}
210  
211  
212 do_test where-1.30 {
213 count {SELECT w FROM t1 WHERE w>97}
214 } {98 99 100 3}
215 do_test where-1.31 {
216 count {SELECT w FROM t1 WHERE w>=97}
217 } {97 98 99 100 4}
218 do_test where-1.33 {
219 count {SELECT w FROM t1 WHERE w==97}
220 } {97 2}
221 do_test where-1.33.1 {
222 count {SELECT w FROM t1 WHERE w<=97 AND w==97}
223 } {97 2}
224 do_test where-1.33.2 {
225 count {SELECT w FROM t1 WHERE w<98 AND w==97}
226 } {97 2}
227 do_test where-1.33.3 {
228 count {SELECT w FROM t1 WHERE w>=97 AND w==97}
229 } {97 2}
230 do_test where-1.33.4 {
231 count {SELECT w FROM t1 WHERE w>96 AND w==97}
232 } {97 2}
233 do_test where-1.33.5 {
234 count {SELECT w FROM t1 WHERE w==97 AND w==97}
235 } {97 2}
236 do_test where-1.34 {
237 count {SELECT w FROM t1 WHERE w+1==98}
238 } {97 99}
239 do_test where-1.35 {
240 count {SELECT w FROM t1 WHERE w<3}
241 } {1 2 2}
242 do_test where-1.36 {
243 count {SELECT w FROM t1 WHERE w<=3}
244 } {1 2 3 3}
245 do_test where-1.37 {
246 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
247 } {1 2 3 99}
248  
249 do_test where-1.38 {
250 count {SELECT (w) FROM t1 WHERE (w)>(97)}
251 } {98 99 100 3}
252 do_test where-1.39 {
253 count {SELECT (w) FROM t1 WHERE (w)>=(97)}
254 } {97 98 99 100 4}
255 do_test where-1.40 {
256 count {SELECT (w) FROM t1 WHERE (w)==(97)}
257 } {97 2}
258 do_test where-1.41 {
259 count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
260 } {97 99}
261  
262  
263 # Do the same kind of thing except use a join as the data source.
264 #
265 do_test where-2.1 {
266 count {
267 SELECT w, p FROM t2, t1
268 WHERE x=q AND y=s AND r=8977
269 }
270 } {34 67 6}
271 do_test where-2.2 {
272 count {
273 SELECT w, p FROM t2, t1
274 WHERE x=q AND s=y AND r=8977
275 }
276 } {34 67 6}
277 do_test where-2.3 {
278 count {
279 SELECT w, p FROM t2, t1
280 WHERE x=q AND s=y AND r=8977 AND w>10
281 }
282 } {34 67 6}
283 do_test where-2.4 {
284 count {
285 SELECT w, p FROM t2, t1
286 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
287 }
288 } {34 67 6}
289 do_test where-2.5 {
290 count {
291 SELECT w, p FROM t2, t1
292 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
293 }
294 } {34 67 6}
295 do_test where-2.6 {
296 count {
297 SELECT w, p FROM t2, t1
298 WHERE x=q AND p=77 AND s=y AND w>5
299 }
300 } {24 77 6}
301 do_test where-2.7 {
302 count {
303 SELECT w, p FROM t1, t2
304 WHERE x=q AND p>77 AND s=y AND w=5
305 }
306 } {5 96 6}
307  
308 # Lets do a 3-way join.
309 #
310 do_test where-3.1 {
311 count {
312 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
313 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
314 }
315 } {11 90 11 8}
316 do_test where-3.2 {
317 count {
318 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
319 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
320 }
321 } {12 89 12 8}
322 do_test where-3.3 {
323 count {
324 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
325 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
326 }
327 } {15 86 86 8}
328  
329 # Test to see that the special case of a constant WHERE clause is
330 # handled.
331 #
332 do_test where-4.1 {
333 count {
334 SELECT * FROM t1 WHERE 0
335 }
336 } {0}
337 do_test where-4.2 {
338 count {
339 SELECT * FROM t1 WHERE 1 LIMIT 1
340 }
341 } {1 0 4 0}
342 do_test where-4.3 {
343 execsql {
344 SELECT 99 WHERE 0
345 }
346 } {}
347 do_test where-4.4 {
348 execsql {
349 SELECT 99 WHERE 1
350 }
351 } {99}
352 do_test where-4.5 {
353 execsql {
354 SELECT 99 WHERE 0.1
355 }
356 } {99}
357 do_test where-4.6 {
358 execsql {
359 SELECT 99 WHERE 0.0
360 }
361 } {}
362 do_test where-4.7 {
363 execsql {
364 SELECT count(*) FROM t1 WHERE t1.w
365 }
366 } {100}
367  
368 # Verify that IN operators in a WHERE clause are handled correctly.
369 # Omit these tests if the build is not capable of sub-queries.
370 #
371 ifcapable subquery {
372 do_test where-5.1 {
373 count {
374 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
375 }
376 } {1 0 4 2 1 9 3 1 16 4}
377 do_test where-5.2 {
378 count {
379 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
380 }
381 } {1 0 4 2 1 9 3 1 16 102}
382 do_test where-5.3 {
383 count {
384 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
385 }
386 } {1 0 4 2 1 9 3 1 16 14}
387 do_test where-5.4 {
388 count {
389 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
390 }
391 } {1 0 4 2 1 9 3 1 16 102}
392 do_test where-5.5 {
393 count {
394 SELECT * FROM t1 WHERE rowid IN
395 (select rowid from t1 where rowid IN (-1,2,4))
396 ORDER BY 1;
397 }
398 } {2 1 9 4 2 25 3}
399 do_test where-5.6 {
400 count {
401 SELECT * FROM t1 WHERE rowid+0 IN
402 (select rowid from t1 where rowid IN (-1,2,4))
403 ORDER BY 1;
404 }
405 } {2 1 9 4 2 25 103}
406 do_test where-5.7 {
407 count {
408 SELECT * FROM t1 WHERE w IN
409 (select rowid from t1 where rowid IN (-1,2,4))
410 ORDER BY 1;
411 }
412 } {2 1 9 4 2 25 9}
413 do_test where-5.8 {
414 count {
415 SELECT * FROM t1 WHERE w+0 IN
416 (select rowid from t1 where rowid IN (-1,2,4))
417 ORDER BY 1;
418 }
419 } {2 1 9 4 2 25 103}
420 do_test where-5.9 {
421 count {
422 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
423 }
424 } {2 1 9 3 1 16 7}
425 do_test where-5.10 {
426 count {
427 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
428 }
429 } {2 1 9 3 1 16 199}
430 do_test where-5.11 {
431 count {
432 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
433 }
434 } {79 6 6400 89 6 8100 199}
435 do_test where-5.12 {
436 count {
437 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
438 }
439 } {79 6 6400 89 6 8100 7}
440 do_test where-5.13 {
441 count {
442 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
443 }
444 } {2 1 9 3 1 16 7}
445 do_test where-5.14 {
446 count {
447 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
448 }
449 } {2 1 9 8}
450 do_test where-5.15 {
451 count {
452 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
453 }
454 } {2 1 9 3 1 16 11}
455 }
456  
457 # This procedure executes the SQL. Then it checks to see if the OP_Sort
458 # opcode was executed. If an OP_Sort did occur, then "sort" is appended
459 # to the result. If no OP_Sort happened, then "nosort" is appended.
460 #
461 # This procedure is used to check to make sure sorting is or is not
462 # occurring as expected.
463 #
464 proc cksort {sql} {
465 set data [execsql $sql]
466 if {[db status sort]} {set x sort} {set x nosort}
467 lappend data $x
468 return $data
469 }
470 # Check out the logic that attempts to implement the ORDER BY clause
471 # using an index rather than by sorting.
472 #
473 do_test where-6.1 {
474 execsql {
475 CREATE TABLE t3(a,b,c);
476 CREATE INDEX t3a ON t3(a);
477 CREATE INDEX t3bc ON t3(b,c);
478 CREATE INDEX t3acb ON t3(a,c,b);
479 INSERT INTO t3 SELECT w, 101-w, y FROM t1;
480 SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
481 }
482 } {100 5050 5050 348550}
483 do_test where-6.2 {
484 cksort {
485 SELECT * FROM t3 ORDER BY a LIMIT 3
486 }
487 } {1 100 4 2 99 9 3 98 16 nosort}
488 do_test where-6.3 {
489 cksort {
490 SELECT * FROM t3 ORDER BY a+1 LIMIT 3
491 }
492 } {1 100 4 2 99 9 3 98 16 sort}
493 do_test where-6.4 {
494 cksort {
495 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
496 }
497 } {1 100 4 2 99 9 3 98 16 nosort}
498 do_test where-6.5 {
499 cksort {
500 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
501 }
502 } {1 100 4 2 99 9 3 98 16 nosort}
503 do_test where-6.6 {
504 cksort {
505 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
506 }
507 } {1 100 4 2 99 9 3 98 16 nosort}
508 do_test where-6.7 {
509 cksort {
510 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
511 }
512 } {1 100 4 2 99 9 3 98 16 nosort}
513 ifcapable subquery {
514 do_test where-6.8 {
515 cksort {
516 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
517 }
518 } {1 100 4 2 99 9 3 98 16 sort}
519 }
520 do_test where-6.9.1 {
521 cksort {
522 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
523 }
524 } {1 100 4 nosort}
525 do_test where-6.9.1.1 {
526 cksort {
527 SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3
528 }
529 } {1 100 4 nosort}
530 do_test where-6.9.1.2 {
531 cksort {
532 SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3
533 }
534 } {1 100 4 nosort}
535 do_test where-6.9.2 {
536 cksort {
537 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
538 }
539 } {1 100 4 nosort}
540 do_test where-6.9.3 {
541 cksort {
542 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
543 }
544 } {1 100 4 nosort}
545 do_test where-6.9.4 {
546 cksort {
547 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
548 }
549 } {1 100 4 nosort}
550 do_test where-6.9.5 {
551 cksort {
552 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
553 }
554 } {1 100 4 nosort}
555 do_test where-6.9.6 {
556 cksort {
557 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
558 }
559 } {1 100 4 nosort}
560 do_test where-6.9.7 {
561 cksort {
562 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
563 }
564 } {1 100 4 sort}
565 do_test where-6.9.8 {
566 cksort {
567 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
568 }
569 } {1 100 4 nosort}
570 do_test where-6.9.9 {
571 cksort {
572 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
573 }
574 } {1 100 4 nosort}
575 do_test where-6.10 {
576 cksort {
577 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
578 }
579 } {1 100 4 nosort}
580 do_test where-6.11 {
581 cksort {
582 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
583 }
584 } {1 100 4 nosort}
585 do_test where-6.12 {
586 cksort {
587 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
588 }
589 } {1 100 4 nosort}
590 do_test where-6.13 {
591 cksort {
592 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
593 }
594 } {100 1 10201 99 2 10000 98 3 9801 nosort}
595 do_test where-6.13.1 {
596 cksort {
597 SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
598 }
599 } {100 1 10201 99 2 10000 98 3 9801 sort}
600 do_test where-6.14 {
601 cksort {
602 SELECT * FROM t3 ORDER BY b LIMIT 3
603 }
604 } {100 1 10201 99 2 10000 98 3 9801 nosort}
605 do_test where-6.15 {
606 cksort {
607 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
608 }
609 } {1 0 2 1 3 1 nosort}
610 do_test where-6.16 {
611 cksort {
612 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
613 }
614 } {1 0 2 1 3 1 sort}
615 do_test where-6.19 {
616 cksort {
617 SELECT y FROM t1 ORDER BY w LIMIT 3;
618 }
619 } {4 9 16 nosort}
620 do_test where-6.20 {
621 cksort {
622 SELECT y FROM t1 ORDER BY rowid LIMIT 3;
623 }
624 } {4 9 16 nosort}
625 do_test where-6.21 {
626 cksort {
627 SELECT y FROM t1 ORDER BY rowid, y LIMIT 3;
628 }
629 } {4 9 16 nosort}
630 do_test where-6.22 {
631 cksort {
632 SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3;
633 }
634 } {4 9 16 nosort}
635 do_test where-6.23 {
636 cksort {
637 SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3;
638 }
639 } {9 16 25 nosort}
640 do_test where-6.24 {
641 cksort {
642 SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3;
643 }
644 } {9 16 25 nosort}
645 do_test where-6.25 {
646 cksort {
647 SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid;
648 }
649 } {9 16 nosort}
650 do_test where-6.26 {
651 cksort {
652 SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid;
653 }
654 } {4 9 16 25 nosort}
655 do_test where-6.27 {
656 cksort {
657 SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y;
658 }
659 } {4 9 16 25 nosort}
660  
661  
662 # Tests for reverse-order sorting.
663 #
664 do_test where-7.1 {
665 cksort {
666 SELECT w FROM t1 WHERE x=3 ORDER BY y;
667 }
668 } {8 9 10 11 12 13 14 15 nosort}
669 do_test where-7.2 {
670 cksort {
671 SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
672 }
673 } {15 14 13 12 11 10 9 8 nosort}
674 do_test where-7.3 {
675 cksort {
676 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
677 }
678 } {10 11 12 nosort}
679 do_test where-7.4 {
680 cksort {
681 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
682 }
683 } {15 14 13 nosort}
684 do_test where-7.5 {
685 cksort {
686 SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
687 }
688 } {15 14 13 12 11 nosort}
689 do_test where-7.6 {
690 cksort {
691 SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
692 }
693 } {15 14 13 12 11 10 nosort}
694 do_test where-7.7 {
695 cksort {
696 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
697 }
698 } {12 11 10 nosort}
699 do_test where-7.8 {
700 cksort {
701 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
702 }
703 } {13 12 11 10 nosort}
704 do_test where-7.9 {
705 cksort {
706 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
707 }
708 } {13 12 11 nosort}
709 do_test where-7.10 {
710 cksort {
711 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
712 }
713 } {12 11 10 nosort}
714 do_test where-7.11 {
715 cksort {
716 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
717 }
718 } {10 11 12 nosort}
719 do_test where-7.12 {
720 cksort {
721 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
722 }
723 } {10 11 12 13 nosort}
724 do_test where-7.13 {
725 cksort {
726 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
727 }
728 } {11 12 13 nosort}
729 do_test where-7.14 {
730 cksort {
731 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
732 }
733 } {10 11 12 nosort}
734 do_test where-7.15 {
735 cksort {
736 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
737 }
738 } {nosort}
739 do_test where-7.16 {
740 cksort {
741 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
742 }
743 } {8 nosort}
744 do_test where-7.17 {
745 cksort {
746 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
747 }
748 } {nosort}
749 do_test where-7.18 {
750 cksort {
751 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
752 }
753 } {15 nosort}
754 do_test where-7.19 {
755 cksort {
756 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
757 }
758 } {nosort}
759 do_test where-7.20 {
760 cksort {
761 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
762 }
763 } {8 nosort}
764 do_test where-7.21 {
765 cksort {
766 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
767 }
768 } {nosort}
769 do_test where-7.22 {
770 cksort {
771 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
772 }
773 } {15 nosort}
774 do_test where-7.23 {
775 cksort {
776 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
777 }
778 } {nosort}
779 do_test where-7.24 {
780 cksort {
781 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
782 }
783 } {1 nosort}
784 do_test where-7.25 {
785 cksort {
786 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
787 }
788 } {nosort}
789 do_test where-7.26 {
790 cksort {
791 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
792 }
793 } {100 nosort}
794 do_test where-7.27 {
795 cksort {
796 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
797 }
798 } {nosort}
799 do_test where-7.28 {
800 cksort {
801 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
802 }
803 } {1 nosort}
804 do_test where-7.29 {
805 cksort {
806 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
807 }
808 } {nosort}
809 do_test where-7.30 {
810 cksort {
811 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
812 }
813 } {100 nosort}
814 do_test where-7.31 {
815 cksort {
816 SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3
817 }
818 } {10201 10000 9801 nosort}
819 do_test where-7.32 {
820 cksort {
821 SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC
822 }
823 } {16 9 4 nosort}
824 do_test where-7.33 {
825 cksort {
826 SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC
827 }
828 } {25 16 9 4 nosort}
829 do_test where-7.34 {
830 cksort {
831 SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC
832 }
833 } {16 9 nosort}
834 do_test where-7.35 {
835 cksort {
836 SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC
837 }
838 } {16 9 4 nosort}
839  
840 do_test where-8.1 {
841 execsql {
842 CREATE TABLE t4 AS SELECT * FROM t1;
843 CREATE INDEX i4xy ON t4(x,y);
844 }
845 cksort {
846 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
847 }
848 } {30 29 28 nosort}
849 do_test where-8.2 {
850 execsql {
851 DELETE FROM t4;
852 }
853 cksort {
854 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
855 }
856 } {nosort}
857  
858 # Make sure searches with an index work with an empty table.
859 #
860 do_test where-9.1 {
861 execsql {
862 CREATE TABLE t5(x PRIMARY KEY);
863 SELECT * FROM t5 WHERE x<10;
864 }
865 } {}
866 do_test where-9.2 {
867 execsql {
868 SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
869 }
870 } {}
871 do_test where-9.3 {
872 execsql {
873 SELECT * FROM t5 WHERE x=10;
874 }
875 } {}
876  
877 do_test where-10.1 {
878 execsql {
879 SELECT 1 WHERE abs(random())<0
880 }
881 } {}
882 do_test where-10.2 {
883 proc tclvar_func {vname} {return [set ::$vname]}
884 db function tclvar tclvar_func
885 set ::v1 0
886 execsql {
887 SELECT count(*) FROM t1 WHERE tclvar('v1');
888 }
889 } {0}
890 do_test where-10.3 {
891 set ::v1 1
892 execsql {
893 SELECT count(*) FROM t1 WHERE tclvar('v1');
894 }
895 } {100}
896 do_test where-10.4 {
897 set ::v1 1
898 proc tclvar_func {vname} {
899 upvar #0 $vname v
900 set v [expr {!$v}]
901 return $v
902 }
903 execsql {
904 SELECT count(*) FROM t1 WHERE tclvar('v1');
905 }
906 } {50}
907  
908 # Ticket #1376. The query below was causing a segfault.
909 # The problem was the age-old error of calling realloc() on an
910 # array while there are still pointers to individual elements of
911 # that array.
912 #
913 do_test where-11.1 {
914 execsql {
915 CREATE TABLE t99(Dte INT, X INT);
916 DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR
917 (Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR
918 (Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR
919 (Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR
920 (Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR
921 (Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR
922 (Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR
923 (Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR
924 (Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR
925 (Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR
926 (Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR
927 (Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR
928 (Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR
929 (Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR
930 (Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR
931 (Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR
932 (Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR
933 (Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR
934 (Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR
935 (Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR
936 (Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR
937 (Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611);
938 }
939 } {}
940  
941 # Ticket #2116: Make sure sorting by index works well with nn INTEGER PRIMARY
942 # KEY.
943 #
944 do_test where-12.1 {
945 execsql {
946 CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
947 INSERT INTO t6 VALUES(1,'one');
948 INSERT INTO t6 VALUES(4,'four');
949 CREATE INDEX t6i1 ON t6(b);
950 }
951 cksort {
952 SELECT * FROM t6 ORDER BY b;
953 }
954 } {4 four 1 one nosort}
955 do_test where-12.2 {
956 cksort {
957 SELECT * FROM t6 ORDER BY b, a;
958 }
959 } {4 four 1 one nosort}
960 do_test where-12.3 {
961 cksort {
962 SELECT * FROM t6 ORDER BY a;
963 }
964 } {1 one 4 four nosort}
965 do_test where-12.4 {
966 cksort {
967 SELECT * FROM t6 ORDER BY a, b;
968 }
969 } {1 one 4 four nosort}
970 do_test where-12.5 {
971 cksort {
972 SELECT * FROM t6 ORDER BY b DESC;
973 }
974 } {1 one 4 four nosort}
975 do_test where-12.6 {
976 cksort {
977 SELECT * FROM t6 ORDER BY b DESC, a DESC;
978 }
979 } {1 one 4 four nosort}
980 do_test where-12.7 {
981 cksort {
982 SELECT * FROM t6 ORDER BY b DESC, a ASC;
983 }
984 } {1 one 4 four sort}
985 do_test where-12.8 {
986 cksort {
987 SELECT * FROM t6 ORDER BY b ASC, a DESC;
988 }
989 } {4 four 1 one sort}
990 do_test where-12.9 {
991 cksort {
992 SELECT * FROM t6 ORDER BY a DESC;
993 }
994 } {4 four 1 one nosort}
995 do_test where-12.10 {
996 cksort {
997 SELECT * FROM t6 ORDER BY a DESC, b DESC;
998 }
999 } {4 four 1 one nosort}
1000 do_test where-12.11 {
1001 cksort {
1002 SELECT * FROM t6 ORDER BY a DESC, b ASC;
1003 }
1004 } {4 four 1 one nosort}
1005 do_test where-12.12 {
1006 cksort {
1007 SELECT * FROM t6 ORDER BY a ASC, b DESC;
1008 }
1009 } {1 one 4 four nosort}
1010 do_test where-13.1 {
1011 execsql {
1012 CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT);
1013 INSERT INTO t7 VALUES(1,'one');
1014 INSERT INTO t7 VALUES(4,'four');
1015 CREATE INDEX t7i1 ON t7(b);
1016 }
1017 cksort {
1018 SELECT * FROM t7 ORDER BY b;
1019 }
1020 } {4 four 1 one nosort}
1021 do_test where-13.2 {
1022 cksort {
1023 SELECT * FROM t7 ORDER BY b, a;
1024 }
1025 } {4 four 1 one nosort}
1026 do_test where-13.3 {
1027 cksort {
1028 SELECT * FROM t7 ORDER BY a;
1029 }
1030 } {1 one 4 four nosort}
1031 do_test where-13.4 {
1032 cksort {
1033 SELECT * FROM t7 ORDER BY a, b;
1034 }
1035 } {1 one 4 four nosort}
1036 do_test where-13.5 {
1037 cksort {
1038 SELECT * FROM t7 ORDER BY b DESC;
1039 }
1040 } {1 one 4 four nosort}
1041 do_test where-13.6 {
1042 cksort {
1043 SELECT * FROM t7 ORDER BY b DESC, a DESC;
1044 }
1045 } {1 one 4 four nosort}
1046 do_test where-13.7 {
1047 cksort {
1048 SELECT * FROM t7 ORDER BY b DESC, a ASC;
1049 }
1050 } {1 one 4 four sort}
1051 do_test where-13.8 {
1052 cksort {
1053 SELECT * FROM t7 ORDER BY b ASC, a DESC;
1054 }
1055 } {4 four 1 one sort}
1056 do_test where-13.9 {
1057 cksort {
1058 SELECT * FROM t7 ORDER BY a DESC;
1059 }
1060 } {4 four 1 one nosort}
1061 do_test where-13.10 {
1062 cksort {
1063 SELECT * FROM t7 ORDER BY a DESC, b DESC;
1064 }
1065 } {4 four 1 one nosort}
1066 do_test where-13.11 {
1067 cksort {
1068 SELECT * FROM t7 ORDER BY a DESC, b ASC;
1069 }
1070 } {4 four 1 one nosort}
1071 do_test where-13.12 {
1072 cksort {
1073 SELECT * FROM t7 ORDER BY a ASC, b DESC;
1074 }
1075 } {1 one 4 four nosort}
1076  
1077 # Ticket #2211.
1078 #
1079 # When optimizing out ORDER BY clauses, make sure that trailing terms
1080 # of the ORDER BY clause do not reference other tables in a join.
1081 #
1082 do_test where-14.1 {
1083 execsql {
1084 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
1085 INSERT INTO t8 VALUES(1,'one');
1086 INSERT INTO t8 VALUES(4,'four');
1087 }
1088 cksort {
1089 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
1090 }
1091 } {1/4 1/1 4/4 4/1 sort}
1092 do_test where-14.2 {
1093 cksort {
1094 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
1095 }
1096 } {1/1 1/4 4/1 4/4 sort}
1097 do_test where-14.3 {
1098 cksort {
1099 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
1100 }
1101 } {1/1 1/4 4/1 4/4 nosort}
1102 do_test where-14.4 {
1103 cksort {
1104 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
1105 }
1106 } {1/1 1/4 4/1 4/4 nosort}
1107 do_test where-14.5 {
1108 cksort {
1109 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
1110 }
1111 } {4/1 4/4 1/1 1/4 nosort}
1112 do_test where-14.6 {
1113 cksort {
1114 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
1115 }
1116 } {4/1 4/4 1/1 1/4 nosort}
1117 do_test where-14.7 {
1118 cksort {
1119 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
1120 }
1121 } {4/1 4/4 1/1 1/4 sort}
1122 do_test where-14.7.1 {
1123 cksort {
1124 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
1125 }
1126 } {4/1 4/4 1/1 1/4 sort}
1127 do_test where-14.7.2 {
1128 cksort {
1129 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
1130 }
1131 } {4/1 4/4 1/1 1/4 nosort}
1132 do_test where-14.8 {
1133 cksort {
1134 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
1135 }
1136 } {4/4 4/1 1/4 1/1 sort}
1137 do_test where-14.9 {
1138 cksort {
1139 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b
1140 }
1141 } {4/4 4/1 1/4 1/1 sort}
1142 do_test where-14.10 {
1143 cksort {
1144 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC
1145 }
1146 } {4/1 4/4 1/1 1/4 sort}
1147 do_test where-14.11 {
1148 cksort {
1149 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b
1150 }
1151 } {4/1 4/4 1/1 1/4 sort}
1152 do_test where-14.12 {
1153 cksort {
1154 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC
1155 }
1156 } {4/4 4/1 1/4 1/1 sort}
1157  
1158 # Ticket #2445.
1159 #
1160 # There was a crash that could occur when a where clause contains an
1161 # alias for an expression in the result set, and that expression retrieves
1162 # a column of the second or subsequent table in a join.
1163 #
1164 do_test where-15.1 {
1165 execsql {
1166 CREATE TEMP TABLE t1 (a, b, c, d, e);
1167 CREATE TEMP TABLE t2 (f);
1168 SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ;
1169 }
1170 } {}
1171  
1172 # Ticket #3408.
1173 #
1174 # The branch of code in where.c that generated rowid lookups was
1175 # incorrectly deallocating a constant register, meaning that if the
1176 # vdbe code ran more than once, the second time around the constant
1177 # value may have been clobbered by some other value.
1178 #
1179 do_test where-16.1 {
1180 execsql {
1181 CREATE TABLE a1(id INTEGER PRIMARY KEY, v);
1182 CREATE TABLE a2(id INTEGER PRIMARY KEY, v);
1183 INSERT INTO a1 VALUES(1, 'one');
1184 INSERT INTO a1 VALUES(2, 'two');
1185 INSERT INTO a2 VALUES(1, 'one');
1186 INSERT INTO a2 VALUES(2, 'two');
1187 }
1188 } {}
1189 do_test where-16.2 {
1190 execsql {
1191 SELECT * FROM a2 CROSS JOIN a1 WHERE a1.id=1 AND a1.v='one';
1192 }
1193 } {1 one 1 one 2 two 1 one}
1194  
1195 # The actual problem reported in #3408.
1196 do_test where-16.3 {
1197 execsql {
1198 CREATE TEMP TABLE foo(idx INTEGER);
1199 INSERT INTO foo VALUES(1);
1200 INSERT INTO foo VALUES(1);
1201 INSERT INTO foo VALUES(1);
1202 INSERT INTO foo VALUES(2);
1203 INSERT INTO foo VALUES(2);
1204 CREATE TEMP TABLE bar(stuff INTEGER);
1205 INSERT INTO bar VALUES(100);
1206 INSERT INTO bar VALUES(200);
1207 INSERT INTO bar VALUES(300);
1208 }
1209 } {}
1210 do_test where-16.4 {
1211 execsql {
1212 SELECT bar.RowID id FROM foo, bar WHERE foo.idx = bar.RowID AND id = 2;
1213 }
1214 } {2 2}
1215  
1216 integrity_check {where-99.0}
1217  
1218 #---------------------------------------------------------------------
1219 # These tests test that a bug surrounding the use of ForceInt has been
1220 # fixed in where.c.
1221 #
1222 do_test where-17.1 {
1223 execsql {
1224 CREATE TABLE tbooking (
1225 id INTEGER PRIMARY KEY,
1226 eventtype INTEGER NOT NULL
1227 );
1228 INSERT INTO tbooking VALUES(42, 3);
1229 INSERT INTO tbooking VALUES(43, 4);
1230 }
1231 } {}
1232 do_test where-17.2 {
1233 execsql {
1234 SELECT a.id
1235 FROM tbooking AS a
1236 WHERE a.eventtype=3;
1237 }
1238 } {42}
1239 do_test where-17.3 {
1240 execsql {
1241 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id)
1242 FROM tbooking AS a
1243 WHERE a.eventtype=3;
1244 }
1245 } {42 43}
1246 do_test where-17.4 {
1247 execsql {
1248 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id)
1249 FROM (SELECT 1.5 AS id) AS a
1250 }
1251 } {1.5 42}
1252 do_test where-17.5 {
1253 execsql {
1254 CREATE TABLE tother(a, b);
1255 INSERT INTO tother VALUES(1, 3.7);
1256 SELECT id, a FROM tbooking, tother WHERE id>a;
1257 }
1258 } {42 1 43 1}
1259  
1260 finish_test