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