/ Check-in [22eda098]
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:Combine the Parse.ckBase and Parse.iSelfTab fields into just Parse.iSelfTab. This fixes a problem with date/time functions in check-constraints. Add some test cases for date/time functions in index expressions and check constraints.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | index-on-date-func
Files: files | file ages | folders
SHA3-256: 22eda0985ecd1f456c073e6ad735a8417f3ff1fb6aaad1640e1cec01e50c51d8
User & Date: drh 2017-07-20 13:17:08
Context
2017-07-20
14:36
New test cases for date/time functions in indexes on expressions, in the WHERE clause of partial indexes, and in CHECK constraints. Closed-Leaf check-in: b7f70c7f user: drh tags: index-on-date-func
13:17
Combine the Parse.ckBase and Parse.iSelfTab fields into just Parse.iSelfTab. This fixes a problem with date/time functions in check-constraints. Add some test cases for date/time functions in index expressions and check constraints. check-in: 22eda098 user: drh tags: index-on-date-func
2017-07-19
19:48
Allow indexes to be created on date/time functions as long as the 'now' date and the 'localtime' and 'utc' modifiers are not used. check-in: 0a5e1c04 user: drh tags: index-on-date-func
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/date.c.

   382    382     DateTime *p
   383    383   ){
   384    384     double r;
   385    385     if( parseYyyyMmDd(zDate,p)==0 ){
   386    386       return 0;
   387    387     }else if( parseHhMmSs(zDate, p)==0 ){
   388    388       return 0;
   389         -  }else if( sqlite3StrICmp(zDate,"now")==0 ){
   390         -    sqlite3VdbePureFuncOnly(context);
          389  +  }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
   391    390       return setDateTimeToCurrent(context, p);
   392    391     }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
   393    392       setRawDateNumber(p, r);
   394    393       return 0;
   395    394     }
   396    395     return 1;
   397    396   }
................................................................................
   666    665   #ifndef SQLITE_OMIT_LOCALTIME
   667    666       case 'l': {
   668    667         /*    localtime
   669    668         **
   670    669         ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
   671    670         ** show local time.
   672    671         */
   673         -      if( sqlite3_stricmp(z, "localtime")==0 ){
   674         -        sqlite3VdbePureFuncOnly(pCtx);
          672  +      if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
   675    673           computeJD(p);
   676    674           p->iJD += localtimeOffset(p, pCtx, &rc);
   677    675           clearYMD_HMS_TZ(p);
   678    676         }
   679    677         break;
   680    678       }
   681    679   #endif
