Optimization: when doing an UPDATE on a table with indexes on an expression,

do not update the expression indexes if they do not refer to any of the
columns of the table being updated.

FossilOrigin-Name: a71b101635ed28a4c99734dabb20bd65ef1018c1d63ac143b7321cdb0fafa5d7

1 files changed, 25 insertions, 9 deletions

diff --git a/src/insert.c b/src/insert.c
index 12a6bb805..d110ab763 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -1126,14 +1126,15 @@ insert_cleanup:
 #endif
 
 /*
-** Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
+** Meanings of bits in of pWalker->eCode for 
+** sqlite3ExprReferencesUpdatedColumn()
 */
 #define CKCNSTRNT_COLUMN   0x01    /* CHECK constraint uses a changing column */
 #define CKCNSTRNT_ROWID    0x02    /* CHECK constraint references the ROWID */
 
-/* This is the Walker callback from checkConstraintUnchanged().  Set
-** bit 0x01 of pWalker->eCode if
-** pWalker->eCode to 0 if this expression node references any of the
+/* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
+*  Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
+** expression node references any of the
 ** columns that are being modifed by an UPDATE statement.
 */
 static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
@@ -1155,12 +1156,21 @@ static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
 ** only columns that are modified by the UPDATE are those for which
 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
 **
-** Return true if CHECK constraint pExpr does not use any of the
+** Return true if CHECK constraint pExpr uses any of the
 ** changing columns (or the rowid if it is changing).  In other words,
-** return true if this CHECK constraint can be skipped when validating
+** return true if this CHECK constraint must be validated for
 ** the new row in the UPDATE statement.
+**
+** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
+** The operation of this routine is the same - return true if an only if
+** the expression uses one or more of columns identified by the second and
+** third arguments.
 */
-static int checkConstraintUnchanged(Expr *pExpr, int *aiChng, int chngRowid){
+int sqlite3ExprReferencesUpdatedColumn(
+  Expr *pExpr,    /* The expression to be checked */
+  int *aiChng,    /* aiChng[x]>=0 if column x changed by the UPDATE */
+  int chngRowid   /* True if UPDATE changes the rowid */
+){
   Walker w;
   memset(&w, 0, sizeof(w));
   w.eCode = 0;
@@ -1175,7 +1185,7 @@ static int checkConstraintUnchanged(Expr *pExpr, int *aiChng, int chngRowid){
   testcase( w.eCode==CKCNSTRNT_COLUMN );
   testcase( w.eCode==CKCNSTRNT_ROWID );
   testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
-  return !w.eCode;
+  return w.eCode!=0;
 }
 
 /*
@@ -1381,7 +1391,13 @@ void sqlite3GenerateConstraintChecks(
     for(i=0; i<pCheck->nExpr; i++){
       int allOk;
       Expr *pExpr = pCheck->a[i].pExpr;
-      if( aiChng && checkConstraintUnchanged(pExpr, aiChng, pkChng) ) continue;
+      if( aiChng
+       && !sqlite3ExprReferencesUpdatedColumn(pExpr, aiChng, pkChng)
+      ){
+        /* The check constraints do not reference any of the columns being
+        ** updated so there is no point it verifying the check constraint */
+        continue;
+      }
       allOk = sqlite3VdbeMakeLabel(v);
       sqlite3VdbeVerifyAbortable(v, onError);
       sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL);