SQLite

        pItem->zEName = sqlite3DbStrDup(db, pList->a[i].zEName);
        pItem->fg.eEName = pList->a[i].fg.eEName;
      }
    }
  }
  return pNew;
}

/* If the Expr node is a subquery or an EXISTS operator or an IN operator that
** uses a subquery, and if the subquery is SF_Correlated, then mark the
** expression as EP_VarSelect.
*/
static int sqlite3ReturningSubqueryVarSelect(Walker *NotUsed, Expr *pExpr){
  UNUSED_PARAMETER(NotUsed);
  if( ExprUseXSelect(pExpr)
   && (pExpr->x.pSelect->selFlags & SF_Correlated)!=0
  ){
    testcase( ExprHasProperty(pExpr, EP_VarSelect) );
    ExprSetProperty(pExpr, EP_VarSelect);
  }
  return WRC_Continue;
}


/*
** If the SELECT references the table pWalker->u.pTab, then do two things:
**
**    (1) Mark the SELECT as as SF_Correlated.
**    (2) Set pWalker->eCode to non-zero so that the caller will know
**        that (1) has happened.
*/
static int sqlite3ReturningSubqueryCorrelated(Walker *pWalker, Select *pSelect){
  int i;
  SrcList *pSrc;
  assert( pSelect!=0 );
  pSrc = pSelect->pSrc;
  assert( pSrc!=0 );
  for(i=0; i<pSrc->nSrc; i++){
    if( pSrc->a[i].pTab==pWalker->u.pTab ){
      testcase( pSelect->selFlags & SF_Correlated );
      pSelect->selFlags |= SF_Correlated;
      pWalker->eCode = 1;
      break;
    }
  }
  return WRC_Continue;
}

/*
** Scan the expression list that is the argument to RETURNING looking
** for subqueries that depend on the table which is being modified in the
** statement that is hosting the RETURNING clause (pTab).  Mark all such
** subqueries as SF_Correlated.  If the subqueries are part of an
** expression, mark the expression as EP_VarSelect.
**
** https://sqlite.org/forum/forumpost/2c83569ce8945d39
*/
static void sqlite3ProcessReturningSubqueries(
  ExprList *pEList,
  Table *pTab
){
  Walker w;
  memset(&w, 0, sizeof(w));
  w.xExprCallback = sqlite3ExprWalkNoop;
  w.xSelectCallback = sqlite3ReturningSubqueryCorrelated;
  w.u.pTab = pTab;
  sqlite3WalkExprList(&w, pEList);
  if( w.eCode ){
    w.xExprCallback = sqlite3ReturningSubqueryVarSelect;
    w.xSelectCallback = sqlite3SelectWalkNoop;
    sqlite3WalkExprList(&w, pEList);
  }
}

/*
** Generate code for the RETURNING trigger.  Unlike other triggers
** that invoke a subprogram in the bytecode, the code for RETURNING
** is generated in-line.
*/
static void codeReturningTrigger(

    pParse->pTriggerTab = pTab;
    if( sqlite3ResolveExprListNames(&sNC, pNew)==SQLITE_OK
     && ALWAYS(!db->mallocFailed)
    ){
      int i;
      int nCol = pNew->nExpr;
      int reg = pParse->nMem+1;
      sqlite3ProcessReturningSubqueries(pNew, pTab);
      pParse->nMem += nCol+2;
      pReturning->iRetReg = reg;
      for(i=0; i<nCol; i++){
        Expr *pCol = pNew->a[i].pExpr;
        assert( pCol!=0 ); /* Due to !db->mallocFailed ~9 lines above */
        sqlite3ExprCodeFactorable(pParse, pCol, reg+i);
        if( sqlite3ExprAffinity(pCol)==SQLITE_AFF_REAL ){

} {}
do_catchsql_test 19.1 {
  CREATE TRIGGER IF NOT EXISTS r1 AFTER DELETE ON t1 BEGIN
    INSERT  INTO t1(a) VALUES (1) RETURNING FALSE;
    INSERT  INTO t1(a) VALUES (2) RETURNING TRUE;
  END;
} {0 {}}

# 2024-04-24
# https://sqlite.org/forum/forumpost/2c83569ce8945d39
#
# If the RETURNING clause includes subqueries that reference the
# table being modified, make sure that the subqueries are identified
# as correlated so that the results are recomputed after each step
# instead of being computed once and reused.
#
reset_db
db null N
do_execsql_test 20.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
  INSERT INTO t1 VALUES(1,10),(2,20),(3,30),(4,40),(6,60),(8,80);
  BEGIN;
  DELETE FROM t1 WHERE a<>3
    RETURNING a,
              (SELECT min(a) FROM t1),
              (SELECT max(a) FROM t1),
              (SELECT round(avg(a),2) FROM t1);
  ROLLBACK;
} {
  1 2 8 4.6
  2 3 8 5.25
  4 3 8 5.67
  6 3 8 5.5
  8 3 3 3.0
}
do_execsql_test 20.2 {
  BEGIN;
  DELETE FROM t1
    RETURNING a,
              (SELECT min(a) FROM t1),
              (SELECT max(a) FROM t1),
              (SELECT round(avg(a),2) FROM t1);
  ROLLBACK;
} {
  1 2 8 4.6
  2 3 8 5.25
  3 4 8 6.0
  4 6 8 7.0
  6 8 8 8.0
  8 N N N
}
do_execsql_test 20.3 {
  BEGIN;
  DELETE FROM t1
    RETURNING a,
              (SELECT min(t2.a)+t1.a*100 FROM t1 AS t2),
              (SELECT max(t2.a)+t1.a*100 FROM t1 AS t2),
              (SELECT round(avg(t2.a),2)+t1.a*100 FROM t1 AS t2);
  ROLLBACK;
} {
  1 102 108 104.6
  2 203 208 205.25
  3 304 308 306.0
  4 406 408 407.0
  6 608 608 608.0
  8 N N N
}

finish_test