/ Check-in [294ed337]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the conflict2.test script. Fix issues discovered by this script.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 294ed33756b06375e56c41f1088d42ee48adbdc8
User & Date: drh 2013-11-05 01:59:07
Context
2013-11-05
13:33
Standardize the error messages generated by constraint failures to a format of "$TYPE constraint failed: $DETAIL". This involves many changes to the expected output of test cases. check-in: 54b22192 user: drh tags: omit-rowid
01:59
Add the conflict2.test script. Fix issues discovered by this script. check-in: 294ed337 user: drh tags: omit-rowid
2013-11-04
22:04
Add the index7.test script for testing partial indices with WITHOUT ROWID tables. Fix bugs in ANALYZE located by that script. check-in: 79befe3a user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1687   1687       for(i=0, j=pIdx->nKeyCol; i<nPk; i++){
  1688   1688         if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ){
  1689   1689           pIdx->aiColumn[j] = pPk->aiColumn[i];
  1690   1690           pIdx->azColl[j] = pPk->azColl[i];
  1691   1691           j++;
  1692   1692         }
  1693   1693       }
  1694         -    assert( pIdx->nColumn==j );
  1695         -    assert( pIdx->nKeyCol+n==j );
         1694  +    assert( pIdx->nColumn>=pIdx->nKeyCol+n );
         1695  +    assert( pIdx->nColumn>=j );
  1696   1696     }
  1697   1697   
  1698   1698     /* Add all table columns to the PRIMARY KEY index
  1699   1699     */
  1700   1700     if( nPk<pTab->nCol ){
  1701   1701       if( resizeIndexObject(db, pPk, pTab->nCol) ) return;
  1702   1702       for(i=0, j=nPk; i<pTab->nCol; i++){

Changes to src/insert.c.

  1459   1459       VdbeComment((v, "for %s", pIdx->zName));
  1460   1460       sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn);
  1461   1461   
  1462   1462       /* In an UPDATE operation, if this index is the PRIMARY KEY index 
  1463   1463       ** of a WITHOUT ROWID table and there has been no change the
  1464   1464       ** primary key, then no collision is possible.  The collision detection
  1465   1465       ** logic below can all be skipped. */
  1466         -    if( isUpdate && pPk && pkChng==0 ){
         1466  +    if( isUpdate && pPk==pIdx && pkChng==0 ){
  1467   1467         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1468   1468         continue;
  1469   1469       }
  1470   1470   
  1471   1471       /* Find out what action to take in case there is a uniqueness conflict */
  1472   1472       onError = pIdx->onError;
  1473   1473       if( onError==OE_None ){ 
................................................................................
  1542   1542       /* Generate code that executes if the new index entry is not unique */
  1543   1543       assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
  1544   1544           || onError==OE_Ignore || onError==OE_Replace );
  1545   1545       switch( onError ){
  1546   1546         case OE_Rollback:
  1547   1547         case OE_Abort:
  1548   1548         case OE_Fail: {
  1549         -        int j;
  1550         -        StrAccum errMsg;
  1551         -        const char *zSep;
  1552         -        char *zErr;
  1553         -
  1554         -        sqlite3StrAccumInit(&errMsg, 0, 0, 200);
  1555         -        errMsg.db = db;
  1556         -        zSep = pIdx->nKeyCol>1 ? "columns " : "column ";
  1557         -        for(j=0; j<pIdx->nKeyCol; j++){
  1558         -          char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
  1559         -          sqlite3StrAccumAppend(&errMsg, zSep, -1);
  1560         -          zSep = ", ";
  1561         -          sqlite3StrAccumAppend(&errMsg, zCol, -1);
  1562         -        }
  1563         -        sqlite3StrAccumAppend(&errMsg,
  1564         -            pIdx->nKeyCol>1 ? " are not unique" : " is not unique", -1);
  1565         -        zErr = sqlite3StrAccumFinish(&errMsg);
  1566         -        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_UNIQUE,
  1567         -                              onError, zErr, 0);
  1568         -        sqlite3DbFree(errMsg.db, zErr);
         1549  +        if( pIdx->autoIndex==2 ){
         1550  +          sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_PRIMARYKEY,
         1551  +                                onError, "PRIMARY KEY must be unique", 0);
         1552  +        }else{
         1553  +          int j;
         1554  +          StrAccum errMsg;
         1555  +          const char *zSep;
         1556  +          char *zErr;
         1557  +
         1558  +          sqlite3StrAccumInit(&errMsg, 0, 0, 200);
         1559  +          errMsg.db = db;
         1560  +          zSep = pIdx->nKeyCol>1 ? "columns " : "column ";
         1561  +          for(j=0; j<pIdx->nKeyCol; j++){
         1562  +            char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
         1563  +            sqlite3StrAccumAppend(&errMsg, zSep, -1);
         1564  +            zSep = ", ";
         1565  +            sqlite3StrAccumAppend(&errMsg, zCol, -1);
         1566  +          }
         1567  +          sqlite3StrAccumAppend(&errMsg,
         1568  +              pIdx->nKeyCol>1 ? " are not unique" : " is not unique", -1);
         1569  +          zErr = sqlite3StrAccumFinish(&errMsg);
         1570  +          sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_UNIQUE,
         1571  +                               onError, zErr, 0);
         1572  +          sqlite3DbFree(errMsg.db, zErr);
         1573  +        }
  1569   1574           break;
  1570   1575         }
  1571   1576         case OE_Ignore: {
  1572   1577           assert( seenReplace==0 );
  1573   1578           sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
  1574   1579           break;
  1575   1580         }

