/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
....
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332



1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
....
1437
1438
1439
1440
1441
1442
1443

1444
1445
1446
1447
1448
1449
1450
1451
1452





1453
1454
1455
1456
1457
1458
1459
....
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
....
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518

1519
1520
1521
1522
1523
1524
1525
....
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
....
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641

1642
1643




1644
1645
1646
1647
1648
1649
1650
....
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
....
1839
1840
1841
1842
1843
1844
1845

1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856

1857

1858
1859
1860
1861
1862
1863
1864
  testcase( w.eCode==0 );
  testcase( w.eCode==CKCNSTRNT_COLUMN );
  testcase( w.eCode==CKCNSTRNT_ROWID );
  testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  return !w.eCode;
}

/*
** An instance of the ConstraintAddr object remembers the byte-code addresses
** for sections of the constraint checks that deal with uniqueness constraints
** on the rowid and on the upsert constraint.
**
** This information is passed into checkReorderConstraintChecks() to insert
** some OP_Goto operations so that the rowid and upsert constraints occur
** in the correct order relative to other constraints.
*/
typedef struct ConstraintAddr ConstraintAddr;
struct ConstraintAddr {
  int ipkTop;          /* Subroutine for rowid constraint check */
  int upsertTop;       /* Label for upsert constraint check subroutine */
  int upsertTop2;      /* Copy of upsertTop not cleared by the call */
  int upsertBtm;       /* upsert constraint returns to this label */
  int ipkBtm;          /* Return opcode rowid constraint check */
};

/*
** Generate any OP_Goto operations needed to cause constraints to be
** run that haven't already been run.
*/
static void reorderConstraintChecks(Vdbe *v, ConstraintAddr *p){
  if( p->upsertTop ){
    testcase( sqlite3VdbeLabelHasBeenResolved(v, p->upsertTop) );
    sqlite3VdbeGoto(v, p->upsertTop);
    VdbeComment((v, "call upsert subroutine"));
    sqlite3VdbeResolveLabel(v, p->upsertBtm);
    p->upsertTop = 0;
  }
  if( p->ipkTop ){
    sqlite3VdbeGoto(v, p->ipkTop);
    VdbeComment((v, "call rowid unique-check subroutine"));
    sqlite3VdbeJumpHere(v, p->ipkBtm);
    p->ipkTop = 0;
  }
}

