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