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

Changes to src/insert.c.

  1578   1578           if( db->flags&SQLITE_RecTriggers ){
  1579   1579             pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
  1580   1580           }
  1581   1581           if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){
  1582   1582             sqlite3MultiWrite(pParse);
  1583   1583             sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
  1584   1584                                      regNewData, 1, 0, OE_Replace, 1, -1);
         1585  +          sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
         1586  +          sqlite3RowidConstraint(pParse, OE_Abort, pTab);
  1585   1587           }else{
  1586   1588   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
  1587   1589             assert( HasRowid(pTab) );
  1588   1590             /* This OP_Delete opcode fires the pre-update-hook only. It does
  1589   1591             ** not modify the b-tree. It is more efficient to let the coming
  1590   1592             ** OP_Insert replace the existing entry than it is to delete the
  1591   1593             ** existing entry and then insert a new one. */
................................................................................
  1825   1827         case OE_Ignore: {
  1826   1828           testcase( onError==OE_Ignore );
  1827   1829           sqlite3VdbeGoto(v, ignoreDest);
  1828   1830           break;
  1829   1831         }
  1830   1832         default: {
  1831   1833           Trigger *pTrigger = 0;
         1834  +        int bRetryConstraintCheck = 0;
  1832   1835           assert( onError==OE_Replace );
  1833   1836           if( db->flags&SQLITE_RecTriggers ){
  1834   1837             pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
  1835   1838           }
  1836   1839           if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){
  1837   1840             sqlite3MultiWrite(pParse);
         1841  +          bRetryConstraintCheck = 1;
  1838   1842           }
  1839   1843           sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
  1840   1844               regR, nPkField, 0, OE_Replace,
  1841   1845               (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
         1846  +        if( bRetryConstraintCheck ){
         1847  +          sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
         1848  +                               regIdx, pIdx->nKeyCol); VdbeCoverage(v);
         1849  +          sqlite3UniqueConstraint(pParse, OE_Abort, pIdx);
         1850  +        }
  1842   1851           seenReplace = 1;
  1843   1852           break;
  1844   1853         }
  1845   1854       }
  1846   1855       if( pUpIdx==pIdx ){
  1847   1856         sqlite3VdbeGoto(v, upsertJump+1);
  1848   1857         sqlite3VdbeJumpHere(v, upsertBypass);

Changes to test/insert.test.

     1         -# 2001 September 15
            1  +# 2001-09-15
     2      2   #
     3      3   # The author disclaims copyright to this source code.  In place of
     4      4   # a legal notice, here is a blessing:
     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the INSERT statement.
    13     13   #
    14         -# $Id: insert.test,v 1.31 2007/04/05 11:25:59 drh Exp $
    15     14   
    16     15   set testdir [file dirname $argv0]
    17     16   source $testdir/tester.tcl
    18     17   
    19     18   # Try to insert into a non-existant table.
    20     19   #
    21     20   do_test insert-1.1 {
................................................................................
   454    453   do_execsql_test insert-14.1 {
   455    454     DROP TABLE IF EXISTS t14;
   456    455     CREATE TABLE t14(x INTEGER PRIMARY KEY);
   457    456     INSERT INTO t14 VALUES(CASE WHEN 1 THEN null END);
   458    457     SELECT x FROM t14;
   459    458   } {1}
   460    459   
   461         -integrity_check insert-99.0
          460  +integrity_check insert-14.2
   462    461   
   463    462   # 2019-08-12.
   464    463   #
   465    464   do_execsql_test insert-15.1 {
   466    465     DROP TABLE IF EXISTS t1;
   467    466     DROP TABLE IF EXISTS t2;
   468    467     CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
................................................................................
   471    470     INSERT INTO t2 VALUES(4, randomblob(31000));
   472    471     INSERT INTO t2 VALUES(4, randomblob(32000));
   473    472     INSERT INTO t2 VALUES(4, randomblob(33000));
   474    473     REPLACE INTO t1 SELECT a, b FROM t2;
   475    474     SELECT a, length(b) FROM t1;
   476    475   } {4 33000}
   477    476   
          477  +# 2019-10-16
          478  +# ticket https://www.sqlite.org/src/info/a8a4847a2d96f5de
          479  +# On a REPLACE INTO, if an AFTER trigger adds back the conflicting
          480  +# row, you can end up with the wrong number of rows in an index.
          481  +#
          482  +db close
          483  +sqlite3 db :memory:
          484  +do_catchsql_test insert-16.1 {
          485  +  PRAGMA recursive_triggers = true;
          486  +  CREATE TABLE t0(c0,c1);
          487  +  CREATE UNIQUE INDEX i0 ON t0(c0);
          488  +  INSERT INTO t0(c0,c1) VALUES(123,1);
          489  +  CREATE TRIGGER tr0 AFTER DELETE ON t0
          490  +  BEGIN
          491  +    INSERT INTO t0 VALUES(123,2);
          492  +  END;
          493  +  REPLACE INTO t0(c0,c1) VALUES(123,3);
          494  +} {1 {UNIQUE constraint failed: t0.c0}}
          495  +do_execsql_test insert-16.2 {
          496  +  SELECT * FROM t0;
          497  +} {123 1}
          498  +integrity_check insert-16.3
          499  +do_catchsql_test insert-16.4 {
          500  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          501  +  CREATE INDEX t1b ON t1(b);
          502  +  INSERT INTO t1 VALUES(1, 'one');
          503  +  CREATE TRIGGER tr3 AFTER DELETE ON t1 BEGIN
          504  +    INSERT INTO t1 VALUES(1, 'three');
          505  +  END;
          506  +  REPLACE INTO t1 VALUES(1, 'two');
          507  +} {1 {UNIQUE constraint failed: t1.a}}
          508  +integrity_check insert-16.5
          509  +do_catchsql_test insert-16.6 {
          510  +  PRAGMA foreign_keys = 1;
          511  +  CREATE TABLE p1(a, b UNIQUE);
          512  +  CREATE TABLE c1(c, d REFERENCES p1(b) ON DELETE CASCADE);
          513  +  CREATE TRIGGER tr6 AFTER DELETE ON c1 BEGIN
          514  +    INSERT INTO p1 VALUES(4, 1);
          515  +  END;
          516  +  INSERT INTO p1 VALUES(1, 1);
          517  +  INSERT INTO c1 VALUES(2, 1);
          518  +  REPLACE INTO p1 VALUES(3, 1);
          519  +} {1 {UNIQUE constraint failed: p1.b}}
          520  +integrity_check insert-16.7
          521  +
          522  +
          523  +
          524  +  
   478    525   
   479    526   finish_test