/ Check-in [fba24aec]
Login

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

Overview
Comment:Add support for PostgreSQL UPSERT syntax and functionality.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: fba24aec235dd6f124c1ece66cdac485c9d96e0578c4fbacd8f49d7b767a5b4a
User & Date: drh 2018-04-18 16:03:36
Context
2018-04-18
17:52
Fix a problem in the sqlite3ExprCompare() function that caused two dissimilar expressions to match if they have the same "COLLATE name" at the outer layer. check-in: fb16348a user: drh tags: trunk
16:03
Add support for PostgreSQL UPSERT syntax and functionality. check-in: fba24aec user: drh tags: trunk
15:33
Fix the build for SQLITE_OMIT_UPSERT Closed-Leaf check-in: 32956519 user: drh tags: upsert
11:35
Enhance ALTER TABLE ADD COLUMN to support "DEFAULT true" and "DEFAULT false". check-in: 594ebc69 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to Makefile.in.

   183    183            memdb.lo memjournal.lo \
   184    184            mutex.lo mutex_noop.lo mutex_unix.lo mutex_w32.lo \
   185    185            notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \
   186    186            pager.lo parse.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \
   187    187            random.lo resolve.lo rowset.lo rtree.lo \
   188    188            sqlite3session.lo select.lo sqlite3rbu.lo status.lo stmt.lo \
   189    189            table.lo threads.lo tokenize.lo treeview.lo trigger.lo \
   190         -         update.lo util.lo vacuum.lo \
          190  +         update.lo upsert.lo util.lo vacuum.lo \
   191    191            vdbe.lo vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \
   192    192            vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo whereexpr.lo \
   193    193            utf.lo vtab.lo
   194    194   
   195    195   # Object files for the amalgamation.
   196    196   #
   197    197   LIBOBJS1 = sqlite3.lo
................................................................................
   280    280     $(TOP)/src/tclsqlite.c \
   281    281     $(TOP)/src/threads.c \
   282    282     $(TOP)/src/tokenize.c \
   283    283     $(TOP)/src/treeview.c \
   284    284     $(TOP)/src/trigger.c \
   285    285     $(TOP)/src/utf.c \
   286    286     $(TOP)/src/update.c \
          287  +  $(TOP)/src/upsert.c \
   287    288     $(TOP)/src/util.c \
   288    289     $(TOP)/src/vacuum.c \
   289    290     $(TOP)/src/vdbe.c \
   290    291     $(TOP)/src/vdbe.h \
   291    292     $(TOP)/src/vdbeapi.c \
   292    293     $(TOP)/src/vdbeaux.c \
   293    294     $(TOP)/src/vdbeblob.c \
................................................................................
   910    911   
   911    912   trigger.lo:	$(TOP)/src/trigger.c $(HDR)
   912    913   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/trigger.c
   913    914   
   914    915   update.lo:	$(TOP)/src/update.c $(HDR)
   915    916   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/update.c
   916    917   
          918  +upsert.lo:	$(TOP)/src/upsert.c $(HDR)
          919  +	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/upsert.c
          920  +
   917    921   utf.lo:	$(TOP)/src/utf.c $(HDR)
   918    922   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/utf.c
   919    923   
   920    924   util.lo:	$(TOP)/src/util.c $(HDR)
   921    925   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/util.c
   922    926   
   923    927   vacuum.lo:	$(TOP)/src/vacuum.c $(HDR)

Changes to Makefile.msc.

  1189   1189            memdb.lo memjournal.lo \
  1190   1190            mutex.lo mutex_noop.lo mutex_unix.lo mutex_w32.lo \
  1191   1191            notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \
  1192   1192            pager.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \
  1193   1193            random.lo resolve.lo rowset.lo rtree.lo \
  1194   1194            sqlite3session.lo select.lo sqlite3rbu.lo status.lo \
  1195   1195            table.lo threads.lo tokenize.lo treeview.lo trigger.lo \
  1196         -         update.lo util.lo vacuum.lo \
         1196  +         update.lo upsert.lo util.lo vacuum.lo \
  1197   1197            vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \
  1198   1198            vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo whereexpr.lo \
  1199   1199            utf.lo vtab.lo
  1200   1200   # <</mark>>
  1201   1201   
  1202   1202   # Object files for the amalgamation.
  1203   1203   #
................................................................................
  1288   1288     $(TOP)\src\threads.c \
  1289   1289     $(TOP)\src\tclsqlite.c \
  1290   1290     $(TOP)\src\tokenize.c \
  1291   1291     $(TOP)\src\treeview.c \
  1292   1292     $(TOP)\src\trigger.c \
  1293   1293     $(TOP)\src\utf.c \
  1294   1294     $(TOP)\src\update.c \
         1295  +  $(TOP)\src\upsert.c \
  1295   1296     $(TOP)\src\util.c \
  1296   1297     $(TOP)\src\vacuum.c \
  1297   1298     $(TOP)\src\vdbe.c \
  1298   1299     $(TOP)\src\vdbeapi.c \
  1299   1300     $(TOP)\src\vdbeaux.c \
  1300   1301     $(TOP)\src\vdbeblob.c \
  1301   1302     $(TOP)\src\vdbemem.c \
................................................................................
  1989   1990   
  1990   1991   trigger.lo:	$(TOP)\src\trigger.c $(HDR)
  1991   1992   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\trigger.c
  1992   1993   
  1993   1994   update.lo:	$(TOP)\src\update.c $(HDR)
  1994   1995   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\update.c
  1995   1996   
         1997  +upsert.lo:	$(TOP)\src\upsert.c $(HDR)
         1998  +	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\upsert.c
         1999  +
  1996   2000   utf.lo:	$(TOP)\src\utf.c $(HDR)
  1997   2001   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\utf.c
  1998   2002   
  1999   2003   util.lo:	$(TOP)\src\util.c $(HDR)
  2000   2004   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\util.c
  2001   2005   
  2002   2006   vacuum.lo:	$(TOP)\src\vacuum.c $(HDR)

Changes to main.mk.

    68     68            memdb.o memjournal.o \
    69     69            mutex.o mutex_noop.o mutex_unix.o mutex_w32.o \
    70     70            notify.o opcodes.o os.o os_unix.o os_win.o \
    71     71            pager.o pcache.o pcache1.o pragma.o prepare.o printf.o \
    72     72            random.o resolve.o rowset.o rtree.o \
    73     73            select.o sqlite3rbu.o status.o stmt.o \
    74     74            table.o threads.o tokenize.o treeview.o trigger.o \
    75         -         update.o userauth.o util.o vacuum.o \
           75  +         update.o upsert.o userauth.o util.o vacuum.o \
    76     76            vdbeapi.o vdbeaux.o vdbeblob.o vdbemem.o vdbesort.o \
    77     77   	 vdbetrace.o wal.o walker.o where.o wherecode.o whereexpr.o \
    78     78            utf.o vtab.o
    79     79   
    80     80   LIBOBJ += sqlite3session.o
    81     81   
    82     82   # All of the source code files.
................................................................................
   158    158     $(TOP)/src/tclsqlite.c \
   159    159     $(TOP)/src/threads.c \
   160    160     $(TOP)/src/tokenize.c \
   161    161     $(TOP)/src/treeview.c \
   162    162     $(TOP)/src/trigger.c \
   163    163     $(TOP)/src/utf.c \
   164    164     $(TOP)/src/update.c \
          165  +  $(TOP)/src/upsert.c \
   165    166     $(TOP)/src/util.c \
   166    167     $(TOP)/src/vacuum.c \
   167    168     $(TOP)/src/vdbe.c \
   168    169     $(TOP)/src/vdbe.h \
   169    170     $(TOP)/src/vdbeapi.c \
   170    171     $(TOP)/src/vdbeaux.c \
   171    172     $(TOP)/src/vdbeblob.c \

Changes to src/expr.c.

  1821   1821         testcase( pExpr->op==TK_AGG_FUNCTION );
  1822   1822         testcase( pExpr->op==TK_AGG_COLUMN );
  1823   1823         if( pWalker->eCode==3 && pExpr->iTable==pWalker->u.iCur ){
  1824   1824           return WRC_Continue;
  1825   1825         }
  1826   1826         /* Fall through */
  1827   1827       case TK_IF_NULL_ROW:
         1828  +    case TK_REGISTER:
         1829  +      testcase( pExpr->op==TK_REGISTER );
  1828   1830         testcase( pExpr->op==TK_IF_NULL_ROW );
  1829   1831         pWalker->eCode = 0;
  1830   1832         return WRC_Abort;
  1831   1833       case TK_VARIABLE:
  1832   1834         if( pWalker->eCode==5 ){
  1833   1835           /* Silently convert bound parameters that appear inside of CREATE
  1834   1836           ** statements into a NULL when parsing the CREATE statement text out
................................................................................
  1838   1840           /* A bound parameter in a CREATE statement that originates from
  1839   1841           ** sqlite3_prepare() causes an error */
  1840   1842           pWalker->eCode = 0;
  1841   1843           return WRC_Abort;
  1842   1844         }
  1843   1845         /* Fall through */
  1844   1846       default:
  1845         -      testcase( pExpr->op==TK_SELECT ); /* sqlite3SelectWalkFail will disallow */
  1846         -      testcase( pExpr->op==TK_EXISTS ); /* sqlite3SelectWalkFail will disallow */
         1847  +      testcase( pExpr->op==TK_SELECT ); /* sqlite3SelectWalkFail() disallows */
         1848  +      testcase( pExpr->op==TK_EXISTS ); /* sqlite3SelectWalkFail() disallows */
  1847   1849         return WRC_Continue;
  1848   1850     }
  1849   1851   }
  1850   1852   static int exprIsConst(Expr *p, int initFlag, int iCur){
  1851   1853     Walker w;
  1852   1854     w.eCode = initFlag;
  1853   1855     w.xExprCallback = exprNodeIsConstant;
................................................................................
  4895   4897         return 1;
  4896   4898       }
  4897   4899       return 2;
  4898   4900     }
  4899   4901     if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){
  4900   4902       if( pA->op==TK_FUNCTION ){
  4901   4903         if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2;
         4904  +    }else if( pA->op==TK_COLLATE ){
         4905  +      return sqlite3_stricmp(pA->u.zToken,pB->u.zToken)!=0 ? 2 : 0;
  4902   4906       }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
  4903         -      return pA->op==TK_COLLATE ? 1 : 2;
         4907  +      return 2;
  4904   4908       }
  4905   4909     }
  4906   4910     if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
  4907   4911     if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){
  4908   4912       if( combinedFlags & EP_xIsSelect ) return 2;
  4909   4913       if( sqlite3ExprCompare(pParse, pA->pLeft, pB->pLeft, iTab) ) return 2;
  4910   4914       if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2;

Changes to src/insert.c.

   484    484   **      D: cleanup
   485    485   */
   486    486   void sqlite3Insert(
   487    487     Parse *pParse,        /* Parser context */
   488    488     SrcList *pTabList,    /* Name of table into which we are inserting */
   489    489     Select *pSelect,      /* A SELECT statement to use as the data source */
   490    490     IdList *pColumn,      /* Column names corresponding to IDLIST. */
   491         -  int onError           /* How to handle constraint errors */
          491  +  int onError,          /* How to handle constraint errors */
          492  +  Upsert *pUpsert       /* ON CONFLICT clauses for upsert, or NULL */
   492    493   ){
   493    494     sqlite3 *db;          /* The main database structure */
   494    495     Table *pTab;          /* The table to insert into.  aka TABLE */
   495    496     int i, j;             /* Loop counters */
   496    497     Vdbe *v;              /* Generate code into this virtual machine */
   497    498     Index *pIdx;          /* For looping over indices of the table */
   498    499     int nColumn;          /* Number of columns in the data */
................................................................................
   799    800       }
   800    801       for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
   801    802         assert( pIdx );
   802    803         aRegIdx[i] = ++pParse->nMem;
   803    804         pParse->nMem += pIdx->nColumn;
   804    805       }
   805    806     }
          807  +#ifndef SQLITE_OMIT_UPSERT
          808  +  if( pUpsert ){
          809  +    pTabList->a[0].iCursor = iDataCur;
          810  +    pUpsert->pUpsertSrc = pTabList;
          811  +    pUpsert->regData = regData;
          812  +    if( pUpsert->pUpsertTarget ){
          813  +      sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
          814  +    }
          815  +  }
          816  +#endif
          817  +
   806    818   
   807    819     /* This is the top of the main insertion loop */
   808    820     if( useTempTable ){
   809    821       /* This block codes the top of loop only.  The complete loop is the
   810    822       ** following pseudocode (template 4):
   811    823       **
   812    824       **         rewind temp table, if empty goto D
................................................................................
  1001   1013         sqlite3MayAbort(pParse);
  1002   1014       }else
  1003   1015   #endif
  1004   1016       {
  1005   1017         int isReplace;    /* Set to true if constraints may cause a replace */
  1006   1018         int bUseSeek;     /* True to use OPFLAG_SEEKRESULT */
  1007   1019         sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
  1008         -          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0
         1020  +          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert
  1009   1021         );
  1010   1022         sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
  1011   1023   
  1012   1024         /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
  1013   1025         ** constraints or (b) there are no triggers and this table is not a
  1014   1026         ** parent table in a foreign key constraint. It is safe to set the
  1015   1027         ** flag in the second case as if any REPLACE constraint is hit, an
