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.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: ifcapable !conflict {
21: finish_test
22: return
23: }
24:
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}}
184:
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}}
265:
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}}
423:
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}}
504:
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}
537:
538: finish_test
539:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to notnull.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test