aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/libraries/sqlite/unix/sqlite-3.5.1/test/notnull.test
diff options
context:
space:
mode:
Diffstat (limited to 'libraries/sqlite/unix/sqlite-3.5.1/test/notnull.test')
-rw-r--r--libraries/sqlite/unix/sqlite-3.5.1/test/notnull.test505
1 files changed, 505 insertions, 0 deletions
diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/notnull.test b/libraries/sqlite/unix/sqlite-3.5.1/test/notnull.test
new file mode 100644
index 0000000..5af9940
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/notnull.test
@@ -0,0 +1,505 @@
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
505finish_test
generated by cgit v1.1 at 2025-04-25 23:49:08 +0000