/ Check-in [0a5e1c04]
Login

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

Overview
Comment:Allow indexes to be created on date/time functions as long as the 'now' date and the 'localtime' and 'utc' modifiers are not used.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | index-on-date-func
Files: files | file ages | folders
SHA3-256:0a5e1c04d9d07bb7fd6546a9ddac1bf42b19ea19c2b79570aea6cd4226887a27
User & Date: drh 2017-07-19 19:48:40
Context
2017-07-20
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
18:01
Remove some 'breakpoint' commands in test files. check-in: d14fc621 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace 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){
          389  +  }else if( sqlite3StrICmp(zDate,"now")==0 ){
          390  +    sqlite3VdbePureFuncOnly(context);
   390    391       return setDateTimeToCurrent(context, p);
   391    392     }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
   392    393       setRawDateNumber(p, r);
   393    394       return 0;
   394    395     }
   395    396     return 1;
   396    397   }
................................................................................
   666    667       case 'l': {
   667    668         /*    localtime
   668    669         **
   669    670         ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
   670    671         ** show local time.
   671    672         */
   672    673         if( sqlite3_stricmp(z, "localtime")==0 ){
          674  +        sqlite3VdbePureFuncOnly(pCtx);
   673    675           computeJD(p);
   674    676           p->iJD += localtimeOffset(p, pCtx, &rc);
   675    677           clearYMD_HMS_TZ(p);
   676    678         }
   677    679         break;
   678    680       }
   679    681   #endif
