/ Check-in [3c75605b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Generate all records for INSERT or UPDATE prior to running foreign key constraint checks, since the FK checks might modify the datatype of registers used to generate the records. Fix for ticket [e63cbcfd3378afe6980d6].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3c75605b4652ae88659465d832953ac9d467369f9cb417f73b3d8599ec60b18b
User & Date: drh 2019-05-07 20:06:41
Context
2019-05-08
00:17
Earlier detections of errors in the byte-offset-to-cell-content integer at offset 5 in the header of a btree page. check-in: a0819086 user: drh tags: trunk
2019-05-07
20:06
Generate all records for INSERT or UPDATE prior to running foreign key constraint checks, since the FK checks might modify the datatype of registers used to generate the records. Fix for ticket [e63cbcfd3378afe6980d6]. check-in: 3c75605b user: drh tags: trunk
19:44
Do not commit an "OR FAIL" statement that causes foriegn key constraint violations. check-in: 659c551d user: dan tags: trunk
19:21
Add test cases for the fix on this branch. Closed-Leaf check-in: 2e31abe0 user: dan tags: tkt-e63cbcfd
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

   810    810     }
   811    811   
   812    812     /* If this is not a view, open the table and and all indices */
   813    813     if( !isView ){
   814    814       int nIdx;
   815    815       nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, -1, 0,
   816    816                                         &iDataCur, &iIdxCur);
   817         -    aRegIdx = sqlite3DbMallocRawNN(db, sizeof(int)*(nIdx+1));
          817  +    aRegIdx = sqlite3DbMallocRawNN(db, sizeof(int)*(nIdx+2));
   818    818       if( aRegIdx==0 ){
   819    819         goto insert_cleanup;
   820    820       }
   821    821       for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
   822    822         assert( pIdx );
   823    823         aRegIdx[i] = ++pParse->nMem;
   824    824         pParse->nMem += pIdx->nColumn;
   825    825       }
          826  +    aRegIdx[i] = ++pParse->nMem;  /* Register to store the table record */
   826    827     }
   827    828   #ifndef SQLITE_OMIT_UPSERT
   828    829     if( pUpsert ){
   829    830       if( IsVirtual(pTab) ){
   830    831         sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"",
   831    832                 pTab->zName);
   832    833         goto insert_cleanup;
................................................................................
  1221   1222   ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
  1222   1223   **
  1223   1224   ** The code generated by this routine will store new index entries into
  1224   1225   ** registers identified by aRegIdx[].  No index entry is created for
  1225   1226   ** indices where aRegIdx[i]==0.  The order of indices in aRegIdx[] is
  1226   1227   ** the same as the order of indices on the linked list of indices
  1227   1228   ** at pTab->pIndex.
         1229  +**
         1230  +** (2019-05-07) The generated code also creates a new record for the
         1231  +** main table, if pTab is a rowid table, and stores that record in the
         1232  +** register identified by aRegIdx[nIdx] - in other words in the first
         1233  +** entry of aRegIdx[] past the last index.  It is important that the
         1234  +** record be generated during constraint checks to avoid affinity changes
         1235  +** to the register content that occur after constraint checks but before
         1236  +** the new record is inserted.
  1228   1237   **
  1229   1238   ** The caller must have already opened writeable cursors on the main
  1230   1239   ** table and all applicable indices (that is to say, all indices for which
  1231   1240   ** aRegIdx[] is not zero).  iDataCur is the cursor for the main table when
  1232   1241   ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
  1233   1242   ** index when operating on a WITHOUT ROWID table.  iIdxCur is the cursor
  1234   1243   ** for the first index in the pTab->pIndex list.  Cursors for other indices
................................................................................
  1840   1849   
  1841   1850     /* If the IPK constraint is a REPLACE, run it last */
  1842   1851     if( ipkTop ){
  1843   1852       sqlite3VdbeGoto(v, ipkTop);
  1844   1853       VdbeComment((v, "Do IPK REPLACE"));
  1845   1854       sqlite3VdbeJumpHere(v, ipkBottom);
  1846   1855     }
         1856  +
         1857  +  /* Generate the table record */
         1858  +  if( HasRowid(pTab) ){
         1859  +    int regRec = aRegIdx[ix];
         1860  +    sqlite3VdbeAddOp3(v, OP_MakeRecord, regNewData+1, pTab->nCol, regRec);
         1861  +    sqlite3SetMakeRecordP5(v, pTab);
         1862  +    if( !bAffinityDone ){
         1863  +      sqlite3TableAffinity(v, pTab, 0);
         1864  +    }
         1865  +  }
  1847   1866   
  1848   1867     *pbMayReplace = seenReplace;
  1849   1868     VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
  1850   1869   }
  1851   1870   
  1852   1871   #ifdef SQLITE_ENABLE_NULL_TRIM
  1853   1872   /*
................................................................................
  1890   1909     int update_flags,   /* True for UPDATE, False for INSERT */
  1891   1910     int appendBias,     /* True if this is likely to be an append */
  1892   1911     int useSeekResult   /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
  1893   1912   ){
  1894   1913     Vdbe *v;            /* Prepared statements under construction */
  1895   1914     Index *pIdx;        /* An index being inserted or updated */
  1896   1915     u8 pik_flags;       /* flag values passed to the btree insert */
  1897         -  int regData;        /* Content registers (after the rowid) */
  1898         -  int regRec;         /* Register holding assembled record for the table */
  1899   1916     int i;              /* Loop counter */
  1900         -  u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */
  1901   1917   
  1902   1918     assert( update_flags==0
  1903   1919          || update_flags==OPFLAG_ISUPDATE
  1904   1920          || update_flags==(OPFLAG_ISUPDATE|OPFLAG_SAVEPOSITION)
  1905   1921     );
  1906   1922   
  1907   1923     v = sqlite3GetVdbe(pParse);
  1908   1924     assert( v!=0 );
  1909   1925     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1910   1926     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1911   1927       if( aRegIdx[i]==0 ) continue;
  1912         -    bAffinityDone = 1;
  1913   1928       if( pIdx->pPartIdxWhere ){
  1914   1929         sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
  1915   1930         VdbeCoverage(v);
  1916   1931       }
  1917   1932       pik_flags = (useSeekResult ? OPFLAG_USESEEKRESULT : 0);
  1918   1933       if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
  1919   1934         assert( pParse->nested==0 );
................................................................................
  1933   1948       }
  1934   1949       sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i],
  1935   1950                            aRegIdx[i]+1,
  1936   1951                            pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn);
  1937   1952       sqlite3VdbeChangeP5(v, pik_flags);
  1938   1953     }
  1939   1954     if( !HasRowid(pTab) ) return;
  1940         -  regData = regNewData + 1;
  1941         -  regRec = sqlite3GetTempReg(pParse);
  1942         -  sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec);
  1943         -  sqlite3SetMakeRecordP5(v, pTab);
  1944         -  if( !bAffinityDone ){
  1945         -    sqlite3TableAffinity(v, pTab, 0);
  1946         -  }
  1947   1955     if( pParse->nested ){
  1948   1956       pik_flags = 0;
  1949   1957     }else{
  1950   1958       pik_flags = OPFLAG_NCHANGE;
  1951   1959       pik_flags |= (update_flags?update_flags:OPFLAG_LASTROWID);
  1952   1960     }
  1953   1961     if( appendBias ){
  1954   1962       pik_flags |= OPFLAG_APPEND;
  1955   1963     }
  1956   1964     if( useSeekResult ){
  1957   1965       pik_flags |= OPFLAG_USESEEKRESULT;
  1958   1966     }
  1959         -  sqlite3VdbeAddOp3(v, OP_Insert, iDataCur, regRec, regNewData);
         1967  +  sqlite3VdbeAddOp3(v, OP_Insert, iDataCur, aRegIdx[i], regNewData);
  1960   1968     if( !pParse->nested ){
  1961   1969       sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
  1962   1970     }
  1963   1971     sqlite3VdbeChangeP5(v, pik_flags);
  1964   1972   }
  1965   1973   
  1966   1974   /*

Changes to src/update.c.

   155    155     Index *pIdx;           /* For looping over indices */
   156    156     Index *pPk;            /* The PRIMARY KEY index for WITHOUT ROWID tables */
   157    157     int nIdx;              /* Number of indices that need updating */
   158    158     int iBaseCur;          /* Base cursor number */
   159    159     int iDataCur;          /* Cursor for the canonical data btree */
   160    160     int iIdxCur;           /* Cursor for the first index */
   161    161     sqlite3 *db;           /* The database structure */
   162         -  int *aRegIdx = 0;      /* First register in array assigned to each index */
          162  +  int *aRegIdx = 0;      /* Registers for to each index and the main table */
   163    163     int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
   164    164                            ** an expression for the i-th column of the table.
   165    165                            ** aXRef[i]==-1 if the i-th column is not changed. */
   166    166     u8 *aToOpen;           /* 1 for tables and indices to be opened */
   167    167     u8 chngPk;             /* PRIMARY KEY changed in a WITHOUT ROWID table */
   168    168     u8 chngRowid;          /* Rowid changed in a normal table */
   169    169     u8 chngKey;            /* Either chngPk or chngRowid */