................................................................................
   693    691             p->iJD = (sqlite3_int64)r;
   694    692             p->validJD = 1;
   695    693             p->rawS = 0;
   696    694             rc = 0;
   697    695           }
   698    696         }
   699    697   #ifndef SQLITE_OMIT_LOCALTIME
   700         -      else if( sqlite3_stricmp(z, "utc")==0 ){
   701         -        sqlite3VdbePureFuncOnly(pCtx);
          698  +      else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
   702    699           if( p->tzSet==0 ){
   703    700             sqlite3_int64 c1;
   704    701             computeJD(p);
   705    702             c1 = localtimeOffset(p, pCtx, &rc);
   706    703             if( rc==SQLITE_OK ){
   707    704               p->iJD -= c1;
   708    705               clearYMD_HMS_TZ(p);

Changes to src/expr.c.

  3480   3480           return target;
  3481   3481         }
  3482   3482         /* Otherwise, fall thru into the TK_COLUMN case */
  3483   3483       }
  3484   3484       case TK_COLUMN: {
  3485   3485         int iTab = pExpr->iTable;
  3486   3486         if( iTab<0 ){
  3487         -        if( pParse->ckBase>0 ){
         3487  +        if( pParse->iSelfTab<0 ){
  3488   3488             /* Generating CHECK constraints or inserting into partial index */
  3489         -          return pExpr->iColumn + pParse->ckBase;
         3489  +          return pExpr->iColumn - pParse->iSelfTab;
  3490   3490           }else{
  3491   3491             /* Coding an expression that is part of an index where column names
  3492   3492             ** in the index refer to the table to which the index belongs */
  3493   3493             iTab = pParse->iSelfTab - 1;
  3494   3494           }
  3495   3495         }
  3496   3496         return sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,

Changes to src/insert.c.

  1329   1329     }
  1330   1330   
  1331   1331     /* Test all CHECK constraints
  1332   1332     */
  1333   1333   #ifndef SQLITE_OMIT_CHECK
  1334   1334     if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
  1335   1335       ExprList *pCheck = pTab->pCheck;
  1336         -    pParse->ckBase = regNewData+1;
         1336  +    pParse->iSelfTab = -(regNewData+1);
  1337   1337       onError = overrideError!=OE_Default ? overrideError : OE_Abort;
  1338   1338       for(i=0; i<pCheck->nExpr; i++){
  1339   1339         int allOk;
  1340   1340         Expr *pExpr = pCheck->a[i].pExpr;
  1341   1341         if( aiChng && checkConstraintUnchanged(pExpr, aiChng, pkChng) ) continue;
  1342   1342         allOk = sqlite3VdbeMakeLabel(v);
  1343   1343         sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL);
................................................................................
  1349   1349           if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */
  1350   1350           sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,
  1351   1351                                 onError, zName, P4_TRANSIENT,
  1352   1352                                 P5_ConstraintCheck);
  1353   1353         }
  1354   1354         sqlite3VdbeResolveLabel(v, allOk);
  1355   1355       }
         1356  +    pParse->iSelfTab = 0;
  1356   1357     }
  1357   1358   #endif /* !defined(SQLITE_OMIT_CHECK) */
  1358   1359   
  1359   1360     /* If rowid is changing, make sure the new rowid does not previously
  1360   1361     ** exist in the table.
  1361   1362     */
  1362   1363     if( pkChng && pPk==0 ){
................................................................................
  1493   1494       }
  1494   1495       iThisCur = iIdxCur+ix;
  1495   1496       addrUniqueOk = sqlite3VdbeMakeLabel(v);
  1496   1497   
  1497   1498       /* Skip partial indices for which the WHERE clause is not true */
  1498   1499       if( pIdx->pPartIdxWhere ){
  1499   1500         sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
  1500         -      pParse->ckBase = regNewData+1;
         1501  +      pParse->iSelfTab = -(regNewData+1);
  1501   1502         sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
  1502   1503                               SQLITE_JUMPIFNULL);
  1503         -      pParse->ckBase = 0;
         1504  +      pParse->iSelfTab = 0;
  1504   1505       }
  1505   1506   
  1506   1507       /* Create a record for this index entry as it should appear after
  1507   1508       ** the insert or update.  Store that record in the aRegIdx[ix] register
  1508   1509       */
  1509   1510       regIdx = aRegIdx[ix]+1;
  1510   1511       for(i=0; i<pIdx->nColumn; i++){
  1511   1512         int iField = pIdx->aiColumn[i];
  1512   1513         int x;
  1513   1514         if( iField==XN_EXPR ){
  1514         -        pParse->ckBase = regNewData+1;
         1515  +        pParse->iSelfTab = -(regNewData+1);
  1515   1516           sqlite3ExprCodeCopy(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i);
  1516         -        pParse->ckBase = 0;
         1517  +        pParse->iSelfTab = 0;
  1517   1518           VdbeComment((v, "%s column %d", pIdx->zName, i));
  1518   1519         }else{
  1519   1520           if( iField==XN_ROWID || iField==pTab->iPKey ){
  1520   1521             x = regNewData;
  1521   1522           }else{
  1522   1523             x = iField + regNewData + 1;
  1523   1524           }

Changes to src/pragma.c.

whitespace changes only

Changes to src/shell.c.

  8392   8392     find_home_dir(1);
  8393   8393   #if !SQLITE_SHELL_IS_UTF8
  8394   8394     for(i=0; i<argc; i++) sqlite3_free(argv[i]);
  8395   8395     sqlite3_free(argv);
  8396   8396   #endif
  8397   8397     return rc;
  8398   8398   }
  8399         -

Changes to src/sqliteInt.h.

  2959   2959     int nRangeReg;       /* Size of the temporary register block */
  2960   2960     int iRangeReg;       /* First register in temporary register block */
  2961   2961     int nErr;            /* Number of errors seen */
  2962   2962     int nTab;            /* Number of previously allocated VDBE cursors */
  2963   2963     int nMem;            /* Number of memory cells used so far */
  2964   2964     int nOpAlloc;        /* Number of slots allocated for Vdbe.aOp[] */
  2965   2965     int szOpAlloc;       /* Bytes of memory space allocated for Vdbe.aOp[] */
  2966         -  int ckBase;          /* Base register of data during check constraints */
  2967         -  int iSelfTab;        /* Table of an index whose exprs are being coded */
         2966  +  int iSelfTab;        /* Table for associated with an index on expr, or negative
         2967  +                       ** of the base register during check-constraint eval */
  2968   2968     int iCacheLevel;     /* ColCache valid when aColCache[].iLevel<=iCacheLevel */
  2969   2969     int iCacheCnt;       /* Counter used to generate aColCache[].lru values */
  2970   2970     int nLabel;          /* Number of labels used */
  2971   2971     int *aLabel;         /* Space to hold the labels */
  2972   2972     ExprList *pConstExpr;/* Constant expressions */
  2973   2973     Token constraintName;/* Name of the constraint currently being parsed */
  2974   2974     yDbMask writeMask;   /* Start a write transaction on these databases */

Changes to src/vdbe.h.

   249    249   typedef int (*RecordCompare)(int,const void*,UnpackedRecord*);
   250    250   RecordCompare sqlite3VdbeFindCompare(UnpackedRecord*);
   251    251   
   252    252   #ifndef SQLITE_OMIT_TRIGGER
   253    253   void sqlite3VdbeLinkSubProgram(Vdbe *, SubProgram *);
   254    254   #endif
   255    255   
   256         -void sqlite3VdbePureFuncOnly(sqlite3_context*);
          256  +int sqlite3NotPureFunc(sqlite3_context*);
   257    257   
   258    258   /* Use SQLITE_ENABLE_COMMENTS to enable generation of extra comments on
   259    259   ** each VDBE opcode.
   260    260   **
   261    261   ** Use the SQLITE_ENABLE_MODULE_COMMENTS macro to see some extra no-op
   262    262   ** comments in VDBE programs that show key decision points in the code
   263    263   ** generator.

Changes to src/vdbeaux.c.

  4588   4588   ** rather than OP_Function.
  4589   4589   **
  4590   4590   ** OP_PureFunc means that the function must be deterministic, and should
  4591   4591   ** throw an error if it is given inputs that would make it non-deterministic.
  4592   4592   ** This routine is invoked by date/time functions that use non-deterministic
  4593   4593   ** features such as 'now'.
  4594   4594   */
  4595         -void sqlite3VdbePureFuncOnly(sqlite3_context *pCtx){
         4595  +int sqlite3NotPureFunc(sqlite3_context *pCtx){
  4596   4596     if( pCtx->pVdbe->aOp[pCtx->iOp].opcode==OP_PureFunc ){
  4597   4597       sqlite3_result_error(pCtx, 
  4598         -       "non-deterministic functions prohibited in index expressions", -1);
         4598  +       "non-deterministic function in index expression or CHECK constraint",
         4599  +       -1);
         4600  +    return 0;
  4599   4601     }
         4602  +  return 1;
  4600   4603   }
  4601   4604   
  4602   4605   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4603   4606   /*
  4604   4607   ** Transfer error message text from an sqlite3_vtab.zErrMsg (text stored
  4605   4608   ** in memory obtained from sqlite3_malloc) into a Vdbe.zErrMsg (text stored
  4606   4609   ** in memory obtained from sqlite3DbMalloc).

Added test/date2.test.

            1  +# 2017-07-20
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing date and time functions used in
           13  +# check constraints and index expressions.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +# Skip this whole file if date and time functions are omitted
           20  +# at compile-time
           21  +#
           22  +ifcapable {!datetime} {
           23  +  finish_test
           24  +  return
           25  +}
           26  +
           27  +do_execsql_test date2-100 {
           28  +  CREATE TABLE t1(x, y, CHECK( date(x) BETWEEN '2017-07-01' AND '2017-07-31' ));
           29  +  INSERT INTO t1(x,y) VALUES('2017-07-20','one');
           30  +} {}
           31  +do_catchsql_test date2-110 {
           32  +  INSERT INTO t1(x,y) VALUES('now','two');
           33  +} {1 {non-deterministic function in index expression or CHECK constraint}}
           34  +do_execsql_test date2-120 {
           35  +  SELECT * FROM t1;
           36  +} {2017-07-20 one}
           37  +do_catchsql_test date2-130 {
           38  +  INSERT INTO t1(x,y) VALUES('2017-08-01','two');
           39  +} {1 {CHECK constraint failed: t1}}
           40  +
           41  +do_execsql_test date2-200 {
           42  +  CREATE TABLE t2(x,y);
           43  +  INSERT INTO t2(x,y) VALUES(1, '2017-07-20'), (2, 'xyzzy');
           44  +  CREATE INDEX t2y ON t2(date(y));
           45  +}
           46  +do_catchsql_test date2-210 {
           47  +  INSERT INTO t2(x,y) VALUES(3, 'now');
           48  +} {1 {non-deterministic function in index expression or CHECK constraint}}
           49  +do_execsql_test date2-220 {
           50  +  SELECT x, y FROM t2 ORDER BY x;
           51  +} {1 2017-07-20 2 xyzzy}
           52  +
           53  +finish_test

Changes to test/indexexpr1.test.

   182    182   
   183    183   do_catchsql_test indexexpr1-300 {
   184    184     CREATE TABLE t2(a,b,c); INSERT INTO t2 VALUES(1,2,3);
   185    185     CREATE INDEX t2x1 ON t2(a,b+random());
   186    186   } {1 {non-deterministic functions prohibited in index expressions}}
   187    187   do_catchsql_test indexexpr1-301 {
   188    188     CREATE INDEX t2x1 ON t2(julianday('now',a));
   189         -} {1 {non-deterministic function}}
          189  +} {1 {non-deterministic function in index expression or CHECK constraint}}
   190    190   do_catchsql_test indexexpr1-310 {
   191    191     CREATE INDEX t2x2 ON t2(a,b+(SELECT 15));
   192    192   } {1 {subqueries prohibited in index expressions}}
   193    193   do_catchsql_test indexexpr1-320 {
   194    194     CREATE TABLE e1(x,y,UNIQUE(y,substr(x,1,5)));
   195    195   } {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}}
   196    196   do_catchsql_test indexexpr1-330 {