Changes to src/update.c.

   449    449       u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
   450    450       oldmask |= sqlite3TriggerColmask(pParse, 
   451    451           pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError
   452    452       );
   453    453       for(i=0; i<pTab->nCol; i++){
   454    454         if( oldmask==0xffffffff
   455    455          || (i<32 && (oldmask & (1<<i)))
   456         -       || (chngPk && (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0)
          456  +       || (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0
   457    457         ){
   458    458           sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regOld+i);
   459    459         }else{
   460    460           sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
   461    461         }
   462    462       }
   463    463       if( chngRowid==0 && pPk==0 ){

Changes to test/collate4.test.

   469    469     }
   470    470   } {}
   471    471   do_test collate4-3.1 {
   472    472     catchsql {
   473    473       INSERT INTO collate4t1 VALUES('abc');
   474    474       INSERT INTO collate4t1 VALUES('ABC');
   475    475     }
   476         -} {1 {column a is not unique}}
          476  +} {1 {PRIMARY KEY must be unique}}
   477    477   do_test collate4-3.2 {
   478    478     execsql {
   479    479       SELECT * FROM collate4t1;
   480    480     }
   481    481   } {abc}
   482    482   do_test collate4-3.3 {
   483    483     catchsql {
   484    484       INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
   485    485     }
   486         -} {1 {column a is not unique}}
          486  +} {1 {PRIMARY KEY must be unique}}
   487    487   do_test collate4-3.4 {
   488    488     catchsql {
   489    489       INSERT INTO collate4t1 VALUES(1);
   490    490       UPDATE collate4t1 SET a = 'abc';
   491    491     }
   492         -} {1 {column a is not unique}}
          492  +} {1 {PRIMARY KEY must be unique}}
   493    493   do_test collate4-3.5 {
   494    494     execsql {
   495    495       DROP TABLE collate4t1;
   496    496       CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
   497    497     }
   498    498   } {}
   499    499   do_test collate4-3.6 {

Added test/conflict2.test.

            1  +# 2013-11-04
            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.
           12  +#
           13  +# This file implements tests for the conflict resolution extension
           14  +# in WITHOUT ROWID tables
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +ifcapable !conflict {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +# Create tables for the first group of tests.
           26  +#
           27  +do_test conflict2-1.0 {
           28  +  execsql {
           29  +    CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITHOUT rowid;
           30  +    CREATE TABLE t2(x);
           31  +    SELECT c FROM t1 ORDER BY c;
           32  +  }
           33  +} {}
           34  +
           35  +# Six columns of configuration data as follows:
           36  +#
           37  +#   i      The reference number of the test
           38  +#   cmd    An INSERT or REPLACE command to execute against table t1
           39  +#   t0     True if there is an error from $cmd
           40  +#   t1     Content of "c" column of t1 assuming no error in $cmd
           41  +#   t2     Content of "x" column of t2
           42  +#   t3     Number of temporary files created by this test
           43  +#
           44  +foreach {i cmd t0 t1 t2 t3} {
           45  +  1 INSERT                  1 {}  1  0
           46  +  2 {INSERT OR IGNORE}      0 3   1  0
           47  +  3 {INSERT OR REPLACE}     0 4   1  0
           48  +  4 REPLACE                 0 4   1  0
           49  +  5 {INSERT OR FAIL}        1 {}  1  0
           50  +  6 {INSERT OR ABORT}       1 {}  1  0
           51  +  7 {INSERT OR ROLLBACK}    1 {}  {} 0
           52  +} {
           53  +  do_test conflict2-1.$i {
           54  +    set ::sqlite_opentemp_count 0
           55  +    set r0 [catch {execsql [subst {
           56  +      DELETE FROM t1;
           57  +      DELETE FROM t2;
           58  +      INSERT INTO t1 VALUES(1,2,3);
           59  +      BEGIN;
           60  +      INSERT INTO t2 VALUES(1); 
           61  +      $cmd INTO t1 VALUES(1,2,4);
           62  +    }]} r1]
           63  +    catch {execsql {COMMIT}}
           64  +    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
           65  +    set r2 [execsql {SELECT x FROM t2}]
           66  +    set r3 $::sqlite_opentemp_count
           67  +    list $r0 $r1 $r2 $r3
           68  +  } [list $t0 $t1 $t2 $t3]
           69  +}
           70  +
           71  +# Create tables for the first group of tests.
           72  +#
           73  +do_test conflict2-2.0 {
           74  +  execsql {
           75  +    DROP TABLE t1;
           76  +    DROP TABLE t2;
           77  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b)) WITHOUT rowid;
           78  +    CREATE TABLE t2(x);
           79  +    SELECT c FROM t1 ORDER BY c;
           80  +  }
           81  +} {}
           82  +
           83  +# Six columns of configuration data as follows:
           84  +#
           85  +#   i      The reference number of the test
           86  +#   cmd    An INSERT or REPLACE command to execute against table t1
           87  +#   t0     True if there is an error from $cmd
           88  +#   t1     Content of "c" column of t1 assuming no error in $cmd
           89  +#   t2     Content of "x" column of t2
           90  +#
           91  +foreach {i cmd t0 t1 t2} {
           92  +  1 INSERT                  1 {}  1
           93  +  2 {INSERT OR IGNORE}      0 3   1
           94  +  3 {INSERT OR REPLACE}     0 4   1
           95  +  4 REPLACE                 0 4   1
           96  +  5 {INSERT OR FAIL}        1 {}  1
           97  +  6 {INSERT OR ABORT}       1 {}  1
           98  +  7 {INSERT OR ROLLBACK}    1 {}  {}
           99  +} {
          100  +  do_test conflict2-2.$i {
          101  +    set r0 [catch {execsql [subst {
          102  +      DELETE FROM t1;
          103  +      DELETE FROM t2;
          104  +      INSERT INTO t1 VALUES(1,2,3);
          105  +      BEGIN;
          106  +      INSERT INTO t2 VALUES(1); 
          107  +      $cmd INTO t1 VALUES(1,2,4);
          108  +    }]} r1]
          109  +    catch {execsql {COMMIT}}
          110  +    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
          111  +    set r2 [execsql {SELECT x FROM t2}]
          112  +    list $r0 $r1 $r2
          113  +  } [list $t0 $t1 $t2]
          114  +}
          115  +
          116  +# Create tables for the first group of tests.
          117  +#
          118  +do_test conflict2-3.0 {
          119  +  execsql {
          120  +    DROP TABLE t1;
          121  +    DROP TABLE t2;
          122  +    CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b)) WITHOUT rowid;
          123  +    CREATE TABLE t2(x);
          124  +    SELECT c FROM t1 ORDER BY c;
          125  +  }
          126  +} {}
          127  +
          128  +# Six columns of configuration data as follows:
          129  +#
          130  +#   i      The reference number of the test
          131  +#   cmd    An INSERT or REPLACE command to execute against table t1
          132  +#   t0     True if there is an error from $cmd
          133  +#   t1     Content of "c" column of t1 assuming no error in $cmd
          134  +#   t2     Content of "x" column of t2
          135  +#
          136  +foreach {i cmd t0 t1 t2} {
          137  +  1 INSERT                  1 {}  1
          138  +  2 {INSERT OR IGNORE}      0 3   1
          139  +  3 {INSERT OR REPLACE}     0 4   1
          140  +  4 REPLACE                 0 4   1
          141  +  5 {INSERT OR FAIL}        1 {}  1
          142  +  6 {INSERT OR ABORT}       1 {}  1
          143  +  7 {INSERT OR ROLLBACK}    1 {}  {}
          144  +} {
          145  +  do_test conflict2-3.$i {
          146  +    set r0 [catch {execsql [subst {
          147  +      DELETE FROM t1;
          148  +      DELETE FROM t2;
          149  +      INSERT INTO t1 VALUES(1,2,3);
          150  +      BEGIN;
          151  +      INSERT INTO t2 VALUES(1); 
          152  +      $cmd INTO t1 VALUES(1,2,4);
          153  +    }]} r1]
          154  +    catch {execsql {COMMIT}}
          155  +    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
          156  +    set r2 [execsql {SELECT x FROM t2}]
          157  +    list $r0 $r1 $r2
          158  +  } [list $t0 $t1 $t2]
          159  +}
          160  +
          161  +do_test conflict2-4.0 {
          162  +  execsql {
          163  +    DROP TABLE t2;
          164  +    CREATE TABLE t2(x);
          165  +    SELECT x FROM t2;
          166  +  }
          167  +} {}
          168  +
          169  +# Six columns of configuration data as follows:
          170  +#
          171  +#   i      The reference number of the test
          172  +#   conf1  The conflict resolution algorithm on the UNIQUE constraint
          173  +#   cmd    An INSERT or REPLACE command to execute against table t1
          174  +#   t0     True if there is an error from $cmd
          175  +#   t1     Content of "c" column of t1 assuming no error in $cmd
          176  +#   t2     Content of "x" column of t2
          177  +#
          178  +foreach {i conf1 cmd t0 t1 t2} {
          179  +  1 {}       INSERT                  1 {}  1
          180  +  2 REPLACE  INSERT                  0 4   1
          181  +  3 IGNORE   INSERT                  0 3   1
          182  +  4 FAIL     INSERT                  1 {}  1
          183  +  5 ABORT    INSERT                  1 {}  1
          184  +  6 ROLLBACK INSERT                  1 {}  {}
          185  +  7 REPLACE  {INSERT OR IGNORE}      0 3   1
          186  +  8 IGNORE   {INSERT OR REPLACE}     0 4   1
          187  +  9 FAIL     {INSERT OR IGNORE}      0 3   1
          188  + 10 ABORT    {INSERT OR REPLACE}     0 4   1
          189  + 11 ROLLBACK {INSERT OR IGNORE }     0 3   1
          190  +} {
          191  +  do_test conflict2-4.$i {
          192  +    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
          193  +    set r0 [catch {execsql [subst {
          194  +      DROP TABLE t1;
          195  +      CREATE TABLE t1(a,b,c,PRIMARY KEY(a,b) $conf1) WITHOUT rowid;
          196  +      DELETE FROM t2;
          197  +      INSERT INTO t1 VALUES(1,2,3);
          198  +      BEGIN;
          199  +      INSERT INTO t2 VALUES(1); 
          200  +      $cmd INTO t1 VALUES(1,2,4);
          201  +    }]} r1]
          202  +    catch {execsql {COMMIT}}
          203  +    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
          204  +    set r2 [execsql {SELECT x FROM t2}]
          205  +    list $r0 $r1 $r2
          206  +  } [list $t0 $t1 $t2]
          207  +}
          208  +
          209  +do_test conflict2-5.0 {
          210  +  execsql {
          211  +    DROP TABLE t2;
          212  +    CREATE TABLE t2(x);
          213  +    SELECT x FROM t2;
          214  +  }
          215  +} {}
          216  +
          217  +# Six columns of configuration data as follows:
          218  +#
          219  +#   i      The reference number of the test
          220  +#   conf1  The conflict resolution algorithm on the NOT NULL constraint
          221  +#   cmd    An INSERT or REPLACE command to execute against table t1
          222  +#   t0     True if there is an error from $cmd
          223  +#   t1     Content of "c" column of t1 assuming no error in $cmd
          224  +#   t2     Content of "x" column of t2
          225  +#
          226  +foreach {i conf1 cmd t0 t1 t2} {
          227  +  1 {}       INSERT                  1 {}  1
          228  +  2 REPLACE  INSERT                  0 5   1
          229  +  3 IGNORE   INSERT                  0 {}  1
          230  +  4 FAIL     INSERT                  1 {}  1
          231  +  5 ABORT    INSERT                  1 {}  1
          232  +  6 ROLLBACK INSERT                  1 {}  {}
          233  +  7 REPLACE  {INSERT OR IGNORE}      0 {}  1
          234  +  8 IGNORE   {INSERT OR REPLACE}     0 5   1
          235  +  9 FAIL     {INSERT OR IGNORE}      0 {}  1
          236  + 10 ABORT    {INSERT OR REPLACE}     0 5   1
          237  + 11 ROLLBACK {INSERT OR IGNORE}      0 {}  1
          238  + 12 {}       {INSERT OR IGNORE}      0 {}  1
          239  + 13 {}       {INSERT OR REPLACE}     0 5   1
          240  + 14 {}       {INSERT OR FAIL}        1 {}  1
          241  + 15 {}       {INSERT OR ABORT}       1 {}  1
          242  + 16 {}       {INSERT OR ROLLBACK}    1 {}  {}
          243  +} {
          244  +  if {$t0} {set t1 {t1.c may not be NULL}}
          245  +  do_test conflict2-5.$i {
          246  +    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
          247  +    set r0 [catch {execsql [subst {
          248  +      DROP TABLE t1;
          249  +      CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
          250  +      DELETE FROM t2;
          251  +      BEGIN;
          252  +      INSERT INTO t2 VALUES(1); 
          253  +      $cmd INTO t1 VALUES(1,2,NULL);
          254  +    }]} r1]
          255  +    catch {execsql {COMMIT}}
          256  +    if {!$r0} {set r1 [execsql {SELECT c FROM t1}]}
          257  +    set r2 [execsql {SELECT x FROM t2}]
          258  +    list $r0 $r1 $r2
          259  +  } [list $t0 $t1 $t2]
          260  +}
          261  +
          262  +do_test conflict2-6.0 {
          263  +  execsql {
          264  +    DROP TABLE t2;
          265  +    CREATE TABLE t2(a,b,c);
          266  +    INSERT INTO t2 VALUES(1,2,1);
          267  +    INSERT INTO t2 VALUES(2,3,2);
          268  +    INSERT INTO t2 VALUES(3,4,1);
          269  +    INSERT INTO t2 VALUES(4,5,4);
          270  +    SELECT c FROM t2 ORDER BY b;
          271  +    CREATE TABLE t3(x);
          272  +    INSERT INTO t3 VALUES(1);
          273  +  }
          274  +} {1 2 1 4}
          275  +
          276  +# Six columns of configuration data as follows:
          277  +#
          278  +#   i      The reference number of the test
          279  +#   conf1  The conflict resolution algorithm on the UNIQUE constraint
          280  +#   cmd    An UPDATE command to execute against table t1
          281  +#   t0     True if there is an error from $cmd
          282  +#   t1     Content of "b" column of t1 assuming no error in $cmd
          283  +#   t2     Content of "x" column of t3
          284  +#   t3     Number of temporary files for tables
          285  +#   t4     Number of temporary files for statement journals
          286  +#
          287  +# Update: Since temporary table files are now opened lazily, and none
          288  +# of the following tests use large quantities of data, t3 is always 0.
          289  +#
          290  +foreach {i conf1 cmd t0 t1 t2 t3 t4} {
          291  +  1 {}       UPDATE                  1 {6 7 8 9}  1 0 1
          292  +  2 REPLACE  UPDATE                  0 {7 6 9}    1 0 0
          293  +  3 IGNORE   UPDATE                  0 {6 7 3 9}  1 0 0
          294  +  4 FAIL     UPDATE                  1 {6 7 3 4}  1 0 0
          295  +  5 ABORT    UPDATE                  1 {1 2 3 4}  1 0 1
          296  +  6 ROLLBACK UPDATE                  1 {1 2 3 4}  0 0 0
          297  +  7 REPLACE  {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
          298  +  8 IGNORE   {UPDATE OR REPLACE}     0 {7 6 9}    1 0 1
          299  +  9 FAIL     {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
          300  + 10 ABORT    {UPDATE OR REPLACE}     0 {7 6 9}    1 0 1
          301  + 11 ROLLBACK {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
          302  + 12 {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
          303  + 13 {}       {UPDATE OR REPLACE}     0 {7 6 9}    1 0 1
          304  + 14 {}       {UPDATE OR FAIL}        1 {6 7 3 4}  1 0 0
          305  + 15 {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1 0 1
          306  + 16 {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0 0 0
          307  +} {
          308  +  if {$t0} {set t1 {PRIMARY KEY must be unique}}
          309  +  if {[info exists TEMP_STORE] && $TEMP_STORE==3} {
          310  +    set t3 0
          311  +  } else {
          312  +    set t3 [expr {$t3+$t4}]
          313  +  }
          314  +  do_test conflict2-6.$i {
          315  +    db close
          316  +    sqlite3 db test.db 
          317  +    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
          318  +    execsql {pragma temp_store=file}
          319  +    set ::sqlite_opentemp_count 0
          320  +    set r0 [catch {execsql [subst {
          321  +      DROP TABLE t1;
          322  +      CREATE TABLE t1(a,b,c, PRIMARY KEY(a) $conf1) WITHOUT rowid;
          323  +      INSERT INTO t1 SELECT * FROM t2;
          324  +      UPDATE t3 SET x=0;
          325  +      BEGIN;
          326  +      $cmd t3 SET x=1;
          327  +      $cmd t1 SET b=b*2;
          328  +      $cmd t1 SET a=c+5;
          329  +    }]} r1]
          330  +    catch {execsql {COMMIT}}
          331  +    if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
          332  +    set r2 [execsql {SELECT x FROM t3}]
          333  +    list $r0 $r1 $r2 $::sqlite_opentemp_count
          334  +  } [list $t0 $t1 $t2 $t3]
          335  +}
          336  +
          337  +# Test to make sure a lot of IGNOREs don't cause a stack overflow
          338  +#
          339  +do_test conflict2-7.1 {
          340  +  execsql {
          341  +    DROP TABLE t1;
          342  +    DROP TABLE t2;
          343  +    DROP TABLE t3;
          344  +    CREATE TABLE t1(a PRIMARY KEY, b) without rowid;
          345  +  }
          346  +  for {set i 1} {$i<=50} {incr i} {
          347  +    execsql "INSERT into t1 values($i,[expr {$i+1}]);"
          348  +  }
          349  +  execsql {
          350  +    SELECT count(*), min(a), max(b) FROM t1;
          351  +  }
          352  +} {50 1 51}
          353  +do_test conflict2-7.2 {
          354  +  execsql {
          355  +    PRAGMA count_changes=on;
          356  +    UPDATE OR IGNORE t1 SET a=1000;
          357  +  }
          358  +} {1}
          359  +do_test conflict2-7.2.1 {
          360  +  db changes
          361  +} {1}
          362  +do_test conflict2-7.3 {
          363  +  execsql {
          364  +    SELECT b FROM t1 WHERE a=1000;
          365  +  }
          366  +} {2}
          367  +do_test conflict2-7.4 {
          368  +  execsql {
          369  +    SELECT count(*) FROM t1;
          370  +  }
          371  +} {50}
          372  +do_test conflict2-7.5 {
          373  +  execsql {
          374  +    PRAGMA count_changes=on;
          375  +    UPDATE OR REPLACE t1 SET a=1001;
          376  +  }
          377  +} {50}
          378  +do_test conflict2-7.5.1 {
          379  +  db changes
          380  +} {50}
          381  +do_test conflict2-7.6 {
          382  +  execsql {
          383  +    SELECT b FROM t1 WHERE a=1001;
          384  +  }
          385  +} {51}
          386  +do_test conflict2-7.7 {
          387  +  execsql {
          388  +    SELECT count(*) FROM t1;
          389  +  }
          390  +} {1}
          391  +
          392  +# Update for version 3: A SELECT statement no longer resets the change
          393  +# counter (Test result changes from 0 to 50).
          394  +do_test conflict2-7.7.1 {
          395  +  db changes
          396  +} {50}
          397  +
          398  +# Make sure the row count is right for rows that are ignored on
          399  +# an insert.
          400  +#
          401  +do_test conflict2-8.1 {
          402  +  execsql {
          403  +    DELETE FROM t1;
          404  +    INSERT INTO t1 VALUES(1,2);
          405  +  }
          406  +  execsql {
          407  +    INSERT OR IGNORE INTO t1 VALUES(2,3);
          408  +  }
          409  +} {1}
          410  +do_test conflict2-8.1.1 {
          411  +  db changes
          412  +} {1}
          413  +do_test conflict2-8.2 {
          414  +  execsql {
          415  +    INSERT OR IGNORE INTO t1 VALUES(2,4);
          416  +  }
          417  +} {0}
          418  +do_test conflict2-8.2.1 {
          419  +  db changes
          420  +} {0}
          421  +do_test conflict2-8.3 {
          422  +  execsql {
          423  +    INSERT OR REPLACE INTO t1 VALUES(2,4);
          424  +  }
          425  +} {1}
          426  +do_test conflict2-8.3.1 {
          427  +  db changes
          428  +} {1}
          429  +do_test conflict2-8.4 {
          430  +  execsql {
          431  +    INSERT OR IGNORE INTO t1 SELECT * FROM t1;
          432  +  }
          433  +} {0}
          434  +do_test conflict2-8.4.1 {
          435  +  db changes
          436  +} {0}
          437  +do_test conflict2-8.5 {
          438  +  execsql {
          439  +    INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1;
          440  +  }
          441  +} {2}
          442  +do_test conflict2-8.5.1 {
          443  +  db changes
          444  +} {2}
          445  +do_test conflict2-8.6 {
          446  +  execsql {
          447  +    INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
          448  +  }
          449  +} {3}
          450  +do_test conflict2-8.6.1 {
          451  +  db changes
          452  +} {3}
          453  +
          454  +integrity_check conflict2-8.99
          455  +
          456  +do_test conflict2-9.1 {
          457  +  execsql {
          458  +    PRAGMA count_changes=0;
          459  +    CREATE TABLE t2(
          460  +      a INTEGER PRIMARY KEY ON CONFLICT IGNORE,
          461  +      b INTEGER UNIQUE ON CONFLICT FAIL,
          462  +      c INTEGER UNIQUE ON CONFLICT REPLACE,
          463  +      d INTEGER UNIQUE ON CONFLICT ABORT,
          464  +      e INTEGER UNIQUE ON CONFLICT ROLLBACK
          465  +    ) WITHOUT rowid;
          466  +    CREATE TABLE t3(x);
          467  +    INSERT INTO t3 VALUES(1);
          468  +    SELECT * FROM t3;
          469  +  }
          470  +} {1}
          471  +do_test conflict2-9.2 {
          472  +  catchsql {
          473  +    INSERT INTO t2 VALUES(1,1,1,1,1);
          474  +    INSERT INTO t2 VALUES(2,2,2,2,2);
          475  +    SELECT * FROM t2;
          476  +  }
          477  +} {0 {1 1 1 1 1 2 2 2 2 2}}
          478  +do_test conflict2-9.3 {
          479  +  catchsql {
          480  +    INSERT INTO t2 VALUES(1,3,3,3,3);
          481  +    SELECT * FROM t2;
          482  +  }
          483  +} {0 {1 1 1 1 1 2 2 2 2 2}}
          484  +do_test conflict2-9.4 {
          485  +  catchsql {
          486  +    UPDATE t2 SET a=a+1 WHERE a=1;
          487  +    SELECT * FROM t2;
          488  +  }
          489  +} {0 {1 1 1 1 1 2 2 2 2 2}}
          490  +do_test conflict2-9.5 {
          491  +  catchsql {
          492  +    INSERT INTO t2 VALUES(3,1,3,3,3);
          493  +  }
          494  +} {1 {column b is not unique}}
          495  +do_test conflict2-9.5b {
          496  +  db eval {SELECT * FROM t2;}
          497  +} {1 1 1 1 1 2 2 2 2 2}
          498  +do_test conflict2-9.6 {
          499  +  catchsql {
          500  +    UPDATE t2 SET b=b+1 WHERE b=1;
          501  +    SELECT * FROM t2;
          502  +  }
          503  +} {1 {column b is not unique}}
          504  +do_test conflict2-9.6b {
          505  +  db eval {SELECT * FROM t2;}
          506  +} {1 1 1 1 1 2 2 2 2 2}
          507  +do_test conflict2-9.7 {
          508  +  catchsql {
          509  +    BEGIN;
          510  +    UPDATE t3 SET x=x+1;
          511  +    INSERT INTO t2 VALUES(3,1,3,3,3);
          512  +    SELECT * FROM t2;
          513  +  }
          514  +} {1 {column b is not unique}}
          515  +do_test conflict2-9.8 {
          516  +  execsql {COMMIT}
          517  +  execsql {SELECT * FROM t3}
          518  +} {2}
          519  +do_test conflict2-9.9 {
          520  +  catchsql {
          521  +    BEGIN;
          522  +    UPDATE t3 SET x=x+1;
          523  +    UPDATE t2 SET b=b+1 WHERE b=1;
          524  +    SELECT * FROM t2;
          525  +  }
          526  +} {1 {column b is not unique}}
          527  +do_test conflict2-9.10 {
          528  +  execsql {COMMIT}
          529  +  execsql {SELECT * FROM t3}
          530  +} {3}
          531  +do_test conflict2-9.11 {
          532  +  catchsql {
          533  +    INSERT INTO t2 VALUES(3,3,3,1,3);
          534  +    SELECT * FROM t2;
          535  +  }
          536  +} {1 {column d is not unique}}
          537  +do_test conflict2-9.12 {
          538  +  catchsql {
          539  +    UPDATE t2 SET d=d+1 WHERE d=1;
          540  +    SELECT * FROM t2;
          541  +  }
          542  +} {1 {column d is not unique}}
          543  +do_test conflict2-9.13 {
          544  +  catchsql {
          545  +    BEGIN;
          546  +    UPDATE t3 SET x=x+1;
          547  +    INSERT INTO t2 VALUES(3,3,3,1,3);
          548  +    SELECT * FROM t2;
          549  +  }
          550  +} {1 {column d is not unique}}
          551  +do_test conflict2-9.14 {
          552  +  execsql {COMMIT}
          553  +  execsql {SELECT * FROM t3}
          554  +} {4}
          555  +do_test conflict2-9.15 {
          556  +  catchsql {
          557  +    BEGIN;
          558  +    UPDATE t3 SET x=x+1;
          559  +    UPDATE t2 SET d=d+1 WHERE d=1;
          560  +    SELECT * FROM t2;
          561  +  }
          562  +} {1 {column d is not unique}}
          563  +do_test conflict2-9.16 {
          564  +  execsql {COMMIT}
          565  +  execsql {SELECT * FROM t3}
          566  +} {5}
          567  +do_test conflict2-9.17 {
          568  +  catchsql {
          569  +    INSERT INTO t2 VALUES(3,3,3,3,1);
          570  +    SELECT * FROM t2;
          571  +  }
          572  +} {1 {column e is not unique}}
          573  +do_test conflict2-9.18 {
          574  +  catchsql {
          575  +    UPDATE t2 SET e=e+1 WHERE e=1;
          576  +    SELECT * FROM t2;
          577  +  }
          578  +} {1 {column e is not unique}}
          579  +do_test conflict2-9.19 {
          580  +  catchsql {
          581  +    BEGIN;
          582  +    UPDATE t3 SET x=x+1;
          583  +    INSERT INTO t2 VALUES(3,3,3,3,1);
          584  +    SELECT * FROM t2;
          585  +  }
          586  +} {1 {column e is not unique}}
          587  +verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE
          588  +do_test conflict2-9.20 {
          589  +  catch {execsql {COMMIT}}
          590  +  execsql {SELECT * FROM t3}
          591  +} {5}
          592  +do_test conflict2-9.21 {
          593  +  catchsql {
          594  +    BEGIN;
          595  +    UPDATE t3 SET x=x+1;
          596  +    UPDATE t2 SET e=e+1 WHERE e=1;
          597  +    SELECT * FROM t2;
          598  +  }
          599  +} {1 {column e is not unique}}
          600  +verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE
          601  +do_test conflict2-9.22 {
          602  +  catch {execsql {COMMIT}}
          603  +  execsql {SELECT * FROM t3}
          604  +} {5}
          605  +do_test conflict2-9.23 {
          606  +  catchsql {
          607  +    INSERT INTO t2 VALUES(3,3,1,3,3);
          608  +    SELECT * FROM t2;
          609  +  }
          610  +} {0 {2 2 2 2 2 3 3 1 3 3}}
          611  +do_test conflict2-9.24 {
          612  +  catchsql {
          613  +    UPDATE t2 SET c=c-1 WHERE c=2;
          614  +    SELECT * FROM t2;
          615  +  }
          616  +} {0 {2 2 1 2 2}}
          617  +do_test conflict2-9.25 {
          618  +  catchsql {
          619  +    BEGIN;
          620  +    UPDATE t3 SET x=x+1;
          621  +    INSERT INTO t2 VALUES(3,3,1,3,3);
          622  +    SELECT * FROM t2;
          623  +  }
          624  +} {0 {3 3 1 3 3}}
          625  +do_test conflict2-9.26 {
          626  +  catch {execsql {COMMIT}}
          627  +  execsql {SELECT * FROM t3}
          628  +} {6}
          629  +
          630  +do_test conflict2-10.1 {
          631  +  catchsql {
          632  +    DELETE FROM t1;
          633  +    BEGIN;
          634  +    INSERT OR ROLLBACK INTO t1 VALUES(1,2);
          635  +    INSERT OR ROLLBACK INTO t1 VALUES(1,3);
          636  +    COMMIT;
          637  +  }
          638  +  execsql {SELECT * FROM t1}
          639  +} {}
          640  +do_test conflict2-10.2 {
          641  +  catchsql {
          642  +    CREATE TABLE t4(x);
          643  +    CREATE UNIQUE INDEX t4x ON t4(x);
          644  +    BEGIN;
          645  +    INSERT OR ROLLBACK INTO t4 VALUES(1);
          646  +    INSERT OR ROLLBACK INTO t4 VALUES(1);
          647  +    COMMIT;
          648  +  }
          649  +  execsql {SELECT * FROM t4}
          650  +} {}
          651  +
          652  +# Ticket #1171.  Make sure statement rollbacks do not
          653  +# damage the database.
          654  +#
          655  +do_test conflict2-11.1 {
          656  +  execsql {
          657  +    -- Create a database object (pages 2, 3 of the file)
          658  +    BEGIN;
          659  +      CREATE TABLE abc(a PRIMARY KEY, b, c) WITHOUT rowid;
          660  +      INSERT INTO abc VALUES(1, 2, 3);
          661  +      INSERT INTO abc VALUES(4, 5, 6);
          662  +      INSERT INTO abc VALUES(7, 8, 9);
          663  +    COMMIT;
          664  +  }
          665  +
          666  +  
          667  +  # Set a small cache size so that changes will spill into
          668  +  # the database file.  
          669  +  execsql {
          670  +    PRAGMA cache_size = 10;
          671  +  }
          672  +  
          673  +  # Make lots of changes.  Because of the small cache, some
          674  +  # (most?) of these changes will spill into the disk file.
          675  +  # In other words, some of the changes will not be held in
          676  +  # cache.
          677  +  #
          678  +  execsql {
          679  +    BEGIN;
          680  +      -- Make sure the pager is in EXCLUSIVE state.
          681  +      CREATE TABLE def(d, e, f);
          682  +      INSERT INTO def VALUES
          683  +          ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
          684  +      INSERT INTO def SELECT * FROM def;
          685  +      INSERT INTO def SELECT * FROM def;
          686  +      INSERT INTO def SELECT * FROM def;
          687  +      INSERT INTO def SELECT * FROM def;
          688  +      INSERT INTO def SELECT * FROM def;
          689  +      INSERT INTO def SELECT * FROM def;
          690  +      INSERT INTO def SELECT * FROM def;
          691  +      DELETE FROM abc WHERE a = 4;
          692  +  }
          693  +
          694  +  # Execute a statement that does a statement rollback due to
          695  +  # a constraint failure.
          696  +  #
          697  +  catchsql {
          698  +    INSERT INTO abc SELECT 10, 20, 30 FROM def;
          699  +  }
          700  +
          701  +  # Rollback the database.  Verify that the state of the ABC table
          702  +  # is unchanged from the beginning of the transaction.  In other words,
          703  +  # make sure the DELETE on table ABC that occurred within the transaction
          704  +  # had no effect.
          705  +  #
          706  +  execsql {
          707  +    ROLLBACK;
          708  +    SELECT * FROM abc;
          709  +  }
          710  +} {1 2 3 4 5 6 7 8 9}
          711  +integrity_check conflict2-11.2
          712  +
          713  +# Repeat test conflict2-11.1 but this time commit.
          714  +#
          715  +do_test conflict2-11.3 {
          716  +  execsql {
          717  +    BEGIN;
          718  +      -- Make sure the pager is in EXCLUSIVE state.
          719  +      UPDATE abc SET a=a+1;
          720  +      CREATE TABLE def(d, e, f);
          721  +      INSERT INTO def VALUES
          722  +          ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
          723  +      INSERT INTO def SELECT * FROM def;
          724  +      INSERT INTO def SELECT * FROM def;
          725  +      INSERT INTO def SELECT * FROM def;
          726  +      INSERT INTO def SELECT * FROM def;
          727  +      INSERT INTO def SELECT * FROM def;
          728  +      INSERT INTO def SELECT * FROM def;
          729  +      INSERT INTO def SELECT * FROM def;
          730  +      DELETE FROM abc WHERE a = 4;
          731  +  }
          732  +  catchsql {
          733  +    INSERT INTO abc SELECT 10, 20, 30 FROM def;
          734  +  }
          735  +  execsql {
          736  +    ROLLBACK;
          737  +    SELECT * FROM abc;
          738  +  }
          739  +} {1 2 3 4 5 6 7 8 9}
          740  +# Repeat test conflict2-11.1 but this time commit.
          741  +#
          742  +do_test conflict2-11.5 {
          743  +  execsql {
          744  +    BEGIN;
          745  +      -- Make sure the pager is in EXCLUSIVE state.
          746  +      CREATE TABLE def(d, e, f);
          747  +      INSERT INTO def VALUES
          748  +          ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
          749  +      INSERT INTO def SELECT * FROM def;
          750  +      INSERT INTO def SELECT * FROM def;
          751  +      INSERT INTO def SELECT * FROM def;
          752  +      INSERT INTO def SELECT * FROM def;
          753  +      INSERT INTO def SELECT * FROM def;
          754  +      INSERT INTO def SELECT * FROM def;
          755  +      INSERT INTO def SELECT * FROM def;
          756  +      DELETE FROM abc WHERE a = 4;
          757  +  }
          758  +  catchsql {
          759  +    INSERT INTO abc SELECT 10, 20, 30 FROM def;
          760  +  }
          761  +  execsql {
          762  +    COMMIT;
          763  +    SELECT * FROM abc;
          764  +  }
          765  +} {1 2 3 7 8 9}
          766  +integrity_check conflict2-11.6
          767  +
          768  +# Make sure UPDATE OR REPLACE works on tables that have only
          769  +# an INTEGER PRIMARY KEY.
          770  +#
          771  +do_test conflict2-12.1 {
          772  +  execsql {
          773  +    CREATE TABLE t5(a INTEGER PRIMARY KEY, b text) WITHOUT rowid;
          774  +    INSERT INTO t5 VALUES(1,'one');
          775  +    INSERT INTO t5 VALUES(2,'two');
          776  +    SELECT * FROM t5
          777  +  }
          778  +} {1 one 2 two}
          779  +do_test conflict2-12.2 {
          780  +  execsql {
          781  +    UPDATE OR IGNORE t5 SET a=a+1 WHERE a=1;
          782  +    SELECT * FROM t5;
          783  +  }
          784  +} {1 one 2 two}
          785  +do_test conflict2-12.3 {
          786  +  catchsql {
          787  +    UPDATE t5 SET a=a+1 WHERE a=1;
          788  +  }
          789  +} {1 {PRIMARY KEY must be unique}}
          790  +verify_ex_errcode conflict2-12.3b SQLITE_CONSTRAINT_PRIMARYKEY
          791  +do_test conflict2-12.4 {
          792  +  execsql {
          793  +    UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
          794  +    SELECT * FROM t5;
          795  +  }
          796  +} {2 one}
          797  +
          798  +
          799  +# Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437]
          800  +# REPLACE works like ABORT on a CHECK constraint.
          801  +#
          802  +do_test conflict2-13.1 {
          803  +  execsql {
          804  +    CREATE TABLE t13(a PRIMARY KEY CHECK(a!=2)) WITHOUT rowid;
          805  +    BEGIN;
          806  +    REPLACE INTO t13 VALUES(1);
          807  +  }
          808  +  catchsql {
          809  +    REPLACE INTO t13 VALUES(2);
          810  +  }
          811  +} {1 {constraint failed}}
          812  +verify_ex_errcode conflict2-13.1b SQLITE_CONSTRAINT_CHECK
          813  +do_test conflict2-13.2 {
          814  +  execsql {
          815  +    REPLACE INTO t13 VALUES(3);
          816  +    COMMIT;
          817  +    SELECT * FROM t13;
          818  +  }
          819  +} {1 3}
          820  +
          821  +
          822  +finish_test

Changes to test/e_createtable.test.

  1178   1178     CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
  1179   1179     INSERT INTO t2 VALUES(0,          'zero');
  1180   1180     INSERT INTO t2 VALUES(45.5,       'one');
  1181   1181     INSERT INTO t2 VALUES('brambles', 'two');
  1182   1182     INSERT INTO t2 VALUES(X'ABCDEF',  'three');
  1183   1183   } {}
  1184   1184   
  1185         -do_createtable_tests 4.3.1 -error { %s not unique } {
         1185  +do_createtable_tests 4.3.1 -error {PRIMARY KEY must be unique} {
  1186   1186     1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
  1187   1187     2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
  1188   1188     3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
  1189   1189     4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
  1190   1190     5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}
  1191   1191   
  1192   1192     6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
................................................................................
  1204   1204   
  1205   1205     6    "INSERT INTO t2 VALUES(0, 0)"                 {}
  1206   1206     7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
  1207   1207     8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
  1208   1208     9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
  1209   1209     10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
  1210   1210   }
  1211         -do_createtable_tests 4.3.3 -error { %s not unique } {
         1211  +do_createtable_tests 4.3.3 -error {PRIMARY KEY must be unique} {
  1212   1212     1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
  1213   1213     2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
  1214   1214     3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
  1215   1215     4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
  1216   1216     5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}
  1217   1217   
  1218   1218     6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
................................................................................
  1533   1533     INSERT INTO t3_ig SELECT * FROM t3_ab;
  1534   1534     INSERT INTO t3_fa SELECT * FROM t3_ab;
  1535   1535     INSERT INTO t3_re SELECT * FROM t3_ab;
  1536   1536     INSERT INTO t3_xx SELECT * FROM t3_ab;
  1537   1537   }
  1538   1538   
  1539   1539   foreach {tn tbl res ac data} {
  1540         -  1   t1_ab    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
  1541         -  2   t1_ro    {1 {column a is not unique}} 1 {1 one 2 two}
  1542         -  3   t1_fa    {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string}
         1540  +  1   t1_ab    {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three}
         1541  +  2   t1_ro    {1 {PRIMARY KEY must be unique}} 1 {1 one 2 two}
         1542  +  3   t1_fa    {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three 4 string}
  1543   1543     4   t1_ig    {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
  1544   1544     5   t1_re    {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
  1545         -  6   t1_xx    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
         1545  +  6   t1_xx    {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three}
  1546   1546   } {
  1547   1547     catchsql COMMIT
  1548   1548     do_execsql_test  4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
  1549   1549   
  1550   1550     do_catchsql_test 4.15.$tn.2 " 
  1551   1551       INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
  1552   1552     " $res
................................................................................
  1743   1743     SELECT typeof(pk), pk FROM t7;
  1744   1744     SELECT typeof(pk), pk FROM t8;
  1745   1745     SELECT typeof(pk), pk FROM t9;
  1746   1746   } {integer 2 integer 2 integer 2 integer 2}
  1747   1747   
  1748   1748   do_catchsql_test 5.4.4.1 { 
  1749   1749     INSERT INTO t6 VALUES(2) 
  1750         -} {1 {column pk is not unique}}
         1750  +} {1 {PRIMARY KEY must be unique}}
  1751   1751   do_catchsql_test 5.4.4.2 { 
  1752   1752     INSERT INTO t7 VALUES(2) 
  1753         -} {1 {column pk is not unique}}
         1753  +} {1 {PRIMARY KEY must be unique}}
  1754   1754   do_catchsql_test 5.4.4.3 { 
  1755   1755     INSERT INTO t8 VALUES(2) 
  1756         -} {1 {column pk is not unique}}
         1756  +} {1 {PRIMARY KEY must be unique}}
  1757   1757   do_catchsql_test 5.4.4.4 { 
  1758   1758     INSERT INTO t9 VALUES(2) 
  1759         -} {1 {column pk is not unique}}
         1759  +} {1 {PRIMARY KEY must be unique}}
  1760   1760   
  1761   1761   # EVIDENCE-OF: R-56094-57830 the following three table declarations all
  1762   1762   # cause the column "x" to be an alias for the rowid (an integer primary
  1763   1763   # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
  1764   1764   # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
  1765   1765   # z, PRIMARY KEY(x DESC));
  1766   1766   #

Changes to test/fkey2.test.

   371    371   fkey2-2-test 62 0   "DELETE FROM leaf"
   372    372   fkey2-2-test 63 0   "DELETE FROM node"
   373    373   fkey2-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
   374    374   fkey2-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
   375    375   fkey2-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
   376    376   do_test fkey2-2-test-67 {
   377    377     catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
   378         -} {1 {column nodeid is not unique}}
          378  +} {1 {PRIMARY KEY must be unique}}
   379    379   fkey2-2-test 68 0 "COMMIT"           FKV
   380    380   fkey2-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
   381    381   fkey2-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
   382    382   fkey2-2-test 71 0 "COMMIT"
   383    383   
   384    384   fkey2-2-test 72 0 "BEGIN"
   385    385   fkey2-2-test 73 1   "DELETE FROM node"

Changes to test/misc1.test.

   231    231       SELECT * FROM t5 ORDER BY a;
   232    232     }
   233    233   } {1 2 3}
   234    234   do_test misc1-7.4 {
   235    235     catchsql {
   236    236       INSERT INTO t5 VALUES(1,2,4);
   237    237     }
   238         -} {1 {columns a, b are not unique}}
          238  +} {1 {PRIMARY KEY must be unique}}
   239    239   do_test misc1-7.5 {
   240    240     catchsql {
   241    241       INSERT INTO t5 VALUES(0,2,4);
   242    242     }
   243    243   } {0 {}}
   244    244   do_test misc1-7.6 {
   245    245     execsql {

Changes to test/savepoint.test.

   854    854       BEGIN;
   855    855         INSERT INTO t4 VALUES(2, 'two');
   856    856         SAVEPOINT sp1;
   857    857           INSERT INTO t4 VALUES(3, 'three');
   858    858           SAVEPOINT sp2;
   859    859             INSERT OR ROLLBACK INTO t4 VALUES(1, 'one');
   860    860     }
   861         -} {1 {column a is not unique}}
          861  +} {1 {PRIMARY KEY must be unique}}
   862    862   do_test savepoint-12.3 {
   863    863     sqlite3_get_autocommit db
   864    864   } {1}
   865    865   do_test savepoint-12.4 {
   866    866     execsql { SAVEPOINT one }
   867    867   } {}
   868    868   wal_check_journal_mode savepoint-12.5