/*
** Generate code to do constraint checks prior to an INSERT or an UPDATE
** on table pTab.
**
** The regNewData parameter is the first register in a range that contains
** the data to be inserted or the data after the update.  There will be
** pTab->nCol+1 registers in this range.  The first register (the one
................................................................................
  int i;               /* loop counter */
  int ix;              /* Index loop counter */
  int nCol;            /* Number of columns */
  int onError;         /* Conflict resolution strategy */
  int addr1;           /* Address of jump instruction */
  int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
  ConstraintAddr sAddr;/* Address information for constraint reordering */
  Index *pUpIdx = 0;   /* Index to which to apply the upsert */
  u8 isUpdate;         /* True if this is an UPDATE operation */
  u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
  int upsertBypass = 0;  /* Address of Goto to bypass upsert subroutine */




  isUpdate = regOldData!=0;
  db = pParse->db;
  v = sqlite3GetVdbe(pParse);
  assert( v!=0 );
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  nCol = pTab->nCol;
  memset(&sAddr, 0, sizeof(sAddr));
  
  /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
  ** normal rowid tables.  nPkField is the number of key fields in the 
  ** pPk index or 1 for a rowid table.  In other words, nPkField is the
  ** number of fields in the true primary key of the table. */
  if( HasRowid(pTab) ){
    pPk = 0;
................................................................................
    pParse->iSelfTab = 0;
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* UNIQUE and PRIMARY KEY constraints should be handled in the following
  ** order:
  **

  **   (1)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
  **   (2)  OE_Update
  **   (3)  OE_Replace
  **
  ** OE_Fail and OE_Ignore must happen before any changes are made.
  ** OE_Update guarantees that only a single row will change, so it
  ** must happen before OE_Replace.  Technically, OE_Abort and OE_Rollback
  ** could happen in any order, but they are grouped up front for
  ** convenience.





  **
  ** Constraint checking code is generated in this order:
  **   (A)  The rowid constraint
  **   (B)  Unique index constraints that do not have OE_Replace as their
  **        default conflict resolution strategy
  **   (C)  Unique index that do use OE_Replace by default.
  **
................................................................................
    if( pUpsert->pUpsertTarget==0 ){
      /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
      ** Make all unique constraint resolution be OE_Ignore */
      assert( pUpsert->pUpsertSet==0 );
      overrideError = OE_Ignore;
      pUpsert = 0;
    }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
      /* If the constraint-target is on some column other than
      ** then ROWID, then we might need to move the UPSERT around
      ** so that it occurs in the correct order. */
      sAddr.upsertTop = sAddr.upsertTop2 = sqlite3VdbeMakeLabel(v);
      sAddr.upsertBtm = sqlite3VdbeMakeLabel(v);
    }
  }

  /* If rowid is changing, make sure the new rowid does not previously
  ** exist in the table.
  */
  if( pkChng && pPk==0 ){
................................................................................
    }

    /* If the response to a rowid conflict is REPLACE but the response
    ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
    ** to defer the running of the rowid conflict checking until after
    ** the UNIQUE constraints have run.
    */
    assert( OE_Update>OE_Replace );
    assert( OE_Ignore<OE_Replace );
    assert( OE_Fail<OE_Replace );
    assert( OE_Abort<OE_Replace );
    assert( OE_Rollback<OE_Replace );
    if( onError>=OE_Replace
     && (pUpsert || onError!=overrideError)
     && pTab->pIndex
    ){
      sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;

    }

    if( isUpdate ){
      /* pkChng!=0 does not mean that the rowid has changed, only that
      ** it might have changed.  Skip the conflict logic below if the rowid
      ** is unchanged. */
      sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
................................................................................
      case OE_Ignore: {
        testcase( onError==OE_Ignore );
        sqlite3VdbeGoto(v, ignoreDest);
        break;
      }
    }
    sqlite3VdbeResolveLabel(v, addrRowidOk);
    if( sAddr.ipkTop ){
      sAddr.ipkBtm = sqlite3VdbeAddOp0(v, OP_Goto);
      sqlite3VdbeJumpHere(v, sAddr.ipkTop-1);
    }
  }

  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Compute the revised record entries for indices as we go.
  **
................................................................................
  for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
    int regIdx;          /* Range of registers hold conent for pIdx */
    int regR;            /* Range of registers holding conflicting PK */
    int iThisCur;        /* Cursor for this UNIQUE index */
    int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */

    if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
    if( bAffinityDone==0 ){
      sqlite3TableAffinity(v, pTab, regNewData+1);
      bAffinityDone = 1;
    }
    if( pUpIdx==pIdx ){
      addrUniqueOk = sAddr.upsertBtm;
      upsertBypass = sqlite3VdbeGoto(v, 0);
      VdbeComment((v, "Skip upsert subroutine"));
      sqlite3VdbeResolveLabel(v, sAddr.upsertTop2);

    }else{
      addrUniqueOk = sqlite3VdbeMakeLabel(v);




    }
    VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
    iThisCur = iIdxCur+ix;


    /* Skip partial indices for which the WHERE clause is not true */
    if( pIdx->pPartIdxWhere ){
................................................................................
      if( pUpsert->pUpsertSet==0 ){
        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
      }else{
        onError = OE_Update;  /* DO UPDATE */
      }
    }

    /* Invoke subroutines to handle IPK replace and upsert prior to running
    ** the first REPLACE constraint check. */
    if( onError==OE_Replace ){
      testcase( sAddr.ipkTop );
      testcase( sAddr.upsertTop
             && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
      reorderConstraintChecks(v, &sAddr);
    }

    /* Collision detection may be omitted if all of the following are true:
    **   (1) The conflict resolution algorithm is REPLACE
    **   (2) The table is a WITHOUT ROWID table
    **   (3) There are no secondary indexes on the table
    **   (4) No delete triggers need to be fired if there is a conflict
    **   (5) No FK constraint counters need to be updated if a conflict occurs.
    */ 
................................................................................
            regR, nPkField, 0, OE_Replace,
            (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
        seenReplace = 1;
        break;
      }
    }
    if( pUpIdx==pIdx ){

      sqlite3VdbeJumpHere(v, upsertBypass);
    }else{
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
    }
    if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);

  }
  testcase( sAddr.ipkTop!=0 );
  testcase( sAddr.upsertTop
         && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
  reorderConstraintChecks(v, &sAddr);

  

  *pbMayReplace = seenReplace;
  VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
}

