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 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