/ Check-in [8a788594]
Login

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

Overview
Comment:Fix problems in trigger and foreign key handling when doing REPLACE on a WITHOUT ROWID table that has no secondary indexes. Fix for ticket [30027b613b4].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.16
Files: files | file ages | folders
SHA1: 8a788594e2097ab5f3fe32f1d6b58caef2887d20
User & Date: drh 2017-01-05 13:56:17
Context
2017-01-05
14:05
Increase the version number to 3.16.2 check-in: 9592cbcf user: drh tags: branch-3.16
13:56
Fix problems in trigger and foreign key handling when doing REPLACE on a WITHOUT ROWID table that has no secondary indexes. Fix for ticket [30027b613b4]. check-in: 8a788594 user: drh tags: branch-3.16
13:52
Ensure that the sqlite3_value_text() interface returns a buffer that is long enough to hold the complete string plus the zero terminator even when the input is a zeroblob. Fix for a problem detected by OSS-Fuzz. check-in: ca185808 user: drh tags: branch-3.16
13:50
Fix problems in trigger and foreign key handling when doing REPLACE on a WITHOUT ROWID table that has no secondary indexes. Fix for ticket [30027b613b4]. check-in: 571f166e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1545   1545       }
  1546   1546       if( overrideError!=OE_Default ){
  1547   1547         onError = overrideError;
  1548   1548       }else if( onError==OE_Default ){
  1549   1549         onError = OE_Abort;
  1550   1550       }
  1551   1551   
  1552         -    if( ix==0 && pPk==pIdx && onError==OE_Replace && pPk->pNext==0 ){
         1552  +    /* Collision detection may be omitted if all of the following are true:
         1553  +    **   (1) The conflict resolution algorithm is REPLACE
         1554  +    **   (2) The table is a WITHOUT ROWID table
         1555  +    **   (3) There are no secondary indexes on the table
         1556  +    **   (4) No delete triggers need to be fired if there is a conflict
         1557  +    **   (5) No FK constraint counters need to be updated if a conflict occurs.
         1558  +    */ 
         1559  +    if( (ix==0 && pIdx->pNext==0)                   /* Condition 3 */
         1560  +     && pPk==pIdx                                   /* Condition 2 */
         1561  +     && onError==OE_Replace                         /* Condition 1 */
         1562  +     && ( 0==(db->flags&SQLITE_RecTriggers) ||      /* Condition 4 */
         1563  +          0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0))
         1564  +     && ( 0==(db->flags&SQLITE_ForeignKeys) ||      /* Condition 5 */
         1565  +         (0==pTab->pFKey && 0==sqlite3FkReferences(pTab)))
         1566  +    ){
  1553   1567         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1554   1568         continue;
  1555   1569       }
  1556   1570   
  1557         -    
  1558   1571       /* Check to see if the new index entry will be unique */
  1559   1572       sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
  1560   1573                            regIdx, pIdx->nKeyCol); VdbeCoverage(v);
  1561   1574   
  1562   1575       /* Generate code to handle collisions */
  1563   1576       regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField);
  1564   1577       if( isUpdate || onError==OE_Replace ){

Changes to test/fkey8.test.

    97     97       set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
    98     98       set ret [uses_stmt_journal $stmt]
    99     99       sqlite3_finalize $stmt
   100    100       set ret
   101    101     } $use_stmt
   102    102   }
   103    103   
          104  +#-------------------------------------------------------------------------
          105  +# The following tests check that foreign key constaint counters are
          106  +# correctly updated for any implicit DELETE operations that occur
          107  +# when a REPLACE command is executed against a WITHOUT ROWID table
          108  +# that has no triggers or auxiliary indexes.
          109  +#
          110  +reset_db
          111  +do_execsql_test 2.1.0 {
          112  +  PRAGMA foreign_keys = on;
          113  +  CREATE TABLE p1(a PRIMARY KEY, b) WITHOUT ROWID;
          114  +  CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);
          115  +
          116  +  INSERT INTO p1 VALUES(1, 'one');
          117  +  INSERT INTO p1 VALUES(2, 'two');
          118  +  INSERT INTO c1 VALUES(1);
          119  +  INSERT INTO c1 VALUES(2);
          120  +}
          121  +
          122  +do_catchsql_test 2.1.2 {
          123  +  BEGIN;
          124  +    DELETE FROM p1 WHERE a=1;
          125  +    INSERT OR REPLACE INTO p1 VALUES(2, 'two');
          126  +  COMMIT;
          127  +} {1 {FOREIGN KEY constraint failed}}
          128  +
          129  +reset_db
          130  +do_execsql_test 2.2.0 {
          131  +  PRAGMA foreign_keys = on;
          132  +  CREATE TABLE p2(a PRIMARY KEY, b);
          133  +  CREATE TABLE c2(
          134  +    x PRIMARY KEY,
          135  +    y REFERENCES p2 DEFERRABLE INITIALLY DEFERRED
          136  +  ) WITHOUT ROWID;
          137  +}
          138  +
          139  +do_catchsql_test 2.2.1 {
          140  +  BEGIN;
          141  +    INSERT INTO c2 VALUES(13, 13);
          142  +    INSERT OR REPLACE INTO c2 VALUES(13, 13);
          143  +    DELETE FROM c2;
          144  +  COMMIT;
          145  +} {0 {}}
          146  +
          147  +reset_db
          148  +do_execsql_test 2.3.0 {
          149  +  PRAGMA foreign_keys = on;
          150  +  CREATE TABLE p3(a PRIMARY KEY, b) WITHOUT ROWID;
          151  +  CREATE TABLE c3(x REFERENCES p3);
          152  +
          153  +  INSERT INTO p3 VALUES(1, 'one');
          154  +  INSERT INTO p3 VALUES(2, 'two');
          155  +  INSERT INTO c3 VALUES(1);
          156  +  INSERT INTO c3 VALUES(2);
          157  +
          158  +  CREATE TRIGGER p3d AFTER DELETE ON p3 WHEN old.a=1 BEGIN
          159  +    INSERT OR REPLACE INTO p3 VALUES(2, 'three');
          160  +  END;
          161  +}
          162  +
          163  +do_catchsql_test 2.3.1 {
          164  +  DELETE FROM p3 WHERE a=1
          165  +} {1 {FOREIGN KEY constraint failed}}
   104    166   
   105    167   finish_test

