/ Check-in [02fbdbc7]
Login

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

Overview
Comment:Omit unnecessary CHECK constraints in UPDATE statements, when none of the columns referenced in the CHECK constraint are modified.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:02fbdbc782dd98f080bf4482d820f36c0ef3d519
User & Date: drh 2016-02-10 16:52:24
Context
2016-02-10
18:24
Fix to previous check-in: Make sure CHECK constraints involving the ROWID are not ignored when the ROWID changes. check-in: 7782cb1d user: drh tags: trunk
16:52
Omit unnecessary CHECK constraints in UPDATE statements, when none of the columns referenced in the CHECK constraint are modified. check-in: 02fbdbc7 user: drh tags: trunk
16:03
Omit NOT NULL checks on unchanging columns in an UPDATE. check-in: 6a3aaedf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1072
1073
1074
1075
1076
1077
1078
































1079
1080
1081
1082
1083
1084
1085
....
1271
1272
1273
1274
1275
1276
1277


1278
1279
1280
1281
1282
1283
1284
1285
#endif
#ifdef pTrigger
 #undef pTrigger
#endif
#ifdef tmask
 #undef tmask
#endif

































/*
** Generate code to do constraint checks prior to an INSERT or an UPDATE
** on table pTab.
**
** The regNewData parameter is the first register in a range that contains
** the data to be inserted or the data after the update.  There will be
................................................................................
#ifndef SQLITE_OMIT_CHECK
  if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
    ExprList *pCheck = pTab->pCheck;
    pParse->ckBase = regNewData+1;
    onError = overrideError!=OE_Default ? overrideError : OE_Abort;
    for(i=0; i<pCheck->nExpr; i++){
      int allOk = sqlite3VdbeMakeLabel(v);


      sqlite3ExprIfTrue(pParse, pCheck->a[i].pExpr, allOk, SQLITE_JUMPIFNULL);
      if( onError==OE_Ignore ){
        sqlite3VdbeGoto(v, ignoreDest);
      }else{
        char *zName = pCheck->a[i].zName;
        if( zName==0 ) zName = pTab->zName;
        if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */
        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,







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







 







>
>
|







1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
....
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
#endif
#ifdef pTrigger
 #undef pTrigger
#endif
#ifdef tmask
 #undef tmask
#endif

/* This is the Walker callback from checkConstraintUnchanged().  Set
** pWalker->eCode to 0 if this expression node references any of the
** columns that are being modifed by an UPDATE statement.
*/
static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_COLUMN
   && pExpr->iColumn>=0
   && pWalker->u.aiCol[pExpr->iColumn]>=0
  ){
    pWalker->eCode = 0;
  }
  return WRC_Continue;
}

/*
** pExpr is a CHECK constraint on a row that is being UPDATE-ed.  The
** only columns that are modified by the UPDATE are those for which
** aiChng[i]>=0.  Return true if CHECK constraint pExpr does not use
** any of the changing columns.  In other words, return true if this
** CHECK constraint can be skipped when validating the new row in
** the UPDATE statement.
*/
static int checkConstraintUnchanged(Expr *pExpr, int *aiChng){
  Walker w;
  memset(&w, 0, sizeof(w));
  w.eCode = 1;
  w.xExprCallback = checkConstraintExprNode;
  w.u.aiCol = aiChng;
  sqlite3WalkExpr(&w, pExpr);
  return w.eCode;
}

/*
** Generate code to do constraint checks prior to an INSERT or an UPDATE
** on table pTab.
**
** The regNewData parameter is the first register in a range that contains
** the data to be inserted or the data after the update.  There will be
................................................................................
#ifndef SQLITE_OMIT_CHECK
  if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
    ExprList *pCheck = pTab->pCheck;
    pParse->ckBase = regNewData+1;
    onError = overrideError!=OE_Default ? overrideError : OE_Abort;
    for(i=0; i<pCheck->nExpr; i++){
      int allOk = sqlite3VdbeMakeLabel(v);
      Expr *pExpr = pCheck->a[i].pExpr;
      if( aiChng && checkConstraintUnchanged(pExpr, aiChng) ) continue;
      sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL);
      if( onError==OE_Ignore ){
        sqlite3VdbeGoto(v, ignoreDest);
      }else{
        char *zName = pCheck->a[i].zName;
        if( zName==0 ) zName = pTab->zName;
        if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */
        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,

Changes to src/sqliteInt.h.

3096
3097
3098
3099
3100
3101
3102

3103
3104
3105
3106
3107
3108
3109
  union {                                   /* Extra data for callback */
    NameContext *pNC;                          /* Naming context */
    int n;                                     /* A counter */
    int iCur;                                  /* A cursor number */
    SrcList *pSrcList;                         /* FROM clause */
    struct SrcCount *pSrcCount;                /* Counting column references */
    struct CCurHint *pCCurHint;                /* Used by codeCursorHint() */

  } u;
};

/* Forward declarations */
int sqlite3WalkExpr(Walker*, Expr*);
int sqlite3WalkExprList(Walker*, ExprList*);
int sqlite3WalkSelect(Walker*, Select*);







>







3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
  union {                                   /* Extra data for callback */
    NameContext *pNC;                          /* Naming context */
    int n;                                     /* A counter */
    int iCur;                                  /* A cursor number */
    SrcList *pSrcList;                         /* FROM clause */
    struct SrcCount *pSrcCount;                /* Counting column references */
    struct CCurHint *pCCurHint;                /* Used by codeCursorHint() */
    int *aiCol;                                /* array of column indexes */
  } u;
};

/* Forward declarations */
int sqlite3WalkExpr(Walker*, Expr*);
int sqlite3WalkExprList(Walker*, ExprList*);
int sqlite3WalkSelect(Walker*, Select*);