SQLite

      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
    aRegIdx[i] = ++pParse->nMem;  /* Register to store the table record */
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert ){
    Upsert *pNx;
    if( IsVirtual(pTab) ){
      sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"",
              pTab->zName);
      goto insert_cleanup;
    }
    if( pTab->pSelect ){
      sqlite3ErrorMsg(pParse, "cannot UPSERT a view");
      goto insert_cleanup;
    }
    if( sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget) ){
      goto insert_cleanup;
    }
    pTabList->a[0].iCursor = iDataCur;
    pNx = pUpsert;
    do{
      pNx->pUpsertSrc = pTabList;
      pNx->regData = regData;
      pNx->iDataCur = iDataCur;
      pNx->iIdxCur = iIdxCur;
      if( pNx->pUpsertTarget ){
        sqlite3UpsertAnalyzeTarget(pParse, pTabList, pNx);
      }
      pNx = pNx->pNextUpsert;
    }while( pNx!=0 );
  }
#endif


  /* This is the top of the main insertion loop */
  if( useTempTable ){
    /* This block codes the top of loop only.  The complete loop is the

  testcase( w.eCode==0 );
  testcase( w.eCode==CKCNSTRNT_COLUMN );
  testcase( w.eCode==CKCNSTRNT_ROWID );
  testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  return w.eCode!=0;
}

/*
** The sqlite3GenerateConstraintChecks() routine usually wants to visit
** the indexes of a table in the order provided in the Table->pIndex list.
** However, sometimes (rarely - when there is an upsert) it wants to visit
** the indexes in a different order.  The following data structures accomplish
** this.
**
** The IndexIterator object is used to walk through all of the indexes
** of a table in either Index.pNext order, or in some other order established
** by an array of IndexListTerm objects.
*/
typedef struct IndexListTerm IndexListTerm;
typedef struct IndexIterator IndexIterator;
struct IndexIterator {
  int eType;    /* 0 for Index.pNext list.  1 for an array of IndexListTerm */
  int i;        /* Index of the current item from the list */
  union {
    struct {    /* Use this object for eType==0: A Index.pNext list */
      Index *pIdx;   /* The current Index */
    } lx;      
    struct {    /* Use this object for eType==1; Array of IndexListTerm */
      int nIdx;               /* Size of the array */
      IndexListTerm *aIdx;    /* Array of IndexListTerms */
    } ax;
  } u;
};

/* When IndexIterator.eType==1, then each index is an array of instances
** of the following object
*/
struct IndexListTerm {
  Index *p;  /* The index */
  int ix;    /* Which entry in the original Table.pIndex list is this index*/
};

/* Return the first index on the list */
static Index *indexIteratorFirst(IndexIterator *pIter, int *pIx){
  assert( pIter->i==0 );
  if( pIter->eType ){
    *pIx = pIter->u.ax.aIdx[0].ix;
    return pIter->u.ax.aIdx[0].p;
  }else{
    *pIx = 0;
    return pIter->u.lx.pIdx;
  }
}

/* Return the next index from the list.  Return NULL when out of indexes */
static Index *indexIteratorNext(IndexIterator *pIter, int *pIx){
  if( pIter->eType ){
    int i = ++pIter->i;
    if( i>=pIter->u.ax.nIdx ){
      *pIx = i;
      return 0;
    }
    *pIx = pIter->u.ax.aIdx[i].ix;
    return pIter->u.ax.aIdx[i].p;
  }else{
    ++(*pIx);
    pIter->u.lx.pIdx = pIter->u.lx.pIdx->pNext;
    return pIter->u.lx.pIdx;
  }
}
  
/*
** 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 ignoreDest,      /* Jump to this label on an OE_Ignore resolution */
  int *pbMayReplace,   /* OUT: Set to true if constraint may cause a replace */
  int *aiChng,         /* column i is unchanged if aiChng[i]<0 */
  Upsert *pUpsert      /* ON CONFLICT clauses, if any.  NULL otherwise */
){
  Vdbe *v;             /* VDBE under constrution */
  Index *pIdx;         /* Pointer to one of the indices */
  Index *pPk = 0;      /* The PRIMARY KEY index for WITHOUT ROWID tables */
  sqlite3 *db;         /* Database connection */
  int i;               /* loop counter */
  int ix;              /* Index loop counter */
  int nCol;            /* Number of columns */
  int onError;         /* Conflict resolution strategy */
  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 */
  Upsert *pUpsertClause = 0;  /* The specific ON CONFLICT clause for pIdx */
  u8 isUpdate;           /* True if this is an UPDATE operation */
  u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
  int upsertIpkReturn = 0; /* Address of Goto at end of IPK uniqueness check */
  int upsertIpkDelay = 0;  /* Address of Goto to bypass initial IPK check */
  int ipkTop = 0;        /* Top of the IPK uniqueness check */
  int ipkBottom = 0;     /* OP_Goto at the end of the IPK uniqueness check */
  /* Variables associated with retesting uniqueness constraints after
  ** replace triggers fire have run */
  int regTrigCnt;       /* Register used to count replace trigger invocations */
  int addrRecheck = 0;  /* Jump here to recheck all uniqueness constraints */
  int lblRecheckOk = 0; /* Each recheck jumps to this label if it passes */
  Trigger *pTrigger;    /* List of DELETE triggers on the table pTab */
  int nReplaceTrig = 0; /* Number of replace triggers coded */
  IndexIterator sIdxIter;  /* Index iterator */

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

  **        default conflict resolution strategy
  **   (C)  Unique index that do use OE_Replace by default.
  **
  ** The ordering of (2) and (3) is accomplished by making sure the linked
  ** list of indexes attached to a table puts all OE_Replace indexes last
  ** in the list.  See sqlite3CreateIndex() for where that happens.
  */
  sIdxIter.eType = 0;
  sIdxIter.i = 0;
  sIdxIter.u.ax.aIdx = 0;  /* Silence harmless compiler warning */
  sIdxIter.u.lx.pIdx = pTab->pIndex;
  if( pUpsert ){
    if( pUpsert->pUpsertTarget==0 ){
      /* There is just on ON CONFLICT clause and it has no constraint-target */
      assert( pUpsert->pNextUpsert==0 );
      if( pUpsert->isDoUpdate==0 ){
        /* A single ON CONFLICT DO NOTHING clause, without a constraint-target.
        ** Make all unique constraint resolution be OE_Ignore */

        overrideError = OE_Ignore;
        pUpsert = 0;
      }else{
        /* A single ON CONFLICT DO UPDATE.  Make all resolutions OE_Update */
        overrideError = OE_Update;
      }
    }else if( pTab->pIndex!=0 ){
      /* Otherwise, we'll need to run the IndexListTerm array version of the
      ** iterator to ensure that all of the ON CONFLICT conditions are
      ** checked first and in order. */
      int nIdx, jj;
      u64 nByte;
      Upsert *pTerm;
      u8 *bUsed;
      for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
         assert( aRegIdx[nIdx]>0 );
      }
      sIdxIter.eType = 1;
      sIdxIter.u.ax.nIdx = nIdx;
      nByte = (sizeof(IndexListTerm)+1)*nIdx + nIdx;
      sIdxIter.u.ax.aIdx = sqlite3DbMallocZero(db, nByte);

      if( sIdxIter.u.ax.aIdx==0 ) return; /* OOM */
      bUsed = (u8*)&sIdxIter.u.ax.aIdx[nIdx];
      pUpsert->pToFree = sIdxIter.u.ax.aIdx;
      for(i=0, pTerm=pUpsert; pTerm; pTerm=pTerm->pNextUpsert){
        if( pTerm->pUpsertTarget==0 ) break;
        if( pTerm->pUpsertIdx==0 ) continue;  /* Skip ON CONFLICT for the IPK */
        jj = 0;
        pIdx = pTab->pIndex;
        while( ALWAYS(pIdx!=0) && pIdx!=pTerm->pUpsertIdx ){
           pIdx = pIdx->pNext;
           jj++;
        }
        if( bUsed[jj] ) continue; /* Duplicate ON CONFLICT clause ignored */
        bUsed[jj] = 1;
        sIdxIter.u.ax.aIdx[i].p = pIdx;
        sIdxIter.u.ax.aIdx[i].ix = jj;
        i++;
      }
      for(jj=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, jj++){
        if( bUsed[jj] ) continue;
        sIdxIter.u.ax.aIdx[i].p = pIdx;
        sIdxIter.u.ax.aIdx[i].ix = jj;
        i++;
      }
      assert( i==nIdx );
    }
  }

  /* Determine if it is possible that triggers (either explicitly coded
  ** triggers or FK resolution actions) might run as a result of deletes
  ** that happen when OE_Replace conflict resolution occurs. (Call these
  ** "replace triggers".)  If any replace triggers run, we will need to

    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }

    /* figure out whether or not upsert applies in this case */
    if( pUpsert ){
      pUpsertClause = sqlite3UpsertOfIndex(pUpsert,0);
      if( pUpsertClause!=0 ){
        if( pUpsertClause->isDoUpdate==0 ){
          onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
        }else{
          onError = OE_Update;  /* DO UPDATE */
        }
      }
      if( pUpsertClause!=pUpsert ){
        /* The first ON CONFLICT clause has a conflict target other than
        ** the IPK.  We have to jump ahead to that first ON CONFLICT clause
        ** and then come back here and deal with the IPK afterwards */
        upsertIpkDelay = sqlite3VdbeAddOp0(v, OP_Goto);
      }
    }

    /* 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.

      case OE_Ignore: {
        testcase( onError==OE_Ignore );
        sqlite3VdbeGoto(v, ignoreDest);
        break;
      }
    }
    sqlite3VdbeResolveLabel(v, addrRowidOk);
    if( pUpsert && pUpsertClause!=pUpsert ){
      upsertIpkReturn = sqlite3VdbeAddOp0(v, OP_Goto);
    }else 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.
  **
  ** This loop also handles the case of the PRIMARY KEY index for a
  ** WITHOUT ROWID table.
  */
  for(pIdx = indexIteratorFirst(&sIdxIter, &ix);
      pIdx;
      pIdx = indexIteratorNext(&sIdxIter, &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 */
    int addrConflictCk;  /* First opcode in the conflict check logic */

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

      pUpsertClause = sqlite3UpsertOfIndex(pUpsert, pIdx);
      if( upsertIpkDelay && pUpsertClause==pUpsert ){
        sqlite3VdbeJumpHere(v, upsertIpkDelay);


      }
    }
    addrUniqueOk = sqlite3VdbeMakeLabel(pParse);
    if( bAffinityDone==0 ){
      sqlite3TableAffinity(v, pTab, regNewData+1);
      bAffinityDone = 1;
    }
    VdbeNoopComment((v, "prep index %s", pIdx->zName));
    iThisCur = iIdxCur+ix;

    if( overrideError!=OE_Default ){
      onError = overrideError;
    }else if( onError==OE_Default ){
      onError = OE_Abort;
    }

    /* Figure out if the upsert clause applies to this index */
    if( pUpsertClause ){
      if( pUpsertClause->isDoUpdate==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:

    /* Check to see if the new index entry will be unique */
    sqlite3VdbeVerifyAbortable(v, onError);
    addrConflictCk = 
      sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
                           regIdx, pIdx->nKeyCol); VdbeCoverage(v);

    /* Generate code to handle collisions */
    regR = pIdx==pPk ? regIdx : sqlite3GetTempRange(pParse, nPkField);
    if( isUpdate || onError==OE_Replace ){
      if( HasRowid(pTab) ){
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iThisCur, regR);
        /* Conflict only if the rowid of the existing index entry
        ** is different from old-rowid */
        if( isUpdate ){
          sqlite3VdbeAddOp3(v, OP_Eq, regR, addrUniqueOk, regOldData);

          sqlite3VdbeJumpHere(v, addrBypass); /* Terminate the recheck bypass */
        }
        seenReplace = 1;
        break;
      }
    }
    sqlite3VdbeResolveLabel(v, addrUniqueOk);
    if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
    if( pUpsertClause 
     && upsertIpkReturn
     && sqlite3UpsertNextIsIPK(pUpsertClause)
    ){
      sqlite3VdbeGoto(v, upsertIpkDelay+1);
      sqlite3VdbeJumpHere(v, upsertIpkReturn);
      upsertIpkReturn = 0;

    }

  }

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

