Add support for the RETURNING clause following PostgreSQL syntax.

FossilOrigin-Name: 416c898bfb8ff9639ffbaefcfb47fce3782763af1fc67969fa91c5f01a336676

13 files changed, 292 insertions, 61 deletions

diff --git a/src/build.c b/src/build.c
index 50289c6a1..25f61e815 100644
--- a/src/build.c
+++ b/src/build.c
@@ -1243,6 +1243,75 @@ void sqlite3ColumnPropertiesFromName(Table *pTab, Column *pCol){
 }
 #endif
 
+/*
+** Name of the special TEMP trigger used to implement RETURNING.  The
+** name begins with "sqlite_" so that it is guaranteed not to collide
+** with any application-generated triggers.
+*/
+#define RETURNING_TRIGGER_NAME  "sqlite_returning"
+
+/*
+** Clean up the data structures associated with the RETURNING clause.
+*/
+static void sqlite3DeleteReturning(sqlite3 *db, Returning *pRet){
+  Hash *pHash;
+  pHash = &(db->aDb[1].pSchema->trigHash);
+  sqlite3HashInsert(pHash, RETURNING_TRIGGER_NAME, 0);
+  sqlite3ExprListDelete(db, pRet->pReturnEL);
+  sqlite3DbFree(db, pRet);
+}
+
+/*
+** Add the RETURNING clause to the parse currently underway.
+**
+** This routine creates a special TEMP trigger that will fire for each row
+** of the DML statement.  That TEMP trigger contains a single SELECT
+** statement with a result set that is the argument of the RETURNING clause.
+** The trigger has the Trigger.bReturning flag and an opcode of
+** TK_RETURNING instead of TK_SELECT, so that the trigger code generator
+** knows to handle it specially.  The TEMP trigger is automatically
+** removed at the end of the parse.
+**
+** When this routine is called, we do not yet know if the RETURNING clause
+** is attached to a DELETE, INSERT, or UPDATE, so construct it as a
+** RETURNING trigger instead.  It will then be converted into the appropriate
+** type on the first call to sqlite3TriggersExist().
+*/
+void sqlite3AddReturning(Parse *pParse, ExprList *pList){
+  Returning *pRet;
+  Hash *pHash;
+  sqlite3 *db = pParse->db;
+  assert( !pParse->bReturning );
+  pParse->bReturning = 1;
+  pRet = sqlite3DbMallocZero(db, sizeof(*pRet));
+  if( pRet==0 ){
+    sqlite3ExprListDelete(db, pList);
+    return;
+  }
+  pRet->pParse = pParse;
+  pRet->pReturnEL = pList;
+  sqlite3ParserAddCleanup(pParse,
+     (void(*)(sqlite3*,void*))sqlite3DeleteReturning, pRet);
+  if( db->mallocFailed ) return;
+  pRet->retTrig.zName = RETURNING_TRIGGER_NAME;
+  pRet->retTrig.op = TK_RETURNING;
+  pRet->retTrig.tr_tm = TRIGGER_AFTER;
+  pRet->retTrig.bReturning = 1;
+  pRet->retTrig.pSchema = db->aDb[1].pSchema;
+  pRet->retTrig.step_list = &pRet->retTStep;
+  pRet->retTStep.op = TK_RETURNING;
+  pRet->retTStep.pTrig = &pRet->retTrig;
+  pRet->retTStep.pSelect = &pRet->retSel;
+  pRet->retSel.op = TK_ALL;
+  pRet->retSel.pEList = pList;
+  pRet->retSel.pSrc = (SrcList*)&pRet->retSrcList;
+  pHash = &(db->aDb[1].pSchema->trigHash);
+  assert( sqlite3HashFind(pHash, RETURNING_TRIGGER_NAME)==0 );
+  if( sqlite3HashInsert(pHash, RETURNING_TRIGGER_NAME, &pRet->retTrig)
+          ==&pRet->retTrig ){
+    sqlite3OomFault(db);
+  }
+}
 
 /*
 ** Add a new column to the table currently being constructed.
diff --git a/src/delete.c b/src/delete.c
index 064ae7325..b2edaa9ab 100644
--- a/src/delete.c
+++ b/src/delete.c
@@ -387,6 +387,7 @@ void sqlite3DeleteFrom(
   if( (db->flags & SQLITE_CountRows)!=0
    && !pParse->nested
    && !pParse->pTriggerTab
+   && !pParse->bReturning
   ){
     memCnt = ++pParse->nMem;
     sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
@@ -608,7 +609,7 @@ void sqlite3DeleteFrom(
   ** invoke the callback function.
   */
   if( memCnt ){
-    sqlite3VdbeAddOp2(v, OP_ResultRow, memCnt, 1);
+    sqlite3VdbeAddOp2(v, OP_ChngCntRow, memCnt, 1);
     sqlite3VdbeSetNumCols(v, 1);
     sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
   }