#ifdef SQLITE_ENABLE_NULL_TRIM
/*
** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







<




>
>
>







<







 







>
|
<







>
>
>
>
>







 







|
|
|
|
<







 







|
<
<
<
<
<
|
|

|
>







 







|
|
|







 







<
<
<
<

|


<
>


>
>
>
>







 







<
<
<
<
<
<
<
<
<







 







>





|
|
|
|
|
|
>
|
>







1174
1175
1176
1177
1178
1179
1180






































1181
1182
1183
1184
1185
1186
1187
....
1283
1284
1285
1286
1287
1288
1289

1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303

1304
1305
1306
1307
1308
1309
1310
....
1400
1401
1402
1403
1404
1405
1406
1407
1408

1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
....
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444

1445
1446
1447
1448
1449
1450
1451
....
1469
1470
1471
1472
1473
1474
1475
1476





1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
....
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
....
1589
1590
1591
1592
1593
1594
1595




1596
1597
1598
1599

1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
....
1672
1673
1674
1675
1676
1677
1678









1679
1680
1681
1682
1683
1684
1685
....
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
  testcase( w.eCode==0 );
  testcase( w.eCode==CKCNSTRNT_COLUMN );
  testcase( w.eCode==CKCNSTRNT_ROWID );
  testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  return !w.eCode;
}







































/*
** Generate code to do constraint checks prior to an INSERT or an UPDATE
** on table pTab.
**
** The regNewData parameter is the first register in a range that contains
** the data to be inserted or the data after the update.  There will be
** pTab->nCol+1 registers in this range.  The first register (the one
................................................................................
  int i;               /* loop counter */
  int ix;              /* Index loop counter */
  int nCol;            /* Number of columns */
  int onError;         /* Conflict resolution strategy */
  int addr1;           /* Address of jump instruction */
  int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */

  Index *pUpIdx = 0;   /* Index to which to apply the upsert */
  u8 isUpdate;         /* True if this is an UPDATE operation */
  u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
  int upsertBypass = 0;  /* Address of Goto to bypass upsert subroutine */
  int upsertJump = 0;    /* Address of Goto that jumps into upsert subroutine */
  int ipkTop = 0;        /* Top of the IPK uniqueness check */
  int ipkBottom = 0;     /* OP_Goto at the end of the IPK uniqueness check */

  isUpdate = regOldData!=0;
  db = pParse->db;
  v = sqlite3GetVdbe(pParse);
  assert( v!=0 );
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  nCol = pTab->nCol;

  
  /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
  ** normal rowid tables.  nPkField is the number of key fields in the 
  ** pPk index or 1 for a rowid table.  In other words, nPkField is the
  ** number of fields in the true primary key of the table. */
  if( HasRowid(pTab) ){
    pPk = 0;
................................................................................
    pParse->iSelfTab = 0;
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* UNIQUE and PRIMARY KEY constraints should be handled in the following
  ** order:
  **
  **   (1)  OE_Update
  **   (2)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore

  **   (3)  OE_Replace
  **
  ** OE_Fail and OE_Ignore must happen before any changes are made.
  ** OE_Update guarantees that only a single row will change, so it
  ** must happen before OE_Replace.  Technically, OE_Abort and OE_Rollback
  ** could happen in any order, but they are grouped up front for
  ** convenience.
  **
  ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
  ** The order of constraints used to have OE_Update as (2) and OE_Abort
  ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
  ** constraint before any others, so it had to be moved.
  **
  ** Constraint checking code is generated in this order:
  **   (A)  The rowid constraint
  **   (B)  Unique index constraints that do not have OE_Replace as their
  **        default conflict resolution strategy
  **   (C)  Unique index that do use OE_Replace by default.
  **
................................................................................
    if( pUpsert->pUpsertTarget==0 ){
      /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
      ** Make all unique constraint resolution be OE_Ignore */
      assert( pUpsert->pUpsertSet==0 );
      overrideError = OE_Ignore;
      pUpsert = 0;
    }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
      /* If the constraint-target uniqueness check must be run first.
      ** Jump to that uniqueness check now */
      upsertJump = sqlite3VdbeAddOp0(v, OP_Goto);
      VdbeComment((v, "UPSERT constraint goes first"));

    }
  }

  /* If rowid is changing, make sure the new rowid does not previously
  ** exist in the table.
  */
  if( pkChng && pPk==0 ){
................................................................................
    }

    /* If the response to a rowid conflict is REPLACE but the response
    ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
    ** to defer the running of the rowid conflict checking until after
    ** the UNIQUE constraints have run.
    */
    if( onError==OE_Replace      /* IPK rule is REPLACE */





     && onError!=overrideError   /* Rules for other contraints are different */
     && pTab->pIndex             /* There exist other constraints */
    ){
      ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
      VdbeComment((v, "defer IPK REPLACE until last"));
    }

    if( isUpdate ){
      /* pkChng!=0 does not mean that the rowid has changed, only that
      ** it might have changed.  Skip the conflict logic below if the rowid
      ** is unchanged. */
      sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
................................................................................
      case OE_Ignore: {
        testcase( onError==OE_Ignore );
        sqlite3VdbeGoto(v, ignoreDest);
        break;
      }
    }
    sqlite3VdbeResolveLabel(v, addrRowidOk);
    if( ipkTop ){
      ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
      sqlite3VdbeJumpHere(v, ipkTop-1);
    }
  }

  /* Test all UNIQUE constraints by creating entries for each UNIQUE
  ** index and making sure that duplicate entries do not already exist.
  ** Compute the revised record entries for indices as we go.
  **
................................................................................
  for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
    int regIdx;          /* Range of registers hold conent for pIdx */
    int regR;            /* Range of registers holding conflicting PK */
    int iThisCur;        /* Cursor for this UNIQUE index */
    int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */

    if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */




    if( pUpIdx==pIdx ){
      addrUniqueOk = upsertJump+1;
      upsertBypass = sqlite3VdbeGoto(v, 0);
      VdbeComment((v, "Skip upsert subroutine"));

      sqlite3VdbeJumpHere(v, upsertJump);
    }else{
      addrUniqueOk = sqlite3VdbeMakeLabel(v);
    }
    if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){
      sqlite3TableAffinity(v, pTab, regNewData+1);
      bAffinityDone = 1;
    }
    VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
    iThisCur = iIdxCur+ix;


    /* Skip partial indices for which the WHERE clause is not true */
    if( pIdx->pPartIdxWhere ){
................................................................................
      if( pUpsert->pUpsertSet==0 ){
        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
      }else{
        onError = OE_Update;  /* DO UPDATE */
      }
    }










    /* Collision detection may be omitted if all of the following are true:
    **   (1) The conflict resolution algorithm is REPLACE
    **   (2) The table is a WITHOUT ROWID table
    **   (3) There are no secondary indexes on the table
    **   (4) No delete triggers need to be fired if there is a conflict
    **   (5) No FK constraint counters need to be updated if a conflict occurs.
    */ 
