AryaWu/sqlite

1# 2002 January 292#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.12#13# This file implements tests for the NOT NULL constraint.14#15# $Id: notnull.test,v 1.4 2006/01/17 09:35:02 danielk1977 Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable !conflict {21  finish_test22  return23}24 25do_test notnull-1.0 {26  execsql {27    CREATE TABLE t1 (28      a NOT NULL,29      b NOT NULL DEFAULT 5,30      c NOT NULL ON CONFLICT REPLACE DEFAULT 6,31      d NOT NULL ON CONFLICT IGNORE DEFAULT 7,32      e NOT NULL ON CONFLICT ABORT DEFAULT 833    );34    SELECT * FROM t1;35  }36} {}37do_test notnull-1.1 {38  catchsql {39    DELETE FROM t1;40    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);41    SELECT * FROM t1 order by a;42  }43} {0 {1 2 3 4 5}}44do_test notnull-1.2 {45  catchsql {46    DELETE FROM t1;47    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);48    SELECT * FROM t1 order by a;49  }50} {1 {NOT NULL constraint failed: t1.a}}51verify_ex_errcode notnull-1.2b SQLITE_CONSTRAINT_NOTNULL52do_test notnull-1.3 {53  catchsql {54    DELETE FROM t1;55    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);56    SELECT * FROM t1 order by a;57  }58} {0 {}}59do_test notnull-1.4 {60  catchsql {61    DELETE FROM t1;62    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);63    SELECT * FROM t1 order by a;64  }65} {1 {NOT NULL constraint failed: t1.a}}66verify_ex_errcode notnull-1.4b SQLITE_CONSTRAINT_NOTNULL67do_test notnull-1.5 {68  catchsql {69    DELETE FROM t1;70    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);71    SELECT * FROM t1 order by a;72  }73} {1 {NOT NULL constraint failed: t1.a}}74verify_ex_errcode notnull-1.5b SQLITE_CONSTRAINT_NOTNULL75do_test notnull-1.6 {76  catchsql {77    DELETE FROM t1;78    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);79    SELECT * FROM t1 order by a;80  }81} {0 {1 5 3 4 5}}82do_test notnull-1.7 {83  catchsql {84    DELETE FROM t1;85    INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);86    SELECT * FROM t1 order by a;87  }88} {0 {1 5 3 4 5}}89do_test notnull-1.8 {90  catchsql {91    DELETE FROM t1;92    INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);93    SELECT * FROM t1 order by a;94  }95} {0 {1 5 3 4 5}}96do_test notnull-1.9 {97  catchsql {98    DELETE FROM t1;99    INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);100    SELECT * FROM t1 order by a;101  }102} {0 {1 5 3 4 5}}103do_test notnull-1.10 {104  catchsql {105    DELETE FROM t1;106    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);107    SELECT * FROM t1 order by a;108  }109} {1 {NOT NULL constraint failed: t1.b}}110verify_ex_errcode notnull-1.10b SQLITE_CONSTRAINT_NOTNULL111do_test notnull-1.11 {112  catchsql {113    DELETE FROM t1;114    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);115    SELECT * FROM t1 order by a;116  }117} {0 {}}118do_test notnull-1.12 {119  catchsql {120    DELETE FROM t1;121    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);122    SELECT * FROM t1 order by a;123  }124} {0 {1 5 3 4 5}}125do_test notnull-1.13 {126  catchsql {127    DELETE FROM t1;128    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);129    SELECT * FROM t1 order by a;130  }131} {0 {1 2 6 4 5}}132do_test notnull-1.14 {133  catchsql {134    DELETE FROM t1;135    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);136    SELECT * FROM t1 order by a;137  }138} {0 {}}139do_test notnull-1.15 {140  catchsql {141    DELETE FROM t1;142    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);143    SELECT * FROM t1 order by a;144  }145} {0 {1 2 6 4 5}}146do_test notnull-1.16 {147  catchsql {148    DELETE FROM t1;149    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);150    SELECT * FROM t1 order by a;151  }152} {1 {NOT NULL constraint failed: t1.c}}153verify_ex_errcode notnull-1.16b SQLITE_CONSTRAINT_NOTNULL154do_test notnull-1.17 {155  catchsql {156    DELETE FROM t1;157    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);158    SELECT * FROM t1 order by a;159  }160} {1 {NOT NULL constraint failed: t1.d}}161verify_ex_errcode notnull-1.17b SQLITE_CONSTRAINT_NOTNULL162do_test notnull-1.18 {163  catchsql {164    DELETE FROM t1;165    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);166    SELECT * FROM t1 order by a;167  }168} {0 {1 2 3 7 5}}169do_test notnull-1.19 {170  catchsql {171    DELETE FROM t1;172    INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);173    SELECT * FROM t1 order by a;174  }175} {0 {1 2 3 4 8}}176do_test notnull-1.20 {177  catchsql {178    DELETE FROM t1;179    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);180    SELECT * FROM t1 order by a;181  }182} {1 {NOT NULL constraint failed: t1.e}}183verify_ex_errcode notnull-1.20b SQLITE_CONSTRAINT_NOTNULL184do_test notnull-1.21 {185  catchsql {186    DELETE FROM t1;187    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);188    SELECT * FROM t1 order by a;189  }190} {0 {5 5 3 2 1}}191 192do_test notnull-2.1 {193  catchsql {194    DELETE FROM t1;195    INSERT INTO t1 VALUES(1,2,3,4,5);196    UPDATE t1 SET a=null;197    SELECT * FROM t1 ORDER BY a;198  }199} {1 {NOT NULL constraint failed: t1.a}}200verify_ex_errcode notnull-2.1b SQLITE_CONSTRAINT_NOTNULL201do_test notnull-2.2 {202  catchsql {203    DELETE FROM t1;204    INSERT INTO t1 VALUES(1,2,3,4,5);205    UPDATE OR REPLACE t1 SET a=null;206    SELECT * FROM t1 ORDER BY a;207  }208} {1 {NOT NULL constraint failed: t1.a}}209verify_ex_errcode notnull-2.2b SQLITE_CONSTRAINT_NOTNULL210do_test notnull-2.3 {211  catchsql {212    DELETE FROM t1;213    INSERT INTO t1 VALUES(1,2,3,4,5);214    UPDATE OR IGNORE t1 SET a=null;215    SELECT * FROM t1 ORDER BY a;216  }217} {0 {1 2 3 4 5}}218do_test notnull-2.4 {219  catchsql {220    DELETE FROM t1;221    INSERT INTO t1 VALUES(1,2,3,4,5);222    UPDATE OR ABORT t1 SET a=null;223    SELECT * FROM t1 ORDER BY a;224  }225} {1 {NOT NULL constraint failed: t1.a}}226verify_ex_errcode notnull-2.4b SQLITE_CONSTRAINT_NOTNULL227do_test notnull-2.5 {228  catchsql {229    DELETE FROM t1;230    INSERT INTO t1 VALUES(1,2,3,4,5);231    UPDATE t1 SET b=null;232    SELECT * FROM t1 ORDER BY a;233  }234} {1 {NOT NULL constraint failed: t1.b}}235verify_ex_errcode notnull-2.6b SQLITE_CONSTRAINT_NOTNULL236do_test notnull-2.6 {237  catchsql {238    DELETE FROM t1;239    INSERT INTO t1 VALUES(1,2,3,4,5);240    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;241    SELECT * FROM t1 ORDER BY a;242  }243} {0 {1 5 3 5 4}}244do_test notnull-2.7 {245  catchsql {246    DELETE FROM t1;247    INSERT INTO t1 VALUES(1,2,3,4,5);248    UPDATE OR IGNORE t1 SET b=null, d=e, e=d;249    SELECT * FROM t1 ORDER BY a;250  }251} {0 {1 2 3 4 5}}252do_test notnull-2.8 {253  catchsql {254    DELETE FROM t1;255    INSERT INTO t1 VALUES(1,2,3,4,5);256    UPDATE t1 SET c=null, d=e, e=d;257    SELECT * FROM t1 ORDER BY a;258  }259} {0 {1 2 6 5 4}}260do_test notnull-2.9 {261  catchsql {262    DELETE FROM t1;263    INSERT INTO t1 VALUES(1,2,3,4,5);264    UPDATE t1 SET d=null, a=b, b=a;265    SELECT * FROM t1 ORDER BY a;266  }267} {0 {1 2 3 4 5}}268do_test notnull-2.10 {269  catchsql {270    DELETE FROM t1;271    INSERT INTO t1 VALUES(1,2,3,4,5);272    UPDATE t1 SET e=null, a=b, b=a;273    SELECT * FROM t1 ORDER BY a;274  }275} {1 {NOT NULL constraint failed: t1.e}}276verify_ex_errcode notnull-2.10b SQLITE_CONSTRAINT_NOTNULL277 278do_test notnull-3.0 {279  execsql {280    CREATE INDEX t1a ON t1(a);281    CREATE INDEX t1b ON t1(b);282    CREATE INDEX t1c ON t1(c);283    CREATE INDEX t1d ON t1(d);284    CREATE INDEX t1e ON t1(e);285    CREATE INDEX t1abc ON t1(a,b,c);286  }287} {}288do_test notnull-3.1 {289  catchsql {290    DELETE FROM t1;291    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);292    SELECT * FROM t1 order by a;293  }294} {0 {1 2 3 4 5}}295do_test notnull-3.2 {296  catchsql {297    DELETE FROM t1;298    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);299    SELECT * FROM t1 order by a;300  }301} {1 {NOT NULL constraint failed: t1.a}}302verify_ex_errcode notnull-3.2b SQLITE_CONSTRAINT_NOTNULL303do_test notnull-3.3 {304  catchsql {305    DELETE FROM t1;306    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);307    SELECT * FROM t1 order by a;308  }309} {0 {}}310do_test notnull-3.4 {311  catchsql {312    DELETE FROM t1;313    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);314    SELECT * FROM t1 order by a;315  }316} {1 {NOT NULL constraint failed: t1.a}}317verify_ex_errcode notnull-3.4b SQLITE_CONSTRAINT_NOTNULL318do_test notnull-3.5 {319  catchsql {320    DELETE FROM t1;321    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);322    SELECT * FROM t1 order by a;323  }324} {1 {NOT NULL constraint failed: t1.a}}325verify_ex_errcode notnull-3.5b SQLITE_CONSTRAINT_NOTNULL326do_test notnull-3.6 {327  catchsql {328    DELETE FROM t1;329    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);330    SELECT * FROM t1 order by a;331  }332} {0 {1 5 3 4 5}}333do_test notnull-3.7 {334  catchsql {335    DELETE FROM t1;336    INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);337    SELECT * FROM t1 order by a;338  }339} {0 {1 5 3 4 5}}340do_test notnull-3.8 {341  catchsql {342    DELETE FROM t1;343    INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);344    SELECT * FROM t1 order by a;345  }346} {0 {1 5 3 4 5}}347do_test notnull-3.9 {348  catchsql {349    DELETE FROM t1;350    INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);351    SELECT * FROM t1 order by a;352  }353} {0 {1 5 3 4 5}}354do_test notnull-3.10 {355  catchsql {356    DELETE FROM t1;357    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);358    SELECT * FROM t1 order by a;359  }360} {1 {NOT NULL constraint failed: t1.b}}361verify_ex_errcode notnull-3.10b SQLITE_CONSTRAINT_NOTNULL362do_test notnull-3.11 {363  catchsql {364    DELETE FROM t1;365    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);366    SELECT * FROM t1 order by a;367  }368} {0 {}}369do_test notnull-3.12 {370  catchsql {371    DELETE FROM t1;372    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);373    SELECT * FROM t1 order by a;374  }375} {0 {1 5 3 4 5}}376do_test notnull-3.13 {377  catchsql {378    DELETE FROM t1;379    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);380    SELECT * FROM t1 order by a;381  }382} {0 {1 2 6 4 5}}383do_test notnull-3.14 {384  catchsql {385    DELETE FROM t1;386    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);387    SELECT * FROM t1 order by a;388  }389} {0 {}}390do_test notnull-3.15 {391  catchsql {392    DELETE FROM t1;393    INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);394    SELECT * FROM t1 order by a;395  }396} {0 {1 2 6 4 5}}397do_test notnull-3.16 {398  catchsql {399    DELETE FROM t1;400    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);401    SELECT * FROM t1 order by a;402  }403} {1 {NOT NULL constraint failed: t1.c}}404verify_ex_errcode notnull-3.16b SQLITE_CONSTRAINT_NOTNULL405do_test notnull-3.17 {406  catchsql {407    DELETE FROM t1;408    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);409    SELECT * FROM t1 order by a;410  }411} {1 {NOT NULL constraint failed: t1.d}}412verify_ex_errcode notnull-3.17b SQLITE_CONSTRAINT_NOTNULL413do_test notnull-3.18 {414  catchsql {415    DELETE FROM t1;416    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);417    SELECT * FROM t1 order by a;418  }419} {0 {1 2 3 7 5}}420do_test notnull-3.19 {421  catchsql {422    DELETE FROM t1;423    INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);424    SELECT * FROM t1 order by a;425  }426} {0 {1 2 3 4 8}}427do_test notnull-3.20 {428  catchsql {429    DELETE FROM t1;430    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);431    SELECT * FROM t1 order by a;432  }433} {1 {NOT NULL constraint failed: t1.e}}434verify_ex_errcode notnull-3.20b SQLITE_CONSTRAINT_NOTNULL435do_test notnull-3.21 {436  catchsql {437    DELETE FROM t1;438    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);439    SELECT * FROM t1 order by a;440  }441} {0 {5 5 3 2 1}}442 443do_test notnull-4.1 {444  catchsql {445    DELETE FROM t1;446    INSERT INTO t1 VALUES(1,2,3,4,5);447    UPDATE t1 SET a=null;448    SELECT * FROM t1 ORDER BY a;449  }450} {1 {NOT NULL constraint failed: t1.a}}451verify_ex_errcode notnull-4.1b SQLITE_CONSTRAINT_NOTNULL452do_test notnull-4.2 {453  catchsql {454    DELETE FROM t1;455    INSERT INTO t1 VALUES(1,2,3,4,5);456    UPDATE OR REPLACE t1 SET a=null;457    SELECT * FROM t1 ORDER BY a;458  }459} {1 {NOT NULL constraint failed: t1.a}}460verify_ex_errcode notnull-4.2b SQLITE_CONSTRAINT_NOTNULL461do_test notnull-4.3 {462  catchsql {463    DELETE FROM t1;464    INSERT INTO t1 VALUES(1,2,3,4,5);465    UPDATE OR IGNORE t1 SET a=null;466    SELECT * FROM t1 ORDER BY a;467  }468} {0 {1 2 3 4 5}}469do_test notnull-4.4 {470  catchsql {471    DELETE FROM t1;472    INSERT INTO t1 VALUES(1,2,3,4,5);473    UPDATE OR ABORT t1 SET a=null;474    SELECT * FROM t1 ORDER BY a;475  }476} {1 {NOT NULL constraint failed: t1.a}}477verify_ex_errcode notnull-4.4b SQLITE_CONSTRAINT_NOTNULL478do_test notnull-4.5 {479  catchsql {480    DELETE FROM t1;481    INSERT INTO t1 VALUES(1,2,3,4,5);482    UPDATE t1 SET b=null;483    SELECT * FROM t1 ORDER BY a;484  }485} {1 {NOT NULL constraint failed: t1.b}}486verify_ex_errcode notnull-4.5b SQLITE_CONSTRAINT_NOTNULL487do_test notnull-4.6 {488  catchsql {489    DELETE FROM t1;490    INSERT INTO t1 VALUES(1,2,3,4,5);491    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;492    SELECT * FROM t1 ORDER BY a;493  }494} {0 {1 5 3 5 4}}495do_test notnull-4.7 {496  catchsql {497    DELETE FROM t1;498    INSERT INTO t1 VALUES(1,2,3,4,5);499    UPDATE OR IGNORE t1 SET b=null, d=e, e=d;500    SELECT * FROM t1 ORDER BY a;501  }502} {0 {1 2 3 4 5}}503do_test notnull-4.8 {504  catchsql {505    DELETE FROM t1;506    INSERT INTO t1 VALUES(1,2,3,4,5);507    UPDATE t1 SET c=null, d=e, e=d;508    SELECT * FROM t1 ORDER BY a;509  }510} {0 {1 2 6 5 4}}511do_test notnull-4.9 {512  catchsql {513    DELETE FROM t1;514    INSERT INTO t1 VALUES(1,2,3,4,5);515    UPDATE t1 SET d=null, a=b, b=a;516    SELECT * FROM t1 ORDER BY a;517  }518} {0 {1 2 3 4 5}}519do_test notnull-4.10 {520  catchsql {521    DELETE FROM t1;522    INSERT INTO t1 VALUES(1,2,3,4,5);523    UPDATE t1 SET e=null, a=b, b=a;524    SELECT * FROM t1 ORDER BY a;525  }526} {1 {NOT NULL constraint failed: t1.e}}527verify_ex_errcode notnull-4.10b SQLITE_CONSTRAINT_NOTNULL528 529# Test that bug 29ab7be99f is fixed.530#531do_test notnull-5.1 {532  execsql {533    DROP TABLE IF EXISTS t1;534    CREATE TABLE t1(a, b NOT NULL);535    CREATE TABLE t2(c, d);536    INSERT INTO t2 VALUES(3, 4);537    INSERT INTO t2 VALUES(5, NULL);538  }539}  {}540do_test notnull-5.2 {541  catchsql {542    INSERT INTO t1 VALUES(1, 2);543    INSERT INTO t1 SELECT * FROM t2;544  }545} {1 {NOT NULL constraint failed: t1.b}}546verify_ex_errcode notnull-5.2b SQLITE_CONSTRAINT_NOTNULL547do_test notnull-5.3 {548  execsql { SELECT * FROM t1 }549} {1 2}550do_test notnull-5.4 {551  catchsql {552    DELETE FROM t1;553    BEGIN;554      INSERT INTO t1 VALUES(1, 2);555      INSERT INTO t1 SELECT * FROM t2;556    COMMIT;557  }558} {1 {NOT NULL constraint failed: t1.b}}559verify_ex_errcode notnull-5.4b SQLITE_CONSTRAINT_NOTNULL560do_test notnull-5.5 {561  execsql { SELECT * FROM t1 }562} {1 2}563 564#-------------------------------------------------------------------------565# Check that UNIQUE NOT NULL indexes are always recognized as such.566#567proc uses_op_next {sql} {568  db eval "EXPLAIN $sql" a {569    if {$a(opcode)=="Next"} { return 1 }570  }571  return 0572}573 574proc do_uses_op_next_test {tn sql res} {575  uplevel [list do_test $tn [list uses_op_next $sql] $res]576}577 578reset_db579do_execsql_test notnull-6.0 {580  CREATE TABLE t1(a UNIQUE);581  CREATE TABLE t2(a NOT NULL UNIQUE);582  CREATE TABLE t3(a UNIQUE NOT NULL);583  CREATE TABLE t4(a NOT NULL);584  CREATE UNIQUE INDEX t4a ON t4(a);585 586  CREATE TABLE t5(a PRIMARY KEY);587  CREATE TABLE t6(a PRIMARY KEY NOT NULL);588  CREATE TABLE t7(a NOT NULL PRIMARY KEY);589  CREATE TABLE t8(a PRIMARY KEY) WITHOUT ROWID;590 591  CREATE TABLE t9(a PRIMARY KEY UNIQUE NOT NULL);592  CREATE TABLE t10(a UNIQUE PRIMARY KEY NOT NULL);593}594 595do_uses_op_next_test notnull-6.1 "SELECT * FROM t1 WHERE a IS ?" 1596do_uses_op_next_test notnull-6.2 "SELECT * FROM t2 WHERE a IS ?" 0597do_uses_op_next_test notnull-6.3 "SELECT * FROM t3 WHERE a IS ?" 0598do_uses_op_next_test notnull-6.4 "SELECT * FROM t4 WHERE a IS ?" 0599 600do_uses_op_next_test notnull-6.5 "SELECT * FROM t5 WHERE a IS ?" 1601do_uses_op_next_test notnull-6.6 "SELECT * FROM t6 WHERE a IS ?" 0602do_uses_op_next_test notnull-6.7 "SELECT * FROM t7 WHERE a IS ?" 0603do_uses_op_next_test notnull-6.8 "SELECT * FROM t8 WHERE a IS ?" 0604 605do_uses_op_next_test notnull-6.9 "SELECT * FROM t8 WHERE a IS ?" 0606do_uses_op_next_test notnull-6.10 "SELECT * FROM t8 WHERE a IS ?" 0607 608finish_test609