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

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

Overview
Comment:Merge the fuzzershell enhancement from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: ed206048484667e4fe6aaeadd65882537d74bd35
User & Date: drh 2016-09-03 16:24:43
Context
2016-09-03
19:52
Fix a problem causing the affinity of sub-select row-value elements to be ignored in some contextes. check-in: 7d9bd22c user: dan tags: rowvalue
16:24
Merge the fuzzershell enhancement from trunk. check-in: ed206048 user: drh tags: rowvalue
16:23
Build the generate_series(START,END,STEP) table-valued function into fuzzershell, to make it easier to construct compact test cases that contain actual data. check-in: 672c21bc user: drh tags: trunk
15:31
Consider the affinity of "b" when using an "a IN (SELECT b ...)" expression with an index on "a". Fix for [199df416]. check-in: f5e49855 user: dan tags: rowvalue
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to tool/fuzzershell.c.

   308    308     }else{
   309    309       sqlite3_result_text(context, x.z, (int)x.nUsed, sqlite3_free);
   310    310     }
   311    311   }
   312    312   /* End of the eval() implementation
   313    313   ******************************************************************************/
   314    314   
          315  +/******************************************************************************
          316  +** The generate_series(START,END,STEP) eponymous table-valued function.
          317  +**
          318  +** This code is copy/pasted from ext/misc/series.c in the SQLite source tree.
          319  +*/
          320  +/* series_cursor is a subclass of sqlite3_vtab_cursor which will
          321  +** serve as the underlying representation of a cursor that scans
          322  +** over rows of the result
          323  +*/
          324  +typedef struct series_cursor series_cursor;
          325  +struct series_cursor {
          326  +  sqlite3_vtab_cursor base;  /* Base class - must be first */
          327  +  int isDesc;                /* True to count down rather than up */
          328  +  sqlite3_int64 iRowid;      /* The rowid */
          329  +  sqlite3_int64 iValue;      /* Current value ("value") */
          330  +  sqlite3_int64 mnValue;     /* Mimimum value ("start") */
          331  +  sqlite3_int64 mxValue;     /* Maximum value ("stop") */
          332  +  sqlite3_int64 iStep;       /* Increment ("step") */
          333  +};
          334  +
          335  +/*
          336  +** The seriesConnect() method is invoked to create a new
          337  +** series_vtab that describes the generate_series virtual table.
          338  +**
          339  +** Think of this routine as the constructor for series_vtab objects.
          340  +**
          341  +** All this routine needs to do is:
          342  +**
          343  +**    (1) Allocate the series_vtab object and initialize all fields.
          344  +**
          345  +**    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
          346  +**        result set of queries against generate_series will look like.
          347  +*/
          348  +static int seriesConnect(
          349  +  sqlite3 *db,
          350  +  void *pAux,
          351  +  int argc, const char *const*argv,
          352  +  sqlite3_vtab **ppVtab,
          353  +  char **pzErr
          354  +){
          355  +  sqlite3_vtab *pNew;
          356  +  int rc;
          357  +
          358  +/* Column numbers */
          359  +#define SERIES_COLUMN_VALUE 0
          360  +#define SERIES_COLUMN_START 1
          361  +#define SERIES_COLUMN_STOP  2
          362  +#define SERIES_COLUMN_STEP  3
          363  +
          364  +  rc = sqlite3_declare_vtab(db,
          365  +     "CREATE TABLE x(value,start hidden,stop hidden,step hidden)");
          366  +  if( rc==SQLITE_OK ){
          367  +    pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) );
          368  +    if( pNew==0 ) return SQLITE_NOMEM;
          369  +    memset(pNew, 0, sizeof(*pNew));
          370  +  }
          371  +  return rc;
          372  +}
          373  +
          374  +/*
          375  +** This method is the destructor for series_cursor objects.
          376  +*/
          377  +static int seriesDisconnect(sqlite3_vtab *pVtab){
          378  +  sqlite3_free(pVtab);
          379  +  return SQLITE_OK;
          380  +}
          381  +
          382  +/*
          383  +** Constructor for a new series_cursor object.
          384  +*/
          385  +static int seriesOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
          386  +  series_cursor *pCur;
          387  +  pCur = sqlite3_malloc( sizeof(*pCur) );
          388  +  if( pCur==0 ) return SQLITE_NOMEM;
          389  +  memset(pCur, 0, sizeof(*pCur));
          390  +  *ppCursor = &pCur->base;
          391  +  return SQLITE_OK;
          392  +}
          393  +
          394  +/*
          395  +** Destructor for a series_cursor.
          396  +*/
          397  +static int seriesClose(sqlite3_vtab_cursor *cur){
          398  +  sqlite3_free(cur);
          399  +  return SQLITE_OK;
          400  +}
          401  +
          402  +
          403  +/*
          404  +** Advance a series_cursor to its next row of output.
          405  +*/
          406  +static int seriesNext(sqlite3_vtab_cursor *cur){
          407  +  series_cursor *pCur = (series_cursor*)cur;
          408  +  if( pCur->isDesc ){
          409  +    pCur->iValue -= pCur->iStep;
          410  +  }else{
          411  +    pCur->iValue += pCur->iStep;
          412  +  }
          413  +  pCur->iRowid++;
          414  +  return SQLITE_OK;
          415  +}
          416  +
          417  +/*
          418  +** Return values of columns for the row at which the series_cursor
          419  +** is currently pointing.
          420  +*/
          421  +static int seriesColumn(
          422  +  sqlite3_vtab_cursor *cur,   /* The cursor */
          423  +  sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
          424  +  int i                       /* Which column to return */
          425  +){
          426  +  series_cursor *pCur = (series_cursor*)cur;
          427  +  sqlite3_int64 x = 0;
          428  +  switch( i ){
          429  +    case SERIES_COLUMN_START:  x = pCur->mnValue; break;
          430  +    case SERIES_COLUMN_STOP:   x = pCur->mxValue; break;
          431  +    case SERIES_COLUMN_STEP:   x = pCur->iStep;   break;
          432  +    default:                   x = pCur->iValue;  break;
          433  +  }
          434  +  sqlite3_result_int64(ctx, x);
          435  +  return SQLITE_OK;
          436  +}
          437  +
          438  +/*
          439  +** Return the rowid for the current row.  In this implementation, the
          440  +** rowid is the same as the output value.
          441  +*/
          442  +static int seriesRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          443  +  series_cursor *pCur = (series_cursor*)cur;
          444  +  *pRowid = pCur->iRowid;
          445  +  return SQLITE_OK;
          446  +}
          447  +
          448  +/*
          449  +** Return TRUE if the cursor has been moved off of the last
          450  +** row of output.
          451  +*/
          452  +static int seriesEof(sqlite3_vtab_cursor *cur){
          453  +  series_cursor *pCur = (series_cursor*)cur;
          454  +  if( pCur->isDesc ){
          455  +    return pCur->iValue < pCur->mnValue;
          456  +  }else{
          457  +    return pCur->iValue > pCur->mxValue;
          458  +  }
          459  +}
          460  +
          461  +/* True to cause run-time checking of the start=, stop=, and/or step= 
          462  +** parameters.  The only reason to do this is for testing the
          463  +** constraint checking logic for virtual tables in the SQLite core.
          464  +*/
          465  +#ifndef SQLITE_SERIES_CONSTRAINT_VERIFY
          466  +# define SQLITE_SERIES_CONSTRAINT_VERIFY 0
          467  +#endif
          468  +
          469  +/*
          470  +** This method is called to "rewind" the series_cursor object back
          471  +** to the first row of output.  This method is always called at least
          472  +** once prior to any call to seriesColumn() or seriesRowid() or 
          473  +** seriesEof().
          474  +**
          475  +** The query plan selected by seriesBestIndex is passed in the idxNum
          476  +** parameter.  (idxStr is not used in this implementation.)  idxNum
          477  +** is a bitmask showing which constraints are available:
          478  +**
          479  +**    1:    start=VALUE
          480  +**    2:    stop=VALUE
          481  +**    4:    step=VALUE
          482  +**
          483  +** Also, if bit 8 is set, that means that the series should be output
          484  +** in descending order rather than in ascending order.
          485  +**
          486  +** This routine should initialize the cursor and position it so that it
          487  +** is pointing at the first row, or pointing off the end of the table
          488  +** (so that seriesEof() will return true) if the table is empty.
          489  +*/
          490  +static int seriesFilter(
          491  +  sqlite3_vtab_cursor *pVtabCursor, 
          492  +  int idxNum, const char *idxStr,
          493  +  int argc, sqlite3_value **argv
          494  +){
          495  +  series_cursor *pCur = (series_cursor *)pVtabCursor;
          496  +  int i = 0;
          497  +  if( idxNum & 1 ){
          498  +    pCur->mnValue = sqlite3_value_int64(argv[i++]);
          499  +  }else{
          500  +    pCur->mnValue = 0;
          501  +  }
          502  +  if( idxNum & 2 ){
          503  +    pCur->mxValue = sqlite3_value_int64(argv[i++]);
          504  +  }else{
          505  +    pCur->mxValue = 0xffffffff;
          506  +  }
          507  +  if( idxNum & 4 ){
          508  +    pCur->iStep = sqlite3_value_int64(argv[i++]);
          509  +    if( pCur->iStep<1 ) pCur->iStep = 1;
          510  +  }else{
          511  +    pCur->iStep = 1;
          512  +  }
          513  +  if( idxNum & 8 ){
          514  +    pCur->isDesc = 1;
          515  +    pCur->iValue = pCur->mxValue;
          516  +    if( pCur->iStep>0 ){
          517  +      pCur->iValue -= (pCur->mxValue - pCur->mnValue)%pCur->iStep;
          518  +    }
          519  +  }else{
          520  +    pCur->isDesc = 0;
          521  +    pCur->iValue = pCur->mnValue;
          522  +  }
          523  +  pCur->iRowid = 1;
          524  +  return SQLITE_OK;
          525  +}
          526  +
          527  +/*
          528  +** SQLite will invoke this method one or more times while planning a query
          529  +** that uses the generate_series virtual table.  This routine needs to create
          530  +** a query plan for each invocation and compute an estimated cost for that
          531  +** plan.
          532  +**
          533  +** In this implementation idxNum is used to represent the
          534  +** query plan.  idxStr is unused.
          535  +**
          536  +** The query plan is represented by bits in idxNum:
          537  +**
          538  +**  (1)  start = $value  -- constraint exists
          539  +**  (2)  stop = $value   -- constraint exists
          540  +**  (4)  step = $value   -- constraint exists
          541  +**  (8)  output in descending order
          542  +*/
          543  +static int seriesBestIndex(
          544  +  sqlite3_vtab *tab,
          545  +  sqlite3_index_info *pIdxInfo
          546  +){
          547  +  int i;                 /* Loop over constraints */
          548  +  int idxNum = 0;        /* The query plan bitmask */
          549  +  int startIdx = -1;     /* Index of the start= constraint, or -1 if none */
          550  +  int stopIdx = -1;      /* Index of the stop= constraint, or -1 if none */
          551  +  int stepIdx = -1;      /* Index of the step= constraint, or -1 if none */
          552  +  int nArg = 0;          /* Number of arguments that seriesFilter() expects */
          553  +
          554  +  const struct sqlite3_index_constraint *pConstraint;
          555  +  pConstraint = pIdxInfo->aConstraint;
          556  +  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
          557  +    if( pConstraint->usable==0 ) continue;
          558  +    if( pConstraint->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
          559  +    switch( pConstraint->iColumn ){
          560  +      case SERIES_COLUMN_START:
          561  +        startIdx = i;
          562  +        idxNum |= 1;
          563  +        break;
          564  +      case SERIES_COLUMN_STOP:
          565  +        stopIdx = i;
          566  +        idxNum |= 2;
          567  +        break;
          568  +      case SERIES_COLUMN_STEP:
          569  +        stepIdx = i;
          570  +        idxNum |= 4;
          571  +        break;
          572  +    }
          573  +  }
          574  +  if( startIdx>=0 ){
          575  +    pIdxInfo->aConstraintUsage[startIdx].argvIndex = ++nArg;
          576  +    pIdxInfo->aConstraintUsage[startIdx].omit= !SQLITE_SERIES_CONSTRAINT_VERIFY;
          577  +  }
          578  +  if( stopIdx>=0 ){
          579  +    pIdxInfo->aConstraintUsage[stopIdx].argvIndex = ++nArg;
          580  +    pIdxInfo->aConstraintUsage[stopIdx].omit = !SQLITE_SERIES_CONSTRAINT_VERIFY;
          581  +  }
          582  +  if( stepIdx>=0 ){
          583  +    pIdxInfo->aConstraintUsage[stepIdx].argvIndex = ++nArg;
          584  +    pIdxInfo->aConstraintUsage[stepIdx].omit = !SQLITE_SERIES_CONSTRAINT_VERIFY;
          585  +  }
          586  +  if( (idxNum & 3)==3 ){
          587  +    /* Both start= and stop= boundaries are available.  This is the 
          588  +    ** the preferred case */
          589  +    pIdxInfo->estimatedCost = (double)(2 - ((idxNum&4)!=0));
          590  +    pIdxInfo->estimatedRows = 1000;
          591  +    if( pIdxInfo->nOrderBy==1 ){
          592  +      if( pIdxInfo->aOrderBy[0].desc ) idxNum |= 8;
          593  +      pIdxInfo->orderByConsumed = 1;
          594  +    }
          595  +  }else{
          596  +    /* If either boundary is missing, we have to generate a huge span
          597  +    ** of numbers.  Make this case very expensive so that the query
          598  +    ** planner will work hard to avoid it. */
          599  +    pIdxInfo->estimatedCost = (double)2147483647;
          600  +    pIdxInfo->estimatedRows = 2147483647;
          601  +  }
          602  +  pIdxInfo->idxNum = idxNum;
          603  +  return SQLITE_OK;
          604  +}
          605  +
          606  +/*
          607  +** This following structure defines all the methods for the 
          608  +** generate_series virtual table.
          609  +*/
          610  +static sqlite3_module seriesModule = {
          611  +  0,                         /* iVersion */
          612  +  0,                         /* xCreate */
          613  +  seriesConnect,             /* xConnect */
          614  +  seriesBestIndex,           /* xBestIndex */
          615  +  seriesDisconnect,          /* xDisconnect */
          616  +  0,                         /* xDestroy */
          617  +  seriesOpen,                /* xOpen - open a cursor */
          618  +  seriesClose,               /* xClose - close a cursor */
          619  +  seriesFilter,              /* xFilter - configure scan constraints */
          620  +  seriesNext,                /* xNext - advance a cursor */
          621  +  seriesEof,                 /* xEof - check for end of scan */
          622  +  seriesColumn,              /* xColumn - read data */
          623  +  seriesRowid,               /* xRowid - read data */
          624  +  0,                         /* xUpdate */
          625  +  0,                         /* xBegin */
          626  +  0,                         /* xSync */
          627  +  0,                         /* xCommit */
          628  +  0,                         /* xRollback */
          629  +  0,                         /* xFindMethod */
          630  +  0,                         /* xRename */
          631  +};
          632  +/* END the generate_series(START,END,STEP) implementation
          633  +*********************************************************************************/
          634  +
   315    635   /*
   316    636   ** Print sketchy documentation for this utility program
   317    637   */
   318    638   static void showHelp(void){
   319    639     printf("Usage: %s [options] ?FILE...?\n", g.zArgv0);
   320    640     printf(
   321    641   "Read SQL text from FILE... (or from standard input if FILE... is omitted)\n"
................................................................................
   725   1045             if( rc!=SQLITE_OK ) abendError("lookaside configuration filed: %d", rc);
   726   1046           }
   727   1047       #ifndef SQLITE_OMIT_TRACE
   728   1048           sqlite3_trace(db, verboseFlag ? traceCallback : traceNoop, 0);
   729   1049       #endif
   730   1050           sqlite3_create_function(db, "eval", 1, SQLITE_UTF8, 0, sqlEvalFunc, 0, 0);
   731   1051           sqlite3_create_function(db, "eval", 2, SQLITE_UTF8, 0, sqlEvalFunc, 0, 0);
         1052  +        sqlite3_create_module(db, "generate_series", &seriesModule, 0);
   732   1053           sqlite3_limit(db, SQLITE_LIMIT_LENGTH, 1000000);
   733   1054           if( zEncoding ) sqlexec(db, "PRAGMA encoding=%s", zEncoding);
   734   1055           if( pageSize ) sqlexec(db, "PRAGMA pagesize=%d", pageSize);
   735   1056           if( doAutovac ) sqlexec(db, "PRAGMA auto_vacuum=FULL");
   736   1057           iStart = timeOfDay();
   737   1058           g.bOomEnable = 1;
   738   1059           if( verboseFlag ){