................................................................................
  1070   1082       sqlite3VdbeSetNumCols(v, 1);
  1071   1083       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
  1072   1084     }
  1073   1085   
  1074   1086   insert_cleanup:
  1075   1087     sqlite3SrcListDelete(db, pTabList);
  1076   1088     sqlite3ExprListDelete(db, pList);
         1089  +  sqlite3UpsertDelete(db, pUpsert);
  1077   1090     sqlite3SelectDelete(db, pSelect);
  1078   1091     sqlite3IdListDelete(db, pColumn);
  1079   1092     sqlite3DbFree(db, aRegIdx);
  1080   1093   }
  1081   1094   
  1082   1095   /* Make sure "isView" and other macros defined above are undefined. Otherwise
  1083   1096   ** they may interfere with compilation of other functions in this file
................................................................................
  1140   1153     }
  1141   1154     testcase( w.eCode==0 );
  1142   1155     testcase( w.eCode==CKCNSTRNT_COLUMN );
  1143   1156     testcase( w.eCode==CKCNSTRNT_ROWID );
  1144   1157     testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  1145   1158     return !w.eCode;
  1146   1159   }
         1160  +
         1161  +/*
         1162  +** An instance of the ConstraintAddr object remembers the byte-code addresses
         1163  +** for sections of the constraint checks that deal with uniqueness constraints
         1164  +** on the rowid and on the upsert constraint.
         1165  +**
         1166  +** This information is passed into checkReorderConstraintChecks() to insert
         1167  +** some OP_Goto operations so that the rowid and upsert constraints occur
         1168  +** in the correct order relative to other constraints.
         1169  +*/
         1170  +typedef struct ConstraintAddr ConstraintAddr;
         1171  +struct ConstraintAddr {
         1172  +  int ipkTop;          /* Subroutine for rowid constraint check */
         1173  +  int upsertTop;       /* Label for upsert constraint check subroutine */
         1174  +  int upsertTop2;      /* Copy of upsertTop not cleared by the call */
         1175  +  int upsertBtm;       /* upsert constraint returns to this label */
         1176  +  int ipkBtm;          /* Return opcode rowid constraint check */
         1177  +};
         1178  +
         1179  +/*
         1180  +** Generate any OP_Goto operations needed to cause constraints to be
         1181  +** run that haven't already been run.
         1182  +*/
         1183  +static void reorderConstraintChecks(Vdbe *v, ConstraintAddr *p){
         1184  +  if( p->upsertTop ){
         1185  +    testcase( sqlite3VdbeLabelHasBeenResolved(v, p->upsertTop) );
         1186  +    sqlite3VdbeGoto(v, p->upsertTop);
         1187  +    VdbeComment((v, "call upsert subroutine"));
         1188  +    sqlite3VdbeResolveLabel(v, p->upsertBtm);
         1189  +    p->upsertTop = 0;
         1190  +  }
         1191  +  if( p->ipkTop ){
         1192  +    sqlite3VdbeGoto(v, p->ipkTop);
         1193  +    VdbeComment((v, "call rowid unique-check subroutine"));
         1194  +    sqlite3VdbeJumpHere(v, p->ipkBtm);
         1195  +    p->ipkTop = 0;
         1196  +  }
         1197  +}
  1147   1198   
  1148   1199   /*
  1149   1200   ** Generate code to do constraint checks prior to an INSERT or an UPDATE
  1150   1201   ** on table pTab.
  1151   1202   **
  1152   1203   ** The regNewData parameter is the first register in a range that contains
  1153   1204   ** the data to be inserted or the data after the update.  There will be
................................................................................
  1236   1287     int iIdxCur,         /* First index cursor */
  1237   1288     int regNewData,      /* First register in a range holding values to insert */
  1238   1289     int regOldData,      /* Previous content.  0 for INSERTs */
  1239   1290     u8 pkChng,           /* Non-zero if the rowid or PRIMARY KEY changed */
  1240   1291     u8 overrideError,    /* Override onError to this if not OE_Default */
  1241   1292     int ignoreDest,      /* Jump to this label on an OE_Ignore resolution */
  1242   1293     int *pbMayReplace,   /* OUT: Set to true if constraint may cause a replace */
  1243         -  int *aiChng          /* column i is unchanged if aiChng[i]<0 */
         1294  +  int *aiChng,         /* column i is unchanged if aiChng[i]<0 */
         1295  +  Upsert *pUpsert      /* ON CONFLICT clauses, if any.  NULL otherwise */
  1244   1296   ){
  1245   1297     Vdbe *v;             /* VDBE under constrution */
  1246   1298     Index *pIdx;         /* Pointer to one of the indices */
  1247   1299     Index *pPk = 0;      /* The PRIMARY KEY index */
  1248   1300     sqlite3 *db;         /* Database connection */
  1249   1301     int i;               /* loop counter */
  1250   1302     int ix;              /* Index loop counter */
  1251   1303     int nCol;            /* Number of columns */
  1252   1304     int onError;         /* Conflict resolution strategy */
  1253   1305     int addr1;           /* Address of jump instruction */
  1254   1306     int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  1255   1307     int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
  1256         -  int ipkTop = 0;      /* Top of the rowid change constraint check */
  1257         -  int ipkBottom = 0;   /* Bottom of the rowid change constraint check */
         1308  +  ConstraintAddr sAddr;/* Address information for constraint reordering */
         1309  +  Index *pUpIdx = 0;   /* Index to which to apply the upsert */
  1258   1310     u8 isUpdate;         /* True if this is an UPDATE operation */
  1259   1311     u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
         1312  +  int upsertBypass = 0;  /* Address of Goto to bypass upsert subroutine */
  1260   1313   
  1261   1314     isUpdate = regOldData!=0;
  1262   1315     db = pParse->db;
  1263   1316     v = sqlite3GetVdbe(pParse);
  1264   1317     assert( v!=0 );
  1265   1318     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1266   1319     nCol = pTab->nCol;
         1320  +  memset(&sAddr, 0, sizeof(sAddr));
  1267   1321     
  1268   1322     /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
  1269   1323     ** normal rowid tables.  nPkField is the number of key fields in the 
  1270   1324     ** pPk index or 1 for a rowid table.  In other words, nPkField is the
  1271   1325     ** number of fields in the true primary key of the table. */
  1272   1326     if( HasRowid(pTab) ){
  1273   1327       pPk = 0;
................................................................................
  1358   1412                                 P5_ConstraintCheck);
  1359   1413         }
  1360   1414         sqlite3VdbeResolveLabel(v, allOk);
  1361   1415       }
  1362   1416       pParse->iSelfTab = 0;
  1363   1417     }
  1364   1418   #endif /* !defined(SQLITE_OMIT_CHECK) */
         1419  +
         1420  +  /* UNIQUE and PRIMARY KEY constraints should be handled in the following
         1421  +  ** order:
         1422  +  **
         1423  +  **   (1)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
         1424  +  **   (2)  OE_Update
         1425  +  **   (3)  OE_Replace
         1426  +  **
         1427  +  ** OE_Fail and OE_Ignore must happen before any changes are made.
         1428  +  ** OE_Update guarantees that only a single row will change, so it
         1429  +  ** must happen before OE_Replace.  Technically, OE_Abort and OE_Rollback
         1430  +  ** could happen in any order, but they are grouped up front for
         1431  +  ** convenience.
         1432  +  **
         1433  +  ** Constraint checking code is generated in this order:
         1434  +  **   (A)  The rowid constraint
         1435  +  **   (B)  Unique index constraints that do not have OE_Replace as their
         1436  +  **        default conflict resolution strategy
         1437  +  **   (C)  Unique index that do use OE_Replace by default.
         1438  +  **
         1439  +  ** The ordering of (2) and (3) is accomplished by making sure the linked
         1440  +  ** list of indexes attached to a table puts all OE_Replace indexes last
         1441  +  ** in the list.  See sqlite3CreateIndex() for where that happens.
         1442  +  */
         1443  +
         1444  +  if( pUpsert ){
         1445  +    if( pUpsert->pUpsertTarget==0 ){
         1446  +      /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
         1447  +      ** Make all unique constraint resolution be OE_Ignore */
         1448  +      assert( pUpsert->pUpsertSet==0 );
         1449  +      overrideError = OE_Ignore;
         1450  +      pUpsert = 0;
         1451  +    }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
         1452  +      /* If the constraint-target is on some column other than
         1453  +      ** then ROWID, then we might need to move the UPSERT around
         1454  +      ** so that it occurs in the correct order. */
         1455  +      sAddr.upsertTop = sAddr.upsertTop2 = sqlite3VdbeMakeLabel(v);
         1456  +      sAddr.upsertBtm = sqlite3VdbeMakeLabel(v);
         1457  +    }
         1458  +  }
  1365   1459   
  1366   1460     /* If rowid is changing, make sure the new rowid does not previously
  1367   1461     ** exist in the table.
  1368   1462     */
  1369   1463     if( pkChng && pPk==0 ){
  1370   1464       int addrRowidOk = sqlite3VdbeMakeLabel(v);
  1371   1465   
................................................................................
  1381   1475         /* pkChng!=0 does not mean that the rowid has changed, only that
  1382   1476         ** it might have changed.  Skip the conflict logic below if the rowid
  1383   1477         ** is unchanged. */
  1384   1478         sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
  1385   1479         sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
  1386   1480         VdbeCoverage(v);
  1387   1481       }
         1482  +
         1483  +    /* figure out whether or not upsert applies in this case */
         1484  +    if( pUpsert && pUpsert->pUpsertIdx==0 ){
         1485  +      if( pUpsert->pUpsertSet==0 ){
         1486  +        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
         1487  +      }else{
         1488  +        onError = OE_Update;  /* DO UPDATE */
         1489  +      }
         1490  +    }
  1388   1491   
  1389   1492       /* If the response to a rowid conflict is REPLACE but the response
  1390   1493       ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
  1391   1494       ** to defer the running of the rowid conflict checking until after
  1392   1495       ** the UNIQUE constraints have run.
  1393   1496       */
  1394         -    if( onError==OE_Replace && overrideError!=OE_Replace ){
  1395         -      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  1396         -        if( pIdx->onError==OE_Ignore || pIdx->onError==OE_Fail ){
  1397         -          ipkTop = sqlite3VdbeAddOp0(v, OP_Goto);
  1398         -          break;
  1399         -        }
  1400         -      }
         1497  +    assert( OE_Update>OE_Replace );
         1498  +    assert( OE_Ignore<OE_Replace );
         1499  +    assert( OE_Fail<OE_Replace );
         1500  +    assert( OE_Abort<OE_Replace );
         1501  +    assert( OE_Rollback<OE_Replace );
         1502  +    if( onError>=OE_Replace
         1503  +     && onError!=overrideError
         1504  +     && pTab->pIndex
         1505  +    ){
         1506  +      sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
  1401   1507       }
  1402   1508   
  1403   1509       /* Check to see if the new rowid already exists in the table.  Skip
  1404   1510       ** the following conflict logic if it does not. */
         1511  +    VdbeNoopComment((v, "uniqueness check for ROWID"));
  1405   1512       sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
  1406   1513       VdbeCoverage(v);
  1407   1514   
  1408         -    /* Generate code that deals with a rowid collision */
  1409   1515       switch( onError ){
  1410   1516         default: {
  1411   1517           onError = OE_Abort;
  1412   1518           /* Fall thru into the next case */
  1413   1519         }
  1414   1520         case OE_Rollback:
  1415   1521         case OE_Abort:
  1416   1522         case OE_Fail: {
         1523  +        testcase( onError==OE_Rollback );
         1524  +        testcase( onError==OE_Abort );
         1525  +        testcase( onError==OE_Fail );
  1417   1526           sqlite3RowidConstraint(pParse, onError, pTab);
  1418   1527           break;
  1419   1528         }
  1420   1529         case OE_Replace: {
  1421   1530           /* If there are DELETE triggers on this table and the
  1422   1531           ** recursive-triggers flag is set, call GenerateRowDelete() to
  1423   1532           ** remove the conflicting row from the table. This will fire
................................................................................
  1462   1571               sqlite3MultiWrite(pParse);
  1463   1572               sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,-1);
  1464   1573             }
  1465   1574           }
  1466   1575           seenReplace = 1;
  1467   1576           break;
  1468   1577         }
         1578  +#ifndef SQLITE_OMIT_UPSERT
         1579  +      case OE_Update: {
         1580  +        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur);
         1581  +        /* Fall through */
         1582  +      }
         1583  +#endif
  1469   1584         case OE_Ignore: {
  1470         -        /*assert( seenReplace==0 );*/
         1585  +        testcase( onError==OE_Ignore );
  1471   1586           sqlite3VdbeGoto(v, ignoreDest);
  1472   1587           break;
  1473   1588         }
  1474   1589       }
  1475   1590       sqlite3VdbeResolveLabel(v, addrRowidOk);
  1476         -    if( ipkTop ){
  1477         -      ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
  1478         -      sqlite3VdbeJumpHere(v, ipkTop);
         1591  +    if( sAddr.ipkTop ){
         1592  +      sAddr.ipkBtm = sqlite3VdbeAddOp0(v, OP_Goto);
         1593  +      sqlite3VdbeJumpHere(v, sAddr.ipkTop-1);
  1479   1594       }
  1480   1595     }
  1481   1596   
  1482   1597     /* Test all UNIQUE constraints by creating entries for each UNIQUE
  1483   1598     ** index and making sure that duplicate entries do not already exist.
  1484   1599     ** Compute the revised record entries for indices as we go.
  1485   1600     **
................................................................................
  1489   1604     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1490   1605       int regIdx;          /* Range of registers hold conent for pIdx */
  1491   1606       int regR;            /* Range of registers holding conflicting PK */
  1492   1607       int iThisCur;        /* Cursor for this UNIQUE index */
  1493   1608       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
  1494   1609   
  1495   1610       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
         1611  +    if( pUpIdx==pIdx ){
         1612  +      addrUniqueOk = sAddr.upsertBtm;
         1613  +      upsertBypass = sqlite3VdbeGoto(v, 0);
         1614  +      VdbeComment((v, "Skip upsert subroutine"));
         1615  +      sqlite3VdbeResolveLabel(v, sAddr.upsertTop2);
         1616  +    }else{
         1617  +      addrUniqueOk = sqlite3VdbeMakeLabel(v);
         1618  +    }
         1619  +    VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
  1496   1620       if( bAffinityDone==0 ){
  1497   1621         sqlite3TableAffinity(v, pTab, regNewData+1);
  1498   1622         bAffinityDone = 1;
  1499   1623       }
  1500   1624       iThisCur = iIdxCur+ix;
  1501         -    addrUniqueOk = sqlite3VdbeMakeLabel(v);
         1625  +
  1502   1626   
  1503   1627       /* Skip partial indices for which the WHERE clause is not true */
  1504   1628       if( pIdx->pPartIdxWhere ){
  1505   1629         sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
  1506   1630         pParse->iSelfTab = -(regNewData+1);
  1507   1631         sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
  1508   1632                               SQLITE_JUMPIFNULL);
