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