wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2008 September 1
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 # $Id: in4.test,v 1.4 2009/06/05 17:09:12 drh Exp $
13  
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16  
17 do_test in4-1.1 {
18 execsql {
19 CREATE TABLE t1(a, b);
20 CREATE INDEX i1 ON t1(a);
21 }
22 } {}
23 do_test in4-1.2 {
24 execsql {
25 SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc');
26 }
27 } {}
28 do_test in4-1.3 {
29 execsql {
30 INSERT INTO t1 VALUES('aaa', 1);
31 INSERT INTO t1 VALUES('ddd', 2);
32 INSERT INTO t1 VALUES('ccc', 3);
33 INSERT INTO t1 VALUES('eee', 4);
34 SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc');
35 }
36 } {1 3}
37 do_test in4-1.4 {
38 execsql {
39 SELECT a FROM t1 WHERE rowid IN (1, 3);
40 }
41 } {aaa ccc}
42 do_test in4-1.5 {
43 execsql {
44 SELECT a FROM t1 WHERE rowid IN ();
45 }
46 } {}
47 do_test in4-1.6 {
48 execsql {
49 SELECT a FROM t1 WHERE a IN ('ddd');
50 }
51 } {ddd}
52  
53 do_test in4-2.1 {
54 execsql {
55 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
56 INSERT INTO t2 VALUES(-1, '-one');
57 INSERT INTO t2 VALUES(0, 'zero');
58 INSERT INTO t2 VALUES(1, 'one');
59 INSERT INTO t2 VALUES(2, 'two');
60 INSERT INTO t2 VALUES(3, 'three');
61 }
62 } {}
63  
64 do_test in4-2.2 {
65 execsql { SELECT b FROM t2 WHERE a IN (0, 2) }
66 } {zero two}
67  
68 do_test in4-2.3 {
69 execsql { SELECT b FROM t2 WHERE a IN (2, 0) }
70 } {zero two}
71  
72 do_test in4-2.4 {
73 execsql { SELECT b FROM t2 WHERE a IN (2, -1) }
74 } {-one two}
75  
76 do_test in4-2.5 {
77 execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) }
78 } {three}
79  
80 do_test in4-2.6 {
81 execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) }
82 } {one}
83  
84 do_test in4-2.7 {
85 execsql { SELECT b FROM t2 WHERE a IN ('1', '2') }
86 } {one two}
87  
88 do_test in4-2.8 {
89 execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') }
90 } {two}
91  
92 # The following block of tests test expressions of the form:
93 #
94 # <expr> IN ()
95 #
96 # i.e. IN expressions with a literal empty set.
97 #
98 # This has led to crashes on more than one occasion. Test case in4-3.2
99 # was added in reponse to a bug reported on the mailing list on 11/7/2008.
100 # See also tickets #3602 and #185.
101 #
102 do_test in4-3.1 {
103 execsql {
104 DROP TABLE IF EXISTS t1;
105 DROP TABLE IF EXISTS t2;
106 CREATE TABLE t1(x, id);
107 CREATE TABLE t2(x, id);
108 INSERT INTO t1 VALUES(NULL, NULL);
109 INSERT INTO t1 VALUES(0, NULL);
110 INSERT INTO t1 VALUES(1, 3);
111 INSERT INTO t1 VALUES(2, 4);
112 INSERT INTO t1 VALUES(3, 5);
113 INSERT INTO t1 VALUES(4, 6);
114 INSERT INTO t2 VALUES(0, NULL);
115 INSERT INTO t2 VALUES(4, 1);
116 INSERT INTO t2 VALUES(NULL, 1);
117 INSERT INTO t2 VALUES(NULL, NULL);
118 }
119 } {}
120 do_test in4-3.2 {
121 execsql {
122 SELECT x FROM t1 WHERE id IN () AND x IN (SELECT x FROM t2 WHERE id=1)
123 }
124 } {}
125 do_test in4-3.3 {
126 execsql {
127 CREATE TABLE t3(x, y, z);
128 CREATE INDEX t3i1 ON t3(x, y);
129 INSERT INTO t3 VALUES(1, 1, 1);
130 INSERT INTO t3 VALUES(10, 10, 10);
131 }
132 execsql { SELECT * FROM t3 WHERE x IN () }
133 } {}
134 do_test in4-3.4 {
135 execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () }
136 } {}
137 do_test in4-3.5 {
138 execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 }
139 } {}
140 do_test in4-3.6 {
141 execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 }
142 } {10 10 10}
143 do_test in4-3.7 {
144 execsql { SELECT * FROM t3 WHERE y IN () }
145 } {}
146 do_test in4-3.8 {
147 execsql { SELECT x IN() AS a FROM t3 WHERE a }
148 } {}
149 do_test in4-3.9 {
150 execsql { SELECT x IN() AS a FROM t3 WHERE NOT a }
151 } {0 0}
152 do_test in4-3.10 {
153 execsql { SELECT * FROM t3 WHERE oid IN () }
154 } {}
155 do_test in4-3.11 {
156 execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
157 } {1 1 1}
158 do_test in4-3.12 {
159 execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
160 } {}
161  
162 finish_test