................................................................................
  1553   1677         continue;  /* pIdx is not a UNIQUE index */
  1554   1678       }
  1555   1679       if( overrideError!=OE_Default ){
  1556   1680         onError = overrideError;
  1557   1681       }else if( onError==OE_Default ){
  1558   1682         onError = OE_Abort;
  1559   1683       }
         1684  +
         1685  +    /* Figure out if the upsert clause applies to this index */
         1686  +    if( pUpIdx==pIdx ){
         1687  +      if( pUpsert->pUpsertSet==0 ){
         1688  +        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
         1689  +      }else{
         1690  +        onError = OE_Update;  /* DO UPDATE */
         1691  +      }
         1692  +    }
         1693  +
         1694  +    /* Invoke subroutines to handle IPK replace and upsert prior to running
         1695  +    ** the first REPLACE constraint check. */
         1696  +    if( onError==OE_Replace ){
         1697  +      testcase( sAddr.ipkTop );
         1698  +      testcase( sAddr.upsertTop
         1699  +             && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
         1700  +      reorderConstraintChecks(v, &sAddr);
         1701  +    }
  1560   1702   
  1561   1703       /* Collision detection may be omitted if all of the following are true:
  1562   1704       **   (1) The conflict resolution algorithm is REPLACE
  1563   1705       **   (2) The table is a WITHOUT ROWID table
  1564   1706       **   (3) There are no secondary indexes on the table
  1565   1707       **   (4) No delete triggers need to be fired if there is a conflict
  1566   1708       **   (5) No FK constraint counters need to be updated if a conflict occurs.
................................................................................
  1636   1778             }
  1637   1779           }
  1638   1780         }
  1639   1781       }
  1640   1782   
  1641   1783       /* Generate code that executes if the new index entry is not unique */
  1642   1784       assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
  1643         -        || onError==OE_Ignore || onError==OE_Replace );
         1785  +        || onError==OE_Ignore || onError==OE_Replace || onError==OE_Update );
  1644   1786       switch( onError ){
  1645   1787         case OE_Rollback:
  1646   1788         case OE_Abort:
  1647   1789         case OE_Fail: {
         1790  +        testcase( onError==OE_Rollback );
         1791  +        testcase( onError==OE_Abort );
         1792  +        testcase( onError==OE_Fail );
  1648   1793           sqlite3UniqueConstraint(pParse, onError, pIdx);
  1649   1794           break;
  1650   1795         }
         1796  +#ifndef SQLITE_OMIT_UPSERT
         1797  +      case OE_Update: {
         1798  +        sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix);
         1799  +        /* Fall through */
         1800  +      }
         1801  +#endif
  1651   1802         case OE_Ignore: {
         1803  +        testcase( onError==OE_Ignore );
  1652   1804           sqlite3VdbeGoto(v, ignoreDest);
  1653   1805           break;
  1654   1806         }
  1655   1807         default: {
  1656   1808           Trigger *pTrigger = 0;
  1657   1809           assert( onError==OE_Replace );
  1658   1810           sqlite3MultiWrite(pParse);
................................................................................
  1662   1814           sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
  1663   1815               regR, nPkField, 0, OE_Replace,
  1664   1816               (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
  1665   1817           seenReplace = 1;
  1666   1818           break;
  1667   1819         }
  1668   1820       }
         1821  +    if( pUpIdx==pIdx ){
         1822  +      sqlite3VdbeJumpHere(v, upsertBypass);
         1823  +    }else{
  1669   1824       sqlite3VdbeResolveLabel(v, addrUniqueOk);
         1825  +    }
  1670   1826       sqlite3ExprCachePop(pParse);
  1671   1827       if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
         1828  +
  1672   1829     }
  1673         -  if( ipkTop ){
  1674         -    sqlite3VdbeGoto(v, ipkTop+1);
  1675         -    sqlite3VdbeJumpHere(v, ipkBottom);
  1676         -  }
         1830  +  testcase( sAddr.ipkTop!=0 );
         1831  +  testcase( sAddr.upsertTop
         1832  +         && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
         1833  +  reorderConstraintChecks(v, &sAddr);
  1677   1834     
  1678   1835     *pbMayReplace = seenReplace;
  1679   1836     VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
  1680   1837   }
  1681   1838   
  1682   1839   #ifdef SQLITE_ENABLE_NULL_TRIM
  1683   1840   /*

Changes to src/parse.y.

   201    201   
   202    202   // The following directive causes tokens ABORT, AFTER, ASC, etc. to
   203    203   // fallback to ID if they will not parse as their original value.
   204    204   // This obviates the need for the "id" nonterminal.
   205    205   //
   206    206   %fallback ID
   207    207     ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
   208         -  CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
          208  +  CONFLICT DATABASE DEFERRED DESC DETACH DO
          209  +  EACH END EXCLUSIVE EXPLAIN FAIL FOR
   209    210     IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
   210    211     QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
   211    212     ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
   212    213   %ifdef SQLITE_OMIT_COMPOUND_SELECT
   213    214     EXCEPT INTERSECT UNION
   214    215   %endif SQLITE_OMIT_COMPOUND_SELECT
   215    216     REINDEX RENAME CTIME_KW IF
................................................................................
   235    236   %right ESCAPE.
   236    237   %left BITAND BITOR LSHIFT RSHIFT.
   237    238   %left PLUS MINUS.
   238    239   %left STAR SLASH REM.
   239    240   %left CONCAT.
   240    241   %left COLLATE.
   241    242   %right BITNOT.
          243  +%nonassoc ON.
   242    244   
   243    245   // An IDENTIFIER can be a generic identifier, or one of several
   244    246   // keywords.  Any non-standard keyword can also be an identifier.
   245    247   //
   246    248   %token_class id  ID|INDEXED.
   247    249   
   248    250   
................................................................................
   693    695   joinop(X) ::= JOIN_KW(A) JOIN.
   694    696                     {X = sqlite3JoinType(pParse,&A,0,0);  /*X-overwrites-A*/}
   695    697   joinop(X) ::= JOIN_KW(A) nm(B) JOIN.
   696    698                     {X = sqlite3JoinType(pParse,&A,&B,0); /*X-overwrites-A*/}
   697    699   joinop(X) ::= JOIN_KW(A) nm(B) nm(C) JOIN.
   698    700                     {X = sqlite3JoinType(pParse,&A,&B,&C);/*X-overwrites-A*/}
   699    701   
          702  +// There is a parsing abiguity in an upsert statement that uses a
          703  +// SELECT on the RHS of a the INSERT:
          704  +//
          705  +//      INSERT INTO tab SELECT * FROM aaa JOIN bbb ON CONFLICT ...
          706  +//                                        here ----^^
          707  +//
          708  +// When the ON token is encountered, the parser does not know if it is
          709  +// the beginning of an ON CONFLICT clause, or the beginning of an ON
          710  +// clause associated with the JOIN.  The conflict is resolved in favor
          711  +// of the JOIN.  If an ON CONFLICT clause is intended, insert a dummy
          712  +// WHERE clause in between, like this:
          713  +//
          714  +//      INSERT INTO tab SELECT * FROM aaa JOIN bbb WHERE true ON CONFLICT ...
          715  +//
          716  +// The [AND] and [OR] precedence marks in the rules for on_opt cause the
          717  +// ON in this context to always be interpreted as belonging to the JOIN.
          718  +//
   700    719   %type on_opt {Expr*}
   701    720   %destructor on_opt {sqlite3ExprDelete(pParse->db, $$);}
   702    721   on_opt(N) ::= ON expr(E).   {N = E;}
   703         -on_opt(N) ::= .             {N = 0;}
          722  +on_opt(N) ::= .     [OR]   {N = 0;}
   704    723   
   705    724   // Note that this block abuses the Token type just a little. If there is
   706    725   // no "INDEXED BY" clause, the returned token is empty (z==0 && n==0). If
   707    726   // there is an INDEXED BY clause, then the token is populated as per normal,
   708    727   // with z pointing to the token data and n containing the number of bytes
   709    728   // in the token.
   710    729   //