diff --git a/src/fkey.c b/src/fkey.c
index 959e994d1..59e12b5fa 100644
--- a/src/fkey.c
+++ b/src/fkey.c
@@ -1352,7 +1352,7 @@ static Trigger *fkActionTrigger(
 
     switch( action ){
       case OE_Restrict:
-        pStep->op = TK_SELECT; 
+        pStep->op = TK_SELECT;
         break;
       case OE_Cascade: 
         if( !pChanges ){ 
diff --git a/src/insert.c b/src/insert.c
index 6047969c0..2f22121f7 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -954,6 +954,7 @@ void sqlite3Insert(
   if( (db->flags & SQLITE_CountRows)!=0
    && !pParse->nested
    && !pParse->pTriggerTab
+   && !pParse->bReturning
   ){
     regRowCount = ++pParse->nMem;
     sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
@@ -1318,7 +1319,7 @@ insert_end:
   ** invoke the callback function.
   */
   if( regRowCount ){
-    sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
+    sqlite3VdbeAddOp2(v, OP_ChngCntRow, regRowCount, 1);
     sqlite3VdbeSetNumCols(v, 1);
     sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
   }
diff --git a/src/parse.y b/src/parse.y
index faec4b5cf..591cde3b9 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -868,7 +868,7 @@ limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y).
 /////////////////////////// The DELETE statement /////////////////////////////
 //
 %if SQLITE_ENABLE_UPDATE_DELETE_LIMIT || SQLITE_UDL_CAPABLE_PARSER
-cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt(W) 
+cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt_ret(W)
         orderby_opt(O) limit_opt(L). {
   sqlite3SrcListIndexedBy(pParse, X, &I);
 #ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
@@ -881,7 +881,7 @@ cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt(W)
   sqlite3DeleteFrom(pParse,X,W,O,L);
 }
 %else
-cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt(W). {
+cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt_ret(W). {
   sqlite3SrcListIndexedBy(pParse, X, &I);
   sqlite3DeleteFrom(pParse,X,W,0,0);
 }
@@ -889,15 +889,23 @@ cmd ::= with DELETE FROM xfullname(X) indexed_opt(I) where_opt(W). {
 
 %type where_opt {Expr*}
 %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
+%type where_opt_ret {Expr*}
+%destructor where_opt_ret {sqlite3ExprDelete(pParse->db, $$);}
 
 where_opt(A) ::= .                    {A = 0;}
 where_opt(A) ::= WHERE expr(X).       {A = X;}
+where_opt_ret(A) ::= .                                      {A = 0;}
+where_opt_ret(A) ::= WHERE expr(X).                         {A = X;}
+where_opt_ret(A) ::= RETURNING selcollist(X).               
+       {sqlite3AddReturning(pParse,X); A = 0;}
+where_opt_ret(A) ::= WHERE expr(X) RETURNING selcollist(Y).
+       {sqlite3AddReturning(pParse,Y); A = X;}
 
 ////////////////////////// The UPDATE command ////////////////////////////////
 //
 %if SQLITE_ENABLE_UPDATE_DELETE_LIMIT || SQLITE_UDL_CAPABLE_PARSER
 cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) from(F)
-        where_opt(W) orderby_opt(O) limit_opt(L).  {
+        where_opt_ret(W) orderby_opt(O) limit_opt(L).  {
   sqlite3SrcListIndexedBy(pParse, X, &I);
   X = sqlite3SrcListAppendList(pParse, X, F);
   sqlite3ExprListCheckLength(pParse,Y,"set list"); 
@@ -912,7 +920,7 @@ cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) from(F)
 }
 %else
 cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) from(F)
-        where_opt(W). {
+        where_opt_ret(W). {
   sqlite3SrcListIndexedBy(pParse, X, &I);
   sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   X = sqlite3SrcListAppendList(pParse, X, F);
@@ -946,7 +954,7 @@ cmd ::= with insert_cmd(R) INTO xfullname(X) idlist_opt(F) select(S)
         upsert(U). {
   sqlite3Insert(pParse, X, S, F, R, U);
 }
-cmd ::= with insert_cmd(R) INTO xfullname(X) idlist_opt(F) DEFAULT VALUES.
+cmd ::= with insert_cmd(R) INTO xfullname(X) idlist_opt(F) DEFAULT VALUES returning.
 {
   sqlite3Insert(pParse, X, 0, F, R, 0);
 }
@@ -959,16 +967,20 @@ cmd ::= with insert_cmd(R) INTO xfullname(X) idlist_opt(F) DEFAULT VALUES.
 // avoid unreachable code.
 //%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
 upsert(A) ::= . { A = 0; }
+upsert(A) ::= RETURNING selcollist(X).  { A = 0; sqlite3AddReturning(pParse,X); }
 upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW)
               DO UPDATE SET setlist(Z) where_opt(W) upsert(N).
               { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W,N);}
 upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING upsert(N).
               { A = sqlite3UpsertNew(pParse->db,T,TW,0,0,N); }
-upsert(A) ::= ON CONFLICT DO NOTHING.
+upsert(A) ::= ON CONFLICT DO NOTHING returning.
               { A = sqlite3UpsertNew(pParse->db,0,0,0,0,0); }
-upsert(A) ::= ON CONFLICT DO UPDATE SET setlist(Z) where_opt(W).
+upsert(A) ::= ON CONFLICT DO UPDATE SET setlist(Z) where_opt(W) returning.
               { A = sqlite3UpsertNew(pParse->db,0,0,Z,W,0);}
 
+returning ::= RETURNING selcollist(X).  {sqlite3AddReturning(pParse,X);}
+returning ::= .
+
 %type insert_cmd {int}
 insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
 insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
diff --git a/src/resolve.c b/src/resolve.c
index b55bdc418..5074a2881 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -371,23 +371,26 @@ static int lookupName(
     ** it is a new.* or old.* trigger argument reference.  Or
     ** maybe it is an excluded.* from an upsert.
     */
-    if( zDb==0 && zTab!=0 && cntTab==0 ){
+    if( zDb==0 && cntTab==0 ){
       pTab = 0;
 #ifndef SQLITE_OMIT_TRIGGER
       if( pParse->pTriggerTab!=0 ){
         int op = pParse->eTriggerOp;
         assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT );
-        if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){
+        if( op!=TK_DELETE && zTab && sqlite3StrICmp("new",zTab) == 0 ){
           pExpr->iTable = 1;
           pTab = pParse->pTriggerTab;
-        }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){
+        }else if( op!=TK_INSERT && zTab && sqlite3StrICmp("old",zTab)==0 ){
           pExpr->iTable = 0;
           pTab = pParse->pTriggerTab;
+        }else if( pParse->bReturning ){
+          pExpr->iTable = op!=TK_DELETE;
+          pTab = pParse->pTriggerTab;
         }
       }
 #endif /* SQLITE_OMIT_TRIGGER */
 #ifndef SQLITE_OMIT_UPSERT
-      if( (pNC->ncFlags & NC_UUpsert)!=0 ){
+      if( (pNC->ncFlags & NC_UUpsert)!=0 && ALWAYS(zTab) ){
         Upsert *pUpsert = pNC->uNC.pUpsert;
         if( pUpsert && sqlite3StrICmp("excluded",zTab)==0 ){
           pTab = pUpsert->pUpsertSrc->a[0].pTab;
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index c17d01200..8a9470f01 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -2115,7 +2115,13 @@ struct sqlite3_mem_methods {
 ** The second parameter is a pointer to an integer into which
 ** is written 0 or 1 to indicate whether triggers are disabled or enabled
 ** following this call.  The second parameter may be a NULL pointer, in
-** which case the trigger setting is not reported back. </dd>
+** which case the trigger setting is not reported back.
+**
+** <p>Originally this option disabled all triggers.  ^(However, since
+** SQLite version 3.35.0, TEMP triggers are still allowed even if
+** this option is off.  So, in other words, this option now only disables
+** triggers in the main database schema or in the schemas of ATTACH-ed
+** databases.)^ </dd>
 **
 ** [[SQLITE_DBCONFIG_ENABLE_VIEW]]
 ** <dt>SQLITE_DBCONFIG_ENABLE_VIEW</dt>
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 510aab1ca..3cadb7f53 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1159,6 +1159,7 @@ typedef struct ParseCleanup ParseCleanup;
 typedef struct PreUpdate PreUpdate;
 typedef struct PrintfArguments PrintfArguments;
 typedef struct RenameToken RenameToken;
+typedef struct Returning Returning;
 typedef struct RowSet RowSet;
 typedef struct Savepoint Savepoint;
 typedef struct Select Select;
@@ -3429,6 +3430,7 @@ struct Parse {
   u32 oldmask;         /* Mask of old.* columns referenced */
   u32 newmask;         /* Mask of new.* columns referenced */
   u8 eTriggerOp;       /* TK_UPDATE, TK_INSERT or TK_DELETE */
+  u8 bReturning;       /* Coding a RETURNING trigger */
   u8 eOrconf;          /* Default ON CONFLICT policy for trigger steps */
   u8 disableTriggers;  /* True to disable triggers */
 
@@ -3578,6 +3580,7 @@ struct Trigger {
   char *table;            /* The table or view to which the trigger applies */
   u8 op;                  /* One of TK_DELETE, TK_UPDATE, TK_INSERT         */
   u8 tr_tm;               /* One of TRIGGER_BEFORE, TRIGGER_AFTER */
+  u8 bReturning;          /* This trigger implements a RETURNING clause */
   Expr *pWhen;            /* The WHEN clause of the expression (may be NULL) */
   IdList *pColumns;       /* If this is an UPDATE OF <column-list> trigger,
                              the <column-list> is stored here */
@@ -3636,7 +3639,8 @@ struct Trigger {
  *
  */
 struct TriggerStep {
-  u8 op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
+  u8 op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT,
+                       ** or TK_RETURNING */
   u8 orconf;           /* OE_Rollback etc. */
   Trigger *pTrig;      /* The trigger that this step is a part of */
   Select *pSelect;     /* SELECT statement or RHS of INSERT INTO SELECT ... */
@@ -3652,6 +3656,18 @@ struct TriggerStep {
 };
 
 /*
+** Information about a RETURNING clause
+*/
+struct Returning {
+  Parse *pParse;        /* The parse that includes the RETURNING clause */
+  ExprList *pReturnEL;  /* List of expressions to return */
+  Trigger retTrig;      /* The transient trigger that implements RETURNING */
+  TriggerStep retTStep; /* The trigger step */
+  Select retSel;        /* The SELECT statement that implements RETURNING */
+  u64 retSrcList;       /* The empty FROM clause of the SELECT */
+};
+
+/*
 ** An objected used to accumulate the text of a string where we
 ** do not necessarily know how big the string will be in the end.
 */
@@ -4252,6 +4268,7 @@ void sqlite3AddDefaultValue(Parse*,Expr*,const char*,const char*);
 void sqlite3AddCollateType(Parse*, Token*);
 void sqlite3AddGenerated(Parse*,Expr*,Token*);
 void sqlite3EndTable(Parse*,Token*,Token*,u8,Select*);
+void sqlite3AddReturning(Parse*,ExprList*);
 int sqlite3ParseUri(const char*,const char*,unsigned int*,
                     sqlite3_vfs**,char**,char **);
 #define sqlite3CodecQueryParameters(A,B,C) 0
diff --git a/src/trigger.c b/src/trigger.c
index a9378fd3a..ef0a90b24 100644
--- a/src/trigger.c
+++ b/src/trigger.c
@@ -65,11 +65,16 @@ Trigger *sqlite3TriggerList(Parse *pParse, Table *pTab){
     while( p ){
       Trigger *pTrig = (Trigger *)sqliteHashData(p);
       if( pTrig->pTabSchema==pTab->pSchema
-       && 0==sqlite3StrICmp(pTrig->table, pTab->zName) 
+       && 0==sqlite3StrICmp(pTrig->table, pTab->zName)
       ){
         pTrig->pNext = pList;
         pList = pTrig;
-      }
+      }else if( pTrig->op==TK_RETURNING ){
+        pTrig->table = pTab->zName;
+        pTrig->pTabSchema = pTab->pSchema;
+        pTrig->pNext = pList;
+        pList = pTrig;
+      }        
       p = sqliteHashNext(p);    
     }
   }
@@ -562,7 +567,7 @@ TriggerStep *sqlite3TriggerDeleteStep(
 ** Recursively delete a Trigger structure
 */
 void sqlite3DeleteTrigger(sqlite3 *db, Trigger *pTrigger){
-  if( pTrigger==0 ) return;
+  if( pTrigger==0 || pTrigger->bReturning ) return;
   sqlite3DeleteTriggerStep(db, pTrigger->step_list);
   sqlite3DbFree(db, pTrigger->zName);
   sqlite3DbFree(db, pTrigger->table);
@@ -727,15 +732,48 @@ Trigger *sqlite3TriggersExist(
   Trigger *pList = 0;
   Trigger *p;
 
-  if( (pParse->db->flags & SQLITE_EnableTrigger)!=0 ){
-    pList = sqlite3TriggerList(pParse, pTab);
-  }
-  assert( pList==0 || IsVirtual(pTab)==0 );
-  for(p=pList; p; p=p->pNext){
-    if( p->op==op && checkColumnOverlap(p->pColumns, pChanges) ){
-      mask |= p->tr_tm;
+  pList = sqlite3TriggerList(pParse, pTab);
+  assert( pList==0 || IsVirtual(pTab)==0 
+           || (pList->bReturning && pList->pNext==0) );
+  if( pList!=0 ){
+    p = pList;
+    if( (pParse->db->flags & SQLITE_EnableTrigger)==0
+     && pTab->pTrigger!=0
+    ){
+      /* The SQLITE_DBCONFIG_ENABLE_TRIGGER setting is off.  That means that
+      ** only TEMP triggers are allowed.  Truncate the pList so that it
+      ** includes only TEMP triggers */
+      if( pList==pTab->pTrigger ){
+        pList = 0;
+        goto exit_triggers_exist;
+      }
+      while( ALWAYS(p->pNext) && p->pNext!=pTab->pTrigger ) p = p->pNext;
+      p->pNext = 0;
+      p = pList;
     }
+    do{
+      if( p->op==op && checkColumnOverlap(p->pColumns, pChanges) ){
+        mask |= p->tr_tm;
+      }else if( p->op==TK_RETURNING ){
+        /* The first time a RETURNING trigger is seen, the "op" value tells
+        ** us what time of trigger it should be. */
+        assert( sqlite3IsToplevel(pParse) );
+        p->op = op;
+        mask |= TRIGGER_AFTER;
+        if( IsVirtual(pTab) && op!=TK_INSERT ){
+          sqlite3ErrorMsg(pParse,
+             "%s RETURNING is not available on virtual tables",
+             op==TK_DELETE ? "DELETE" : "UPDATE");
+        }
+      }else if( p->bReturning && p->op==TK_INSERT && op==TK_UPDATE
+                && sqlite3IsToplevel(pParse) ){
+        /* Also fire a RETURNING trigger for an UPSERT */
+        mask |= TRIGGER_AFTER;
+      }
+      p = p->pNext;
+    }while( p );
   }
+exit_triggers_exist:
   if( pMask ){
     *pMask = mask;
   }
@@ -778,6 +816,47 @@ SrcList *sqlite3TriggerStepSrc(
   return pSrc;
 }
 
+/* The input list pList is the list of result set terms from a RETURNING
+** clause.  The table that we are returning from is pTab.
+**
+** This routine makes a copy of the pList, and at the same time expands
+** any "*" wildcards to be the complete set of columns from pTab.
+*/
+static ExprList *sqlite3ExpandReturning(
+  Parse *pParse,        /* Parsing context */
+  ExprList *pList,      /* The arguments to RETURNING */
+  Table *pTab           /* The table being updated */
+){
+  ExprList *pNew = 0;
+  sqlite3 *db = pParse->db;
+  int i;
+  for(i=0; i<pList->nExpr; i++){
+    Expr *pOldExpr = pList->a[i].pExpr;
+    if( ALWAYS(pOldExpr!=0) && pOldExpr->op==TK_ASTERISK ){
+      int jj;
+      for(jj=0; jj<pTab->nCol; jj++){
+        if( IsHiddenColumn(pTab->aCol+jj) ) continue;
+        Expr *pNewExpr = sqlite3Expr(db, TK_ID, pTab->aCol[jj].zName);
+        pNew = sqlite3ExprListAppend(pParse, pNew, pNewExpr);
+        if( !db->mallocFailed ){
+          struct ExprList_item *pItem = &pNew->a[pNew->nExpr-1];
+          pItem->zEName = sqlite3DbStrDup(db, pTab->aCol[jj].zName);
+          pItem->eEName = ENAME_NAME;
+        }
+      }
+    }else{
+      Expr *pNewExpr = sqlite3ExprDup(db, pOldExpr, 0);
+      pNew = sqlite3ExprListAppend(pParse, pNew, pNewExpr);
+      if( !db->mallocFailed && ALWAYS(pList->a[i].zEName!=0) ){
+        struct ExprList_item *pItem = &pNew->a[pNew->nExpr-1];
+        pItem->zEName = sqlite3DbStrDup(db, pList->a[i].zEName);
+        pItem->eEName = pList->a[i].eEName;
+      }
+    }
+  }
+  return pNew;
+}
+
 /*
 ** Generate VDBE code for the statements inside the body of a single 
 ** trigger.
@@ -827,6 +906,7 @@ static int codeTriggerProgram(
           sqlite3ExprDup(db, pStep->pWhere, 0), 
           pParse->eOrconf, 0, 0, 0
         );
+        sqlite3VdbeAddOp0(v, OP_ResetCount);
         break;
       }
       case TK_INSERT: {
@@ -837,6 +917,7 @@ static int codeTriggerProgram(
           pParse->eOrconf,
           sqlite3UpsertDup(db, pStep->pUpsert)
         );
+        sqlite3VdbeAddOp0(v, OP_ResetCount);
         break;
       }
       case TK_DELETE: {
@@ -844,9 +925,10 @@ static int codeTriggerProgram(
           sqlite3TriggerStepSrc(pParse, pStep),
           sqlite3ExprDup(db, pStep->pWhere, 0), 0, 0
         );
+        sqlite3VdbeAddOp0(v, OP_ResetCount);
         break;
       }
-      default: assert( pStep->op==TK_SELECT ); {
+      case TK_SELECT: {
         SelectDest sDest;
         Select *pSelect = sqlite3SelectDup(db, pStep->pSelect, 0);
         sqlite3SelectDestInit(&sDest, SRT_Discard, 0);
@@ -854,10 +936,27 @@ static int codeTriggerProgram(
         sqlite3SelectDelete(db, pSelect);
         break;
       }
+      default: assert( pStep->op==TK_RETURNING ); {
+        Select *pSelect = pStep->pSelect;
+        ExprList *pList = pSelect->pEList;
+        SelectDest sDest;
+        Select *pNew;
+        pSelect->pEList =
+           sqlite3ExpandReturning(pParse, pList, pParse->pTriggerTab);
+        sqlite3SelectDestInit(&sDest, SRT_Output, 0);
+        pNew = sqlite3SelectDup(db, pSelect, 0);
+        if( pNew ){
+          sqlite3Select(pParse, pNew, &sDest);
+          if( pNew->selFlags & (SF_Aggregate|SF_HasAgg|SF_WinRewrite) ){
+            sqlite3ErrorMsg(pParse, "aggregates not allowed in RETURNING");
+          }
+          sqlite3SelectDelete(db, pNew);
+        }
+        sqlite3ExprListDelete(db, pSelect->pEList);
+        pStep->pSelect->pEList = pList;
+        break;
+      }
     } 
-    if( pStep->op!=TK_SELECT ){
-      sqlite3VdbeAddOp0(v, OP_ResetCount);
-    }
   }
 
   return 0;
@@ -947,6 +1046,7 @@ static TriggerPrg *codeRowTrigger(
   pSubParse->pToplevel = pTop;
   pSubParse->zAuthContext = pTrigger->zName;
   pSubParse->eTriggerOp = pTrigger->op;
+  pSubParse->bReturning = pTrigger->bReturning;
   pSubParse->nQueryLoop = pParse->nQueryLoop;
   pSubParse->disableVtab = pParse->disableVtab;
 
@@ -996,6 +1096,9 @@ static TriggerPrg *codeRowTrigger(
     if( db->mallocFailed==0 && pParse->nErr==0 ){
       pProgram->aOp = sqlite3VdbeTakeOpArray(v, &pProgram->nOp, &pTop->nMaxArg);
     }
+    if( pTrigger->bReturning ){
+      sqlite3VdbeColumnInfoXfer(sqlite3ParseToplevel(pParse)->pVdbe, v);
+    }
     pProgram->nMem = pSubParse->nMem;
     pProgram->nCsr = pSubParse->nTab;
     pProgram->token = (void *)pTrigger;
@@ -1150,12 +1253,20 @@ void sqlite3CodeRowTrigger(
     assert( p->pSchema==p->pTabSchema 
          || p->pSchema==pParse->db->aDb[1].pSchema );
 
-    /* Determine whether we should code this trigger */
-    if( p->op==op 
+    /* Determine whether we should code this trigger.  One of two choices:
+    **   1. The trigger is an exact match to the current DML statement
+    **   2. This is a RETURNING trigger for INSERT but we are currently
+    **      doing the UPDATE part of an UPSERT.
+    */
+    if( (p->op==op || (p->bReturning && p->op==TK_INSERT && op==TK_UPDATE))
      && p->tr_tm==tr_tm 
      && checkColumnOverlap(p->pColumns, pChanges)
+     && (sqlite3IsToplevel(pParse) || !p->bReturning)
     ){
+      u8 origOp = p->op;
+      p->op = op;
       sqlite3CodeRowTriggerDirect(pParse, p, pTab, reg, orconf, ignoreJump);
+      p->op = origOp;
     }
   }
 }
diff --git a/src/update.c b/src/update.c
index f8cb2afed..b360766b6 100644
--- a/src/update.c
+++ b/src/update.c
@@ -643,6 +643,7 @@ void sqlite3Update(
   if( (db->flags&SQLITE_CountRows)!=0
    && !pParse->pTriggerTab
    && !pParse->nested
+   && !pParse->bReturning
    && pUpsert==0
   ){
     regRowCount = ++pParse->nMem;
@@ -1106,7 +1107,7 @@ void sqlite3Update(
   ** that information.
   */
   if( regRowCount ){
-    sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
+    sqlite3VdbeAddOp2(v, OP_ChngCntRow, regRowCount, 1);
     sqlite3VdbeSetNumCols(v, 1);
     sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC);
   }
diff --git a/src/vdbe.c b/src/vdbe.c
index 3a00515e5..bb1827789 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -1445,6 +1445,26 @@ case OP_IntCopy: {            /* out2 */
   break;
 }
 
+/* Opcode: ChngCntRow P1 P2 * * *
+** Synopsis: output=r[P1]
+**
+** Output value in register P1 as the chance count for a DML statement,
+** due to the "PRAGMA count_changes=ON" setting.  Or, if there was a
+** foreign key error in the statement, trigger the error now.
+**
+** This opcode is a variant of OP_ResultRow that checks the foreign key
+** immediate constraint count and throws an error if the count is
+** non-zero.  The P2 opcode must be 1.
+*/
+case OP_ChngCntRow: {
+  assert( pOp->p2==1 );
+  if( (rc = sqlite3VdbeCheckFk(p,0))!=SQLITE_OK ){
+    goto abort_due_to_error;
+  }
+  /* Fall through to the next case, OP_String */
+  /* no break */ deliberate_fall_through
+}
+
 /* Opcode: ResultRow P1 P2 * * *
 ** Synopsis: output=r[P1@P2]
 **
@@ -1461,34 +1481,6 @@ case OP_ResultRow: {
   assert( pOp->p1>0 );
   assert( pOp->p1+pOp->p2<=(p->nMem+1 - p->nCursor)+1 );
 
-  /* If this statement has violated immediate foreign key constraints, do
-  ** not return the number of rows modified. And do not RELEASE the statement
-  ** transaction. It needs to be rolled back.  */
-  if( SQLITE_OK!=(rc = sqlite3VdbeCheckFk(p, 0)) ){
-    assert( db->flags&SQLITE_CountRows );
-    assert( p->usesStmtJournal );
-    goto abort_due_to_error;
-  }
-
-  /* If the SQLITE_CountRows flag is set in sqlite3.flags mask, then 
-  ** DML statements invoke this opcode to return the number of rows 
-  ** modified to the user. This is the only way that a VM that
-  ** opens a statement transaction may invoke this opcode.
-  **
-  ** In case this is such a statement, close any statement transaction
-  ** opened by this VM before returning control to the user. This is to
-  ** ensure that statement-transactions are always nested, not overlapping.
-  ** If the open statement-transaction is not closed here, then the user
-  ** may step another VM that opens its own statement transaction. This
-  ** may lead to overlapping statement transactions.
-  **
-  ** The statement transaction is never a top-level transaction.  Hence
-  ** the RELEASE call below can never fail.
-  */
-  assert( p->iStatement==0 || db->flags&SQLITE_CountRows );
-  rc = sqlite3VdbeCloseStatement(p, SAVEPOINT_RELEASE);
-  assert( rc==SQLITE_OK );
-
   /* Invalidate all ephemeral cursor row caches */
   p->cacheCtr = (p->cacheCtr + 2)|1;
 
diff --git a/src/vdbe.h b/src/vdbe.h
index 17f11fdd7..48be53df7 100644
--- a/src/vdbe.h
+++ b/src/vdbe.h
@@ -259,6 +259,7 @@ void sqlite3VdbeResetStepResult(Vdbe*);
 void sqlite3VdbeRewind(Vdbe*);
 int sqlite3VdbeReset(Vdbe*);
 void sqlite3VdbeSetNumCols(Vdbe*,int);
+void sqlite3VdbeColumnInfoXfer(Vdbe*,Vdbe*);
 int sqlite3VdbeSetColName(Vdbe*, int, int, const char *, void(*)(void*));
 void sqlite3VdbeCountChanges(Vdbe*);
 sqlite3 *sqlite3VdbeDb(Vdbe*);
diff --git a/src/vdbeaux.c b/src/vdbeaux.c
index 7b9b79205..c7c212575 100644
--- a/src/vdbeaux.c
+++ b/src/vdbeaux.c
@@ -2596,6 +2596,23 @@ void sqlite3VdbeSetNumCols(Vdbe *p, int nResColumn){
 }
 
 /*
+** Transfer the column count and name information from one Vdbe to
+** another.
+*/
+void sqlite3VdbeColumnInfoXfer(Vdbe *pTo, Vdbe *pFrom){
+  sqlite3 *db = pTo->db;
+  assert( db==pFrom->db );
+  if( pTo->nResColumn ){
+    releaseMemArray(pTo->aColName, pTo->nResColumn*COLNAME_N);
+    sqlite3DbFree(db, pTo->aColName);
+  }
+  pTo->aColName = pFrom->aColName;
+  pFrom->aColName = 0;
+  pTo->nResColumn = pFrom->nResColumn;
+  pFrom->nResColumn = 0;
+}
+
+/*
 ** Set the name of the idx'th column to be returned by the SQL statement.
 ** zName must be a pointer to a nul terminated string.
 **