/ Check-in [eea1e7aa]
Login

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

Overview
Comment:If an AFTER DELETE trigger fires when a conflict row is deleted by REPLACE conflict resolution, make sure the conflict really has been resolved and that the trigger did not recreate the row before continuing. Ticket [a8a4847a2d96f5de]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: eea1e7aa57e74c4329003f4550168e2aed9e33d2301a3ba84b10781a9cebbc1b
User & Date: drh 2019-10-16 14:56:03
References
2019-10-21
23:41
Add a VdbeCoverage() macro that was omitted from check-in [eea1e7aa57e74c43]. check-in: cd2317d0 user: drh tags: trunk
Context
2019-10-16
17:46
Enhancements to SQL query normalization for UPDATE statements. check-in: bba975c7 user: mistachkin tags: trunk
14:56
If an AFTER DELETE trigger fires when a conflict row is deleted by REPLACE conflict resolution, make sure the conflict really has been resolved and that the trigger did not recreate the row before continuing. Ticket [a8a4847a2d96f5de] check-in: eea1e7aa user: drh tags: trunk
2019-10-15
19:01
Formatting change on a multi-line conditional, for improved clarity. No logic changes. check-in: 7248e347 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/insert.c.

1578
1579
1580
1581
1582
1583
1584


1585
1586
1587
1588
1589
1590
1591
....
1825
1826
1827
1828
1829
1830
1831

1832
1833
1834
1835
1836
1837

1838
1839
1840
1841





1842
1843
1844
1845
1846
1847
1848
        if( db->flags&SQLITE_RecTriggers ){
          pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
        }
        if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){
          sqlite3MultiWrite(pParse);
          sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
                                   regNewData, 1, 0, OE_Replace, 1, -1);


        }else{
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
          assert( HasRowid(pTab) );
          /* This OP_Delete opcode fires the pre-update-hook only. It does
          ** not modify the b-tree. It is more efficient to let the coming
          ** OP_Insert replace the existing entry than it is to delete the
          ** existing entry and then insert a new one. */
................................................................................
      case OE_Ignore: {
        testcase( onError==OE_Ignore );
        sqlite3VdbeGoto(v, ignoreDest);
        break;
      }
      default: {
        Trigger *pTrigger = 0;

        assert( onError==OE_Replace );
        if( db->flags&SQLITE_RecTriggers ){
          pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
        }
        if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){
          sqlite3MultiWrite(pParse);

        }
        sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
            regR, nPkField, 0, OE_Replace,
            (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);





        seenReplace = 1;
        break;
      }
    }
    if( pUpIdx==pIdx ){
      sqlite3VdbeGoto(v, upsertJump+1);
      sqlite3VdbeJumpHere(v, upsertBypass);







>
>







 







>






>




>
>
>
>
>







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

Changes to test/insert.test.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
...
470
471
472
473
474
475
476
477




478












































479
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.31 2007/04/05 11:25:59 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
do_execsql_test insert-14.1 {
  DROP TABLE IF EXISTS t14;
  CREATE TABLE t14(x INTEGER PRIMARY KEY);
  INSERT INTO t14 VALUES(CASE WHEN 1 THEN null END);
  SELECT x FROM t14;
} {1}

integrity_check insert-99.0

# 2019-08-12.
#
do_execsql_test insert-15.1 {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
................................................................................
  CREATE TABLE t2(a, b);
  INSERT INTO t2 VALUES(4, randomblob(31000));
  INSERT INTO t2 VALUES(4, randomblob(32000));
  INSERT INTO t2 VALUES(4, randomblob(33000));
  REPLACE INTO t1 SELECT a, b FROM t2;
  SELECT a, length(b) FROM t1;
} {4 33000}


















































finish_test
|












<







 







|







 








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

1
2
3
4
5
6
7
8
9
10
11
12
13

14
15
16
17
18
19
20
...
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
...
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
# 2001-09-15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
do_execsql_test insert-14.1 {
  DROP TABLE IF EXISTS t14;
  CREATE TABLE t14(x INTEGER PRIMARY KEY);
  INSERT INTO t14 VALUES(CASE WHEN 1 THEN null END);
  SELECT x FROM t14;
} {1}

integrity_check insert-14.2

# 2019-08-12.
#
do_execsql_test insert-15.1 {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
................................................................................
  CREATE TABLE t2(a, b);
  INSERT INTO t2 VALUES(4, randomblob(31000));
  INSERT INTO t2 VALUES(4, randomblob(32000));
  INSERT INTO t2 VALUES(4, randomblob(33000));
  REPLACE INTO t1 SELECT a, b FROM t2;
  SELECT a, length(b) FROM t1;
} {4 33000}

# 2019-10-16
# ticket https://www.sqlite.org/src/info/a8a4847a2d96f5de
# On a REPLACE INTO, if an AFTER trigger adds back the conflicting
# row, you can end up with the wrong number of rows in an index.
#
db close
sqlite3 db :memory:
do_catchsql_test insert-16.1 {
  PRAGMA recursive_triggers = true;
  CREATE TABLE t0(c0,c1);
  CREATE UNIQUE INDEX i0 ON t0(c0);
  INSERT INTO t0(c0,c1) VALUES(123,1);
  CREATE TRIGGER tr0 AFTER DELETE ON t0
  BEGIN
    INSERT INTO t0 VALUES(123,2);
  END;
  REPLACE INTO t0(c0,c1) VALUES(123,3);
} {1 {UNIQUE constraint failed: t0.c0}}
do_execsql_test insert-16.2 {
  SELECT * FROM t0;
} {123 1}
integrity_check insert-16.3
do_catchsql_test insert-16.4 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  CREATE INDEX t1b ON t1(b);
  INSERT INTO t1 VALUES(1, 'one');
  CREATE TRIGGER tr3 AFTER DELETE ON t1 BEGIN
    INSERT INTO t1 VALUES(1, 'three');
  END;
  REPLACE INTO t1 VALUES(1, 'two');
} {1 {UNIQUE constraint failed: t1.a}}
integrity_check insert-16.5
do_catchsql_test insert-16.6 {
  PRAGMA foreign_keys = 1;
  CREATE TABLE p1(a, b UNIQUE);
  CREATE TABLE c1(c, d REFERENCES p1(b) ON DELETE CASCADE);
  CREATE TRIGGER tr6 AFTER DELETE ON c1 BEGIN
    INSERT INTO p1 VALUES(4, 1);
  END;
  INSERT INTO p1 VALUES(1, 1);
  INSERT INTO c1 VALUES(2, 1);
  REPLACE INTO p1 VALUES(3, 1);
} {1 {UNIQUE constraint failed: p1.b}}
integrity_check insert-16.7



  

finish_test