................................................................................
   692    694             p->validJD = 1;
   693    695             p->rawS = 0;
   694    696             rc = 0;
   695    697           }
   696    698         }
   697    699   #ifndef SQLITE_OMIT_LOCALTIME
   698    700         else if( sqlite3_stricmp(z, "utc")==0 ){
          701  +        sqlite3VdbePureFuncOnly(pCtx);
   699    702           if( p->tzSet==0 ){
   700    703             sqlite3_int64 c1;
   701    704             computeJD(p);
   702    705             c1 = localtimeOffset(p, pCtx, &rc);
   703    706             if( rc==SQLITE_OK ){
   704    707               p->iJD -= c1;
   705    708               clearYMD_HMS_TZ(p);
................................................................................
  1227   1230   ** This function registered all of the above C functions as SQL
  1228   1231   ** functions.  This should be the only routine in this file with
  1229   1232   ** external linkage.
  1230   1233   */
  1231   1234   void sqlite3RegisterDateTimeFunctions(void){
  1232   1235     static FuncDef aDateTimeFuncs[] = {
  1233   1236   #ifndef SQLITE_OMIT_DATETIME_FUNCS
  1234         -    DFUNCTION(julianday,        -1, 0, 0, juliandayFunc ),
  1235         -    DFUNCTION(date,             -1, 0, 0, dateFunc      ),
  1236         -    DFUNCTION(time,             -1, 0, 0, timeFunc      ),
  1237         -    DFUNCTION(datetime,         -1, 0, 0, datetimeFunc  ),
  1238         -    DFUNCTION(strftime,         -1, 0, 0, strftimeFunc  ),
         1237  +    PURE_DATE(julianday,        -1, 0, 0, juliandayFunc ),
         1238  +    PURE_DATE(date,             -1, 0, 0, dateFunc      ),
         1239  +    PURE_DATE(time,             -1, 0, 0, timeFunc      ),
         1240  +    PURE_DATE(datetime,         -1, 0, 0, datetimeFunc  ),
         1241  +    PURE_DATE(strftime,         -1, 0, 0, strftimeFunc  ),
  1239   1242       DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
  1240   1243       DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
  1241   1244       DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
  1242   1245   #else
  1243   1246       STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
  1244   1247       STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
  1245   1248       STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
  1246   1249   #endif
  1247   1250     };
  1248   1251     sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
  1249   1252   }

Changes to src/delete.c.

   848    848     int j;
   849    849     int regBase;
   850    850     int nCol;
   851    851   
   852    852     if( piPartIdxLabel ){
   853    853       if( pIdx->pPartIdxWhere ){
   854    854         *piPartIdxLabel = sqlite3VdbeMakeLabel(v);
   855         -      pParse->iSelfTab = iDataCur;
          855  +      pParse->iSelfTab = iDataCur + 1;
   856    856         sqlite3ExprCachePush(pParse);
   857    857         sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, *piPartIdxLabel, 
   858    858                               SQLITE_JUMPIFNULL);
          859  +      pParse->iSelfTab = 0;
   859    860       }else{
   860    861         *piPartIdxLabel = 0;
   861    862       }
   862    863     }
   863    864     nCol = (prefixOnly && pIdx->uniqNotNull) ? pIdx->nKeyCol : pIdx->nColumn;
   864    865     regBase = sqlite3GetTempRange(pParse, nCol);
   865    866     if( pPrior && (regBase!=regPrior || pPrior->pPartIdxWhere) ) pPrior = 0;

Changes to src/expr.c.

  3234   3234     int iIdxCol,    /* The column of the index to be loaded */
  3235   3235     int regOut      /* Store the index column value in this register */
  3236   3236   ){
  3237   3237     i16 iTabCol = pIdx->aiColumn[iIdxCol];
  3238   3238     if( iTabCol==XN_EXPR ){
  3239   3239       assert( pIdx->aColExpr );
  3240   3240       assert( pIdx->aColExpr->nExpr>iIdxCol );
  3241         -    pParse->iSelfTab = iTabCur;
         3241  +    pParse->iSelfTab = iTabCur + 1;
  3242   3242       sqlite3ExprCodeCopy(pParse, pIdx->aColExpr->a[iIdxCol].pExpr, regOut);
         3243  +    pParse->iSelfTab = 0;
  3243   3244     }else{
  3244   3245       sqlite3ExprCodeGetColumnOfTable(pParse->pVdbe, pIdx->pTable, iTabCur,
  3245   3246                                       iTabCol, regOut);
  3246   3247     }
  3247   3248   }
  3248   3249   
  3249   3250   /*
................................................................................
  3485   3486         if( iTab<0 ){
  3486   3487           if( pParse->ckBase>0 ){
  3487   3488             /* Generating CHECK constraints or inserting into partial index */
  3488   3489             return pExpr->iColumn + pParse->ckBase;
  3489   3490           }else{
  3490   3491             /* Coding an expression that is part of an index where column names
  3491   3492             ** in the index refer to the table to which the index belongs */
  3492         -          iTab = pParse->iSelfTab;
         3493  +          iTab = pParse->iSelfTab - 1;
  3493   3494           }
  3494   3495         }
  3495   3496         return sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,
  3496   3497                                  pExpr->iColumn, iTab, target,
  3497   3498                                  pExpr->op2);
  3498   3499       }
  3499   3500       case TK_INTEGER: {
................................................................................
  3822   3823           pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr);
  3823   3824         }
  3824   3825   #endif
  3825   3826         if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  3826   3827           if( !pColl ) pColl = db->pDfltColl; 
  3827   3828           sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
  3828   3829         }
  3829         -      sqlite3VdbeAddOp4(v, OP_Function0, constMask, r1, target,
  3830         -                        (char*)pDef, P4_FUNCDEF);
         3830  +      sqlite3VdbeAddOp4(v, pParse->iSelfTab ? OP_PureFunc0 : OP_Function0,
         3831  +                        constMask, r1, target, (char*)pDef, P4_FUNCDEF);
  3831   3832         sqlite3VdbeChangeP5(v, (u8)nFarg);
  3832   3833         if( nFarg && constMask==0 ){
  3833   3834           sqlite3ReleaseTempRange(pParse, r1, nFarg);
  3834   3835         }
  3835   3836         return target;
  3836   3837       }
  3837   3838   #ifndef SQLITE_OMIT_SUBQUERY

