/ Check-in [529fb55e]
Login

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

Overview
Comment:Fix UPSERT so that it checks the target-constraint first and fires the DO UPDATE if that constraint is violated regardless of whether or not other constraints are in violation. This aligns SQLite behavior with what PostgreSQL does. Fix for ticket [908f001483982c43cdb476dfb590a1a].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 529fb55e3d00472f13446117527b0896827b11e870b581af7fe7cbb7392ef3cd
User & Date: drh 2018-08-14 15:12:52
Context
2018-08-15
14:03
Allow sqlite3_snapshot_open() to be called to change the snapshot after a read transaction is already open on database. check-in: 41399169 user: dan tags: trunk
2018-08-14
18:12
Merge fixes and enhancements from trunk. check-in: dff0314b user: drh tags: alter-table-rename-column
15:12
Fix UPSERT so that it checks the target-constraint first and fires the DO UPDATE if that constraint is violated regardless of whether or not other constraints are in violation. This aligns SQLite behavior with what PostgreSQL does. Fix for ticket [908f001483982c43cdb476dfb590a1a]. check-in: 529fb55e user: drh tags: trunk
2018-08-13
22:50
Stop requiring the global VFS mutex to access the unixInodeInfo.pUnused field. The unixInodeInfo mutex is sufficient. check-in: e3ea43da user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1174   1174     testcase( w.eCode==0 );
  1175   1175     testcase( w.eCode==CKCNSTRNT_COLUMN );
  1176   1176     testcase( w.eCode==CKCNSTRNT_ROWID );
  1177   1177     testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  1178   1178     return !w.eCode;
  1179   1179   }
  1180   1180   
  1181         -/*
  1182         -** An instance of the ConstraintAddr object remembers the byte-code addresses
  1183         -** for sections of the constraint checks that deal with uniqueness constraints
  1184         -** on the rowid and on the upsert constraint.
  1185         -**
  1186         -** This information is passed into checkReorderConstraintChecks() to insert
  1187         -** some OP_Goto operations so that the rowid and upsert constraints occur
  1188         -** in the correct order relative to other constraints.
  1189         -*/
  1190         -typedef struct ConstraintAddr ConstraintAddr;
  1191         -struct ConstraintAddr {
  1192         -  int ipkTop;          /* Subroutine for rowid constraint check */
  1193         -  int upsertTop;       /* Label for upsert constraint check subroutine */
  1194         -  int upsertTop2;      /* Copy of upsertTop not cleared by the call */
  1195         -  int upsertBtm;       /* upsert constraint returns to this label */
  1196         -  int ipkBtm;          /* Return opcode rowid constraint check */
  1197         -};
  1198         -
  1199         -/*
  1200         -** Generate any OP_Goto operations needed to cause constraints to be
  1201         -** run that haven't already been run.
  1202         -*/
  1203         -static void reorderConstraintChecks(Vdbe *v, ConstraintAddr *p){
  1204         -  if( p->upsertTop ){
  1205         -    testcase( sqlite3VdbeLabelHasBeenResolved(v, p->upsertTop) );
  1206         -    sqlite3VdbeGoto(v, p->upsertTop);
  1207         -    VdbeComment((v, "call upsert subroutine"));
  1208         -    sqlite3VdbeResolveLabel(v, p->upsertBtm);
  1209         -    p->upsertTop = 0;
  1210         -  }
  1211         -  if( p->ipkTop ){
  1212         -    sqlite3VdbeGoto(v, p->ipkTop);
  1213         -    VdbeComment((v, "call rowid unique-check subroutine"));
  1214         -    sqlite3VdbeJumpHere(v, p->ipkBtm);
  1215         -    p->ipkTop = 0;
  1216         -  }
  1217         -}
  1218         -
  1219   1181   /*
  1220   1182   ** Generate code to do constraint checks prior to an INSERT or an UPDATE
  1221   1183   ** on table pTab.
  1222   1184   **
  1223   1185   ** The regNewData parameter is the first register in a range that contains
  1224   1186   ** the data to be inserted or the data after the update.  There will be
  1225   1187   ** pTab->nCol+1 registers in this range.  The first register (the one
................................................................................
  1321   1283     int i;               /* loop counter */
  1322   1284     int ix;              /* Index loop counter */
  1323   1285     int nCol;            /* Number of columns */
  1324   1286     int onError;         /* Conflict resolution strategy */
  1325   1287     int addr1;           /* Address of jump instruction */
  1326   1288     int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  1327   1289     int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
  1328         -  ConstraintAddr sAddr;/* Address information for constraint reordering */
  1329   1290     Index *pUpIdx = 0;   /* Index to which to apply the upsert */
  1330   1291     u8 isUpdate;         /* True if this is an UPDATE operation */
  1331   1292     u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
  1332   1293     int upsertBypass = 0;  /* Address of Goto to bypass upsert subroutine */
         1294  +  int upsertJump = 0;    /* Address of Goto that jumps into upsert subroutine */
         1295  +  int ipkTop = 0;        /* Top of the IPK uniqueness check */
         1296  +  int ipkBottom = 0;     /* OP_Goto at the end of the IPK uniqueness check */
  1333   1297   
  1334   1298     isUpdate = regOldData!=0;
  1335   1299     db = pParse->db;
  1336   1300     v = sqlite3GetVdbe(pParse);
  1337   1301     assert( v!=0 );
  1338   1302     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1339   1303     nCol = pTab->nCol;
  1340         -  memset(&sAddr, 0, sizeof(sAddr));
  1341   1304     
  1342   1305     /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
  1343   1306     ** normal rowid tables.  nPkField is the number of key fields in the 
  1344   1307     ** pPk index or 1 for a rowid table.  In other words, nPkField is the
  1345   1308     ** number of fields in the true primary key of the table. */
  1346   1309     if( HasRowid(pTab) ){
  1347   1310       pPk = 0;
................................................................................
  1437   1400       pParse->iSelfTab = 0;
  1438   1401     }
  1439   1402   #endif /* !defined(SQLITE_OMIT_CHECK) */
  1440   1403   
  1441   1404     /* UNIQUE and PRIMARY KEY constraints should be handled in the following
  1442   1405     ** order:
  1443   1406     **
  1444         -  **   (1)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
  1445         -  **   (2)  OE_Update
         1407  +  **   (1)  OE_Update
         1408  +  **   (2)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
  1446   1409     **   (3)  OE_Replace
  1447   1410     **
  1448   1411     ** OE_Fail and OE_Ignore must happen before any changes are made.
  1449   1412     ** OE_Update guarantees that only a single row will change, so it
  1450   1413     ** must happen before OE_Replace.  Technically, OE_Abort and OE_Rollback
  1451   1414     ** could happen in any order, but they are grouped up front for
  1452   1415     ** convenience.
         1416  +  **
         1417  +  ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
         1418  +  ** The order of constraints used to have OE_Update as (2) and OE_Abort
         1419  +  ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
         1420  +  ** constraint before any others, so it had to be moved.
  1453   1421     **
  1454   1422     ** Constraint checking code is generated in this order:
  1455   1423     **   (A)  The rowid constraint
  1456   1424     **   (B)  Unique index constraints that do not have OE_Replace as their
  1457   1425     **        default conflict resolution strategy
  1458   1426     **   (C)  Unique index that do use OE_Replace by default.
  1459   1427     **
................................................................................
  1466   1434       if( pUpsert->pUpsertTarget==0 ){
  1467   1435         /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
  1468   1436         ** Make all unique constraint resolution be OE_Ignore */
  1469   1437         assert( pUpsert->pUpsertSet==0 );
  1470   1438         overrideError = OE_Ignore;
  1471   1439         pUpsert = 0;
  1472   1440       }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
  1473         -      /* If the constraint-target is on some column other than
  1474         -      ** then ROWID, then we might need to move the UPSERT around
  1475         -      ** so that it occurs in the correct order. */
  1476         -      sAddr.upsertTop = sAddr.upsertTop2 = sqlite3VdbeMakeLabel(v);
  1477         -      sAddr.upsertBtm = sqlite3VdbeMakeLabel(v);
         1441  +      /* If the constraint-target uniqueness check must be run first.
         1442  +      ** Jump to that uniqueness check now */
         1443  +      upsertJump = sqlite3VdbeAddOp0(v, OP_Goto);
         1444  +      VdbeComment((v, "UPSERT constraint goes first"));
  1478   1445       }
  1479   1446     }
  1480   1447   
  1481   1448     /* If rowid is changing, make sure the new rowid does not previously
  1482   1449     ** exist in the table.
  1483   1450     */
  1484   1451     if( pkChng && pPk==0 ){
................................................................................
  1502   1469       }
  1503   1470   
  1504   1471       /* If the response to a rowid conflict is REPLACE but the response
  1505   1472       ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
  1506   1473       ** to defer the running of the rowid conflict checking until after
  1507   1474       ** the UNIQUE constraints have run.
  1508   1475       */
  1509         -    assert( OE_Update>OE_Replace );
  1510         -    assert( OE_Ignore<OE_Replace );
  1511         -    assert( OE_Fail<OE_Replace );
  1512         -    assert( OE_Abort<OE_Replace );
  1513         -    assert( OE_Rollback<OE_Replace );
  1514         -    if( onError>=OE_Replace
  1515         -     && (pUpsert || onError!=overrideError)
  1516         -     && pTab->pIndex
         1476  +    if( onError==OE_Replace      /* IPK rule is REPLACE */
         1477  +     && onError!=overrideError   /* Rules for other contraints are different */
         1478  +     && pTab->pIndex             /* There exist other constraints */
  1517   1479       ){
  1518         -      sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
         1480  +      ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
         1481  +      VdbeComment((v, "defer IPK REPLACE until last"));
  1519   1482       }
  1520   1483   
  1521   1484       if( isUpdate ){
  1522   1485         /* pkChng!=0 does not mean that the rowid has changed, only that
  1523   1486         ** it might have changed.  Skip the conflict logic below if the rowid
  1524   1487         ** is unchanged. */
  1525   1488         sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
................................................................................
  1606   1569         case OE_Ignore: {
  1607   1570           testcase( onError==OE_Ignore );
  1608   1571           sqlite3VdbeGoto(v, ignoreDest);
  1609   1572           break;
  1610   1573         }
  1611   1574       }
  1612   1575       sqlite3VdbeResolveLabel(v, addrRowidOk);
  1613         -    if( sAddr.ipkTop ){
  1614         -      sAddr.ipkBtm = sqlite3VdbeAddOp0(v, OP_Goto);
  1615         -      sqlite3VdbeJumpHere(v, sAddr.ipkTop-1);
         1576  +    if( ipkTop ){
         1577  +      ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
         1578  +      sqlite3VdbeJumpHere(v, ipkTop-1);
  1616   1579       }
  1617   1580     }
  1618   1581   
  1619   1582     /* Test all UNIQUE constraints by creating entries for each UNIQUE
  1620   1583     ** index and making sure that duplicate entries do not already exist.
  1621   1584     ** Compute the revised record entries for indices as we go.
  1622   1585     **
................................................................................
  1626   1589     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1627   1590       int regIdx;          /* Range of registers hold conent for pIdx */
  1628   1591       int regR;            /* Range of registers holding conflicting PK */
  1629   1592       int iThisCur;        /* Cursor for this UNIQUE index */
  1630   1593       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
  1631   1594   
  1632   1595       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
  1633         -    if( bAffinityDone==0 ){
  1634         -      sqlite3TableAffinity(v, pTab, regNewData+1);
  1635         -      bAffinityDone = 1;
  1636         -    }
  1637   1596       if( pUpIdx==pIdx ){
  1638         -      addrUniqueOk = sAddr.upsertBtm;
         1597  +      addrUniqueOk = upsertJump+1;
  1639   1598         upsertBypass = sqlite3VdbeGoto(v, 0);
  1640   1599         VdbeComment((v, "Skip upsert subroutine"));
  1641         -      sqlite3VdbeResolveLabel(v, sAddr.upsertTop2);
         1600  +      sqlite3VdbeJumpHere(v, upsertJump);
  1642   1601       }else{
  1643   1602         addrUniqueOk = sqlite3VdbeMakeLabel(v);
         1603  +    }
         1604  +    if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){
         1605  +      sqlite3TableAffinity(v, pTab, regNewData+1);
         1606  +      bAffinityDone = 1;
  1644   1607       }
  1645   1608       VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
  1646   1609       iThisCur = iIdxCur+ix;
  1647   1610   
  1648   1611   
  1649   1612       /* Skip partial indices for which the WHERE clause is not true */
  1650   1613       if( pIdx->pPartIdxWhere ){
................................................................................
  1709   1672         if( pUpsert->pUpsertSet==0 ){
  1710   1673           onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
  1711   1674         }else{
  1712   1675           onError = OE_Update;  /* DO UPDATE */
  1713   1676         }
  1714   1677       }
  1715   1678   
  1716         -    /* Invoke subroutines to handle IPK replace and upsert prior to running
  1717         -    ** the first REPLACE constraint check. */
  1718         -    if( onError==OE_Replace ){
  1719         -      testcase( sAddr.ipkTop );
  1720         -      testcase( sAddr.upsertTop
  1721         -             && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
  1722         -      reorderConstraintChecks(v, &sAddr);
  1723         -    }
  1724         -
  1725   1679       /* Collision detection may be omitted if all of the following are true:
  1726   1680       **   (1) The conflict resolution algorithm is REPLACE
  1727   1681       **   (2) The table is a WITHOUT ROWID table
  1728   1682       **   (3) There are no secondary indexes on the table
  1729   1683       **   (4) No delete triggers need to be fired if there is a conflict
  1730   1684       **   (5) No FK constraint counters need to be updated if a conflict occurs.
  1731   1685       */ 
................................................................................
  1839   1793               regR, nPkField, 0, OE_Replace,
  1840   1794               (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
  1841   1795           seenReplace = 1;
  1842   1796           break;
  1843   1797         }
  1844   1798       }
  1845   1799       if( pUpIdx==pIdx ){
         1800  +      sqlite3VdbeGoto(v, upsertJump+1);
  1846   1801         sqlite3VdbeJumpHere(v, upsertBypass);
  1847   1802       }else{
  1848   1803         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1849   1804       }
  1850   1805       if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
         1806  +  }
  1851   1807   
         1808  +  /* If the IPK constraint is a REPLACE, run it last */
         1809  +  if( ipkTop ){
         1810  +    sqlite3VdbeGoto(v, ipkTop+1);
         1811  +    VdbeComment((v, "Do IPK REPLACE"));
         1812  +    sqlite3VdbeJumpHere(v, ipkBottom);
  1852   1813     }
  1853         -  testcase( sAddr.ipkTop!=0 );
  1854         -  testcase( sAddr.upsertTop
  1855         -         && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
  1856         -  reorderConstraintChecks(v, &sAddr);
  1857         -  
         1814  +
  1858   1815     *pbMayReplace = seenReplace;
  1859   1816     VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
  1860   1817   }
  1861   1818   
  1862   1819   #ifdef SQLITE_ENABLE_NULL_TRIM
  1863   1820   /*
  1864   1821   ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)

Changes to src/vdbe.h.

   234    234   void sqlite3VdbeRunOnlyOnce(Vdbe*);
   235    235   void sqlite3VdbeReusable(Vdbe*);
   236    236   void sqlite3VdbeDelete(Vdbe*);
   237    237   void sqlite3VdbeClearObject(sqlite3*,Vdbe*);
   238    238   void sqlite3VdbeMakeReady(Vdbe*,Parse*);
   239    239   int sqlite3VdbeFinalize(Vdbe*);
   240    240   void sqlite3VdbeResolveLabel(Vdbe*, int);
   241         -#ifdef SQLITE_COVERAGE_TEST
   242         -  int sqlite3VdbeLabelHasBeenResolved(Vdbe*,int);
   243         -#endif
   244    241   int sqlite3VdbeCurrentAddr(Vdbe*);
   245    242   #ifdef SQLITE_DEBUG
   246    243     int sqlite3VdbeAssertMayAbort(Vdbe *, int);
   247    244   #endif
   248    245   void sqlite3VdbeResetStepResult(Vdbe*);
   249    246   void sqlite3VdbeRewind(Vdbe*);
   250    247   int sqlite3VdbeReset(Vdbe*);

Changes to src/vdbeaux.c.

   433    433       }
   434    434   #endif
   435    435       assert( p->aLabel[j]==(-1) ); /* Labels may only be resolved once */
   436    436       p->aLabel[j] = v->nOp;
   437    437     }
   438    438   }
   439    439   
   440         -#ifdef SQLITE_COVERAGE_TEST
   441         -/*
   442         -** Return TRUE if and only if the label x has already been resolved.
   443         -** Return FALSE (zero) if label x is still unresolved.
   444         -**
   445         -** This routine is only used inside of testcase() macros, and so it
   446         -** only exists when measuring test coverage.
   447         -*/
   448         -int sqlite3VdbeLabelHasBeenResolved(Vdbe *v, int x){
   449         -  return v->pParse->aLabel && v->pParse->aLabel[ADDR(x)]>=0;
   450         -}
   451         -#endif /* SQLITE_COVERAGE_TEST */
   452         -
   453    440   /*
   454    441   ** Mark the VDBE as one that can only be run one time.
   455    442   */
   456    443   void sqlite3VdbeRunOnlyOnce(Vdbe *p){
   457    444     p->runOnlyOnce = 1;
   458    445   }
   459    446   