// Because upsert only occurs at the tip end of the INSERT rule for cmd,
// there is never a case where the value of the upsert pointer will not
// be destroyed by the cmd action.  So comment-out the destructor to
// avoid unreachable code.
//%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW)
              DO UPDATE SET setlist(Z) where_opt(W) upsert(N).
              { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W,N);}
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING upsert(N).
              { A = sqlite3UpsertNew(pParse->db,T,TW,0,0,N); }
upsert(A) ::= ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,0,0,0,0,0); }
upsert(A) ::= ON CONFLICT DO UPDATE SET setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,0,0,Z,W,0);}

%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}

%type idlist_opt {IdList*}
%destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}

** the operation in progress stops and returns an error code.  But prior
** changes due to the same operation are not backed out and no rollback
** occurs.  IGNORE means that the particular row that caused the constraint
** error is not inserted or updated.  Processing continues and no error
** is returned.  REPLACE means that preexisting database rows that caused
** a UNIQUE constraint violation are removed so that the new insert or
** update can proceed.  Processing continues and no error is reported.
** UPDATE applies to insert operations only and means that the insert
** is omitted and the DO UPDATE clause of an upsert is run instead.
**
** RESTRICT, SETNULL, SETDFLT, and CASCADE actions apply only to foreign keys.
** RESTRICT is the same as ABORT for IMMEDIATE foreign keys and the
** same as ROLLBACK for DEFERRED keys.  SETNULL means that the foreign
** key is set to NULL.  SETDFLT means that the foreign key is set
** to its default value.  CASCADE means that a DELETE or UPDATE of the
** referenced table row is propagated into the row that holds the
** foreign key.
**
** The OE_Default value is a place holder that means to use whatever
** conflict resolution algorthm is required from context.
**
** The following symbolic values are used to record which type
** of conflict resolution action to take.
*/
#define OE_None     0   /* There is no constraint to check */
#define OE_Rollback 1   /* Fail the operation and rollback the transaction */
#define OE_Abort    2   /* Back out changes but do no rollback transaction */
#define OE_Fail     3   /* Stop the operation but leave all prior changes */
#define OE_Ignore   4   /* Ignore the error. Do not do the INSERT or UPDATE */
#define OE_Replace  5   /* Delete existing record, then do INSERT or UPDATE */

