/ Check-in [8922be1a]
Login

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

Overview
Comment:Merge updates from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | toTypeFuncs
Files: files | file ages | folders
SHA1: 8922be1a3e0269552e12b87fe1e5141c38a8d3f8
User & Date: mistachkin 2013-09-06 22:27:55
Context
2013-09-24
19:07
Merge updates from trunk. check-in: 435ce3b3 user: mistachkin tags: toTypeFuncs
2013-09-06
22:27
Merge updates from trunk. check-in: 8922be1a user: mistachkin tags: toTypeFuncs
21:41
Add the ability to generate assembly listing files using the MSVC makefile. check-in: 6caa2cd1 user: mistachkin tags: toTypeFuncs
13:10
Combine the FuncDef.iPrefEnc and FuncDef.flags fields into a single new FuncDef.funcFlags field. check-in: 97b10e66 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   367    367   #endif
   368    368   
   369    369     /* Return a pointer to the allocated object to the caller */
   370    370     sqlite3_result_blob(context, p, sizeof(p), sqlite3_free);
   371    371   }
   372    372   static const FuncDef statInitFuncdef = {
   373    373     1+IsStat34,      /* nArg */
   374         -  SQLITE_UTF8,     /* iPrefEnc */
   375         -  0,               /* flags */
          374  +  SQLITE_UTF8,     /* funcFlags */
   376    375     0,               /* pUserData */
   377    376     0,               /* pNext */
   378    377     statInit,        /* xFunc */
   379    378     0,               /* xStep */
   380    379     0,               /* xFinalize */
   381    380     "stat_init",     /* zName */
   382    381     0,               /* pHash */
   383    382     0                /* pDestructor */
   384    383   };
          384  +
          385  +#ifdef SQLITE_ENABLE_STAT4
          386  +/*
          387  +** pNew and pOld are both candidate non-periodic samples selected for 
          388  +** the same column (pNew->iCol==pOld->iCol). Ignoring this column and 
          389  +** considering only any trailing columns and the sample hash value, this
          390  +** function returns true if sample pNew is to be preferred over pOld.
          391  +** In other words, if we assume that the cardinalities of the selected
          392  +** column for pNew and pOld are equal, is pNew to be preferred over pOld.
          393  +**
          394  +** This function assumes that for each argument sample, the contents of
          395  +** the anEq[] array from pSample->anEq[pSample->iCol+1] onwards are valid. 
          396  +*/
          397  +static int sampleIsBetterPost(
          398  +  Stat4Accum *pAccum, 
          399  +  Stat4Sample *pNew, 
          400  +  Stat4Sample *pOld
          401  +){
          402  +  int nCol = pAccum->nCol;
          403  +  int i;
          404  +  assert( pNew->iCol==pOld->iCol );
          405  +  for(i=pNew->iCol+1; i<nCol; i++){
          406  +    if( pNew->anEq[i]>pOld->anEq[i] ) return 1;
          407  +    if( pNew->anEq[i]<pOld->anEq[i] ) return 0;
          408  +  }
          409  +  if( pNew->iHash>pOld->iHash ) return 1;
          410  +  return 0;
          411  +}
          412  +#endif
   385    413   
   386    414   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
   387    415   /*
   388    416   ** Return true if pNew is to be preferred over pOld.
          417  +**
          418  +** This function assumes that for each argument sample, the contents of
          419  +** the anEq[] array from pSample->anEq[pSample->iCol] onwards are valid. 
   389    420   */
   390         -static int sampleIsBetter(Stat4Sample *pNew, Stat4Sample *pOld){
          421  +static int sampleIsBetter(
          422  +  Stat4Accum *pAccum, 
          423  +  Stat4Sample *pNew, 
          424  +  Stat4Sample *pOld
          425  +){
   391    426     tRowcnt nEqNew = pNew->anEq[pNew->iCol];
   392    427     tRowcnt nEqOld = pOld->anEq[pOld->iCol];
   393    428   
   394    429     assert( pOld->isPSample==0 && pNew->isPSample==0 );
   395    430     assert( IsStat4 || (pNew->iCol==0 && pOld->iCol==0) );
   396    431   
   397         -  if( (nEqNew>nEqOld)
   398         -   || (nEqNew==nEqOld && pNew->iCol<pOld->iCol)
   399         -   || (nEqNew==nEqOld && pNew->iCol==pOld->iCol && pNew->iHash>pOld->iHash)
   400         -  ){
   401         -    return 1;
          432  +  if( (nEqNew>nEqOld) ) return 1;
          433  +#ifdef SQLITE_ENABLE_STAT4
          434  +  if( nEqNew==nEqOld ){
          435  +    if( pNew->iCol<pOld->iCol ) return 1;
          436  +    return (pNew->iCol==pOld->iCol && sampleIsBetterPost(pAccum, pNew, pOld));
   402    437     }
   403    438     return 0;
          439  +#else
          440  +  return (nEqNew==nEqOld && pNew->iHash>pOld->iHash);
          441  +#endif
   404    442   }
   405    443   
   406    444   /*
   407    445   ** Copy the contents of object (*pFrom) into (*pTo).
   408    446   */
   409    447   void sampleCopy(Stat4Accum *p, Stat4Sample *pTo, Stat4Sample *pFrom){
   410    448     pTo->iRowid = pFrom->iRowid;
................................................................................
   419    457   /*
   420    458   ** Copy the contents of sample *pNew into the p->a[] array. If necessary,
   421    459   ** remove the least desirable sample from p->a[] to make room.
   422    460   */
   423    461   static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){
   424    462     Stat4Sample *pSample;
   425    463     int i;
   426         -  i64 iSeq;
   427         -  int iPos;
   428    464   
   429    465     assert( IsStat4 || nEqZero==0 );
   430    466   
          467  +#ifdef SQLITE_ENABLE_STAT4
   431    468     if( pNew->isPSample==0 ){
   432    469       Stat4Sample *pUpgrade = 0;
   433    470       assert( pNew->anEq[pNew->iCol]>0 );
   434    471   
   435    472       /* This sample is being added because the prefix that ends in column 
   436    473       ** iCol occurs many times in the table. However, if we have already
   437    474       ** added a sample that shares this prefix, there is no need to add
   438    475       ** this one. Instead, upgrade the priority of the highest priority
   439    476       ** existing sample that shares this prefix.  */
   440    477       for(i=p->nSample-1; i>=0; i--){
   441    478         Stat4Sample *pOld = &p->a[i];
   442    479         if( pOld->anEq[pNew->iCol]==0 ){
   443    480           if( pOld->isPSample ) return;
   444         -        assert( sampleIsBetter(pNew, pOld) );
   445         -        if( pUpgrade==0 || sampleIsBetter(pOld, pUpgrade) ){
          481  +        assert( pOld->iCol>pNew->iCol );
          482  +        assert( sampleIsBetter(p, pNew, pOld) );
          483  +        if( pUpgrade==0 || sampleIsBetter(p, pOld, pUpgrade) ){
   446    484             pUpgrade = pOld;
   447    485           }
   448    486         }
   449    487       }
   450    488       if( pUpgrade ){
   451    489         pUpgrade->iCol = pNew->iCol;
   452    490         pUpgrade->anEq[pUpgrade->iCol] = pNew->anEq[pUpgrade->iCol];
   453    491         goto find_new_min;
   454    492       }
   455    493     }
          494  +#endif
   456    495   
   457    496     /* If necessary, remove sample iMin to make room for the new sample. */
   458    497     if( p->nSample>=p->mxSample ){
   459    498       Stat4Sample *pMin = &p->a[p->iMin];
   460    499       tRowcnt *anEq = pMin->anEq;
   461    500       tRowcnt *anLt = pMin->anLt;
   462    501       tRowcnt *anDLt = pMin->anDLt;
................................................................................
   464    503       pSample = &p->a[p->nSample-1];
   465    504       pSample->anEq = anEq;
   466    505       pSample->anDLt = anDLt;
   467    506       pSample->anLt = anLt;
   468    507       p->nSample = p->mxSample-1;
   469    508     }
   470    509   
   471         -  /* Figure out where in the a[] array the new sample should be inserted. */
   472         -  iSeq = pNew->anLt[p->nCol-1];
   473         -  for(iPos=p->nSample; iPos>0; iPos--){
   474         -    if( iSeq>p->a[iPos-1].anLt[p->nCol-1] ) break;
   475         -  }
          510  +  /* The "rows less-than" for the rowid column must be greater than that
          511  +  ** for the last sample in the p->a[] array. Otherwise, the samples would
          512  +  ** be out of order. */
          513  +#ifdef SQLITE_ENABLE_STAT4
          514  +  assert( p->nSample==0 
          515  +       || pNew->anLt[p->nCol-1] > p->a[p->nSample-1].anLt[p->nCol-1] );
          516  +#endif
   476    517   
   477    518     /* Insert the new sample */
   478         -  pSample = &p->a[iPos];
   479         -  if( iPos!=p->nSample ){
   480         -    Stat4Sample *pEnd = &p->a[p->nSample];
   481         -    tRowcnt *anEq = pEnd->anEq;
   482         -    tRowcnt *anLt = pEnd->anLt;
   483         -    tRowcnt *anDLt = pEnd->anDLt;
   484         -    memmove(&p->a[iPos], &p->a[iPos+1], (p->nSample-iPos)*sizeof(p->a[0]));
   485         -    pSample->anEq = anEq;
   486         -    pSample->anDLt = anDLt;
   487         -    pSample->anLt = anLt;
   488         -  }
   489         -  p->nSample++;
          519  +  pSample = &p->a[p->nSample];
   490    520     sampleCopy(p, pSample, pNew);
          521  +  p->nSample++;
   491    522   
   492    523     /* Zero the first nEqZero entries in the anEq[] array. */
   493    524     memset(pSample->anEq, 0, sizeof(tRowcnt)*nEqZero);
   494    525   
          526  +#ifdef SQLITE_ENABLE_STAT4
   495    527    find_new_min:
          528  +#endif
   496    529     if( p->nSample>=p->mxSample ){
   497    530       int iMin = -1;
   498    531       for(i=0; i<p->mxSample; i++){
   499    532         if( p->a[i].isPSample ) continue;
   500         -      if( iMin<0 || sampleIsBetter(&p->a[iMin], &p->a[i]) ){
          533  +      if( iMin<0 || sampleIsBetter(p, &p->a[iMin], &p->a[i]) ){
   501    534           iMin = i;
   502    535         }
   503    536       }
   504    537       assert( iMin>=0 );
   505    538       p->iMin = iMin;
   506    539     }
   507    540   }
................................................................................
   517    550   #ifdef SQLITE_ENABLE_STAT4
   518    551     int i;
   519    552   
   520    553     /* Check if any samples from the aBest[] array should be pushed
   521    554     ** into IndexSample.a[] at this point.  */
   522    555     for(i=(p->nCol-2); i>=iChng; i--){
   523    556       Stat4Sample *pBest = &p->aBest[i];
   524         -    if( p->nSample<p->mxSample
   525         -     || sampleIsBetter(pBest, &p->a[p->iMin])
   526         -    ){
          557  +    pBest->anEq[i] = p->current.anEq[i];
          558  +    if( p->nSample<p->mxSample || sampleIsBetter(p, pBest, &p->a[p->iMin]) ){
   527    559         sampleInsert(p, pBest, i);
   528    560       }
   529    561     }
   530    562   
   531    563     /* Update the anEq[] fields of any samples already collected. */
   532    564     for(i=p->nSample-1; i>=0; i--){
   533    565       int j;
................................................................................
   546    578       if( (nLt/p->nPSample)!=(nLt+nEq)/p->nPSample ){
   547    579         p->current.isPSample = 1;
   548    580         sampleInsert(p, &p->current, 0);
   549    581         p->current.isPSample = 0;
   550    582       }else 
   551    583   
   552    584       /* Or if it is a non-periodic sample. Add it in this case too. */
   553         -    if( p->nSample<p->mxSample || sampleIsBetter(&p->current, &p->a[p->iMin]) ){
          585  +    if( p->nSample<p->mxSample 
          586  +     || sampleIsBetter(p, &p->current, &p->a[p->iMin]) 
          587  +    ){
   554    588         sampleInsert(p, &p->current, 0);
   555    589       }
   556    590     }
   557    591   #endif
   558    592   }
   559    593   
   560    594   /*
................................................................................
   580    614     Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]);
   581    615     int iChng = sqlite3_value_int(argv[1]);
   582    616   
   583    617     assert( p->nCol>1 );        /* Includes rowid field */
   584    618     assert( iChng<p->nCol );
   585    619   
   586    620     if( p->nRow==0 ){
   587         -    /* anEq[0] is only zero for the very first call to this function.  Do
   588         -    ** appropriate initialization */
          621  +    /* This is the first call to this function. Do initialization. */
   589    622       for(i=0; i<p->nCol; i++) p->current.anEq[i] = 1;
   590    623     }else{
   591    624       /* Second and subsequent calls get processed here */
   592    625       samplePushPrevious(p, iChng);
   593    626   
   594    627       /* Update anDLt[], anLt[] and anEq[] to reflect the values that apply
   595    628       ** to the current row of the index. */
................................................................................
   621    654         sampleInsert(p, &p->current, p->nCol-1);
   622    655         p->current.isPSample = 0;
   623    656       }
   624    657   
   625    658       /* Update the aBest[] array. */
   626    659       for(i=0; i<(p->nCol-1); i++){
   627    660         p->current.iCol = i;
   628         -      if( i>=iChng || sampleIsBetter(&p->current, &p->aBest[i]) ){
          661  +      if( i>=iChng || sampleIsBetterPost(p, &p->current, &p->aBest[i]) ){
   629    662           sampleCopy(p, &p->aBest[i], &p->current);
   630    663         }
   631    664       }
   632    665     }
   633    666   #endif
   634    667   }
   635    668   static const FuncDef statPushFuncdef = {
   636    669     2+IsStat34,      /* nArg */
   637         -  SQLITE_UTF8,     /* iPrefEnc */
   638         -  0,               /* flags */
          670  +  SQLITE_UTF8,     /* funcFlags */
   639    671     0,               /* pUserData */
   640    672     0,               /* pNext */
   641    673     statPush,        /* xFunc */
   642    674     0,               /* xStep */
   643    675     0,               /* xFinalize */
   644    676     "stat_push",     /* zName */
   645    677     0,               /* pHash */
................................................................................
   766    798         }
   767    799       }
   768    800     }
   769    801   #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
   770    802   }
   771    803   static const FuncDef statGetFuncdef = {
   772    804     1+IsStat34,      /* nArg */
   773         -  SQLITE_UTF8,     /* iPrefEnc */
   774         -  0,               /* flags */
          805  +  SQLITE_UTF8,     /* funcFlags */
   775    806     0,               /* pUserData */
   776    807     0,               /* pNext */
   777    808     statGet,         /* xFunc */
   778    809     0,               /* xStep */
   779    810     0,               /* xFinalize */
   780    811     "stat_get",      /* zName */
   781    812     0,               /* pHash */

Changes to src/attach.c.

   375    375   ** Called by the parser to compile a DETACH statement.
   376    376   **
   377    377   **     DETACH pDbname
   378    378   */
   379    379   void sqlite3Detach(Parse *pParse, Expr *pDbname){
   380    380     static const FuncDef detach_func = {
   381    381       1,                /* nArg */
   382         -    SQLITE_UTF8,      /* iPrefEnc */
   383         -    0,                /* flags */
          382  +    SQLITE_UTF8,      /* funcFlags */
   384    383       0,                /* pUserData */
   385    384       0,                /* pNext */
   386    385       detachFunc,       /* xFunc */
   387    386       0,                /* xStep */
   388    387       0,                /* xFinalize */
   389    388       "sqlite_detach",  /* zName */
   390    389       0,                /* pHash */
................................................................................
   397    396   ** Called by the parser to compile an ATTACH statement.
   398    397   **
   399    398   **     ATTACH p AS pDbname KEY pKey
   400    399   */
   401    400   void sqlite3Attach(Parse *pParse, Expr *p, Expr *pDbname, Expr *pKey){
   402    401     static const FuncDef attach_func = {
   403    402       3,                /* nArg */
   404         -    SQLITE_UTF8,      /* iPrefEnc */
   405         -    0,                /* flags */
          403  +    SQLITE_UTF8,      /* funcFlags */
   406    404       0,                /* pUserData */
   407    405       0,                /* pNext */
   408    406       attachFunc,       /* xFunc */
   409    407       0,                /* xStep */
   410    408       0,                /* xFinalize */
   411    409       "sqlite_attach",  /* zName */
   412    410       0,                /* pHash */

Changes to src/callback.c.

   266    266     if( p->nArg==nArg ){
   267    267       match = 4;
   268    268     }else{
   269    269       match = 1;
   270    270     }
   271    271   
   272    272     /* Bonus points if the text encoding matches */
   273         -  if( enc==p->iPrefEnc ){
          273  +  if( enc==(p->funcFlags & SQLITE_FUNC_ENCMASK) ){
   274    274       match += 2;  /* Exact encoding match */
   275         -  }else if( (enc & p->iPrefEnc & 2)!=0 ){
          275  +  }else if( (enc & p->funcFlags & 2)!=0 ){
   276    276       match += 1;  /* Both are UTF16, but with different byte orders */
   277    277     }
   278    278   
   279    279     return match;
   280    280   }
   281    281   
   282    282   /*
................................................................................
   402    402     ** exact match for the name, number of arguments and encoding, then add a
   403    403     ** new entry to the hash table and return it.
   404    404     */
   405    405     if( createFlag && bestScore<FUNC_PERFECT_MATCH && 
   406    406         (pBest = sqlite3DbMallocZero(db, sizeof(*pBest)+nName+1))!=0 ){
   407    407       pBest->zName = (char *)&pBest[1];
   408    408       pBest->nArg = (u16)nArg;
   409         -    pBest->iPrefEnc = enc;
          409  +    pBest->funcFlags = enc;
   410    410       memcpy(pBest->zName, zName, nName);
   411    411       pBest->zName[nName] = 0;
   412    412       sqlite3FuncDefInsert(&db->aFunc, pBest);
   413    413     }
   414    414   
   415    415     if( pBest && (pBest->xStep || pBest->xFunc || createFlag) ){
   416    416       return pBest;

Changes to src/delete.c.

   532    532       ** being deleted. Do not attempt to delete the row a second time, and 
   533    533       ** do not fire AFTER triggers.  */
   534    534       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);
   535    535   
   536    536       /* Do FK processing. This call checks that any FK constraints that
   537    537       ** refer to this table (i.e. constraints attached to other tables) 
   538    538       ** are not violated by deleting this row.  */
   539         -    sqlite3FkCheck(pParse, pTab, iOld, 0);
          539  +    sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0);
   540    540     }
   541    541   
   542    542     /* Delete the index and table entries. Skip this step if pTab is really
   543    543     ** a view (in which case the only effect of the DELETE statement is to
   544    544     ** fire the INSTEAD OF triggers).  */ 
   545    545     if( pTab->pSelect==0 ){
   546    546       sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);
................................................................................
   549    549         sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT);
   550    550       }
   551    551     }
   552    552   
   553    553     /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
   554    554     ** handle rows (possibly in other tables) that refer via a foreign key
   555    555     ** to the row just deleted. */ 
   556         -  sqlite3FkActions(pParse, pTab, 0, iOld);
          556  +  sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0);
   557    557   
   558    558     /* Invoke AFTER DELETE trigger programs. */
   559    559     sqlite3CodeRowTrigger(pParse, pTrigger, 
   560    560         TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel
   561    561     );
   562    562   
   563    563     /* Jump here if the row had already been deleted before any BEFORE

Changes to src/expr.c.

  2630   2630           break;
  2631   2631         }
  2632   2632   
  2633   2633         /* Attempt a direct implementation of the built-in COALESCE() and
  2634   2634         ** IFNULL() functions.  This avoids unnecessary evalation of
  2635   2635         ** arguments past the first non-NULL argument.
  2636   2636         */
  2637         -      if( pDef->flags & SQLITE_FUNC_COALESCE ){
         2637  +      if( pDef->funcFlags & SQLITE_FUNC_COALESCE ){
  2638   2638           int endCoalesce = sqlite3VdbeMakeLabel(v);
  2639   2639           assert( nFarg>=2 );
  2640   2640           sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
  2641   2641           for(i=1; i<nFarg; i++){
  2642   2642             sqlite3VdbeAddOp2(v, OP_NotNull, target, endCoalesce);
  2643   2643             sqlite3ExprCacheRemove(pParse, target, 1);
  2644   2644             sqlite3ExprCachePush(pParse);
................................................................................
  2654   2654           r1 = sqlite3GetTempRange(pParse, nFarg);
  2655   2655   
  2656   2656           /* For length() and typeof() functions with a column argument,
  2657   2657           ** set the P5 parameter to the OP_Column opcode to OPFLAG_LENGTHARG
  2658   2658           ** or OPFLAG_TYPEOFARG respectively, to avoid unnecessary data
  2659   2659           ** loading.
  2660   2660           */
  2661         -        if( (pDef->flags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){
         2661  +        if( (pDef->funcFlags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){
  2662   2662             u8 exprOp;
  2663   2663             assert( nFarg==1 );
  2664   2664             assert( pFarg->a[0].pExpr!=0 );
  2665   2665             exprOp = pFarg->a[0].pExpr->op;
  2666   2666             if( exprOp==TK_COLUMN || exprOp==TK_AGG_COLUMN ){
  2667   2667               assert( SQLITE_FUNC_LENGTH==OPFLAG_LENGTHARG );
  2668   2668               assert( SQLITE_FUNC_TYPEOF==OPFLAG_TYPEOFARG );
  2669         -            testcase( pDef->flags==SQLITE_FUNC_LENGTH );
  2670         -            pFarg->a[0].pExpr->op2 = pDef->flags;
         2669  +            testcase( (pDef->funcFlags&~SQLITE_FUNC_ENCMASK)
         2670  +                       ==SQLITE_FUNC_LENGTH );
         2671  +            pFarg->a[0].pExpr->op2 = pDef->funcFlags&~SQLITE_FUNC_ENCMASK;
  2671   2672             }
  2672   2673           }
  2673   2674   
  2674   2675           sqlite3ExprCachePush(pParse);     /* Ticket 2ea2425d34be */
  2675   2676           sqlite3ExprCodeExprList(pParse, pFarg, r1, 1);
  2676   2677           sqlite3ExprCachePop(pParse, 1);   /* Ticket 2ea2425d34be */
  2677   2678         }else{
................................................................................
  2696   2697           pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr);
  2697   2698         }
  2698   2699   #endif
  2699   2700         for(i=0; i<nFarg; i++){
  2700   2701           if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
  2701   2702             constMask |= (1<<i);
  2702   2703           }
  2703         -        if( (pDef->flags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
         2704  +        if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
  2704   2705             pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr);
  2705   2706           }
  2706   2707         }
  2707         -      if( pDef->flags & SQLITE_FUNC_NEEDCOLL ){
         2708  +      if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  2708   2709           if( !pColl ) pColl = db->pDfltColl; 
  2709   2710           sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
  2710   2711         }
  2711   2712         sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target,
  2712   2713                           (char*)pDef, P4_FUNCDEF);
  2713   2714         sqlite3VdbeChangeP5(v, (u8)nFarg);
  2714   2715         if( nFarg ){

Changes to src/fkey.c.

   678    678   
   679    679       if( iSkip ){
   680    680         sqlite3VdbeResolveLabel(v, iSkip);
   681    681       }
   682    682     }
   683    683   }
   684    684   
          685  +
          686  +/*
          687  +** The second argument points to an FKey object representing a foreign key
          688  +** for which pTab is the child table. An UPDATE statement against pTab
          689  +** is currently being processed. For each column of the table that is 
          690  +** actually updated, the corresponding element in the aChange[] array
          691  +** is zero or greater (if a column is unmodified the corresponding element
          692  +** is set to -1). If the rowid column is modified by the UPDATE statement
          693  +** the bChngRowid argument is non-zero.
          694  +**
          695  +** This function returns true if any of the columns that are part of the
          696  +** child key for FK constraint *p are modified.
          697  +*/
          698  +static int fkChildIsModified(
          699  +  Table *pTab,                    /* Table being updated */
          700  +  FKey *p,                        /* Foreign key for which pTab is the child */
          701  +  int *aChange,                   /* Array indicating modified columns */
          702  +  int bChngRowid                  /* True if rowid is modified by this update */
          703  +){
          704  +  int i;
          705  +  for(i=0; i<p->nCol; i++){
          706  +    int iChildKey = p->aCol[i].iFrom;
          707  +    if( aChange[iChildKey]>=0 ) return 1;
          708  +    if( iChildKey==pTab->iPKey && bChngRowid ) return 1;
          709  +  }
          710  +  return 0;
          711  +}
          712  +
          713  +/*
          714  +** The second argument points to an FKey object representing a foreign key
          715  +** for which pTab is the parent table. An UPDATE statement against pTab
          716  +** is currently being processed. For each column of the table that is 
          717  +** actually updated, the corresponding element in the aChange[] array
          718  +** is zero or greater (if a column is unmodified the corresponding element
          719  +** is set to -1). If the rowid column is modified by the UPDATE statement
          720  +** the bChngRowid argument is non-zero.
          721  +**
          722  +** This function returns true if any of the columns that are part of the
          723  +** parent key for FK constraint *p are modified.
          724  +*/
          725  +static int fkParentIsModified(
          726  +  Table *pTab, 
          727  +  FKey *p, 
          728  +  int *aChange, 
          729  +  int bChngRowid
          730  +){
          731  +  int i;
          732  +  for(i=0; i<p->nCol; i++){
          733  +    char *zKey = p->aCol[i].zCol;
          734  +    int iKey;
          735  +    for(iKey=0; iKey<pTab->nCol; iKey++){
          736  +      if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){
          737  +        Column *pCol = &pTab->aCol[iKey];
          738  +        if( zKey ){
          739  +          if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1;
          740  +        }else if( pCol->colFlags & COLFLAG_PRIMKEY ){
          741  +          return 1;
          742  +        }
          743  +      }
          744  +    }
          745  +  }
          746  +  return 0;
          747  +}
          748  +
   685    749   /*
   686    750   ** This function is called when inserting, deleting or updating a row of
   687    751   ** table pTab to generate VDBE code to perform foreign key constraint 
   688    752   ** processing for the operation.
   689    753   **
   690    754   ** For a DELETE operation, parameter regOld is passed the index of the
   691    755   ** first register in an array of (pTab->nCol+1) registers containing the
................................................................................
   702    766   ** described for DELETE. Then again after the original record is deleted
   703    767   ** but before the new record is inserted using the INSERT convention. 
   704    768   */
   705    769   void sqlite3FkCheck(
   706    770     Parse *pParse,                  /* Parse context */
   707    771     Table *pTab,                    /* Row is being deleted from this table */ 
   708    772     int regOld,                     /* Previous row data is stored here */
   709         -  int regNew                      /* New row data is stored here */
          773  +  int regNew,                     /* New row data is stored here */
          774  +  int *aChange,                   /* Array indicating UPDATEd columns (or 0) */
          775  +  int bChngRowid                  /* True if rowid is UPDATEd */
   710    776   ){
   711    777     sqlite3 *db = pParse->db;       /* Database handle */
   712    778     FKey *pFKey;                    /* Used to iterate through FKs */
   713    779     int iDb;                        /* Index of database containing pTab */
   714    780     const char *zDb;                /* Name of database containing pTab */
   715    781     int isIgnoreErrors = pParse->disableTriggers;
   716    782   
................................................................................
   729    795       Table *pTo;                   /* Parent table of foreign key pFKey */
   730    796       Index *pIdx = 0;              /* Index on key columns in pTo */
   731    797       int *aiFree = 0;
   732    798       int *aiCol;
   733    799       int iCol;
   734    800       int i;
   735    801       int isIgnore = 0;
          802  +
          803  +    if( aChange 
          804  +     && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0
          805  +     && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 
          806  +    ){
          807  +      continue;
          808  +    }
   736    809   
   737    810       /* Find the parent table of this foreign key. Also find a unique index 
   738    811       ** on the parent key columns in the parent table. If either of these 
   739    812       ** schema items cannot be located, set an error in pParse and return 
   740    813       ** early.  */
   741    814       if( pParse->disableTriggers ){
   742    815         pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
................................................................................
   811    884     }
   812    885   
   813    886     /* Loop through all the foreign key constraints that refer to this table */
   814    887     for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
   815    888       Index *pIdx = 0;              /* Foreign key index for pFKey */
   816    889       SrcList *pSrc;
   817    890       int *aiCol = 0;
          891  +
          892  +    if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){
          893  +      continue;
          894  +    }
   818    895   
   819    896       if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) 
   820    897        && !pParse->pToplevel && !pParse->isMultiWrite 
   821    898       ){
   822    899         assert( regOld==0 && regNew!=0 );
   823    900         /* Inserting a single row into a parent table cannot cause an immediate
   824    901         ** foreign key violation. So do nothing in this case.  */
................................................................................
   884    961         if( pIdx ){
   885    962           for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
   886    963         }
   887    964       }
   888    965     }
   889    966     return mask;
   890    967   }
          968  +
   891    969   
   892    970   /*
   893    971   ** This function is called before generating code to update or delete a 
   894    972   ** row contained in table pTab. If the operation is a DELETE, then
   895    973   ** parameter aChange is passed a NULL value. For an UPDATE, aChange points
   896    974   ** to an array of size N, where N is the number of columns in table pTab.
   897    975   ** If the i'th column is not modified by the UPDATE, then the corresponding 
................................................................................
   914    992         /* A DELETE operation. Foreign key processing is required if the 
   915    993         ** table in question is either the child or parent table for any 
   916    994         ** foreign key constraint.  */
   917    995         return (sqlite3FkReferences(pTab) || pTab->pFKey);
   918    996       }else{
   919    997         /* This is an UPDATE. Foreign key processing is only required if the
   920    998         ** operation modifies one or more child or parent key columns. */
   921         -      int i;
   922    999         FKey *p;
   923   1000   
   924   1001         /* Check if any child key columns are being modified. */
   925   1002         for(p=pTab->pFKey; p; p=p->pNextFrom){
   926         -        for(i=0; i<p->nCol; i++){
   927         -          int iChildKey = p->aCol[i].iFrom;
   928         -          if( aChange[iChildKey]>=0 ) return 1;
   929         -          if( iChildKey==pTab->iPKey && chngRowid ) return 1;
   930         -        }
         1003  +        if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1;
   931   1004         }
   932   1005   
   933   1006         /* Check if any parent key columns are being modified. */
   934   1007         for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
   935         -        for(i=0; i<p->nCol; i++){
   936         -          char *zKey = p->aCol[i].zCol;
   937         -          int iKey;
   938         -          for(iKey=0; iKey<pTab->nCol; iKey++){
   939         -            Column *pCol = &pTab->aCol[iKey];
   940         -            if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey)
   941         -                      : (pCol->colFlags & COLFLAG_PRIMKEY)!=0) ){
   942         -              if( aChange[iKey]>=0 ) return 1;
   943         -              if( iKey==pTab->iPKey && chngRowid ) return 1;
   944         -            }
   945         -          }
   946         -        }
         1008  +        if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1;
   947   1009         }
   948   1010       }
   949   1011     }
   950   1012     return 0;
   951   1013   }
   952   1014   
   953   1015   /*
................................................................................
  1165   1227   ** This function is called when deleting or updating a row to implement
  1166   1228   ** any required CASCADE, SET NULL or SET DEFAULT actions.
  1167   1229   */
  1168   1230   void sqlite3FkActions(
  1169   1231     Parse *pParse,                  /* Parse context */
  1170   1232     Table *pTab,                    /* Table being updated or deleted from */
  1171   1233     ExprList *pChanges,             /* Change-list for UPDATE, NULL for DELETE */
  1172         -  int regOld                      /* Address of array containing old row */
         1234  +  int regOld,                     /* Address of array containing old row */
         1235  +  int *aChange,                   /* Array indicating UPDATEd columns (or 0) */
         1236  +  int bChngRowid                  /* True if rowid is UPDATEd */
  1173   1237   ){
  1174   1238     /* If foreign-key support is enabled, iterate through all FKs that 
  1175   1239     ** refer to table pTab. If there is an action associated with the FK 
  1176   1240     ** for this operation (either update or delete), invoke the associated 
  1177   1241     ** trigger sub-program.  */
  1178   1242     if( pParse->db->flags&SQLITE_ForeignKeys ){
  1179   1243       FKey *pFKey;                  /* Iterator variable */
  1180   1244       for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
  1181         -      Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
  1182         -      if( pAction ){
  1183         -        sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
         1245  +      if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){
         1246  +        Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges);
         1247  +        if( pAct ){
         1248  +          sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0);
         1249  +        }
  1184   1250         }
  1185   1251       }
  1186   1252     }
  1187   1253   }
  1188   1254   
  1189   1255   #endif /* ifndef SQLITE_OMIT_TRIGGER */
  1190   1256   

