/ Check-in [6a3aaedf]
Login

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

Overview
Comment:Omit NOT NULL checks on unchanging columns in an UPDATE.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:6a3aaedfb41735996470abbae6d3cd1be1f508b3
User & Date: drh 2016-02-10 16:03:20
Context
2016-02-10
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
13:36
When generating the snapshot-tarball, truncate the date/time in the name to 12 significant digits (YYYYMMDDhhmm) omitting the seconds and fractional seconds. check-in: 604f7775 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

   991    991         sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
   992    992         sqlite3MayAbort(pParse);
   993    993       }else
   994    994   #endif
   995    995       {
   996    996         int isReplace;    /* Set to true if constraints may cause a replace */
   997    997         sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
   998         -          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace
          998  +          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0
   999    999         );
  1000   1000         sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
  1001   1001         sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
  1002   1002                                  regIns, aRegIdx, 0, appendFlag, isReplace==0);
  1003   1003       }
  1004   1004     }
  1005   1005   
................................................................................
  1167   1167     int iDataCur,        /* Canonical data cursor (main table or PK index) */
  1168   1168     int iIdxCur,         /* First index cursor */
  1169   1169     int regNewData,      /* First register in a range holding values to insert */
  1170   1170     int regOldData,      /* Previous content.  0 for INSERTs */
  1171   1171     u8 pkChng,           /* Non-zero if the rowid or PRIMARY KEY changed */
  1172   1172     u8 overrideError,    /* Override onError to this if not OE_Default */
  1173   1173     int ignoreDest,      /* Jump to this label on an OE_Ignore resolution */
  1174         -  int *pbMayReplace    /* OUT: Set to true if constraint may cause a replace */
         1174  +  int *pbMayReplace,   /* OUT: Set to true if constraint may cause a replace */
         1175  +  int *aiChng          /* column i is unchanged if aiChng[i]<0 */
  1175   1176   ){
  1176   1177     Vdbe *v;             /* VDBE under constrution */
  1177   1178     Index *pIdx;         /* Pointer to one of the indices */
  1178   1179     Index *pPk = 0;      /* The PRIMARY KEY index */
  1179   1180     sqlite3 *db;         /* Database connection */
  1180   1181     int i;               /* loop counter */
  1181   1182     int ix;              /* Index loop counter */
................................................................................
  1213   1214     VdbeModuleComment((v, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
  1214   1215                        iDataCur, iIdxCur, regNewData, regOldData, pkChng));
  1215   1216   
  1216   1217     /* Test all NOT NULL constraints.
  1217   1218     */
  1218   1219     for(i=0; i<nCol; i++){
  1219   1220       if( i==pTab->iPKey ){
         1221  +      continue;        /* ROWID is never NULL */
         1222  +    }
         1223  +    if( aiChng && aiChng[i]<0 ){
         1224  +      /* Don't bother checking for NOT NULL on columns that do not change */
  1220   1225         continue;
  1221   1226       }
  1222   1227       onError = pTab->aCol[i].notNull;
  1223         -    if( onError==OE_None ) continue;
         1228  +    if( onError==OE_None ) continue;  /* This column is allowed to be NULL */
  1224   1229       if( overrideError!=OE_Default ){
  1225   1230         onError = overrideError;
  1226   1231       }else if( onError==OE_Default ){
  1227   1232         onError = OE_Abort;
  1228   1233       }
  1229   1234       if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
  1230   1235         onError = OE_Abort;

Changes to src/sqliteInt.h.

  3541   3541   int sqlite3IsRowid(const char*);
  3542   3542   void sqlite3GenerateRowDelete(
  3543   3543       Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8,int);
  3544   3544   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*, int);
  3545   3545   int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
  3546   3546   void sqlite3ResolvePartIdxLabel(Parse*,int);
  3547   3547   void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
  3548         -                                     u8,u8,int,int*);
         3548  +                                     u8,u8,int,int*,int*);
  3549   3549   void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
  3550   3550   int sqlite3OpenTableAndIndices(Parse*, Table*, int, u8, int, u8*, int*, int*);
  3551   3551   void sqlite3BeginWriteOperation(Parse*, int, int);
  3552   3552   void sqlite3MultiWrite(Parse*);
  3553   3553   void sqlite3MayAbort(Parse*);
  3554   3554   void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8);
  3555   3555   void sqlite3UniqueConstraint(Parse*, int, Index*);

Changes to src/update.c.

   568    568     if( !isView ){
   569    569       int addr1 = 0;        /* Address of jump instruction */
   570    570       int bReplace = 0;     /* True if REPLACE conflict resolution might happen */
   571    571   
   572    572       /* Do constraint checks. */
   573    573       assert( regOldRowid>0 );
   574    574       sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
   575         -        regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace);
          575  +        regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace,
          576  +        aXRef);
   576    577   
   577    578       /* Do FK constraint checks. */
   578    579       if( hasFK ){
   579    580         sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
   580    581       }
   581    582   
   582    583       /* Delete the index entries associated with the current record.  */