Debian Codegraph @

test/check.test

raw
1 # 2005 November 2
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. The
12 # focus of this file is testing CHECK constraints
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set ::testprefix check
18
19 # Only run these tests if the build includes support for CHECK constraints
20 ifcapable !check {
21 finish_test
22 return
23 }
24 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1
25 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
26
27 do_test check-1.1 {
28 execsql {
29 CREATE TABLE t1(
30 x INTEGER CHECK( x<5 ),
31 y REAL CHECK( y>x )
32 );
33 }
34 } {}
35 do_test check-1.2 {
36 execsql {
37 INSERT INTO t1 VALUES(3,4);
38 SELECT * FROM t1;
39 }
40 } {3 4.0}
41 do_test check-1.3 {
42 catchsql {
43 INSERT INTO t1 VALUES(6,7);
44 }
45 } {1 {CHECK constraint failed: x<5}}
46 do_test check-1.4 {
47 execsql {
48 SELECT * FROM t1;
49 }
50 } {3 4.0}
51 do_test check-1.5 {
52 catchsql {
53 INSERT INTO t1 VALUES(4,3);
54 }
55 } {1 {CHECK constraint failed: y>x}}
56 do_test check-1.6 {
57 execsql {
58 SELECT * FROM t1;
59 }
60 } {3 4.0}
61 do_test check-1.7 {
62 catchsql {
63 INSERT INTO t1 VALUES(NULL,6);
64 }
65 } {0 {}}
66 do_test check-1.8 {
67 execsql {
68 SELECT * FROM t1;
69 }
70 } {3 4.0 {} 6.0}
71 do_test check-1.9 {
72 catchsql {
73 INSERT INTO t1 VALUES(2,NULL);
74 }
75 } {0 {}}
76 do_test check-1.10 {
77 execsql {
78 SELECT * FROM t1;
79 }
80 } {3 4.0 {} 6.0 2 {}}
81 do_test check-1.11 {
82 execsql {
83 DELETE FROM t1 WHERE x IS NULL OR x!=3;
84 UPDATE t1 SET x=2 WHERE x==3;
85 SELECT * FROM t1;
86 }
87 } {2 4.0}
88 do_test check-1.12 {
89 catchsql {
90 UPDATE t1 SET x=7 WHERE x==2
91 }
92 } {1 {CHECK constraint failed: x<5}}
93 do_test check-1.13 {
94 execsql {
95 SELECT * FROM t1;
96 }
97 } {2 4.0}
98 do_test check-1.14 {
99 catchsql {
100 UPDATE t1 SET x=5 WHERE x==2
101 }
102 } {1 {CHECK constraint failed: x<5}}
103 do_test check-1.15 {
104 execsql {
105 SELECT * FROM t1;
106 }
107 } {2 4.0}
108 do_test check-1.16 {
109 catchsql {
110 UPDATE t1 SET x=4, y=11 WHERE x==2
111 }
112 } {0 {}}
113 do_test check-1.17 {
114 execsql {
115 SELECT * FROM t1;
116 }
117 } {4 11.0}
118
119 do_test check-2.1 {
120 execsql {
121 PRAGMA writable_schema = 1;
122 CREATE TABLE t2(
123 x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
124 y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
125 z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
126 );
127 CREATE TABLE t2n(
128 x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
129 y NUMERIC CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
130 z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
131 );
132 PRAGMA writable_schema = 0;
133 }
134 } {}
135 do_test check-2.2 {
136 execsql {
137 INSERT INTO t2 VALUES(1,2.2,'three');
138 SELECT * FROM t2;
139 }
140 } {1 2.2 three}
141 db close
142 sqlite3 db test.db
143 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1
144 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
145 do_test check-2.3 {
146 execsql {
147 INSERT INTO t2 VALUES(NULL, NULL, NULL);
148 SELECT * FROM t2;
149 }
150 } {1 2.2 three {} {} {}}
151 do_test check-2.4 {
152 catchsql {
153 INSERT INTO t2 VALUES(1.1, NULL, NULL);
154 }
155 } {1 {CHECK constraint failed: one}}
156 do_test check-2.5 {
157 # The 5 gets automatically promoted to 5.0 because the column type is REAL
158 catchsql {
159 INSERT INTO t2 VALUES(NULL, 5, NULL);
160 }
161 } {0 {}}
162 do_test check-2.5b {
163 # This time the column type is NUMERIC, so not automatic promption to REAL
164 # occurs and the constraint fails.
165 catchsql {
166 INSERT INTO t2n VALUES(NULL, 5, NULL);
167 }
168 } {1 {CHECK constraint failed: two}}
169 do_test check-2.6 {
170 catchsql {
171 INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
172 }
173 } {0 {}}
174
175 # Undocumented behavior: The CONSTRAINT name clause can follow a constraint.
176 # Such a clause is ignored. But the parser must accept it for backwards
177 # compatibility.
178 #
179 do_test check-2.10 {
180 execsql {
181 CREATE TABLE t2b(
182 x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one,
183 y TEXT PRIMARY KEY constraint two,
184 z INTEGER,
185 UNIQUE(x,z) constraint three
186 );
187 }
188 } {}
189 do_test check-2.11 {
190 catchsql {
191 INSERT INTO t2b VALUES('xyzzy','hi',5);
192 }
193 } {1 {CHECK constraint failed: typeof(coalesce(x,0))=='integer'}}
194 do_test check-2.12 {
195 execsql {
196 CREATE TABLE t2c(
197 x INTEGER CONSTRAINT x_one CONSTRAINT x_two
198 CHECK( typeof(coalesce(x,0))=='integer' )
199 CONSTRAINT x_two CONSTRAINT x_three,
200 y INTEGER, z INTEGER,
201 CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two
202 );
203 }
204 } {}
205 do_test check-2.13 {
206 catchsql {
207 INSERT INTO t2c VALUES('xyzzy',7,8);
208 }
209 } {1 {CHECK constraint failed: x_two}}
210 do_test check-2.cleanup {
211 execsql {
212 DROP TABLE IF EXISTS t2b;
213 DROP TABLE IF EXISTS t2c;
214 DROP TABLE IF EXISTS t2n;
215 }
216 } {}
217
218 ifcapable subquery {
219 do_test check-3.1 {
220 catchsql {
221 CREATE TABLE t3(
222 x, y, z,
223 CHECK( x<(SELECT min(x) FROM t1) )
224 );
225 }
226 } {1 {subqueries prohibited in CHECK constraints}}
227 }
228
229 do_test check-3.2 {
230 execsql {
231 SELECT name FROM sqlite_master ORDER BY name
232 }
233 } {t1 t2}
234 do_test check-3.3 {
235 catchsql {
236 CREATE TABLE t3(
237 x, y, z,
238 CHECK( q<x )
239 );
240 }
241 } {1 {no such column: q}}
242 do_test check-3.4 {
243 execsql {
244 SELECT name FROM sqlite_master ORDER BY name
245 }
246 } {t1 t2}
247 do_test check-3.5 {
248 catchsql {
249 CREATE TABLE t3(
250 x, y, z,
251 CHECK( t2.x<x )
252 );
253 }
254 } {1 {no such column: t2.x}}
255 do_test check-3.6 {
256 execsql {
257 SELECT name FROM sqlite_master ORDER BY name
258 }
259 } {t1 t2}
260 do_test check-3.7 {
261 catchsql {
262 CREATE TABLE t3(
263 x, y, z,
264 CHECK( t3.x<25 )
265 );
266 }
267 } {0 {}}
268 do_test check-3.8 {
269 execsql {
270 INSERT INTO t3 VALUES(1,2,3);
271 SELECT * FROM t3;
272 }
273 } {1 2 3}
274 do_test check-3.9 {
275 catchsql {
276 INSERT INTO t3 VALUES(111,222,333);
277 }
278 } {1 {CHECK constraint failed: t3.x<25}}
279
280 do_test check-4.1 {
281 execsql {
282 CREATE TABLE t4(x, y,
283 CHECK (
284 x+y==11
285 OR x*y==12
286 OR x/y BETWEEN 5 AND 8
287 OR -x==y+10
288 )
289 );
290 }
291 } {}
292 do_test check-4.2 {
293 execsql {
294 INSERT INTO t4 VALUES(1,10);
295 SELECT * FROM t4
296 }
297 } {1 10}
298 do_test check-4.3 {
299 execsql {
300 UPDATE t4 SET x=4, y=3;
301 SELECT * FROM t4
302 }
303 } {4 3}
304 do_test check-4.4 {
305 execsql {
306 UPDATE t4 SET x=12, y=2;
307 SELECT * FROM t4
308 }
309 } {12 2}
310 do_test check-4.5 {
311 execsql {
312 UPDATE t4 SET x=12, y=-22;
313 SELECT * FROM t4
314 }
315 } {12 -22}
316 do_test check-4.6 {
317 catchsql {
318 UPDATE t4 SET x=0, y=1;
319 }
320 } {1 {CHECK constraint failed: x+y==11
321 OR x*y==12
322 OR x/y BETWEEN 5 AND 8
323 OR -x==y+10}}
324 do_test check-4.7 {
325 execsql {
326 SELECT * FROM t4;
327 }
328 } {12 -22}
329 do_test check-4.8 {
330 execsql {
331 PRAGMA ignore_check_constraints=ON;
332 UPDATE t4 SET x=0, y=1;
333 SELECT * FROM t4;
334 PRAGMA integrity_check;
335 }
336 } {0 1 ok}
337 do_execsql_test check-4.8.1 {
338 PRAGMA ignore_check_constraints=OFF;
339 PRAGMA integrity_check;
340 } {{CHECK constraint failed in t4}}
341 do_test check-4.9 {
342 catchsql {
343 UPDATE t4 SET x=0, y=2;
344 }
345 } {1 {CHECK constraint failed: x+y==11
346 OR x*y==12
347 OR x/y BETWEEN 5 AND 8
348 OR -x==y+10}}
349 ifcapable vacuum {
350 do_test check_4.10 {
351 catchsql {
352 VACUUM
353 }
354 } {0 {}}
355 }
356
357 do_test check-5.1 {
358 catchsql {
359 CREATE TABLE t5(x, y,
360 CHECK( x*y<:abc )
361 );
362 }
363 } {1 {parameters prohibited in CHECK constraints}}
364 do_test check-5.2 {
365 catchsql {
366 CREATE TABLE t5(x, y,
367 CHECK( x*y<? )
368 );
369 }
370 } {1 {parameters prohibited in CHECK constraints}}
371
372 ifcapable conflict {
373
374 do_test check-6.1 {
375 execsql {SELECT * FROM t1}
376 } {4 11.0}
377 do_test check-6.2 {
378 execsql {
379 UPDATE OR IGNORE t1 SET x=5;
380 SELECT * FROM t1;
381 }
382 } {4 11.0}
383 do_test check-6.3 {
384 execsql {
385 INSERT OR IGNORE INTO t1 VALUES(5,4.0);
386 SELECT * FROM t1;
387 }
388 } {4 11.0}
389 do_test check-6.4 {
390 execsql {
391 INSERT OR IGNORE INTO t1 VALUES(2,20.0);
392 SELECT * FROM t1;
393 }
394 } {4 11.0 2 20.0}
395 do_test check-6.5 {
396 catchsql {
397 UPDATE OR FAIL t1 SET x=7-x, y=y+1;
398 }
399 } {1 {CHECK constraint failed: x<5}}
400 do_test check-6.6 {
401 execsql {
402 SELECT * FROM t1;
403 }
404 } {3 12.0 2 20.0}
405 do_test check-6.7 {
406 catchsql {
407 BEGIN;
408 INSERT INTO t1 VALUES(1,30.0);
409 INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);
410 }
411 } {1 {CHECK constraint failed: x<5}}
412 do_test check-6.8 {
413 catchsql {
414 COMMIT;
415 }
416 } {1 {cannot commit - no transaction is active}}
417 do_test check-6.9 {
418 execsql {
419 SELECT * FROM t1
420 }
421 } {3 12.0 2 20.0}
422
423 do_test check-6.11 {
424 execsql {SELECT * FROM t1}
425 } {3 12.0 2 20.0}
426 do_test check-6.12 {
427 catchsql {
428 REPLACE INTO t1 VALUES(6,7);
429 }
430 } {1 {CHECK constraint failed: x<5}}
431 do_test check-6.13 {
432 execsql {SELECT * FROM t1}
433 } {3 12.0 2 20.0}
434 do_test check-6.14 {
435 catchsql {
436 INSERT OR IGNORE INTO t1 VALUES(6,7);
437 }
438 } {0 {}}
439 do_test check-6.15 {
440 execsql {SELECT * FROM t1}
441 } {3 12.0 2 20.0}
442
443
444 }
445
446 #--------------------------------------------------------------------------
447 # If a connection opens a database that contains a CHECK constraint that
448 # uses an unknown UDF, the schema should not be considered malformed.
449 # Attempting to modify the table should fail (since the CHECK constraint
450 # cannot be tested).
451 #
452 reset_db
453 proc myfunc {x} {expr $x < 10}
454 db func myfunc -deterministic myfunc
455
456 do_execsql_test 7.1 { CREATE TABLE t6(a CHECK (myfunc(a))) }
457 do_execsql_test 7.2 { INSERT INTO t6 VALUES(9) }
458 do_catchsql_test 7.3 { INSERT INTO t6 VALUES(11) } \
459 {1 {CHECK constraint failed: myfunc(a)}}
460
461 do_test 7.4 {
462 sqlite3 db2 test.db
463 execsql { SELECT * FROM t6 } db2
464 } {9}
465
466 do_test 7.5 {
467 catchsql { INSERT INTO t6 VALUES(8) } db2
468 } {1 {unknown function: myfunc()}}
469
470 do_test 7.6 {
471 catchsql { CREATE TABLE t7(a CHECK (myfunc(a))) } db2
472 } {1 {no such function: myfunc}}
473
474 do_test 7.7 {
475 db2 func myfunc myfunc
476 execsql { INSERT INTO t6 VALUES(8) } db2
477 } {}
478
479 do_test 7.8 {
480 db2 func myfunc myfunc
481 catchsql { INSERT INTO t6 VALUES(12) } db2
482 } {1 {CHECK constraint failed: myfunc(a)}}
483
484 # 2013-08-02: Silently ignore database name qualifiers in CHECK constraints.
485 #
486 do_execsql_test 8.1 {
487 CREATE TABLE t810(a, CHECK( main.t810.a>0 ));
488 CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 ));
489 } {}
490
491 # Make sure check constraints involving the ROWID are not ignored
492 #
493 do_execsql_test 9.1 {
494 CREATE TABLE t1(
495 a INTEGER PRIMARY KEY,
496 b INTEGER NOT NULL CONSTRAINT 'b-check' CHECK( b>a ),
497 c INTEGER NOT NULL CONSTRAINT 'c-check' CHECK( c>rowid*2 ),
498 d INTEGER NOT NULL CONSTRAINT 'd-check' CHECK( d BETWEEN b AND c )
499 );
500 INSERT INTO t1(a,b,c,d) VALUES(1,2,4,3),(2,4,6,5),(3,10,30,20);
501 } {}
502 do_catchsql_test 9.2 {
503 UPDATE t1 SET b=0 WHERE a=1;
504 } {1 {CHECK constraint failed: b-check}}
505 do_catchsql_test 9.3 {
506 UPDATE t1 SET c=a*2 WHERE a=1;
507 } {1 {CHECK constraint failed: c-check}}
508
509 # Integrity check on a VIEW with columns.
510 #
511 db close
512 db2 close
513 forcedelete test.db
514 sqlite3 db test.db
515 do_execsql_test 10.1 {
516 CREATE TABLE t1(x);
517 CREATE VIEW v1(y) AS SELECT x FROM t1;
518 PRAGMA integrity_check;
519 } {ok}
520
521 #-------------------------------------------------------------------------
522 reset_db
523 do_execsql_test 11.0 {
524 CREATE TABLE t1 (Col0 CHECK(1 COLLATE BINARY BETWEEN 1 AND 1) ) ;
525 }
526 do_execsql_test 11.1 {
527 INSERT INTO t1 VALUES (NULL);
528 }
529 do_execsql_test 11.2 {
530 INSERT INTO t1 VALUES (NULL);
531 }
532
533 do_execsql_test 11.3 {
534 CREATE TABLE t2(b, a CHECK(
535 CASE 'abc' COLLATE nocase WHEN a THEN 1 ELSE 0 END)
536 );
537 }
538 do_execsql_test 11.4 {
539 INSERT INTO t2(a) VALUES('abc');
540 }
541 do_execsql_test 11.5 {
542 INSERT INTO t2(b, a) VALUES(1, 'abc'||'');
543 }
544 do_execsql_test 11.6 {
545 INSERT INTO t2(b, a) VALUES(2, 'abc');
546 }
547
548 # 2019-12-24 ticket b383b90278186263
549 #
550 reset_db
551 do_execsql_test 12.10 {
552 CREATE TABLE t1(a TEXT, CHECK(a=+a));
553 INSERT INTO t1(a) VALUES(NULL),('xyz'),(5),(x'303132'),(4.75);
554 SELECT quote(a) FROM t1 ORDER BY rowid;
555 } {NULL 'xyz' '5' X'303132' '4.75'}
556 do_execsql_test 12.20 {
557 DROP TABLE t1;
558 CREATE TABLE t1(a TEXT, CHECK(a<>+a));
559 INSERT INTO t1(a) VALUES(NULL);
560 } {}
561 do_catchsql_test 12.21 {
562 INSERT INTO t1(a) VALUES('xyz');
563 } {1 {CHECK constraint failed: a<>+a}}
564 do_catchsql_test 12.22 {
565 INSERT INTO t1(a) VALUES(123);
566 } {1 {CHECK constraint failed: a<>+a}}
567 do_execsql_test 12.30 {
568 DROP TABLE t1;
569 CREATE TABLE t1(a TEXT, CHECK(NOT(a=+a)));
570 INSERT INTO t1(a) VALUES(NULL);
571 } {}
572 do_catchsql_test 12.31 {
573 INSERT INTO t1(a) VALUES('xyz');
574 } {1 {CHECK constraint failed: NOT(a=+a)}}
575 do_catchsql_test 12.32 {
576 INSERT INTO t1(a) VALUES(123);
577 } {1 {CHECK constraint failed: NOT(a=+a)}}
578 do_execsql_test 12.40 {
579 DROP TABLE t1;
580 CREATE TABLE t1(a TEXT, CHECK(NOT(a<>+a)));
581 INSERT INTO t1(a) VALUES(NULL),('xyz'),(5),(x'303132'),(4.75);
582 SELECT quote(a) FROM t1 ORDER BY rowid;
583 } {NULL 'xyz' '5' X'303132' '4.75'}
584 do_execsql_test 12.50 {
585 DROP TABLE t1;
586 CREATE TABLE t1(a TEXT, CHECK(a BETWEEN 0 AND +a));
587 INSERT INTO t1(a) VALUES(NULL),('xyz'),(5),(x'303132'),(4.75);
588 SELECT quote(a) FROM t1 ORDER BY rowid;
589 } {NULL 'xyz' '5' X'303132' '4.75'}
590 do_execsql_test 12.60 {
591 DROP TABLE t1;
592 CREATE TABLE t1(a TEXT, CHECK(a NOT BETWEEN 0 AND +a));
593 INSERT INTO t1(a) VALUES(NULL);
594 SELECT quote(a) FROM t1 ORDER BY rowid;
595 } {NULL}
596 do_catchsql_test 12.61 {
597 INSERT INTO t1(a) VALUES(456);
598 } {1 {CHECK constraint failed: a NOT BETWEEN 0 AND +a}}
599 do_execsql_test 12.70 {
600 DROP TABLE t1;
601 CREATE TABLE t1(a TEXT, CHECK(a BETWEEN +a AND 999999));
602 INSERT INTO t1(a) VALUES(NULL),(5);
603 SELECT quote(a) FROM t1 ORDER BY rowid;
604 } {NULL '5'}
605 do_execsql_test 12.80 {
606 DROP TABLE t1;
607 CREATE TABLE t1(a TEXT, CHECK(a NOT BETWEEN +a AND 999999));
608 INSERT INTO t1(a) VALUES(NULL);
609 SELECT quote(a) FROM t1 ORDER BY rowid;
610 } {NULL}
611 do_catchsql_test 12.81 {
612 INSERT INTO t1(a) VALUES(456);
613 } {1 {CHECK constraint failed: a NOT BETWEEN +a AND 999999}}
614
615 #-------------------------------------------------------------------------
616
617 reset_db
618
619 do_execsql_test 13.1.0 {
620 CREATE TABLE Table0 (Col0 , CHECK(Table0.Col0 NOT NULL ) ) ;
621 REPLACE INTO Table0 VALUES (hex(randomblob(100000)));
622 }
623 integrity_check 13.1.1
624 do_execsql_test 13.1.2 {
625 UPDATE OR REPLACE Table0 SET Col0 = Table0.Col0 ;
626 }
627 integrity_check 13.1.3
628 do_execsql_test 13.1.4 {
629 SELECT length(col0) FROM table0;
630 } {200000}
631
632 do_execsql_test 13.2.0 {
633 CREATE TABLE t2 (x , CHECK((NOT (x ISNULL) )));
634 REPLACE INTO t2 VALUES (hex(randomblob(100000)));
635 }
636 do_execsql_test 13.2.1 {
637 SELECT length(x) FROM t2
638 } {200000}
639 do_execsql_test 13.2.2 {
640 UPDATE OR REPLACE t2 SET x = x;
641 }
642 do_execsql_test 13.2.3 {
643 SELECT length(x) FROM t2
644 } {200000}
645 integrity_check 13.2.4
646
647 finish_test