Changes to src/func.c.

  1689   1689   ** Set the LIKEOPT flag on the 2-argument function with the given name.
  1690   1690   */
  1691   1691   static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
  1692   1692     FuncDef *pDef;
  1693   1693     pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName),
  1694   1694                                2, SQLITE_UTF8, 0);
  1695   1695     if( ALWAYS(pDef) ){
  1696         -    pDef->flags = flagVal;
         1696  +    pDef->funcFlags |= flagVal;
  1697   1697     }
  1698   1698   }
  1699   1699   
  1700   1700   /*
  1701   1701   ** Register the built-in LIKE and GLOB functions.  The caseSensitive
  1702   1702   ** parameter determines whether or not the LIKE operator is case
  1703   1703   ** sensitive.  GLOB is always case sensitive.
................................................................................
  1733   1733     ){
  1734   1734       return 0;
  1735   1735     }
  1736   1736     assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
  1737   1737     pDef = sqlite3FindFunction(db, pExpr->u.zToken, 
  1738   1738                                sqlite3Strlen30(pExpr->u.zToken),
  1739   1739                                2, SQLITE_UTF8, 0);
  1740         -  if( NEVER(pDef==0) || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){
         1740  +  if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){
  1741   1741       return 0;
  1742   1742     }
  1743   1743   
  1744   1744     /* The memcpy() statement assumes that the wildcard characters are
  1745   1745     ** the first three statements in the compareInfo structure.  The
  1746   1746     ** asserts() that follow verify that assumption
  1747   1747     */
  1748   1748     memcpy(aWc, pDef->pUserData, 3);
  1749   1749     assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
  1750   1750     assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
  1751   1751     assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
  1752         -  *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0;
         1752  +  *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0;
  1753   1753     return 1;
  1754   1754   }
  1755   1755   
  1756   1756   /*
  1757   1757   ** All all of the FuncDef structures in the aBuiltinFunc[] array above
  1758   1758   ** to the global function hash table.  This occurs at start-time (as
  1759   1759   ** a consequence of calling sqlite3_initialize()).
................................................................................
  1826   1826       FUNCTION(load_extension,     1, 0, 0, loadExt          ),
  1827   1827       FUNCTION(load_extension,     2, 0, 0, loadExt          ),
  1828   1828     #endif
  1829   1829       AGGREGATE(sum,               1, 0, 0, sumStep,         sumFinalize    ),
  1830   1830       AGGREGATE(total,             1, 0, 0, sumStep,         totalFinalize    ),
  1831   1831       AGGREGATE(avg,               1, 0, 0, sumStep,         avgFinalize    ),
  1832   1832    /* AGGREGATE(count,             0, 0, 0, countStep,       countFinalize  ), */
  1833         -    {0,SQLITE_UTF8,SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0},
         1833  +    {0,SQLITE_UTF8|SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0},
  1834   1834       AGGREGATE(count,             1, 0, 0, countStep,       countFinalize  ),
  1835   1835       AGGREGATE(group_concat,      1, 0, 0, groupConcatStep, groupConcatFinalize),
  1836   1836       AGGREGATE(group_concat,      2, 0, 0, groupConcatStep, groupConcatFinalize),
  1837   1837     
  1838   1838       LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  1839   1839     #ifdef SQLITE_CASE_SENSITIVE_LIKE
  1840   1840       LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),