Changes to test/schema5.test.

    26     26       CREATE TABLE t1(a,b,c, PRIMARY KEY(a) UNIQUE (a) CONSTRAINT one);
    27     27       INSERT INTO t1 VALUES(1,2,3);
    28     28       SELECT * FROM t1;
    29     29     }
    30     30   } {1 2 3}
    31     31   do_test schema5-1.2 {
    32     32     catchsql {INSERT INTO t1 VALUES(1,3,4);}
    33         -} {1 {column a is not unique}}
           33  +} {1 {PRIMARY KEY must be unique}}
    34     34   do_test schema5-1.3 {
    35     35     db eval {
    36     36       DROP TABLE t1;
    37     37       CREATE TABLE t1(a,b,c,
    38     38           CONSTRAINT one PRIMARY KEY(a) CONSTRAINT two CHECK(b<10) UNIQUE(b)
    39     39           CONSTRAINT three
    40     40       );
................................................................................
    56     56     }
    57     57   } {}
    58     58   do_test schema5-1.6 {
    59     59     catchsql {INSERT INTO t1 VALUES(1,3,4)}
    60     60   } {1 {column a is not unique}}
    61     61   do_test schema5-1.7 {
    62     62     catchsql {INSERT INTO t1 VALUES(10,2,3)}
    63         -} {1 {columns b, c are not unique}}
           63  +} {1 {PRIMARY KEY must be unique}}
    64     64   
    65     65   
    66     66   
    67     67       
    68     68   
    69     69   finish_test

