/ Check-in [3f1c8051]
Login

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

Overview
Comment:On an INSERT or UPDATE, generate the new table record prior to running foreign key checks, in case the foreign key checks changes datatypes on the registers holding column values. Proposed fix for ticket [e63cbcfd3378afe6980d626].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-e63cbcfd
Files: files | file ages | folders
SHA3-256: 3f1c8051648a341db4dffad66d3b1f9980d8a2b314cb0ce879cb2a10d1779b84
User & Date: drh 2019-05-07 19:13:42
Context
2019-05-07
19:21
Add test cases for the fix on this branch. Closed-Leaf check-in: 2e31abe0 user: dan tags: tkt-e63cbcfd
19:13
On an INSERT or UPDATE, generate the new table record prior to running foreign key checks, in case the foreign key checks changes datatypes on the registers holding column values. Proposed fix for ticket [e63cbcfd3378afe6980d626]. check-in: 3f1c8051 user: drh tags: tkt-e63cbcfd
17:47
Strive to prevent harmless compiler warnings in GCC 4.8.5. check-in: 8b6691f6 user: drh tags: trunk
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. */