Changes to src/insert.c.

  1027   1027       }else
  1028   1028   #endif
  1029   1029       {
  1030   1030         int isReplace;    /* Set to true if constraints may cause a replace */
  1031   1031         sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
  1032   1032             keyColumn>=0, 0, onError, endOfLoop, &isReplace
  1033   1033         );
  1034         -      sqlite3FkCheck(pParse, pTab, 0, regIns);
         1034  +      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
  1035   1035         sqlite3CompleteInsertion(
  1036   1036             pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
  1037   1037         );
  1038   1038       }
  1039   1039     }
  1040   1040   
  1041   1041     /* Update the count of rows that are inserted

Changes to src/main.c.

  1402   1402     
  1403   1403     /* Check if an existing function is being overridden or deleted. If so,
  1404   1404     ** and there are active VMs, then return SQLITE_BUSY. If a function
  1405   1405     ** is being overridden/deleted but there are no active VMs, allow the
  1406   1406     ** operation to continue but invalidate all precompiled statements.
  1407   1407     */
  1408   1408     p = sqlite3FindFunction(db, zFunctionName, nName, nArg, (u8)enc, 0);
  1409         -  if( p && p->iPrefEnc==enc && p->nArg==nArg ){
         1409  +  if( p && (p->funcFlags & SQLITE_FUNC_ENCMASK)==enc && p->nArg==nArg ){
  1410   1410       if( db->nVdbeActive ){
  1411   1411         sqlite3Error(db, SQLITE_BUSY, 
  1412   1412           "unable to delete/modify user-function due to active statements");
  1413   1413         assert( !db->mallocFailed );
  1414   1414         return SQLITE_BUSY;
  1415   1415       }else{
  1416   1416         sqlite3ExpirePreparedStatements(db);
................................................................................
  1427   1427     ** being replaced invoke the destructor function here. */
  1428   1428     functionDestroy(db, p);
  1429   1429   
  1430   1430     if( pDestructor ){
  1431   1431       pDestructor->nRef++;
  1432   1432     }
  1433   1433     p->pDestructor = pDestructor;
  1434         -  p->flags = 0;
         1434  +  p->funcFlags &= SQLITE_FUNC_ENCMASK;
  1435   1435     p->xFunc = xFunc;
  1436   1436     p->xStep = xStep;
  1437   1437     p->xFinalize = xFinal;
  1438   1438     p->pUserData = pUserData;
  1439   1439     p->nArg = (u16)nArg;
  1440   1440     return SQLITE_OK;
  1441   1441   }

Changes to src/select.c.

  3224   3224     pTab = p->pSrc->a[0].pTab;
  3225   3225     pExpr = p->pEList->a[0].pExpr;
  3226   3226     assert( pTab && !pTab->pSelect && pExpr );
  3227   3227   
  3228   3228     if( IsVirtual(pTab) ) return 0;
  3229   3229     if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  3230   3230     if( NEVER(pAggInfo->nFunc==0) ) return 0;
  3231         -  if( (pAggInfo->aFunc[0].pFunc->flags&SQLITE_FUNC_COUNT)==0 ) return 0;
         3231  +  if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0;
  3232   3232     if( pExpr->flags&EP_Distinct ) return 0;
  3233   3233   
  3234   3234     return pTab;
  3235   3235   }
  3236   3236   
  3237   3237   /*
  3238   3238   ** If the source-list item passed as an argument was augmented with an
................................................................................
  3821   3821         regAgg = 0;
  3822   3822       }
  3823   3823       if( pF->iDistinct>=0 ){
  3824   3824         addrNext = sqlite3VdbeMakeLabel(v);
  3825   3825         assert( nArg==1 );
  3826   3826         codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg);
  3827   3827       }
  3828         -    if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
         3828  +    if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  3829   3829         CollSeq *pColl = 0;
  3830   3830         struct ExprList_item *pItem;
  3831   3831         int j;
  3832   3832         assert( pList!=0 );  /* pList!=0 if pF->pFunc has NEEDCOLL */
  3833   3833         for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){
  3834   3834           pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
  3835   3835         }