Changes to test/tkt1567.test.

    36     36       UPDATE t1 SET a = a||'x' WHERE rowid%2==0;
    37     37     }
    38     38   } {}
    39     39   do_test tkt1567-1.4 {
    40     40     catchsql {
    41     41       UPDATE t1 SET a = CASE WHEN rowid<90 THEN substr(a,1,10) ELSE '9999' END;
    42     42     }
    43         -} {1 {column a is not unique}}
           43  +} {1 {PRIMARY KEY must be unique}}
    44     44   do_test tkt1567-1.5 {
    45     45     execsql {
    46     46       COMMIT;
    47     47     }
    48     48   } {}
    49     49   integrity_check tkt1567-1.6
    50     50   
           51  +do_test tkt1567-2.1 {
           52  +  execsql {
           53  +    CREATE TABLE t2(a TEXT PRIMARY KEY, rowid INT) WITHOUT rowid;
           54  +  }
           55  +  set bigstr abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
           56  +  for {set i 0} {$i<100} {incr i} {
           57  +    set x [format %5d [expr $i*2]]
           58  +    set sql "INSERT INTO t2 VALUES('$x-$bigstr', $i+1)"
           59  +    execsql $sql
           60  +  }
           61  +} {}
           62  +integrity_check tkt1567-2.2
           63  +
           64  +do_test tkt1567-2.3 {
           65  +  execsql {
           66  +    BEGIN;
           67  +    UPDATE t2 SET a = a||'x' WHERE rowid%2==0;
           68  +  }
           69  +} {}
           70  +do_test tkt1567-2.4 {
           71  +  catchsql {
           72  +    UPDATE t2 SET a = CASE WHEN rowid<90 THEN substr(a,1,10) ELSE '9999' END;
           73  +  }
           74  +} {1 {PRIMARY KEY must be unique}}
           75  +do_test tkt1567-2.5 {
           76  +  execsql {
           77  +    COMMIT;
           78  +  }
           79  +} {}
           80  +integrity_check tkt1567-2.6
           81  +
    51     82   finish_test

