test/check.test

   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
  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}
 116
 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 {}}
 170
 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.
 174 #
 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 } {}
 213
 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}}
 223 }
 224
 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}}
 275
 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 {}}
 351 }
 352
 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}}
 367
 368 ifcapable conflict {
 369
 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}
 418
 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}
 438
 439
 440 }
 441
 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).
 447 #
 448 reset_db
 449 proc myfunc {x} {expr $x < 10}
 450 db func myfunc -deterministic myfunc
 451
 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)}}
 456
 457 do_test 7.4 {
 458   sqlite3 db2 test.db
 459   execsql { SELECT * FROM t6 } db2
 460 } {9}
 461
 462 do_test 7.5 {
 463   catchsql { INSERT INTO t6 VALUES(8) } db2
 464 } {1 {unknown function: myfunc()}}
 465
 466 do_test 7.6 {
 467   catchsql { CREATE TABLE t7(a CHECK (myfunc(a))) } db2
 468 } {1 {no such function: myfunc}}
 469
 470 do_test 7.7 {
 471   db2 func myfunc myfunc
 472   execsql { INSERT INTO t6 VALUES(8) } db2
 473 } {}
 474
 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)}}
 479
 480 # 2013-08-02:  Silently ignore database name qualifiers in CHECK constraints.
 481 #
 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 } {}
 486
 487 # Make sure check constraints involving the ROWID are not ignored
 488 #
 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}}
 504
 505 # Integrity check on a VIEW with columns.
 506 #
 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}
 516
 517 #-------------------------------------------------------------------------
 518 reset_db
 519 do_execsql_test 11.0 {
 520   CREATE TABLE t1 (Col0 CHECK(1 COLLATE BINARY BETWEEN 1 AND 1) ) ;
 521 }
 522 do_execsql_test 11.1 {
 523   INSERT INTO t1 VALUES (NULL);
 524 }
 525 do_execsql_test 11.2 {
 526   INSERT  INTO t1 VALUES (NULL);
 527 }
 528
 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   );
 533 }
 534 do_execsql_test 11.4 {
 535   INSERT INTO t2(a) VALUES('abc');
 536 }
 537 do_execsql_test 11.5 {
 538   INSERT INTO t2(b, a) VALUES(1, 'abc'||'');
 539 }
 540 do_execsql_test 11.6 {
 541   INSERT INTO t2(b, a) VALUES(2, 'abc');
 542 }
 543
 544 # 2019-12-24 ticket b383b90278186263
 545 #
 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}}
 610
 611 finish_test