AryaWu/sqlite
0
1# 2005 November 22#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing CHECK constraints13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set ::testprefix check18 19# Only run these tests if the build includes support for CHECK constraints20ifcapable !check {21 finish_test22 return23}24sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 125sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 126 27do_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} {}35do_test check-1.2 {36 execsql {37 INSERT INTO t1 VALUES(3,4);38 SELECT * FROM t1;39 } 40} {3 4.0}41do_test check-1.3 {42 catchsql {43 INSERT INTO t1 VALUES(6,7);44 }45} {1 {CHECK constraint failed: x<5}}46do_test check-1.4 {47 execsql {48 SELECT * FROM t1;49 } 50} {3 4.0}51do_test check-1.5 {52 catchsql {53 INSERT INTO t1 VALUES(4,3);54 }55} {1 {CHECK constraint failed: y>x}}56do_test check-1.6 {57 execsql {58 SELECT * FROM t1;59 } 60} {3 4.0}61do_test check-1.7 {62 catchsql {63 INSERT INTO t1 VALUES(NULL,6);64 }65} {0 {}}66do_test check-1.8 {67 execsql {68 SELECT * FROM t1;69 } 70} {3 4.0 {} 6.0}71do_test check-1.9 {72 catchsql {73 INSERT INTO t1 VALUES(2,NULL);74 }75} {0 {}}76do_test check-1.10 {77 execsql {78 SELECT * FROM t1;79 } 80} {3 4.0 {} 6.0 2 {}}81do_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}88do_test check-1.12 {89 catchsql {90 UPDATE t1 SET x=7 WHERE x==291 }92} {1 {CHECK constraint failed: x<5}}93do_test check-1.13 {94 execsql {95 SELECT * FROM t1;96 }97} {2 4.0}98do_test check-1.14 {99 catchsql {100 UPDATE t1 SET x=5 WHERE x==2101 }102} {1 {CHECK constraint failed: x<5}}103do_test check-1.15 {104 execsql {105 SELECT * FROM t1;106 }107} {2 4.0}108do_test check-1.16 {109 catchsql {110 UPDATE t1 SET x=4, y=11 WHERE x==2111 }112} {0 {}}113do_test check-1.17 {114 execsql {115 SELECT * FROM t1;116 }117} {4 11.0}118 119do_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} {}135do_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}141db close142sqlite3 db test.db143sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1144sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1145do_test check-2.3 {146 execsql {147 INSERT INTO t2 VALUES(NULL, NULL, NULL);148 SELECT * FROM t2;149 }150} {1 2.2 three {} {} {}}151do_test check-2.4 {152 catchsql {153 INSERT INTO t2 VALUES(1.1, NULL, NULL);154 }155} {1 {CHECK constraint failed: one}}156do_test check-2.5 {157 # The 5 gets automatically promoted to 5.0 because the column type is REAL158 catchsql {159 INSERT INTO t2 VALUES(NULL, 5, NULL);160 }161} {0 {}}162do_test check-2.5b {163 # This time the column type is NUMERIC, so not automatic promption to REAL164 # occurs and the constraint fails.165 catchsql {166 INSERT INTO t2n VALUES(NULL, 5, NULL);167 }168} {1 {CHECK constraint failed: two}}169do_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 backwards177# compatibility.178#179do_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 three186 );187 }188} {}189do_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'}}194do_test check-2.12 {195 execsql {196 CREATE TABLE t2c(197 x INTEGER CONSTRAINT x_one CONSTRAINT x_two198 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_two202 );203 }204} {}205do_test check-2.13 {206 catchsql {207 INSERT INTO t2c VALUES('xyzzy',7,8);208 }209} {1 {CHECK constraint failed: x_two}}210do_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 218ifcapable 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 229do_test check-3.2 {230 execsql {231 SELECT name FROM sqlite_master ORDER BY name232 }233} {t1 t2}234do_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}}242do_test check-3.4 {243 execsql {244 SELECT name FROM sqlite_master ORDER BY name245 }246} {t1 t2}247do_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}}255do_test check-3.6 {256 execsql {257 SELECT name FROM sqlite_master ORDER BY name258 }259} {t1 t2}260do_test check-3.7 {261 catchsql {262 CREATE TABLE t3(263 x, y, z,264 CHECK( t3.x<25 )265 );266 }267} {0 {}}268do_test check-3.8 {269 execsql {270 INSERT INTO t3 VALUES(1,2,3);271 SELECT * FROM t3;272 }273} {1 2 3}274do_test check-3.9 {275 catchsql {276 INSERT INTO t3 VALUES(111,222,333);277 }278} {1 {CHECK constraint failed: t3.x<25}}279 280do_test check-4.1 {281 execsql {282 CREATE TABLE t4(x, y,283 CHECK (284 x+y==11285 OR x*y==12286 OR x/y BETWEEN 5 AND 8287 OR -x==y+10288 )289 );290 }291} {}292do_test check-4.2 {293 execsql {294 INSERT INTO t4 VALUES(1,10);295 SELECT * FROM t4296 }297} {1 10}298do_test check-4.3 {299 execsql {300 UPDATE t4 SET x=4, y=3;301 SELECT * FROM t4302 }303} {4 3}304do_test check-4.4 {305 execsql {306 UPDATE t4 SET x=12, y=2;307 SELECT * FROM t4308 }309} {12 2}310do_test check-4.5 {311 execsql {312 UPDATE t4 SET x=12, y=-22;313 SELECT * FROM t4314 }315} {12 -22}316do_test check-4.6 {317 catchsql {318 UPDATE t4 SET x=0, y=1;319 }320} {1 {CHECK constraint failed: x+y==11321 OR x*y==12322 OR x/y BETWEEN 5 AND 8323 OR -x==y+10}}324do_test check-4.7 {325 execsql {326 SELECT * FROM t4;327 }328} {12 -22}329do_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}337do_execsql_test check-4.8.1 {338 PRAGMA ignore_check_constraints=OFF;339 PRAGMA integrity_check;340} {{CHECK constraint failed in t4}}341do_test check-4.9 {342 catchsql {343 UPDATE t4 SET x=0, y=2;344 }345} {1 {CHECK constraint failed: x+y==11346 OR x*y==12347 OR x/y BETWEEN 5 AND 8348 OR -x==y+10}}349ifcapable vacuum {350 do_test check_4.10 {351 catchsql {352 VACUUM353 }354 } {0 {}}355}356 357do_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}}364do_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 372ifcapable conflict {373 374do_test check-6.1 {375 execsql {SELECT * FROM t1}376} {4 11.0}377do_test check-6.2 {378 execsql {379 UPDATE OR IGNORE t1 SET x=5;380 SELECT * FROM t1;381 }382} {4 11.0}383do_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}389do_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}395do_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}}400do_test check-6.6 {401 execsql {402 SELECT * FROM t1;403 }404} {3 12.0 2 20.0}405do_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}}412do_test check-6.8 {413 catchsql {414 COMMIT;415 }416} {1 {cannot commit - no transaction is active}}417do_test check-6.9 {418 execsql {419 SELECT * FROM t1420 }421} {3 12.0 2 20.0}422 423do_test check-6.11 {424 execsql {SELECT * FROM t1}425} {3 12.0 2 20.0}426do_test check-6.12 {427 catchsql {428 REPLACE INTO t1 VALUES(6,7);429 }430} {1 {CHECK constraint failed: x<5}}431do_test check-6.13 {432 execsql {SELECT * FROM t1}433} {3 12.0 2 20.0}434do_test check-6.14 {435 catchsql {436 INSERT OR IGNORE INTO t1 VALUES(6,7);437 }438} {0 {}}439do_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 that448# uses an unknown UDF, the schema should not be considered malformed.449# Attempting to modify the table should fail (since the CHECK constraint450# cannot be tested).451#452reset_db453proc myfunc {x} {expr $x < 10}454db func myfunc -deterministic myfunc455 456do_execsql_test 7.1 { CREATE TABLE t6(a CHECK (myfunc(a))) }457do_execsql_test 7.2 { INSERT INTO t6 VALUES(9) }458do_catchsql_test 7.3 { INSERT INTO t6 VALUES(11) } \459 {1 {CHECK constraint failed: myfunc(a)}}460 461do_test 7.4 {462 sqlite3 db2 test.db463 execsql { SELECT * FROM t6 } db2 464} {9}465 466do_test 7.5 {467 catchsql { INSERT INTO t6 VALUES(8) } db2468} {1 {unknown function: myfunc()}}469 470do_test 7.6 {471 catchsql { CREATE TABLE t7(a CHECK (myfunc(a))) } db2472} {1 {no such function: myfunc}}473 474do_test 7.7 {475 db2 func myfunc myfunc476 execsql { INSERT INTO t6 VALUES(8) } db2477} {}478 479do_test 7.8 {480 db2 func myfunc myfunc481 catchsql { INSERT INTO t6 VALUES(12) } db2482} {1 {CHECK constraint failed: myfunc(a)}}483 484# 2013-08-02: Silently ignore database name qualifiers in CHECK constraints.485#486do_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 ignored492#493do_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} {}502do_catchsql_test 9.2 {503 UPDATE t1 SET b=0 WHERE a=1;504} {1 {CHECK constraint failed: b-check}}505do_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#511db close512db2 close513forcedelete test.db514sqlite3 db test.db515do_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#-------------------------------------------------------------------------522reset_db523do_execsql_test 11.0 {524 CREATE TABLE t1 (Col0 CHECK(1 COLLATE BINARY BETWEEN 1 AND 1) ) ;525}526do_execsql_test 11.1 {527 INSERT INTO t1 VALUES (NULL);528}529do_execsql_test 11.2 {530 INSERT INTO t1 VALUES (NULL);531}532 533do_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}538do_execsql_test 11.4 {539 INSERT INTO t2(a) VALUES('abc');540}541do_execsql_test 11.5 {542 INSERT INTO t2(b, a) VALUES(1, 'abc'||'');543}544do_execsql_test 11.6 {545 INSERT INTO t2(b, a) VALUES(2, 'abc');546}547 548# 2019-12-24 ticket b383b90278186263549#550reset_db551do_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'}556do_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} {}561do_catchsql_test 12.21 {562 INSERT INTO t1(a) VALUES('xyz');563} {1 {CHECK constraint failed: a<>+a}}564do_catchsql_test 12.22 {565 INSERT INTO t1(a) VALUES(123);566} {1 {CHECK constraint failed: a<>+a}}567do_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} {}572do_catchsql_test 12.31 {573 INSERT INTO t1(a) VALUES('xyz');574} {1 {CHECK constraint failed: NOT(a=+a)}}575do_catchsql_test 12.32 {576 INSERT INTO t1(a) VALUES(123);577} {1 {CHECK constraint failed: NOT(a=+a)}}578do_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'}584do_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'}590do_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}596do_catchsql_test 12.61 {597 INSERT INTO t1(a) VALUES(456);598} {1 {CHECK constraint failed: a NOT BETWEEN 0 AND +a}}599do_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'}605do_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}611do_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 617reset_db618 619do_execsql_test 13.1.0 {620 CREATE TABLE Table0 (Col0 , CHECK(Table0.Col0 NOT NULL ) ) ;621 REPLACE INTO Table0 VALUES (hex(randomblob(100000)));622}623integrity_check 13.1.1624do_execsql_test 13.1.2 {625 UPDATE OR REPLACE Table0 SET Col0 = Table0.Col0 ;626}627integrity_check 13.1.3628do_execsql_test 13.1.4 {629 SELECT length(col0) FROM table0;630} {200000}631 632do_execsql_test 13.2.0 {633 CREATE TABLE t2 (x , CHECK((NOT (x ISNULL) )));634 REPLACE INTO t2 VALUES (hex(randomblob(100000)));635}636do_execsql_test 13.2.1 {637 SELECT length(x) FROM t2638} {200000}639do_execsql_test 13.2.2 {640 UPDATE OR REPLACE t2 SET x = x;641}642do_execsql_test 13.2.3 {643 SELECT length(x) FROM t2644} {200000}645integrity_check 13.2.4646 647finish_test648