for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
      assert( pIdx );
      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert ){
    pTabList->a[0].iCursor = iDataCur;
    sqlite3UpsertAnalyze(pParse, pTabList, pUpsert);
  }
#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

    for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
      assert( pIdx );
      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert && pUpsert->pUpsertTarget ){
    pTabList->a[0].iCursor = iDataCur;
    sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
  }
#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

{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%type upsert {Upsert*}
%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= upsert(X) ON CONFLICT LP sortlist(Y) RP
              DO UPDATE SET setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,X,Y,Z,W); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON DUPLICATE KEY UPDATE setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,X,0,Z,W); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT LP sortlist(Y) RP DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,Y,0,0); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,0,0,0); /*X-overwrites-A*/ }

%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, $$);}

{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%type upsert {Upsert*}
%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).
              { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W);}
upsert(A) ::= ON DUPLICATE KEY UPDATE setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,0,0,Z,W); }
upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,T,TW,0,0); }
upsert(A) ::= ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,0,0,0,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, $$);}

#define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
#define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
#define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
#define NC_Complex   0x2000  /* True if a function or subquery seen */

/*
** An instance of the following object describes a single ON CONFLICT
** clause in an upsert.  A list of these objects may be attached to
** an INSERT statement in order to form an upsert.
**
** The pUpsertTarget field is only set if the ON CONFLICT clause includes
** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the
** conflict-target clause.)

**
** 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.
**
** The pUpsertIdx is a transient pointer to the unique index described
** by pUpsertTarget.  If pUpsertTarget describes the rowid, then pUpsertIdx
** will be NULL.  pUpsertIdx does not own the Index object it points to.
** Care must be taken to ensure that the Index object does not expire while
** the pointer is valid.
*/
struct Upsert {
  ExprList *pUpsertTarget;  /* Optional description of conflicting index */


  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */
  Index *pUpsertIdx;        /* UNIQUE index referenced by pUpsertTarget */
  Upsert *pUpsertNext;      /* Next ON CONFLICT clause in the list */
};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
................................................................................
  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*,Upsert*,ExprList*,ExprList*,Expr*);
  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
  int sqlite3UpsertAnalyze(Parse*,SrcList*,Upsert*);
#else
#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif

#define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
#define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
#define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
#define NC_Complex   0x2000  /* True if a function or subquery seen */

/*
** An instance of the following object describes a single ON CONFLICT
** clause in an upsert.

**
** The pUpsertTarget field is only set if the ON CONFLICT clause includes
** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the
** conflict-target clause.)  The pUpsertTargetWhere is the optional
** WHERE clause used to identify partial unique indexes.
**
** 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 conflicting index */
  Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
  Index *pUpsertIdx;        /* Constraint that pUpsertTarget identifies */
  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */


};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
................................................................................
  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*);
  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
  int sqlite3UpsertAnalyzeTarget(Parse*,SrcList*,Upsert*);
#else
#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif

#include "sqliteInt.h"

#ifndef SQLITE_OMIT_UPSERT
/*
** Free a list of Upsert objects
*/
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
  while( p ){
    Upsert *pNext = p->pUpsertNext;
    sqlite3ExprListDelete(db, p->pUpsertTarget);
    sqlite3ExprListDelete(db, p->pUpsertSet);
    sqlite3ExprDelete(db, p->pUpsertWhere);
    sqlite3DbFree(db, p);
    p = pNext;
  }
}

/*
** Duplicate an Upsert object.
*/
Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
  if( p==0 ) return 0;
  return sqlite3UpsertNew(db,
           sqlite3UpsertDup(db, p->pUpsertNext),
           sqlite3ExprListDup(db, p->pUpsertTarget, 0),

           sqlite3ExprListDup(db, p->pUpsertSet, 0),
           sqlite3ExprDup(db, p->pUpsertWhere, 0)
         );
}