................................................................................
   803    822   ////////////////////////// The UPDATE command ////////////////////////////////
   804    823   //
   805    824   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   806    825   cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   807    826           where_opt(W) orderby_opt(O) limit_opt(L).  {
   808    827     sqlite3SrcListIndexedBy(pParse, X, &I);
   809    828     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   810         -  sqlite3Update(pParse,X,Y,W,R,O,L);
          829  +  sqlite3Update(pParse,X,Y,W,R,O,L,0);
   811    830   }
   812    831   %endif
   813    832   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   814    833   cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   815    834           where_opt(W).  {
   816    835     sqlite3SrcListIndexedBy(pParse, X, &I);
   817    836     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   818         -  sqlite3Update(pParse,X,Y,W,R,0,0);
          837  +  sqlite3Update(pParse,X,Y,W,R,0,0,0);
   819    838   }
   820    839   %endif
   821    840   
   822    841   %type setlist {ExprList*}
   823    842   %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
   824    843   
   825    844   setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). {
................................................................................
   835    854   }
   836    855   setlist(A) ::= LP idlist(X) RP EQ expr(Y). {
   837    856     A = sqlite3ExprListAppendVector(pParse, 0, X, Y);
   838    857   }
   839    858   
   840    859   ////////////////////////// The INSERT command /////////////////////////////////
   841    860   //
   842         -cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). {
   843         -  sqlite3Insert(pParse, X, S, F, R);
          861  +cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S)
          862  +        upsert(U). {
          863  +  sqlite3Insert(pParse, X, S, F, R, U);
          864  +}
          865  +cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) AS nm(A) select(S)
          866  +        upsert(U). {
          867  +  /* X could only be NULL following a OOM, but an OOM would have been detected
          868  +  ** and stopped the parse long before this rule reduces. */
          869  +  assert( X!=0 );
          870  +  X->a[0].zAlias = sqlite3NameFromToken(pParse->db, &A);
          871  +  sqlite3Insert(pParse, X, S, F, R, U);
   844    872   }
   845    873   cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
   846    874   {
   847         -  sqlite3Insert(pParse, X, 0, F, R);
          875  +  sqlite3Insert(pParse, X, 0, F, R, 0);
   848    876   }
   849    877   
          878  +%type upsert {Upsert*}
          879  +
          880  +// Because upsert only occurs at the tip end of the INSERT rule for cmd,
          881  +// there is never a case where the value of the upsert pointer will not
          882  +// be destroyed by the cmd action.  So comment-out the destructor to
          883  +// avoid unreachable code.
          884  +//%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
          885  +upsert(A) ::= . { A = 0; }
          886  +upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW)
          887  +              DO UPDATE SET setlist(Z) where_opt(W).
          888  +              { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W);}
          889  +upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING.
          890  +              { A = sqlite3UpsertNew(pParse->db,T,TW,0,0); }
          891  +upsert(A) ::= ON CONFLICT DO NOTHING.
          892  +              { A = sqlite3UpsertNew(pParse->db,0,0,0,0); }
          893  +
   850    894   %type insert_cmd {int}
   851    895   insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
   852    896   insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
   853    897   
   854    898   %type idlist_opt {IdList*}
   855    899   %destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}
   856    900   %type idlist {IdList*}
................................................................................
  1388   1432   // UPDATE 
  1389   1433   trigger_cmd(A) ::=
  1390   1434      UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E).  
  1391   1435      {A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R, B.z, E);}
  1392   1436   
  1393   1437   // INSERT
  1394   1438   trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO
  1395         -                      trnm(X) idlist_opt(F) select(S) scanpt(Z).
  1396         -   {A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,B,Z);/*A-overwrites-R*/}
  1397         -
         1439  +                      trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). {
         1440  +   A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,U,B,Z);/*A-overwrites-R*/
         1441  +}
  1398   1442   // DELETE
  1399   1443   trigger_cmd(A) ::= DELETE(B) FROM trnm(X) tridxby where_opt(Y) scanpt(E).
  1400   1444      {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y, B.z, E);}
  1401   1445   
  1402   1446   // SELECT
  1403   1447   trigger_cmd(A) ::= scanpt(B) select(X) scanpt(E).
  1404   1448      {A = sqlite3TriggerSelectStep(pParse->db, X, B, E); /*A-overwrites-X*/}

Changes to src/resolve.c.

   187    187     int cntTab = 0;                   /* Number of matching table names */
   188    188     int nSubquery = 0;                /* How many levels of subquery */
   189    189     sqlite3 *db = pParse->db;         /* The database connection */
   190    190     struct SrcList_item *pItem;       /* Use for looping over pSrcList items */
   191    191     struct SrcList_item *pMatch = 0;  /* The matching pSrcList item */
   192    192     NameContext *pTopNC = pNC;        /* First namecontext in the list */
   193    193     Schema *pSchema = 0;              /* Schema of the expression */
   194         -  int isTrigger = 0;                /* True if resolved to a trigger column */
          194  +  int eNewExprOp = TK_COLUMN;       /* New value for pExpr->op on success */
   195    195     Table *pTab = 0;                  /* Table hold the row */
   196    196     Column *pCol;                     /* A column of pTab */
   197    197   
   198    198     assert( pNC );     /* the name context cannot be NULL. */
   199    199     assert( zCol );    /* The Z in X.Y.Z cannot be NULL */
   200    200     assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) );
   201    201   
................................................................................
   292    292           if( (pMatch->fg.jointype & JT_LEFT)!=0 ){
   293    293             ExprSetProperty(pExpr, EP_CanBeNull);
   294    294           }
   295    295           pSchema = pExpr->pTab->pSchema;
   296    296         }
   297    297       } /* if( pSrcList ) */
   298    298   
   299         -#ifndef SQLITE_OMIT_TRIGGER
          299  +#if !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT)
   300    300       /* If we have not already resolved the name, then maybe 
   301         -    ** it is a new.* or old.* trigger argument reference
          301  +    ** it is a new.* or old.* trigger argument reference.  Or
          302  +    ** maybe it is an excluded.* from an upsert.
   302    303       */
   303         -    if( zDb==0 && zTab!=0 && cntTab==0 && pParse->pTriggerTab!=0 ){
          304  +    if( zDb==0 && zTab!=0 && cntTab==0 ){
          305  +      pTab = 0;
          306  +#ifndef SQLITE_OMIT_TRIGGER
          307  +      if( pParse->pTriggerTab!=0 ){
   304    308         int op = pParse->eTriggerOp;
   305    309         assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT );
   306    310         if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){
   307    311           pExpr->iTable = 1;
   308    312           pTab = pParse->pTriggerTab;
   309    313         }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){
   310    314           pExpr->iTable = 0;
   311    315           pTab = pParse->pTriggerTab;
   312         -      }else{
   313         -        pTab = 0;
   314    316         }
          317  +      }
          318  +#endif /* SQLITE_OMIT_TRIGGER */
          319  +#ifndef SQLITE_OMIT_UPSERT
          320  +      if( (pNC->ncFlags & NC_UUpsert)!=0 ){
          321  +        Upsert *pUpsert = pNC->uNC.pUpsert;
          322  +        if( pUpsert && sqlite3StrICmp("excluded",zTab)==0 ){
          323  +          pTab = pUpsert->pUpsertSrc->a[0].pTab;
          324  +          pExpr->iTable = 2;
          325  +        }
          326  +      }
          327  +#endif /* SQLITE_OMIT_UPSERT */
   315    328   
   316    329         if( pTab ){ 
   317    330           int iCol;
   318    331           pSchema = pTab->pSchema;
   319    332           cntTab++;
   320    333           for(iCol=0, pCol=pTab->aCol; iCol<pTab->nCol; iCol++, pCol++){
   321    334             if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
................................................................................
   327    340           }
   328    341           if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && VisibleRowid(pTab) ){
   329    342             /* IMP: R-51414-32910 */
   330    343             iCol = -1;
   331    344           }
   332    345           if( iCol<pTab->nCol ){
   333    346             cnt++;
          347  +#ifndef SQLITE_OMIT_UPSERT
          348  +          if( pExpr->iTable==2 ){
          349  +            testcase( iCol==(-1) );
          350  +            pExpr->iTable = pNC->uNC.pUpsert->regData + iCol;
          351  +            eNewExprOp = TK_REGISTER;
          352  +          }else
          353  +#endif /* SQLITE_OMIT_UPSERT */
          354  +          {
          355  +#ifndef SQLITE_OMIT_TRIGGER
   334    356             if( iCol<0 ){
   335    357               pExpr->affinity = SQLITE_AFF_INTEGER;
   336    358             }else if( pExpr->iTable==0 ){
   337    359               testcase( iCol==31 );
   338    360               testcase( iCol==32 );
   339    361               pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
   340    362             }else{
   341    363               testcase( iCol==31 );
   342    364               testcase( iCol==32 );
   343    365               pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol));
   344    366             }
   345         -          pExpr->iColumn = (i16)iCol;
   346    367             pExpr->pTab = pTab;
   347         -          isTrigger = 1;
          368  +            pExpr->iColumn = (i16)iCol;
          369  +            eNewExprOp = TK_TRIGGER;
          370  +#endif /* SQLITE_OMIT_TRIGGER */
   348    371           }
   349    372         }
   350    373       }
   351         -#endif /* !defined(SQLITE_OMIT_TRIGGER) */
          374  +    }
          375  +#endif /* !defined(SQLITE_OMIT_TRIGGER) || !defined(SQLITE_OMIT_UPSERT) */
   352    376   
   353    377       /*
   354    378       ** Perhaps the name is a reference to the ROWID
   355    379       */
   356    380       if( cnt==0
   357    381        && cntTab==1
   358    382        && pMatch
................................................................................
   481    505   
   482    506     /* Clean up and return
   483    507     */
   484    508     sqlite3ExprDelete(db, pExpr->pLeft);
   485    509     pExpr->pLeft = 0;
   486    510     sqlite3ExprDelete(db, pExpr->pRight);
   487    511     pExpr->pRight = 0;
   488         -  pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN);
          512  +  pExpr->op = eNewExprOp;
   489    513     ExprSetProperty(pExpr, EP_Leaf);
   490    514   lookupname_end:
   491    515     if( cnt==1 ){
   492    516       assert( pNC!=0 );
   493    517       if( !ExprHasProperty(pExpr, EP_Alias) ){
   494    518         sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList);
   495    519       }
