| author | sl@SLION-WIN7.fritz.box |
| Fri, 15 Jun 2012 03:10:57 +0200 | |
| changeset 0 | bde4ae8d615e |
| permissions | -rw-r--r-- |
1 # 2007 June 8
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 NULL comparisons in the WHERE clause.
13 # See ticket #2404.
14 #
15 # $Id: where5.test,v 1.2 2007/06/08 08:43:10 drh Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Build some test data
21 #
22 do_test where5-1.0 {
23 execsql {
24 CREATE TABLE t1(x TEXT);
25 CREATE TABLE t2(x INTEGER);
26 CREATE TABLE t3(x INTEGER PRIMARY KEY);
27 INSERT INTO t1 VALUES(-1);
28 INSERT INTO t1 VALUES(0);
29 INSERT INTO t1 VALUES(1);
30 INSERT INTO t2 SELECT * FROM t1;
31 INSERT INTO t3 SELECT * FROM t2;
32 }
33 execsql {
34 SELECT * FROM t1 WHERE x<0
35 }
36 } {-1}
37 do_test where5-1.1 {
38 execsql {
39 SELECT * FROM t1 WHERE x<=0
40 }
41 } {-1 0}
42 do_test where5-1.2 {
43 execsql {
44 SELECT * FROM t1 WHERE x=0
45 }
46 } {0}
47 do_test where5-1.3 {
48 execsql {
49 SELECT * FROM t1 WHERE x>=0
50 }
51 } {0 1}
52 do_test where5-1.4 {
53 execsql {
54 SELECT * FROM t1 WHERE x>0
55 }
56 } {1}
57 do_test where5-1.5 {
58 execsql {
59 SELECT * FROM t1 WHERE x<>0
60 }
61 } {-1 1}
62 do_test where5-1.6 {
63 execsql {
64 SELECT * FROM t1 WHERE x<NULL
65 }
66 } {}
67 do_test where5-1.7 {
68 execsql {
69 SELECT * FROM t1 WHERE x<=NULL
70 }
71 } {}
72 do_test where5-1.8 {
73 execsql {
74 SELECT * FROM t1 WHERE x=NULL
75 }
76 } {}
77 do_test where5-1.9 {
78 execsql {
79 SELECT * FROM t1 WHERE x>=NULL
80 }
81 } {}
82 do_test where5-1.10 {
83 execsql {
84 SELECT * FROM t1 WHERE x>NULL
85 }
86 } {}
87 do_test where5-1.11 {
88 execsql {
89 SELECT * FROM t1 WHERE x!=NULL
90 }
91 } {}
92 do_test where5-1.12 {
93 execsql {
94 SELECT * FROM t1 WHERE x IS NULL
95 }
96 } {}
97 do_test where5-1.13 {
98 execsql {
99 SELECT * FROM t1 WHERE x IS NOT NULL
100 }
101 } {-1 0 1}
104 do_test where5-2.0 {
105 execsql {
106 SELECT * FROM t2 WHERE x<0
107 }
108 } {-1}
109 do_test where5-2.1 {
110 execsql {
111 SELECT * FROM t2 WHERE x<=0
112 }
113 } {-1 0}
114 do_test where5-2.2 {
115 execsql {
116 SELECT * FROM t2 WHERE x=0
117 }
118 } {0}
119 do_test where5-2.3 {
120 execsql {
121 SELECT * FROM t2 WHERE x>=0
122 }
123 } {0 1}
124 do_test where5-2.4 {
125 execsql {
126 SELECT * FROM t2 WHERE x>0
127 }
128 } {1}
129 do_test where5-2.5 {
130 execsql {
131 SELECT * FROM t2 WHERE x<>0
132 }
133 } {-1 1}
134 do_test where5-2.6 {
135 execsql {
136 SELECT * FROM t2 WHERE x<NULL
137 }
138 } {}
139 do_test where5-2.7 {
140 execsql {
141 SELECT * FROM t2 WHERE x<=NULL
142 }
143 } {}
144 do_test where5-2.8 {
145 execsql {
146 SELECT * FROM t2 WHERE x=NULL
147 }
148 } {}
149 do_test where5-2.9 {
150 execsql {
151 SELECT * FROM t2 WHERE x>=NULL
152 }
153 } {}
154 do_test where5-2.10 {
155 execsql {
156 SELECT * FROM t2 WHERE x>NULL
157 }
158 } {}
159 do_test where5-2.11 {
160 execsql {
161 SELECT * FROM t2 WHERE x!=NULL
162 }
163 } {}
164 do_test where5-2.12 {
165 execsql {
166 SELECT * FROM t2 WHERE x IS NULL
167 }
168 } {}
169 do_test where5-2.13 {
170 execsql {
171 SELECT * FROM t2 WHERE x IS NOT NULL
172 }
173 } {-1 0 1}
176 do_test where5-3.0 {
177 execsql {
178 SELECT * FROM t3 WHERE x<0
179 }
180 } {-1}
181 do_test where5-3.1 {
182 execsql {
183 SELECT * FROM t3 WHERE x<=0
184 }
185 } {-1 0}
186 do_test where5-3.2 {
187 execsql {
188 SELECT * FROM t3 WHERE x=0
189 }
190 } {0}
191 do_test where5-3.3 {
192 execsql {
193 SELECT * FROM t3 WHERE x>=0
194 }
195 } {0 1}
196 do_test where5-3.4 {
197 execsql {
198 SELECT * FROM t3 WHERE x>0
199 }
200 } {1}
201 do_test where5-3.5 {
202 execsql {
203 SELECT * FROM t3 WHERE x<>0
204 }
205 } {-1 1}
206 do_test where5-3.6 {
207 execsql {
208 SELECT * FROM t3 WHERE x<NULL
209 }
210 } {}
211 do_test where5-3.7 {
212 execsql {
213 SELECT * FROM t3 WHERE x<=NULL
214 }
215 } {}
216 do_test where5-3.8 {
217 execsql {
218 SELECT * FROM t3 WHERE x=NULL
219 }
220 } {}
221 do_test where5-3.9 {
222 execsql {
223 SELECT * FROM t3 WHERE x>=NULL
224 }
225 } {}
226 do_test where5-3.10 {
227 execsql {
228 SELECT * FROM t3 WHERE x>NULL
229 }
230 } {}
231 do_test where5-3.11 {
232 execsql {
233 SELECT * FROM t3 WHERE x!=NULL
234 }
235 } {}
236 do_test where5-3.12 {
237 execsql {
238 SELECT * FROM t3 WHERE x IS NULL
239 }
240 } {}
241 do_test where5-3.13 {
242 execsql {
243 SELECT * FROM t3 WHERE x IS NOT NULL
244 }
245 } {-1 0 1}
247 do_test where5-4.0 {
248 execsql {
249 SELECT x<NULL FROM t3
250 }
251 } {{} {} {}}
252 do_test where5-4.1 {
253 execsql {
254 SELECT x<=NULL FROM t3
255 }
256 } {{} {} {}}
257 do_test where5-4.2 {
258 execsql {
259 SELECT x==NULL FROM t3
260 }
261 } {{} {} {}}
262 do_test where5-4.3 {
263 execsql {
264 SELECT x>NULL FROM t3
265 }
266 } {{} {} {}}
267 do_test where5-4.4 {
268 execsql {
269 SELECT x>=NULL FROM t3
270 }
271 } {{} {} {}}
272 do_test where5-4.5 {
273 execsql {
274 SELECT x!=NULL FROM t3
275 }
276 } {{} {} {}}
277 do_test where5-4.6 {
278 execsql {
279 SELECT x IS NULL FROM t3
280 }
281 } {0 0 0}
282 do_test where5-4.7 {
283 execsql {
284 SELECT x IS NOT NULL FROM t3
285 }
286 } {1 1 1}
288 finish_test