/*
** Create a new Upsert object.
*/
Upsert *sqlite3UpsertNew(
  sqlite3 *db,           /* Determines which memory allocator to use */
  Upsert *pPrior,        /* Append this upsert to the end of the new one */
  ExprList *pTarget,     /* Target argument to ON CONFLICT, or NULL */

  ExprList *pSet,        /* UPDATE columns, or NULL for a DO NOTHING */
  Expr *pWhere           /* WHERE clause for the ON CONFLICT UPDATE */
){
  Upsert *pNew;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ){
    sqlite3UpsertDelete(db, pPrior);
    sqlite3ExprListDelete(db, pTarget);

    sqlite3ExprListDelete(db, pSet);
    sqlite3ExprDelete(db, pWhere);
    return 0;
  }else{
    pNew->pUpsertTarget = pTarget;

    pNew->pUpsertSet = pSet;
    pNew->pUpsertNext = pPrior;
    pNew->pUpsertWhere = pWhere;
  }
  return pNew;
}

/*
** Analyze the ON CONFLICT clause(s) described by pUpsert.  Resolve all
** symbols in the conflict-target clausees.  Fill in the pUpsertIdx pointers.
**
** Return non-zero if there are errors.

*/
int sqlite3UpsertAnalyze(
  Parse *pParse,     /* The parsing context */
  SrcList *pTabList, /* Table into which we are inserting */
  Upsert *pUpsert    /* The list of ON CONFLICT clauses */
){
  NameContext sNC;
  Upsert *p;
  Table *pTab;
  Index *pIdx;


  int rc = SQLITE_OK;
  int nDoNothing = 0;

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







  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;






  pTab = pTabList->a[0].pTab;
  for(p=pUpsert; p; p=p->pUpsertNext){
    if( p->pUpsertTarget==0 ){
      if( p->pUpsertSet ){
        /* This is a MySQL-style ON DUPLICATE KEY clause.  The ON DUPLICATE
        ** KEY clause can only be used if there is exactly one uniqueness
        ** constraint and/or PRIMARY KEY */
        int nUnique = 0;











        for(pIdx = pTab->pIndex; pIdx; pIdx=pIdx->pNext){

          if( IsUniqueIndex(pIdx) ){
            p->pUpsertIdx = pIdx;
            nUnique++;






          }
        }
        if( pTab->iPKey>=0 ) nUnique++;
        if( nUnique!=0 ){
          sqlite3ErrorMsg(pParse, "ON DUPLICATE KEY may only be used if there "
               "is exactly one UNIQUE or PRIMARY KEY constraint");
          return SQLITE_ERROR;







        }
      }else{
        nDoNothing++;
        if( nDoNothing>1 ){
          sqlite3ErrorMsg(pParse, "multiple unconstrained DO NOTHING clauses");
          return SQLITE_ERROR;







        }
      }
      continue;
    }
    rc = sqlite3ResolveExprListNames(&sNC, p->pUpsertTarget);




    if( rc ) return rc;
  }


  return rc;
}

#endif /* SQLITE_OMIT_UPSERT */

#include "sqliteInt.h"

#ifndef SQLITE_OMIT_UPSERT
/*
** Free a list of Upsert objects
*/
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
  if( p ){
    sqlite3ExprListDelete(db, p->pUpsertTarget);
    sqlite3ExprDelete(db, p->pUpsertTargetWhere);
    sqlite3ExprListDelete(db, p->pUpsertSet);
    sqlite3ExprDelete(db, p->pUpsertWhere);
    sqlite3DbFree(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)
         );
}