Changes to test/trigger2.test.

   493    493         SELECT * from tbl;
   494    494       }
   495    495     } {1 2 3}
   496    496     do_test trigger2-6.1b {
   497    497       catchsql {
   498    498         INSERT OR ABORT INTO tbl values (2, 2, 3);
   499    499       }
   500         -  } {1 {column a is not unique}}
          500  +  } {1 {PRIMARY KEY must be unique}}
   501    501     do_test trigger2-6.1c {
   502    502       execsql {
   503    503         SELECT * from tbl;
   504    504       }
   505    505     } {1 2 3}
   506    506     do_test trigger2-6.1d {
   507    507       catchsql {
   508    508         INSERT OR FAIL INTO tbl values (2, 2, 3);
   509    509       }
   510         -  } {1 {column a is not unique}}
          510  +  } {1 {PRIMARY KEY must be unique}}
   511    511     do_test trigger2-6.1e {
   512    512       execsql {
   513    513         SELECT * from tbl;
   514    514       }
   515    515     } {1 2 3 2 2 3}
   516    516     do_test trigger2-6.1f {
   517    517       execsql {
................................................................................
   519    519         SELECT * from tbl;
   520    520       }
   521    521     } {1 2 3 2 0 0}
   522    522     do_test trigger2-6.1g {
   523    523       catchsql {
   524    524         INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
   525    525       }
   526         -  } {1 {column a is not unique}}
          526  +  } {1 {PRIMARY KEY must be unique}}
   527    527     do_test trigger2-6.1h {
   528    528       execsql {
   529    529         SELECT * from tbl;
   530    530       }
   531    531     } {}
   532    532     execsql {DELETE FROM tbl}
   533    533     