................................................................................
  1297   1321       ** other expressions in the SELECT statement. This is so that
  1298   1322       ** expressions in the WHERE clause (etc.) can refer to expressions by
  1299   1323       ** aliases in the result set.
  1300   1324       **
  1301   1325       ** Minor point: If this is the case, then the expression will be
  1302   1326       ** re-evaluated for each reference to it.
  1303   1327       */
  1304         -    assert( (sNC.ncFlags & (NC_UAggInfo))==0 );
         1328  +    assert( (sNC.ncFlags & (NC_UAggInfo|NC_UUpsert))==0 );
  1305   1329       sNC.uNC.pEList = p->pEList;
  1306   1330       sNC.ncFlags |= NC_UEList;
  1307   1331       if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort;
  1308   1332       if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort;
  1309   1333   
  1310   1334       /* Resolve names in table-valued-function arguments */
  1311   1335       for(i=0; i<p->pSrc->nSrc; i++){
................................................................................
  1532   1556   ** Any errors cause an error message to be set in pParse.
  1533   1557   */
  1534   1558   void sqlite3ResolveSelfReference(
  1535   1559     Parse *pParse,      /* Parsing context */
  1536   1560     Table *pTab,        /* The table being referenced */
  1537   1561     int type,           /* NC_IsCheck or NC_PartIdx or NC_IdxExpr */
  1538   1562     Expr *pExpr,        /* Expression to resolve.  May be NULL. */
  1539         -  ExprList *pList     /* Expression list to resolve.  May be NUL. */
         1563  +  ExprList *pList     /* Expression list to resolve.  May be NULL. */
  1540   1564   ){
  1541   1565     SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
  1542   1566     NameContext sNC;                /* Name context for pParse->pNewTable */
  1543   1567   
  1544   1568     assert( type==NC_IsCheck || type==NC_PartIdx || type==NC_IdxExpr );
  1545   1569     memset(&sNC, 0, sizeof(sNC));
  1546   1570     memset(&sSrc, 0, sizeof(sSrc));

Changes to src/sqliteInt.h.

  1091   1091   typedef struct TableLock TableLock;
  1092   1092   typedef struct Token Token;
  1093   1093   typedef struct TreeView TreeView;
  1094   1094   typedef struct Trigger Trigger;
  1095   1095   typedef struct TriggerPrg TriggerPrg;
  1096   1096   typedef struct TriggerStep TriggerStep;
  1097   1097   typedef struct UnpackedRecord UnpackedRecord;
         1098  +typedef struct Upsert Upsert;
  1098   1099   typedef struct VTable VTable;
  1099   1100   typedef struct VtabCtx VtabCtx;
  1100   1101   typedef struct Walker Walker;
  1101   1102   typedef struct WhereInfo WhereInfo;
  1102   1103   typedef struct With With;
  1103   1104   
  1104   1105   /* A VList object records a mapping between parameters/variables/wildcards
................................................................................
  2042   2043   */
  2043   2044   #define OE_None     0   /* There is no constraint to check */
  2044   2045   #define OE_Rollback 1   /* Fail the operation and rollback the transaction */
  2045   2046   #define OE_Abort    2   /* Back out changes but do no rollback transaction */
  2046   2047   #define OE_Fail     3   /* Stop the operation but leave all prior changes */
  2047   2048   #define OE_Ignore   4   /* Ignore the error. Do not do the INSERT or UPDATE */
  2048   2049   #define OE_Replace  5   /* Delete existing record, then do INSERT or UPDATE */
  2049         -
  2050         -#define OE_Restrict 6   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
  2051         -#define OE_SetNull  7   /* Set the foreign key value to NULL */
  2052         -#define OE_SetDflt  8   /* Set the foreign key value to its default */
  2053         -#define OE_Cascade  9   /* Cascade the changes */
  2054         -
  2055         -#define OE_Default  10  /* Do whatever the default action is */
         2050  +#define OE_Update   6   /* Process as a DO UPDATE in an upsert */
         2051  +#define OE_Restrict 7   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
         2052  +#define OE_SetNull  8   /* Set the foreign key value to NULL */
         2053  +#define OE_SetDflt  9   /* Set the foreign key value to its default */
         2054  +#define OE_Cascade  10  /* Cascade the changes */
         2055  +#define OE_Default  11  /* Do whatever the default action is */
  2056   2056   
  2057   2057   
  2058   2058   /*
  2059   2059   ** An instance of the following structure is passed as the first
  2060   2060   ** argument to sqlite3VdbeKeyCompare and is used to control the
  2061   2061   ** comparison of the two index keys.
  2062   2062   **
................................................................................
  2681   2681   */
  2682   2682   struct NameContext {
  2683   2683     Parse *pParse;       /* The parser */
  2684   2684     SrcList *pSrcList;   /* One or more tables used to resolve names */
  2685   2685     union {
  2686   2686       ExprList *pEList;    /* Optional list of result-set columns */
  2687   2687       AggInfo *pAggInfo;   /* Information about aggregates at this level */
         2688  +    Upsert *pUpsert;     /* ON CONFLICT clause information from an upsert */
  2688   2689     } uNC;
  2689   2690     NameContext *pNext;  /* Next outer name context.  NULL for outermost */
  2690   2691     int nRef;            /* Number of names resolved by this context */
  2691   2692     int nErr;            /* Number of errors encountered while resolving names */
  2692   2693     u16 ncFlags;         /* Zero or more NC_* flags defined below */
  2693   2694   };
  2694   2695   
................................................................................
  2705   2706   #define NC_IsCheck   0x0004  /* True if resolving names in a CHECK constraint */
  2706   2707   #define NC_InAggFunc 0x0008  /* True if analyzing arguments to an agg func */
  2707   2708   #define NC_HasAgg    0x0010  /* One or more aggregate functions seen */
  2708   2709   #define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
  2709   2710   #define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
  2710   2711   #define NC_UEList    0x0080  /* True if uNC.pEList is used */
  2711   2712   #define NC_UAggInfo  0x0100  /* True if uNC.pAggInfo is used */
         2713  +#define NC_UUpsert   0x0200  /* True if uNC.pUpsert is used */
  2712   2714   #define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
  2713   2715   #define NC_Complex   0x2000  /* True if a function or subquery seen */
         2716  +
         2717  +/*
         2718  +** An instance of the following object describes a single ON CONFLICT
         2719  +** clause in an upsert.
         2720  +**
         2721  +** The pUpsertTarget field is only set if the ON CONFLICT clause includes
         2722  +** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the
         2723  +** conflict-target clause.)  The pUpsertTargetWhere is the optional
         2724  +** WHERE clause used to identify partial unique indexes.
         2725  +**
         2726  +** pUpsertSet is the list of column=expr terms of the UPDATE statement. 
         2727  +** The pUpsertSet field is NULL for a ON CONFLICT DO NOTHING.  The
         2728  +** pUpsertWhere is the WHERE clause for the UPDATE and is NULL if the
         2729  +** WHERE clause is omitted.
         2730  +*/
         2731  +struct Upsert {
         2732  +  ExprList *pUpsertTarget;  /* Optional description of conflicting index */
         2733  +  Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
         2734  +  Index *pUpsertIdx;        /* Constraint that pUpsertTarget identifies */
         2735  +  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
         2736  +  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */
         2737  +  SrcList *pUpsertSrc;      /* Table to be updated */
         2738  +  int regData;              /* First register holding array of VALUES */
         2739  +};
  2714   2740   
  2715   2741   /*
  2716   2742   ** An instance of the following structure contains all information
  2717   2743   ** needed to generate code for a single SELECT statement.
  2718   2744   **
  2719   2745   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
  2720   2746   ** If there is a LIMIT clause, the parser sets nLimit to the value of the
................................................................................
  3207   3233   struct TriggerStep {
  3208   3234     u8 op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
  3209   3235     u8 orconf;           /* OE_Rollback etc. */
  3210   3236     Trigger *pTrig;      /* The trigger that this step is a part of */
  3211   3237     Select *pSelect;     /* SELECT statement or RHS of INSERT INTO SELECT ... */
  3212   3238     char *zTarget;       /* Target table for DELETE, UPDATE, INSERT */
  3213   3239     Expr *pWhere;        /* The WHERE clause for DELETE or UPDATE steps */
  3214         -  ExprList *pExprList; /* SET clause for UPDATE. */
         3240  +  ExprList *pExprList; /* SET clause for UPDATE */
  3215   3241     IdList *pIdList;     /* Column names for INSERT */
         3242  +  Upsert *pUpsert;     /* Upsert clauses on an INSERT */
  3216   3243     char *zSpan;         /* Original SQL text of this command */
  3217   3244     TriggerStep *pNext;  /* Next in the link-list */
  3218   3245     TriggerStep *pLast;  /* Last element in link-list. Valid for 1st elem only */
  3219   3246   };
  3220   3247   
  3221   3248   /*
  3222   3249   ** The following structure contains information used by the sqliteFix...
................................................................................
  3740   3767   #ifndef SQLITE_OMIT_AUTOINCREMENT
  3741   3768     void sqlite3AutoincrementBegin(Parse *pParse);
  3742   3769     void sqlite3AutoincrementEnd(Parse *pParse);
  3743   3770   #else
  3744   3771   # define sqlite3AutoincrementBegin(X)
  3745   3772   # define sqlite3AutoincrementEnd(X)
  3746   3773   #endif
  3747         -void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int);
         3774  +void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, Upsert*);
  3748   3775   void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*);
  3749   3776   IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
  3750   3777   int sqlite3IdListIndex(IdList*,const char*);
  3751   3778   SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int);
  3752   3779   SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
  3753   3780   SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,
  3754   3781                                         Token*, Select*, Expr*, IdList*);
................................................................................
  3770   3797   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  3771   3798   int sqlite3IsReadOnly(Parse*, Table*, int);
  3772   3799   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  3773   3800   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  3774   3801   Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,char*);
  3775   3802   #endif
  3776   3803   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*, ExprList*, Expr*);
  3777         -void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*);
         3804  +void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*,
         3805  +                   Upsert*);
  3778   3806   WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
  3779   3807   void sqlite3WhereEnd(WhereInfo*);
  3780   3808   LogEst sqlite3WhereOutputRowCount(WhereInfo*);
  3781   3809   int sqlite3WhereIsDistinct(WhereInfo*);
  3782   3810   int sqlite3WhereIsOrdered(WhereInfo*);
  3783   3811   int sqlite3WhereOrderedInnerLoop(WhereInfo*);
  3784   3812   int sqlite3WhereIsSorted(WhereInfo*);
................................................................................
  3863   3891   int sqlite3IsRowid(const char*);
  3864   3892   void sqlite3GenerateRowDelete(
  3865   3893       Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8,int);
  3866   3894   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*, int);
  3867   3895   int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
  3868   3896   void sqlite3ResolvePartIdxLabel(Parse*,int);
  3869   3897   void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
  3870         -                                     u8,u8,int,int*,int*);
         3898  +                                     u8,u8,int,int*,int*,Upsert*);
  3871   3899   #ifdef SQLITE_ENABLE_NULL_TRIM
  3872   3900     void sqlite3SetMakeRecordP5(Vdbe*,Table*);
  3873   3901   #else
  3874   3902   # define sqlite3SetMakeRecordP5(A,B)
  3875   3903   #endif
  3876   3904   void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
  3877   3905   int sqlite3OpenTableAndIndices(Parse*, Table*, int, u8, int, u8*, int*, int*);
................................................................................
  3916   3944                               int, int, int);
  3917   3945     void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int);
  3918   3946     void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
  3919   3947     void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
  3920   3948     TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*,
  3921   3949                                           const char*,const char*);
  3922   3950     TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
  3923         -                                        Select*,u8,const char*,const char*);
         3951  +                                        Select*,u8,Upsert*,
         3952  +                                        const char*,const char*);
  3924   3953     TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8,
  3925   3954                                           const char*,const char*);
  3926   3955     TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*,
  3927   3956                                           const char*,const char*);
  3928   3957     void sqlite3DeleteTrigger(sqlite3*, Trigger*);
  3929   3958     void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
  3930   3959     u32 sqlite3TriggerColmask(Parse*,Trigger*,ExprList*,int,int,Table*,int);
................................................................................
  4255   4284     With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
  4256   4285     void sqlite3WithDelete(sqlite3*,With*);
  4257   4286     void sqlite3WithPush(Parse*, With*, u8);
  4258   4287   #else
  4259   4288   #define sqlite3WithPush(x,y,z)
  4260   4289   #define sqlite3WithDelete(x,y)
  4261   4290   #endif
         4291  +#ifndef SQLITE_OMIT_UPSERT
         4292  +  Upsert *sqlite3UpsertNew(sqlite3*,ExprList*,Expr*,ExprList*,Expr*);
         4293  +  void sqlite3UpsertDelete(sqlite3*,Upsert*);
         4294  +  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
         4295  +  int sqlite3UpsertAnalyzeTarget(Parse*,SrcList*,Upsert*);
         4296  +  void sqlite3UpsertDoUpdate(Parse*,Upsert*,Table*,Index*,int);
         4297  +#else
         4298  +#define sqlite3UpsertNew(v,w,x,y,z) ((Upsert*)0)
         4299  +#define sqlite3UpsertDelete(x,y)
         4300  +#define sqlite3UpsertDup(x,y)       ((Upsert*)0)
         4301  +#endif
         4302  +
  4262   4303   
  4263   4304   /* Declarations for functions in fkey.c. All of these are replaced by
  4264   4305   ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
  4265   4306   ** key functionality is available. If OMIT_TRIGGER is defined but
  4266   4307   ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
  4267   4308   ** this case foreign keys are parsed, but no other functionality is
  4268   4309   ** provided (enforcement of FK constraints requires the triggers sub-system).

Changes to src/trigger.c.

    21     21       TriggerStep * pTmp = pTriggerStep;
    22     22       pTriggerStep = pTriggerStep->pNext;
    23     23   
    24     24       sqlite3ExprDelete(db, pTmp->pWhere);
    25     25       sqlite3ExprListDelete(db, pTmp->pExprList);
    26     26       sqlite3SelectDelete(db, pTmp->pSelect);
    27     27       sqlite3IdListDelete(db, pTmp->pIdList);
           28  +    sqlite3UpsertDelete(db, pTmp->pUpsert);
    28     29       sqlite3DbFree(db, pTmp->zSpan);
    29     30   
    30     31       sqlite3DbFree(db, pTmp);
    31     32     }
    32     33   }
    33     34   
    34     35   /*
................................................................................
   412    413   */
   413    414   TriggerStep *sqlite3TriggerInsertStep(
   414    415     sqlite3 *db,        /* The database connection */
   415    416     Token *pTableName,  /* Name of the table into which we insert */
   416    417     IdList *pColumn,    /* List of columns in pTableName to insert into */
   417    418     Select *pSelect,    /* A SELECT statement that supplies values */
   418    419     u8 orconf,          /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */
          420  +  Upsert *pUpsert,    /* ON CONFLICT clauses for upsert */
   419    421     const char *zStart, /* Start of SQL text */
   420    422     const char *zEnd    /* End of SQL text */
   421    423   ){
   422    424     TriggerStep *pTriggerStep;
   423    425   
   424    426     assert(pSelect != 0 || db->mallocFailed);
   425    427   
   426    428     pTriggerStep = triggerStepAllocate(db, TK_INSERT, pTableName, zStart, zEnd);
   427    429     if( pTriggerStep ){
   428    430       pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
   429    431       pTriggerStep->pIdList = pColumn;
          432  +    pTriggerStep->pUpsert = pUpsert;
   430    433       pTriggerStep->orconf = orconf;
   431    434     }else{
          435  +    testcase( pColumn );
   432    436       sqlite3IdListDelete(db, pColumn);
          437  +    testcase( pUpsert );
          438  +    sqlite3UpsertDelete(db, pUpsert);
   433    439     }
   434    440     sqlite3SelectDelete(db, pSelect);
   435    441   
   436    442     return pTriggerStep;
   437    443   }
   438    444   
   439    445   /*
................................................................................
   742    748   
   743    749       switch( pStep->op ){
   744    750         case TK_UPDATE: {
   745    751           sqlite3Update(pParse, 
   746    752             targetSrcList(pParse, pStep),
   747    753             sqlite3ExprListDup(db, pStep->pExprList, 0), 
   748    754             sqlite3ExprDup(db, pStep->pWhere, 0), 
   749         -          pParse->eOrconf, 0, 0
          755  +          pParse->eOrconf, 0, 0, 0
   750    756           );
   751    757           break;
   752    758         }
   753    759         case TK_INSERT: {
   754    760           sqlite3Insert(pParse, 
   755    761             targetSrcList(pParse, pStep),
   756    762             sqlite3SelectDup(db, pStep->pSelect, 0), 
   757    763             sqlite3IdListDup(db, pStep->pIdList), 
   758         -          pParse->eOrconf
          764  +          pParse->eOrconf,
          765  +          sqlite3UpsertDup(db, pStep->pUpsert)
   759    766           );
   760    767           break;
   761    768         }
   762    769         case TK_DELETE: {
   763    770           sqlite3DeleteFrom(pParse, 
   764    771             targetSrcList(pParse, pStep),
   765    772             sqlite3ExprDup(db, pStep->pWhere, 0), 0, 0

Changes to src/update.c.

    89     89   void sqlite3Update(
    90     90     Parse *pParse,         /* The parser context */
    91     91     SrcList *pTabList,     /* The table in which we should change things */
    92     92     ExprList *pChanges,    /* Things to be changed */
    93     93     Expr *pWhere,          /* The WHERE clause.  May be null */
    94     94     int onError,           /* How to handle constraint errors */
    95     95     ExprList *pOrderBy,    /* ORDER BY clause. May be null */
    96         -  Expr *pLimit           /* LIMIT clause. May be null */
           96  +  Expr *pLimit,          /* LIMIT clause. May be null */
           97  +  Upsert *pUpsert        /* ON CONFLICT clause, or null */
    97     98   ){
    98     99     int i, j;              /* Loop counters */
    99    100     Table *pTab;           /* The table to be updated */
   100    101     int addrTop = 0;       /* VDBE instruction address of the start of the loop */
   101    102     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   102    103     Vdbe *v;               /* The virtual database engine */
   103    104     Index *pIdx;           /* For looping over indices */
................................................................................
   222    223     aToOpen[nIdx+1] = 0;
   223    224     for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
   224    225   
   225    226     /* Initialize the name-context */
   226    227     memset(&sNC, 0, sizeof(sNC));
   227    228     sNC.pParse = pParse;
   228    229     sNC.pSrcList = pTabList;
          230  +  sNC.uNC.pUpsert = pUpsert;
          231  +  sNC.ncFlags = NC_UUpsert;
   229    232   
   230    233     /* Resolve the column names in all the expressions of the
   231    234     ** of the UPDATE statement.  Also find the column index
   232    235     ** for each column to be updated in the pChanges array.  For each
   233    236     ** column to be updated, make sure we have authorization to change
   234    237     ** that column.
   235    238     */
................................................................................
   510    513       labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, labelBreak,
   511    514                                regOldRowid);
   512    515       VdbeCoverage(v);
   513    516       sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
   514    517       VdbeCoverage(v);
   515    518     }
   516    519   
   517         -  /* If the record number will change, set register regNewRowid to
   518         -  ** contain the new value. If the record number is not being modified,
          520  +  /* If the rowid value will change, set register regNewRowid to
          521  +  ** contain the new value. If the rowid is not being modified,
   519    522     ** then regNewRowid is the same register as regOldRowid, which is
   520    523     ** already populated.  */
   521    524     assert( chngKey || pTrigger || hasFK || regOldRowid==regNewRowid );
   522    525     if( chngRowid ){
   523    526       sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
   524    527       sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); VdbeCoverage(v);
   525    528     }
