Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| 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 |
|
2019-10-21
| ||
| 23:41 | Add a VdbeCoverage() macro that was omitted from check-in [eea1e7aa57e74c43]. (check-in: cd2317d0 user: drh tags: trunk) | |
|
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 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