Changes to test/upsert1.test.

   123    123     PRAGMA integrity_check;
   124    124   } {ok}
   125    125   do_execsql_test upsert1-610 {
   126    126     DELETE FROM t1;
   127    127     INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
   128    128     PRAGMA integrity_check;
   129    129   } {ok}
          130  +
          131  +# 2018-08-14
          132  +# Ticket https://www.sqlite.org/src/info/908f001483982c43
          133  +# If there are multiple uniqueness contraints, the UPSERT should fire
          134  +# if the one constraint it targets fails, regardless of whether or not
          135  +# the other constraints pass or fail.  In other words, the UPSERT constraint
          136  +# should be tested first.
          137  +#
          138  +do_execsql_test upsert1-700 {
          139  +  DROP TABLE t1;
          140  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
          141  +  CREATE UNIQUE INDEX t1b ON t1(b);
          142  +  CREATE UNIQUE INDEX t1e ON t1(e);
          143  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          144  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          145  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          146  +  SELECT * FROM t1;
          147  +} {1 2 33 4 5}
          148  +do_execsql_test upsert1-710 {
          149  +  DELETE FROM t1;
          150  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          151  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          152  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          153  +  SELECT * FROM t1;
          154  +} {1 2 33 4 5}
          155  +do_execsql_test upsert1-720 {
          156  +  DELETE FROM t1;
          157  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          158  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          159  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          160  +  SELECT * FROM t1;
          161  +} {1 2 33 4 5}
          162  +do_execsql_test upsert1-730 {
          163  +  DROP TABLE t1;
          164  +  CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT);
          165  +  CREATE UNIQUE INDEX t1a ON t1(a);
          166  +  CREATE UNIQUE INDEX t1b ON t1(b);
          167  +  CREATE UNIQUE INDEX t1e ON t1(e);
          168  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          169  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          170  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          171  +  SELECT * FROM t1;
          172  +} {1 2 33 4 5}
          173  +do_execsql_test upsert1-740 {
          174  +  DELETE FROM t1;
          175  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          176  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          177  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          178  +  SELECT * FROM t1;
          179  +} {1 2 33 4 5}
          180  +do_execsql_test upsert1-750 {
          181  +  DELETE FROM t1;
          182  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          183  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          184  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          185  +  SELECT * FROM t1;
          186  +} {1 2 33 4 5}
          187  +do_execsql_test upsert1-760 {
          188  +  DROP TABLE t1;
          189  +  CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) WITHOUT ROWID;
          190  +  CREATE UNIQUE INDEX t1a ON t1(a);
          191  +  CREATE UNIQUE INDEX t1b ON t1(b);
          192  +  CREATE UNIQUE INDEX t1e ON t1(e);
          193  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          194  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          195  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          196  +  SELECT * FROM t1;
          197  +} {1 2 33 4 5}
          198  +do_execsql_test upsert1-770 {
          199  +  DELETE FROM t1;
          200  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          201  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          202  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          203  +  SELECT * FROM t1;
          204  +} {1 2 33 4 5}
          205  +do_execsql_test upsert1-780 {
          206  +  DELETE FROM t1;
          207  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          208  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          209  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          210  +  SELECT * FROM t1;
          211  +} {1 2 33 4 5}
          212  +
   130    213   
   131    214   finish_test