gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/check.test (about) 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 finish_test