**
** pUpsertSet is the list of column=expr terms of the UPDATE statement. 
** The pUpsertSet field is NULL for a ON CONFLICT DO NOTHING.  The
** pUpsertWhere is the WHERE clause for the UPDATE and is NULL if the
** WHERE clause is omitted.
*/
struct Upsert {
  ExprList *pUpsertTarget;  /* Optional description of conflict target */
  Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */
  Upsert *pNextUpsert;      /* Next ON CONFLICT clause in the list */
  u8 isDoUpdate;            /* True for DO UPDATE.  False for DO NOTHING */
  /* Above this point is the parse tree for the ON CONFLICT clauses.
  ** The next group of fields stores intermediate data. */
  void *pToFree;            /* Free memory when deleting the Upsert object */
  /* All fields above are owned by the Upsert object and must be freed
  ** when the Upsert is destroyed.  The fields below are used to transfer
  ** information from the INSERT processing down into the UPDATE processing

  ** while generating code.  The fields below are owned by the INSERT
  ** statement and will be freed by INSERT processing. */
  Index *pUpsertIdx;        /* UNIQUE constraint specified by pUpsertTarget */
  SrcList *pUpsertSrc;      /* Table to be updated */
  int regData;              /* First register holding array of VALUES */
  int iDataCur;             /* Index of the data cursor */
  int iIdxCur;              /* Index of the first index cursor */
};

