/ Check-in [a71b1016]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a71b101635ed28a4c99734dabb20bd65ef1018c1d63ac143b7321cdb0fafa5d7
User & Date: drh 2018-09-15 21:38:48
Context
2018-09-15
21:43
Increase the version number to 3.26.0 as we start the next development cycle. check-in: 885f0f82 user: drh tags: trunk
21:38
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. check-in: a71b1016 user: drh tags: trunk
04:01
Version 3.25.0 check-in: b63af6c3 user: drh tags: trunk, release, version-3.25.0
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1122   1122    #undef pTrigger
  1123   1123   #endif
  1124   1124   #ifdef tmask
  1125   1125    #undef tmask
  1126   1126   #endif
  1127   1127   
  1128   1128   /*
  1129         -** Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
         1129  +** Meanings of bits in of pWalker->eCode for 
         1130  +** sqlite3ExprReferencesUpdatedColumn()
  1130   1131   */
  1131   1132   #define CKCNSTRNT_COLUMN   0x01    /* CHECK constraint uses a changing column */
  1132   1133   #define CKCNSTRNT_ROWID    0x02    /* CHECK constraint references the ROWID */
  1133   1134   
  1134         -/* This is the Walker callback from checkConstraintUnchanged().  Set
  1135         -** bit 0x01 of pWalker->eCode if
  1136         -** pWalker->eCode to 0 if this expression node references any of the
         1135  +/* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
         1136  +*  Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
         1137  +** expression node references any of the
  1137   1138   ** columns that are being modifed by an UPDATE statement.
  1138   1139   */
  1139   1140   static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
  1140   1141     if( pExpr->op==TK_COLUMN ){
  1141   1142       assert( pExpr->iColumn>=0 || pExpr->iColumn==-1 );
  1142   1143       if( pExpr->iColumn>=0 ){
  1143   1144         if( pWalker->u.aiCol[pExpr->iColumn]>=0 ){
................................................................................
  1151   1152   }
  1152   1153   
  1153   1154   /*
  1154   1155   ** pExpr is a CHECK constraint on a row that is being UPDATE-ed.  The
  1155   1156   ** only columns that are modified by the UPDATE are those for which
  1156   1157   ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
  1157   1158   **
  1158         -** Return true if CHECK constraint pExpr does not use any of the
         1159  +** Return true if CHECK constraint pExpr uses any of the
  1159   1160   ** changing columns (or the rowid if it is changing).  In other words,
  1160         -** return true if this CHECK constraint can be skipped when validating
         1161  +** return true if this CHECK constraint must be validated for
  1161   1162   ** the new row in the UPDATE statement.
         1163  +**
         1164  +** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
         1165  +** The operation of this routine is the same - return true if an only if
         1166  +** the expression uses one or more of columns identified by the second and
         1167  +** third arguments.
  1162   1168   */
  1163         -static int checkConstraintUnchanged(Expr *pExpr, int *aiChng, int chngRowid){
         1169  +int sqlite3ExprReferencesUpdatedColumn(
         1170  +  Expr *pExpr,    /* The expression to be checked */
         1171  +  int *aiChng,    /* aiChng[x]>=0 if column x changed by the UPDATE */
         1172  +  int chngRowid   /* True if UPDATE changes the rowid */
         1173  +){
  1164   1174     Walker w;
  1165   1175     memset(&w, 0, sizeof(w));
  1166   1176     w.eCode = 0;
  1167   1177     w.xExprCallback = checkConstraintExprNode;
  1168   1178     w.u.aiCol = aiChng;
  1169   1179     sqlite3WalkExpr(&w, pExpr);
  1170   1180     if( !chngRowid ){
................................................................................
  1171   1181       testcase( (w.eCode & CKCNSTRNT_ROWID)!=0 );
  1172   1182       w.eCode &= ~CKCNSTRNT_ROWID;
  1173   1183     }
  1174   1184     testcase( w.eCode==0 );
  1175   1185     testcase( w.eCode==CKCNSTRNT_COLUMN );
  1176   1186     testcase( w.eCode==CKCNSTRNT_ROWID );
  1177   1187     testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  1178         -  return !w.eCode;
         1188  +  return w.eCode!=0;
  1179   1189   }
  1180   1190   
  1181   1191   /*
  1182   1192   ** Generate code to do constraint checks prior to an INSERT or an UPDATE
  1183   1193   ** on table pTab.
  1184   1194   **
  1185   1195   ** The regNewData parameter is the first register in a range that contains
................................................................................
  1377   1387     if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
  1378   1388       ExprList *pCheck = pTab->pCheck;
  1379   1389       pParse->iSelfTab = -(regNewData+1);
  1380   1390       onError = overrideError!=OE_Default ? overrideError : OE_Abort;
  1381   1391       for(i=0; i<pCheck->nExpr; i++){
  1382   1392         int allOk;
  1383   1393         Expr *pExpr = pCheck->a[i].pExpr;
  1384         -      if( aiChng && checkConstraintUnchanged(pExpr, aiChng, pkChng) ) continue;
         1394  +      if( aiChng
         1395  +       && !sqlite3ExprReferencesUpdatedColumn(pExpr, aiChng, pkChng)
         1396  +      ){
         1397  +        /* The check constraints do not reference any of the columns being
         1398  +        ** updated so there is no point it verifying the check constraint */
         1399  +        continue;
         1400  +      }
  1385   1401         allOk = sqlite3VdbeMakeLabel(v);
  1386   1402         sqlite3VdbeVerifyAbortable(v, onError);
  1387   1403         sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL);
  1388   1404         if( onError==OE_Ignore ){
  1389   1405           sqlite3VdbeGoto(v, ignoreDest);
  1390   1406         }else{
  1391   1407           char *zName = pCheck->a[i].zName;

Changes to src/sqliteInt.h.

  3998   3998   int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
  3999   3999   int sqlite3IsRowid(const char*);
  4000   4000   void sqlite3GenerateRowDelete(
  4001   4001       Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8,int);
  4002   4002   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*, int);
  4003   4003   int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
  4004   4004   void sqlite3ResolvePartIdxLabel(Parse*,int);
         4005  +int sqlite3ExprReferencesUpdatedColumn(Expr*,int*,int);
  4005   4006   void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
  4006   4007                                        u8,u8,int,int*,int*,Upsert*);
  4007   4008   #ifdef SQLITE_ENABLE_NULL_TRIM
  4008   4009     void sqlite3SetMakeRecordP5(Vdbe*,Table*);
  4009   4010   #else
  4010   4011   # define sqlite3SetMakeRecordP5(A,B)
  4011   4012   #endif