Added test/triggerF.test.

            1  +# 2017 January 4
            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  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix triggerF
           16  +ifcapable {!trigger} {
           17  +  finish_test
           18  +  return
           19  +}
           20  +
           21  +
           22  +foreach {tn sql log} {
           23  +  1 { } { }
           24  +
           25  +  2 { 
           26  +    CREATE TRIGGER trd AFTER DELETE ON t1 BEGIN
           27  +      INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1));
           28  +    END;
           29  +  } {1one2 2two1 3three1}
           30  +
           31  +  3 { 
           32  +    CREATE TRIGGER trd BEFORE DELETE ON t1 BEGIN
           33  +      INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1));
           34  +    END;
           35  +  } {1one3 2two2 3three2}
           36  +
           37  +  4 { 
           38  +    CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
           39  +      INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1));
           40  +    END;
           41  +    CREATE TRIGGER tr2 BEFORE DELETE ON t1 BEGIN
           42  +      INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1));
           43  +    END;
           44  +  } {1one3 1one2 2two2 2two1 3three2 3three1}
           45  +
           46  +} {
           47  +  reset_db
           48  +  do_execsql_test 1.$tn.0 {
           49  +    PRAGMA recursive_triggers = on;
           50  +    CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID;
           51  +    CREATE TABLE log(t);
           52  +  }
           53  +  
           54  +  execsql $sql
           55  +
           56  +  do_execsql_test 1.$tn.1 {
           57  +    INSERT INTO t1 VALUES(1, 'one');
           58  +    INSERT INTO t1 VALUES(2, 'two');
           59  +    INSERT INTO t1 VALUES(3, 'three');
           60  +
           61  +    DELETE FROM t1 WHERE a=1;
           62  +    INSERT OR REPLACE INTO t1 VALUES(2, 'three');
           63  +    UPDATE OR REPLACE t1 SET a=3 WHERE a=2;
           64  +  }
           65  +
           66  +  do_execsql_test 1.$tn.2 {
           67  +    SELECT * FROM log ORDER BY rowid;
           68  +  } $log
           69  +}
           70  +
           71  +finish_test
           72  +