Changes to src/shell.c.

  1190   1190             void *pData = sqlite3_malloc(3*nCol*sizeof(const char*) + 1);
  1191   1191             if( !pData ){
  1192   1192               rc = SQLITE_NOMEM;
  1193   1193             }else{
  1194   1194               char **azCols = (char **)pData;      /* Names of result columns */
  1195   1195               char **azVals = &azCols[nCol];       /* Results */
  1196   1196               int *aiTypes = (int *)&azVals[nCol]; /* Result types */
  1197         -            int i;
         1197  +            int i, x;
  1198   1198               assert(sizeof(int) <= sizeof(char *)); 
  1199   1199               /* save off ptrs to column names */
  1200   1200               for(i=0; i<nCol; i++){
  1201   1201                 azCols[i] = (char *)sqlite3_column_name(pStmt, i);
  1202   1202               }
  1203   1203               do{
  1204   1204                 /* extract the data and data types */
  1205   1205                 for(i=0; i<nCol; i++){
  1206         -                azVals[i] = (char *)sqlite3_column_text(pStmt, i);
  1207         -                aiTypes[i] = sqlite3_column_type(pStmt, i);
         1206  +                aiTypes[i] = x = sqlite3_column_type(pStmt, i);
         1207  +                if( x==SQLITE_BLOB && pArg->mode==MODE_Insert ){
         1208  +                  azVals[i] = "";
         1209  +                }else{
         1210  +                  azVals[i] = (char*)sqlite3_column_text(pStmt, i);
         1211  +                }
  1208   1212                   if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){
  1209   1213                     rc = SQLITE_NOMEM;
  1210   1214                     break; /* from for */
  1211   1215                   }
  1212   1216                 } /* end for */
  1213   1217   
  1214   1218                 /* if data and types extracted successfully... */

Changes to src/sqliteInt.h.

  1061   1061   ** Each SQL function is defined by an instance of the following
  1062   1062   ** structure.  A pointer to this structure is stored in the sqlite.aFunc
  1063   1063   ** hash table.  When multiple functions have the same name, the hash table
  1064   1064   ** points to a linked list of these structures.
  1065   1065   */
  1066   1066   struct FuncDef {
  1067   1067     i16 nArg;            /* Number of arguments.  -1 means unlimited */
  1068         -  u8 iPrefEnc;         /* Preferred text encoding (SQLITE_UTF8, 16LE, 16BE) */
  1069         -  u8 flags;            /* Some combination of SQLITE_FUNC_* */
         1068  +  u16 funcFlags;       /* Some combination of SQLITE_FUNC_* */
  1070   1069     void *pUserData;     /* User data parameter */
  1071   1070     FuncDef *pNext;      /* Next function with same name */
  1072   1071     void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */
  1073   1072     void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */
  1074   1073     void (*xFinalize)(sqlite3_context*);                /* Aggregate finalizer */
  1075   1074     char *zName;         /* SQL name of the function. */
  1076   1075     FuncDef *pHash;      /* Next with a different name but the same hash */
................................................................................
  1098   1097   };
  1099   1098   
  1100   1099   /*
  1101   1100   ** Possible values for FuncDef.flags.  Note that the _LENGTH and _TYPEOF
  1102   1101   ** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG.  There
  1103   1102   ** are assert() statements in the code to verify this.
  1104   1103   */
  1105         -#define SQLITE_FUNC_LIKE     0x01 /* Candidate for the LIKE optimization */
  1106         -#define SQLITE_FUNC_CASE     0x02 /* Case-sensitive LIKE-type function */
  1107         -#define SQLITE_FUNC_EPHEM    0x04 /* Ephemeral.  Delete with VDBE */
  1108         -#define SQLITE_FUNC_NEEDCOLL 0x08 /* sqlite3GetFuncCollSeq() might be called */
  1109         -#define SQLITE_FUNC_COUNT    0x10 /* Built-in count(*) aggregate */
  1110         -#define SQLITE_FUNC_COALESCE 0x20 /* Built-in coalesce() or ifnull() function */
  1111         -#define SQLITE_FUNC_LENGTH   0x40 /* Built-in length() function */
  1112         -#define SQLITE_FUNC_TYPEOF   0x80 /* Built-in typeof() function */
         1104  +#define SQLITE_FUNC_ENCMASK  0x003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */
         1105  +#define SQLITE_FUNC_LIKE     0x004 /* Candidate for the LIKE optimization */
         1106  +#define SQLITE_FUNC_CASE     0x008 /* Case-sensitive LIKE-type function */
         1107  +#define SQLITE_FUNC_EPHEM    0x010 /* Ephemeral.  Delete with VDBE */
         1108  +#define SQLITE_FUNC_NEEDCOLL 0x020 /* sqlite3GetFuncCollSeq() might be called */
         1109  +#define SQLITE_FUNC_LENGTH   0x040 /* Built-in length() function */
         1110  +#define SQLITE_FUNC_TYPEOF   0x080 /* Built-in typeof() function */
         1111  +#define SQLITE_FUNC_COUNT    0x100 /* Built-in count(*) aggregate */
         1112  +#define SQLITE_FUNC_COALESCE 0x200 /* Built-in coalesce() or ifnull() */
         1113  +#define SQLITE_FUNC_UNLIKELY 0x400 /* Built-in unlikely() function */
  1113   1114   
  1114   1115   /*
  1115   1116   ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
  1116   1117   ** used to create the initializers for the FuncDef structures.
  1117   1118   **
  1118   1119   **   FUNCTION(zName, nArg, iArg, bNC, xFunc)
  1119   1120   **     Used to create a scalar function definition of a function zName 
................................................................................
  1133   1134   **     that accepts nArg arguments and is implemented by a call to C 
  1134   1135   **     function likeFunc. Argument pArg is cast to a (void *) and made
  1135   1136   **     available as the function user-data (sqlite3_user_data()). The
  1136   1137   **     FuncDef.flags variable is set to the value passed as the flags
  1137   1138   **     parameter.
  1138   1139   */
  1139   1140   #define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  1140         -  {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL), \
         1141  +  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1141   1142      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
  1142   1143   #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  1143         -  {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
         1144  +  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
  1144   1145      SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0}
  1145   1146   #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  1146         -  {nArg, SQLITE_UTF8, bNC*SQLITE_FUNC_NEEDCOLL, \
         1147  +  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
  1147   1148      pArg, 0, xFunc, 0, 0, #zName, 0, 0}
  1148   1149   #define LIKEFUNC(zName, nArg, arg, flags) \
  1149         -  {nArg, SQLITE_UTF8, flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0}
         1150  +  {nArg, SQLITE_UTF8|flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0}
  1150   1151   #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \
  1151         -  {nArg, SQLITE_UTF8, nc*SQLITE_FUNC_NEEDCOLL, \
         1152  +  {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
  1152   1153      SQLITE_INT_TO_PTR(arg), 0, 0, xStep,xFinal,#zName,0,0}
  1153   1154   
  1154   1155   /*
  1155   1156   ** All current savepoints are stored in a linked list starting at
  1156   1157   ** sqlite3.pSavepoint. The first element in the list is the most recently
  1157   1158   ** opened savepoint. Savepoints are added to the list by the vdbe
  1158   1159   ** OP_Savepoint instruction.
................................................................................
  3205   3206   ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
  3206   3207   ** key functionality is available. If OMIT_TRIGGER is defined but
  3207   3208   ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
  3208   3209   ** this case foreign keys are parsed, but no other functionality is 
  3209   3210   ** provided (enforcement of FK constraints requires the triggers sub-system).
  3210   3211   */
  3211   3212   #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  3212         -  void sqlite3FkCheck(Parse*, Table*, int, int);
         3213  +  void sqlite3FkCheck(Parse*, Table*, int, int, int*, int);
  3213   3214     void sqlite3FkDropTable(Parse*, SrcList *, Table*);
  3214         -  void sqlite3FkActions(Parse*, Table*, ExprList*, int);
         3215  +  void sqlite3FkActions(Parse*, Table*, ExprList*, int, int*, int);
  3215   3216     int sqlite3FkRequired(Parse*, Table*, int*, int);
  3216   3217     u32 sqlite3FkOldmask(Parse*, Table*);
  3217   3218     FKey *sqlite3FkReferences(Table *);
  3218   3219   #else
  3219         -  #define sqlite3FkActions(a,b,c,d)
         3220  +  #define sqlite3FkActions(a,b,c,d,e,f)
  3220   3221     #define sqlite3FkCheck(a,b,c,d)
  3221   3222     #define sqlite3FkDropTable(a,b,c)
  3222         -  #define sqlite3FkOldmask(a,b)      0
  3223         -  #define sqlite3FkRequired(a,b,c,d) 0
         3223  +  #define sqlite3FkOldmask(a,b)          0
         3224  +  #define sqlite3FkRequired(a,b,c,d,e,f) 0
  3224   3225   #endif
  3225   3226   #ifndef SQLITE_OMIT_FOREIGN_KEY
  3226   3227     void sqlite3FkDelete(sqlite3 *, Table*);
  3227   3228     int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**);
  3228   3229   #else
  3229   3230     #define sqlite3FkDelete(a,b)
  3230   3231     #define sqlite3FkLocateIndex(a,b,c,d,e)

Changes to src/update.c.

   484    484   
   485    485       /* Do constraint checks. */
   486    486       sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
   487    487           aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);
   488    488   
   489    489       /* Do FK constraint checks. */
   490    490       if( hasFK ){
   491         -      sqlite3FkCheck(pParse, pTab, regOldRowid, 0);
          491  +      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngRowid);
   492    492       }
   493    493   
   494    494       /* Delete the index entries associated with the current record.  */
   495    495       j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
   496    496       sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
   497    497     
   498    498       /* If changing the record number, delete the old record.  */
   499    499       if( hasFK || chngRowid ){
   500    500         sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
   501    501       }
   502    502       sqlite3VdbeJumpHere(v, j1);
   503    503   
   504    504       if( hasFK ){
   505         -      sqlite3FkCheck(pParse, pTab, 0, regNewRowid);
          505  +      sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngRowid);
   506    506       }
   507    507     
   508    508       /* Insert the new index entries and the new record. */
   509    509       sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0);
   510    510   
   511    511       /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
   512    512       ** handle rows (possibly in other tables) that refer via a foreign key
   513    513       ** to the row just updated. */ 
   514    514       if( hasFK ){
   515         -      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
          515  +      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngRowid);
   516    516       }
   517    517     }
   518    518   
   519    519     /* Increment the row counter 
   520    520     */
   521    521     if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
   522    522       sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);

Changes to src/vdbe.c.

  1433   1433     ** the pointer to ctx.s so in case the user-function can use
  1434   1434     ** the already allocated buffer instead of allocating a new one.
  1435   1435     */
  1436   1436     sqlite3VdbeMemMove(&ctx.s, pOut);
  1437   1437     MemSetTypeFlag(&ctx.s, MEM_Null);
  1438   1438   
  1439   1439     ctx.fErrorOrAux = 0;
  1440         -  if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
         1440  +  if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  1441   1441       assert( pOp>aOp );
  1442   1442       assert( pOp[-1].p4type==P4_COLLSEQ );
  1443   1443       assert( pOp[-1].opcode==OP_CollSeq );
  1444   1444       ctx.pColl = pOp[-1].p4.pColl;
  1445   1445     }
  1446   1446     db->lastRowid = lastRowid;
  1447   1447     (*ctx.pFunc->xFunc)(&ctx, n, apVal); /* IMP: R-24505-23230 */