Changes to src/pragma.c.

  1590   1590           if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
  1591   1591             ExprList *pCheck = sqlite3ExprListDup(db, pTab->pCheck, 0);
  1592   1592             if( db->mallocFailed==0 ){
  1593   1593               int addrCkFault = sqlite3VdbeMakeLabel(v);
  1594   1594               int addrCkOk = sqlite3VdbeMakeLabel(v);
  1595   1595               char *zErr;
  1596   1596               int k;
  1597         -            pParse->iSelfTab = iDataCur;
         1597  +            pParse->iSelfTab = iDataCur+1;
  1598   1598               sqlite3ExprCachePush(pParse);
  1599   1599               for(k=pCheck->nExpr-1; k>0; k--){
  1600   1600                 sqlite3ExprIfFalse(pParse, pCheck->a[k].pExpr, addrCkFault, 0);
  1601   1601               }
  1602   1602               sqlite3ExprIfTrue(pParse, pCheck->a[0].pExpr, addrCkOk, 
  1603   1603                   SQLITE_JUMPIFNULL);
  1604   1604               sqlite3VdbeResolveLabel(v, addrCkFault);
         1605  +            pParse->iSelfTab = 0;
  1605   1606               zErr = sqlite3MPrintf(db, "CHECK constraint failed in %s",
  1606   1607                   pTab->zName);
  1607   1608               sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC);
  1608   1609               integrityCheckResultRow(v, 3);
  1609   1610               sqlite3VdbeResolveLabel(v, addrCkOk);
  1610   1611               sqlite3ExprCachePop(pParse);
  1611   1612             }

Changes to src/sqliteInt.h.

  1624   1624   **   VFUNCTION(zName, nArg, iArg, bNC, xFunc)
  1625   1625   **     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag.
  1626   1626   **
  1627   1627   **   DFUNCTION(zName, nArg, iArg, bNC, xFunc)
  1628   1628   **     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and
  1629   1629   **     adds the SQLITE_FUNC_SLOCHNG flag.  Used for date & time functions
  1630   1630   **     and functions like sqlite_version() that can change, but not during
  1631         -**     a single query.
         1631  +**     a single query.  The iArg is ignored.  The user-data is always set
         1632  +**     to a NULL pointer.  The bNC parameter is not used.
         1633  +**
         1634  +**   PURE_DATE(zName, nArg, iArg, bNC, xFunc)
         1635  +**     Used for "pure" date/time functions, this macro is like DFUNCTION
         1636  +**     except that it does set the SQLITE_FUNC_CONSTANT flags.  iArg is
         1637  +**     ignored and the user-data for these functions is set to an 
         1638  +**     arbitrary non-NULL pointer.  The bNC parameter is not used.
  1632   1639   **
  1633   1640   **   AGGREGATE(zName, nArg, iArg, bNC, xStep, xFinal)
  1634   1641   **     Used to create an aggregate function definition implemented by
  1635   1642   **     the C functions xStep and xFinal. The first four parameters
  1636   1643   **     are interpreted in the same way as the first 4 parameters to
  1637   1644   **     FUNCTION().
  1638   1645   **
................................................................................
  1647   1654   #define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1648   1655     {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1649   1656      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
  1650   1657   #define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1651   1658     {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1652   1659      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
  1653   1660   #define DFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1654         -  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1655         -   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
         1661  +  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8, \
         1662  +   0, 0, xFunc, 0, #zName, {0} }
         1663  +#define PURE_DATE(zName, nArg, iArg, bNC, xFunc) \
         1664  +  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|SQLITE_FUNC_CONSTANT, \
         1665  +   (void*)xFunc, 0, xFunc, 0, #zName, {0} }
  1656   1666   #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  1657   1667     {nArg,SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags,\
  1658   1668      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
  1659   1669   #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  1660   1670     {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1661   1671      pArg, 0, xFunc, 0, #zName, }
  1662   1672   #define LIKEFUNC(zName, nArg, arg, flags) \

