1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
4# 2001 September 15
5#
6# The author disclaims copyright to this source code.  In place of
7# a legal notice, here is a blessing:
8#
9#    May you do good and not evil.
10#    May you find forgiveness for yourself and forgive others.
11#    May you share freely, never taking more than you give.
12#
13#***********************************************************************
14# This file implements regression tests for SQLite library.  The
15# focus of this file is testing the use of indices in WHERE clases.
16#
17# $Id: where.test,v 1.17 2003/06/15 23:42:25 drh Exp $
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# Build some test data
23#
24do_test where-1.0 {
25  execsql {
26    CREATE TABLE t1(w int, x int, y int);
27    CREATE TABLE t2(p int, q int, r int, s int);
28  }
29  for {set i 1} {$i<=100} {incr i} {
30    set w $i
31    set x [expr {int(log($i)/log(2))}]
32    set y [expr {$i*$i + 2*$i + 1}]
33    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
34  }
35  execsql {
36    INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
37    CREATE INDEX i1w ON t1(w);
38    CREATE INDEX i1xy ON t1(x,y);
39    CREATE INDEX i2p ON t2(p);
40    CREATE INDEX i2r ON t2(r);
41    CREATE INDEX i2qs ON t2(q, s);
42  }
43} {}
44
45# Do an SQL statement.  Append the search count to the end of the result.
46#
47proc count sql {
48  set ::sqlite_search_count 0
49  return [concat [execsql $sql] $::sqlite_search_count]
50}
51
52# Verify that queries use an index.  We are using the special variable
53# "sqlite_search_count" which tallys the number of executions of MoveTo
54# and Next operators in the VDBE.  By verifing that the search count is
55# small we can be assured that indices are being used properly.
56#
57do_test where-1.1 {
58  count {SELECT x, y FROM t1 WHERE w=10}
59} {3 121 3}
60do_test where-1.2 {
61  count {SELECT x, y FROM t1 WHERE w=11}
62} {3 144 3}
63do_test where-1.3 {
64  count {SELECT x, y FROM t1 WHERE 11=w}
65} {3 144 3}
66do_test where-1.4 {
67  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
68} {3 144 3}
69do_test where-1.5 {
70  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
71} {3 144 3}
72do_test where-1.6 {
73  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
74} {3 144 3}
75do_test where-1.7 {
76  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
77} {3 144 3}
78do_test where-1.8 {
79  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
80} {3 144 3}
81do_test where-1.9 {
82  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
83} {3 144 3}
84do_test where-1.10 {
85  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
86} {3 121 3}
87do_test where-1.11 {
88  count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
89} {3 100 3}
90
91# New for SQLite version 2.1: Verify that that inequality constraints
92# are used correctly.
93#
94do_test where-1.12 {
95  count {SELECT w FROM t1 WHERE x=3 AND y<100}
96} {8 3}
97do_test where-1.13 {
98  count {SELECT w FROM t1 WHERE x=3 AND 100>y}
99} {8 3}
100do_test where-1.14 {
101  count {SELECT w FROM t1 WHERE 3=x AND y<100}
102} {8 3}
103do_test where-1.15 {
104  count {SELECT w FROM t1 WHERE 3=x AND 100>y}
105} {8 3}
106do_test where-1.16 {
107  count {SELECT w FROM t1 WHERE x=3 AND y<=100}
108} {8 9 5}
109do_test where-1.17 {
110  count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
111} {8 9 5}
112do_test where-1.18 {
113  count {SELECT w FROM t1 WHERE x=3 AND y>225}
114} {15 3}
115do_test where-1.19 {
116  count {SELECT w FROM t1 WHERE x=3 AND 225<y}
117} {15 3}
118do_test where-1.20 {
119  count {SELECT w FROM t1 WHERE x=3 AND y>=225}
120} {14 15 5}
121do_test where-1.21 {
122  count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
123} {14 15 5}
124do_test where-1.22 {
125  count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
126} {11 12 5}
127do_test where-1.23 {
128  count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
129} {10 11 12 13 9}
130do_test where-1.24 {
131  count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
132} {11 12 5}
133do_test where-1.25 {
134  count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
135} {10 11 12 13 9}
136
137# Need to work on optimizing the BETWEEN operator.
138#
139# do_test where-1.26 {
140#   count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
141# } {10 11 12 13 9}
142
143do_test where-1.27 {
144  count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
145} {10 17}
146do_test where-1.28 {
147  count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
148} {10 99}
149do_test where-1.29 {
150  count {SELECT w FROM t1 WHERE y==121}
151} {10 99}
152
153
154do_test where-1.30 {
155  count {SELECT w FROM t1 WHERE w>97}
156} {98 99 100 6}
157do_test where-1.31 {
158  count {SELECT w FROM t1 WHERE w>=97}
159} {97 98 99 100 8}
160do_test where-1.33 {
161  count {SELECT w FROM t1 WHERE w==97}
162} {97 3}
163do_test where-1.34 {
164  count {SELECT w FROM t1 WHERE w+1==98}
165} {97 99}
166do_test where-1.35 {
167  count {SELECT w FROM t1 WHERE w<3}
168} {1 2 4}
169do_test where-1.36 {
170  count {SELECT w FROM t1 WHERE w<=3}
171} {1 2 3 6}
172do_test where-1.37 {
173  count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
174} {1 2 3 199}
175
176do_test where-1.38 {
177  count {SELECT (w) FROM t1 WHERE (w)>(97)}
178} {98 99 100 6}
179do_test where-1.39 {
180  count {SELECT (w) FROM t1 WHERE (w)>=(97)}
181} {97 98 99 100 8}
182do_test where-1.40 {
183  count {SELECT (w) FROM t1 WHERE (w)==(97)}
184} {97 3}
185do_test where-1.41 {
186  count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
187} {97 99}
188
189
190# Do the same kind of thing except use a join as the data source.
191#
192do_test where-2.1 {
193  count {
194    SELECT w, p FROM t2, t1
195    WHERE x=q AND y=s AND r=8977
196  }
197} {34 67 6}
198do_test where-2.2 {
199  count {
200    SELECT w, p FROM t2, t1
201    WHERE x=q AND s=y AND r=8977
202  }
203} {34 67 6}
204do_test where-2.3 {
205  count {
206    SELECT w, p FROM t2, t1
207    WHERE x=q AND s=y AND r=8977 AND w>10
208  }
209} {34 67 6}
210do_test where-2.4 {
211  count {
212    SELECT w, p FROM t2, t1
213    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
214  }
215} {34 67 6}
216do_test where-2.5 {
217  count {
218    SELECT w, p FROM t2, t1
219    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
220  }
221} {34 67 6}
222do_test where-2.6 {
223  count {
224    SELECT w, p FROM t2, t1
225    WHERE x=q AND p=77 AND s=y AND w>5
226  }
227} {24 77 6}
228do_test where-2.7 {
229  count {
230    SELECT w, p FROM t1, t2
231    WHERE x=q AND p>77 AND s=y AND w=5
232  }
233} {5 96 6}
234
235# Lets do a 3-way join.
236#
237do_test where-3.1 {
238  count {
239    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
240    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
241  }
242} {11 90 11 9}
243do_test where-3.2 {
244  count {
245    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
246    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
247  }
248} {12 89 12 9}
249do_test where-3.3 {
250  count {
251    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
252    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
253  }
254} {15 86 86 9}
255
256# Test to see that the special case of a constant WHERE clause is
257# handled.
258#
259do_test where-4.1 {
260  count {
261    SELECT * FROM t1 WHERE 0
262  }
263} {0}
264do_test where-4.2 {
265  count {
266    SELECT * FROM t1 WHERE 1 LIMIT 1
267  }
268} {1 0 4 1}
269do_test where-4.3 {
270  execsql {
271    SELECT 99 WHERE 0
272  }
273} {}
274do_test where-4.4 {
275  execsql {
276    SELECT 99 WHERE 1
277  }
278} {99}
279
280# Verify that IN operators in a WHERE clause are handled correctly.
281#
282do_test where-5.1 {
283  count {
284    SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
285  }
286} {1 0 4 2 1 9 3 1 16 0}
287do_test where-5.2 {
288  count {
289    SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
290  }
291} {1 0 4 2 1 9 3 1 16 199}
292do_test where-5.3 {
293  count {
294    SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
295  }
296} {1 0 4 2 1 9 3 1 16 10}
297do_test where-5.4 {
298  count {
299    SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
300  }
301} {1 0 4 2 1 9 3 1 16 199}
302do_test where-5.5 {
303  count {
304    SELECT * FROM t1 WHERE rowid IN
305       (select rowid from t1 where rowid IN (-1,2,4))
306    ORDER BY 1;
307  }
308} {2 1 9 4 2 25 1}
309do_test where-5.6 {
310  count {
311    SELECT * FROM t1 WHERE rowid+0 IN
312       (select rowid from t1 where rowid IN (-1,2,4))
313    ORDER BY 1;
314  }
315} {2 1 9 4 2 25 199}
316do_test where-5.7 {
317  count {
318    SELECT * FROM t1 WHERE w IN
319       (select rowid from t1 where rowid IN (-1,2,4))
320    ORDER BY 1;
321  }
322} {2 1 9 4 2 25 7}
323do_test where-5.8 {
324  count {
325    SELECT * FROM t1 WHERE w+0 IN
326       (select rowid from t1 where rowid IN (-1,2,4))
327    ORDER BY 1;
328  }
329} {2 1 9 4 2 25 199}
330do_test where-5.9 {
331  count {
332    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
333  }
334} {2 1 9 3 1 16 6}
335do_test where-5.10 {
336  count {
337    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
338  }
339} {2 1 9 3 1 16 199}
340do_test where-5.11 {
341  count {
342    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
343  }
344} {79 6 6400 89 6 8100 199}
345do_test where-5.12 {
346  count {
347    SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
348  }
349} {79 6 6400 89 6 8100 74}
350do_test where-5.13 {
351  count {
352    SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
353  }
354} {2 1 9 3 1 16 6}
355do_test where-5.14 {
356  count {
357    SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
358  }
359} {2 1 9 6}
360
361# This procedure executes the SQL.  Then it checks the generated program
362# for the SQL and appends a "nosort" to the result if the program contains the
363# SortCallback opcode.  If the program does not contain the SortCallback
364# opcode it appends "sort"
365#
366proc cksort {sql} {
367  set data [execsql $sql]
368  set prog [execsql "EXPLAIN $sql"]
369  if {[regexp SortCallback $prog]} {set x sort} {set x nosort}
370  lappend data $x
371  return $data
372}
373# Check out the logic that attempts to implement the ORDER BY clause
374# using an index rather than by sorting.
375#
376do_test where-6.1 {
377  execsql {
378    CREATE TABLE t3(a,b,c);
379    CREATE INDEX t3a ON t3(a);
380    CREATE INDEX t3bc ON t3(b,c);
381    CREATE INDEX t3acb ON t3(a,c,b);
382    INSERT INTO t3 SELECT w, 101-w, y FROM t1;
383    SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
384  }
385} {100 5050 5050 348550}
386do_test where-6.2 {
387  cksort {
388    SELECT * FROM t3 ORDER BY a LIMIT 3
389  }
390} {1 100 4 2 99 9 3 98 16 nosort}
391do_test where-6.3 {
392  cksort {
393    SELECT * FROM t3 ORDER BY a+1 LIMIT 3
394  }
395} {1 100 4 2 99 9 3 98 16 sort}
396do_test where-6.4 {
397  cksort {
398    SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
399  }
400} {1 100 4 2 99 9 3 98 16 nosort}
401do_test where-6.5 {
402  cksort {
403    SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
404  }
405} {1 100 4 2 99 9 3 98 16 nosort}
406do_test where-6.6 {
407  cksort {
408    SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
409  }
410} {1 100 4 2 99 9 3 98 16 nosort}
411do_test where-6.7 {
412  cksort {
413    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
414  }
415} {1 100 4 2 99 9 3 98 16 sort}
416do_test where-6.8 {
417  cksort {
418    SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
419  }
420} {1 100 4 2 99 9 3 98 16 sort}
421do_test where-6.9.1 {
422  cksort {
423    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
424  }
425} {1 100 4 nosort}
426do_test where-6.9.2 {
427  cksort {
428    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
429  }
430} {1 100 4 nosort}
431do_test where-6.9.3 {
432  cksort {
433    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
434  }
435} {1 100 4 nosort}
436do_test where-6.9.4 {
437  cksort {
438    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
439  }
440} {1 100 4 nosort}
441do_test where-6.9.5 {
442  cksort {
443    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
444  }
445} {1 100 4 nosort}
446do_test where-6.9.6 {
447  cksort {
448    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
449  }
450} {1 100 4 nosort}
451do_test where-6.9.7 {
452  cksort {
453    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
454  }
455} {1 100 4 sort}
456do_test where-6.9.8 {
457  cksort {
458    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
459  }
460} {1 100 4 sort}
461do_test where-6.9.9 {
462  cksort {
463    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
464  }
465} {1 100 4 sort}
466do_test where-6.10 {
467  cksort {
468    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
469  }
470} {1 100 4 nosort}
471do_test where-6.11 {
472  cksort {
473    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
474  }
475} {1 100 4 nosort}
476do_test where-6.12 {
477  cksort {
478    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
479  }
480} {1 100 4 nosort}
481do_test where-6.13 {
482  cksort {
483    SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
484  }
485} {100 1 10201 99 2 10000 98 3 9801 nosort}
486do_test where-6.13.1 {
487  cksort {
488    SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
489  }
490} {100 1 10201 99 2 10000 98 3 9801 sort}
491do_test where-6.14 {
492  cksort {
493    SELECT * FROM t3 ORDER BY b LIMIT 3
494  }
495} {100 1 10201 99 2 10000 98 3 9801 nosort}
496do_test where-6.15 {
497  cksort {
498    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
499  }
500} {1 0 2 1 3 1 nosort}
501do_test where-6.16 {
502  cksort {
503    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
504  }
505} {1 0 2 1 3 1 sort}
506do_test where-6.17 {
507  cksort {
508    SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;
509  }
510} {4 121 10201 sort}
511do_test where-6.18 {
512  cksort {
513    SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;
514  }
515} {4 9 16 sort}
516do_test where-6.19 {
517  cksort {
518    SELECT y FROM t1 ORDER BY w LIMIT 3;
519  }
520} {4 9 16 nosort}
521
522# Tests for reverse-order sorting.
523#
524do_test where-7.1 {
525  cksort {
526    SELECT w FROM t1 WHERE x=3 ORDER BY y;
527  }
528} {8 9 10 11 12 13 14 15 nosort}
529do_test where-7.2 {
530  cksort {
531    SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
532  }
533} {15 14 13 12 11 10 9 8 nosort}
534do_test where-7.3 {
535  cksort {
536    SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
537  }
538} {10 11 12 nosort}
539do_test where-7.4 {
540  cksort {
541    SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
542  }
543} {15 14 13 nosort}
544do_test where-7.5 {
545  cksort {
546    SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
547  }
548} {15 14 13 12 11 nosort}
549do_test where-7.6 {
550  cksort {
551    SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
552  }
553} {15 14 13 12 11 10 nosort}
554do_test where-7.7 {
555  cksort {
556    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
557  }
558} {12 11 10 nosort}
559do_test where-7.8 {
560  cksort {
561    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
562  }
563} {13 12 11 10 nosort}
564do_test where-7.9 {
565  cksort {
566    SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
567  }
568} {13 12 11 nosort}
569do_test where-7.10 {
570  cksort {
571    SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
572  }
573} {12 11 10 nosort}
574do_test where-7.11 {
575  cksort {
576    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
577  }
578} {10 11 12 nosort}
579do_test where-7.12 {
580  cksort {
581    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
582  }
583} {10 11 12 13 nosort}
584do_test where-7.13 {
585  cksort {
586    SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
587  }
588} {11 12 13 nosort}
589do_test where-7.14 {
590  cksort {
591    SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
592  }
593} {10 11 12 nosort}
594do_test where-7.15 {
595  cksort {
596    SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
597  }
598} {nosort}
599do_test where-7.16 {
600  cksort {
601    SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
602  }
603} {8 nosort}
604do_test where-7.17 {
605  cksort {
606    SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
607  }
608} {nosort}
609do_test where-7.18 {
610  cksort {
611    SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
612  }
613} {15 nosort}
614do_test where-7.19 {
615  cksort {
616    SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
617  }
618} {nosort}
619do_test where-7.20 {
620  cksort {
621    SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
622  }
623} {8 nosort}
624do_test where-7.21 {
625  cksort {
626    SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
627  }
628} {nosort}
629do_test where-7.22 {
630  cksort {
631    SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
632  }
633} {15 nosort}
634do_test where-7.23 {
635  cksort {
636    SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
637  }
638} {nosort}
639do_test where-7.24 {
640  cksort {
641    SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
642  }
643} {1 nosort}
644do_test where-7.25 {
645  cksort {
646    SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
647  }
648} {nosort}
649do_test where-7.26 {
650  cksort {
651    SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
652  }
653} {100 nosort}
654do_test where-7.27 {
655  cksort {
656    SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
657  }
658} {nosort}
659do_test where-7.28 {
660  cksort {
661    SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
662  }
663} {1 nosort}
664do_test where-7.29 {
665  cksort {
666    SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
667  }
668} {nosort}
669do_test where-7.30 {
670  cksort {
671    SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
672  }
673} {100 nosort}
674
675do_test where-8.1 {
676  execsql {
677    CREATE TABLE t4 AS SELECT * FROM t1;
678    CREATE INDEX i4xy ON t4(x,y);
679  }
680  cksort {
681    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
682  }
683} {30 29 28 nosort}
684do_test where-8.2 {
685  execsql {
686    DELETE FROM t4;
687  }
688  cksort {
689    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
690  }
691} {nosort}
692
693# Make sure searches with an index work with an empty table.
694#
695do_test where-9.1 {
696  execsql {
697    CREATE TABLE t5(x PRIMARY KEY);
698    SELECT * FROM t5 WHERE x<10;
699  }
700} {}
701do_test where-9.2 {
702  execsql {
703    SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
704  }
705} {}
706do_test where-9.3 {
707  execsql {
708    SELECT * FROM t5 WHERE x=10;
709  }
710} {}
711
712do_test where-10.1 {
713  execsql {
714    SELECT 1 WHERE abs(random())<0
715  }
716} {}
717do_test where-10.2 {
718  proc tclvar_func {vname} {return [set ::$vname]}
719  db function tclvar tclvar_func
720  set ::v1 0
721  execsql {
722    SELECT count(*) FROM t1 WHERE tclvar('v1');
723  }
724} {0}
725do_test where-10.3 {
726  set ::v1 1
727  execsql {
728    SELECT count(*) FROM t1 WHERE tclvar('v1');
729  }
730} {100}
731do_test where-10.4 {
732  set ::v1 1
733  proc tclvar_func {vname} {
734    upvar #0 $vname v
735    set v [expr {!$v}]
736    return $v
737  }
738  execsql {
739    SELECT count(*) FROM t1 WHERE tclvar('v1');
740  }
741} {50}
742
743integrity_check {where-99.0}
744
745finish_test
746