................................................................................
   622    625     if( !isView ){
   623    626       int addr1 = 0;        /* Address of jump instruction */
   624    627   
   625    628       /* Do constraint checks. */
   626    629       assert( regOldRowid>0 );
   627    630       sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
   628    631           regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace,
   629         -        aXRef);
          632  +        aXRef, 0);
   630    633   
   631    634       /* Do FK constraint checks. */
   632    635       if( hasFK ){
   633    636         sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
   634    637       }
   635    638   
   636    639       /* Delete the index entries associated with the current record.  */

Added src/upsert.c.

            1  +/*
            2  +** 2018-04-12
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +** This file contains code to implement various aspects of UPSERT
           13  +** processing and handling of the Upsert object.
           14  +*/
           15  +#include "sqliteInt.h"
           16  +
           17  +#ifndef SQLITE_OMIT_UPSERT
           18  +/*
           19  +** Free a list of Upsert objects
           20  +*/
           21  +void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
           22  +  if( p ){
           23  +    sqlite3ExprListDelete(db, p->pUpsertTarget);
           24  +    sqlite3ExprDelete(db, p->pUpsertTargetWhere);
           25  +    sqlite3ExprListDelete(db, p->pUpsertSet);
           26  +    sqlite3ExprDelete(db, p->pUpsertWhere);
           27  +    sqlite3DbFree(db, p);
           28  +  }
           29  +}
           30  +
           31  +/*
           32  +** Duplicate an Upsert object.
           33  +*/
           34  +Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
           35  +  if( p==0 ) return 0;
           36  +  return sqlite3UpsertNew(db,
           37  +           sqlite3ExprListDup(db, p->pUpsertTarget, 0),
           38  +           sqlite3ExprDup(db, p->pUpsertTargetWhere, 0),
           39  +           sqlite3ExprListDup(db, p->pUpsertSet, 0),
           40  +           sqlite3ExprDup(db, p->pUpsertWhere, 0)
           41  +         );
           42  +}
           43  +
           44  +/*
           45  +** Create a new Upsert object.
           46  +*/
           47  +Upsert *sqlite3UpsertNew(
           48  +  sqlite3 *db,           /* Determines which memory allocator to use */
           49  +  ExprList *pTarget,     /* Target argument to ON CONFLICT, or NULL */
           50  +  Expr *pTargetWhere,    /* Optional WHERE clause on the target */
           51  +  ExprList *pSet,        /* UPDATE columns, or NULL for a DO NOTHING */
           52  +  Expr *pWhere           /* WHERE clause for the ON CONFLICT UPDATE */
           53  +){
           54  +  Upsert *pNew;
           55  +  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
           56  +  if( pNew==0 ){
           57  +    sqlite3ExprListDelete(db, pTarget);
           58  +    sqlite3ExprDelete(db, pTargetWhere);
           59  +    sqlite3ExprListDelete(db, pSet);
           60  +    sqlite3ExprDelete(db, pWhere);
           61  +    return 0;
           62  +  }else{
           63  +    pNew->pUpsertTarget = pTarget;
           64  +    pNew->pUpsertTargetWhere = pTargetWhere;
           65  +    pNew->pUpsertSet = pSet;
           66  +    pNew->pUpsertWhere = pWhere;
           67  +    pNew->pUpsertIdx = 0;
           68  +  }
           69  +  return pNew;
           70  +}
           71  +
           72  +/*
           73  +** Analyze the ON CONFLICT clause described by pUpsert.  Resolve all
           74  +** symbols in the conflict-target.
           75  +**
           76  +** Return SQLITE_OK if everything works, or an error code is something
           77  +** is wrong.
           78  +*/
           79  +int sqlite3UpsertAnalyzeTarget(
           80  +  Parse *pParse,     /* The parsing context */
           81  +  SrcList *pTabList, /* Table into which we are inserting */
           82  +  Upsert *pUpsert    /* The ON CONFLICT clauses */
           83  +){
           84  +  Table *pTab;            /* That table into which we are inserting */
           85  +  int rc;                 /* Result code */
           86  +  int iCursor;            /* Cursor used by pTab */
           87  +  Index *pIdx;            /* One of the indexes of pTab */
           88  +  ExprList *pTarget;      /* The conflict-target clause */
           89  +  Expr *pTerm;            /* One term of the conflict-target clause */
           90  +  NameContext sNC;        /* Context for resolving symbolic names */
           91  +  Expr sCol[2];           /* Index column converted into an Expr */
           92  +
           93  +  assert( pTabList->nSrc==1 );
           94  +  assert( pTabList->a[0].pTab!=0 );
           95  +  assert( pUpsert!=0 );
           96  +  assert( pUpsert->pUpsertTarget!=0 );
           97  +
           98  +  /* Resolve all symbolic names in the conflict-target clause, which
           99  +  ** includes both the list of columns and the optional partial-index
          100  +  ** WHERE clause.
          101  +  */
          102  +  memset(&sNC, 0, sizeof(sNC));
          103  +  sNC.pParse = pParse;
          104  +  sNC.pSrcList = pTabList;
          105  +  rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
          106  +  if( rc ) return rc;
          107  +  rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
          108  +  if( rc ) return rc;
          109  +
          110  +  /* Check to see if the conflict target matches the rowid. */  
          111  +  pTab = pTabList->a[0].pTab;
          112  +  pTarget = pUpsert->pUpsertTarget;
          113  +  iCursor = pTabList->a[0].iCursor;
          114  +  if( HasRowid(pTab) 
          115  +   && pTarget->nExpr==1
          116  +   && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
          117  +   && pTerm->iColumn==XN_ROWID
          118  +  ){
          119  +    /* The conflict-target is the rowid of the primary table */
          120  +    assert( pUpsert->pUpsertIdx==0 );
          121  +    return SQLITE_OK;
          122  +  }
          123  +
          124  +  /* Initialize sCol[0..1] to be an expression parse tree for a
          125  +  ** single column of an index.  The sCol[0] node will be the TK_COLLATE
          126  +  ** operator and sCol[1] will be the TK_COLUMN operator.  Code below
          127  +  ** will populate the specific collation and column number values
          128  +  ** prior to comparing against the conflict-target expression.
          129  +  */
          130  +  memset(sCol, 0, sizeof(sCol));
          131  +  sCol[0].op = TK_COLLATE;
          132  +  sCol[0].pLeft = &sCol[1];
          133  +  sCol[1].op = TK_COLUMN;
          134  +  sCol[1].iTable = pTabList->a[0].iCursor;
          135  +
          136  +  /* Check for matches against other indexes */
          137  +  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          138  +    int ii, jj, nn;
          139  +    if( !IsUniqueIndex(pIdx) ) continue;
          140  +    if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
          141  +    if( pIdx->pPartIdxWhere ){
          142  +      if( pUpsert->pUpsertTargetWhere==0 ) continue;
          143  +      if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
          144  +                             pIdx->pPartIdxWhere, iCursor)!=0 ){
          145  +        continue;
          146  +      }
          147  +    }
          148  +    nn = pIdx->nKeyCol;
          149  +    for(ii=0; ii<nn; ii++){
          150  +      Expr *pExpr;
          151  +      if( pIdx->aiColumn[ii]==XN_EXPR ){
          152  +        assert( pIdx->aColExpr!=0 );
          153  +        assert( pIdx->aColExpr->nExpr>ii );
          154  +        pExpr = pIdx->aColExpr->a[ii].pExpr;
          155  +      }else{
          156  +        sCol[1].iColumn = pIdx->aiColumn[ii];
          157  +        sCol[0].u.zToken = (char*)pIdx->azColl[ii];
          158  +        pExpr = &sCol[0];
          159  +      }
          160  +      for(jj=0; jj<nn; jj++){
          161  +        if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr,iCursor)<2 ){
          162  +          break;  /* Column ii of the index matches column jj of target */
          163  +        }
          164  +      }
          165  +      if( jj>=nn ){
          166  +        /* The target contains no match for column jj of the index */
          167  +        break;
          168  +      }
          169  +    }
          170  +    if( ii<nn ){
          171  +      /* Column ii of the index did not match any term of the conflict target.
          172  +      ** Continue the search with the next index. */
          173  +      continue;
          174  +    }
          175  +    pUpsert->pUpsertIdx = pIdx;
          176  +    return SQLITE_OK;
          177  +  }
          178  +  sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
          179  +                          "PRIMARY KEY or UNIQUE constraint");
          180  +  return SQLITE_ERROR;
          181  +}
          182  +
          183  +/*
          184  +** Generate bytecode that does an UPDATE as part of an upsert.
          185  +**
          186  +** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK.
          187  +** In this case parameter iCur is a cursor open on the table b-tree that
          188  +** currently points to the conflicting table row. Otherwise, if pIdx
          189  +** is not NULL, then pIdx is the constraint that failed and iCur is a
          190  +** cursor points to the conflicting row.
          191  +*/
          192  +void sqlite3UpsertDoUpdate(
          193  +  Parse *pParse,        /* The parsing and code-generating context */
          194  +  Upsert *pUpsert,      /* The ON CONFLICT clause for the upsert */
          195  +  Table *pTab,          /* The table being updated */
          196  +  Index *pIdx,          /* The UNIQUE constraint that failed */
          197  +  int iCur              /* Cursor for pIdx (or pTab if pIdx==NULL) */
          198  +){
          199  +  Vdbe *v = pParse->pVdbe;
          200  +  sqlite3 *db = pParse->db;
          201  +  int regKey;               /* Register(s) containing the key */
          202  +  Expr *pWhere;             /* Where clause for the UPDATE */
          203  +  Expr *pE1, *pE2;
          204  +  SrcList *pSrc;            /* FROM clause for the UPDATE */
          205  +
          206  +  assert( v!=0 );
          207  +  VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
          208  +  pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0);
          209  +  if( pIdx==0 || HasRowid(pTab) ){
          210  +    /* We are dealing with an IPK */
          211  +    regKey = ++pParse->nMem;
          212  +    if( pIdx ){
          213  +      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey);
          214  +    }else{
          215  +      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey);
          216  +    }
          217  +    pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
          218  +    if( pE1 ){
          219  +      pE1->pTab = pTab;
          220  +      pE1->iTable = pParse->nTab;
          221  +      pE1->iColumn = -1;
          222  +    }
          223  +    pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
          224  +    if( pE2 ){
          225  +      pE2->iTable = regKey;
          226  +      pE2->affinity = SQLITE_AFF_INTEGER;
          227  +    }
          228  +    pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
          229  +  }else{
          230  +    /* a WITHOUT ROWID table */
          231  +    int i, j;
          232  +    int iTab = pParse->nTab+1;
          233  +    Index *pX;
          234  +    for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){
          235  +      iTab++;
          236  +    }
          237  +    for(i=0; i<pIdx->nKeyCol; i++){
          238  +      regKey = ++pParse->nMem;
          239  +      sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey);
          240  +      j = pIdx->aiColumn[i];
          241  +      VdbeComment((v, "%s", pTab->aCol[j].zName));
          242  +      pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
          243  +      if( pE1 ){
          244  +        pE1->pTab = pTab;
          245  +        pE1->iTable = iTab;
          246  +        pE1->iColumn = j;
          247  +      }
          248  +      pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
          249  +      if( pE2 ){
          250  +        pE2->iTable = regKey;
          251  +        pE2->affinity = pTab->zColAff[j];
          252  +      }
          253  +      pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
          254  +    }
          255  +  }
          256  +  pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
          257  +  sqlite3Update(pParse, pSrc, 
          258  +      sqlite3ExprListDup(db, pUpsert->pUpsertSet, 0),
          259  +      pWhere, OE_Abort, 0, 0, pUpsert);
          260  +  VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
          261  +}
          262  +
          263  +#endif /* SQLITE_OMIT_UPSERT */

