Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -1227,11 +1227,13 @@ int nCol; /* Number of columns */ int onError; /* Conflict resolution strategy */ int j1; /* Addresss of jump instruction */ int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */ int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */ - u8 isUpdate; + int ipkTop = 0; /* Top of the rowid change constraint check */ + int ipkBottom = 0; /* Bottom of the rowid change constraint check */ + u8 isUpdate; /* True if this is an UPDATE operation */ isUpdate = regOldData!=0; db = pParse->db; v = sqlite3GetVdbe(pParse); assert( v!=0 ); @@ -1342,10 +1344,24 @@ /* pkChng!=0 does not mean that the rowid has change, only that ** it might have changed. Skip the conflict logic below if the rowid ** is unchanged. */ sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData); } + + /* If the response to a rowid conflict is REPLACE but the response + ** to some other UNIQUE constraint is FAIL or IGNORE, then we need + ** to defer the running of the rowid conflict checking until after + ** the UNIQUE constraints have run. + */ + if( onError==OE_Replace && overrideError!=OE_Replace ){ + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ + if( pIdx->onError==OE_Ignore || pIdx->onError==OE_Fail ){ + ipkTop = sqlite3VdbeAddOp0(v, OP_Goto); + break; + } + } + } /* Check to see if the new rowid already exists in the table. Skip ** the following conflict logic if it does not. */ sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData); @@ -1398,16 +1414,20 @@ } seenReplace = 1; break; } case OE_Ignore: { - assert( seenReplace==0 ); + /*assert( seenReplace==0 );*/ sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); break; } } sqlite3VdbeResolveLabel(v, addrRowidOk); + if( ipkTop ){ + ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto); + sqlite3VdbeJumpHere(v, ipkTop); + } } /* Test all UNIQUE constraints by creating entries for each UNIQUE ** index and making sure that duplicate entries do not already exist. ** Compute the revised record entries for indices as we go. @@ -1473,14 +1493,10 @@ if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ onError = OE_Abort; } - if( seenReplace ){ - if( onError==OE_Ignore ) onError = OE_Replace; - else if( onError==OE_Fail ) onError = OE_Abort; - } /* Check to see if the new index entry will be unique */ regR = sqlite3GetTempRange(pParse, nPkField); sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, regIdx, pIdx->nKeyCol); @@ -1542,11 +1558,10 @@ case OE_Fail: { sqlite3UniqueConstraint(pParse, onError, pIdx); break; } case OE_Ignore: { - assert( seenReplace==0 ); sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); break; } default: { Trigger *pTrigger = 0; @@ -1562,10 +1577,14 @@ } } sqlite3VdbeResolveLabel(v, addrUniqueOk); sqlite3ReleaseTempRange(pParse, regR, nPkField); } + if( ipkTop ){ + sqlite3VdbeAddOp2(v, OP_Goto, 0, ipkTop+1); + sqlite3VdbeJumpHere(v, ipkBottom); + } if( pbMayReplace ){ *pbMayReplace = seenReplace; } VdbeModuleComment((v, "END: GenCnstCks()")); ADDED test/conflict3.test Index: test/conflict3.test ================================================================== --- /dev/null +++ test/conflict3.test @@ -0,0 +1,356 @@ +# 2013-11-05 +# +# 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. +# +# This file implements tests for the conflict resolution extension +# to SQLite. +# +# This file focuses on making sure that combinations of REPLACE, +# IGNORE, and FAIL conflict resolution play well together. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +ifcapable !conflict { + finish_test + return +} + +do_execsql_test conflict-1.1 { + CREATE TABLE t1( + a INTEGER PRIMARY KEY ON CONFLICT REPLACE, + b UNIQUE ON CONFLICT IGNORE, + c UNIQUE ON CONFLICT FAIL + ); + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-1.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-1.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-1.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Replete the tests above, but this time on a table non-INTEGER primary key. +# +do_execsql_test conflict-2.1 { + DROP TABLE t1; + CREATE TABLE t1( + a INT PRIMARY KEY ON CONFLICT REPLACE, + b UNIQUE ON CONFLICT IGNORE, + c UNIQUE ON CONFLICT FAIL + ); + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-2.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-2.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-2.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Replete again on a WITHOUT ROWID table. +# +do_execsql_test conflict-3.1 { + DROP TABLE t1; + CREATE TABLE t1( + a INT PRIMARY KEY ON CONFLICT REPLACE, + b UNIQUE ON CONFLICT IGNORE, + c UNIQUE ON CONFLICT FAIL + ) WITHOUT ROWID; + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-3.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-3.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-3.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Arrange the table rows in a different order and repeat. +# +do_execsql_test conflict-4.1 { + DROP TABLE t1; + CREATE TABLE t1( + b UNIQUE ON CONFLICT IGNORE, + c UNIQUE ON CONFLICT FAIL, + a INT PRIMARY KEY ON CONFLICT REPLACE + ) WITHOUT ROWID; + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-4.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-4.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-4.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Arrange the table rows in a different order and repeat. +# +do_execsql_test conflict-5.1 { + DROP TABLE t1; + CREATE TABLE t1( + b UNIQUE ON CONFLICT IGNORE, + a INT PRIMARY KEY ON CONFLICT REPLACE, + c UNIQUE ON CONFLICT FAIL + ); + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-5.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-5.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-5.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Arrange the table rows in a different order and repeat. +# +do_execsql_test conflict-6.1 { + DROP TABLE t1; + CREATE TABLE t1( + c UNIQUE ON CONFLICT FAIL, + a INT PRIMARY KEY ON CONFLICT REPLACE, + b UNIQUE ON CONFLICT IGNORE + ); + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-6.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-6.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-6.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Change which column is the PRIMARY KEY +# +do_execsql_test conflict-7.1 { + DROP TABLE t1; + CREATE TABLE t1( + a UNIQUE ON CONFLICT REPLACE, + b INTEGER PRIMARY KEY ON CONFLICT IGNORE, + c UNIQUE ON CONFLICT FAIL + ); + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-7.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-7.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-7.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Change which column is the PRIMARY KEY +# +do_execsql_test conflict-8.1 { + DROP TABLE t1; + CREATE TABLE t1( + a UNIQUE ON CONFLICT REPLACE, + b INT PRIMARY KEY ON CONFLICT IGNORE, + c UNIQUE ON CONFLICT FAIL + ); + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-8.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-8.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-8.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Change which column is the PRIMARY KEY +# +do_execsql_test conflict-9.1 { + DROP TABLE t1; + CREATE TABLE t1( + a UNIQUE ON CONFLICT REPLACE, + b INT PRIMARY KEY ON CONFLICT IGNORE, + c UNIQUE ON CONFLICT FAIL + ) WITHOUT ROWID; + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-9.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-9.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-9.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Change which column is the PRIMARY KEY +# +do_execsql_test conflict-10.1 { + DROP TABLE t1; + CREATE TABLE t1( + a UNIQUE ON CONFLICT REPLACE, + b UNIQUE ON CONFLICT IGNORE, + c INTEGER PRIMARY KEY ON CONFLICT FAIL + ); + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-10.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-10.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-10.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + +# Change which column is the PRIMARY KEY +# +do_execsql_test conflict-11.1 { + DROP TABLE t1; + CREATE TABLE t1( + a UNIQUE ON CONFLICT REPLACE, + b UNIQUE ON CONFLICT IGNORE, + c PRIMARY KEY ON CONFLICT FAIL + ) WITHOUT ROWID; + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert a row that conflicts on column B. The insert should be ignored. +# +do_execsql_test conflict-11.2 { + INSERT INTO t1(a,b,c) VALUES(3,2,5); + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4} + +# Insert two rows where the second conflicts on C. The first row show go +# and and then there should be a constraint error. +# +do_test conflict-11.3 { + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} +} {1 {UNIQUE constraint failed: t1.c}} +do_execsql_test conflict-11.4 { + SELECT a,b,c FROM t1 ORDER BY a; +} {1 2 3 2 3 4 4 5 6} + + +finish_test