/*

  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)
#endif
#ifndef SQLITE_OMIT_UPSERT
  Upsert *sqlite3UpsertNew(sqlite3*,ExprList*,Expr*,ExprList*,Expr*,Upsert*);
  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
  int sqlite3UpsertAnalyzeTarget(Parse*,SrcList*,Upsert*);
  void sqlite3UpsertDoUpdate(Parse*,Upsert*,Table*,Index*,int);
  Upsert *sqlite3UpsertOfIndex(Upsert*,Index*);
  int sqlite3UpsertNextIsIPK(Upsert*);
#else
#define sqlite3UpsertNew(u,v,w,x,y,z) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)         ((Upsert*)0)
#define sqlite3UpsertOfIndex(x,y)     ((Upsert*)0)
#define sqlite3UpsertNextIsIPK(x)     0
#endif


/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In

*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_UPSERT
/*
** Free a list of Upsert objects
*/
static void SQLITE_NOINLINE upsertDelete(sqlite3 *db, Upsert *p){
  do{
    Upsert *pNext = p->pNextUpsert;
    sqlite3ExprListDelete(db, p->pUpsertTarget);
    sqlite3ExprDelete(db, p->pUpsertTargetWhere);
    sqlite3ExprListDelete(db, p->pUpsertSet);
    sqlite3ExprDelete(db, p->pUpsertWhere);
    sqlite3DbFree(db, p->pToFree);
    sqlite3DbFree(db, p);
    p = pNext;
  }while( p );
}
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
  if( p ) upsertDelete(db, p);
}