................................................................................
   547    547         SELECT * from tbl;
   548    548       }
   549    549     } {1 2 10 6 3 4}
   550    550     do_test trigger2-6.2b {
   551    551       catchsql {
   552    552         UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
   553    553       }
   554         -  } {1 {column a is not unique}}
          554  +  } {1 {PRIMARY KEY must be unique}}
   555    555     do_test trigger2-6.2c {
   556    556       execsql {
   557    557         SELECT * from tbl;
   558    558       }
   559    559     } {1 2 10 6 3 4}
   560    560     do_test trigger2-6.2d {
   561    561       catchsql {
   562    562         UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
   563    563       }
   564         -  } {1 {column a is not unique}}
          564  +  } {1 {PRIMARY KEY must be unique}}
   565    565     do_test trigger2-6.2e {
   566    566       execsql {
   567    567         SELECT * from tbl;
   568    568       }
   569    569     } {4 2 10 6 3 4}
   570    570     do_test trigger2-6.2f.1 {
   571    571       execsql {
................................................................................
   579    579         SELECT * FROM tbl;
   580    580       }
   581    581     } {1 3 10 2 3 4}
   582    582     do_test trigger2-6.2g {
   583    583       catchsql {
   584    584         UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
   585    585       }
   586         -  } {1 {column a is not unique}}
          586  +  } {1 {PRIMARY KEY must be unique}}
   587    587     do_test trigger2-6.2h {
   588    588       execsql {
   589    589         SELECT * from tbl;
   590    590       }
   591    591     } {4 2 3 6 3 4}
   592    592     execsql {
   593    593       DROP TABLE tbl;

Changes to test/unique.test.

    43     43       INSERT INTO t1(a,b,c) VALUES(1,2,3)
    44     44     }
    45     45   } {0 {}}
    46     46   do_test unique-1.3 {
    47     47     catchsql {
    48     48       INSERT INTO t1(a,b,c) VALUES(1,3,4)
    49     49     }
    50         -} {1 {column a is not unique}}
    51         -verify_ex_errcode unique-1.3b SQLITE_CONSTRAINT_UNIQUE
           50  +} {1 {PRIMARY KEY must be unique}}
           51  +verify_ex_errcode unique-1.3b SQLITE_CONSTRAINT_PRIMARYKEY
    52     52   do_test unique-1.4 {
    53     53     execsql {
    54     54       SELECT * FROM t1 ORDER BY a;
    55     55     }
    56     56   } {1 2 3}
    57     57   do_test unique-1.5 {
    58     58     catchsql {

Changes to test/without_rowid1.test.

    48     48   
    49     49   # Trying to insert a duplicate PRIMARY KEY fails.
    50     50   #
    51     51   do_test without_rowid1-1.21 {
    52     52     catchsql {
    53     53       INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard');
    54     54     }
    55         -} {1 {columns c, a are not unique}}
           55  +} {1 {PRIMARY KEY must be unique}}
    56     56   
    57     57   # REPLACE INTO works, however.
    58     58   #
    59     59   do_execsql_test without_rowid1-1.22 {
    60     60     REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard');
    61     61     SELECT *, '|' FROM t1 ORDER BY c, a;
    62     62   } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |}

