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: |
eea1e7aa57e74c4329003f4550168e2a |
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
Changes to src/insert.c.
︙ | ︙ | |||
1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 | 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. */ | > > | 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 | 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. */ |
︙ | ︙ | |||
1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 | 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); | > > > > > > > | 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 | 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 | # 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 { |
︙ | ︙ | |||
454 455 456 457 458 459 460 | 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} | | > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 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 | 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 INDEX i1 ON t1(b); 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 |