................................................................................
            regR, nPkField, 0, OE_Replace,
            (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
        seenReplace = 1;
        break;
      }
    }
    if( pUpIdx==pIdx ){
      sqlite3VdbeGoto(v, upsertJump+1);
      sqlite3VdbeJumpHere(v, upsertBypass);
    }else{
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
    }
    if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
  }

  /* If the IPK constraint is a REPLACE, run it last */
  if( ipkTop ){
    sqlite3VdbeGoto(v, ipkTop+1);
    VdbeComment((v, "Do IPK REPLACE"));
    sqlite3VdbeJumpHere(v, ipkBottom);
  }

  *pbMayReplace = seenReplace;
  VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
}

#ifdef SQLITE_ENABLE_NULL_TRIM
/*
** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)

Changes to src/vdbe.h.

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







<
<
<







234
235
236
237
238
239
240



241
242
243
244
245
246
247
void sqlite3VdbeRunOnlyOnce(Vdbe*);
void sqlite3VdbeReusable(Vdbe*);
void sqlite3VdbeDelete(Vdbe*);
void sqlite3VdbeClearObject(sqlite3*,Vdbe*);
void sqlite3VdbeMakeReady(Vdbe*,Parse*);
int sqlite3VdbeFinalize(Vdbe*);
void sqlite3VdbeResolveLabel(Vdbe*, int);



int sqlite3VdbeCurrentAddr(Vdbe*);
#ifdef SQLITE_DEBUG
  int sqlite3VdbeAssertMayAbort(Vdbe *, int);
#endif
void sqlite3VdbeResetStepResult(Vdbe*);
void sqlite3VdbeRewind(Vdbe*);
int sqlite3VdbeReset(Vdbe*);

Changes to src/vdbeaux.c.

433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
    }
#endif
    assert( p->aLabel[j]==(-1) ); /* Labels may only be resolved once */
    p->aLabel[j] = v->nOp;
  }
}