Changes to src/vdbe.c.

  1586   1586     assert( pOp->p4type==P4_COLLSEQ );
  1587   1587     if( pOp->p1 ){
  1588   1588       sqlite3VdbeMemSetInt64(&aMem[pOp->p1], 0);
  1589   1589     }
  1590   1590     break;
  1591   1591   }
  1592   1592   
  1593         -/* Opcode: Function0 P1 P2 P3 P4 P5
  1594         -** Synopsis: r[P3]=func(r[P2@P5])
  1595         -**
  1596         -** Invoke a user function (P4 is a pointer to a FuncDef object that
  1597         -** defines the function) with P5 arguments taken from register P2 and
  1598         -** successors.  The result of the function is stored in register P3.
  1599         -** Register P3 must not be one of the function inputs.
  1600         -**
  1601         -** P1 is a 32-bit bitmask indicating whether or not each argument to the 
  1602         -** function was determined to be constant at compile time. If the first
  1603         -** argument was constant then bit 0 of P1 is set. This is used to determine
  1604         -** whether meta data associated with a user function argument using the
  1605         -** sqlite3_set_auxdata() API may be safely retained until the next
  1606         -** invocation of this opcode.
  1607         -**
  1608         -** See also: Function, AggStep, AggFinal
  1609         -*/
  1610         -/* Opcode: Function P1 P2 P3 P4 P5
  1611         -** Synopsis: r[P3]=func(r[P2@P5])
  1612         -**
  1613         -** Invoke a user function (P4 is a pointer to an sqlite3_context object that
  1614         -** contains a pointer to the function to be run) with P5 arguments taken
  1615         -** from register P2 and successors.  The result of the function is stored
  1616         -** in register P3.  Register P3 must not be one of the function inputs.
  1617         -**
  1618         -** P1 is a 32-bit bitmask indicating whether or not each argument to the 
  1619         -** function was determined to be constant at compile time. If the first
  1620         -** argument was constant then bit 0 of P1 is set. This is used to determine
  1621         -** whether meta data associated with a user function argument using the
  1622         -** sqlite3_set_auxdata() API may be safely retained until the next
  1623         -** invocation of this opcode.
  1624         -**
  1625         -** SQL functions are initially coded as OP_Function0 with P4 pointing
  1626         -** to a FuncDef object.  But on first evaluation, the P4 operand is
  1627         -** automatically converted into an sqlite3_context object and the operation
  1628         -** changed to this OP_Function opcode.  In this way, the initialization of
  1629         -** the sqlite3_context object occurs only once, rather than once for each
  1630         -** evaluation of the function.
  1631         -**
  1632         -** See also: Function0, AggStep, AggFinal
  1633         -*/
  1634         -case OP_Function0: {
  1635         -  int n;
  1636         -  sqlite3_context *pCtx;
  1637         -
  1638         -  assert( pOp->p4type==P4_FUNCDEF );
  1639         -  n = pOp->p5;
  1640         -  assert( pOp->p3>0 && pOp->p3<=(p->nMem+1 - p->nCursor) );
  1641         -  assert( n==0 || (pOp->p2>0 && pOp->p2+n<=(p->nMem+1 - p->nCursor)+1) );
  1642         -  assert( pOp->p3<pOp->p2 || pOp->p3>=pOp->p2+n );
  1643         -  pCtx = sqlite3DbMallocRawNN(db, sizeof(*pCtx) + (n-1)*sizeof(sqlite3_value*));
  1644         -  if( pCtx==0 ) goto no_mem;
  1645         -  pCtx->pOut = 0;
  1646         -  pCtx->pFunc = pOp->p4.pFunc;
  1647         -  pCtx->iOp = (int)(pOp - aOp);
  1648         -  pCtx->pVdbe = p;
  1649         -  pCtx->argc = n;
  1650         -  pOp->p4type = P4_FUNCCTX;
  1651         -  pOp->p4.pCtx = pCtx;
  1652         -  pOp->opcode = OP_Function;
  1653         -  /* Fall through into OP_Function */
  1654         -}
  1655         -case OP_Function: {
  1656         -  int i;
  1657         -  sqlite3_context *pCtx;
  1658         -
  1659         -  assert( pOp->p4type==P4_FUNCCTX );
  1660         -  pCtx = pOp->p4.pCtx;
  1661         -
  1662         -  /* If this function is inside of a trigger, the register array in aMem[]
  1663         -  ** might change from one evaluation to the next.  The next block of code
  1664         -  ** checks to see if the register array has changed, and if so it
  1665         -  ** reinitializes the relavant parts of the sqlite3_context object */
  1666         -  pOut = &aMem[pOp->p3];
  1667         -  if( pCtx->pOut != pOut ){
  1668         -    pCtx->pOut = pOut;
  1669         -    for(i=pCtx->argc-1; i>=0; i--) pCtx->argv[i] = &aMem[pOp->p2+i];
  1670         -  }
  1671         -
  1672         -  memAboutToChange(p, pOut);
  1673         -#ifdef SQLITE_DEBUG
  1674         -  for(i=0; i<pCtx->argc; i++){
  1675         -    assert( memIsValid(pCtx->argv[i]) );
  1676         -    REGISTER_TRACE(pOp->p2+i, pCtx->argv[i]);
  1677         -  }
  1678         -#endif
  1679         -  MemSetTypeFlag(pOut, MEM_Null);
  1680         -  pCtx->fErrorOrAux = 0;
  1681         -  (*pCtx->pFunc->xSFunc)(pCtx, pCtx->argc, pCtx->argv);/* IMP: R-24505-23230 */
  1682         -
  1683         -  /* If the function returned an error, throw an exception */
  1684         -  if( pCtx->fErrorOrAux ){
  1685         -    if( pCtx->isError ){
  1686         -      sqlite3VdbeError(p, "%s", sqlite3_value_text(pOut));
  1687         -      rc = pCtx->isError;
  1688         -    }
  1689         -    sqlite3VdbeDeleteAuxData(db, &p->pAuxData, pCtx->iOp, pOp->p1);
  1690         -    if( rc ) goto abort_due_to_error;
  1691         -  }
  1692         -
  1693         -  /* Copy the result of the function into register P3 */
  1694         -  if( pOut->flags & (MEM_Str|MEM_Blob) ){
  1695         -    sqlite3VdbeChangeEncoding(pOut, encoding);
  1696         -    if( sqlite3VdbeMemTooBig(pOut) ) goto too_big;
  1697         -  }
  1698         -
  1699         -  REGISTER_TRACE(pOp->p3, pOut);
  1700         -  UPDATE_MAX_BLOBSIZE(pOut);
  1701         -  break;
  1702         -}
  1703         -
  1704   1593   /* Opcode: BitAnd P1 P2 P3 * *
  1705   1594   ** Synopsis: r[P3]=r[P1]&r[P2]
  1706   1595   **
  1707   1596   ** Take the bit-wise AND of the values in register P1 and P2 and
  1708   1597   ** store the result in register P3.
  1709   1598   ** If either input is NULL, the result is NULL.
  1710   1599   */