/*
** Duplicate an Upsert object.
*/
Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
  if( p==0 ) return 0;
  return sqlite3UpsertNew(db,
           sqlite3ExprListDup(db, p->pUpsertTarget, 0),
           sqlite3ExprDup(db, p->pUpsertTargetWhere, 0),
           sqlite3ExprListDup(db, p->pUpsertSet, 0),
           sqlite3ExprDup(db, p->pUpsertWhere, 0),
           sqlite3UpsertDup(db, p->pNextUpsert)
         );
}

/*
** Create a new Upsert object.
*/
Upsert *sqlite3UpsertNew(
  sqlite3 *db,           /* Determines which memory allocator to use */
  ExprList *pTarget,     /* Target argument to ON CONFLICT, or NULL */
  Expr *pTargetWhere,    /* Optional WHERE clause on the target */
  ExprList *pSet,        /* UPDATE columns, or NULL for a DO NOTHING */
  Expr *pWhere,          /* WHERE clause for the ON CONFLICT UPDATE */
  Upsert *pNext          /* Next ON CONFLICT clause in the list */
){
  Upsert *pNew;
  pNew = sqlite3DbMallocZero(db, sizeof(Upsert));
  if( pNew==0 ){
    sqlite3ExprListDelete(db, pTarget);
    sqlite3ExprDelete(db, pTargetWhere);
    sqlite3ExprListDelete(db, pSet);
    sqlite3ExprDelete(db, pWhere);
    sqlite3UpsertDelete(db, pNext);
    return 0;
  }else{
    pNew->pUpsertTarget = pTarget;
    pNew->pUpsertTargetWhere = pTargetWhere;
    pNew->pUpsertSet = pSet;
    pNew->pUpsertWhere = pWhere;
    pNew->isDoUpdate = pSet!=0;
    pNew->pNextUpsert = pNext;
  }
  return pNew;
}

