SQLite

Check-in [9ea6bcc8]
Login

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

Overview
Comment:If a RETURNING clause contains a subquery that references the table that is being updated in the statement that includes the RETURNING clause, then mark the subquery as correlated so that it is recomputed for each result and not just computed once and reused. See forum post 2c83569ce8945d39.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9ea6bcc8fdf6aadb756ec5bcaaa7af314167f8973bdd32fd23f83bd964f0c21e
User & Date: drh 2024-04-24 16:36:37
Original Comment: If a RETURNING clause contains a subquery that references the table that is being updated in the statement that includes the RETURNING clause, then mark the subquery as correlated sot hat it is recomputed for each result and not just computed once and reused. See forum post 2c83569ce8945d39.
Context
2024-04-25
06:52
wasm: add a makefile comment about the static sqlite3.h/c version info injected into the JS files possibly differing from the runtime-emited version info when a user provides their own sqlite3.c. (check-in: d99a01a0 user: stephan tags: trunk)
2024-04-24
19:49
An experimental minor tweak to the way affinities are computed for compound subqueries, when the actual affinity is ambiguous. (check-in: 779723ad user: drh tags: compound-subquery-affinity)
16:36
If a RETURNING clause contains a subquery that references the table that is being updated in the statement that includes the RETURNING clause, then mark the subquery as correlated so that it is recomputed for each result and not just computed once and reused. See forum post 2c83569ce8945d39. (check-in: 9ea6bcc8 user: drh tags: trunk)
14:18
Fix a case where a corrupt stat4 record could go unrecognized due to integer overflow. (check-in: 240a4a48 user: dan tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/trigger.c.

946
947
948
949
950
951
952


































































953
954
955
956
957
958
959
        pItem->zEName = sqlite3DbStrDup(db, pList->a[i].zEName);
        pItem->fg.eEName = pList->a[i].fg.eEName;
      }
    }
  }
  return pNew;
}



































































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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
        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(
1010
1011
1012
1013
1014
1015
1016

1017
1018
1019
1020
1021
1022
1023
    pParse->pTriggerTab = pTab;
    if( sqlite3ResolveExprListNames(&sNC, pNew)==SQLITE_OK
     && ALWAYS(!db->mallocFailed)
    ){
      int i;
      int nCol = pNew->nExpr;
      int reg = pParse->nMem+1;

      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 ){







>







1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
    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 ){

Changes to test/returning1.test.

455
456
457
458
459
460
461




























































462
463
} {}
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 {}}





























































finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
} {}
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