| blob | 240aaba9d7eded5a5590bd423811ff5c65520931 |
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 $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 ifcapable !conflict {
21 finish_test
22 return
23 }
25 do_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 } {}
37 do_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}}
44 do_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}}
51 do_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 {}}
58 do_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}}
65 do_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}}
72 do_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}}
79 do_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}}
86 do_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}}
93 do_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}}
100 do_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}}
107 do_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 {}}
114 do_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}}
121 do_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}}
128 do_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 {}}
135 do_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}}
142 do_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}}
149 do_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}}
156 do_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}}
163 do_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}}
170 do_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}}
177 do_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}}
185 do_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}}
193 do_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}}
201 do_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}}
209 do_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}}
217 do_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}}
225 do_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}}
233 do_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}}
241 do_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}}
249 do_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}}
257 do_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}}
266 do_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 } {}
276 do_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}}
283 do_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}}
290 do_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 {}}
297 do_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}}
304 do_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}}
311 do_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}}
318 do_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}}
325 do_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}}
332 do_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}}
339 do_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}}
346 do_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 {}}
353 do_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}}
360 do_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}}
367 do_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 {}}
374 do_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}}
381 do_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}}
388 do_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}}
395 do_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}}
402 do_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}}
409 do_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}}
416 do_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}}
424 do_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}}
432 do_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}}
440 do_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}}
448 do_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}}
456 do_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}}
464 do_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}}
472 do_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}}
480 do_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}}
488 do_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}}
496 do_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}}
505 # Test that bug 29ab7be99f is fixed.
506 #
507 do_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 } {}
516 do_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}}
522 do_test notnull-5.3 {
523 execsql { SELECT * FROM t1 }
524 } {1 2}
525 do_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}}
534 do_test notnull-5.5 {
535 execsql { SELECT * FROM t1 }
536 } {1 2}
538 finish_test