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