/*
** Analyze the ON CONFLICT clause described by pUpsert.  Resolve all
** symbols in the conflict-target.

  int rc;                 /* Result code */
  int iCursor;            /* Cursor used by pTab */
  Index *pIdx;            /* One of the indexes of pTab */
  ExprList *pTarget;      /* The conflict-target clause */
  Expr *pTerm;            /* One term of the conflict-target clause */
  NameContext sNC;        /* Context for resolving symbolic names */
  Expr sCol[2];           /* Index column converted into an Expr */
  int nClause = 0;        /* Counter of ON CONFLICT clauses */

  assert( pTabList->nSrc==1 );
  assert( pTabList->a[0].pTab!=0 );
  assert( pUpsert!=0 );
  assert( pUpsert->pUpsertTarget!=0 );

  /* Resolve all symbolic names in the conflict-target clause, which
  ** includes both the list of columns and the optional partial-index
  ** WHERE clause.
  */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  for(; pUpsert && pUpsert->pUpsertTarget;
        pUpsert=pUpsert->pNextUpsert, nClause++){
    rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
    if( rc ) return rc;
    rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
    if( rc ) return rc;
  
    /* Check to see if the conflict target matches the rowid. */  
    pTab = pTabList->a[0].pTab;
    pTarget = pUpsert->pUpsertTarget;
    iCursor = pTabList->a[0].iCursor;
    if( HasRowid(pTab) 
     && pTarget->nExpr==1
     && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
     && pTerm->iColumn==XN_ROWID
    ){
      /* The conflict-target is the rowid of the primary table */
      assert( pUpsert->pUpsertIdx==0 );

      continue;
    }
  
    /* Initialize sCol[0..1] to be an expression parse tree for a
    ** single column of an index.  The sCol[0] node will be the TK_COLLATE
    ** operator and sCol[1] will be the TK_COLUMN operator.  Code below
    ** will populate the specific collation and column number values
    ** prior to comparing against the conflict-target expression.
    */
    memset(sCol, 0, sizeof(sCol));
    sCol[0].op = TK_COLLATE;
    sCol[0].pLeft = &sCol[1];
    sCol[1].op = TK_COLUMN;
    sCol[1].iTable = pTabList->a[0].iCursor;
  
    /* Check for matches against other indexes */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      int ii, jj, nn;
      if( !IsUniqueIndex(pIdx) ) continue;
      if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
      if( pIdx->pPartIdxWhere ){
        if( pUpsert->pUpsertTargetWhere==0 ) continue;
        if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
                               pIdx->pPartIdxWhere, iCursor)!=0 ){
          continue;
        }
      }
      nn = pIdx->nKeyCol;
      for(ii=0; ii<nn; ii++){
        Expr *pExpr;
        sCol[0].u.zToken = (char*)pIdx->azColl[ii];
        if( pIdx->aiColumn[ii]==XN_EXPR ){
          assert( pIdx->aColExpr!=0 );
          assert( pIdx->aColExpr->nExpr>ii );
          pExpr = pIdx->aColExpr->a[ii].pExpr;
          if( pExpr->op!=TK_COLLATE ){
            sCol[0].pLeft = pExpr;
            pExpr = &sCol[0];
          }
        }else{
          sCol[0].pLeft = &sCol[1];
          sCol[1].iColumn = pIdx->aiColumn[ii];
          pExpr = &sCol[0];
        }
        for(jj=0; jj<nn; jj++){
          if( sqlite3ExprCompare(pParse,pTarget->a[jj].pExpr,pExpr,iCursor)<2 ){
            break;  /* Column ii of the index matches column jj of target */
          }
        }
        if( jj>=nn ){
          /* The target contains no match for column jj of the index */
          break;
        }
      }
      if( ii<nn ){
        /* Column ii of the index did not match any term of the conflict target.
        ** Continue the search with the next index. */
        continue;
      }
      pUpsert->pUpsertIdx = pIdx;
      break;
    }
    if( pUpsert->pUpsertIdx==0 ){
      char zWhich[16];
      if( nClause==0 && pUpsert->pNextUpsert==0 ){
        zWhich[0] = 0;
      }else{
        sqlite3_snprintf(sizeof(zWhich),zWhich,"%r ", nClause+1);
      }
      sqlite3ErrorMsg(pParse, "%sON CONFLICT clause does not match any "
                              "PRIMARY KEY or UNIQUE constraint", zWhich);
      return SQLITE_ERROR;
    }
  }
  return SQLITE_OK;
}

/*
** Return true if pUpsert is the last ON CONFLICT clause with a
** conflict target, or if pUpsert is followed by another ON CONFLICT
** clause that targets the INTEGER PRIMARY KEY.
*/
int sqlite3UpsertNextIsIPK(Upsert *pUpsert){
  Upsert *pNext;
  if( NEVER(pUpsert==0) ) return 0;
  pNext = pUpsert->pNextUpsert;
  if( pNext==0 ) return 1;
  if( pNext->pUpsertTarget==0 ) return 1;
  if( pNext->pUpsertIdx==0 ) return 1;
  return 0;
}

/*
** Given the list of ON CONFLICT clauses described by pUpsert, and
** a particular index pIdx, return a pointer to the particular ON CONFLICT
** clause that applies to the index.  Or, if the index is not subject to
** any ON CONFLICT clause, return NULL.
*/
Upsert *sqlite3UpsertOfIndex(Upsert *pUpsert, Index *pIdx){
  while(
      pUpsert
   && pUpsert->pUpsertTarget!=0
   && pUpsert->pUpsertIdx!=pIdx
  ){
     pUpsert = pUpsert->pNextUpsert;
  }
  return pUpsert;
}

/*
** Generate bytecode that does an UPDATE as part of an upsert.
**
** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK.
** In this case parameter iCur is a cursor open on the table b-tree that

  int iCur              /* Cursor for pIdx (or pTab if pIdx==NULL) */
){
  Vdbe *v = pParse->pVdbe;
  sqlite3 *db = pParse->db;
  SrcList *pSrc;            /* FROM clause for the UPDATE */
  int iDataCur;
  int i;
  Upsert *pTop = pUpsert;

  assert( v!=0 );
  assert( pUpsert!=0 );

  iDataCur = pUpsert->iDataCur;
  pUpsert = sqlite3UpsertOfIndex(pTop, pIdx);
  VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
  if( pIdx && iCur!=iDataCur ){
    if( HasRowid(pTab) ){
      int regRowid = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regRowid);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, iDataCur, 0, regRowid);
      VdbeCoverage(v);
      sqlite3ReleaseTempReg(pParse, regRowid);

      VdbeCoverage(v);
      sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CORRUPT, OE_Abort, 0, 
            "corrupt database", P4_STATIC);
      sqlite3MayAbort(pParse);
      sqlite3VdbeJumpHere(v, i);
    }
  }
  /* pUpsert does not own pTop->pUpsertSrc - the outer INSERT statement does.
  ** So we have to make a copy before passing it down into sqlite3Update() */
  pSrc = sqlite3SrcListDup(db, pTop->pUpsertSrc, 0);
  /* excluded.* columns of type REAL need to be converted to a hard real */
  for(i=0; i<pTab->nCol; i++){
    if( pTab->aCol[i].affinity==SQLITE_AFF_REAL ){
      sqlite3VdbeAddOp1(v, OP_RealAffinity, pTop->regData+i);
    }
  }
  sqlite3Update(pParse, pSrc, sqlite3ExprListDup(db,pUpsert->pUpsertSet,0),
      sqlite3ExprDup(db,pUpsert->pUpsertWhere,0), OE_Abort, 0, 0, pUpsert);


  VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
}

#endif /* SQLITE_OMIT_UPSERT */

# 2020-12-11
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for generalized UPSERT

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix upsert5