Changes to src/vdbe.h.

   216    216   void sqlite3VdbeRunOnlyOnce(Vdbe*);
   217    217   void sqlite3VdbeReusable(Vdbe*);
   218    218   void sqlite3VdbeDelete(Vdbe*);
   219    219   void sqlite3VdbeClearObject(sqlite3*,Vdbe*);
   220    220   void sqlite3VdbeMakeReady(Vdbe*,Parse*);
   221    221   int sqlite3VdbeFinalize(Vdbe*);
   222    222   void sqlite3VdbeResolveLabel(Vdbe*, int);
          223  +#ifdef SQLITE_COVERAGE_TEST
          224  +  int sqlite3VdbeLabelHasBeenResolved(Vdbe*,int);
          225  +#endif
   223    226   int sqlite3VdbeCurrentAddr(Vdbe*);
   224    227   #ifdef SQLITE_DEBUG
   225    228     int sqlite3VdbeAssertMayAbort(Vdbe *, int);
   226    229   #endif
   227    230   void sqlite3VdbeResetStepResult(Vdbe*);
   228    231   void sqlite3VdbeRewind(Vdbe*);
   229    232   int sqlite3VdbeReset(Vdbe*);

Changes to src/vdbeaux.c.

   388    388   void sqlite3VdbeResolveLabel(Vdbe *v, int x){
   389    389     Parse *p = v->pParse;
   390    390     int j = ADDR(x);
   391    391     assert( v->magic==VDBE_MAGIC_INIT );
   392    392     assert( j<p->nLabel );
   393    393     assert( j>=0 );
   394    394     if( p->aLabel ){
          395  +#ifdef SQLITE_DEBUG
          396  +    if( p->db->flags & SQLITE_VdbeAddopTrace ){
          397  +      printf("RESOLVE LABEL %d to %d\n", x, v->nOp);
          398  +    }
          399  +#endif
          400  +    assert( p->aLabel[j]==(-1) ); /* Labels may only be resolved once */
   395    401       p->aLabel[j] = v->nOp;
   396    402     }
   397    403   }
   398    404   
          405  +#ifdef SQLITE_COVERAGE_TEST
          406  +/*
          407  +** Return TRUE if and only if the label x has already been resolved.
          408  +** Return FALSE (zero) if label x is still unresolved.
          409  +**
          410  +** This routine is only used inside of testcase() macros, and so it
          411  +** only exists when measuring test coverage.
          412  +*/
          413  +int sqlite3VdbeLabelHasBeenResolved(Vdbe *v, int x){
          414  +  return v->pParse->aLabel && v->pParse->aLabel[ADDR(x)]>=0;
          415  +}
          416  +#endif /* SQLITE_COVERAGE_TEST */
          417  +
   399    418   /*
   400    419   ** Mark the VDBE as one that can only be run one time.
   401    420   */
   402    421   void sqlite3VdbeRunOnlyOnce(Vdbe *p){
   403    422     p->runOnlyOnce = 1;
   404    423   }
   405    424   

Changes to test/distinct.test.

   164    164     2   "b, a FROM t1"                                       {}      {B A b a}
   165    165     3   "a, b, c FROM t1"                                    {hash}  {A B C a b c}
   166    166     4   "a, b, c FROM t1 ORDER BY a, b, c"                   {btree} {A B C a b c}
   167    167     5   "b FROM t1 WHERE a = 'a'"                            {}      {b}
   168    168     6   "b FROM t1 ORDER BY +b COLLATE binary"          {btree hash} {B b}
   169    169     7   "a FROM t1"                                          {}      {A a}
   170    170     8   "b COLLATE nocase FROM t1"                           {}      {b}
   171         -  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {b}
          171  +  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {B}
   172    172   } {
   173    173     do_execsql_test    2.$tn.1 "SELECT DISTINCT $sql" $res
   174    174     do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
   175    175   }
   176    176   
   177    177   do_execsql_test 2.A {
   178    178     SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;

Added test/upsert1.test.

            1  +# 2018-04-12
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test cases for UPSERT
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix zipfile
           17  +
           18  +do_execsql_test upsert1-100 {
           19  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
           20  +  CREATE UNIQUE INDEX t1x1 ON t1(b);
           21  +  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
           22  +  INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING;
           23  +  SELECT * FROM t1;
           24  +} {1 2 0}
           25  +do_execsql_test upsert1-101 {
           26  +  DELETE FROM t1;
           27  +  INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
           28  +  INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING;
           29  +  SELECT * FROM t1;
           30  +} {2 3 0}
           31  +do_execsql_test upsert1-102 {
           32  +  DELETE FROM t1;
           33  +  INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;
           34  +  INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING;
           35  +  SELECT * FROM t1;
           36  +} {3 4 0}
           37  +do_catchsql_test upsert1-110 {
           38  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
           39  +  SELECT * FROM t1;
           40  +} {1 {no such column: x}}
           41  +do_catchsql_test upsert1-120 {
           42  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
           43  +  SELECT * FROM t1;
           44  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           45  +breakpoint
           46  +do_catchsql_test upsert1-130 {
           47  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
           48  +  SELECT * FROM t1;
           49  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           50  +do_execsql_test upsert1-140 {
           51  +  DELETE FROM t1;
           52  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
           53  +  SELECT * FROM t1;
           54  +} {5 6 0}
           55  +
           56  +do_catchsql_test upsert1-200 {
           57  +  DROP TABLE t1;
           58  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c DEFAULT 0);
           59  +  CREATE UNIQUE INDEX t1x1 ON t1(a+b);
           60  +  INSERT INTO t1(a,b) VALUES(7,8) ON CONFLICT(a+b) DO NOTHING;
           61  +  INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a+b) DO NOTHING;
           62  +  SELECT * FROM t1;
           63  +} {0 {7 8 0}}
           64  +do_catchsql_test upsert1-201 {
           65  +  INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a) DO NOTHING;
           66  +} {1 {UNIQUE constraint failed: index 't1x1'}}
           67  +do_catchsql_test upsert1-210 {
           68  +  DELETE FROM t1;
           69  +  INSERT INTO t1(a,b) VALUES(9,10) ON CONFLICT(a+(+b)) DO NOTHING;
           70  +  SELECT * FROM t1;
           71  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           72  +
           73  +do_catchsql_test upsert1-300 {
           74  +  DROP INDEX t1x1;
           75  +  DELETE FROM t1;
           76  +  CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10;
           77  +  INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) DO NOTHING;
           78  +  SELECT * FROM t1;
           79  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           80  +do_catchsql_test upsert1-310 {
           81  +  DELETE FROM t1;
           82  +  INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
           83  +  SELECT * FROM t1;
           84  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           85  +do_execsql_test upsert1-320 {
           86  +  DELETE FROM t1;
           87  +  INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
           88  +         ON CONFLICT(b) WHERE b>10 DO NOTHING;
           89  +  SELECT *, 'x' FROM t1 ORDER BY b, a;
           90  +} {1 2 0 x 3 2 0 x 4 20 0 x}
           91  +
           92  +finish_test

Added test/upsert2.test.

            1  +# 2018-04-17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test cases for UPSERT
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix zipfile
           17  +
           18  +do_execsql_test upsert2-100 {
           19  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
           20  +  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
           21  +  INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
           22  +    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
           23  +  SELECT *, 'x' FROM t1 ORDER BY a;
           24  +} {1 8 1 x 2 11 0 x 3 4 0 x}
           25  +do_execsql_test upsert2-110 {
           26  +  DROP TABLE t1;
           27  +  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
           28  +  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
           29  +  INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
           30  +    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
           31  +  SELECT *, 'x' FROM t1 ORDER BY a;
           32  +} {1 8 1 x 2 11 0 x 3 4 0 x}
           33  +
           34  +do_execsql_test upsert2-200 {
           35  +  DROP TABLE t1;
           36  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
           37  +  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
           38  +  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
           39  +  INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
           40  +    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
           41  +  SELECT *, 'x' FROM t1 ORDER BY a;
           42  +} {1 99 2 x 2 15 1 x 3 4 0 x}
           43  +do_execsql_test upsert2-201 {
           44  +  DELETE FROM t1;
           45  +  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
           46  +  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
           47  +  INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
           48  +    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t2.c+1 WHERE t2.b<excluded.b;
           49  +  SELECT *, 'x' FROM t1 ORDER BY a;
           50  +} {1 99 2 x 2 15 1 x 3 4 0 x}
           51  +do_catchsql_test upsert2-202 {
           52  +  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
           53  +  INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
           54  +    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t1.c+1 WHERE t1.b<excluded.b;
           55  +} {1 {no such column: t1.c}}
           56  +do_execsql_test upsert2-210 {
           57  +  DROP TABLE t1;
           58  +  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
           59  +  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
           60  +  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
           61  +  INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
           62  +    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
           63  +  SELECT *, 'x' FROM t1 ORDER BY a;
           64  +} {1 99 2 x 2 15 1 x 3 4 0 x}
           65  +
           66  +# On an ON CONFLICT DO UPDATE, the before-insert, before-update, and
           67  +# after-update triggers fire.
           68  +#
           69  +do_execsql_test upsert2-300 {
           70  +  DROP TABLE t1;
           71  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
           72  +  CREATE TABLE record(x TEXT, y TEXT);
           73  +  CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
           74  +    INSERT INTO record(x,y)
           75  +        VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
           76  +  END;
           77  +  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
           78  +    INSERT INTO record(x,y)
           79  +        VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
           80  +  END;
           81  +  CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
           82  +    INSERT INTO record(x,y)
           83  +        VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
           84  +                                      old.a,old.b,old.c,new.a,new.b,new.c));
           85  +  END;
           86  +  CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
           87  +    INSERT INTO record(x,y)
           88  +        VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
           89  +                                      old.a,old.b,old.c,new.a,new.b,new.c));
           90  +  END;
           91  +  INSERT INTO t1(a,b) VALUES(1,2);
           92  +  DELETE FROM record;
           93  +  INSERT INTO t1(a,b) VALUES(1,2)
           94  +    ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
           95  +  SELECT * FROM record
           96  +} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
           97  +
           98  +# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
           99  +#
          100  +do_execsql_test upsert2-310 {
          101  +  DELETE FROM record;
          102  +  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
          103  +  SELECT * FROM record;
          104  +} {before-insert 1,2,0}
          105  +
          106  +# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
          107  +# trigger fires.
          108  +#
          109  +do_execsql_test upsert2-320 {
          110  +  DELETE FROM record;
          111  +  INSERT INTO t1(a,b) VALUES(1,2)
          112  +    ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
          113  +  SELECT * FROM record;
          114  +} {before-insert 1,2,0}
          115  +do_execsql_test upsert2-321 {
          116  +  SELECT * FROM t1;
          117  +} {1 2 1}
          118  +
          119  +# Trigger tests repeated for a WITHOUT ROWID table.
          120  +#
          121  +do_execsql_test upsert2-400 {
          122  +  DROP TABLE t1;
          123  +  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
          124  +  CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
          125  +    INSERT INTO record(x,y)
          126  +        VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
          127  +  END;
          128  +  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
          129  +    INSERT INTO record(x,y)
          130  +        VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
          131  +  END;
          132  +  CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
          133  +    INSERT INTO record(x,y)
          134  +        VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
          135  +                                      old.a,old.b,old.c,new.a,new.b,new.c));
          136  +  END;
          137  +  CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
          138  +    INSERT INTO record(x,y)
          139  +        VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
          140  +                                      old.a,old.b,old.c,new.a,new.b,new.c));
          141  +  END;
          142  +  INSERT INTO t1(a,b) VALUES(1,2);
          143  +  DELETE FROM record;
          144  +  INSERT INTO t1(a,b) VALUES(1,2)
          145  +    ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
          146  +  SELECT * FROM record
          147  +} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
          148  +
          149  +# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
          150  +#
          151  +do_execsql_test upsert2-410 {
          152  +  DELETE FROM record;
          153  +  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
          154  +  SELECT * FROM record;
          155  +} {before-insert 1,2,0}
          156  +
          157  +# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
          158  +# trigger fires.
          159  +#
          160  +do_execsql_test upsert2-420 {
          161  +  DELETE FROM record;
          162  +  INSERT INTO t1(a,b) VALUES(1,2)
          163  +    ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
          164  +  SELECT * FROM record;
          165  +} {before-insert 1,2,0}
          166  +do_execsql_test upsert2-421 {
          167  +  SELECT * FROM t1;
          168  +} {1 2 1}
          169  +
          170  +finish_test

