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