................................................................................
  6995   6884       newMax = sqlite3BtreeLastPage(pBt);
  6996   6885       if( newMax < (unsigned)pOp->p3 ) newMax = (unsigned)pOp->p3;
  6997   6886     }
  6998   6887     pOut->u.i = sqlite3BtreeMaxPageCount(pBt, newMax);
  6999   6888     break;
  7000   6889   }
  7001   6890   #endif
         6891  +
         6892  +/* Opcode: Function0 P1 P2 P3 P4 P5
         6893  +** Synopsis: r[P3]=func(r[P2@P5])
         6894  +**
         6895  +** Invoke a user function (P4 is a pointer to a FuncDef object that
         6896  +** defines the function) with P5 arguments taken from register P2 and
         6897  +** successors.  The result of the function is stored in register P3.
         6898  +** Register P3 must not be one of the function inputs.
         6899  +**
         6900  +** P1 is a 32-bit bitmask indicating whether or not each argument to the 
         6901  +** function was determined to be constant at compile time. If the first
         6902  +** argument was constant then bit 0 of P1 is set. This is used to determine
         6903  +** whether meta data associated with a user function argument using the
         6904  +** sqlite3_set_auxdata() API may be safely retained until the next
         6905  +** invocation of this opcode.
         6906  +**
         6907  +** See also: Function, AggStep, AggFinal
         6908  +*/
         6909  +/* Opcode: Function P1 P2 P3 P4 P5
         6910  +** Synopsis: r[P3]=func(r[P2@P5])
         6911  +**
         6912  +** Invoke a user function (P4 is a pointer to an sqlite3_context object that
         6913  +** contains a pointer to the function to be run) with P5 arguments taken
         6914  +** from register P2 and successors.  The result of the function is stored
         6915  +** in register P3.  Register P3 must not be one of the function inputs.
         6916  +**
         6917  +** P1 is a 32-bit bitmask indicating whether or not each argument to the 
         6918  +** function was determined to be constant at compile time. If the first
         6919  +** argument was constant then bit 0 of P1 is set. This is used to determine
         6920  +** whether meta data associated with a user function argument using the
         6921  +** sqlite3_set_auxdata() API may be safely retained until the next
         6922  +** invocation of this opcode.
         6923  +**
         6924  +** SQL functions are initially coded as OP_Function0 with P4 pointing
         6925  +** to a FuncDef object.  But on first evaluation, the P4 operand is
         6926  +** automatically converted into an sqlite3_context object and the operation
         6927  +** changed to this OP_Function opcode.  In this way, the initialization of
         6928  +** the sqlite3_context object occurs only once, rather than once for each
         6929  +** evaluation of the function.
         6930  +**
         6931  +** See also: Function0, AggStep, AggFinal
         6932  +*/
         6933  +case OP_PureFunc0:
         6934  +case OP_Function0: {
         6935  +  int n;
         6936  +  sqlite3_context *pCtx;
         6937  +
         6938  +  assert( pOp->p4type==P4_FUNCDEF );
         6939  +  n = pOp->p5;
         6940  +  assert( pOp->p3>0 && pOp->p3<=(p->nMem+1 - p->nCursor) );
         6941  +  assert( n==0 || (pOp->p2>0 && pOp->p2+n<=(p->nMem+1 - p->nCursor)+1) );
         6942  +  assert( pOp->p3<pOp->p2 || pOp->p3>=pOp->p2+n );
         6943  +  pCtx = sqlite3DbMallocRawNN(db, sizeof(*pCtx) + (n-1)*sizeof(sqlite3_value*));
         6944  +  if( pCtx==0 ) goto no_mem;
         6945  +  pCtx->pOut = 0;
         6946  +  pCtx->pFunc = pOp->p4.pFunc;
         6947  +  pCtx->iOp = (int)(pOp - aOp);
         6948  +  pCtx->pVdbe = p;
         6949  +  pCtx->argc = n;
         6950  +  pOp->p4type = P4_FUNCCTX;
         6951  +  pOp->p4.pCtx = pCtx;
         6952  +  assert( OP_PureFunc == OP_PureFunc0+2 );
         6953  +  assert( OP_Function == OP_Function0+2 );
         6954  +  pOp->opcode += 2;
         6955  +  /* Fall through into OP_Function */
         6956  +}
         6957  +case OP_PureFunc:
         6958  +case OP_Function: {
         6959  +  int i;
         6960  +  sqlite3_context *pCtx;
         6961  +
         6962  +  assert( pOp->p4type==P4_FUNCCTX );
         6963  +  pCtx = pOp->p4.pCtx;
         6964  +
         6965  +  /* If this function is inside of a trigger, the register array in aMem[]
         6966  +  ** might change from one evaluation to the next.  The next block of code
         6967  +  ** checks to see if the register array has changed, and if so it
         6968  +  ** reinitializes the relavant parts of the sqlite3_context object */
         6969  +  pOut = &aMem[pOp->p3];
         6970  +  if( pCtx->pOut != pOut ){
         6971  +    pCtx->pOut = pOut;
         6972  +    for(i=pCtx->argc-1; i>=0; i--) pCtx->argv[i] = &aMem[pOp->p2+i];
         6973  +  }
         6974  +
         6975  +  memAboutToChange(p, pOut);
         6976  +#ifdef SQLITE_DEBUG
         6977  +  for(i=0; i<pCtx->argc; i++){
         6978  +    assert( memIsValid(pCtx->argv[i]) );
         6979  +    REGISTER_TRACE(pOp->p2+i, pCtx->argv[i]);
         6980  +  }
         6981  +#endif
         6982  +  MemSetTypeFlag(pOut, MEM_Null);
         6983  +  pCtx->fErrorOrAux = 0;
         6984  +  (*pCtx->pFunc->xSFunc)(pCtx, pCtx->argc, pCtx->argv);/* IMP: R-24505-23230 */
         6985  +
         6986  +  /* If the function returned an error, throw an exception */
         6987  +  if( pCtx->fErrorOrAux ){
         6988  +    if( pCtx->isError ){
         6989  +      sqlite3VdbeError(p, "%s", sqlite3_value_text(pOut));
         6990  +      rc = pCtx->isError;
         6991  +    }
         6992  +    sqlite3VdbeDeleteAuxData(db, &p->pAuxData, pCtx->iOp, pOp->p1);
         6993  +    if( rc ) goto abort_due_to_error;
         6994  +  }
         6995  +
         6996  +  /* Copy the result of the function into register P3 */
         6997  +  if( pOut->flags & (MEM_Str|MEM_Blob) ){
         6998  +    sqlite3VdbeChangeEncoding(pOut, encoding);
         6999  +    if( sqlite3VdbeMemTooBig(pOut) ) goto too_big;
         7000  +  }
         7001  +
         7002  +  REGISTER_TRACE(pOp->p3, pOut);
         7003  +  UPDATE_MAX_BLOBSIZE(pOut);
         7004  +  break;
         7005  +}
  7002   7006   
  7003   7007   
  7004   7008   /* Opcode: Init P1 P2 * P4 *
  7005   7009   ** Synopsis: Start at P2
  7006   7010   **
  7007   7011   ** Programs contain a single instance of this opcode as the very first
  7008   7012   ** opcode.

Changes to src/vdbe.h.

   248    248   
   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  +
          256  +void sqlite3VdbePureFuncOnly(sqlite3_context*);
   255    257   
   256    258   /* Use SQLITE_ENABLE_COMMENTS to enable generation of extra comments on
   257    259   ** each VDBE opcode.
   258    260   **
   259    261   ** Use the SQLITE_ENABLE_MODULE_COMMENTS macro to see some extra no-op
   260    262   ** comments in VDBE programs that show key decision points in the code
   261    263   ** generator.

Changes to src/vdbeaux.c.

  4578   4578     assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
  4579   4579     if( iVar>=32 ){
  4580   4580       v->expmask |= 0x80000000;
  4581   4581     }else{
  4582   4582       v->expmask |= ((u32)1 << (iVar-1));
  4583   4583     }
  4584   4584   }
         4585  +
         4586  +/*
         4587  +** Cause a function to throw an error if it was call from OP_PureFunc
         4588  +** rather than OP_Function.
         4589  +**
         4590  +** OP_PureFunc means that the function must be deterministic, and should
         4591  +** throw an error if it is given inputs that would make it non-deterministic.
         4592  +** This routine is invoked by date/time functions that use non-deterministic
         4593  +** features such as 'now'.
         4594  +*/
         4595  +void sqlite3VdbePureFuncOnly(sqlite3_context *pCtx){
         4596  +  if( pCtx->pVdbe->aOp[pCtx->iOp].opcode==OP_PureFunc ){
         4597  +    sqlite3_result_error(pCtx, 
         4598  +       "non-deterministic functions prohibited in index expressions", -1);
         4599  +  }
         4600  +}
  4585   4601   
  4586   4602   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4587   4603   /*
  4588   4604   ** Transfer error message text from an sqlite3_vtab.zErrMsg (text stored
  4589   4605   ** in memory obtained from sqlite3_malloc) into a Vdbe.zErrMsg (text stored
  4590   4606   ** in memory obtained from sqlite3DbMalloc).
  4591   4607   */

