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 |
Timelines: | family | ancestors | descendants | both | omit-rowid |
Files: | files | file ages | folders |
SHA1: |
573cc27427af297185f11aac8dce88ca |
User & Date: | drh 2013-11-05 19:41:32.450 |
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: c80e229dd9 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: 03e7019e14 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: 573cc27427 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: 52a3d88519 user: drh tags: omit-rowid) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
1225 1226 1227 1228 1229 1230 1231 | int i; /* loop counter */ int ix; /* Index loop counter */ 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 */ | > > | | 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 | int i; /* loop counter */ int ix; /* Index loop counter */ 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 */ 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 ); assert( pTab->pSelect==0 ); /* This table is not a VIEW */ nCol = pTab->nCol; |
︙ | ︙ | |||
1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 | if( isUpdate ){ /* 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); } /* 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); /* Generate code that deals with a rowid collision */ switch( onError ){ | > > > > > > > > > > > > > > | 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 | if( isUpdate ){ /* 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); /* Generate code that deals with a rowid collision */ switch( onError ){ |
︙ | ︙ | |||
1396 1397 1398 1399 1400 1401 1402 | sqlite3MultiWrite(pParse); sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, 0); } seenReplace = 1; break; } case OE_Ignore: { | | > > > > | 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 | sqlite3MultiWrite(pParse); sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, 0); } seenReplace = 1; break; } case OE_Ignore: { /*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. ** ** This loop also handles the case of the PRIMARY KEY index for a |
︙ | ︙ | |||
1471 1472 1473 1474 1475 1476 1477 | continue; /* pIdx is not a UNIQUE index */ } if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ onError = OE_Abort; } | < < < < | 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 | continue; /* pIdx is not a UNIQUE index */ } if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ 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); /* Generate code to handle collisions */ |
︙ | ︙ | |||
1540 1541 1542 1543 1544 1545 1546 | case OE_Rollback: case OE_Abort: case OE_Fail: { sqlite3UniqueConstraint(pParse, onError, pIdx); break; } case OE_Ignore: { | < > > > > | 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 | case OE_Rollback: case OE_Abort: case OE_Fail: { sqlite3UniqueConstraint(pParse, onError, pIdx); break; } case OE_Ignore: { sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); break; } default: { Trigger *pTrigger = 0; assert( onError==OE_Replace ); sqlite3MultiWrite(pParse); if( db->flags&SQLITE_RecTriggers ){ pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); } sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, regR, nPkField, 0, OE_Replace); seenReplace = 1; break; } } 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.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 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 |