OLD | NEW |
| (Empty) |
1 # 2011 November 16 | |
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 # | |
12 | |
13 set testdir [file dirname $argv0] | |
14 source $testdir/tester.tcl | |
15 set testprefix whereC | |
16 | |
17 do_execsql_test 1.0 { | |
18 CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b INTEGER); | |
19 | |
20 INSERT INTO t1 VALUES(1, 1, 1); | |
21 INSERT INTO t1 VALUES(2, 1, 1); | |
22 INSERT INTO t1 VALUES(3, 1, 2); | |
23 INSERT INTO t1 VALUES(4, 1, 2); | |
24 INSERT INTO t1 VALUES(5, 1, 2); | |
25 INSERT INTO t1 VALUES(6, 1, 3); | |
26 INSERT INTO t1 VALUES(7, 1, 3); | |
27 | |
28 INSERT INTO t1 VALUES(8, 2, 1); | |
29 INSERT INTO t1 VALUES(9, 2, 1); | |
30 INSERT INTO t1 VALUES(10, 2, 2); | |
31 INSERT INTO t1 VALUES(11, 2, 2); | |
32 INSERT INTO t1 VALUES(12, 2, 2); | |
33 INSERT INTO t1 VALUES(13, 2, 3); | |
34 INSERT INTO t1 VALUES(14, 2, 3); | |
35 | |
36 INSERT INTO t1 VALUES(15, 2, 1); | |
37 INSERT INTO t1 VALUES(16, 2, 1); | |
38 INSERT INTO t1 VALUES(17, 2, 2); | |
39 INSERT INTO t1 VALUES(18, 2, 2); | |
40 INSERT INTO t1 VALUES(19, 2, 2); | |
41 INSERT INTO t1 VALUES(20, 2, 3); | |
42 INSERT INTO t1 VALUES(21, 2, 3); | |
43 | |
44 CREATE INDEX i1 ON t1(a, b); | |
45 } | |
46 | |
47 foreach {tn sql res} { | |
48 1 "SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3" {4 5} | |
49 2 "SELECT i FROM t1 WHERE rowid='12'" {12} | |
50 3 "SELECT i FROM t1 WHERE a=1 AND b='2'" {3 4 5} | |
51 4 "SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'" {4 5} | |
52 5 "SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5" {3 4} | |
53 6 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12" {10 11} | |
54 7 "SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11" {3 4 5 10} | |
55 8 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12" {10 11 12} | |
56 9 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 11 AND 12" {11 12} | |
57 10 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 11" {10 11} | |
58 11 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10" {} | |
59 12 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL" {} | |
60 13 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL" {} | |
61 14 "SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5" {3 4} | |
62 } { | |
63 do_execsql_test 1.$tn.1 $sql $res | |
64 do_execsql_test 1.$tn.2 "$sql ORDER BY i ASC" [lsort -integer -inc $res] | |
65 do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec $res] | |
66 } | |
67 | |
68 | |
69 finish_test | |
OLD | NEW |