/ Check-in [7782cb1d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix to previous check-in: Make sure CHECK constraints involving the ROWID are not ignored when the ROWID changes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7782cb1dd5914b867caf5ff2f0f837407abbfd7a
User & Date: drh 2016-02-10 18:24:05
Context
2016-02-10
19:10
Add testcase() macros to the CHECK constraint avoidance logic. Avoid creating an unused VDBE label during CHECK constraint code generation. check-in: 970881be user: drh tags: trunk
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
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/insert.c.

  1073   1073   #ifdef pTrigger
  1074   1074    #undef pTrigger
  1075   1075   #endif
  1076   1076   #ifdef tmask
  1077   1077    #undef tmask
  1078   1078   #endif
  1079   1079   
         1080  +/*
         1081  +** Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
         1082  +*/
         1083  +#define CKCNSTRNT_COLUMN   0x01    /* CHECK constraint uses a changing column */
         1084  +#define CKCNSTRNT_ROWID    0x02    /* CHECK constraint references the ROWID */
         1085  +
  1080   1086   /* This is the Walker callback from checkConstraintUnchanged().  Set
         1087  +** bit 0x01 of pWalker->eCode if
  1081   1088   ** pWalker->eCode to 0 if this expression node references any of the
  1082   1089   ** columns that are being modifed by an UPDATE statement.
  1083   1090   */
  1084   1091   static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
  1085         -  if( pExpr->op==TK_COLUMN
  1086         -   && pExpr->iColumn>=0
  1087         -   && pWalker->u.aiCol[pExpr->iColumn]>=0
  1088         -  ){
  1089         -    pWalker->eCode = 0;
         1092  +  if( pExpr->op==TK_COLUMN ){
         1093  +    assert( pExpr->iColumn>=0 || pExpr->iColumn==-1 );
         1094  +    if( pExpr->iColumn>=0 ){
         1095  +      if( pWalker->u.aiCol[pExpr->iColumn]>=0 ){
         1096  +        pWalker->eCode |= CKCNSTRNT_COLUMN;
         1097  +      }
         1098  +    }else{
         1099  +      pWalker->eCode |= CKCNSTRNT_ROWID;
         1100  +    }
  1090   1101     }
  1091   1102     return WRC_Continue;
  1092   1103   }
  1093   1104   
  1094   1105   /*
  1095   1106   ** pExpr is a CHECK constraint on a row that is being UPDATE-ed.  The
  1096   1107   ** only columns that are modified by the UPDATE are those for which
  1097         -** aiChng[i]>=0.  Return true if CHECK constraint pExpr does not use
  1098         -** any of the changing columns.  In other words, return true if this
  1099         -** CHECK constraint can be skipped when validating the new row in
  1100         -** the UPDATE statement.
         1108  +** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
         1109  +**
         1110  +** Return true if CHECK constraint pExpr does not use any of the
         1111  +** changing columns (or the rowid if it is changing).  In other words,
         1112  +** return true if this CHECK constraint can be skipped when validating
         1113  +** the new row in the UPDATE statement.
  1101   1114   */
  1102         -static int checkConstraintUnchanged(Expr *pExpr, int *aiChng){
         1115  +static int checkConstraintUnchanged(Expr *pExpr, int *aiChng, int chngRowid){
  1103   1116     Walker w;
  1104   1117     memset(&w, 0, sizeof(w));
  1105         -  w.eCode = 1;
         1118  +  w.eCode = 0;
  1106   1119     w.xExprCallback = checkConstraintExprNode;
  1107   1120     w.u.aiCol = aiChng;
  1108   1121     sqlite3WalkExpr(&w, pExpr);
  1109         -  return w.eCode;
         1122  +  if( !chngRowid ) w.eCode &= ~CKCNSTRNT_ROWID;
         1123  +  return !w.eCode;
  1110   1124   }
  1111   1125   
  1112   1126   /*
  1113   1127   ** Generate code to do constraint checks prior to an INSERT or an UPDATE
  1114   1128   ** on table pTab.
  1115   1129   **
  1116   1130   ** The regNewData parameter is the first register in a range that contains
................................................................................
  1304   1318     if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
  1305   1319       ExprList *pCheck = pTab->pCheck;
  1306   1320       pParse->ckBase = regNewData+1;
  1307   1321       onError = overrideError!=OE_Default ? overrideError : OE_Abort;
  1308   1322       for(i=0; i<pCheck->nExpr; i++){
  1309   1323         int allOk = sqlite3VdbeMakeLabel(v);
  1310   1324         Expr *pExpr = pCheck->a[i].pExpr;
  1311         -      if( aiChng && checkConstraintUnchanged(pExpr, aiChng) ) continue;
         1325  +      if( aiChng && checkConstraintUnchanged(pExpr, aiChng, pkChng) ) continue;
  1312   1326         sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL);
  1313   1327         if( onError==OE_Ignore ){
  1314   1328           sqlite3VdbeGoto(v, ignoreDest);
  1315   1329         }else{
  1316   1330           char *zName = pCheck->a[i].zName;
  1317   1331           if( zName==0 ) zName = pTab->zName;
  1318   1332           if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */

Changes to test/check.test.

   454    454   
   455    455   # 2013-08-02:  Silently ignore database name qualifiers in CHECK constraints.
   456    456   #
   457    457   do_execsql_test 8.1 {
   458    458     CREATE TABLE t810(a, CHECK( main.t810.a>0 ));
   459    459     CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 ));
   460    460   } {}
          461  +
          462  +# Make sure check constraints involving the ROWID are not ignored
          463  +#
          464  +do_execsql_test 9.1 {
          465  +  CREATE TABLE t1(
          466  +    a INTEGER PRIMARY KEY,
          467  +    b INTEGER NOT NULL CONSTRAINT 'b-check' CHECK( b>a ),
          468  +    c INTEGER NOT NULL CONSTRAINT 'c-check' CHECK( c>rowid*2 ),
          469  +    d INTEGER NOT NULL CONSTRAINT 'd-check' CHECK( d BETWEEN b AND c )
          470  +  );
          471  +  INSERT INTO t1(a,b,c,d) VALUES(1,2,4,3),(2,4,6,5),(3,10,30,20);
          472  +} {}
          473  +do_catchsql_test 9.2 {
          474  +  UPDATE t1 SET b=0 WHERE a=1;
          475  +} {1 {CHECK constraint failed: b-check}}
          476  +do_catchsql_test 9.3 {
          477  +  UPDATE t1 SET c=a*2 WHERE a=1;
          478  +} {1 {CHECK constraint failed: c-check}}
          479  +
          480  +
   461    481   
   462    482   finish_test