................................................................................
   269    269       pParse->nTab = iBaseCur;
   270    270     }
   271    271     pTabList->a[0].iCursor = iDataCur;
   272    272   
   273    273     /* Allocate space for aXRef[], aRegIdx[], and aToOpen[].  
   274    274     ** Initialize aXRef[] and aToOpen[] to their default values.
   275    275     */
   276         -  aXRef = sqlite3DbMallocRawNN(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 );
          276  +  aXRef = sqlite3DbMallocRawNN(db, sizeof(int) * (pTab->nCol+nIdx+1) + nIdx+2 );
   277    277     if( aXRef==0 ) goto update_cleanup;
   278    278     aRegIdx = aXRef+pTab->nCol;
   279         -  aToOpen = (u8*)(aRegIdx+nIdx);
          279  +  aToOpen = (u8*)(aRegIdx+nIdx+1);
   280    280     memset(aToOpen, 1, nIdx+1);
   281    281     aToOpen[nIdx+1] = 0;
   282    282     for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
   283    283   
   284    284     /* Initialize the name-context */
   285    285     memset(&sNC, 0, sizeof(sNC));
   286    286     sNC.pParse = pParse;
................................................................................
   374    374             break;
   375    375           }
   376    376         }
   377    377       }
   378    378       if( reg==0 ) aToOpen[j+1] = 0;
   379    379       aRegIdx[j] = reg;
   380    380     }
          381  +  aRegIdx[j] = ++pParse->nMem;  /* Register storing the table record */
   381    382     if( bReplace ){
   382    383       /* If REPLACE conflict resolution might be invoked, open cursors on all 
   383    384       ** indexes in case they are needed to delete records.  */
   384    385       memset(aToOpen, 1, nIdx+1);
   385    386     }
   386    387   
   387    388     /* Begin generating code. */

