/ Check-in [c1220b1a]
Login

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

Overview
Comment:Fix some problems with foreign key processing within REPLACE ops on WITHOUT ROWID tables with no triggers or auxiliary indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | replace-fix
Files: files | file ages | folders
SHA1:c1220b1af62629d7fc2178512786d613cd7ea711
User & Date: dan 2017-01-05 06:57:42
Context
2017-01-05
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
06:57
Fix some problems with foreign key processing within REPLACE ops on WITHOUT ROWID tables with no triggers or auxiliary indexes. Closed-Leaf check-in: c1220b1a user: dan tags: replace-fix
2017-01-04
22:02
Improved the comment on the block of code the provides the performance optimization originally added by check-in [925840cfdb]. The original check-in omitted condition 4, which was the cause of bug [30027b613b]. check-in: c6506b82 user: drh tags: replace-fix
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1550
1551
1552
1553
1554
1555
1556

1557
1558
1559
1560
1561
1562


1563
1564
1565
1566
1567
1568
1569
    }

    /* Collision detection may be omitted if all of the following are true:
    **   (1) The conflict resolution algorithm is REPLACE
    **   (2) The table is a WITHOUT ROWID table
    **   (3) There are no secondary indexes on the table
    **   (4) No delete triggers need to be fired if there is a conflict

    */ 
    if( (ix==0 && pIdx->pNext==0)                   /* Condition 3 */
     && pPk==pIdx                                   /* Condition 2 */
     && onError==OE_Replace                         /* Condition 1 */
     && ( 0==(db->flags&SQLITE_RecTriggers) ||      /* Condition 4 */
          0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0))


    ){
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
      continue;
    }

    /* Check to see if the new index entry will be unique */
    sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,







>






>
>







1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
    }

    /* Collision detection may be omitted if all of the following are true:
    **   (1) The conflict resolution algorithm is REPLACE
    **   (2) The table is a WITHOUT ROWID table
    **   (3) There are no secondary indexes on the table
    **   (4) No delete triggers need to be fired if there is a conflict
    **   (5) No FK constraint counters need to be updated if a conflict occurs.
    */ 
    if( (ix==0 && pIdx->pNext==0)                   /* Condition 3 */
     && pPk==pIdx                                   /* Condition 2 */
     && onError==OE_Replace                         /* Condition 1 */
     && ( 0==(db->flags&SQLITE_RecTriggers) ||      /* Condition 4 */
          0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0))
     && ( 0==(db->flags&SQLITE_ForeignKeys) ||      /* Condition 5 */
         (0==pTab->pFKey && 0==sqlite3FkReferences(pTab)))
    ){
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
      continue;
    }

    /* Check to see if the new index entry will be unique */
    sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,

Changes to test/fkey8.test.

97
98
99
100
101
102
103





104

























































105
    set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
    set ret [uses_stmt_journal $stmt]
    sqlite3_finalize $stmt
    set ret
  } $use_stmt
}
































































finish_test







>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
    set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
    set ret [uses_stmt_journal $stmt]
    sqlite3_finalize $stmt
    set ret
  } $use_stmt
}

#-------------------------------------------------------------------------
# The following tests check that foreign key constaint counters are
# correctly updated for any implicit DELETE operations that occur
# when a REPLACE command is executed against a WITHOUT ROWID table
# that has no triggers or auxiliary indexes.
#
reset_db
do_execsql_test 2.1.0 {
  PRAGMA foreign_keys = on;
  CREATE TABLE p1(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);

  INSERT INTO p1 VALUES(1, 'one');
  INSERT INTO p1 VALUES(2, 'two');
  INSERT INTO c1 VALUES(1);
  INSERT INTO c1 VALUES(2);
}

do_catchsql_test 2.1.2 {
  BEGIN;
    DELETE FROM p1 WHERE a=1;
    INSERT OR REPLACE INTO p1 VALUES(2, 'two');
  COMMIT;
} {1 {FOREIGN KEY constraint failed}}

reset_db
do_execsql_test 2.2.0 {
  PRAGMA foreign_keys = on;
  CREATE TABLE p2(a PRIMARY KEY, b);
  CREATE TABLE c2(
    x PRIMARY KEY,
    y REFERENCES p2 DEFERRABLE INITIALLY DEFERRED
  ) WITHOUT ROWID;
}

do_catchsql_test 2.2.1 {
  BEGIN;
    INSERT INTO c2 VALUES(13, 13);
    INSERT OR REPLACE INTO c2 VALUES(13, 13);
    DELETE FROM c2;
  COMMIT;
} {0 {}}

reset_db
do_execsql_test 2.3.0 {
  PRAGMA foreign_keys = on;
  CREATE TABLE p3(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE TABLE c3(x REFERENCES p3);

  INSERT INTO p3 VALUES(1, 'one');
  INSERT INTO p3 VALUES(2, 'two');
  INSERT INTO c3 VALUES(1);
  INSERT INTO c3 VALUES(2);

  CREATE TRIGGER p3d AFTER DELETE ON p3 WHEN old.a=1 BEGIN
    INSERT OR REPLACE INTO p3 VALUES(2, 'three');
  END;
}

do_catchsql_test 2.3.1 {
  DELETE FROM p3 WHERE a=1
} {1 {FOREIGN KEY constraint failed}}

finish_test