xref: /titanic_52/usr/src/lib/libsqlite/test/notnull.test (revision b6c3f7863936abeae522e48a13887dddeb691a45) 
1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
4# 2002 January 29
5#
6# The author disclaims copyright to this source code.  In place of
7# a legal notice, here is a blessing:
8#
9#    May you do good and not evil.
10#    May you find forgiveness for yourself and forgive others.
11#    May you share freely, never taking more than you give.
12#
13#***********************************************************************
14# This file implements regression tests for SQLite library.
15#
16# This file implements tests for the NOT NULL constraint.
17#
18# $Id: notnull.test,v 1.3 2003/01/29 18:46:54 drh Exp $
19
20set testdir [file dirname $argv0]
21source $testdir/tester.tcl
22
23do_test notnull-1.0 {
24  execsql {
25    CREATE TABLE t1 (
26      a NOT NULL,
27      b NOT NULL DEFAULT 5,
28      c NOT NULL ON CONFLICT REPLACE DEFAULT 6,
29      d NOT NULL ON CONFLICT IGNORE DEFAULT 7,
30      e NOT NULL ON CONFLICT ABORT DEFAULT 8
31    );
32    SELECT * FROM t1;
33  }
34} {}
35do_test notnull-1.1 {
36  catchsql {
37    DELETE FROM t1;
38    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
39    SELECT * FROM t1 order by a;
40  }
41} {0 {1 2 3 4 5}}
42do_test notnull-1.2 {
43  catchsql {
44    DELETE FROM t1;
45    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
46    SELECT * FROM t1 order by a;
47  }
48} {1 {t1.a may not be NULL}}
49do_test notnull-1.3 {
50  catchsql {
51    DELETE FROM t1;
52    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
53    SELECT * FROM t1 order by a;
54  }
55} {0 {}}
56do_test notnull-1.4 {
57  catchsql {
58    DELETE FROM t1;
59    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
60    SELECT * FROM t1 order by a;
61  }
62} {1 {t1.a may not be NULL}}
63do_test notnull-1.5 {
64  catchsql {
65    DELETE FROM t1;
66    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
67    SELECT * FROM t1 order by a;
68  }
69} {1 {t1.a may not be NULL}}
70do_test notnull-1.6 {
71  catchsql {
72    DELETE FROM t1;
73    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
74    SELECT * FROM t1 order by a;
75  }
76} {0 {1 5 3 4 5}}
77do_test notnull-1.7 {
78  catchsql {
79    DELETE FROM t1;
80    INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);
81    SELECT * FROM t1 order by a;
82  }
83} {0 {1 5 3 4 5}}
84do_test notnull-1.8 {
85  catchsql {
86    DELETE FROM t1;
87    INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);
88    SELECT * FROM t1 order by a;
89  }
90} {0 {1 5 3 4 5}}
91do_test notnull-1.9 {
92  catchsql {
93    DELETE FROM t1;
94    INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);
95    SELECT * FROM t1 order by a;
96  }
97} {0 {1 5 3 4 5}}
98do_test notnull-1.10 {
99  catchsql {
100    DELETE FROM t1;
101    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
102    SELECT * FROM t1 order by a;
103  }
104} {1 {t1.b may not be NULL}}
105do_test notnull-1.11 {
106  catchsql {
107    DELETE FROM t1;
108    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
109    SELECT * FROM t1 order by a;
110  }
111} {0 {}}
112do_test notnull-1.12 {
113  catchsql {
114    DELETE FROM t1;
115    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
116    SELECT * FROM t1 order by a;
117  }
118} {0 {1 5 3 4 5}}
119do_test notnull-1.13 {
120  catchsql {
121    DELETE FROM t1;
122    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
123    SELECT * FROM t1 order by a;
124  }
125} {0 {1 2 6 4 5}}
126do_test notnull-1.14 {
127  catchsql {
128    DELETE FROM t1;
129    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
130    SELECT * FROM t1 order by a;
131  }
132} {0 {}}
133do_test notnull-1.15 {
134  catchsql {
135    DELETE FROM t1;
136    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
137    SELECT * FROM t1 order by a;
138  }
139} {0 {1 2 6 4 5}}
140do_test notnull-1.16 {
141  catchsql {
142    DELETE FROM t1;
143    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
144    SELECT * FROM t1 order by a;
145  }
146} {1 {t1.c may not be NULL}}
147do_test notnull-1.17 {
148  catchsql {
149    DELETE FROM t1;
150    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
151    SELECT * FROM t1 order by a;
152  }
153} {1 {t1.d may not be NULL}}
154do_test notnull-1.18 {
155  catchsql {
156    DELETE FROM t1;
157    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
158    SELECT * FROM t1 order by a;
159  }
160} {0 {1 2 3 7 5}}
161do_test notnull-1.19 {
162  catchsql {
163    DELETE FROM t1;
164    INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
165    SELECT * FROM t1 order by a;
166  }
167} {0 {1 2 3 4 8}}
168do_test notnull-1.20 {
169  catchsql {
170    DELETE FROM t1;
171    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
172    SELECT * FROM t1 order by a;
173  }
174} {1 {t1.e may not be NULL}}
175do_test notnull-1.21 {
176  catchsql {
177    DELETE FROM t1;
178    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
179    SELECT * FROM t1 order by a;
180  }
181} {0 {5 5 3 2 1}}
182
183do_test notnull-2.1 {
184  catchsql {
185    DELETE FROM t1;
186    INSERT INTO t1 VALUES(1,2,3,4,5);
187    UPDATE t1 SET a=null;
188    SELECT * FROM t1 ORDER BY a;
189  }
190} {1 {t1.a may not be NULL}}
191do_test notnull-2.2 {
192  catchsql {
193    DELETE FROM t1;
194    INSERT INTO t1 VALUES(1,2,3,4,5);
195    UPDATE OR REPLACE t1 SET a=null;
196    SELECT * FROM t1 ORDER BY a;
197  }
198} {1 {t1.a may not be NULL}}
199do_test notnull-2.3 {
200  catchsql {
201    DELETE FROM t1;
202    INSERT INTO t1 VALUES(1,2,3,4,5);
203    UPDATE OR IGNORE t1 SET a=null;
204    SELECT * FROM t1 ORDER BY a;
205  }
206} {0 {1 2 3 4 5}}
207do_test notnull-2.4 {
208  catchsql {
209    DELETE FROM t1;
210    INSERT INTO t1 VALUES(1,2,3,4,5);
211    UPDATE OR ABORT t1 SET a=null;
212    SELECT * FROM t1 ORDER BY a;
213  }
214} {1 {t1.a may not be NULL}}
215do_test notnull-2.5 {
216  catchsql {
217    DELETE FROM t1;
218    INSERT INTO t1 VALUES(1,2,3,4,5);
219    UPDATE t1 SET b=null;
220    SELECT * FROM t1 ORDER BY a;
221  }
222} {1 {t1.b may not be NULL}}
223do_test notnull-2.6 {
224  catchsql {
225    DELETE FROM t1;
226    INSERT INTO t1 VALUES(1,2,3,4,5);
227    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
228    SELECT * FROM t1 ORDER BY a;
229  }
230} {0 {1 5 3 5 4}}
231do_test notnull-2.7 {
232  catchsql {
233    DELETE FROM t1;
234    INSERT INTO t1 VALUES(1,2,3,4,5);
235    UPDATE OR IGNORE t1 SET b=null, d=e, e=d;
236    SELECT * FROM t1 ORDER BY a;
237  }
238} {0 {1 2 3 4 5}}
239do_test notnull-2.8 {
240  catchsql {
241    DELETE FROM t1;
242    INSERT INTO t1 VALUES(1,2,3,4,5);
243    UPDATE t1 SET c=null, d=e, e=d;
244    SELECT * FROM t1 ORDER BY a;
245  }
246} {0 {1 2 6 5 4}}
247do_test notnull-2.9 {
248  catchsql {
249    DELETE FROM t1;
250    INSERT INTO t1 VALUES(1,2,3,4,5);
251    UPDATE t1 SET d=null, a=b, b=a;
252    SELECT * FROM t1 ORDER BY a;
253  }
254} {0 {1 2 3 4 5}}
255do_test notnull-2.10 {
256  catchsql {
257    DELETE FROM t1;
258    INSERT INTO t1 VALUES(1,2,3,4,5);
259    UPDATE t1 SET e=null, a=b, b=a;
260    SELECT * FROM t1 ORDER BY a;
261  }
262} {1 {t1.e may not be NULL}}
263
264do_test notnull-3.0 {
265  execsql {
266    CREATE INDEX t1a ON t1(a);
267    CREATE INDEX t1b ON t1(b);
268    CREATE INDEX t1c ON t1(c);
269    CREATE INDEX t1d ON t1(d);
270    CREATE INDEX t1e ON t1(e);
271    CREATE INDEX t1abc ON t1(a,b,c);
272  }
273} {}
274do_test notnull-3.1 {
275  catchsql {
276    DELETE FROM t1;
277    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
278    SELECT * FROM t1 order by a;
279  }
280} {0 {1 2 3 4 5}}
281do_test notnull-3.2 {
282  catchsql {
283    DELETE FROM t1;
284    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
285    SELECT * FROM t1 order by a;
286  }
287} {1 {t1.a may not be NULL}}
288do_test notnull-3.3 {
289  catchsql {
290    DELETE FROM t1;
291    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
292    SELECT * FROM t1 order by a;
293  }
294} {0 {}}
295do_test notnull-3.4 {
296  catchsql {
297    DELETE FROM t1;
298    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
299    SELECT * FROM t1 order by a;
300  }
301} {1 {t1.a may not be NULL}}
302do_test notnull-3.5 {
303  catchsql {
304    DELETE FROM t1;
305    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
306    SELECT * FROM t1 order by a;
307  }
308} {1 {t1.a may not be NULL}}
309do_test notnull-3.6 {
310  catchsql {
311    DELETE FROM t1;
312    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
313    SELECT * FROM t1 order by a;
314  }
315} {0 {1 5 3 4 5}}
316do_test notnull-3.7 {
317  catchsql {
318    DELETE FROM t1;
319    INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);
320    SELECT * FROM t1 order by a;
321  }
322} {0 {1 5 3 4 5}}
323do_test notnull-3.8 {
324  catchsql {
325    DELETE FROM t1;
326    INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);
327    SELECT * FROM t1 order by a;
328  }
329} {0 {1 5 3 4 5}}
330do_test notnull-3.9 {
331  catchsql {
332    DELETE FROM t1;
333    INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);
334    SELECT * FROM t1 order by a;
335  }
336} {0 {1 5 3 4 5}}
337do_test notnull-3.10 {
338  catchsql {
339    DELETE FROM t1;
340    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
341    SELECT * FROM t1 order by a;
342  }
343} {1 {t1.b may not be NULL}}
344do_test notnull-3.11 {
345  catchsql {
346    DELETE FROM t1;
347    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
348    SELECT * FROM t1 order by a;
349  }
350} {0 {}}
351do_test notnull-3.12 {
352  catchsql {
353    DELETE FROM t1;
354    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
355    SELECT * FROM t1 order by a;
356  }
357} {0 {1 5 3 4 5}}
358do_test notnull-3.13 {
359  catchsql {
360    DELETE FROM t1;
361    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
362    SELECT * FROM t1 order by a;
363  }
364} {0 {1 2 6 4 5}}
365do_test notnull-3.14 {
366  catchsql {
367    DELETE FROM t1;
368    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
369    SELECT * FROM t1 order by a;
370  }
371} {0 {}}
372do_test notnull-3.15 {
373  catchsql {
374    DELETE FROM t1;
375    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
376    SELECT * FROM t1 order by a;
377  }
378} {0 {1 2 6 4 5}}
379do_test notnull-3.16 {
380  catchsql {
381    DELETE FROM t1;
382    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
383    SELECT * FROM t1 order by a;
384  }
385} {1 {t1.c may not be NULL}}
386do_test notnull-3.17 {
387  catchsql {
388    DELETE FROM t1;
389    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
390    SELECT * FROM t1 order by a;
391  }
392} {1 {t1.d may not be NULL}}
393do_test notnull-3.18 {
394  catchsql {
395    DELETE FROM t1;
396    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
397    SELECT * FROM t1 order by a;
398  }
399} {0 {1 2 3 7 5}}
400do_test notnull-3.19 {
401  catchsql {
402    DELETE FROM t1;
403    INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
404    SELECT * FROM t1 order by a;
405  }
406} {0 {1 2 3 4 8}}
407do_test notnull-3.20 {
408  catchsql {
409    DELETE FROM t1;
410    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
411    SELECT * FROM t1 order by a;
412  }
413} {1 {t1.e may not be NULL}}
414do_test notnull-3.21 {
415  catchsql {
416    DELETE FROM t1;
417    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
418    SELECT * FROM t1 order by a;
419  }
420} {0 {5 5 3 2 1}}
421
422do_test notnull-4.1 {
423  catchsql {
424    DELETE FROM t1;
425    INSERT INTO t1 VALUES(1,2,3,4,5);
426    UPDATE t1 SET a=null;
427    SELECT * FROM t1 ORDER BY a;
428  }
429} {1 {t1.a may not be NULL}}
430do_test notnull-4.2 {
431  catchsql {
432    DELETE FROM t1;
433    INSERT INTO t1 VALUES(1,2,3,4,5);
434    UPDATE OR REPLACE t1 SET a=null;
435    SELECT * FROM t1 ORDER BY a;
436  }
437} {1 {t1.a may not be NULL}}
438do_test notnull-4.3 {
439  catchsql {
440    DELETE FROM t1;
441    INSERT INTO t1 VALUES(1,2,3,4,5);
442    UPDATE OR IGNORE t1 SET a=null;
443    SELECT * FROM t1 ORDER BY a;
444  }
445} {0 {1 2 3 4 5}}
446do_test notnull-4.4 {
447  catchsql {
448    DELETE FROM t1;
449    INSERT INTO t1 VALUES(1,2,3,4,5);
450    UPDATE OR ABORT t1 SET a=null;
451    SELECT * FROM t1 ORDER BY a;
452  }
453} {1 {t1.a may not be NULL}}
454do_test notnull-4.5 {
455  catchsql {
456    DELETE FROM t1;
457    INSERT INTO t1 VALUES(1,2,3,4,5);
458    UPDATE t1 SET b=null;
459    SELECT * FROM t1 ORDER BY a;
460  }
461} {1 {t1.b may not be NULL}}
462do_test notnull-4.6 {
463  catchsql {
464    DELETE FROM t1;
465    INSERT INTO t1 VALUES(1,2,3,4,5);
466    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
467    SELECT * FROM t1 ORDER BY a;
468  }
469} {0 {1 5 3 5 4}}
470do_test notnull-4.7 {
471  catchsql {
472    DELETE FROM t1;
473    INSERT INTO t1 VALUES(1,2,3,4,5);
474    UPDATE OR IGNORE t1 SET b=null, d=e, e=d;
475    SELECT * FROM t1 ORDER BY a;
476  }
477} {0 {1 2 3 4 5}}
478do_test notnull-4.8 {
479  catchsql {
480    DELETE FROM t1;
481    INSERT INTO t1 VALUES(1,2,3,4,5);
482    UPDATE t1 SET c=null, d=e, e=d;
483    SELECT * FROM t1 ORDER BY a;
484  }
485} {0 {1 2 6 5 4}}
486do_test notnull-4.9 {
487  catchsql {
488    DELETE FROM t1;
489    INSERT INTO t1 VALUES(1,2,3,4,5);
490    UPDATE t1 SET d=null, a=b, b=a;
491    SELECT * FROM t1 ORDER BY a;
492  }
493} {0 {1 2 3 4 5}}
494do_test notnull-4.10 {
495  catchsql {
496    DELETE FROM t1;
497    INSERT INTO t1 VALUES(1,2,3,4,5);
498    UPDATE t1 SET e=null, a=b, b=a;
499    SELECT * FROM t1 ORDER BY a;
500  }
501} {1 {t1.e may not be NULL}}
502
503finish_test
504