Added test/upsert3.test.

            1  +# 2018-04-17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test cases for UPSERT
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix zipfile
           18  +
           19  +do_execsql_test upsert3-100 {
           20  +  CREATE TABLE t1(k int, v text);
           21  +  CREATE UNIQUE INDEX x1 ON t1(k, v);
           22  +} {}
           23  +do_catchsql_test upsert3-110 {
           24  +  INSERT INTO t1 VALUES(0,'abcdefghij')
           25  +     ON CONFLICT(k) DO NOTHING;
           26  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           27  +do_catchsql_test upsert3-120 {
           28  +  INSERT INTO t1 VALUES(0,'abcdefghij')
           29  +     ON CONFLICT(v) DO NOTHING;
           30  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           31  +
           32  +do_execsql_test upsert3-130 {
           33  +  INSERT INTO t1 VALUES(0, 'abcdefghij')
           34  +      ON CONFLICT(k,v) DO NOTHING;
           35  +  SELECT * FROM t1;
           36  +} {0 abcdefghij}
           37  +do_execsql_test upsert3-140 {
           38  +  INSERT INTO t1 VALUES(0, 'abcdefghij')
           39  +      ON CONFLICT(v,k) DO NOTHING;
           40  +  SELECT * FROM t1;
           41  +} {0 abcdefghij}
           42  +
           43  +do_execsql_test upsert3-200 {
           44  +  CREATE TABLE excluded(a INT, b INT, c INT DEFAULT 0);
           45  +  CREATE UNIQUE INDEX excludedab ON excluded(a,b);
           46  +  INSERT INTO excluded(a,b) VALUES(1,2),(1,2),(3,4),(1,2),(5,6),(3,4)
           47  +    ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1;
           48  +  SELECT *, 'x' FROM excluded ORDER BY a;
           49  +} {1 2 2 x 3 4 1 x 5 6 0 x}
           50  +do_execsql_test upsert3-210 {
           51  +  INSERT INTO excluded(a,b,c) AS base VALUES(1,2,8),(1,2,3)
           52  +    ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1 WHERE base.c<excluded.c;
           53  +  SELECT *, 'x' FROM excluded ORDER BY a;
           54  +} {1 2 9 x 3 4 1 x 5 6 0 x}
           55  +
           56  +
           57  +
           58  +finish_test

Added test/upsert4.test.

            1  +# 2018-04-17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test cases for UPSERT
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix upsert2
           17  +
           18  +foreach {tn sql} {
           19  +  1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) }
           20  +  2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) }
           21  +  3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID}
           22  +} {
           23  +  reset_db
           24  +  execsql $sql
           25  +
           26  +  do_execsql_test 1.$tn.0 {
           27  +    INSERT INTO t1 VALUES(1, NULL, 'one');
           28  +    INSERT INTO t1 VALUES(2, NULL, 'two');
           29  +    INSERT INTO t1 VALUES(3, NULL, 'three');
           30  +  }
           31  +  
           32  +  do_execsql_test 1.$tn.1 {
           33  +    INSERT INTO t1 VALUES(1, NULL, 'xyz') ON CONFLICT DO NOTHING;
           34  +    SELECT * FROM t1;
           35  +  } {
           36  +    1 {} one 2 {} two 3 {} three
           37  +  }
           38  +  
           39  +  do_execsql_test 1.$tn.2 {
           40  +    INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT DO NOTHING;
           41  +    SELECT * FROM t1;
           42  +  } {
           43  +    1 {} one 2 {} two 3 {} three
           44  +  }
           45  +  
           46  +  do_execsql_test 1.$tn.3 {
           47  +    INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT (c) DO UPDATE SET b = 1;
           48  +    SELECT * FROM t1;
           49  +  } {
           50  +    1 {} one 2 1 two 3 {} three
           51  +  }
           52  +  
           53  +  do_execsql_test 1.$tn.4 {
           54  +    INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET b=2;
           55  +    SELECT * FROM t1;
           56  +  } {1 {} one 2 2 two 3 {} three}
           57  +
           58  +  do_catchsql_test 1.$tn.5 {
           59  +    INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 
           60  +      DO UPDATE SET c = 'one';
           61  +  } {1 {UNIQUE constraint failed: t1.c}}
           62  +
           63  +  do_execsql_test 1.$tn.6 {
           64  +    SELECT * FROM t1;
           65  +  } {1 {} one 2 2 two 3 {} three}
           66  +}
           67  +
           68  +finish_test
           69  +

Changes to tool/mkkeywordhash.c.

   139    139   #  define AUTOVACUUM 0x00020000
   140    140   #endif
   141    141   #ifdef SQLITE_OMIT_CTE
   142    142   #  define CTE        0
   143    143   #else
   144    144   #  define CTE        0x00040000
   145    145   #endif
          146  +#ifdef SQLITE_OMIT_UPSERT
          147  +#  define UPSERT     0
          148  +#else
          149  +#  define UPSERT     0x00080000
          150  +#endif
   146    151   
   147    152   /*
   148    153   ** These are the keywords
   149    154   */
   150    155   static Keyword aKeywordTable[] = {
   151    156     { "ABORT",            "TK_ABORT",        CONFLICT|TRIGGER       },
   152    157     { "ACTION",           "TK_ACTION",       FKEY                   },
................................................................................
   182    187     { "DEFAULT",          "TK_DEFAULT",      ALWAYS                 },
   183    188     { "DEFERRED",         "TK_DEFERRED",     ALWAYS                 },
   184    189     { "DEFERRABLE",       "TK_DEFERRABLE",   FKEY                   },
   185    190     { "DELETE",           "TK_DELETE",       ALWAYS                 },
   186    191     { "DESC",             "TK_DESC",         ALWAYS                 },
   187    192     { "DETACH",           "TK_DETACH",       ATTACH                 },
   188    193     { "DISTINCT",         "TK_DISTINCT",     ALWAYS                 },
          194  +  { "DO",               "TK_DO",           UPSERT                 },
   189    195     { "DROP",             "TK_DROP",         ALWAYS                 },
   190    196     { "END",              "TK_END",          ALWAYS                 },
   191    197     { "EACH",             "TK_EACH",         TRIGGER                },
   192    198     { "ELSE",             "TK_ELSE",         ALWAYS                 },
   193    199     { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
   194    200     { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
   195    201     { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
................................................................................
   222    228     { "LEFT",             "TK_JOIN_KW",      ALWAYS                 },
   223    229     { "LIKE",             "TK_LIKE_KW",      ALWAYS                 },
   224    230     { "LIMIT",            "TK_LIMIT",        ALWAYS                 },
   225    231     { "MATCH",            "TK_MATCH",        ALWAYS                 },
   226    232     { "NATURAL",          "TK_JOIN_KW",      ALWAYS                 },
   227    233     { "NO",               "TK_NO",           FKEY                   },
   228    234     { "NOT",              "TK_NOT",          ALWAYS                 },
          235  +  { "NOTHING",          "TK_NOTHING",      UPSERT                 },
   229    236     { "NOTNULL",          "TK_NOTNULL",      ALWAYS                 },
   230    237     { "NULL",             "TK_NULL",         ALWAYS                 },
   231    238     { "OF",               "TK_OF",           ALWAYS                 },
   232    239     { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
   233    240     { "ON",               "TK_ON",           ALWAYS                 },
   234    241     { "OR",               "TK_OR",           ALWAYS                 },
   235    242     { "ORDER",            "TK_ORDER",        ALWAYS                 },

Changes to tool/mksqlite3c.tcl.

   359    359      loadext.c
   360    360      pragma.c
   361    361      prepare.c
   362    362      select.c
   363    363      table.c
   364    364      trigger.c
   365    365      update.c
          366  +   upsert.c
   366    367      vacuum.c
   367    368      vtab.c
   368    369      wherecode.c
   369    370      whereexpr.c
   370    371      where.c
   371    372   
   372    373      parse.c