Changes to src/update.c.

    74     74     }
    75     75   #ifndef SQLITE_OMIT_FLOATING_POINT
    76     76     if( pTab->aCol[i].affinity==SQLITE_AFF_REAL ){
    77     77       sqlite3VdbeAddOp1(v, OP_RealAffinity, iReg);
    78     78     }
    79     79   #endif
    80     80   }
           81  +
           82  +/*
           83  +** Check to see if column iCol of index pIdx references any of the
           84  +** columns defined by aXRef and chngRowid.  Return true if it does
           85  +** and false if not.
           86  +**
           87  +** The iCol-th column of pIdx will be an expression.
           88  +**
           89  +** aXRef[j] will be non-negative if column j of the original table is
           90  +** being updated.  chngRowid will be true if the rowid of the table is
           91  +** being updated.
           92  +*/
           93  +static int indexExprRefsUpdatedColumn(
           94  +  Index *pIdx,      /* The index containing the expression to analyze */
           95  +  int iCol,         /* Which column of the index is the expression */
           96  +  int *aXRef,       /* aXRef[j]>=0 if column j is being updated */
           97  +  int chngRowid     /* true if the rowid is being updated */
           98  +){
           99  +  assert( pIdx->aColExpr!=0 );
          100  +  assert( pIdx->aColExpr->a[iCol].pExpr!=0 );
          101  +  return sqlite3ExprReferencesUpdatedColumn(pIdx->aColExpr->a[iCol].pExpr,
          102  +                                            aXRef,chngRowid);
          103  +}
    81    104   
    82    105   /*
    83    106   ** Process an UPDATE statement.
    84    107   **
    85    108   **   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
    86    109   **          \_______/ \________/     \______/       \________________/
    87    110   *            onError   pTabList      pChanges             pWhere
................................................................................
   298    321     pTabList->a[0].colUsed = IsVirtual(pTab) ? ALLBITS : 0;
   299    322   
   300    323     hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey);
   301    324   
   302    325     /* There is one entry in the aRegIdx[] array for each index on the table
   303    326     ** being updated.  Fill in aRegIdx[] with a register number that will hold
   304    327     ** the key for accessing each index.
   305         -  **
   306         -  ** FIXME:  Be smarter about omitting indexes that use expressions.
   307    328     */
   308    329     for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
   309    330       int reg;
   310    331       if( chngKey || hasFK>1 || pIdx->pPartIdxWhere || pIdx==pPk ){
   311    332         reg = ++pParse->nMem;
   312    333         pParse->nMem += pIdx->nColumn;
   313    334       }else{
   314    335         reg = 0;
   315    336         for(i=0; i<pIdx->nKeyCol; i++){
   316    337           i16 iIdxCol = pIdx->aiColumn[i];
   317         -        if( iIdxCol<0 || aXRef[iIdxCol]>=0 ){
          338  +        if( (iIdxCol>=0 && aXRef[iIdxCol]>=0)
          339  +         || iIdxCol==XN_ROWID
          340  +         || (iIdxCol==XN_EXPR
          341  +             && indexExprRefsUpdatedColumn(pIdx,i,aXRef,chngRowid))
          342  +        ){
   318    343             reg = ++pParse->nMem;
   319    344             pParse->nMem += pIdx->nColumn;
   320    345             if( (onError==OE_Replace)
   321    346              || (onError==OE_Default && pIdx->onError==OE_Replace) 
   322    347             ){
   323    348               bReplace = 1;
   324    349             }

Changes to test/indexexpr2.test.

   155    155     EXPLAIN QUERY PLAN
   156    156     SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE nocase;
   157    157   } {/SCAN TABLE t4 USING INDEX i4/}
   158    158   do_execsql_test 3.4.6 {
   159    159     SELECT * FROM t4 ORDER BY Substr(a,-2) COLLATE binary;
   160    160   } {.ABC1 1 .ABC3 3 .abc2 2 .abc4 4}
   161    161   
          162  +# 2014-09-15:  Verify that UPDATEs of columns not referenced by a
          163  +# index on expression do not modify the index.
          164  +#
          165  +unset -nocomplain cnt
          166  +set cnt 0
          167  +proc refcnt {x} {
          168  +  global cnt
          169  +  incr cnt
          170  +  return $x
          171  +}
          172  +db close
          173  +sqlite3 db :memory:
          174  +db function refcnt -deterministic refcnt
          175  +do_test 4.100 {
          176  +  db eval {
          177  +    CREATE TABLE t1(a,b,c,d,e,f);
          178  +    CREATE INDEX t1abc ON t1(refcnt(a+b+c));
          179  +  }
          180  +  set ::cnt
          181  +} {0}
          182  +do_test 4.110 {
          183  +  db eval {INSERT INTO t1 VALUES(1,2,3,4,5,6);}
          184  +  set ::cnt
          185  +  # The refcnt() function is invoked once to compute the index value 
          186  +} {1}
          187  +do_test 4.120 {
          188  +  set ::cnt 0
          189  +  db eval {UPDATE t1 SET b=b+1;}
          190  +  set ::cnt
          191  +  # The refcnt() function is invoked twice, once to remove the old index
          192  +  # entry and a second time to insert the new one.
          193  +} {2}
          194  +do_test 4.130 {
          195  +  set ::cnt 0
          196  +  db eval {UPDATE t1 SET d=d+1;}
          197  +  set ::cnt
          198  +  # Refcnt() should not be invoked because that index does not change.
          199  +} {0}
   162    200   
   163    201   finish_test