/*
** 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 *pNew;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ){

    sqlite3ExprListDelete(db, pTarget);
    sqlite3ExprDelete(db, pTargetWhere);
    sqlite3ExprListDelete(db, pSet);
    sqlite3ExprDelete(db, pWhere);
    return 0;
  }else{
    pNew->pUpsertTarget = pTarget;
    pNew->pUpsertTargetWhere = pTargetWhere;
    pNew->pUpsertSet = pSet;
    pNew->pUpsertWhere = pWhere;
    pNew->pUpsertIdx = 0;
  }
  return pNew;
}

/*
** Analyze the ON CONFLICT clause described by pUpsert.  Resolve all
** symbols in the conflict-target.
**
** Return SQLITE_OK if everything works, or an error code is something
** is wrong.
*/
int sqlite3UpsertAnalyzeTarget(
  Parse *pParse,     /* The parsing context */
  SrcList *pTabList, /* Table into which we are inserting */
  Upsert *pUpsert    /* The ON CONFLICT clauses */
){
  NameContext sNC;

  Table *pTab;
  Index *pIdx;
  ExprList *pTarget;
  Expr *pTerm;
  int rc;


  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;
  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;





  if( HasRowid(pTab) 
   && pTarget->nExpr==1
   && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
   && (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey)
  ){
    /* The conflict-target is the rowid of the primary table */
    assert( pUpsert->pUpsertIdx==0 );
    return SQLITE_OK;
  }

  /* 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, pTabList->a[0].iCursor)!=0 ){
        continue;
      }
    }





    nn = pIdx->nKeyCol;
    for(ii=0; ii<nn; ii++){
      if( pIdx->aiColumn[ii]!=XN_EXPR ){
        for(jj=0; jj<nn; jj++){
          if( pTarget->a[jj].pExpr->op!=TK_COLUMN ) continue;
          if( pTarget->a[jj].pExpr->iColumn!=pIdx->aiColumn[ii] ) continue;
          break;
        }
      }else{




        Expr *pExpr;
        assert( pIdx->aColExpr!=0 );
        assert( pIdx->aColExpr->nExpr>ii );
        pExpr = pIdx->aColExpr->a[ii].pExpr;
        for(jj=0; jj<nn; jj++){
          if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr, -1)==0 ){
            break;
          }
        }

      }

      if( jj<nn ) break;
    }
    if( ii>=nn ) continue;
    pUpsert->pUpsertIdx = pIdx;
    return SQLITE_OK;
  }
  sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
                          "PRIMARY KEY or UNIQUE constraint");
  return SQLITE_ERROR;
}

#endif /* SQLITE_OMIT_UPSERT */

# Test cases for UPSERT

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

do_execsql_test upsert1-100 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);

  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  SELECT * FROM t1;
} {1 2}
do_execsql_test upsert1-101 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
  SELECT * FROM t1;
} {2 3}
do_execsql_test upsert1-102 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(3,4)
      ON CONFLICT(a) DO NOTHING
      ON CONFLICT(b) DO NOTHING;
  SELECT * FROM t1;
} {3 4}
do_catchsql_test upsert1-110 {

  DELETE FROM t1;


  INSERT INTO t1(a,b) VALUES(3,4)
      ON CONFLICT DO NOTHING
      ON CONFLICT DO NOTHING;
} {1 {multiple unconstrained DO NOTHING clauses}}
do_catchsql_test upsert1-120 {
  INSERT INTO t1(a,b) VALUES(3,4)
      ON DUPLICATE KEY UPDATE a=99;
} {1 {ON DUPLICATE KEY may only be used if there is exactly one UNIQUE or PRIMARY KEY constraint}}











finish_test

# Test cases for UPSERT

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

do_execsql_test upsert1-100 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
  CREATE UNIQUE INDEX t1x1 ON t1(b);
  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  SELECT * FROM t1;
} {1 2 0}
do_execsql_test upsert1-101 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
  SELECT * FROM t1;
} {2 3 0}
do_execsql_test upsert1-102 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;


  SELECT * FROM t1;
} {3 4 0}
do_catchsql_test upsert1-110 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
  SELECT * FROM t1;
} {1 {no such column: x}}
do_catchsql_test upsert1-120 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;

  SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_catchsql_test upsert1-130 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
  SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}

if 0 {
do_catchsql_test upsert1-200 {
  DROP INDEX t1x1;
  DELETE FROM t1;
  CREATE UNIQUE INDEX t1x1 ON t1(a||b);
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING;
  SELECT * FROM t1;
} {0 {5 6}}
}

finish_test