Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix conflict handling for the case when the rowid uses REPLACE but other unique constraints use FAIL or IGNORE. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | omit-rowid |
| Files: | files | file ages | folders |
| SHA1: |
573cc27427af297185f11aac8dce88ca |
| User & Date: | drh 2013-11-05 19:41:32 |
References
|
2013-11-07
| ||
| 16:08 | Add support for WITHOUT ROWID tables. This change also includes (1) standardization of the error message returned from run-time constraint errors, (2) improved EXPLAIN comments, (3) the SQLITE_ENABLE_EXPLAIN_COMMENTS option, (4) the SQLITE_ENABLE_MODULE_COMMENTS option, and (5) a bug fix (see [573cc27427]) in the handling of REPLACE on the rowid when secondary indices use FAIL or IGNORE. check-in: c80e229d user: drh tags: trunk | |
Context
|
2013-11-05
| ||
| 22:39 | Make sure the query planner knows that the PRIMARY KEY index of a WITHOUT ROWID table is always a covering index. check-in: 03e7019e user: drh tags: omit-rowid | |
| 19:41 | Fix conflict handling for the case when the rowid uses REPLACE but other unique constraints use FAIL or IGNORE. check-in: 573cc274 user: drh tags: omit-rowid | |
| 17:30 | Fix a bug in secondary index initialization when the secondary index is a superset of the PRIMARY KEY for a WITHOUT ROWID table. check-in: 52a3d885 user: drh tags: omit-rowid | |
Changes
Changes to src/insert.c.
1225 1225 int i; /* loop counter */ 1226 1226 int ix; /* Index loop counter */ 1227 1227 int nCol; /* Number of columns */ 1228 1228 int onError; /* Conflict resolution strategy */ 1229 1229 int j1; /* Addresss of jump instruction */ 1230 1230 int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */ 1231 1231 int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */ 1232 - u8 isUpdate; 1232 + int ipkTop = 0; /* Top of the rowid change constraint check */ 1233 + int ipkBottom = 0; /* Bottom of the rowid change constraint check */ 1234 + u8 isUpdate; /* True if this is an UPDATE operation */ 1233 1235 1234 1236 isUpdate = regOldData!=0; 1235 1237 db = pParse->db; 1236 1238 v = sqlite3GetVdbe(pParse); 1237 1239 assert( v!=0 ); 1238 1240 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 1239 1241 nCol = pTab->nCol; ................................................................................ 1340 1342 1341 1343 if( isUpdate ){ 1342 1344 /* pkChng!=0 does not mean that the rowid has change, only that 1343 1345 ** it might have changed. Skip the conflict logic below if the rowid 1344 1346 ** is unchanged. */ 1345 1347 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData); 1346 1348 } 1349 + 1350 + /* If the response to a rowid conflict is REPLACE but the response 1351 + ** to some other UNIQUE constraint is FAIL or IGNORE, then we need 1352 + ** to defer the running of the rowid conflict checking until after 1353 + ** the UNIQUE constraints have run. 1354 + */ 1355 + if( onError==OE_Replace && overrideError!=OE_Replace ){ 1356 + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 1357 + if( pIdx->onError==OE_Ignore || pIdx->onError==OE_Fail ){ 1358 + ipkTop = sqlite3VdbeAddOp0(v, OP_Goto); 1359 + break; 1360 + } 1361 + } 1362 + } 1347 1363 1348 1364 /* Check to see if the new rowid already exists in the table. Skip 1349 1365 ** the following conflict logic if it does not. */ 1350 1366 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData); 1351 1367 1352 1368 /* Generate code that deals with a rowid collision */ 1353 1369 switch( onError ){ ................................................................................ 1396 1412 sqlite3MultiWrite(pParse); 1397 1413 sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, 0); 1398 1414 } 1399 1415 seenReplace = 1; 1400 1416 break; 1401 1417 } 1402 1418 case OE_Ignore: { 1403 - assert( seenReplace==0 ); 1419 + /*assert( seenReplace==0 );*/ 1404 1420 sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); 1405 1421 break; 1406 1422 } 1407 1423 } 1408 1424 sqlite3VdbeResolveLabel(v, addrRowidOk); 1425 + if( ipkTop ){ 1426 + ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto); 1427 + sqlite3VdbeJumpHere(v, ipkTop); 1428 + } 1409 1429 } 1410 1430 1411 1431 /* Test all UNIQUE constraints by creating entries for each UNIQUE 1412 1432 ** index and making sure that duplicate entries do not already exist. 1413 1433 ** Compute the revised record entries for indices as we go. 1414 1434 ** 1415 1435 ** This loop also handles the case of the PRIMARY KEY index for a ................................................................................ 1471 1491 continue; /* pIdx is not a UNIQUE index */ 1472 1492 } 1473 1493 if( overrideError!=OE_Default ){ 1474 1494 onError = overrideError; 1475 1495 }else if( onError==OE_Default ){ 1476 1496 onError = OE_Abort; 1477 1497 } 1478 - if( seenReplace ){ 1479 - if( onError==OE_Ignore ) onError = OE_Replace; 1480 - else if( onError==OE_Fail ) onError = OE_Abort; 1481 - } 1482 1498 1483 1499 /* Check to see if the new index entry will be unique */ 1484 1500 regR = sqlite3GetTempRange(pParse, nPkField); 1485 1501 sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, 1486 1502 regIdx, pIdx->nKeyCol); 1487 1503 1488 1504 /* Generate code to handle collisions */ ................................................................................ 1540 1556 case OE_Rollback: 1541 1557 case OE_Abort: 1542 1558 case OE_Fail: { 1543 1559 sqlite3UniqueConstraint(pParse, onError, pIdx); 1544 1560 break; 1545 1561 } 1546 1562 case OE_Ignore: { 1547 - assert( seenReplace==0 ); 1548 1563 sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); 1549 1564 break; 1550 1565 } 1551 1566 default: { 1552 1567 Trigger *pTrigger = 0; 1553 1568 assert( onError==OE_Replace ); 1554 1569 sqlite3MultiWrite(pParse); ................................................................................ 1560 1575 seenReplace = 1; 1561 1576 break; 1562 1577 } 1563 1578 } 1564 1579 sqlite3VdbeResolveLabel(v, addrUniqueOk); 1565 1580 sqlite3ReleaseTempRange(pParse, regR, nPkField); 1566 1581 } 1582 + if( ipkTop ){ 1583 + sqlite3VdbeAddOp2(v, OP_Goto, 0, ipkTop+1); 1584 + sqlite3VdbeJumpHere(v, ipkBottom); 1585 + } 1567 1586 1568 1587 if( pbMayReplace ){ 1569 1588 *pbMayReplace = seenReplace; 1570 1589 } 1571 1590 VdbeModuleComment((v, "END: GenCnstCks()")); 1572 1591 } 1573 1592
Added test/conflict3.test.
1 +# 2013-11-05 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. 12 +# 13 +# This file implements tests for the conflict resolution extension 14 +# to SQLite. 15 +# 16 +# This file focuses on making sure that combinations of REPLACE, 17 +# IGNORE, and FAIL conflict resolution play well together. 18 +# 19 + 20 +set testdir [file dirname $argv0] 21 +source $testdir/tester.tcl 22 + 23 +ifcapable !conflict { 24 + finish_test 25 + return 26 +} 27 + 28 +do_execsql_test conflict-1.1 { 29 + CREATE TABLE t1( 30 + a INTEGER PRIMARY KEY ON CONFLICT REPLACE, 31 + b UNIQUE ON CONFLICT IGNORE, 32 + c UNIQUE ON CONFLICT FAIL 33 + ); 34 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 35 + SELECT a,b,c FROM t1 ORDER BY a; 36 +} {1 2 3 2 3 4} 37 + 38 +# Insert a row that conflicts on column B. The insert should be ignored. 39 +# 40 +do_execsql_test conflict-1.2 { 41 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 42 + SELECT a,b,c FROM t1 ORDER BY a; 43 +} {1 2 3 2 3 4} 44 + 45 +# Insert two rows where the second conflicts on C. The first row show go 46 +# and and then there should be a constraint error. 47 +# 48 +do_test conflict-1.3 { 49 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 50 +} {1 {UNIQUE constraint failed: t1.c}} 51 +do_execsql_test conflict-1.4 { 52 + SELECT a,b,c FROM t1 ORDER BY a; 53 +} {1 2 3 2 3 4 4 5 6} 54 + 55 +# Replete the tests above, but this time on a table non-INTEGER primary key. 56 +# 57 +do_execsql_test conflict-2.1 { 58 + DROP TABLE t1; 59 + CREATE TABLE t1( 60 + a INT PRIMARY KEY ON CONFLICT REPLACE, 61 + b UNIQUE ON CONFLICT IGNORE, 62 + c UNIQUE ON CONFLICT FAIL 63 + ); 64 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 65 + SELECT a,b,c FROM t1 ORDER BY a; 66 +} {1 2 3 2 3 4} 67 + 68 +# Insert a row that conflicts on column B. The insert should be ignored. 69 +# 70 +do_execsql_test conflict-2.2 { 71 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 72 + SELECT a,b,c FROM t1 ORDER BY a; 73 +} {1 2 3 2 3 4} 74 + 75 +# Insert two rows where the second conflicts on C. The first row show go 76 +# and and then there should be a constraint error. 77 +# 78 +do_test conflict-2.3 { 79 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 80 +} {1 {UNIQUE constraint failed: t1.c}} 81 +do_execsql_test conflict-2.4 { 82 + SELECT a,b,c FROM t1 ORDER BY a; 83 +} {1 2 3 2 3 4 4 5 6} 84 + 85 +# Replete again on a WITHOUT ROWID table. 86 +# 87 +do_execsql_test conflict-3.1 { 88 + DROP TABLE t1; 89 + CREATE TABLE t1( 90 + a INT PRIMARY KEY ON CONFLICT REPLACE, 91 + b UNIQUE ON CONFLICT IGNORE, 92 + c UNIQUE ON CONFLICT FAIL 93 + ) WITHOUT ROWID; 94 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 95 + SELECT a,b,c FROM t1 ORDER BY a; 96 +} {1 2 3 2 3 4} 97 + 98 +# Insert a row that conflicts on column B. The insert should be ignored. 99 +# 100 +do_execsql_test conflict-3.2 { 101 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 102 + SELECT a,b,c FROM t1 ORDER BY a; 103 +} {1 2 3 2 3 4} 104 + 105 +# Insert two rows where the second conflicts on C. The first row show go 106 +# and and then there should be a constraint error. 107 +# 108 +do_test conflict-3.3 { 109 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 110 +} {1 {UNIQUE constraint failed: t1.c}} 111 +do_execsql_test conflict-3.4 { 112 + SELECT a,b,c FROM t1 ORDER BY a; 113 +} {1 2 3 2 3 4 4 5 6} 114 + 115 +# Arrange the table rows in a different order and repeat. 116 +# 117 +do_execsql_test conflict-4.1 { 118 + DROP TABLE t1; 119 + CREATE TABLE t1( 120 + b UNIQUE ON CONFLICT IGNORE, 121 + c UNIQUE ON CONFLICT FAIL, 122 + a INT PRIMARY KEY ON CONFLICT REPLACE 123 + ) WITHOUT ROWID; 124 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 125 + SELECT a,b,c FROM t1 ORDER BY a; 126 +} {1 2 3 2 3 4} 127 + 128 +# Insert a row that conflicts on column B. The insert should be ignored. 129 +# 130 +do_execsql_test conflict-4.2 { 131 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 132 + SELECT a,b,c FROM t1 ORDER BY a; 133 +} {1 2 3 2 3 4} 134 + 135 +# Insert two rows where the second conflicts on C. The first row show go 136 +# and and then there should be a constraint error. 137 +# 138 +do_test conflict-4.3 { 139 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 140 +} {1 {UNIQUE constraint failed: t1.c}} 141 +do_execsql_test conflict-4.4 { 142 + SELECT a,b,c FROM t1 ORDER BY a; 143 +} {1 2 3 2 3 4 4 5 6} 144 + 145 +# Arrange the table rows in a different order and repeat. 146 +# 147 +do_execsql_test conflict-5.1 { 148 + DROP TABLE t1; 149 + CREATE TABLE t1( 150 + b UNIQUE ON CONFLICT IGNORE, 151 + a INT PRIMARY KEY ON CONFLICT REPLACE, 152 + c UNIQUE ON CONFLICT FAIL 153 + ); 154 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 155 + SELECT a,b,c FROM t1 ORDER BY a; 156 +} {1 2 3 2 3 4} 157 + 158 +# Insert a row that conflicts on column B. The insert should be ignored. 159 +# 160 +do_execsql_test conflict-5.2 { 161 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 162 + SELECT a,b,c FROM t1 ORDER BY a; 163 +} {1 2 3 2 3 4} 164 + 165 +# Insert two rows where the second conflicts on C. The first row show go 166 +# and and then there should be a constraint error. 167 +# 168 +do_test conflict-5.3 { 169 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 170 +} {1 {UNIQUE constraint failed: t1.c}} 171 +do_execsql_test conflict-5.4 { 172 + SELECT a,b,c FROM t1 ORDER BY a; 173 +} {1 2 3 2 3 4 4 5 6} 174 + 175 +# Arrange the table rows in a different order and repeat. 176 +# 177 +do_execsql_test conflict-6.1 { 178 + DROP TABLE t1; 179 + CREATE TABLE t1( 180 + c UNIQUE ON CONFLICT FAIL, 181 + a INT PRIMARY KEY ON CONFLICT REPLACE, 182 + b UNIQUE ON CONFLICT IGNORE 183 + ); 184 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 185 + SELECT a,b,c FROM t1 ORDER BY a; 186 +} {1 2 3 2 3 4} 187 + 188 +# Insert a row that conflicts on column B. The insert should be ignored. 189 +# 190 +do_execsql_test conflict-6.2 { 191 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 192 + SELECT a,b,c FROM t1 ORDER BY a; 193 +} {1 2 3 2 3 4} 194 + 195 +# Insert two rows where the second conflicts on C. The first row show go 196 +# and and then there should be a constraint error. 197 +# 198 +do_test conflict-6.3 { 199 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 200 +} {1 {UNIQUE constraint failed: t1.c}} 201 +do_execsql_test conflict-6.4 { 202 + SELECT a,b,c FROM t1 ORDER BY a; 203 +} {1 2 3 2 3 4 4 5 6} 204 + 205 +# Change which column is the PRIMARY KEY 206 +# 207 +do_execsql_test conflict-7.1 { 208 + DROP TABLE t1; 209 + CREATE TABLE t1( 210 + a UNIQUE ON CONFLICT REPLACE, 211 + b INTEGER PRIMARY KEY ON CONFLICT IGNORE, 212 + c UNIQUE ON CONFLICT FAIL 213 + ); 214 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 215 + SELECT a,b,c FROM t1 ORDER BY a; 216 +} {1 2 3 2 3 4} 217 + 218 +# Insert a row that conflicts on column B. The insert should be ignored. 219 +# 220 +do_execsql_test conflict-7.2 { 221 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 222 + SELECT a,b,c FROM t1 ORDER BY a; 223 +} {1 2 3 2 3 4} 224 + 225 +# Insert two rows where the second conflicts on C. The first row show go 226 +# and and then there should be a constraint error. 227 +# 228 +do_test conflict-7.3 { 229 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 230 +} {1 {UNIQUE constraint failed: t1.c}} 231 +do_execsql_test conflict-7.4 { 232 + SELECT a,b,c FROM t1 ORDER BY a; 233 +} {1 2 3 2 3 4 4 5 6} 234 + 235 +# Change which column is the PRIMARY KEY 236 +# 237 +do_execsql_test conflict-8.1 { 238 + DROP TABLE t1; 239 + CREATE TABLE t1( 240 + a UNIQUE ON CONFLICT REPLACE, 241 + b INT PRIMARY KEY ON CONFLICT IGNORE, 242 + c UNIQUE ON CONFLICT FAIL 243 + ); 244 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 245 + SELECT a,b,c FROM t1 ORDER BY a; 246 +} {1 2 3 2 3 4} 247 + 248 +# Insert a row that conflicts on column B. The insert should be ignored. 249 +# 250 +do_execsql_test conflict-8.2 { 251 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 252 + SELECT a,b,c FROM t1 ORDER BY a; 253 +} {1 2 3 2 3 4} 254 + 255 +# Insert two rows where the second conflicts on C. The first row show go 256 +# and and then there should be a constraint error. 257 +# 258 +do_test conflict-8.3 { 259 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 260 +} {1 {UNIQUE constraint failed: t1.c}} 261 +do_execsql_test conflict-8.4 { 262 + SELECT a,b,c FROM t1 ORDER BY a; 263 +} {1 2 3 2 3 4 4 5 6} 264 + 265 +# Change which column is the PRIMARY KEY 266 +# 267 +do_execsql_test conflict-9.1 { 268 + DROP TABLE t1; 269 + CREATE TABLE t1( 270 + a UNIQUE ON CONFLICT REPLACE, 271 + b INT PRIMARY KEY ON CONFLICT IGNORE, 272 + c UNIQUE ON CONFLICT FAIL 273 + ) WITHOUT ROWID; 274 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 275 + SELECT a,b,c FROM t1 ORDER BY a; 276 +} {1 2 3 2 3 4} 277 + 278 +# Insert a row that conflicts on column B. The insert should be ignored. 279 +# 280 +do_execsql_test conflict-9.2 { 281 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 282 + SELECT a,b,c FROM t1 ORDER BY a; 283 +} {1 2 3 2 3 4} 284 + 285 +# Insert two rows where the second conflicts on C. The first row show go 286 +# and and then there should be a constraint error. 287 +# 288 +do_test conflict-9.3 { 289 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 290 +} {1 {UNIQUE constraint failed: t1.c}} 291 +do_execsql_test conflict-9.4 { 292 + SELECT a,b,c FROM t1 ORDER BY a; 293 +} {1 2 3 2 3 4 4 5 6} 294 + 295 +# Change which column is the PRIMARY KEY 296 +# 297 +do_execsql_test conflict-10.1 { 298 + DROP TABLE t1; 299 + CREATE TABLE t1( 300 + a UNIQUE ON CONFLICT REPLACE, 301 + b UNIQUE ON CONFLICT IGNORE, 302 + c INTEGER PRIMARY KEY ON CONFLICT FAIL 303 + ); 304 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 305 + SELECT a,b,c FROM t1 ORDER BY a; 306 +} {1 2 3 2 3 4} 307 + 308 +# Insert a row that conflicts on column B. The insert should be ignored. 309 +# 310 +do_execsql_test conflict-10.2 { 311 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 312 + SELECT a,b,c FROM t1 ORDER BY a; 313 +} {1 2 3 2 3 4} 314 + 315 +# Insert two rows where the second conflicts on C. The first row show go 316 +# and and then there should be a constraint error. 317 +# 318 +do_test conflict-10.3 { 319 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 320 +} {1 {UNIQUE constraint failed: t1.c}} 321 +do_execsql_test conflict-10.4 { 322 + SELECT a,b,c FROM t1 ORDER BY a; 323 +} {1 2 3 2 3 4 4 5 6} 324 + 325 +# Change which column is the PRIMARY KEY 326 +# 327 +do_execsql_test conflict-11.1 { 328 + DROP TABLE t1; 329 + CREATE TABLE t1( 330 + a UNIQUE ON CONFLICT REPLACE, 331 + b UNIQUE ON CONFLICT IGNORE, 332 + c PRIMARY KEY ON CONFLICT FAIL 333 + ) WITHOUT ROWID; 334 + INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 335 + SELECT a,b,c FROM t1 ORDER BY a; 336 +} {1 2 3 2 3 4} 337 + 338 +# Insert a row that conflicts on column B. The insert should be ignored. 339 +# 340 +do_execsql_test conflict-11.2 { 341 + INSERT INTO t1(a,b,c) VALUES(3,2,5); 342 + SELECT a,b,c FROM t1 ORDER BY a; 343 +} {1 2 3 2 3 4} 344 + 345 +# Insert two rows where the second conflicts on C. The first row show go 346 +# and and then there should be a constraint error. 347 +# 348 +do_test conflict-11.3 { 349 + catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 350 +} {1 {UNIQUE constraint failed: t1.c}} 351 +do_execsql_test conflict-11.4 { 352 + SELECT a,b,c FROM t1 ORDER BY a; 353 +} {1 2 3 2 3 4 4 5 6} 354 + 355 + 356 +finish_test