Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Ensure that the correct number of columns in a UNIQUE index are checked for uniqueness, regardless of whether or not the original table has a ROWID or if the columns are NOT NULL, etc. Ticket [9a6daf340df99ba93c]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6b785e92f279cb65746834d5cd25594f |
User & Date: | drh 2014-07-30 13:56:48.415 |
Context
2014-07-30
| ||
15:43 | Add the "eForce" parameter to the sqlite3_multiplex_shutdown() entry point in test_multiplex.c. Shutdown is forced if true. Shutdown is not done if there are pending database connections and eForce is false, but an error log entry is made instead. (check-in: c7303d0139 user: drh tags: trunk) | |
14:57 | Merge in the CREATE UNIQUE INDEX fix of ticket [9a6daf340df99ba93c53bcf]. (check-in: fa7912320f user: drh tags: apple-osx) | |
14:44 | Merge recent trunk changes, and especially the fix for the CREATE UNIQUE INDEX problem of ticket [9a6daf340df99ba9]. (check-in: 5b50a8380b user: drh tags: threads) | |
14:29 | Merge the fix for the CREATE UNIQUE INDEX problem into the sessions branch. (check-in: 43401ee624 user: drh tags: sessions) | |
13:56 | Ensure that the correct number of columns in a UNIQUE index are checked for uniqueness, regardless of whether or not the original table has a ROWID or if the columns are NOT NULL, etc. Ticket [9a6daf340df99ba93c]. (check-in: 6b785e92f2 user: drh tags: trunk) | |
2014-07-29
| ||
19:54 | Enhancements and updates to the Win32 mutex subsystem. (check-in: ca9868cdae user: mistachkin tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
2708 2709 2710 2711 2712 2713 2714 | addr1 = sqlite3VdbeAddOp2(v, OP_SorterSort, iSorter, 0); VdbeCoverage(v); assert( pKey!=0 || db->mallocFailed || pParse->nErr ); if( pIndex->onError!=OE_None && pKey!=0 ){ int j2 = sqlite3VdbeCurrentAddr(v) + 3; sqlite3VdbeAddOp2(v, OP_Goto, 0, j2); addr2 = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord, | | | 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 | addr1 = sqlite3VdbeAddOp2(v, OP_SorterSort, iSorter, 0); VdbeCoverage(v); assert( pKey!=0 || db->mallocFailed || pParse->nErr ); if( pIndex->onError!=OE_None && pKey!=0 ){ int j2 = sqlite3VdbeCurrentAddr(v) + 3; sqlite3VdbeAddOp2(v, OP_Goto, 0, j2); addr2 = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord, pIndex->nKeyCol); VdbeCoverage(v); sqlite3UniqueConstraint(pParse, OE_Abort, pIndex); }else{ addr2 = sqlite3VdbeCurrentAddr(v); } sqlite3VdbeAddOp2(v, OP_SorterData, iSorter, regRecord); sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1); sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
4267 4268 4269 4270 4271 4272 4273 | case OP_ResetCount: { sqlite3VdbeSetChanges(db, p->nChange); p->nChange = 0; break; } /* Opcode: SorterCompare P1 P2 P3 P4 | | | | | | | | 4267 4268 4269 4270 4271 4272 4273 4274 4275 4276 4277 4278 4279 4280 4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 4297 4298 4299 4300 4301 4302 4303 4304 4305 | case OP_ResetCount: { sqlite3VdbeSetChanges(db, p->nChange); p->nChange = 0; break; } /* Opcode: SorterCompare P1 P2 P3 P4 ** Synopsis: if key(P1)!=trim(r[P3],P4) goto P2 ** ** P1 is a sorter cursor. This instruction compares a prefix of the ** the record blob in register P3 against a prefix of the entry that ** the sorter cursor currently points to. Only the first P4 fields ** of r[P3] and the sorter record are compared. ** ** If either P3 or the sorter contains a NULL in one of their significant ** fields (not counting the P4 fields at the end which are ignored) then ** the comparison is assumed to be equal. ** ** Fall through to next instruction if the two records compare equal to ** each other. Jump to P2 if they are different. */ case OP_SorterCompare: { VdbeCursor *pC; int res; int nKeyCol; pC = p->apCsr[pOp->p1]; assert( isSorter(pC) ); assert( pOp->p4type==P4_INT32 ); pIn3 = &aMem[pOp->p3]; nKeyCol = pOp->p4.i; rc = sqlite3VdbeSorterCompare(pC, pIn3, nKeyCol, &res); VdbeBranchTaken(res!=0,2); if( res ){ pc = pOp->p2-1; } break; }; |
︙ | ︙ |
Changes to src/vdbesort.c.
︙ | ︙ | |||
381 382 383 384 385 386 387 | ** be less than key2. Even if key2 also contains NULL values. ** ** If pKey2 is passed a NULL pointer, then it is assumed that the pCsr->aSpace ** has been allocated and contains an unpacked record that is used as key2. */ static void vdbeSorterCompare( const VdbeCursor *pCsr, /* Cursor object (for pKeyInfo) */ | | | | < | | 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 | ** be less than key2. Even if key2 also contains NULL values. ** ** If pKey2 is passed a NULL pointer, then it is assumed that the pCsr->aSpace ** has been allocated and contains an unpacked record that is used as key2. */ static void vdbeSorterCompare( const VdbeCursor *pCsr, /* Cursor object (for pKeyInfo) */ int nKeyCol, /* Num of columns. 0 means "all" */ const void *pKey1, int nKey1, /* Left side of comparison */ const void *pKey2, int nKey2, /* Right side of comparison */ int *pRes /* OUT: Result of comparison */ ){ KeyInfo *pKeyInfo = pCsr->pKeyInfo; VdbeSorter *pSorter = pCsr->pSorter; UnpackedRecord *r2 = pSorter->pUnpacked; int i; if( pKey2 ){ sqlite3VdbeRecordUnpack(pKeyInfo, nKey2, pKey2, r2); } if( nKeyCol ){ r2->nField = nKeyCol; for(i=0; i<nKeyCol; i++){ if( r2->aMem[i].flags & MEM_Null ){ *pRes = -1; return; } } assert( r2->default_rc==0 ); } |
︙ | ︙ | |||
1080 1081 1082 1083 1084 1085 1086 | ** Otherwise, set *pRes to a negative, zero or positive value if the ** key in pVal is smaller than, equal to or larger than the current sorter ** key. */ int sqlite3VdbeSorterCompare( const VdbeCursor *pCsr, /* Sorter cursor */ Mem *pVal, /* Value to compare to current sorter key */ | | | | 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 | ** Otherwise, set *pRes to a negative, zero or positive value if the ** key in pVal is smaller than, equal to or larger than the current sorter ** key. */ int sqlite3VdbeSorterCompare( const VdbeCursor *pCsr, /* Sorter cursor */ Mem *pVal, /* Value to compare to current sorter key */ int nKeyCol, /* Only compare this many fields */ int *pRes /* OUT: Result of comparison */ ){ VdbeSorter *pSorter = pCsr->pSorter; void *pKey; int nKey; /* Sorter key to compare pVal with */ pKey = vdbeSorterRowkey(pSorter, &nKey); vdbeSorterCompare(pCsr, nKeyCol, pVal->z, pVal->n, pKey, nKey, pRes); return SQLITE_OK; } |
Added test/unique2.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 | # 2014-07-30 # # 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 CREATE UNIQUE INDEX statement # to verify that ticket 9a6daf340df99ba93c53bcf8fa83d9f28040d2a8 # has been fixed: # # drh added on 2014-07-30 12:33:04: # # The CREATE UNIQUE INDEX on the third line below does not fail even # though the x column values are not all unique. # # CREATE TABLE t1(x NOT NULL); # INSERT INTO t1 VALUES(1),(2),(2),(3); # CREATE UNIQUE INDEX t1x ON t1(x); # # If the index is created before the INSERT, then uniqueness is enforced # at the point of the INSERT. Note that the NOT NULL on the indexed column # seems to be required in order to exhibit this bug. # # "PRAGMA integrity_check" does not detect the resulting malformed database. # That might be considered a separate issue. # # Bisecting shows that this problem was introduced by the addition of # WITHOUT ROWID support in version 3.8.2, specifically in check-in # [c80e229dd9c1230] on 2013-11-07. This problem was reported on the mailing # list by Pavel Pimenov. and primary keys, and the UNIQUE constraint # on table columns # set testdir [file dirname $argv0] source $testdir/tester.tcl foreach {id sql} { 1 {CREATE TABLE t1(x TEXT PRIMARY KEY, y NOT NULL) WITHOUT ROWID} 2 {CREATE TABLE t1(x TEXT PRIMARY KEY, y NOT NULL)} 3 {CREATE TABLE t1(x TEXT PRIMARY KEY, y) WITHOUT ROWID} 4 {CREATE TABLE t1(x TEXT PRIMARY KEY, y)} } { do_test $id.1 { db eval {DROP TABLE IF EXISTS t1} db eval $sql db eval {INSERT INTO t1(x,y) VALUES(1,1),(2,2),(3,2),(4,3)} } {} do_test $id.2 { catchsql {CREATE UNIQUE INDEX t1y ON t1(y)} } {1 {UNIQUE constraint failed: t1.y}} } foreach {id sql} { 5 {CREATE TABLE t1(w,x,y NOT NULL,z NOT NULL,PRIMARY KEY(w,x)) WITHOUT ROWID} 6 {CREATE TABLE t1(w,x,y NOT NULL,z NOT NULL,PRIMARY KEY(w,x))} 7 {CREATE TABLE t1(w,x,y NOT NULL,z,PRIMARY KEY(w,x)) WITHOUT ROWID} 8 {CREATE TABLE t1(w,x,y NOT NULL,z,PRIMARY KEY(w,x))} 9 {CREATE TABLE t1(w,x,y,z NOT NULL,PRIMARY KEY(w,x)) WITHOUT ROWID} 10 {CREATE TABLE t1(w,x,y,z NOT NULL,PRIMARY KEY(w,x))} 11 {CREATE TABLE t1(w,x,y,z,PRIMARY KEY(w,x)) WITHOUT ROWID} 12 {CREATE TABLE t1(w,x,y,z,PRIMARY KEY(w,x))} } { do_test $id.1 { db eval {DROP TABLE IF EXISTS t1} db eval $sql db eval {INSERT INTO t1(w,x,y,z) VALUES(1,2,3,4),(2,3,3,4)} } {} do_test $id.2 { catchsql {CREATE UNIQUE INDEX t1yz ON t1(y,z)} } {1 {UNIQUE constraint failed: t1.y, t1.z}} } finish_test |