................................................................................
  5433   5433     ctx.s.z = 0;
  5434   5434     ctx.s.zMalloc = 0;
  5435   5435     ctx.s.xDel = 0;
  5436   5436     ctx.s.db = db;
  5437   5437     ctx.isError = 0;
  5438   5438     ctx.pColl = 0;
  5439   5439     ctx.skipFlag = 0;
  5440         -  if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
         5440  +  if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  5441   5441       assert( pOp>p->aOp );
  5442   5442       assert( pOp[-1].p4type==P4_COLLSEQ );
  5443   5443       assert( pOp[-1].opcode==OP_CollSeq );
  5444   5444       ctx.pColl = pOp[-1].p4.pColl;
  5445   5445     }
  5446   5446     (ctx.pFunc->xStep)(&ctx, n, apVal); /* IMP: R-24505-23230 */
  5447   5447     if( ctx.isError ){

Changes to src/vdbeaux.c.

   600    600   
   601    601   
   602    602   /*
   603    603   ** If the input FuncDef structure is ephemeral, then free it.  If
   604    604   ** the FuncDef is not ephermal, then do nothing.
   605    605   */
   606    606   static void freeEphemeralFunction(sqlite3 *db, FuncDef *pDef){
   607         -  if( ALWAYS(pDef) && (pDef->flags & SQLITE_FUNC_EPHEM)!=0 ){
          607  +  if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0 ){
   608    608       sqlite3DbFree(db, pDef);
   609    609     }
   610    610   }
   611    611   
   612    612   static void vdbeFreeOpArray(sqlite3 *, Op *, int);
   613    613   
   614    614   /*

Changes to src/vdbemem.c.

  1300   1300     Expr *pExpr,                    /* The expression to extract a value from */
  1301   1301     u8 affinity,                    /* Affinity to use */
  1302   1302     int iVal,                       /* Array element to populate */
  1303   1303     int *pbOk                       /* OUT: True if value was extracted */
  1304   1304   ){
  1305   1305     int rc = SQLITE_OK;
  1306   1306     sqlite3_value *pVal = 0;
         1307  +  sqlite3 *db = pParse->db;
         1308  +
  1307   1309   
  1308   1310     struct ValueNewStat4Ctx alloc;
  1309   1311     alloc.pParse = pParse;
  1310   1312     alloc.pIdx = pIdx;
  1311   1313     alloc.ppRec = ppRec;
  1312   1314     alloc.iVal = iVal;
  1313   1315   
  1314   1316     /* Skip over any TK_COLLATE nodes */
  1315   1317     pExpr = sqlite3ExprSkipCollate(pExpr);
  1316   1318   
  1317   1319     if( !pExpr ){
  1318         -    pVal = valueNew(pParse->db, &alloc);
         1320  +    pVal = valueNew(db, &alloc);
  1319   1321       if( pVal ){
  1320   1322         sqlite3VdbeMemSetNull((Mem*)pVal);
  1321   1323         *pbOk = 1;
  1322   1324       }
  1323   1325     }else if( pExpr->op==TK_VARIABLE
  1324   1326           || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
  1325   1327     ){
  1326   1328       Vdbe *v;
  1327   1329       int iBindVar = pExpr->iColumn;
  1328   1330       sqlite3VdbeSetVarmask(pParse->pVdbe, iBindVar);
  1329   1331       if( (v = pParse->pReprepare)!=0 ){
  1330         -      pVal = valueNew(pParse->db, &alloc);
         1332  +      pVal = valueNew(db, &alloc);
  1331   1333         if( pVal ){
  1332   1334           rc = sqlite3VdbeMemCopy((Mem*)pVal, &v->aVar[iBindVar-1]);
  1333   1335           if( rc==SQLITE_OK ){
  1334         -          sqlite3ValueApplyAffinity(pVal, affinity, SQLITE_UTF8);
         1336  +          sqlite3ValueApplyAffinity(pVal, affinity, ENC(db));
  1335   1337           }
  1336   1338           pVal->db = pParse->db;
  1337   1339           *pbOk = 1;
  1338   1340           sqlite3VdbeMemStoreType((Mem*)pVal);
  1339   1341         }
  1340   1342       }else{
  1341   1343         *pbOk = 0;
  1342   1344       }
  1343   1345     }else{
  1344         -    sqlite3 *db = pParse->db;
  1345   1346       rc = valueFromExpr(db, pExpr, ENC(db), affinity, &pVal, &alloc);
  1346   1347       *pbOk = (pVal!=0);
  1347   1348     }
  1348   1349   
  1349         -  assert( pVal==0 || pVal->db==pParse->db );
         1350  +  assert( pVal==0 || pVal->db==db );
  1350   1351     return rc;
  1351   1352   }
  1352   1353   
  1353   1354   /*
  1354   1355   ** Unless it is NULL, the argument must be an UnpackedRecord object returned
  1355   1356   ** by an earlier call to sqlite3Stat4ProbeSetValue(). This call deletes
  1356   1357   ** the object.

Changes to src/vtab.c.

  1009   1009       return pDef;
  1010   1010     }
  1011   1011     *pNew = *pDef;
  1012   1012     pNew->zName = (char *)&pNew[1];
  1013   1013     memcpy(pNew->zName, pDef->zName, sqlite3Strlen30(pDef->zName)+1);
  1014   1014     pNew->xFunc = xFunc;
  1015   1015     pNew->pUserData = pArg;
  1016         -  pNew->flags |= SQLITE_FUNC_EPHEM;
         1016  +  pNew->funcFlags |= SQLITE_FUNC_EPHEM;
  1017   1017     return pNew;
  1018   1018   }
  1019   1019   
  1020   1020   /*
  1021   1021   ** Make sure virtual table pTab is contained in the pParse->apVirtualLock[]
  1022   1022   ** array so that an OP_VBegin will get generated for it.  Add pTab to the
  1023   1023   ** array if it is missing.  If pTab is already in the array, this routine

Changes to src/where.c.

  2417   2417     Parse *pParse,              /* Database connection */
  2418   2418     Index *pIdx,                /* Index to consider domain of */
  2419   2419     UnpackedRecord *pRec,       /* Vector of values to consider */
  2420   2420     int roundUp,                /* Round up if true.  Round down if false */
  2421   2421     tRowcnt *aStat              /* OUT: stats written here */
  2422   2422   ){
  2423   2423     IndexSample *aSample = pIdx->aSample;
  2424         -  int iCol = pRec->nField-1;  /* Index of required stats in anEq[] etc. */
         2424  +  int iCol;                   /* Index of required stats in anEq[] etc. */
  2425   2425     int iMin = 0;               /* Smallest sample not yet tested */
  2426   2426     int i = pIdx->nSample;      /* Smallest sample larger than or equal to pRec */
  2427   2427     int iTest;                  /* Next sample to test */
  2428   2428     int res;                    /* Result of comparison operation */
  2429   2429   
         2430  +  assert( pRec!=0 || pParse->db->mallocFailed );
         2431  +  if( pRec==0 ) return;
         2432  +  iCol = pRec->nField - 1;
  2430   2433     assert( pIdx->nSample>0 );
  2431   2434     assert( pRec->nField>0 && iCol<pIdx->nSampleCol );
  2432   2435     do{
  2433   2436       iTest = (iMin+i)/2;
  2434   2437       res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec);
  2435   2438       if( res<0 ){
  2436   2439         iMin = iTest+1;
................................................................................
  2542   2545     int rc = SQLITE_OK;
  2543   2546     int nOut = (int)*pnOut;
  2544   2547   
  2545   2548   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  2546   2549     Index *p = pBuilder->pNew->u.btree.pIndex;
  2547   2550     int nEq = pBuilder->pNew->u.btree.nEq;
  2548   2551   
  2549         -  if( nEq==pBuilder->nRecValid
         2552  +  if( p->nSample>0
         2553  +   && nEq==pBuilder->nRecValid
  2550   2554      && nEq<p->nSampleCol
  2551         -   && p->nSample 
  2552   2555      && OptimizationEnabled(pParse->db, SQLITE_Stat3) 
  2553   2556     ){
  2554   2557       UnpackedRecord *pRec = pBuilder->pRec;
  2555   2558       tRowcnt a[2];
  2556         -    u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
         2559  +    u8 aff;
  2557   2560   
  2558   2561       /* Variable iLower will be set to the estimate of the number of rows in 
  2559   2562       ** the index that are less than the lower bound of the range query. The
  2560   2563       ** lower bound being the concatenation of $P and $L, where $P is the
  2561   2564       ** key-prefix formed by the nEq values matched against the nEq left-most
  2562   2565       ** columns of the index, and $L is the value in pLower.
  2563   2566       **
................................................................................
  2571   2574       ** less than the upper bound of the range query. Where the upper bound
  2572   2575       ** is either ($P) or ($P:$U). Again, even if $U is available, both values
  2573   2576       ** of iUpper are requested of whereKeyStats() and the smaller used.
  2574   2577       */
  2575   2578       tRowcnt iLower;
  2576   2579       tRowcnt iUpper;
  2577   2580   
         2581  +    if( nEq==p->nColumn ){
         2582  +      aff = SQLITE_AFF_INTEGER;
         2583  +    }else{
         2584  +      aff = p->pTable->aCol[p->aiColumn[nEq]].affinity;
         2585  +    }
  2578   2586       /* Determine iLower and iUpper using ($P) only. */
  2579   2587       if( nEq==0 ){
  2580   2588         iLower = 0;
  2581   2589         iUpper = p->aiRowEst[0];
  2582   2590       }else{
  2583   2591         /* Note: this call could be optimized away - since the same values must 
  2584   2592         ** have been requested when testing key $P in whereEqualScanEst().  */
................................................................................
  4056   4064     return SQLITE_OK;
  4057   4065   }
  4058   4066   
  4059   4067   /*
  4060   4068   ** Transfer content from the second pLoop into the first.
  4061   4069   */
  4062   4070   static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){
  4063         -  if( whereLoopResize(db, pTo, pFrom->nLTerm) ) return SQLITE_NOMEM;
  4064   4071     whereLoopClearUnion(db, pTo);
         4072  +  if( whereLoopResize(db, pTo, pFrom->nLTerm) ){
         4073  +    memset(&pTo->u, 0, sizeof(pTo->u));
         4074  +    return SQLITE_NOMEM;
         4075  +  }
  4065   4076     memcpy(pTo, pFrom, WHERE_LOOP_XFER_SZ);
  4066   4077     memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0]));
  4067   4078     if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){
  4068   4079       pFrom->u.vtab.needFree = 0;
  4069   4080     }else if( (pFrom->wsFlags & WHERE_AUTO_INDEX)!=0 ){
  4070   4081       pFrom->u.btree.pIndex = 0;
  4071   4082     }
................................................................................
  4171   4182        && p->rRun<=pTemplate->rRun
  4172   4183        && p->nOut<=pTemplate->nOut
  4173   4184       ){
  4174   4185         /* This branch taken when p is equal or better than pTemplate in 
  4175   4186         ** all of (1) dependencies (2) setup-cost, (3) run-cost, and
  4176   4187         ** (4) number of output rows. */
  4177   4188         assert( p->rSetup==pTemplate->rSetup );
  4178         -      if( p->nLTerm<pTemplate->nLTerm
         4189  +      if( p->prereq==pTemplate->prereq
         4190  +       && p->nLTerm<pTemplate->nLTerm
  4179   4191          && (p->wsFlags & WHERE_INDEXED)!=0
  4180   4192          && (pTemplate->wsFlags & WHERE_INDEXED)!=0
  4181   4193          && p->u.btree.pIndex==pTemplate->u.btree.pIndex
  4182         -       && p->prereq==pTemplate->prereq
  4183   4194         ){
  4184   4195           /* Overwrite an existing WhereLoop with an similar one that uses
  4185   4196           ** more terms of the index */
  4186   4197           pNext = p->pNextLoop;
  4187   4198           break;
  4188   4199         }else{
  4189   4200           /* pTemplate is not helpful.
................................................................................
  5900   5911   #endif
  5901   5912     /* Attempt to omit tables from the join that do not effect the result */
  5902   5913     if( pWInfo->nLevel>=2
  5903   5914      && pResultSet!=0
  5904   5915      && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
  5905   5916     ){
  5906   5917       Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
  5907         -    if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
         5918  +    if( sWLB.pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, sWLB.pOrderBy);
  5908   5919       while( pWInfo->nLevel>=2 ){
  5909   5920         WhereTerm *pTerm, *pEnd;
  5910   5921         pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
  5911   5922         if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
  5912   5923         if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
  5913   5924          && (pLoop->wsFlags & WHERE_ONEROW)==0
  5914   5925         ){

Changes to test/analyze9.test.

   240    240       neq,
   241    241       lrange(nlt, 0, 2),
   242    242       lrange(ndlt, 0, 2),
   243    243       lrange(test_decode(sample), 0, 1)
   244    244       FROM sqlite_stat4
   245    245     ORDER BY rowid DESC LIMIT 2;
   246    246   } {
   247         -  {2 1 1 1} {295 296 296} {120 122 125} {201 4} 
          247  +  {2 1 1 1} {295 295 295} {120 121 124} {201 3} 
   248    248     {5 3 1 1} {290 290 292} {119 119 121} {200 1}
   249    249   }
   250    250   
   251    251   do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120
   252    252   do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119
   253    253   
   254    254   # Check that the perioidic samples are present.
................................................................................
   552    552       set sql {
   553    553         SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
   554    554       }
   555    555       do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
   556    556     }
   557    557   }
   558    558   
   559         -finish_test
          559  +#-------------------------------------------------------------------------
          560  +# Check that affinities are taken into account when using stat4 data to
          561  +# estimate the number of rows scanned by a rowid constraint.
          562  +#
          563  +drop_all_tables
          564  +do_test 13.1 {
          565  +  execsql {
          566  +    CREATE TABLE t1(a, b, c);
          567  +    CREATE INDEX i1 ON t1(a);
          568  +    CREATE INDEX i2 ON t1(b, c);
          569  +  }
          570  +  for {set i 0} {$i<100} {incr i} {
          571  +    if {$i %2} {set a abc} else {set a def}
          572  +    execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) }
          573  +  }
          574  +  execsql ANALYZE
          575  +} {}
          576  +do_eqp_test 13.2.1 {
          577  +  SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<20
          578  +} {/SEARCH TABLE t1 USING INDEX i1/}
          579  +do_eqp_test 13.2.2 {
          580  +  SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<20
          581  +} {/SEARCH TABLE t1 USING INDEX i1/}
          582  +do_eqp_test 13.3.1 {
          583  +  SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<20
          584  +} {/SEARCH TABLE t1 USING INDEX i2/}
          585  +do_eqp_test 13.3.2 {
          586  +  SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<20
          587  +} {/SEARCH TABLE t1 USING INDEX i2/}
          588  +
          589  +#-------------------------------------------------------------------------
          590  +# Check also that affinities are taken into account when using stat4 data 
          591  +# to estimate the number of rows scanned by any other constraint on a 
          592  +# column other than the leftmost.
          593  +#
          594  +drop_all_tables
          595  +do_test 14.1 {
          596  +  execsql { CREATE TABLE t1(a, b INTEGER, c) }
          597  +  for {set i 0} {$i<100} {incr i} {
          598  +    set c [expr $i % 3]
          599  +    execsql { INSERT INTO t1 VALUES('ott', $i, $c) }
          600  +  }
          601  +  execsql {
          602  +    CREATE INDEX i1 ON t1(a, b);
          603  +    CREATE INDEX i2 ON t1(c);
          604  +    ANALYZE;
          605  +  }
          606  +} {}
          607  +do_eqp_test 13.2.1 {
          608  +  SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1
          609  +} {/SEARCH TABLE t1 USING INDEX i1/}
          610  +do_eqp_test 13.2.2 {
          611  +  SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1
          612  +} {/SEARCH TABLE t1 USING INDEX i1/}
          613  +
          614  +#-------------------------------------------------------------------------
          615  +# By default, 16 non-periodic samples are collected for the stat4 table.
          616  +# The following tests attempt to verify that the most common keys are
          617  +# being collected.
          618  +#
          619  +proc check_stat4 {tn} {
          620  +  db eval ANALYZE
          621  +  db eval {SELECT a, b, c, d FROM t1} {
          622  +    incr k($a)
          623  +    incr k([list $a $b])
          624  +    incr k([list $a $b $c])
          625  +    if { [info exists k([list $a $b $c $d])]==0 } { incr nRow }
          626  +    incr k([list $a $b $c $d])
          627  +  }
          628  +
          629  +  set L [list]
          630  +  foreach key [array names k] {
          631  +    lappend L [list $k($key) $key]
          632  +  }
          633  +
          634  +  set nSample $nRow
          635  +  if {$nSample>16} {set nSample 16}
          636  +
          637  +  set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0]
          638  +  foreach key [array names k] {
          639  +    if {$k($key)>$nThreshold} {
          640  +      set expect($key) 1
          641  +    }
          642  +    if {$k($key)==$nThreshold} {
          643  +      set possible($key) 1
          644  +    }
          645  +  }
          646  +
          647  +
          648  +  set nPossible [expr $nSample - [llength [array names expect]]]
          649  +
          650  +  #puts "EXPECT: [array names expect]"
          651  +  #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]"
          652  +  #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]"
          653  +
          654  +  db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} {
          655  +    set seen 0
          656  +    for {set i 0} {$i<4} {incr i} {
          657  +      unset -nocomplain expect([lrange $s 0 $i])
          658  +      if {[info exists possible([lrange $s 0 $i])]} {
          659  +        set seen 1
          660  +        unset -nocomplain possible([lrange $s 0 $i])
          661  +      }
          662  +    }
          663  +    if {$seen} {incr nPossible -1}
          664  +  }
          665  +  if {$nPossible<0} {set nPossible 0}
          666  +
          667  +  set res [list [llength [array names expect]] $nPossible]
          668  +  uplevel [list do_test $tn [list set {} $res] {0 0}]
          669  +}
          670  +
          671  +drop_all_tables
          672  +do_test 14.1.1 {
          673  +  execsql {
          674  +    CREATE TABLE t1(a,b,c,d);
          675  +    CREATE INDEX i1 ON t1(a,b,c,d);
          676  +  }
          677  +  for {set i 0} {$i < 160} {incr i} {
          678  +    execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) }
          679  +    if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } }
          680  +  }
          681  +} {}
          682  +check_stat4 14.1.2
          683  +
          684  +do_test 14.2.1 {
          685  +  execsql { DELETE FROM t1 }
          686  +  for {set i 0} {$i < 1600} {incr i} {
          687  +    execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) }
          688  +  }
          689  +} {}
          690  +check_stat4 14.2.2
          691  +
          692  +do_test 14.3.1 {
          693  +  for {set i 0} {$i < 10} {incr i} {
          694  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          695  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          696  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          697  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          698  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          699  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          700  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          701  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          702  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          703  +    execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
          704  +  }
          705  +} {}
          706  +check_stat4 14.3.2
          707  +
          708  +do_test 14.4.1 {
          709  +  execsql {DELETE FROM t1}
          710  +  for {set i 1} {$i < 160} {incr i} {
          711  +    set b [expr $i % 10]
          712  +    if {$b==0 || $b==2} {set b 1}
          713  +    execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) }
          714  +  }
          715  +} {}
          716  +check_stat4 14.4.2
          717  +db func lrange lrange
          718  +db func lindex lindex
          719  +do_execsql_test 14.4.3 {
          720  +  SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4
          721  +  WHERE lindex(s, 1)=='1' ORDER BY rowid
          722  +} {
          723  +  {0 1} {1 1} {2 1} {3 1} 
          724  +  {4 1} {5 1} {6 1} {7 1} 
          725  +  {8 1} {9 1} {10 1} {11 1} 
          726  +  {12 1} {13 1} {14 1} {15 1}
          727  +}
          728  +
          729  +#-------------------------------------------------------------------------
          730  +# Test that nothing untoward happens if the stat4 table contains entries
          731  +# for indexes that do not exist. Or NULL values in the idx column.
          732  +# Or NULL values in any of the other columns.
          733  +#
          734  +drop_all_tables
          735  +do_execsql_test 15.1 {
          736  +  CREATE TABLE x1(a, b, UNIQUE(a, b));
          737  +  INSERT INTO x1 VALUES(1, 2);
          738  +  INSERT INTO x1 VALUES(3, 4);
          739  +  INSERT INTO x1 VALUES(5, 6);
          740  +  ANALYZE;
          741  +  INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL);
          742  +}
          743  +db close
          744  +sqlite3 db test.db
          745  +do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6}
          746  +
          747  +do_execsql_test 15.3 {
          748  +  INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42);
          749  +}
          750  +db close
          751  +sqlite3 db test.db
          752  +do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6}
          753  +
          754  +do_execsql_test 15.5 {
          755  +  UPDATE sqlite_stat1 SET stat = NULL;
          756  +}
          757  +db close
          758  +sqlite3 db test.db
          759  +do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6}
          760  +
          761  +do_execsql_test 15.7 {
          762  +  ANALYZE;
          763  +  UPDATE sqlite_stat1 SET tbl = 'no such tbl';
          764  +}
          765  +db close
          766  +sqlite3 db test.db
          767  +do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6}
          768  +
          769  +do_execsql_test 15.9 {
          770  +  ANALYZE;
          771  +  UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL;
          772  +}
          773  +db close
          774  +sqlite3 db test.db
          775  +do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6}
          776  +
          777  +# This is just for coverage....
          778  +do_execsql_test 15.11 {
          779  +  ANALYZE;
          780  +  UPDATE sqlite_stat1 SET stat = stat || ' unordered';
          781  +}
          782  +db close
          783  +sqlite3 db test.db
          784  +do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6}
          785  +
          786  +#-------------------------------------------------------------------------
          787  +# Test that allocations used for sqlite_stat4 samples are included in
          788  +# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED.
          789  +#
          790  +set one [string repeat x 1000]
          791  +set two [string repeat x 2000]
          792  +do_test 16.1 {
          793  +  reset_db
          794  +  execsql {
          795  +    CREATE TABLE t1(a, UNIQUE(a));
          796  +    INSERT INTO t1 VALUES($one);
          797  +    ANALYZE;
          798  +  }
          799  +  set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
          800  +
          801  +  reset_db
          802  +  execsql {
          803  +    CREATE TABLE t1(a, UNIQUE(a));
          804  +    INSERT INTO t1 VALUES($two);
          805  +    ANALYZE;
          806  +  }
          807  +  set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
          808  +
          809  +  expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050}
          810  +} {1}
          811  +
          812  +#-------------------------------------------------------------------------
          813  +# Test that stat4 data may be used with partial indexes.
          814  +#
          815  +do_test 17.1 {
          816  +  reset_db
          817  +  execsql {
          818  +    CREATE TABLE t1(a, b, c, d);
          819  +    CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
          820  +    INSERT INTO t1 VALUES(-1, -1, -1, NULL);
          821  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          822  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          823  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          824  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          825  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          826  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          827  +  }
          828  +
          829  +  for {set i 0} {$i < 32} {incr i} {
          830  +    if {$i<8} {set b 0} else { set b $i }
          831  +    execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') }
          832  +  }
          833  +  execsql {ANALYZE main.t1}
          834  +} {}
          835  +
          836  +do_catchsql_test 17.1.2 {
          837  +  ANALYZE temp.t1;
          838  +} {1 {no such table: temp.t1}}
          839  +
          840  +do_eqp_test 17.2 {
          841  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
          842  +} {/USING INDEX i1/}
          843  +do_eqp_test 17.3 {
          844  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
          845  +} {/USING INDEX i1/}
          846  +
          847  +do_execsql_test 17.4 {
          848  +  CREATE INDEX i2 ON t1(c);
          849  +  ANALYZE main.i2;
          850  +}
          851  +do_eqp_test 17.5 {
          852  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
          853  +} {/USING INDEX i1/}
          854  +do_eqp_test 17.6 {
          855  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
          856  +} {/USING INDEX i2/}
          857  +
          858  +#-------------------------------------------------------------------------
          859  +#
          860  +do_test 18.1 {
          861  +  reset_db
          862  +  execsql {
          863  +    CREATE TABLE t1(a, b);
          864  +    CREATE INDEX i1 ON t1(a, b);
          865  +  }
          866  +  for {set i 0} {$i < 9} {incr i} {
          867  +    execsql {
          868  +      INSERT INTO t1 VALUES($i, 0);
          869  +      INSERT INTO t1 VALUES($i, 0);
          870  +      INSERT INTO t1 VALUES($i, 0);
          871  +      INSERT INTO t1 VALUES($i, 0);
          872  +      INSERT INTO t1 VALUES($i, 0);
          873  +      INSERT INTO t1 VALUES($i, 0);
          874  +      INSERT INTO t1 VALUES($i, 0);
          875  +      INSERT INTO t1 VALUES($i, 0);
          876  +      INSERT INTO t1 VALUES($i, 0);
          877  +      INSERT INTO t1 VALUES($i, 0);
          878  +      INSERT INTO t1 VALUES($i, 0);
          879  +      INSERT INTO t1 VALUES($i, 0);
          880  +      INSERT INTO t1 VALUES($i, 0);
          881  +      INSERT INTO t1 VALUES($i, 0);
          882  +      INSERT INTO t1 VALUES($i, 0);
          883  +    }
          884  +  }
          885  +  execsql ANALYZE
          886  +  execsql { SELECT count(*) FROM sqlite_stat4 }
          887  +} {9}
          888  +
          889  +#-------------------------------------------------------------------------
          890  +# For coverage.
          891  +#
          892  +ifcapable view {
          893  +  do_test 19.1 {
          894  +    reset_db 
          895  +    execsql {
          896  +      CREATE TABLE t1(x, y);
          897  +      CREATE INDEX i1 ON t1(x, y);
          898  +      CREATE VIEW v1 AS SELECT * FROM t1;
          899  +      ANALYZE;
          900  +    }
          901  +  } {}
          902  +}
          903  +ifcapable auth {
          904  +  proc authproc {op args} {
          905  +    if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" }
          906  +    return "SQLITE_OK"
          907  +  }
          908  +  do_test 19.2 {
          909  +    reset_db 
          910  +    db auth authproc
          911  +    execsql {
          912  +      CREATE TABLE t1(x, y);
          913  +      CREATE VIEW v1 AS SELECT * FROM t1;
          914  +    }
          915  +    catchsql ANALYZE
          916  +  } {1 {not authorized}}
          917  +}
          918  +
          919  +#-------------------------------------------------------------------------
          920  +#
          921  +reset_db
          922  +proc r {args} { expr rand() }
          923  +db func r r
          924  +db func lrange lrange
          925  +do_test 20.1 {
          926  +  execsql {
          927  +    CREATE TABLE t1(a,b,c,d);
          928  +    CREATE INDEX i1 ON t1(a,b,c,d);
          929  +  }
          930  +  for {set i 0} {$i < 16} {incr i} {
          931  +    execsql {
          932  +      INSERT INTO t1 VALUES($i, r(), r(), r());
          933  +      INSERT INTO t1 VALUES($i, $i,  r(), r());
          934  +      INSERT INTO t1 VALUES($i, $i,  $i,  r());
          935  +      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
          936  +      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
          937  +      INSERT INTO t1 VALUES($i, $i,  $i,  r());
          938  +      INSERT INTO t1 VALUES($i, $i,  r(), r());
          939  +      INSERT INTO t1 VALUES($i, r(), r(), r());
          940  +    }
          941  +  }
          942  +} {}
          943  +do_execsql_test 20.2 { ANALYZE }
          944  +for {set i 0} {$i<16} {incr i} {
          945  +    set val "$i $i $i $i"
          946  +    do_execsql_test 20.3.$i {
          947  +      SELECT count(*) FROM sqlite_stat4 
          948  +      WHERE lrange(test_decode(sample), 0, 3)=$val
          949  +    } {1}
          950  +}
   560    951   
          952  +finish_test
   561    953   