foreach {tn sql} {
  1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) }
  2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) }
  3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) WITHOUT ROWID}
  4 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INTEGER PRIMARY KEY, b) }
  5 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INT PRIMARY KEY, b) }
  6 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INT PRIMARY KEY, b) WITHOUT ROWID}
} {
  reset_db
  execsql $sql

  do_execsql_test 1.$tn.100 {
    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,NULL,3,4,5)
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 a 3 4 5}
  do_execsql_test 1.$tn.101 {
    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(91,NULL,3,4,5)
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 c 3 4 5}
  do_execsql_test 1.$tn.102 {
    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(91,NULL,93,4,5)
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 d 3 4 5}
  do_execsql_test 1.$tn.103 {
    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(91,NULL,93,94,5)
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 e 3 4 5}
  do_execsql_test 1.$tn.200 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 a 3 4 5}
  do_execsql_test 1.$tn.201 {
    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,NULL,3,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 c 3 4 5}
  do_execsql_test 1.$tn.202 {
    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,NULL,3,4,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 c 3 4 5}
  do_execsql_test 1.$tn.203 {
    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,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 a 3 4 5}
  do_execsql_test 1.$tn.204 {
    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,NULL,93,4,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 a 3 4 5}
  do_execsql_test 1.$tn.210 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 a 3 4 5}
  do_execsql_test 1.$tn.211 {
    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,NULL,93,4,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 d 3 4 5}
  do_execsql_test 1.$tn.212 {
    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,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 a 3 4 5}
  do_execsql_test 1.$tn.213 {
    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(91,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(a) DO UPDATE SET b='a'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 e 3 4 5}
  do_execsql_test 1.$tn.214 {
    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(91,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e'
      ON CONFLICT(a) DO UPDATE SET b='a';
    SELECT a,b,c,d,e FROM t1;
  } {1 e 3 4 5}
  do_execsql_test 1.$tn.215 {
    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,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e'
      ON CONFLICT(a) DO UPDATE SET b='a';
    SELECT a,b,c,d,e FROM t1;
  } {1 e 3 4 5}
  do_execsql_test 1.$tn.216 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(e) DO UPDATE SET b='e'
      ON CONFLICT(a) DO UPDATE SET b='a';
    SELECT a,b,c,d,e FROM t1;
  } {1 a 3 4 5}

  do_execsql_test 1.$tn.300 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(a) DO UPDATE SET b='a1'
      ON CONFLICT(a) DO UPDATE SET b='a2'
      ON CONFLICT(a) DO UPDATE SET b='a3'
      ON CONFLICT(a) DO UPDATE SET b='a4'
      ON CONFLICT(a) DO UPDATE SET b='a5'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 a1 3 4 5}
  do_execsql_test 1.$tn.301 {
    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(91,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT(a) DO UPDATE SET b='a1'
      ON CONFLICT(a) DO UPDATE SET b='a2'
      ON CONFLICT(a) DO UPDATE SET b='a3'
      ON CONFLICT(a) DO UPDATE SET b='a4'
      ON CONFLICT(a) DO UPDATE SET b='a5'
      ON CONFLICT(e) DO UPDATE SET b='e';
    SELECT a,b,c,d,e FROM t1;
  } {1 e 3 4 5}

  do_execsql_test 1.$tn.400 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.401 {
    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(91,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.402 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.403 {
    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(91,NULL,3,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 c 3 4 5}
  do_execsql_test 1.$tn.404 {
    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(91,NULL,3,4,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 c 3 4 5}
  do_execsql_test 1.$tn.405 {
    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,NULL,93,4,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 d 3 4 5}

  do_execsql_test 1.$tn.410 {
    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,NULL,93,94,95)
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.411 {
    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(91,NULL,93,94,5)
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.412 {
    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(91,NULL,93,4,95)
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.413 {
    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(91,NULL,3,94,95)
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}

  do_execsql_test 1.$tn.420 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO NOTHING
      ON CONFLICT(d) DO NOTHING
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.421 {
    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(91,NULL,93,94,5)
      ON CONFLICT(c) DO NOTHING
      ON CONFLICT(d) DO NOTHING
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 x 3 4 5}
  do_execsql_test 1.$tn.422 {
    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(91,NULL,93,4,95)
      ON CONFLICT(c) DO NOTHING
      ON CONFLICT(d) DO NOTHING
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 2 3 4 5}
  do_execsql_test 1.$tn.423 {
    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(91,NULL,3,94,95)
      ON CONFLICT(c) DO NOTHING
      ON CONFLICT(d) DO NOTHING
      ON CONFLICT DO UPDATE set b='x';
    SELECT a,b,c,d,e FROM t1;
  } {1 2 3 4 5}

  do_execsql_test 1.$tn.500 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO NOTHING;
    SELECT a,b,c,d,e FROM t1;
  } {1 2 3 4 5}
  do_execsql_test 1.$tn.501 {
    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(91,NULL,93,94,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO NOTHING;
    SELECT a,b,c,d,e FROM t1;
  } {1 2 3 4 5}
  do_execsql_test 1.$tn.502 {
    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,NULL,93,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO NOTHING;
    SELECT a,b,c,d,e FROM t1;
  } {1 2 3 4 5}
  do_execsql_test 1.$tn.503 {
    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(91,NULL,3,94,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO NOTHING;
    SELECT a,b,c,d,e FROM t1;
  } {1 c 3 4 5}
  do_execsql_test 1.$tn.504 {
    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(91,NULL,3,4,95)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO NOTHING;
    SELECT a,b,c,d,e FROM t1;
  } {1 c 3 4 5}
  do_execsql_test 1.$tn.505 {
    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,NULL,93,4,5)
      ON CONFLICT(c) DO UPDATE SET b='c'
      ON CONFLICT(d) DO UPDATE SET b='d'
      ON CONFLICT DO NOTHING;
    SELECT a,b,c,d,e FROM t1;
  } {1 d 3 4 5}

}

finish_test