/ Check-in [d120c45f]
Login

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

Overview
Comment:On an UPSERT when the order of constraint checks is rearranged, make sure that the affinity transformations on the inserted content occur before any of the constraint checks. Fix for ticket [79cad5e4b2e219dd197242e9e5f4e].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d120c45f3dc79f67afed0e44e5133569f784bc6792b15f5d79529deac2d13072
User & Date: drh 2018-07-11 13:34:24
Context
2018-07-12
11:28
Add a test case to check that SQLITE_DBCONFIG_RESET_DATABASE can be used with wal mode databases even if there are active readers. check-in: 6145f5b3 user: dan tags: trunk
2018-07-11
13:34
On an UPSERT when the order of constraint checks is rearranged, make sure that the affinity transformations on the inserted content occur before any of the constraint checks. Fix for ticket [79cad5e4b2e219dd197242e9e5f4e]. check-in: d120c45f user: drh tags: trunk
03:27
Adjustments to VdbeCoverage macros to deal with byte-code branches that can never be taken in some directions. check-in: b170c009 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/insert.c.

  1628   1628     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1629   1629       int regIdx;          /* Range of registers hold conent for pIdx */
  1630   1630       int regR;            /* Range of registers holding conflicting PK */
  1631   1631       int iThisCur;        /* Cursor for this UNIQUE index */
  1632   1632       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
  1633   1633   
  1634   1634       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
         1635  +    if( bAffinityDone==0 ){
         1636  +      sqlite3TableAffinity(v, pTab, regNewData+1);
         1637  +      bAffinityDone = 1;
         1638  +    }
  1635   1639       if( pUpIdx==pIdx ){
  1636   1640         addrUniqueOk = sAddr.upsertBtm;
  1637   1641         upsertBypass = sqlite3VdbeGoto(v, 0);
  1638   1642         VdbeComment((v, "Skip upsert subroutine"));
  1639   1643         sqlite3VdbeResolveLabel(v, sAddr.upsertTop2);
  1640   1644       }else{
  1641   1645         addrUniqueOk = sqlite3VdbeMakeLabel(v);
  1642   1646       }
  1643   1647       VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
  1644         -    if( bAffinityDone==0 ){
  1645         -      sqlite3TableAffinity(v, pTab, regNewData+1);
  1646         -      bAffinityDone = 1;
  1647         -    }
  1648   1648       iThisCur = iIdxCur+ix;
  1649   1649   
  1650   1650   
  1651   1651       /* Skip partial indices for which the WHERE clause is not true */
  1652   1652       if( pIdx->pPartIdxWhere ){
  1653   1653         sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
  1654   1654         pParse->iSelfTab = -(regNewData+1);

Changes to test/upsert1.test.

   107    107   do_execsql_test upsert1-500 {
   108    108     DROP TABLE t1;
   109    109     CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT UNIQUE);
   110    110     INSERT INTO t1(x,y) SELECT 1,2 WHERE true
   111    111       ON CONFLICT(x) DO UPDATE SET y=max(t1.y,excluded.y) AND true;
   112    112     SELECT * FROM t1;
   113    113   } {1 2}
          114  +
          115  +# 2018-07-11
          116  +# Ticket https://sqlite.org/src/tktview/79cad5e4b2e219dd197242e9e5f4
          117  +# UPSERT leads to a corrupt index.
          118  +#
          119  +do_execsql_test upsert1-600 {
          120  +  DROP TABLE t1;
          121  +  CREATE TABLE t1(b UNIQUE, a INT PRIMARY KEY) WITHOUT ROWID;
          122  +  INSERT OR IGNORE INTO t1(a) VALUES('1') ON CONFLICT(a) DO NOTHING;
          123  +  PRAGMA integrity_check;
          124  +} {ok}
          125  +do_execsql_test upsert1-610 {
          126  +  DELETE FROM t1;
          127  +  INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
          128  +  PRAGMA integrity_check;
          129  +} {ok}
   114    130   
   115    131   finish_test