Added test/analyzeB.test.

            1  +# 2013 August 3
            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  +#
           12  +# This file contains automated tests used to verify that the sqlite_stat3
           13  +# functionality is working. The tests in this file are based on a subset
           14  +# of the sqlite_stat4 tests in analyze9.test.
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +set testprefix analyzeB
           20  +
           21  +ifcapable !stat3 {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +do_execsql_test 1.0 {
           27  +  CREATE TABLE t1(a TEXT, b TEXT); 
           28  +  INSERT INTO t1 VALUES('(0)', '(0)');
           29  +  INSERT INTO t1 VALUES('(1)', '(1)');
           30  +  INSERT INTO t1 VALUES('(2)', '(2)');
           31  +  INSERT INTO t1 VALUES('(3)', '(3)');
           32  +  INSERT INTO t1 VALUES('(4)', '(4)');
           33  +  CREATE INDEX i1 ON t1(a, b);
           34  +} {}
           35  +
           36  +
           37  +do_execsql_test 1.1 {
           38  +  ANALYZE;
           39  +} {}
           40  +
           41  +do_execsql_test 1.2 {
           42  +  SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3;
           43  +} {
           44  +  t1 i1 1 0 0 '(0)'
           45  +  t1 i1 1 1 1 '(1)'
           46  +  t1 i1 1 2 2 '(2)'
           47  +  t1 i1 1 3 3 '(3)'
           48  +  t1 i1 1 4 4 '(4)'
           49  +}
           50  +
           51  +if {[permutation] != "utf16"} {
           52  +  do_execsql_test 1.3 {
           53  +    SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3;
           54  +  } {
           55  +    t1 i1 1 0 0 '(0)'
           56  +    t1 i1 1 1 1 '(1)'
           57  +    t1 i1 1 2 2 '(2)'
           58  +    t1 i1 1 3 3 '(3)'
           59  +    t1 i1 1 4 4 '(4)'
           60  +  }
           61  +}
           62  +
           63  +
           64  +#-------------------------------------------------------------------------
           65  +# This is really just to test SQL user function "test_decode".
           66  +#
           67  +reset_db
           68  +do_execsql_test 2.1 {
           69  +  CREATE TABLE t1(a, b, c);
           70  +  INSERT INTO t1(a) VALUES('some text');
           71  +  INSERT INTO t1(a) VALUES(14);
           72  +  INSERT INTO t1(a) VALUES(NULL);
           73  +  INSERT INTO t1(a) VALUES(22.0);
           74  +  INSERT INTO t1(a) VALUES(x'656667');
           75  +  CREATE INDEX i1 ON t1(a, b, c);
           76  +  ANALYZE;
           77  +  SELECT quote(sample) FROM sqlite_stat3;
           78  +} {
           79  +  NULL 14 22.0 {'some text'} X'656667' 
           80  +}
           81  +
           82  +#-------------------------------------------------------------------------
           83  +# 
           84  +reset_db
           85  +do_execsql_test 3.1 {
           86  +  CREATE TABLE t2(a, b);
           87  +  CREATE INDEX i2 ON t2(a, b);
           88  +  BEGIN;
           89  +}
           90  +
           91  +do_test 3.2 {
           92  +  for {set i 0} {$i < 1000} {incr i} {
           93  +    set a [expr $i / 10]
           94  +    set b [expr int(rand() * 15.0)]
           95  +    execsql { INSERT INTO t2 VALUES($a, $b) }
           96  +  }
           97  +  execsql COMMIT
           98  +} {}
           99  +
          100  +db func lindex lindex
          101  +
          102  +# Each value of "a" occurs exactly 10 times in the table.
          103  +#
          104  +do_execsql_test 3.3.1 {
          105  +  SELECT count(*) FROM t2 GROUP BY a;
          106  +} [lrange [string repeat "10 " 100] 0 99]
          107  +
          108  +# The first element in the "nEq" list of all samples should therefore be 10.
          109  +#
          110  +do_execsql_test 3.3.2 {
          111  +  ANALYZE;
          112  +  SELECT nEq FROM sqlite_stat3;
          113  +} [lrange [string repeat "10 " 100] 0 23]
          114  +
          115  +#-------------------------------------------------------------------------
          116  +# 
          117  +do_execsql_test 3.4 {
          118  +  DROP TABLE IF EXISTS t1;
          119  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          120  +  INSERT INTO t1 VALUES(1, 1, 'one-a');
          121  +  INSERT INTO t1 VALUES(11, 1, 'one-b');
          122  +  INSERT INTO t1 VALUES(21, 1, 'one-c');
          123  +  INSERT INTO t1 VALUES(31, 1, 'one-d');
          124  +  INSERT INTO t1 VALUES(41, 1, 'one-e');
          125  +  INSERT INTO t1 VALUES(51, 1, 'one-f');
          126  +  INSERT INTO t1 VALUES(61, 1, 'one-g');
          127  +  INSERT INTO t1 VALUES(71, 1, 'one-h');
          128  +  INSERT INTO t1 VALUES(81, 1, 'one-i');
          129  +  INSERT INTO t1 VALUES(91, 1, 'one-j');
          130  +  INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1;
          131  +  INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
          132  +  INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
          133  +  INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
          134  +  INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';	
          135  +  CREATE INDEX t1b ON t1(b);
          136  +  ANALYZE;
          137  +  SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60;
          138  +} {three-d three-e three-f}
          139  +
          140  +
          141  +#-------------------------------------------------------------------------
          142  +# These tests verify that the sample selection for stat3 appears to be 
          143  +# working as designed.
          144  +#
          145  +
          146  +reset_db
          147  +db func lindex lindex
          148  +db func lrange lrange
          149  +
          150  +do_execsql_test 4.0 {
          151  +  DROP TABLE IF EXISTS t1;
          152  +  CREATE TABLE t1(a, b, c);
          153  +  CREATE INDEX i1 ON t1(c, b, a);
          154  +}
          155  +
          156  +
          157  +proc insert_filler_rows_n {iStart args} {
          158  +  set A(-ncopy) 1
          159  +  set A(-nval) 1
          160  +
          161  +  foreach {k v} $args {
          162  +    if {[info exists A($k)]==0} { error "no such option: $k" }
          163  +    set A($k) $v
          164  +  }
          165  +  if {[llength $args] % 2} {
          166  +    error "option requires an argument: [lindex $args end]"
          167  +  }
          168  +
          169  +  for {set i 0} {$i < $A(-nval)} {incr i} {
          170  +    set iVal [expr $iStart+$i]
          171  +    for {set j 0} {$j < $A(-ncopy)} {incr j} {
          172  +      execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) }
          173  +    }
          174  +  }
          175  +}
          176  +
          177  +do_test 4.1 {
          178  +  execsql { BEGIN }
          179  +  insert_filler_rows_n  0  -ncopy 10 -nval 19
          180  +  insert_filler_rows_n 20  -ncopy  1 -nval 100
          181  +
          182  +  execsql {
          183  +    INSERT INTO t1(c, b, a) VALUES(200, 1, 'a');
          184  +    INSERT INTO t1(c, b, a) VALUES(200, 1, 'b');
          185  +    INSERT INTO t1(c, b, a) VALUES(200, 1, 'c');
          186  +
          187  +    INSERT INTO t1(c, b, a) VALUES(200, 2, 'e');
          188  +    INSERT INTO t1(c, b, a) VALUES(200, 2, 'f');
          189  +
          190  +    INSERT INTO t1(c, b, a) VALUES(201, 3, 'g');
          191  +    INSERT INTO t1(c, b, a) VALUES(201, 4, 'h');
          192  +
          193  +    ANALYZE;
          194  +    SELECT count(*) FROM sqlite_stat3;
          195  +    SELECT count(*) FROM t1;
          196  +  }
          197  +} {24 297}
          198  +
          199  +do_execsql_test 4.2 {
          200  +  SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 ORDER BY rowid LIMIT 16;
          201  +} {
          202  +  10 0 0 0
          203  +  10 10 1 1
          204  +  10 20 2 2
          205  +  10 30 3 3
          206  +  10 40 4 4
          207  +  10 50 5 5
          208  +  10 60 6 6
          209  +  10 70 7 7
          210  +  10 80 8 8
          211  +  10 90 9 9
          212  +  10 100 10 10
          213  +  10 110 11 11
          214  +  10 120 12 12
          215  +  10 130 13 13
          216  +  10 140 14 14
          217  +  10 150 15 15
          218  +}
          219  +
          220  +do_execsql_test 4.3 {
          221  +  SELECT neq, nlt, ndlt, sample FROM sqlite_stat3
          222  +  ORDER BY rowid DESC LIMIT 2;
          223  +} {
          224  +  2 295 120 201
          225  +  5 290 119 200
          226  +}
          227  +
          228  +do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120
          229  +do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119
          230  +
          231  +reset_db
          232  +do_test 4.7 {
          233  +  execsql { 
          234  +    BEGIN;
          235  +    CREATE TABLE t1(o,t INTEGER PRIMARY KEY);
          236  +    CREATE INDEX i1 ON t1(o);
          237  +  }
          238  +  for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} {
          239  +    execsql { INSERT INTO t1 VALUES('x', $i) }
          240  +  }
          241  +  execsql {
          242  +    COMMIT;
          243  +    ANALYZE;
          244  +    SELECT count(*) FROM sqlite_stat3;
          245  +  }
          246  +} {1}
          247  +do_execsql_test 4.8 {
          248  +  SELECT sample FROM sqlite_stat3;
          249  +} {x}
          250  +
          251  +
          252  +#-------------------------------------------------------------------------
          253  +# The following would cause a crash at one point.
          254  +#
          255  +reset_db
          256  +do_execsql_test 5.1 {
          257  +  PRAGMA encoding = 'utf-16';
          258  +  CREATE TABLE t0(v);
          259  +  ANALYZE;
          260  +}
          261  +
          262  +#-------------------------------------------------------------------------
          263  +# This was also crashing (corrupt sqlite_stat3 table).
          264  +#
          265  +reset_db
          266  +do_execsql_test 6.1 {
          267  +  CREATE TABLE t1(a, b);
          268  +  CREATE INDEX i1 ON t1(a);
          269  +  CREATE INDEX i2 ON t1(b);
          270  +  INSERT INTO t1 VALUES(1, 1);
          271  +  INSERT INTO t1 VALUES(2, 2);
          272  +  INSERT INTO t1 VALUES(3, 3);
          273  +  INSERT INTO t1 VALUES(4, 4);
          274  +  INSERT INTO t1 VALUES(5, 5);
          275  +  ANALYZE;
          276  +  PRAGMA writable_schema = 1;
          277  +  CREATE TEMP TABLE x1 AS
          278  +    SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3
          279  +    ORDER BY (rowid%5), rowid;
          280  +  DELETE FROM sqlite_stat3;
          281  +  INSERT INTO sqlite_stat3 SELECT * FROM x1;
          282  +  PRAGMA writable_schema = 0;
          283  +  ANALYZE sqlite_master;
          284  +}
          285  +do_execsql_test 6.2 {
          286  +  SELECT * FROM t1 WHERE a = 'abc';
          287  +}
          288  +
          289  +#-------------------------------------------------------------------------
          290  +# The following tests experiment with adding corrupted records to the
          291  +# 'sample' column of the sqlite_stat3 table.
          292  +#
          293  +reset_db
          294  +sqlite3_db_config_lookaside db 0 0 0
          295  +
          296  +do_execsql_test 7.1 {
          297  +  CREATE TABLE t1(a, b);
          298  +  CREATE INDEX i1 ON t1(a, b);
          299  +  INSERT INTO t1 VALUES(1, 1);
          300  +  INSERT INTO t1 VALUES(2, 2);
          301  +  INSERT INTO t1 VALUES(3, 3);
          302  +  INSERT INTO t1 VALUES(4, 4);
          303  +  INSERT INTO t1 VALUES(5, 5);
          304  +  ANALYZE;
          305  +  UPDATE sqlite_stat3 SET sample = X'' WHERE rowid = 1;
          306  +  ANALYZE sqlite_master;
          307  +}
          308  +
          309  +do_execsql_test 7.2 {
          310  +  UPDATE sqlite_stat3 SET sample = X'FFFF';
          311  +  ANALYZE sqlite_master;
          312  +  SELECT * FROM t1 WHERE a = 1;
          313  +} {1 1}
          314  +
          315  +do_execsql_test 7.3 {
          316  +  ANALYZE;
          317  +  UPDATE sqlite_stat3 SET neq = '0 0 0';
          318  +  ANALYZE sqlite_master;
          319  +  SELECT * FROM t1 WHERE a = 1;
          320  +} {1 1}
          321  +
          322  +do_execsql_test 7.4 {
          323  +  ANALYZE;
          324  +  UPDATE sqlite_stat3 SET ndlt = '0 0 0';
          325  +  ANALYZE sqlite_master;
          326  +  SELECT * FROM t1 WHERE a = 3;
          327  +} {3 3}
          328  +
          329  +do_execsql_test 7.5 {
          330  +  ANALYZE;
          331  +  UPDATE sqlite_stat3 SET nlt = '0 0 0';
          332  +  ANALYZE sqlite_master;
          333  +  SELECT * FROM t1 WHERE a = 5;
          334  +} {5 5}
          335  +
          336  +#-------------------------------------------------------------------------
          337  +#
          338  +reset_db
          339  +do_execsql_test 8.1 {
          340  +  CREATE TABLE t1(x TEXT);
          341  +  CREATE INDEX i1 ON t1(x);
          342  +  INSERT INTO t1 VALUES('1');
          343  +  INSERT INTO t1 VALUES('2');
          344  +  INSERT INTO t1 VALUES('3');
          345  +  INSERT INTO t1 VALUES('4');
          346  +  ANALYZE;
          347  +}
          348  +do_execsql_test 8.2 {
          349  +  SELECT * FROM t1 WHERE x = 3;
          350  +} {3}
          351  +
          352  +#-------------------------------------------------------------------------
          353  +#
          354  +reset_db
          355  +do_execsql_test 9.1 {
          356  +  CREATE TABLE t1(a, b, c, d, e);
          357  +  CREATE INDEX i1 ON t1(a, b, c, d);
          358  +  CREATE INDEX i2 ON t1(e);
          359  +}
          360  +do_test 9.2 {
          361  +  execsql BEGIN;
          362  +  for {set i 0} {$i < 100} {incr i} {
          363  +    execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
          364  +  }
          365  +  for {set i 0} {$i < 20} {incr i} {
          366  +    execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)"
          367  +  }
          368  +  for {set i 102} {$i < 200} {incr i} {
          369  +    execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
          370  +  }
          371  +  execsql COMMIT
          372  +  execsql ANALYZE
          373  +} {}
          374  +
          375  +do_eqp_test 9.3.1 {
          376  +  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5;
          377  +} {/t1 USING INDEX i1/}
          378  +do_eqp_test 9.3.2 {
          379  +  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5;
          380  +} {/t1 USING INDEX i1/}
          381  +
          382  +set value_d [expr 101]
          383  +do_eqp_test 9.4.1 {
          384  +  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
          385  +} {/t1 USING INDEX i1/}
          386  +set value_d [expr 99]
          387  +do_eqp_test 9.4.2 {
          388  +  SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
          389  +} {/t1 USING INDEX i1/}
          390  +
          391  +#-------------------------------------------------------------------------
          392  +# Check that the planner takes stat3 data into account when considering
          393  +# "IS NULL" and "IS NOT NULL" constraints.
          394  +#
          395  +do_execsql_test 10.1.1 {
          396  +  DROP TABLE IF EXISTS t3;
          397  +  CREATE TABLE t3(a, b);
          398  +  CREATE INDEX t3a ON t3(a);
          399  +  CREATE INDEX t3b ON t3(b);
          400  +}
          401  +do_test 10.1.2 {
          402  +  for {set i 1} {$i < 100} {incr i} {
          403  +    if {$i>90} { set a $i } else { set a NULL }
          404  +    set b [expr $i % 5]
          405  +    execsql "INSERT INTO t3 VALUES($a, $b)"
          406  +  }
          407  +  execsql ANALYZE
          408  +} {}
          409  +do_eqp_test 10.1.3 {
          410  +  SELECT * FROM t3 WHERE a IS NULL AND b = 2
          411  +} {/t3 USING INDEX t3b/}
          412  +do_eqp_test 10.1.4 {
          413  +  SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2
          414  +} {/t3 USING INDEX t3a/}
          415  +
          416  +#-------------------------------------------------------------------------
          417  +# Check that stat3 data is used correctly with non-default collation
          418  +# sequences.
          419  +#
          420  +foreach {tn schema} {
          421  +  1 {
          422  +    CREATE TABLE t4(a COLLATE nocase, b);
          423  +    CREATE INDEX t4a ON t4(a);
          424  +    CREATE INDEX t4b ON t4(b);
          425  +  }
          426  +  2 {
          427  +    CREATE TABLE t4(a, b);
          428  +    CREATE INDEX t4a ON t4(a COLLATE nocase);
          429  +    CREATE INDEX t4b ON t4(b);
          430  +  }
          431  +} {
          432  +  drop_all_tables
          433  +  do_test 11.$tn.1 { execsql $schema } {}
          434  +
          435  +  do_test 11.$tn.2 {
          436  +    for {set i 0} {$i < 100} {incr i} {
          437  +      if { ($i % 10)==0 } { set a ABC } else { set a DEF }
          438  +      set b [expr $i % 5]
          439  +        execsql { INSERT INTO t4 VALUES($a, $b) }
          440  +    }
          441  +    execsql ANALYZE
          442  +  } {}
          443  +
          444  +  do_eqp_test 11.$tn.3 {
          445  +    SELECT * FROM t4 WHERE a = 'def' AND b = 3;
          446  +  } {/t4 USING INDEX t4b/}
          447  +
          448  +  if {$tn==1} {
          449  +    set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;"
          450  +    do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/}
          451  +  } else {
          452  +
          453  +    set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;"
          454  +    do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/}
          455  +
          456  +    set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;"
          457  +    do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/}
          458  +  }
          459  +}
          460  +
          461  +#-------------------------------------------------------------------------
          462  +# Test that nothing untoward happens if the stat3 table contains entries
          463  +# for indexes that do not exist. Or NULL values in the idx column.
          464  +# Or NULL values in any of the other columns.
          465  +#
          466  +drop_all_tables
          467  +do_execsql_test 15.1 {
          468  +  CREATE TABLE x1(a, b, UNIQUE(a, b));
          469  +  INSERT INTO x1 VALUES(1, 2);
          470  +  INSERT INTO x1 VALUES(3, 4);
          471  +  INSERT INTO x1 VALUES(5, 6);
          472  +  ANALYZE;
          473  +  INSERT INTO sqlite_stat3 VALUES(NULL, NULL, NULL, NULL, NULL, NULL);
          474  +}
          475  +db close
          476  +sqlite3 db test.db
          477  +do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6}
          478  +
          479  +do_execsql_test 15.3 {
          480  +  INSERT INTO sqlite_stat3 VALUES(42, 42, 42, 42, 42, 42);
          481  +}
          482  +db close
          483  +sqlite3 db test.db
          484  +do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6}
          485  +
          486  +do_execsql_test 15.5 {
          487  +  UPDATE sqlite_stat1 SET stat = NULL;
          488  +}
          489  +db close
          490  +sqlite3 db test.db
          491  +do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6}
          492  +
          493  +do_execsql_test 15.7 {
          494  +  ANALYZE;
          495  +  UPDATE sqlite_stat1 SET tbl = 'no such tbl';
          496  +}
          497  +db close
          498  +sqlite3 db test.db
          499  +do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6}
          500  +
          501  +do_execsql_test 15.9 {
          502  +  ANALYZE;
          503  +  UPDATE sqlite_stat3 SET neq = NULL, nlt=NULL, ndlt=NULL;
          504  +}
          505  +db close
          506  +sqlite3 db test.db
          507  +do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6}
          508  +
          509  +# This is just for coverage....
          510  +do_execsql_test 15.11 {
          511  +  ANALYZE;
          512  +  UPDATE sqlite_stat1 SET stat = stat || ' unordered';
          513  +}
          514  +db close
          515  +sqlite3 db test.db
          516  +do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6}
          517  +
          518  +#-------------------------------------------------------------------------
          519  +# Test that allocations used for sqlite_stat3 samples are included in
          520  +# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED.
          521  +#
          522  +set one [string repeat x 1000]
          523  +set two [string repeat x 2000]
          524  +do_test 16.1 {
          525  +  reset_db
          526  +  execsql {
          527  +    CREATE TABLE t1(a, UNIQUE(a));
          528  +    INSERT INTO t1 VALUES($one);
          529  +    ANALYZE;
          530  +  }
          531  +  set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
          532  +
          533  +  reset_db
          534  +  execsql {
          535  +    CREATE TABLE t1(a, UNIQUE(a));
          536  +    INSERT INTO t1 VALUES($two);
          537  +    ANALYZE;
          538  +  }
          539  +  set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
          540  +
          541  +  expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050}
          542  +} {1}
          543  +
          544  +#-------------------------------------------------------------------------
          545  +# Test that stat3 data may be used with partial indexes.
          546  +#
          547  +do_test 17.1 {
          548  +  reset_db
          549  +  execsql {
          550  +    CREATE TABLE t1(a, b, c, d);
          551  +    CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
          552  +    INSERT INTO t1 VALUES(-1, -1, -1, NULL);
          553  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          554  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          555  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          556  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          557  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          558  +    INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
          559  +  }
          560  +
          561  +  for {set i 0} {$i < 32} {incr i} {
          562  +    execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') }
          563  +  }
          564  +  execsql {ANALYZE main.t1}
          565  +} {}
          566  +
          567  +do_catchsql_test 17.1.2 {
          568  +  ANALYZE temp.t1;
          569  +} {1 {no such table: temp.t1}}
          570  +
          571  +do_eqp_test 17.2 {
          572  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0;
          573  +} {/USING INDEX i1/}
          574  +do_eqp_test 17.3 {
          575  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0;
          576  +} {/USING INDEX i1/}
          577  +
          578  +do_execsql_test 17.4 {
          579  +  CREATE INDEX i2 ON t1(c) WHERE d IS NOT NULL;
          580  +  ANALYZE main.i2;
          581  +}
          582  +do_eqp_test 17.5 {
          583  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0;
          584  +} {/USING INDEX i1/}
          585  +do_eqp_test 17.6 {
          586  +  SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
          587  +} {/USING INDEX i2/}
          588  +
          589  +#-------------------------------------------------------------------------
          590  +#
          591  +do_test 18.1 {
          592  +  reset_db
          593  +  execsql {
          594  +    CREATE TABLE t1(a, b);
          595  +    CREATE INDEX i1 ON t1(a, b);
          596  +  }
          597  +  for {set i 0} {$i < 9} {incr i} {
          598  +    execsql {
          599  +      INSERT INTO t1 VALUES($i, 0);
          600  +      INSERT INTO t1 VALUES($i, 0);
          601  +      INSERT INTO t1 VALUES($i, 0);
          602  +      INSERT INTO t1 VALUES($i, 0);
          603  +      INSERT INTO t1 VALUES($i, 0);
          604  +      INSERT INTO t1 VALUES($i, 0);
          605  +      INSERT INTO t1 VALUES($i, 0);
          606  +      INSERT INTO t1 VALUES($i, 0);
          607  +      INSERT INTO t1 VALUES($i, 0);
          608  +      INSERT INTO t1 VALUES($i, 0);
          609  +      INSERT INTO t1 VALUES($i, 0);
          610  +      INSERT INTO t1 VALUES($i, 0);
          611  +      INSERT INTO t1 VALUES($i, 0);
          612  +      INSERT INTO t1 VALUES($i, 0);
          613  +      INSERT INTO t1 VALUES($i, 0);
          614  +    }
          615  +  }
          616  +  execsql ANALYZE
          617  +  execsql { SELECT count(*) FROM sqlite_stat3 }
          618  +} {9}
          619  +
          620  +#-------------------------------------------------------------------------
          621  +# For coverage.
          622  +#
          623  +ifcapable view {
          624  +  do_test 19.1 {
          625  +    reset_db 
          626  +    execsql {
          627  +      CREATE TABLE t1(x, y);
          628  +      CREATE INDEX i1 ON t1(x, y);
          629  +      CREATE VIEW v1 AS SELECT * FROM t1;
          630  +      ANALYZE;
          631  +    }
          632  +  } {}
          633  +}
          634  +ifcapable auth {
          635  +  proc authproc {op args} {
          636  +    if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" }
          637  +    return "SQLITE_OK"
          638  +  }
          639  +  do_test 19.2 {
          640  +    reset_db 
          641  +    db auth authproc
          642  +    execsql {
          643  +      CREATE TABLE t1(x, y);
          644  +      CREATE VIEW v1 AS SELECT * FROM t1;
          645  +    }
          646  +    catchsql ANALYZE
          647  +  } {1 {not authorized}}
          648  +}
          649  +
          650  +#-------------------------------------------------------------------------
          651  +#
          652  +reset_db
          653  +proc r {args} { expr rand() }
          654  +db func r r
          655  +db func lrange lrange
          656  +do_test 20.1 {
          657  +  execsql {
          658  +    CREATE TABLE t1(a,b,c,d);
          659  +    CREATE INDEX i1 ON t1(a,b,c,d);
          660  +  }
          661  +  for {set i 0} {$i < 16} {incr i} {
          662  +    execsql {
          663  +      INSERT INTO t1 VALUES($i, r(), r(), r());
          664  +      INSERT INTO t1 VALUES($i, $i,  r(), r());
          665  +      INSERT INTO t1 VALUES($i, $i,  $i,  r());
          666  +      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
          667  +      INSERT INTO t1 VALUES($i, $i,  $i,  $i);
          668  +      INSERT INTO t1 VALUES($i, $i,  $i,  r());
          669  +      INSERT INTO t1 VALUES($i, $i,  r(), r());
          670  +      INSERT INTO t1 VALUES($i, r(), r(), r());
          671  +    }
          672  +  }
          673  +} {}
          674  +do_execsql_test 20.2 { ANALYZE }
          675  +for {set i 0} {$i<16} {incr i} {
          676  +    set val $i
          677  +    do_execsql_test 20.3.$i {
          678  +      SELECT count(*) FROM sqlite_stat3 WHERE sample=$val
          679  +    } {1}
          680  +}
          681  +
          682  +finish_test
          683  +