Changes to test/indexexpr1.test.

   177    177     EXPLAIN QUERY PLAN
   178    178     SELECT id, b, c FROM t1
   179    179      WHERE substr(a,27,3)=='ord' AND d>=29;
   180    180   } {/USING INDEX t1a2/}
   181    181   
   182    182   
   183    183   do_catchsql_test indexexpr1-300 {
   184         -  CREATE TABLE t2(a,b,c);
          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         -  CREATE INDEX t2x1 ON t2(a+julianday('now'));
   189         -} {1 {non-deterministic functions prohibited in index expressions}}
          188  +  CREATE INDEX t2x1 ON t2(julianday('now',a));
          189  +} {1 {non-deterministic function}}
   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 {
................................................................................
   375    375   #
   376    376   do_execsql_test indexexpr1-1300.1 {
   377    377     CREATE TABLE t1300(a INTEGER PRIMARY KEY, b);
   378    378     INSERT INTO t1300 VALUES(1,'coffee'),(2,'COFFEE'),(3,'stress'),(4,'STRESS');
   379    379     CREATE INDEX t1300bexpr ON t1300( substr(b,4) );
   380    380     SELECT a FROM t1300 WHERE substr(b,4)='ess' COLLATE nocase ORDER BY +a;
   381    381   } {3 4}
          382  +
          383  +# Date and time functions can participate in an index as long as they
          384  +# do not contain
   382    385   
   383    386   finish_test