Changes to test/fkey8.test.

   192    192   }
   193    193   do_catchsql_test 4.1 {
   194    194     INSERT OR REPLACE INTO t1 VALUES(10000, 20000);
   195    195   } {1 {FOREIGN KEY constraint failed}}
   196    196   do_execsql_test 4.2 {
   197    197     INSERT OR REPLACE INTO t1 VALUES(20000, 20000);
   198    198   }
          199  +
          200  +#-------------------------------------------------------------------------
          201  +reset_db
          202  +do_execsql_test 5.0 {
          203  +  PRAGMA foreign_keys = true;
          204  +  CREATE TABLE parent(
          205  +    p TEXT PRIMARY KEY
          206  +  );
          207  +  CREATE TABLE child(
          208  +    c INTEGER UNIQUE, 
          209  +    FOREIGN KEY(c) REFERENCES parent(p) DEFERRABLE INITIALLY DEFERRED
          210  +  );
          211  +  BEGIN;
          212  +    INSERT INTO child VALUES(123);
          213  +    INSERT INTO parent VALUES('123');
          214  +  COMMIT;
          215  +}
          216  +do_execsql_test 5.1 {
          217  +  PRAGMA integrity_check;
          218  +} {ok}
          219  +
          220  +do_execsql_test 5.2 {
          221  +  INSERT INTO parent VALUES(1200);
          222  +  BEGIN;
          223  +    INSERT INTO child VALUES(456);
          224  +    UPDATE parent SET p = '456' WHERE p=1200;
          225  +  COMMIT;
          226  +}
          227  +do_execsql_test 5.3 {
          228  +  PRAGMA integrity_check;
          229  +} {ok}
   199    230   
   200    231   finish_test
   201    232