Changes to test/without_rowid3.test.

   371    371   without_rowid3-2-test 62 0   "DELETE FROM leaf"
   372    372   without_rowid3-2-test 63 0   "DELETE FROM node"
   373    373   without_rowid3-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
   374    374   without_rowid3-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
   375    375   without_rowid3-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
   376    376   do_test without_rowid3-2-test-67 {
   377    377     catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
   378         -} {1 {column nodeid is not unique}}
          378  +} {1 {PRIMARY KEY must be unique}}
   379    379   without_rowid3-2-test 68 0 "COMMIT"           FKV
   380    380   without_rowid3-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
   381    381   without_rowid3-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
   382    382   without_rowid3-2-test 71 0 "COMMIT"
   383    383   
   384    384   without_rowid3-2-test 72 0 "BEGIN"
   385    385   without_rowid3-2-test 73 1   "DELETE FROM node"

Changes to test/without_rowid4.test.

   498    498         SELECT * from tbl;
   499    499       }
   500    500     } {1 2 3}
   501    501     do_test without_rowid4-6.1b {
   502    502       catchsql {
   503    503         INSERT OR ABORT INTO tbl values (2, 2, 3);
   504    504       }
   505         -  } {1 {column a is not unique}}
          505  +  } {1 {PRIMARY KEY must be unique}}
   506    506     do_test without_rowid4-6.1c {
   507    507       execsql {
   508    508         SELECT * from tbl;
   509    509       }
   510    510     } {1 2 3}
   511    511     do_test without_rowid4-6.1d {
   512    512       catchsql {
   513    513         INSERT OR FAIL INTO tbl values (2, 2, 3);
   514    514       }
   515         -  } {1 {column a is not unique}}
          515  +  } {1 {PRIMARY KEY must be unique}}
   516    516     do_test without_rowid4-6.1e {
   517    517       execsql {
   518    518         SELECT * from tbl;
   519    519       }
   520    520     } {1 2 3 2 2 3}
   521    521     do_test without_rowid4-6.1f {
   522    522       execsql {
................................................................................
   524    524         SELECT * from tbl;
   525    525       }
   526    526     } {1 2 3 2 0 0}
   527    527     do_test without_rowid4-6.1g {
   528    528       catchsql {
   529    529         INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
   530    530       }
   531         -  } {1 {column a is not unique}}
          531  +  } {1 {PRIMARY KEY must be unique}}
   532    532     do_test without_rowid4-6.1h {
   533    533       execsql {
   534    534         SELECT * from tbl;
   535    535       }
   536    536     } {}
   537    537     execsql {DELETE FROM tbl}
   538    538     
