Index: ext/fts3/fts3.c ================================================================== --- ext/fts3/fts3.c +++ ext/fts3/fts3.c @@ -4112,10 +4112,19 @@ }while( pCsr->isEof==0 && fts3EvalLoadDeferred(pCsr, &rc) ); } return rc; } +/* +** Restart interation for expression pExpr so that the next call to +** sqlite3Fts3EvalNext() visits the first row. Do not allow incremental +** loading or merging of phrase doclists for this iteration. +** +** If *pRc is other than SQLITE_OK when this function is called, it is +** a no-op. If an error occurs within this function, *pRc is set to an +** SQLite error code before returning. +*/ static void fts3EvalRestart( Fts3Cursor *pCsr, Fts3Expr *pExpr, int *pRc ){ Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -384,17 +384,29 @@ } /* If the parent table is the same as the child table, and we are about ** to increment the constraint-counter (i.e. this is an INSERT operation), ** then check if the row being inserted matches itself. If so, do not - ** increment the constraint-counter. */ + ** increment the constraint-counter. + ** + ** If any of the parent-key values are NULL, then the row cannot match + ** itself. So set JUMPIFNULL to make sure we do the OP_Found if any + ** of the parent-key values are NULL (at this point it is known that + ** none of the child key values are). + */ if( pTab==pFKey->pFrom && nIncr==1 ){ int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1; for(i=0; iaiColumn[i]+1+regData; + assert( aiCol[i]!=pTab->iPKey ); + if( pIdx->aiColumn[i]==pTab->iPKey ){ + /* The parent key is a composite key that includes the IPK column */ + iParent = regData; + } sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent); + sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL); } sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec); Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -504,14 +504,14 @@ ** character is exactly one byte in size. Also, all characters are ** able to participate in upper-case-to-lower-case mappings in EBCDIC ** whereas only characters less than 0x80 do in ASCII. */ #if defined(SQLITE_EBCDIC) -# define sqlite3Utf8Read(A,C) (*(A++)) -# define GlogUpperToLower(A) A = sqlite3UpperToLower[A] +# define sqlite3Utf8Read(A,C) (*(A++)) +# define GlogUpperToLower(A) A = sqlite3UpperToLower[A] #else -# define GlogUpperToLower(A) if( A<0x80 ){ A = sqlite3UpperToLower[A]; } +# define GlogUpperToLower(A) if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; } #endif static const struct compareInfo globInfo = { '*', '?', '[', 0 }; /* The correct SQL-92 behavior is for the LIKE operator to ignore ** case. Thus 'a' LIKE 'A' would be true. */ @@ -550,13 +550,13 @@ */ static int patternCompare( const u8 *zPattern, /* The glob pattern */ const u8 *zString, /* The string to compare against the glob */ const struct compareInfo *pInfo, /* Information about how to do the compare */ - const int esc /* The escape character */ + u32 esc /* The escape character */ ){ - int c, c2; + u32 c, c2; int invert; int seen; u8 matchOne = pInfo->matchOne; u8 matchAll = pInfo->matchAll; u8 matchSet = pInfo->matchSet; @@ -682,11 +682,11 @@ sqlite3_context *context, int argc, sqlite3_value **argv ){ const unsigned char *zA, *zB; - int escape = 0; + u32 escape = 0; int nPat; sqlite3 *db = sqlite3_context_db_handle(context); zB = sqlite3_value_text(argv[0]); zA = sqlite3_value_text(argv[1]); Index: src/pcache1.c ================================================================== --- src/pcache1.c +++ src/pcache1.c @@ -572,11 +572,11 @@ memset(pCache, 0, sz); if( separateCache ){ pGroup = (PGroup*)&pCache[1]; pGroup->mxPinned = 10; }else{ - pGroup = &pcache1_g.grp; + pGroup = &pcache1.grp; } pCache->pGroup = pGroup; pCache->szPage = szPage; pCache->bPurgeable = (bPurgeable ? 1 : 0); if( bPurgeable ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -680,11 +680,11 @@ ** sqlite3_close(). * ** A thread must be holding a mutex on the corresponding Btree in order ** to access Schema content. This implies that the thread must also be ** holding a mutex on the sqlite3 connection pointer that owns the Btree. -** For a TEMP Schema, on the connection mutex is required. +** For a TEMP Schema, only the connection mutex is required. */ struct Schema { int schema_cookie; /* Database schema version number for this file */ int iGeneration; /* Generation counter. Incremented with each change */ Hash tblHash; /* All tables indexed by name */ @@ -2877,11 +2877,11 @@ int sqlite3AtoF(const char *z, double*, int, u8); int sqlite3GetInt32(const char *, int*); int sqlite3Atoi(const char*); int sqlite3Utf16ByteLen(const void *pData, int nChar); int sqlite3Utf8CharLen(const char *pData, int nByte); -int sqlite3Utf8Read(const u8*, const u8**); +u32 sqlite3Utf8Read(const u8*, const u8**); /* ** Routines to read and write variable-length integers. These used to ** be defined locally, but now we use the varint routines in the util.c ** file. Code should use the MACRO forms below, as the Varint32 versions Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -242,11 +242,11 @@ aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx ); if( aRegIdx==0 ) goto update_cleanup; } for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; - if( chngRowid ){ + if( hasFK || chngRowid ){ reg = ++pParse->nMem; }else{ reg = 0; for(i=0; inColumn; i++){ if( aXRef[pIdx->aiColumn[i]]>=0 ){ Index: src/utf.c ================================================================== --- src/utf.c +++ src/utf.c @@ -161,11 +161,11 @@ } \ if( c<0x80 \ || (c&0xFFFFF800)==0xD800 \ || (c&0xFFFFFFFE)==0xFFFE ){ c = 0xFFFD; } \ } -int sqlite3Utf8Read( +u32 sqlite3Utf8Read( const unsigned char *zIn, /* First byte of UTF-8 character */ const unsigned char **pzNext /* Write first byte past UTF-8 char here */ ){ unsigned int c; Index: test/fkey3.test ================================================================== --- test/fkey3.test +++ test/fkey3.test @@ -18,10 +18,12 @@ ifcapable {!foreignkey||!trigger} { finish_test return } + +set testprefix fkey3 # Create a table and some data to work with. # do_test fkey3-1.1 { execsql { @@ -75,6 +77,110 @@ SELECT 1, x FROM t1; SELECT 2, y FROM t2; } } {1 100 1 101 2 100 2 101} + +#------------------------------------------------------------------------- +# The following tests - fkey-3.* - test some edge cases to do with +# inserting rows into tables that have foreign keys where the parent +# table is the same as the child table. Especially cases where the +# new row being inserted matches itself. +# +do_execsql_test 3.1.1 { + CREATE TABLE t3(a, b, c, d, + UNIQUE(a, b), + FOREIGN KEY(c, d) REFERENCES t3(a, b) + ); + INSERT INTO t3 VALUES(1, 2, 1, 2); +} {} +do_catchsql_test 3.1.2 { + INSERT INTO t3 VALUES(NULL, 2, 5, 2); +} {1 {foreign key constraint failed}} +do_catchsql_test 3.1.3 { + INSERT INTO t3 VALUES(NULL, 3, 5, 2); +} {1 {foreign key constraint failed}} + +do_execsql_test 3.2.1 { + CREATE TABLE t4(a UNIQUE, b REFERENCES t4(a)); +} +do_catchsql_test 3.2.2 { + INSERT INTO t4 VALUES(NULL, 1); +} {1 {foreign key constraint failed}} + +do_execsql_test 3.3.1 { + CREATE TABLE t5(a INTEGER PRIMARY KEY, b REFERENCES t5(a)); + INSERT INTO t5 VALUES(NULL, 1); +} {} +do_catchsql_test 3.3.2 { + INSERT INTO t5 VALUES(NULL, 3); +} {1 {foreign key constraint failed}} + +do_execsql_test 3.4.1 { + CREATE TABLE t6(a INTEGER PRIMARY KEY, b, c, d, + FOREIGN KEY(c, d) REFERENCES t6(a, b) + ); + CREATE UNIQUE INDEX t6i ON t6(b, a); +} +do_execsql_test 3.4.2 { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {} +do_execsql_test 3.4.3 { INSERT INTO t6 VALUES(2, 'a', 2, 'a'); } {} +do_execsql_test 3.4.4 { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {} +do_execsql_test 3.4.5 { INSERT INTO t6 VALUES(5, 'a', 2, 'a'); } {} +do_catchsql_test 3.4.6 { + INSERT INTO t6 VALUES(NULL, 'a', 65, 'a'); +} {1 {foreign key constraint failed}} + +do_execsql_test 3.4.7 { + INSERT INTO t6 VALUES(100, 'one', 100, 'one'); + DELETE FROM t6 WHERE a = 100; +} +do_execsql_test 3.4.8 { + INSERT INTO t6 VALUES(100, 'one', 100, 'one'); + UPDATE t6 SET c = 1, d = 'a' WHERE a = 100; + DELETE FROM t6 WHERE a = 100; +} + +do_execsql_test 3.5.1 { + CREATE TABLE t7(a, b, c, d INTEGER PRIMARY KEY, + FOREIGN KEY(c, d) REFERENCES t7(a, b) + ); + CREATE UNIQUE INDEX t7i ON t7(a, b); +} +do_execsql_test 3.5.2 { INSERT INTO t7 VALUES('x', 1, 'x', NULL) } {} +do_execsql_test 3.5.3 { INSERT INTO t7 VALUES('x', 2, 'x', 2) } {} +do_catchsql_test 3.5.4 { + INSERT INTO t7 VALUES('x', 450, 'x', NULL); +} {1 {foreign key constraint failed}} +do_catchsql_test 3.5.5 { + INSERT INTO t7 VALUES('x', 450, 'x', 451); +} {1 {foreign key constraint failed}} + + +do_execsql_test 3.6.1 { + CREATE TABLE t8(a, b, c, d, e, FOREIGN KEY(c, d) REFERENCES t8(a, b)); + CREATE UNIQUE INDEX t8i1 ON t8(a, b); + CREATE UNIQUE INDEX t8i2 ON t8(c); + INSERT INTO t8 VALUES(1, 1, 1, 1, 1); +} +do_catchsql_test 3.6.2 { + UPDATE t8 SET d = 2; +} {1 {foreign key constraint failed}} +do_execsql_test 3.6.3 { UPDATE t8 SET d = 1; } +do_execsql_test 3.6.4 { UPDATE t8 SET e = 2; } + +do_catchsql_test 3.6.5 { + CREATE TABLE TestTable ( + id INTEGER PRIMARY KEY, + name text, + source_id integer not null, + parent_id integer, + + foreign key(source_id, parent_id) references TestTable(source_id, id) + ); + CREATE UNIQUE INDEX testindex on TestTable(source_id, id); + PRAGMA foreign_keys=1; + INSERT INTO TestTable VALUES (1, 'parent', 1, null); + INSERT INTO TestTable VALUES (2, 'child', 1, 1); + UPDATE TestTable SET parent_id=1000 where id=2; +} {1 {foreign key constraint failed}} + finish_test