1*1da57d55SToomas Soome#
27c478bd9Sstevel@tonic-gate# 2001 September 15
37c478bd9Sstevel@tonic-gate#
47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code.  In place of
57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing:
67c478bd9Sstevel@tonic-gate#
77c478bd9Sstevel@tonic-gate#    May you do good and not evil.
87c478bd9Sstevel@tonic-gate#    May you find forgiveness for yourself and forgive others.
97c478bd9Sstevel@tonic-gate#    May you share freely, never taking more than you give.
107c478bd9Sstevel@tonic-gate#
117c478bd9Sstevel@tonic-gate#***********************************************************************
127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library.  The
137c478bd9Sstevel@tonic-gate# focus of this file is testing the use of indices in WHERE clases.
147c478bd9Sstevel@tonic-gate#
157c478bd9Sstevel@tonic-gate# $Id: where.test,v 1.17 2003/06/15 23:42:25 drh Exp $
167c478bd9Sstevel@tonic-gate
177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0]
187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl
197c478bd9Sstevel@tonic-gate
207c478bd9Sstevel@tonic-gate# Build some test data
217c478bd9Sstevel@tonic-gate#
227c478bd9Sstevel@tonic-gatedo_test where-1.0 {
237c478bd9Sstevel@tonic-gate  execsql {
247c478bd9Sstevel@tonic-gate    CREATE TABLE t1(w int, x int, y int);
257c478bd9Sstevel@tonic-gate    CREATE TABLE t2(p int, q int, r int, s int);
267c478bd9Sstevel@tonic-gate  }
277c478bd9Sstevel@tonic-gate  for {set i 1} {$i<=100} {incr i} {
287c478bd9Sstevel@tonic-gate    set w $i
297c478bd9Sstevel@tonic-gate    set x [expr {int(log($i)/log(2))}]
307c478bd9Sstevel@tonic-gate    set y [expr {$i*$i + 2*$i + 1}]
317c478bd9Sstevel@tonic-gate    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
327c478bd9Sstevel@tonic-gate  }
337c478bd9Sstevel@tonic-gate  execsql {
347c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
357c478bd9Sstevel@tonic-gate    CREATE INDEX i1w ON t1(w);
367c478bd9Sstevel@tonic-gate    CREATE INDEX i1xy ON t1(x,y);
377c478bd9Sstevel@tonic-gate    CREATE INDEX i2p ON t2(p);
387c478bd9Sstevel@tonic-gate    CREATE INDEX i2r ON t2(r);
397c478bd9Sstevel@tonic-gate    CREATE INDEX i2qs ON t2(q, s);
407c478bd9Sstevel@tonic-gate  }
417c478bd9Sstevel@tonic-gate} {}
427c478bd9Sstevel@tonic-gate
437c478bd9Sstevel@tonic-gate# Do an SQL statement.  Append the search count to the end of the result.
447c478bd9Sstevel@tonic-gate#
457c478bd9Sstevel@tonic-gateproc count sql {
467c478bd9Sstevel@tonic-gate  set ::sqlite_search_count 0
477c478bd9Sstevel@tonic-gate  return [concat [execsql $sql] $::sqlite_search_count]
487c478bd9Sstevel@tonic-gate}
497c478bd9Sstevel@tonic-gate
507c478bd9Sstevel@tonic-gate# Verify that queries use an index.  We are using the special variable
517c478bd9Sstevel@tonic-gate# "sqlite_search_count" which tallys the number of executions of MoveTo
527c478bd9Sstevel@tonic-gate# and Next operators in the VDBE.  By verifing that the search count is
537c478bd9Sstevel@tonic-gate# small we can be assured that indices are being used properly.
547c478bd9Sstevel@tonic-gate#
557c478bd9Sstevel@tonic-gatedo_test where-1.1 {
567c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE w=10}
577c478bd9Sstevel@tonic-gate} {3 121 3}
587c478bd9Sstevel@tonic-gatedo_test where-1.2 {
597c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE w=11}
607c478bd9Sstevel@tonic-gate} {3 144 3}
617c478bd9Sstevel@tonic-gatedo_test where-1.3 {
627c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE 11=w}
637c478bd9Sstevel@tonic-gate} {3 144 3}
647c478bd9Sstevel@tonic-gatedo_test where-1.4 {
657c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
667c478bd9Sstevel@tonic-gate} {3 144 3}
677c478bd9Sstevel@tonic-gatedo_test where-1.5 {
687c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
697c478bd9Sstevel@tonic-gate} {3 144 3}
707c478bd9Sstevel@tonic-gatedo_test where-1.6 {
717c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
727c478bd9Sstevel@tonic-gate} {3 144 3}
737c478bd9Sstevel@tonic-gatedo_test where-1.7 {
747c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
757c478bd9Sstevel@tonic-gate} {3 144 3}
767c478bd9Sstevel@tonic-gatedo_test where-1.8 {
777c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
787c478bd9Sstevel@tonic-gate} {3 144 3}
797c478bd9Sstevel@tonic-gatedo_test where-1.9 {
807c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
817c478bd9Sstevel@tonic-gate} {3 144 3}
827c478bd9Sstevel@tonic-gatedo_test where-1.10 {
837c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
847c478bd9Sstevel@tonic-gate} {3 121 3}
857c478bd9Sstevel@tonic-gatedo_test where-1.11 {
867c478bd9Sstevel@tonic-gate  count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
877c478bd9Sstevel@tonic-gate} {3 100 3}
887c478bd9Sstevel@tonic-gate
897c478bd9Sstevel@tonic-gate# New for SQLite version 2.1: Verify that that inequality constraints
907c478bd9Sstevel@tonic-gate# are used correctly.
917c478bd9Sstevel@tonic-gate#
927c478bd9Sstevel@tonic-gatedo_test where-1.12 {
937c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND y<100}
947c478bd9Sstevel@tonic-gate} {8 3}
957c478bd9Sstevel@tonic-gatedo_test where-1.13 {
967c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND 100>y}
977c478bd9Sstevel@tonic-gate} {8 3}
987c478bd9Sstevel@tonic-gatedo_test where-1.14 {
997c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE 3=x AND y<100}
1007c478bd9Sstevel@tonic-gate} {8 3}
1017c478bd9Sstevel@tonic-gatedo_test where-1.15 {
1027c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE 3=x AND 100>y}
1037c478bd9Sstevel@tonic-gate} {8 3}
1047c478bd9Sstevel@tonic-gatedo_test where-1.16 {
1057c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND y<=100}
1067c478bd9Sstevel@tonic-gate} {8 9 5}
1077c478bd9Sstevel@tonic-gatedo_test where-1.17 {
1087c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
1097c478bd9Sstevel@tonic-gate} {8 9 5}
1107c478bd9Sstevel@tonic-gatedo_test where-1.18 {
1117c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND y>225}
1127c478bd9Sstevel@tonic-gate} {15 3}
1137c478bd9Sstevel@tonic-gatedo_test where-1.19 {
1147c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND 225<y}
1157c478bd9Sstevel@tonic-gate} {15 3}
1167c478bd9Sstevel@tonic-gatedo_test where-1.20 {
1177c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND y>=225}
1187c478bd9Sstevel@tonic-gate} {14 15 5}
1197c478bd9Sstevel@tonic-gatedo_test where-1.21 {
1207c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
1217c478bd9Sstevel@tonic-gate} {14 15 5}
1227c478bd9Sstevel@tonic-gatedo_test where-1.22 {
1237c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
1247c478bd9Sstevel@tonic-gate} {11 12 5}
1257c478bd9Sstevel@tonic-gatedo_test where-1.23 {
1267c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
1277c478bd9Sstevel@tonic-gate} {10 11 12 13 9}
1287c478bd9Sstevel@tonic-gatedo_test where-1.24 {
1297c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
1307c478bd9Sstevel@tonic-gate} {11 12 5}
1317c478bd9Sstevel@tonic-gatedo_test where-1.25 {
1327c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
1337c478bd9Sstevel@tonic-gate} {10 11 12 13 9}
1347c478bd9Sstevel@tonic-gate
135*1da57d55SToomas Soome# Need to work on optimizing the BETWEEN operator.
1367c478bd9Sstevel@tonic-gate#
1377c478bd9Sstevel@tonic-gate# do_test where-1.26 {
1387c478bd9Sstevel@tonic-gate#   count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
1397c478bd9Sstevel@tonic-gate# } {10 11 12 13 9}
1407c478bd9Sstevel@tonic-gate
1417c478bd9Sstevel@tonic-gatedo_test where-1.27 {
1427c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
1437c478bd9Sstevel@tonic-gate} {10 17}
1447c478bd9Sstevel@tonic-gatedo_test where-1.28 {
1457c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
1467c478bd9Sstevel@tonic-gate} {10 99}
1477c478bd9Sstevel@tonic-gatedo_test where-1.29 {
1487c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE y==121}
1497c478bd9Sstevel@tonic-gate} {10 99}
1507c478bd9Sstevel@tonic-gate
1517c478bd9Sstevel@tonic-gate
1527c478bd9Sstevel@tonic-gatedo_test where-1.30 {
1537c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE w>97}
1547c478bd9Sstevel@tonic-gate} {98 99 100 6}
1557c478bd9Sstevel@tonic-gatedo_test where-1.31 {
1567c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE w>=97}
1577c478bd9Sstevel@tonic-gate} {97 98 99 100 8}
1587c478bd9Sstevel@tonic-gatedo_test where-1.33 {
1597c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE w==97}
1607c478bd9Sstevel@tonic-gate} {97 3}
1617c478bd9Sstevel@tonic-gatedo_test where-1.34 {
1627c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE w+1==98}
1637c478bd9Sstevel@tonic-gate} {97 99}
1647c478bd9Sstevel@tonic-gatedo_test where-1.35 {
1657c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE w<3}
1667c478bd9Sstevel@tonic-gate} {1 2 4}
1677c478bd9Sstevel@tonic-gatedo_test where-1.36 {
1687c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE w<=3}
1697c478bd9Sstevel@tonic-gate} {1 2 3 6}
1707c478bd9Sstevel@tonic-gatedo_test where-1.37 {
1717c478bd9Sstevel@tonic-gate  count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
1727c478bd9Sstevel@tonic-gate} {1 2 3 199}
1737c478bd9Sstevel@tonic-gate
1747c478bd9Sstevel@tonic-gatedo_test where-1.38 {
1757c478bd9Sstevel@tonic-gate  count {SELECT (w) FROM t1 WHERE (w)>(97)}
1767c478bd9Sstevel@tonic-gate} {98 99 100 6}
1777c478bd9Sstevel@tonic-gatedo_test where-1.39 {
1787c478bd9Sstevel@tonic-gate  count {SELECT (w) FROM t1 WHERE (w)>=(97)}
1797c478bd9Sstevel@tonic-gate} {97 98 99 100 8}
1807c478bd9Sstevel@tonic-gatedo_test where-1.40 {
1817c478bd9Sstevel@tonic-gate  count {SELECT (w) FROM t1 WHERE (w)==(97)}
1827c478bd9Sstevel@tonic-gate} {97 3}
1837c478bd9Sstevel@tonic-gatedo_test where-1.41 {
1847c478bd9Sstevel@tonic-gate  count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
1857c478bd9Sstevel@tonic-gate} {97 99}
1867c478bd9Sstevel@tonic-gate
1877c478bd9Sstevel@tonic-gate
1887c478bd9Sstevel@tonic-gate# Do the same kind of thing except use a join as the data source.
1897c478bd9Sstevel@tonic-gate#
1907c478bd9Sstevel@tonic-gatedo_test where-2.1 {
1917c478bd9Sstevel@tonic-gate  count {
1927c478bd9Sstevel@tonic-gate    SELECT w, p FROM t2, t1
1937c478bd9Sstevel@tonic-gate    WHERE x=q AND y=s AND r=8977
1947c478bd9Sstevel@tonic-gate  }
1957c478bd9Sstevel@tonic-gate} {34 67 6}
1967c478bd9Sstevel@tonic-gatedo_test where-2.2 {
1977c478bd9Sstevel@tonic-gate  count {
1987c478bd9Sstevel@tonic-gate    SELECT w, p FROM t2, t1
1997c478bd9Sstevel@tonic-gate    WHERE x=q AND s=y AND r=8977
2007c478bd9Sstevel@tonic-gate  }
2017c478bd9Sstevel@tonic-gate} {34 67 6}
2027c478bd9Sstevel@tonic-gatedo_test where-2.3 {
2037c478bd9Sstevel@tonic-gate  count {
2047c478bd9Sstevel@tonic-gate    SELECT w, p FROM t2, t1
2057c478bd9Sstevel@tonic-gate    WHERE x=q AND s=y AND r=8977 AND w>10
2067c478bd9Sstevel@tonic-gate  }
2077c478bd9Sstevel@tonic-gate} {34 67 6}
2087c478bd9Sstevel@tonic-gatedo_test where-2.4 {
2097c478bd9Sstevel@tonic-gate  count {
2107c478bd9Sstevel@tonic-gate    SELECT w, p FROM t2, t1
2117c478bd9Sstevel@tonic-gate    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
2127c478bd9Sstevel@tonic-gate  }
2137c478bd9Sstevel@tonic-gate} {34 67 6}
2147c478bd9Sstevel@tonic-gatedo_test where-2.5 {
2157c478bd9Sstevel@tonic-gate  count {
2167c478bd9Sstevel@tonic-gate    SELECT w, p FROM t2, t1
2177c478bd9Sstevel@tonic-gate    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
2187c478bd9Sstevel@tonic-gate  }
2197c478bd9Sstevel@tonic-gate} {34 67 6}
2207c478bd9Sstevel@tonic-gatedo_test where-2.6 {
2217c478bd9Sstevel@tonic-gate  count {
2227c478bd9Sstevel@tonic-gate    SELECT w, p FROM t2, t1
2237c478bd9Sstevel@tonic-gate    WHERE x=q AND p=77 AND s=y AND w>5
2247c478bd9Sstevel@tonic-gate  }
2257c478bd9Sstevel@tonic-gate} {24 77 6}
2267c478bd9Sstevel@tonic-gatedo_test where-2.7 {
2277c478bd9Sstevel@tonic-gate  count {
2287c478bd9Sstevel@tonic-gate    SELECT w, p FROM t1, t2
2297c478bd9Sstevel@tonic-gate    WHERE x=q AND p>77 AND s=y AND w=5
2307c478bd9Sstevel@tonic-gate  }
2317c478bd9Sstevel@tonic-gate} {5 96 6}
2327c478bd9Sstevel@tonic-gate
2337c478bd9Sstevel@tonic-gate# Lets do a 3-way join.
2347c478bd9Sstevel@tonic-gate#
2357c478bd9Sstevel@tonic-gatedo_test where-3.1 {
2367c478bd9Sstevel@tonic-gate  count {
2377c478bd9Sstevel@tonic-gate    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
2387c478bd9Sstevel@tonic-gate    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
2397c478bd9Sstevel@tonic-gate  }
2407c478bd9Sstevel@tonic-gate} {11 90 11 9}
2417c478bd9Sstevel@tonic-gatedo_test where-3.2 {
2427c478bd9Sstevel@tonic-gate  count {
2437c478bd9Sstevel@tonic-gate    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
2447c478bd9Sstevel@tonic-gate    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
2457c478bd9Sstevel@tonic-gate  }
2467c478bd9Sstevel@tonic-gate} {12 89 12 9}
2477c478bd9Sstevel@tonic-gatedo_test where-3.3 {
2487c478bd9Sstevel@tonic-gate  count {
2497c478bd9Sstevel@tonic-gate    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
2507c478bd9Sstevel@tonic-gate    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
2517c478bd9Sstevel@tonic-gate  }
2527c478bd9Sstevel@tonic-gate} {15 86 86 9}
2537c478bd9Sstevel@tonic-gate
2547c478bd9Sstevel@tonic-gate# Test to see that the special case of a constant WHERE clause is
2557c478bd9Sstevel@tonic-gate# handled.
2567c478bd9Sstevel@tonic-gate#
2577c478bd9Sstevel@tonic-gatedo_test where-4.1 {
2587c478bd9Sstevel@tonic-gate  count {
2597c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE 0
2607c478bd9Sstevel@tonic-gate  }
2617c478bd9Sstevel@tonic-gate} {0}
2627c478bd9Sstevel@tonic-gatedo_test where-4.2 {
2637c478bd9Sstevel@tonic-gate  count {
2647c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE 1 LIMIT 1
2657c478bd9Sstevel@tonic-gate  }
2667c478bd9Sstevel@tonic-gate} {1 0 4 1}
2677c478bd9Sstevel@tonic-gatedo_test where-4.3 {
2687c478bd9Sstevel@tonic-gate  execsql {
2697c478bd9Sstevel@tonic-gate    SELECT 99 WHERE 0
2707c478bd9Sstevel@tonic-gate  }
2717c478bd9Sstevel@tonic-gate} {}
2727c478bd9Sstevel@tonic-gatedo_test where-4.4 {
2737c478bd9Sstevel@tonic-gate  execsql {
2747c478bd9Sstevel@tonic-gate    SELECT 99 WHERE 1
2757c478bd9Sstevel@tonic-gate  }
2767c478bd9Sstevel@tonic-gate} {99}
2777c478bd9Sstevel@tonic-gate
2787c478bd9Sstevel@tonic-gate# Verify that IN operators in a WHERE clause are handled correctly.
2797c478bd9Sstevel@tonic-gate#
2807c478bd9Sstevel@tonic-gatedo_test where-5.1 {
2817c478bd9Sstevel@tonic-gate  count {
2827c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
2837c478bd9Sstevel@tonic-gate  }
2847c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 0}
2857c478bd9Sstevel@tonic-gatedo_test where-5.2 {
2867c478bd9Sstevel@tonic-gate  count {
2877c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
2887c478bd9Sstevel@tonic-gate  }
2897c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 199}
2907c478bd9Sstevel@tonic-gatedo_test where-5.3 {
2917c478bd9Sstevel@tonic-gate  count {
2927c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
2937c478bd9Sstevel@tonic-gate  }
2947c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 10}
2957c478bd9Sstevel@tonic-gatedo_test where-5.4 {
2967c478bd9Sstevel@tonic-gate  count {
2977c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
2987c478bd9Sstevel@tonic-gate  }
2997c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 199}
3007c478bd9Sstevel@tonic-gatedo_test where-5.5 {
3017c478bd9Sstevel@tonic-gate  count {
302*1da57d55SToomas Soome    SELECT * FROM t1 WHERE rowid IN
3037c478bd9Sstevel@tonic-gate       (select rowid from t1 where rowid IN (-1,2,4))
3047c478bd9Sstevel@tonic-gate    ORDER BY 1;
3057c478bd9Sstevel@tonic-gate  }
3067c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 1}
3077c478bd9Sstevel@tonic-gatedo_test where-5.6 {
3087c478bd9Sstevel@tonic-gate  count {
309*1da57d55SToomas Soome    SELECT * FROM t1 WHERE rowid+0 IN
3107c478bd9Sstevel@tonic-gate       (select rowid from t1 where rowid IN (-1,2,4))
3117c478bd9Sstevel@tonic-gate    ORDER BY 1;
3127c478bd9Sstevel@tonic-gate  }
3137c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 199}
3147c478bd9Sstevel@tonic-gatedo_test where-5.7 {
3157c478bd9Sstevel@tonic-gate  count {
316*1da57d55SToomas Soome    SELECT * FROM t1 WHERE w IN
3177c478bd9Sstevel@tonic-gate       (select rowid from t1 where rowid IN (-1,2,4))
3187c478bd9Sstevel@tonic-gate    ORDER BY 1;
3197c478bd9Sstevel@tonic-gate  }
3207c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 7}
3217c478bd9Sstevel@tonic-gatedo_test where-5.8 {
3227c478bd9Sstevel@tonic-gate  count {
323*1da57d55SToomas Soome    SELECT * FROM t1 WHERE w+0 IN
3247c478bd9Sstevel@tonic-gate       (select rowid from t1 where rowid IN (-1,2,4))
3257c478bd9Sstevel@tonic-gate    ORDER BY 1;
3267c478bd9Sstevel@tonic-gate  }
3277c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 199}
3287c478bd9Sstevel@tonic-gatedo_test where-5.9 {
3297c478bd9Sstevel@tonic-gate  count {
3307c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
3317c478bd9Sstevel@tonic-gate  }
3327c478bd9Sstevel@tonic-gate} {2 1 9 3 1 16 6}
3337c478bd9Sstevel@tonic-gatedo_test where-5.10 {
3347c478bd9Sstevel@tonic-gate  count {
3357c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
3367c478bd9Sstevel@tonic-gate  }
3377c478bd9Sstevel@tonic-gate} {2 1 9 3 1 16 199}
3387c478bd9Sstevel@tonic-gatedo_test where-5.11 {
3397c478bd9Sstevel@tonic-gate  count {
3407c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
3417c478bd9Sstevel@tonic-gate  }
3427c478bd9Sstevel@tonic-gate} {79 6 6400 89 6 8100 199}
3437c478bd9Sstevel@tonic-gatedo_test where-5.12 {
3447c478bd9Sstevel@tonic-gate  count {
3457c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
3467c478bd9Sstevel@tonic-gate  }
3477c478bd9Sstevel@tonic-gate} {79 6 6400 89 6 8100 74}
3487c478bd9Sstevel@tonic-gatedo_test where-5.13 {
3497c478bd9Sstevel@tonic-gate  count {
3507c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
3517c478bd9Sstevel@tonic-gate  }
3527c478bd9Sstevel@tonic-gate} {2 1 9 3 1 16 6}
3537c478bd9Sstevel@tonic-gatedo_test where-5.14 {
3547c478bd9Sstevel@tonic-gate  count {
3557c478bd9Sstevel@tonic-gate    SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
3567c478bd9Sstevel@tonic-gate  }
3577c478bd9Sstevel@tonic-gate} {2 1 9 6}
3587c478bd9Sstevel@tonic-gate
3597c478bd9Sstevel@tonic-gate# This procedure executes the SQL.  Then it checks the generated program
3607c478bd9Sstevel@tonic-gate# for the SQL and appends a "nosort" to the result if the program contains the
3617c478bd9Sstevel@tonic-gate# SortCallback opcode.  If the program does not contain the SortCallback
3627c478bd9Sstevel@tonic-gate# opcode it appends "sort"
3637c478bd9Sstevel@tonic-gate#
3647c478bd9Sstevel@tonic-gateproc cksort {sql} {
3657c478bd9Sstevel@tonic-gate  set data [execsql $sql]
3667c478bd9Sstevel@tonic-gate  set prog [execsql "EXPLAIN $sql"]
3677c478bd9Sstevel@tonic-gate  if {[regexp SortCallback $prog]} {set x sort} {set x nosort}
3687c478bd9Sstevel@tonic-gate  lappend data $x
3697c478bd9Sstevel@tonic-gate  return $data
3707c478bd9Sstevel@tonic-gate}
3717c478bd9Sstevel@tonic-gate# Check out the logic that attempts to implement the ORDER BY clause
3727c478bd9Sstevel@tonic-gate# using an index rather than by sorting.
3737c478bd9Sstevel@tonic-gate#
3747c478bd9Sstevel@tonic-gatedo_test where-6.1 {
3757c478bd9Sstevel@tonic-gate  execsql {
3767c478bd9Sstevel@tonic-gate    CREATE TABLE t3(a,b,c);
3777c478bd9Sstevel@tonic-gate    CREATE INDEX t3a ON t3(a);
3787c478bd9Sstevel@tonic-gate    CREATE INDEX t3bc ON t3(b,c);
3797c478bd9Sstevel@tonic-gate    CREATE INDEX t3acb ON t3(a,c,b);
3807c478bd9Sstevel@tonic-gate    INSERT INTO t3 SELECT w, 101-w, y FROM t1;
3817c478bd9Sstevel@tonic-gate    SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
3827c478bd9Sstevel@tonic-gate  }
3837c478bd9Sstevel@tonic-gate} {100 5050 5050 348550}
3847c478bd9Sstevel@tonic-gatedo_test where-6.2 {
3857c478bd9Sstevel@tonic-gate  cksort {
3867c478bd9Sstevel@tonic-gate    SELECT * FROM t3 ORDER BY a LIMIT 3
3877c478bd9Sstevel@tonic-gate  }
3887c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort}
3897c478bd9Sstevel@tonic-gatedo_test where-6.3 {
3907c478bd9Sstevel@tonic-gate  cksort {
3917c478bd9Sstevel@tonic-gate    SELECT * FROM t3 ORDER BY a+1 LIMIT 3
3927c478bd9Sstevel@tonic-gate  }
3937c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 sort}
3947c478bd9Sstevel@tonic-gatedo_test where-6.4 {
3957c478bd9Sstevel@tonic-gate  cksort {
3967c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
3977c478bd9Sstevel@tonic-gate  }
3987c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort}
3997c478bd9Sstevel@tonic-gatedo_test where-6.5 {
4007c478bd9Sstevel@tonic-gate  cksort {
4017c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
4027c478bd9Sstevel@tonic-gate  }
4037c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort}
4047c478bd9Sstevel@tonic-gatedo_test where-6.6 {
4057c478bd9Sstevel@tonic-gate  cksort {
4067c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
4077c478bd9Sstevel@tonic-gate  }
4087c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort}
4097c478bd9Sstevel@tonic-gatedo_test where-6.7 {
4107c478bd9Sstevel@tonic-gate  cksort {
4117c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
4127c478bd9Sstevel@tonic-gate  }
4137c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 sort}
4147c478bd9Sstevel@tonic-gatedo_test where-6.8 {
4157c478bd9Sstevel@tonic-gate  cksort {
4167c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
4177c478bd9Sstevel@tonic-gate  }
4187c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 sort}
4197c478bd9Sstevel@tonic-gatedo_test where-6.9.1 {
4207c478bd9Sstevel@tonic-gate  cksort {
4217c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
4227c478bd9Sstevel@tonic-gate  }
4237c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4247c478bd9Sstevel@tonic-gatedo_test where-6.9.2 {
4257c478bd9Sstevel@tonic-gate  cksort {
4267c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
4277c478bd9Sstevel@tonic-gate  }
4287c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4297c478bd9Sstevel@tonic-gatedo_test where-6.9.3 {
4307c478bd9Sstevel@tonic-gate  cksort {
4317c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
4327c478bd9Sstevel@tonic-gate  }
4337c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4347c478bd9Sstevel@tonic-gatedo_test where-6.9.4 {
4357c478bd9Sstevel@tonic-gate  cksort {
4367c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
4377c478bd9Sstevel@tonic-gate  }
4387c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4397c478bd9Sstevel@tonic-gatedo_test where-6.9.5 {
4407c478bd9Sstevel@tonic-gate  cksort {
4417c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
4427c478bd9Sstevel@tonic-gate  }
4437c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4447c478bd9Sstevel@tonic-gatedo_test where-6.9.6 {
4457c478bd9Sstevel@tonic-gate  cksort {
4467c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
4477c478bd9Sstevel@tonic-gate  }
4487c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4497c478bd9Sstevel@tonic-gatedo_test where-6.9.7 {
4507c478bd9Sstevel@tonic-gate  cksort {
4517c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
4527c478bd9Sstevel@tonic-gate  }
4537c478bd9Sstevel@tonic-gate} {1 100 4 sort}
4547c478bd9Sstevel@tonic-gatedo_test where-6.9.8 {
4557c478bd9Sstevel@tonic-gate  cksort {
4567c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
4577c478bd9Sstevel@tonic-gate  }
4587c478bd9Sstevel@tonic-gate} {1 100 4 sort}
4597c478bd9Sstevel@tonic-gatedo_test where-6.9.9 {
4607c478bd9Sstevel@tonic-gate  cksort {
4617c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
4627c478bd9Sstevel@tonic-gate  }
4637c478bd9Sstevel@tonic-gate} {1 100 4 sort}
4647c478bd9Sstevel@tonic-gatedo_test where-6.10 {
4657c478bd9Sstevel@tonic-gate  cksort {
4667c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
4677c478bd9Sstevel@tonic-gate  }
4687c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4697c478bd9Sstevel@tonic-gatedo_test where-6.11 {
4707c478bd9Sstevel@tonic-gate  cksort {
4717c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
4727c478bd9Sstevel@tonic-gate  }
4737c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4747c478bd9Sstevel@tonic-gatedo_test where-6.12 {
4757c478bd9Sstevel@tonic-gate  cksort {
4767c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
4777c478bd9Sstevel@tonic-gate  }
4787c478bd9Sstevel@tonic-gate} {1 100 4 nosort}
4797c478bd9Sstevel@tonic-gatedo_test where-6.13 {
4807c478bd9Sstevel@tonic-gate  cksort {
4817c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
4827c478bd9Sstevel@tonic-gate  }
4837c478bd9Sstevel@tonic-gate} {100 1 10201 99 2 10000 98 3 9801 nosort}
4847c478bd9Sstevel@tonic-gatedo_test where-6.13.1 {
4857c478bd9Sstevel@tonic-gate  cksort {
4867c478bd9Sstevel@tonic-gate    SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
4877c478bd9Sstevel@tonic-gate  }
4887c478bd9Sstevel@tonic-gate} {100 1 10201 99 2 10000 98 3 9801 sort}
4897c478bd9Sstevel@tonic-gatedo_test where-6.14 {
4907c478bd9Sstevel@tonic-gate  cksort {
4917c478bd9Sstevel@tonic-gate    SELECT * FROM t3 ORDER BY b LIMIT 3
4927c478bd9Sstevel@tonic-gate  }
4937c478bd9Sstevel@tonic-gate} {100 1 10201 99 2 10000 98 3 9801 nosort}
4947c478bd9Sstevel@tonic-gatedo_test where-6.15 {
4957c478bd9Sstevel@tonic-gate  cksort {
4967c478bd9Sstevel@tonic-gate    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
4977c478bd9Sstevel@tonic-gate  }
4987c478bd9Sstevel@tonic-gate} {1 0 2 1 3 1 nosort}
4997c478bd9Sstevel@tonic-gatedo_test where-6.16 {
5007c478bd9Sstevel@tonic-gate  cksort {
5017c478bd9Sstevel@tonic-gate    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
5027c478bd9Sstevel@tonic-gate  }
5037c478bd9Sstevel@tonic-gate} {1 0 2 1 3 1 sort}
5047c478bd9Sstevel@tonic-gatedo_test where-6.17 {
5057c478bd9Sstevel@tonic-gate  cksort {
5067c478bd9Sstevel@tonic-gate    SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;
5077c478bd9Sstevel@tonic-gate  }
5087c478bd9Sstevel@tonic-gate} {4 121 10201 sort}
5097c478bd9Sstevel@tonic-gatedo_test where-6.18 {
5107c478bd9Sstevel@tonic-gate  cksort {
5117c478bd9Sstevel@tonic-gate    SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;
5127c478bd9Sstevel@tonic-gate  }
5137c478bd9Sstevel@tonic-gate} {4 9 16 sort}
5147c478bd9Sstevel@tonic-gatedo_test where-6.19 {
5157c478bd9Sstevel@tonic-gate  cksort {
5167c478bd9Sstevel@tonic-gate    SELECT y FROM t1 ORDER BY w LIMIT 3;
5177c478bd9Sstevel@tonic-gate  }
5187c478bd9Sstevel@tonic-gate} {4 9 16 nosort}
5197c478bd9Sstevel@tonic-gate
5207c478bd9Sstevel@tonic-gate# Tests for reverse-order sorting.
5217c478bd9Sstevel@tonic-gate#
5227c478bd9Sstevel@tonic-gatedo_test where-7.1 {
5237c478bd9Sstevel@tonic-gate  cksort {
5247c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 ORDER BY y;
5257c478bd9Sstevel@tonic-gate  }
5267c478bd9Sstevel@tonic-gate} {8 9 10 11 12 13 14 15 nosort}
5277c478bd9Sstevel@tonic-gatedo_test where-7.2 {
5287c478bd9Sstevel@tonic-gate  cksort {
5297c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
5307c478bd9Sstevel@tonic-gate  }
5317c478bd9Sstevel@tonic-gate} {15 14 13 12 11 10 9 8 nosort}
5327c478bd9Sstevel@tonic-gatedo_test where-7.3 {
5337c478bd9Sstevel@tonic-gate  cksort {
5347c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
5357c478bd9Sstevel@tonic-gate  }
5367c478bd9Sstevel@tonic-gate} {10 11 12 nosort}
5377c478bd9Sstevel@tonic-gatedo_test where-7.4 {
5387c478bd9Sstevel@tonic-gate  cksort {
5397c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
5407c478bd9Sstevel@tonic-gate  }
5417c478bd9Sstevel@tonic-gate} {15 14 13 nosort}
5427c478bd9Sstevel@tonic-gatedo_test where-7.5 {
5437c478bd9Sstevel@tonic-gate  cksort {
5447c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
5457c478bd9Sstevel@tonic-gate  }
5467c478bd9Sstevel@tonic-gate} {15 14 13 12 11 nosort}
5477c478bd9Sstevel@tonic-gatedo_test where-7.6 {
5487c478bd9Sstevel@tonic-gate  cksort {
5497c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
5507c478bd9Sstevel@tonic-gate  }
5517c478bd9Sstevel@tonic-gate} {15 14 13 12 11 10 nosort}
5527c478bd9Sstevel@tonic-gatedo_test where-7.7 {
5537c478bd9Sstevel@tonic-gate  cksort {
5547c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
5557c478bd9Sstevel@tonic-gate  }
5567c478bd9Sstevel@tonic-gate} {12 11 10 nosort}
5577c478bd9Sstevel@tonic-gatedo_test where-7.8 {
5587c478bd9Sstevel@tonic-gate  cksort {
5597c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
5607c478bd9Sstevel@tonic-gate  }
5617c478bd9Sstevel@tonic-gate} {13 12 11 10 nosort}
5627c478bd9Sstevel@tonic-gatedo_test where-7.9 {
5637c478bd9Sstevel@tonic-gate  cksort {
5647c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
5657c478bd9Sstevel@tonic-gate  }
5667c478bd9Sstevel@tonic-gate} {13 12 11 nosort}
5677c478bd9Sstevel@tonic-gatedo_test where-7.10 {
5687c478bd9Sstevel@tonic-gate  cksort {
5697c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
5707c478bd9Sstevel@tonic-gate  }
5717c478bd9Sstevel@tonic-gate} {12 11 10 nosort}
5727c478bd9Sstevel@tonic-gatedo_test where-7.11 {
5737c478bd9Sstevel@tonic-gate  cksort {
5747c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
5757c478bd9Sstevel@tonic-gate  }
5767c478bd9Sstevel@tonic-gate} {10 11 12 nosort}
5777c478bd9Sstevel@tonic-gatedo_test where-7.12 {
5787c478bd9Sstevel@tonic-gate  cksort {
5797c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
5807c478bd9Sstevel@tonic-gate  }
5817c478bd9Sstevel@tonic-gate} {10 11 12 13 nosort}
5827c478bd9Sstevel@tonic-gatedo_test where-7.13 {
5837c478bd9Sstevel@tonic-gate  cksort {
5847c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
5857c478bd9Sstevel@tonic-gate  }
5867c478bd9Sstevel@tonic-gate} {11 12 13 nosort}
5877c478bd9Sstevel@tonic-gatedo_test where-7.14 {
5887c478bd9Sstevel@tonic-gate  cksort {
5897c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
5907c478bd9Sstevel@tonic-gate  }
5917c478bd9Sstevel@tonic-gate} {10 11 12 nosort}
5927c478bd9Sstevel@tonic-gatedo_test where-7.15 {
5937c478bd9Sstevel@tonic-gate  cksort {
5947c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
5957c478bd9Sstevel@tonic-gate  }
5967c478bd9Sstevel@tonic-gate} {nosort}
5977c478bd9Sstevel@tonic-gatedo_test where-7.16 {
5987c478bd9Sstevel@tonic-gate  cksort {
5997c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
6007c478bd9Sstevel@tonic-gate  }
6017c478bd9Sstevel@tonic-gate} {8 nosort}
6027c478bd9Sstevel@tonic-gatedo_test where-7.17 {
6037c478bd9Sstevel@tonic-gate  cksort {
6047c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
6057c478bd9Sstevel@tonic-gate  }
6067c478bd9Sstevel@tonic-gate} {nosort}
6077c478bd9Sstevel@tonic-gatedo_test where-7.18 {
6087c478bd9Sstevel@tonic-gate  cksort {
6097c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
6107c478bd9Sstevel@tonic-gate  }
6117c478bd9Sstevel@tonic-gate} {15 nosort}
6127c478bd9Sstevel@tonic-gatedo_test where-7.19 {
6137c478bd9Sstevel@tonic-gate  cksort {
6147c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
6157c478bd9Sstevel@tonic-gate  }
6167c478bd9Sstevel@tonic-gate} {nosort}
6177c478bd9Sstevel@tonic-gatedo_test where-7.20 {
6187c478bd9Sstevel@tonic-gate  cksort {
6197c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
6207c478bd9Sstevel@tonic-gate  }
6217c478bd9Sstevel@tonic-gate} {8 nosort}
6227c478bd9Sstevel@tonic-gatedo_test where-7.21 {
6237c478bd9Sstevel@tonic-gate  cksort {
6247c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
6257c478bd9Sstevel@tonic-gate  }
6267c478bd9Sstevel@tonic-gate} {nosort}
6277c478bd9Sstevel@tonic-gatedo_test where-7.22 {
6287c478bd9Sstevel@tonic-gate  cksort {
6297c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
6307c478bd9Sstevel@tonic-gate  }
6317c478bd9Sstevel@tonic-gate} {15 nosort}
6327c478bd9Sstevel@tonic-gatedo_test where-7.23 {
6337c478bd9Sstevel@tonic-gate  cksort {
6347c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
6357c478bd9Sstevel@tonic-gate  }
6367c478bd9Sstevel@tonic-gate} {nosort}
6377c478bd9Sstevel@tonic-gatedo_test where-7.24 {
6387c478bd9Sstevel@tonic-gate  cksort {
6397c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
6407c478bd9Sstevel@tonic-gate  }
6417c478bd9Sstevel@tonic-gate} {1 nosort}
6427c478bd9Sstevel@tonic-gatedo_test where-7.25 {
6437c478bd9Sstevel@tonic-gate  cksort {
6447c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
6457c478bd9Sstevel@tonic-gate  }
6467c478bd9Sstevel@tonic-gate} {nosort}
6477c478bd9Sstevel@tonic-gatedo_test where-7.26 {
6487c478bd9Sstevel@tonic-gate  cksort {
6497c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
6507c478bd9Sstevel@tonic-gate  }
6517c478bd9Sstevel@tonic-gate} {100 nosort}
6527c478bd9Sstevel@tonic-gatedo_test where-7.27 {
6537c478bd9Sstevel@tonic-gate  cksort {
6547c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
6557c478bd9Sstevel@tonic-gate  }
6567c478bd9Sstevel@tonic-gate} {nosort}
6577c478bd9Sstevel@tonic-gatedo_test where-7.28 {
6587c478bd9Sstevel@tonic-gate  cksort {
6597c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
6607c478bd9Sstevel@tonic-gate  }
6617c478bd9Sstevel@tonic-gate} {1 nosort}
6627c478bd9Sstevel@tonic-gatedo_test where-7.29 {
6637c478bd9Sstevel@tonic-gate  cksort {
6647c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
6657c478bd9Sstevel@tonic-gate  }
6667c478bd9Sstevel@tonic-gate} {nosort}
6677c478bd9Sstevel@tonic-gatedo_test where-7.30 {
6687c478bd9Sstevel@tonic-gate  cksort {
6697c478bd9Sstevel@tonic-gate    SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
6707c478bd9Sstevel@tonic-gate  }
6717c478bd9Sstevel@tonic-gate} {100 nosort}
6727c478bd9Sstevel@tonic-gate
6737c478bd9Sstevel@tonic-gatedo_test where-8.1 {
6747c478bd9Sstevel@tonic-gate  execsql {
6757c478bd9Sstevel@tonic-gate    CREATE TABLE t4 AS SELECT * FROM t1;
6767c478bd9Sstevel@tonic-gate    CREATE INDEX i4xy ON t4(x,y);
6777c478bd9Sstevel@tonic-gate  }
6787c478bd9Sstevel@tonic-gate  cksort {
6797c478bd9Sstevel@tonic-gate    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
6807c478bd9Sstevel@tonic-gate  }
6817c478bd9Sstevel@tonic-gate} {30 29 28 nosort}
6827c478bd9Sstevel@tonic-gatedo_test where-8.2 {
6837c478bd9Sstevel@tonic-gate  execsql {
6847c478bd9Sstevel@tonic-gate    DELETE FROM t4;
6857c478bd9Sstevel@tonic-gate  }
6867c478bd9Sstevel@tonic-gate  cksort {
6877c478bd9Sstevel@tonic-gate    SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
6887c478bd9Sstevel@tonic-gate  }
6897c478bd9Sstevel@tonic-gate} {nosort}
6907c478bd9Sstevel@tonic-gate
6917c478bd9Sstevel@tonic-gate# Make sure searches with an index work with an empty table.
6927c478bd9Sstevel@tonic-gate#
6937c478bd9Sstevel@tonic-gatedo_test where-9.1 {
6947c478bd9Sstevel@tonic-gate  execsql {
6957c478bd9Sstevel@tonic-gate    CREATE TABLE t5(x PRIMARY KEY);
6967c478bd9Sstevel@tonic-gate    SELECT * FROM t5 WHERE x<10;
6977c478bd9Sstevel@tonic-gate  }
6987c478bd9Sstevel@tonic-gate} {}
6997c478bd9Sstevel@tonic-gatedo_test where-9.2 {
7007c478bd9Sstevel@tonic-gate  execsql {
7017c478bd9Sstevel@tonic-gate    SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
7027c478bd9Sstevel@tonic-gate  }
7037c478bd9Sstevel@tonic-gate} {}
7047c478bd9Sstevel@tonic-gatedo_test where-9.3 {
7057c478bd9Sstevel@tonic-gate  execsql {
7067c478bd9Sstevel@tonic-gate    SELECT * FROM t5 WHERE x=10;
7077c478bd9Sstevel@tonic-gate  }
7087c478bd9Sstevel@tonic-gate} {}
7097c478bd9Sstevel@tonic-gate
7107c478bd9Sstevel@tonic-gatedo_test where-10.1 {
7117c478bd9Sstevel@tonic-gate  execsql {
7127c478bd9Sstevel@tonic-gate    SELECT 1 WHERE abs(random())<0
7137c478bd9Sstevel@tonic-gate  }
7147c478bd9Sstevel@tonic-gate} {}
7157c478bd9Sstevel@tonic-gatedo_test where-10.2 {
7167c478bd9Sstevel@tonic-gate  proc tclvar_func {vname} {return [set ::$vname]}
7177c478bd9Sstevel@tonic-gate  db function tclvar tclvar_func
7187c478bd9Sstevel@tonic-gate  set ::v1 0
7197c478bd9Sstevel@tonic-gate  execsql {
7207c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t1 WHERE tclvar('v1');
7217c478bd9Sstevel@tonic-gate  }
7227c478bd9Sstevel@tonic-gate} {0}
7237c478bd9Sstevel@tonic-gatedo_test where-10.3 {
7247c478bd9Sstevel@tonic-gate  set ::v1 1
7257c478bd9Sstevel@tonic-gate  execsql {
7267c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t1 WHERE tclvar('v1');
7277c478bd9Sstevel@tonic-gate  }
7287c478bd9Sstevel@tonic-gate} {100}
7297c478bd9Sstevel@tonic-gatedo_test where-10.4 {
7307c478bd9Sstevel@tonic-gate  set ::v1 1
7317c478bd9Sstevel@tonic-gate  proc tclvar_func {vname} {
7327c478bd9Sstevel@tonic-gate    upvar #0 $vname v
7337c478bd9Sstevel@tonic-gate    set v [expr {!$v}]
7347c478bd9Sstevel@tonic-gate    return $v
7357c478bd9Sstevel@tonic-gate  }
7367c478bd9Sstevel@tonic-gate  execsql {
7377c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t1 WHERE tclvar('v1');
7387c478bd9Sstevel@tonic-gate  }
7397c478bd9Sstevel@tonic-gate} {50}
7407c478bd9Sstevel@tonic-gate
7417c478bd9Sstevel@tonic-gateintegrity_check {where-99.0}
7427c478bd9Sstevel@tonic-gate
7437c478bd9Sstevel@tonic-gatefinish_test
744