Added test/fkey7.test.

            1  +# 2001 September 15
            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.
           12  +#
           13  +# This file implements tests for foreign keys.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix fkey7
           19  +
           20  +ifcapable {!foreignkey} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +do_execsql_test 1.1 {
           26  +  PRAGMA foreign_keys = 1;
           27  +
           28  +  CREATE TABLE s1(a PRIMARY KEY, b);
           29  +  CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a));
           30  +
           31  +  CREATE TABLE c1(a, b REFERENCES par);
           32  +  CREATE TABLE c2(a, b REFERENCES par);
           33  +  CREATE TABLE c3(a, b REFERENCES par(c));
           34  +}
           35  +
           36  +proc auth {op tbl args} {
           37  +  if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 }
           38  +  return "SQLITE_OK"
           39  +}
           40  +db auth auth
           41  +db cache size 0
           42  +proc do_tblsread_test {tn sql tbllist} {
           43  +  array unset ::tbls
           44  +  uplevel [list execsql $sql]
           45  +  uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist]
           46  +}
           47  +
           48  +do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1}
           49  +do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par}
           50  +do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par}
           51  +do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1}
           52  +
           53  +
           54  +finish_test

Changes to test/mallocA.test.

    92     92   } -test {
    93     93     faultsim_test_result [list 0 2]
    94     94   }
    95     95   do_faultsim_test 6.2 -faults oom* -body {
    96     96     execsql { SELECT rowid FROM t1 WHERE a='abc' AND b<'y' }
    97     97   } -test {
    98     98     faultsim_test_result [list 0 {1 2}]
           99  +}
          100  +ifcapable stat3 {
          101  +  do_test 6.3-prep {
          102  +    execsql {
          103  +      PRAGMA writable_schema = 1;
          104  +      CREATE TABLE sqlite_stat4 AS 
          105  +      SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) AS sample 
          106  +      FROM sqlite_stat3;
          107  +    }
          108  +  } {}
          109  +  do_faultsim_test 6.3 -faults oom* -body {
          110  +    execsql { 
          111  +      ANALYZE sqlite_master;
          112  +      SELECT rowid FROM t1 WHERE a='abc' AND b<'y';
          113  +    }
          114  +  } -test {
          115  +    faultsim_test_result [list 0 {1 2}]
          116  +  }
    99    117   }
   100    118   
   101    119   # Ensure that no file descriptors were leaked.
   102    120   do_test malloc-99.X {
   103    121     catch {db close}
   104    122     set sqlite_open_file_count
   105    123   } {0}
   106    124   
   107    125   forcedelete test.db.bu
   108    126   finish_test