#ifdef SQLITE_COVERAGE_TEST
/*
** Return TRUE if and only if the label x has already been resolved.
** Return FALSE (zero) if label x is still unresolved.
**
** This routine is only used inside of testcase() macros, and so it
** only exists when measuring test coverage.
*/
int sqlite3VdbeLabelHasBeenResolved(Vdbe *v, int x){
  return v->pParse->aLabel && v->pParse->aLabel[ADDR(x)]>=0;
}
#endif /* SQLITE_COVERAGE_TEST */

/*
** Mark the VDBE as one that can only be run one time.
*/
void sqlite3VdbeRunOnlyOnce(Vdbe *p){
  p->runOnlyOnce = 1;
}








<
<
<
<
<
<
<
<
<
<
<
<
<







433
434
435
436
437
438
439













440
441
442
443
444
445
446
    }
#endif
    assert( p->aLabel[j]==(-1) ); /* Labels may only be resolved once */
    p->aLabel[j] = v->nOp;
  }
}














/*
** Mark the VDBE as one that can only be run one time.
*/
void sqlite3VdbeRunOnlyOnce(Vdbe *p){
  p->runOnlyOnce = 1;
}

Changes to test/upsert1.test.

123
124
125
126
127
128
129
130



















































































131
  PRAGMA integrity_check;
} {ok}
do_execsql_test upsert1-610 {
  DELETE FROM t1;
  INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
  PRAGMA integrity_check;
} {ok}




















































































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
  PRAGMA integrity_check;
} {ok}
do_execsql_test upsert1-610 {
  DELETE FROM t1;
  INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
  PRAGMA integrity_check;
} {ok}

# 2018-08-14
# Ticket https://www.sqlite.org/src/info/908f001483982c43
# If there are multiple uniqueness contraints, the UPSERT should fire
# if the one constraint it targets fails, regardless of whether or not
# the other constraints pass or fail.  In other words, the UPSERT constraint
# should be tested first.
#
do_execsql_test upsert1-700 {
  DROP TABLE t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
  CREATE UNIQUE INDEX t1b ON t1(b);
  CREATE UNIQUE INDEX t1e ON t1(e);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-710 {
  DELETE FROM t1;
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-720 {
  DELETE FROM t1;
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-730 {
  DROP TABLE t1;
  CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT);
  CREATE UNIQUE INDEX t1a ON t1(a);
  CREATE UNIQUE INDEX t1b ON t1(b);
  CREATE UNIQUE INDEX t1e ON t1(e);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-740 {
  DELETE FROM t1;
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-750 {
  DELETE FROM t1;
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-760 {
  DROP TABLE t1;
  CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) WITHOUT ROWID;
  CREATE UNIQUE INDEX t1a ON t1(a);
  CREATE UNIQUE INDEX t1b ON t1(b);
  CREATE UNIQUE INDEX t1e ON t1(e);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-770 {
  DELETE FROM t1;
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}
do_execsql_test upsert1-780 {
  DELETE FROM t1;
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
  SELECT * FROM t1;
} {1 2 33 4 5}


finish_test