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