Changes to test/permutations.test.

   307    307   test_suite "coverage-pager" -description {
   308    308     Coverage tests for file pager.c.
   309    309   } -files {
   310    310     pager1.test    pager2.test  pagerfault.test  pagerfault2.test
   311    311     walfault.test  walbak.test  journal2.test    tkt-9d68c883.test
   312    312   } 
   313    313   
          314  +test_suite "coverage-analyze" -description {
          315  +  Coverage tests for file analyze.c.
          316  +} -files {
          317  +  analyze3.test analyze4.test analyze5.test analyze6.test
          318  +  analyze7.test analyze8.test analyze9.test analyzeA.test
          319  +  analyze.test analyzeB.test mallocA.test
          320  +} 
          321  +
   314    322   
   315    323   lappend ::testsuitelist xxx
   316    324   #-------------------------------------------------------------------------
   317    325   # Define the permutation test suites:
   318    326   #
   319    327   
   320    328   # Run some tests using pre-allocated page and scratch blocks.
................................................................................
   497    505   test_suite "utf16" -description {
   498    506     Run tests using UTF-16 databases
   499    507   } -presql {
   500    508     pragma encoding = 'UTF-16'
   501    509   } -files {
   502    510       alter.test alter3.test
   503    511       analyze.test analyze3.test analyze4.test analyze5.test analyze6.test
   504         -    analyze7.test analyze8.test analyze9.test analyzeA.test
          512  +    analyze7.test analyze8.test analyze9.test analyzeA.test analyzeB.test
   505    513       auth.test bind.test blob.test capi2.test capi3.test collate1.test
   506    514       collate2.test collate3.test collate4.test collate5.test collate6.test
   507    515       conflict.test date.test delete.test expr.test fkey1.test func.test
   508    516       hook.test index.test insert2.test insert.test interrupt.test in.test
   509    517       intpkey.test ioerr.test join2.test join.test lastinsert.test
   510    518       laststmtchanges.test limit.test lock2.test lock.test main.test 
   511    519       memdb.test minmax.test misc1.test misc2.test misc3.test notnull.test

Changes to test/shell1.test.

   718    718     catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'}
   719    719   } [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"]
   720    720   
   721    721   
   722    722   # Test the output of the ".dump" command
   723    723   #
   724    724   do_test shell1-4.1 {
          725  +  db close
          726  +  forcedelete test.db
          727  +  sqlite3 db test.db
   725    728     db eval {
          729  +    PRAGMA encoding=UTF16;
   726    730       CREATE TABLE t1(x);
   727    731       INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
   728    732     }
   729    733     catchcmd test.db {.dump}
   730    734   } {0 {PRAGMA foreign_keys=OFF;
   731    735   BEGIN TRANSACTION;
   732    736   CREATE TABLE t1(x);
................................................................................
   748    752   INSERT INTO t1 VALUES(2.25);
   749    753   INSERT INTO t1 VALUES('hello');
   750    754   INSERT INTO t1 VALUES(X'807f');}}
   751    755   
   752    756   # Test the output of ".mode tcl"
   753    757   #
   754    758   do_test shell1-4.3 {
          759  +  db close
          760  +  forcedelete test.db
          761  +  sqlite3 db test.db
          762  +  db eval {
          763  +    PRAGMA encoding=UTF8;
          764  +    CREATE TABLE t1(x);
          765  +    INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f');
          766  +  }
   755    767     catchcmd test.db ".mode tcl\nselect * from t1;"
   756    768   } {0 {""
   757    769   ""
   758    770   "1"
   759    771   "2.25"
   760    772   "hello"
   761    773   "\200\177"}}

Changes to test/where.test.

  1299   1299   do_test where-17.5 {
  1300   1300     execsql {
  1301   1301       CREATE TABLE tother(a, b);
  1302   1302       INSERT INTO tother VALUES(1, 3.7);
  1303   1303       SELECT id, a FROM tbooking, tother WHERE id>a;
  1304   1304     }
  1305   1305   } {42 1 43 1}
         1306  +
         1307  +# Ticket [be84e357c035d068135f20bcfe82761bbf95006b]  2013-09-03
         1308  +# Segfault during query involving LEFT JOIN column in the ORDER BY clause.
         1309  +#
         1310  +do_execsql_test where-18.1 {
         1311  +  CREATE TABLE t181(a);
         1312  +  CREATE TABLE t182(b,c);
         1313  +  INSERT INTO t181 VALUES(1);
         1314  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
         1315  +} {1}
         1316  +do_execsql_test where-18.2 {
         1317  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
         1318  +} {1}
         1319  +do_execsql_test where-18.3 {
         1320  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
         1321  +} {1}
         1322  +do_execsql_test where-18.4 {
         1323  +  INSERT INTO t181 VALUES(1),(1),(1),(1);
         1324  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
         1325  +} {1}
         1326  +do_execsql_test where-18.5 {
         1327  +  INSERT INTO t181 VALUES(2);
         1328  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
         1329  +} {1 2}
         1330  +do_execsql_test where-18.6 {
         1331  +  INSERT INTO t181 VALUES(2);
         1332  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
         1333  +} {1 2}
         1334  +
  1306   1335   
  1307   1336   finish_test