................................................................................
   552    552         SELECT * from tbl;
   553    553       }
   554    554     } {1 2 10 6 3 4}
   555    555     do_test without_rowid4-6.2b {
   556    556       catchsql {
   557    557         UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
   558    558       }
   559         -  } {1 {column a is not unique}}
          559  +  } {1 {PRIMARY KEY must be unique}}
   560    560     do_test without_rowid4-6.2c {
   561    561       execsql {
   562    562         SELECT * from tbl;
   563    563       }
   564    564     } {1 2 10 6 3 4}
   565    565     do_test without_rowid4-6.2d {
   566    566       catchsql {
   567    567         UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
   568    568       }
   569         -  } {1 {column a is not unique}}
          569  +  } {1 {PRIMARY KEY must be unique}}
   570    570     do_test without_rowid4-6.2e {
   571    571       execsql {
   572    572         SELECT * from tbl;
   573    573       }
   574    574     } {4 2 10 6 3 4}
   575    575     do_test without_rowid4-6.2f.1 {
   576    576       execsql {
................................................................................
   584    584         SELECT * FROM tbl;
   585    585       }
   586    586     } {1 3 10 2 3 4}
   587    587     do_test without_rowid4-6.2g {
   588    588       catchsql {
   589    589         UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
   590    590       }
   591         -  } {1 {column a is not unique}}
          591  +  } {1 {PRIMARY KEY must be unique}}
   592    592     do_test without_rowid4-6.2h {
   593    593       execsql {
   594    594         SELECT * from tbl;
   595    595       }
   596    596     } {4 2 3 6 3 4}
   597    597     execsql {
   598    598       DROP TABLE tbl;