/ Check-in [2993ca20]
Login

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

Overview
Comment:Merge the latest changes from trunk: chiefly the outer/inner loop query optimizer scoring enhancement and the INSTR() function.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1:2993ca20207f8dac02f58d01e31d68c84328356a
User & Date: drh 2012-11-10 01:27:59
Context
2012-11-27
21:56
Update the sessions branch to include the SQLLOG enhancement, the SQLITE_IOERR_DELETE_NOENT fix, and a fix for the number-of-documents bug in FTS4. check-in: ba8d08b6 user: drh tags: sessions
2012-11-10
01:27
Merge the latest changes from trunk: chiefly the outer/inner loop query optimizer scoring enhancement and the INSTR() function. check-in: 2993ca20 user: drh tags: sessions
2012-11-09
21:40
Only log unlink() errors if the error is something other than SQLITE_IOERR_DELETE_NOENT. The error is still reported up the stack, it is simply not added to the sqlite3_log(). check-in: 5a3b07f0 user: drh tags: trunk
2012-10-30
21:03
Pull all the latest trunk enhancements into the sessions branch. check-in: fce667f2 user: drh tags: sessions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

4026
4027
4028
4029
4030
4031
4032
4033
4034



4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046
4047
4048

4049
4050
4051
4052
4053
4054
4055
        }
        /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry
        */
        assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) );
        ExprSetIrreducible(pExpr);
        pExpr->iAgg = (i16)i;
        pExpr->pAggInfo = pAggInfo;
      }
      return WRC_Prune;



    }
  }
  return WRC_Continue;
}
static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){
  UNUSED_PARAMETER(pWalker);
  UNUSED_PARAMETER(pSelect);
  return WRC_Continue;
}

/*
** Analyze the given expression looking for aggregate functions and
** for variables that need to be added to the pParse->aAgg[] array.
** Make additional entries to the pParse->aAgg[] array as necessary.

**
** This routine should only be called after the expression has been
** analyzed by sqlite3ResolveExprNames().
*/
void sqlite3ExprAnalyzeAggregates(NameContext *pNC, Expr *pExpr){
  Walker w;
  memset(&w, 0, sizeof(w));







<
|
>
>
>











|
|
|
>







4026
4027
4028
4029
4030
4031
4032

4033
4034
4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046
4047
4048
4049
4050
4051
4052
4053
4054
4055
4056
4057
4058
        }
        /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry
        */
        assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) );
        ExprSetIrreducible(pExpr);
        pExpr->iAgg = (i16)i;
        pExpr->pAggInfo = pAggInfo;

        return WRC_Prune;
      }else{
        return WRC_Continue;
      }
    }
  }
  return WRC_Continue;
}
static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){
  UNUSED_PARAMETER(pWalker);
  UNUSED_PARAMETER(pSelect);
  return WRC_Continue;
}

/*
** Analyze the pExpr expression looking for aggregate functions and
** for variables that need to be added to AggInfo object that pNC->pAggInfo
** points to.  Additional entries are made on the AggInfo object as
** necessary.
**
** This routine should only be called after the expression has been
** analyzed by sqlite3ResolveExprNames().
*/
void sqlite3ExprAnalyzeAggregates(NameContext *pNC, Expr *pExpr){
  Walker w;
  memset(&w, 0, sizeof(w));

Changes to src/func.c.

163
164
165
166
167
168
169

















































170
171
172
173
174
175
176
....
1532
1533
1534
1535
1536
1537
1538

1539
1540
1541
1542
1543
1544
1545
      double rVal = sqlite3_value_double(argv[0]);
      if( rVal<0 ) rVal = -rVal;
      sqlite3_result_double(context, rVal);
      break;
    }
  }
}


















































/*
** Implementation of the substr() function.
**
** substr(x,p1,p2)  returns p2 characters of x[] beginning with p1.
** p1 is 1-indexed.  So substr(x,1,1) returns the first character
** of x.  If x is text, then we actually count UTF-8 characters.
................................................................................
    FUNCTION(min,                0, 0, 1, 0                ),
    AGGREGATE(min,               1, 0, 1, minmaxStep,      minMaxFinalize ),
    FUNCTION(max,               -1, 1, 1, minmaxFunc       ),
    FUNCTION(max,                0, 1, 1, 0                ),
    AGGREGATE(max,               1, 1, 1, minmaxStep,      minMaxFinalize ),
    FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),
    FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),

    FUNCTION(substr,             2, 0, 0, substrFunc       ),
    FUNCTION(substr,             3, 0, 0, substrFunc       ),
    FUNCTION(abs,                1, 0, 0, absFunc          ),
#ifndef SQLITE_OMIT_FLOATING_POINT
    FUNCTION(round,              1, 0, 0, roundFunc        ),
    FUNCTION(round,              2, 0, 0, roundFunc        ),
#endif







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
....
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
      double rVal = sqlite3_value_double(argv[0]);
      if( rVal<0 ) rVal = -rVal;
      sqlite3_result_double(context, rVal);
      break;
    }
  }
}

/*
** Implementation of the instr() function.
**
** instr(haystack,needle) finds the first occurrence of needle
** in haystack and returns the number of previous characters plus 1,
** or 0 if needle does not occur within haystack.
**
** If both haystack and needle are BLOBs, then the result is one more than
** the number of bytes in haystack prior to the first occurrence of needle,
** or 0 if needle never occurs in haystack.
*/
static void instrFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const unsigned char *zHaystack;
  const unsigned char *zNeedle;
  int nHaystack;
  int nNeedle;
  int typeHaystack, typeNeedle;
  int N = 1;
  int isText;

  typeHaystack = sqlite3_value_type(argv[0]);
  typeNeedle = sqlite3_value_type(argv[1]);
  if( typeHaystack==SQLITE_NULL || typeNeedle==SQLITE_NULL ) return;
  nHaystack = sqlite3_value_bytes(argv[0]);
  nNeedle = sqlite3_value_bytes(argv[1]);
  if( typeHaystack==SQLITE_BLOB && typeNeedle==SQLITE_BLOB ){
    zHaystack = sqlite3_value_blob(argv[0]);
    zNeedle = sqlite3_value_blob(argv[1]);
    isText = 0;
  }else{
    zHaystack = sqlite3_value_text(argv[0]);
    zNeedle = sqlite3_value_text(argv[1]);
    isText = 1;
  }
  while( nNeedle<=nHaystack && memcmp(zHaystack, zNeedle, nNeedle)!=0 ){
    N++;
    do{
      nHaystack--;
      zHaystack++;
    }while( isText && (zHaystack[0]&0xc0)==0x80 );
  }
  if( nNeedle>nHaystack ) N = 0;
  sqlite3_result_int(context, N);
}

/*
** Implementation of the substr() function.
**
** substr(x,p1,p2)  returns p2 characters of x[] beginning with p1.
** p1 is 1-indexed.  So substr(x,1,1) returns the first character
** of x.  If x is text, then we actually count UTF-8 characters.
................................................................................
    FUNCTION(min,                0, 0, 1, 0                ),
    AGGREGATE(min,               1, 0, 1, minmaxStep,      minMaxFinalize ),
    FUNCTION(max,               -1, 1, 1, minmaxFunc       ),
    FUNCTION(max,                0, 1, 1, 0                ),
    AGGREGATE(max,               1, 1, 1, minmaxStep,      minMaxFinalize ),
    FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),
    FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),
    FUNCTION(instr,              2, 0, 0, instrFunc        ),
    FUNCTION(substr,             2, 0, 0, substrFunc       ),
    FUNCTION(substr,             3, 0, 0, substrFunc       ),
    FUNCTION(abs,                1, 0, 0, absFunc          ),
#ifndef SQLITE_OMIT_FLOATING_POINT
    FUNCTION(round,              1, 0, 0, roundFunc        ),
    FUNCTION(round,              2, 0, 0, roundFunc        ),
#endif

Changes to src/os_unix.c.

5370
5371
5372
5373
5374
5375
5376
5377



5378


5379
5380
5381
5382
5383
5384
5385
  sqlite3_vfs *NotUsed,     /* VFS containing this as the xDelete method */
  const char *zPath,        /* Name of file to be deleted */
  int dirSync               /* If true, fsync() directory after deleting file */
){
  int rc = SQLITE_OK;
  UNUSED_PARAMETER(NotUsed);
  SimulateIOError(return SQLITE_IOERR_DELETE);
  if( osUnlink(zPath)==(-1) && errno!=ENOENT ){



    return unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);


  }
#ifndef SQLITE_DISABLE_DIRSYNC
  if( (dirSync & 1)!=0 ){
    int fd;
    rc = osOpenDirectory(zPath, &fd);
    if( rc==SQLITE_OK ){
#if OS_VXWORKS







|
>
>
>
|
>
>







5370
5371
5372
5373
5374
5375
5376
5377
5378
5379
5380
5381
5382
5383
5384
5385
5386
5387
5388
5389
5390
  sqlite3_vfs *NotUsed,     /* VFS containing this as the xDelete method */
  const char *zPath,        /* Name of file to be deleted */
  int dirSync               /* If true, fsync() directory after deleting file */
){
  int rc = SQLITE_OK;
  UNUSED_PARAMETER(NotUsed);
  SimulateIOError(return SQLITE_IOERR_DELETE);
  if( osUnlink(zPath)==(-1) ){
    if( errno==ENOENT ){
      rc = SQLITE_IOERR_DELETE_NOENT;
    }else{
      rc = unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
    }
    return rc;
  }
#ifndef SQLITE_DISABLE_DIRSYNC
  if( (dirSync & 1)!=0 ){
    int fd;
    rc = osOpenDirectory(zPath, &fd);
    if( rc==SQLITE_OK ){
#if OS_VXWORKS

Changes to src/pager.c.

3155
3156
3157
3158
3159
3160
3161

3162
3163
3164
3165
3166
3167
3168
    int isWal;                    /* True if WAL file exists */
    Pgno nPage;                   /* Size of the database file */

    rc = pagerPagecount(pPager, &nPage);
    if( rc ) return rc;
    if( nPage==0 ){
      rc = sqlite3OsDelete(pPager->pVfs, pPager->zWal, 0);

      isWal = 0;
    }else{
      rc = sqlite3OsAccess(
          pPager->pVfs, pPager->zWal, SQLITE_ACCESS_EXISTS, &isWal
      );
    }
    if( rc==SQLITE_OK ){







>







3155
3156
3157
3158
3159
3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
    int isWal;                    /* True if WAL file exists */
    Pgno nPage;                   /* Size of the database file */

    rc = pagerPagecount(pPager, &nPage);
    if( rc ) return rc;
    if( nPage==0 ){
      rc = sqlite3OsDelete(pPager->pVfs, pPager->zWal, 0);
      if( rc==SQLITE_IOERR_DELETE_NOENT ) rc = SQLITE_OK;
      isWal = 0;
    }else{
      rc = sqlite3OsAccess(
          pPager->pVfs, pPager->zWal, SQLITE_ACCESS_EXISTS, &isWal
      );
    }
    if( rc==SQLITE_OK ){

Changes to src/sqlite.h.in.

465
466
467
468
469
470
471

472
473
474
475
476
477
478
#define SQLITE_IOERR_CLOSE             (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE         (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN           (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE           (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK           (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP            (SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK              (SQLITE_IOERR | (22<<8))

#define SQLITE_LOCKED_SHAREDCACHE      (SQLITE_LOCKED |  (1<<8))
#define SQLITE_BUSY_RECOVERY           (SQLITE_BUSY   |  (1<<8))
#define SQLITE_CANTOPEN_NOTEMPDIR      (SQLITE_CANTOPEN | (1<<8))
#define SQLITE_CANTOPEN_ISDIR          (SQLITE_CANTOPEN | (2<<8))
#define SQLITE_CANTOPEN_FULLPATH       (SQLITE_CANTOPEN | (3<<8))
#define SQLITE_CORRUPT_VTAB            (SQLITE_CORRUPT | (1<<8))
#define SQLITE_READONLY_RECOVERY       (SQLITE_READONLY | (1<<8))







>







465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
#define SQLITE_IOERR_CLOSE             (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE         (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN           (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE           (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK           (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP            (SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK              (SQLITE_IOERR | (22<<8))
#define SQLITE_IOERR_DELETE_NOENT      (SQLITE_IOERR | (23<<8))
#define SQLITE_LOCKED_SHAREDCACHE      (SQLITE_LOCKED |  (1<<8))
#define SQLITE_BUSY_RECOVERY           (SQLITE_BUSY   |  (1<<8))
#define SQLITE_CANTOPEN_NOTEMPDIR      (SQLITE_CANTOPEN | (1<<8))
#define SQLITE_CANTOPEN_ISDIR          (SQLITE_CANTOPEN | (2<<8))
#define SQLITE_CANTOPEN_FULLPATH       (SQLITE_CANTOPEN | (3<<8))
#define SQLITE_CORRUPT_VTAB            (SQLITE_CORRUPT | (1<<8))
#define SQLITE_READONLY_RECOVERY       (SQLITE_READONLY | (1<<8))

Changes to src/sqliteInt.h.

1971
1972
1973
1974
1975
1976
1977

1978
1979
1980
1981
1982
1983
1984
      struct InLoop {
        int iCur;              /* The VDBE cursor used by this IN operator */
        int addrInTop;         /* Top of the IN loop */
      } *aInLoop;           /* Information about each nested IN operator */
    } in;                 /* Used when plan.wsFlags&WHERE_IN_ABLE */
    Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
  } u;


  /* The following field is really not part of the current level.  But
  ** we need a place to cache virtual table index information for each
  ** virtual table in the FROM clause and the WhereLevel structure is
  ** a convenient place since there is one WhereLevel for each FROM clause
  ** element.
  */







>







1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
      struct InLoop {
        int iCur;              /* The VDBE cursor used by this IN operator */
        int addrInTop;         /* Top of the IN loop */
      } *aInLoop;           /* Information about each nested IN operator */
    } in;                 /* Used when plan.wsFlags&WHERE_IN_ABLE */
    Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
  } u;
  double rOptCost;      /* "Optimal" cost for this level */

  /* The following field is really not part of the current level.  But
  ** we need a place to cache virtual table index information for each
  ** virtual table in the FROM clause and the WhereLevel structure is
  ** a convenient place since there is one WhereLevel for each FROM clause
  ** element.
  */

Changes to src/status.c.

205
206
207
208
209
210
211

212
213
214
215
216
217
218
    case SQLITE_DBSTATUS_STMT_USED: {
      struct Vdbe *pVdbe;         /* Used to iterate through VMs */
      int nByte = 0;              /* Used to accumulate return value */

      db->pnBytesFreed = &nByte;
      for(pVdbe=db->pVdbe; pVdbe; pVdbe=pVdbe->pNext){
        sqlite3VdbeClearObject(db, pVdbe);

      }
      db->pnBytesFreed = 0;

      *pHighwater = 0;
      *pCurrent = nByte;

      break;







>







205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
    case SQLITE_DBSTATUS_STMT_USED: {
      struct Vdbe *pVdbe;         /* Used to iterate through VMs */
      int nByte = 0;              /* Used to accumulate return value */

      db->pnBytesFreed = &nByte;
      for(pVdbe=db->pVdbe; pVdbe; pVdbe=pVdbe->pNext){
        sqlite3VdbeClearObject(db, pVdbe);
        sqlite3DbFree(db, pVdbe);
      }
      db->pnBytesFreed = 0;

      *pHighwater = 0;
      *pCurrent = nByte;

      break;

Changes to src/test_quota.c.

1175
1176
1177
1178
1179
1180
1181





1182

1183
1184
1185
1186
1187
1188
1189
    pGroup->iSize += szNew - pFile->iSize;
    quotaLeave();
  }
#if SQLITE_OS_UNIX
  rc = ftruncate(fileno(p->f), szNew);
#endif
#if SQLITE_OS_WIN





  rc = _chsize_s(_fileno(p->f), szNew);

#endif
  if( pFile && rc==0 ){
    quotaGroup *pGroup = pFile->pGroup;
    quotaEnter();
    pGroup->iSize += szNew - pFile->iSize;
    pFile->iSize = szNew;
    quotaLeave();







>
>
>
>
>
|
>







1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
    pGroup->iSize += szNew - pFile->iSize;
    quotaLeave();
  }
#if SQLITE_OS_UNIX
  rc = ftruncate(fileno(p->f), szNew);
#endif
#if SQLITE_OS_WIN
#  if defined(__MINGW32__) && defined(SQLITE_TEST)
     /* _chsize_s() is missing from MingW (as of 2012-11-06).  Use
     ** _chsize() as a work-around for testing purposes. */
     rc = _chsize(_fileno(p->f), (long)szNew);
#  else
     rc = _chsize_s(_fileno(p->f), szNew);
#  endif
#endif
  if( pFile && rc==0 ){
    quotaGroup *pGroup = pFile->pGroup;
    quotaEnter();
    pGroup->iSize += szNew - pFile->iSize;
    pFile->iSize = szNew;
    quotaLeave();

Changes to src/where.c.

5098
5099
5100
5101
5102
5103
5104













5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
5115
5116
5117
5118
5119
5120
....
5121
5122
5123
5124
5125
5126
5127
5128
5129
5130
5131
5132
5133
5134
5135
5136
5137
        assert( sWBI.pSrc->pIndex==0 
                  || (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0
                  || sWBI.cost.plan.u.pIdx==sWBI.pSrc->pIndex );

        if( isOptimal && (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ){
          notIndexed |= m;
        }














        /* Conditions under which this table becomes the best so far:
        **
        **   (1) The table must not depend on other tables that have not
        **       yet run.  (In other words, it must not depend on tables
        **       in inner loops.)
        **
        **   (2) A full-table-scan plan cannot supercede indexed plan unless
        **       the full-table-scan is an "optimal" plan as defined above.
        **
        **   (3) All tables have an INDEXED BY clause or this table lacks an
        **       INDEXED BY clause or this table uses the specific
        **       index specified by its INDEXED BY clause.  This rule ensures
        **       that a best-so-far is always selected even if an impossible
        **       combination of INDEXED BY clauses are given.  The error
        **       will be detected and relayed back to the application later.
................................................................................
        **       The NEVER() comes about because rule (2) above prevents
        **       An indexable full-table-scan from reaching rule (3).
        **
        **   (4) The plan cost must be lower than prior plans, where "cost"
        **       is defined by the compareCost() function above. 
        */
        if( (sWBI.cost.used&sWBI.notValid)==0                    /* (1) */
            && (bestJ<0 || (notIndexed&m)!=0                     /* (2) */
                || (bestPlan.plan.wsFlags & WHERE_NOT_FULLSCAN)==0
                || (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)
            && (nUnconstrained==0 || sWBI.pSrc->pIndex==0        /* (3) */
                || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
            && (bestJ<0 || compareCost(&sWBI.cost, &bestPlan))   /* (4) */
        ){
          WHERETRACE(("   === table %d (%s) is best so far\n"
                      "       cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=%08x\n",
                      j, sWBI.pSrc->pTab->zName,







>
>
>
>
>
>
>
>
>
>
>
>
>







|
|







 







<
<
<







5098
5099
5100
5101
5102
5103
5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
5115
5116
5117
5118
5119
5120
5121
5122
5123
5124
5125
5126
5127
5128
5129
5130
5131
5132
5133
....
5134
5135
5136
5137
5138
5139
5140



5141
5142
5143
5144
5145
5146
5147
        assert( sWBI.pSrc->pIndex==0 
                  || (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0
                  || sWBI.cost.plan.u.pIdx==sWBI.pSrc->pIndex );

        if( isOptimal && (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ){
          notIndexed |= m;
        }
        if( isOptimal ){
          pWInfo->a[j].rOptCost = sWBI.cost.rCost;
        }else if( iFrom<nTabList-1 ){
          /* If two or more tables have nearly the same outer loop cost,
          ** very different inner loop (optimal) cost, we want to choose
          ** for the outer loop that table which benefits the least from
          ** being in the inner loop.  The following code scales the 
          ** outer loop cost estimate to accomplish that. */
          WHERETRACE(("   scaling cost from %.1f to %.1f\n",
                      sWBI.cost.rCost,
                      sWBI.cost.rCost/pWInfo->a[j].rOptCost));
          sWBI.cost.rCost /= pWInfo->a[j].rOptCost;
        }

        /* Conditions under which this table becomes the best so far:
        **
        **   (1) The table must not depend on other tables that have not
        **       yet run.  (In other words, it must not depend on tables
        **       in inner loops.)
        **
        **   (2) (This rule was removed on 2012-11-09.  The scaling of the
        **       cost using the optimal scan cost made this rule obsolete.)
        **
        **   (3) All tables have an INDEXED BY clause or this table lacks an
        **       INDEXED BY clause or this table uses the specific
        **       index specified by its INDEXED BY clause.  This rule ensures
        **       that a best-so-far is always selected even if an impossible
        **       combination of INDEXED BY clauses are given.  The error
        **       will be detected and relayed back to the application later.
................................................................................
        **       The NEVER() comes about because rule (2) above prevents
        **       An indexable full-table-scan from reaching rule (3).
        **
        **   (4) The plan cost must be lower than prior plans, where "cost"
        **       is defined by the compareCost() function above. 
        */
        if( (sWBI.cost.used&sWBI.notValid)==0                    /* (1) */



            && (nUnconstrained==0 || sWBI.pSrc->pIndex==0        /* (3) */
                || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
            && (bestJ<0 || compareCost(&sWBI.cost, &bestPlan))   /* (4) */
        ){
          WHERETRACE(("   === table %d (%s) is best so far\n"
                      "       cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=%08x\n",
                      j, sWBI.pSrc->pTab->zName,

Changes to test/aggnested.test.

63
64
65
66
67
68
69
70
































































































































































71
    REPLACE INTO t2 VALUES(2,99,999,9999);
    SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),
            t1.* 
    FROM t1;
  }
} {A,B,B 3 33 333 3333}
db2 close

































































































































































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
    REPLACE INTO t2 VALUES(2,99,999,9999);
    SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),
            t1.* 
    FROM t1;
  }
} {A,B,B 3 33 333 3333}
db2 close

##################### Test cases for ticket [bfbf38e5e9956ac69f] ############
#
# This first test case is the original problem report:
do_test aggnested-3.0 {
  db eval {
    CREATE TABLE AAA (
      aaa_id       INTEGER PRIMARY KEY AUTOINCREMENT
    );
    CREATE TABLE RRR (
      rrr_id      INTEGER     PRIMARY KEY AUTOINCREMENT,
      rrr_date    INTEGER     NOT NULL,
      rrr_aaa     INTEGER
    );
    CREATE TABLE TTT (
      ttt_id      INTEGER PRIMARY KEY AUTOINCREMENT,
      target_aaa  INTEGER NOT NULL,
      source_aaa  INTEGER NOT NULL
    );
    insert into AAA (aaa_id) values (2);
    insert into TTT (ttt_id, target_aaa, source_aaa)
    values (4469, 2, 2);
    insert into TTT (ttt_id, target_aaa, source_aaa)
    values (4476, 2, 1);
    insert into RRR (rrr_id, rrr_date, rrr_aaa)
    values (0, 0, NULL);
    insert into RRR (rrr_id, rrr_date, rrr_aaa)
    values (2, 4312, 2);
    SELECT i.aaa_id,
      (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END)
         FROM TTT t
      ) AS segfault
    FROM
     (SELECT curr.rrr_aaa as aaa_id
        FROM RRR curr
          -- you also can comment out the next line
          -- it causes segfault to happen after one row is outputted
          INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id)
          LEFT JOIN RRR r ON (r.rrr_id <> 0 AND r.rrr_date < curr.rrr_date)
       GROUP BY curr.rrr_id
      HAVING r.rrr_date IS NULL
    ) i;
  }
} {2 1}

# Further variants of the test case, as found in the ticket
#
do_test aggnested-3.1 {
  db eval {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1 (
      id1 INTEGER PRIMARY KEY AUTOINCREMENT,
      value1 INTEGER
    );
    INSERT INTO t1 VALUES(4469,2),(4476,1);
    CREATE TABLE t2 (
      id2 INTEGER PRIMARY KEY AUTOINCREMENT,
      value2 INTEGER
    );
    INSERT INTO t2 VALUES(0,1),(2,2);
    SELECT
     (SELECT sum(value2==xyz) FROM t2)
    FROM
     (SELECT curr.value1 as xyz
        FROM t1 AS curr LEFT JOIN t1 AS other
       GROUP BY curr.id1);
  }
} {1 1}
do_test aggnested-3.2 {
  db eval {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1 (
      id1 INTEGER,
      value1 INTEGER,
      x1 INTEGER
    );
    INSERT INTO t1 VALUES(4469,2,98),(4469,1,99),(4469,3,97);
    CREATE TABLE t2 (
      value2 INTEGER
    );
    INSERT INTO t2 VALUES(1);
    SELECT
     (SELECT sum(value2==xyz) FROM t2)
    FROM
     (SELECT value1 as xyz, max(x1) AS pqr
        FROM t1
       GROUP BY id1);
  }
} {0}
do_test aggnested-3.3 {
  db eval {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(id1, value1);
    INSERT INTO t1 VALUES(4469,2),(4469,1);
    CREATE TABLE t2 (value2);
    INSERT INTO t2 VALUES(1);
    SELECT (SELECT sum(value2=value1) FROM t2), max(value1)
      FROM t1
     GROUP BY id1;
  }
} {0 2}

# A batch of queries all doing approximately the same operation involving
# two nested aggregate queries.
#
do_test aggnested-3.11 {
  db eval {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(id1, value1);
    INSERT INTO t1 VALUES(4469,12),(4469,11),(4470,34);
    CREATE INDEX t1id1 ON t1(id1);
    CREATE TABLE t2 (value2);
    INSERT INTO t2 VALUES(12),(34),(34);
    INSERT INTO t2 SELECT value2 FROM t2;

    SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=max(value1))
      FROM t1
     GROUP BY id1;
  }
} {12 2 34 4}
do_test aggnested-3.12 {
  db eval {
    SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=value1)
      FROM t1
     GROUP BY id1;
  }
} {12 2 34 4}
do_test aggnested-3.13 {
  db eval {
    SELECT value1, (SELECT sum(value2=value1) FROM t2)
      FROM t1;
  }
} {12 2 11 0 34 4}
do_test aggnested-3.14 {
  db eval {
    SELECT value1, (SELECT sum(value2=value1) FROM t2)
      FROM t1
     WHERE value1 IN (SELECT max(value1) FROM t1 GROUP BY id1);
  }
} {12 2 34 4}
do_test aggnested-3.15 {
  # FIXME:  If case 3.16 works, then this case really ought to work too...
  catchsql {
    SELECT max(value1), (SELECT sum(value2=max(value1)) FROM t2)
      FROM t1
     GROUP BY id1;
  }
} {1 {misuse of aggregate function max()}}
do_test aggnested-3.16 {
  db eval {
    SELECT max(value1), (SELECT sum(value2=value1) FROM t2)
      FROM t1
     GROUP BY id1;
  }
} {12 2 34 4}
 

finish_test

Added test/instr.test.





































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
# 2012 October 24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the built-in INSTR() functions.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table to work with.
#
do_test instr-1.1 {
  db eval {SELECT instr('abcdefg','a');}
} {1}
do_test instr-1.2 {
  db eval {SELECT instr('abcdefg','b');}
} {2}
do_test instr-1.3 {
  db eval {SELECT instr('abcdefg','c');}
} {3}
do_test instr-1.4 {
  db eval {SELECT instr('abcdefg','d');}
} {4}
do_test instr-1.5 {
  db eval {SELECT instr('abcdefg','e');}
} {5}
do_test instr-1.6 {
  db eval {SELECT instr('abcdefg','f');}
} {6}
do_test instr-1.7 {
  db eval {SELECT instr('abcdefg','g');}
} {7}
do_test instr-1.8 {
  db eval {SELECT instr('abcdefg','h');}
} {0}
do_test instr-1.9 {
  db eval {SELECT instr('abcdefg','abcdefg');}
} {1}
do_test instr-1.10 {
  db eval {SELECT instr('abcdefg','abcdefgh');}
} {0}
do_test instr-1.11 {
  db eval {SELECT instr('abcdefg','bcdefg');}
} {2}
do_test instr-1.12 {
  db eval {SELECT instr('abcdefg','bcdefgh');}
} {0}
do_test instr-1.13 {
  db eval {SELECT instr('abcdefg','cdefg');}
} {3}
do_test instr-1.14 {
  db eval {SELECT instr('abcdefg','cdefgh');}
} {0}
do_test instr-1.15 {
  db eval {SELECT instr('abcdefg','defg');}
} {4}
do_test instr-1.16 {
  db eval {SELECT instr('abcdefg','defgh');}
} {0}
do_test instr-1.17 {
  db eval {SELECT instr('abcdefg','efg');}
} {5}
do_test instr-1.18 {
  db eval {SELECT instr('abcdefg','efgh');}
} {0}
do_test instr-1.19 {
  db eval {SELECT instr('abcdefg','fg');}
} {6}
do_test instr-1.20 {
  db eval {SELECT instr('abcdefg','fgh');}
} {0}
do_test instr-1.21 {
  db eval {SELECT coalesce(instr('abcdefg',NULL),'nil');}
} {nil}
do_test instr-1.22 {
  db eval {SELECT coalesce(instr(NULL,'x'),'nil');}
} {nil}
do_test instr-1.23 {
  db eval {SELECT instr(12345,34);}
} {3}
do_test instr-1.24 {
  db eval {SELECT instr(123456.78,34);}
} {3}
do_test instr-1.25 {
  db eval {SELECT instr(123456.78,x'3334');}
} {3}
do_test instr-1.26 {
  db eval {SELECT instr('äbcdefg','efg');}
} {5}
do_test instr-1.27 {
  db eval {SELECT instr('€xyzzy','xyz');}
} {2}
do_test instr-1.28 {
  db eval {SELECT instr('abc€xyzzy','xyz');}
} {5}
do_test instr-1.29 {
  db eval {SELECT instr('abc€xyzzy','€xyz');}
} {4}
do_test instr-1.30 {
  db eval {SELECT instr('abc€xyzzy','c€xyz');}
} {3}
do_test instr-1.31 {
  db eval {SELECT instr(x'0102030405',x'01');}
} {1}
do_test instr-1.32 {
  db eval {SELECT instr(x'0102030405',x'02');}
} {2}
do_test instr-1.33 {
  db eval {SELECT instr(x'0102030405',x'03');}
} {3}
do_test instr-1.34 {
  db eval {SELECT instr(x'0102030405',x'04');}
} {4}
do_test instr-1.35 {
  db eval {SELECT instr(x'0102030405',x'05');}
} {5}
do_test instr-1.36 {
  db eval {SELECT instr(x'0102030405',x'06');}
} {0}
do_test instr-1.37 {
  db eval {SELECT instr(x'0102030405',x'0102030405');}
} {1}
do_test instr-1.38 {
  db eval {SELECT instr(x'0102030405',x'02030405');}
} {2}
do_test instr-1.39 {
  db eval {SELECT instr(x'0102030405',x'030405');}
} {3}
do_test instr-1.40 {
  db eval {SELECT instr(x'0102030405',x'0405');}
} {4}
do_test instr-1.41 {
  db eval {SELECT instr(x'0102030405',x'0506');}
} {0}
do_test instr-1.42 {
  db eval {SELECT instr(x'0102030405',x'');}
} {1}
do_test instr-1.43 {
  db eval {SELECT instr(x'',x'');}
} {1}
do_test instr-1.44 {
  db eval {SELECT instr('','');}
} {1}
do_test instr-1.45 {
  db eval {SELECT instr('abcdefg','');}
} {1}
unset -nocomplain longstr
set longstr abcdefghijklmonpqrstuvwxyz
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
append longstr $longstr
# puts [string length $longstr]
append longstr Xabcde
do_test instr-1.46 {
  db eval {SELECT instr($longstr,'X');}
} {106497}
do_test instr-1.47 {
  db eval {SELECT instr($longstr,'Y');}
} {0}
do_test instr-1.48 {
  db eval {SELECT instr($longstr,'Xa');}
} {106497}
do_test instr-1.49 {
  db eval {SELECT instr($longstr,'zXa');}
} {106496}
set longstr [string map {a ä} $longstr]
do_test instr-1.50 {
  db eval {SELECT instr($longstr,'X');}
} {106497}
do_test instr-1.51 {
  db eval {SELECT instr($longstr,'Y');}
} {0}
do_test instr-1.52 {
  db eval {SELECT instr($longstr,'Xä');}
} {106497}
do_test instr-1.53 {
  db eval {SELECT instr($longstr,'zXä');}
} {106496}
do_test instr-1.54 {
  db eval {SELECT instr(x'78c3a4e282ac79','x');}
} {1}
do_test instr-1.55 {
  db eval {SELECT instr(x'78c3a4e282ac79','y');}
} {4}
do_test instr-1.56 {
  db eval {SELECT instr(x'78c3a4e282ac79',x'79');}
} {7}
do_test instr-1.57 {
  db eval {SELECT instr('xä€y',x'79');}
} {4}


finish_test

Changes to test/orderby1.test.

44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
...
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
...
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
        (NULL, 2, 1, 'two-a'),
        (NULL, 3, 1, 'three-a');
    COMMIT;
  }
} {}
do_test 1.1a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}

# Verify that the ORDER BY clause is optimized out
#
do_test 1.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 1.2a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}

# The output is sorted manually in this case.
#
do_test 1.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 1.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 1.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Reverse order sorts
#
do_test 1.4a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 1.4b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 1.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {~/ORDER BY/}  ;# optimized out


do_test 1.5a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 1.5b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
do_test 1.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {~/ORDER BY/}  ;# optimized out

do_test 1.6a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 1.6b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 1.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized-out


# Reconstruct the test data to use indices rather than integer primary keys.
#
do_test 2.0 {
................................................................................
        (20, 1, 'two-a'),
        (3,  1, 'three-a');
    COMMIT;
  }
} {}
do_test 2.1a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}

# Verify that the ORDER BY clause is optimized out
#
do_test 2.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

do_test 2.1c {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 2.1d {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 2.2a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}

# The output is sorted manually in this case.
#
do_test 2.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 2.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 2.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Reverse order sorts
#
do_test 2.4a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 2.4b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 2.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {~/ORDER BY/}  ;# optimized out


do_test 2.5a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 2.5b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
do_test 2.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {~/ORDER BY/}  ;# optimized out

do_test 2.6a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 2.6b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 2.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out


# Generate another test dataset, but this time using mixed ASC/DESC indices.
#
do_test 3.0 {
................................................................................
        (NULL, 2, 1, 'two-a'),
        (NULL, 3, 1, 'three-a');
    COMMIT;
  }
} {}
do_test 3.1a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}

# Verify that the ORDER BY clause is optimized out
#
do_test 3.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 3.2a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}

# The output is sorted manually in this case.
#
do_test 3.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 3.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 3.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Without the mixed ASC/DESC on ORDER BY
#
do_test 3.4a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 3.4b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}  ;# verify same order after sorting
do_test 3.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {~/ORDER BY/}  ;# optimized out


do_test 3.5a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 3.5b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 3.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# optimzed out


do_test 3.6a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 3.6b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 3.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {~/ORDER BY/}  ;# inverted ASC/DESC is optimized out


finish_test







|








|








|








|









|





|









|




|





|






|




|





|





|




|





|







 







|








|





|





|








|








|









|





|









|




|





|






|




|





|





|




|





|







 







|








|








|








|









|





|









|




|





|






|




|





|






|




|





|





44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
...
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
...
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
        (NULL, 2, 1, 'two-a'),
        (NULL, 3, 1, 'three-a');
    COMMIT;
  }
} {}
do_test 1.1a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}

# Verify that the ORDER BY clause is optimized out
#
do_test 1.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 1.2a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}

# The output is sorted manually in this case.
#
do_test 1.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 1.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 1.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Reverse order sorts
#
do_test 1.4a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 1.4b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 1.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {~/ORDER BY/}  ;# optimized out


do_test 1.5a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 1.5b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
do_test 1.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {~/ORDER BY/}  ;# optimized out

do_test 1.6a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 1.6b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 1.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized-out


# Reconstruct the test data to use indices rather than integer primary keys.
#
do_test 2.0 {
................................................................................
        (20, 1, 'two-a'),
        (3,  1, 'three-a');
    COMMIT;
  }
} {}
do_test 2.1a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}

# Verify that the ORDER BY clause is optimized out
#
do_test 2.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

do_test 2.1c {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, aid, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 2.1d {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, aid, tn
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 2.2a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}

# The output is sorted manually in this case.
#
do_test 2.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 2.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 2.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Reverse order sorts
#
do_test 2.4a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 2.4b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 2.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {~/ORDER BY/}  ;# optimized out


do_test 2.5a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 2.5b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
do_test 2.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {~/ORDER BY/}  ;# optimized out

do_test 2.6a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 2.6b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 2.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out


# Generate another test dataset, but this time using mixed ASC/DESC indices.
#
do_test 3.0 {
................................................................................
        (NULL, 2, 1, 'two-a'),
        (NULL, 3, 1, 'three-a');
    COMMIT;
  }
} {}
do_test 3.1a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}

# Verify that the ORDER BY clause is optimized out
#
do_test 3.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 3.2a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}

# The output is sorted manually in this case.
#
do_test 3.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 3.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 3.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Without the mixed ASC/DESC on ORDER BY
#
do_test 3.4a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 3.4b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}  ;# verify same order after sorting
do_test 3.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
  }
} {~/ORDER BY/}  ;# optimized out


do_test 3.5a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 3.5b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 3.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# optimzed out


do_test 3.6a {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 3.6b {
  db eval {
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 3.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {~/ORDER BY/}  ;# inverted ASC/DESC is optimized out


finish_test

Changes to test/pager1.test.

2482
2483
2484
2485
2486
2487
2488
2489




























2490
  }
  db close
  file size test.db
} {20971520}

# Cleanup 20MB file left by the previous test.
forcedelete test.db





























finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
  }
  db close
  file size test.db
} {20971520}

# Cleanup 20MB file left by the previous test.
forcedelete test.db

#-------------------------------------------------------------------------
# Test that if a transaction is committed in journal_mode=DELETE mode,
# and the call to unlink() returns an ENOENT error, the COMMIT does not
# succeed.
#
if {$::tcl_platform(platform)=="unix"} {
  do_test pager1-33.1 {
    sqlite3 db test.db
    execsql {
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES('one');
      INSERT INTO t1 VALUES('two');
      BEGIN;
        INSERT INTO t1 VALUES('three');
        INSERT INTO t1 VALUES('four');
    }
    forcedelete bak-journal
    file rename test.db-journal bak-journal

    catchsql COMMIT
  } {1 {disk I/O error}}

  do_test pager1-33.2 {
    file rename bak-journal test.db-journal
    execsql { SELECT * FROM t1 }
  } {one two}
}

finish_test

Added test/whereE.test.





























































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
# 2012 November 9
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the query planner to make sure it
# is making good planning decisions.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix whereE

do_execsql_test 1.1 {
  CREATE TABLE t1(a,b);
  INSERT INTO t1 VALUES(1,10), (2,20), (3,30), (2,22), (3, 33);
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  ALTER TABLE t1 ADD COLUMN c;
  UPDATE t1 SET c=a*rowid+10000;
  CREATE INDEX t1ab ON t1(a,b);
  
  CREATE TABLE t2(x,y);
  INSERT INTO t2 VALUES(4,44),(5,55),(6,66),(7,77);
  INSERT INTO t2 SELECT x+4, (x+4)*11 FROM t2;
  INSERT INTO t2 SELECT x+8, (x+8)*11 FROM t2;
  INSERT INTO t2 SELECT x+16, (x+16)*11 FROM t2;
  INSERT INTO t2 SELECT x+32, (x+32)*11 FROM t2;
  INSERT INTO t2 SELECT x+64, (x+32)*11 FROM t2;
  ALTER TABLE t2 ADD COLUMN z;
  UPDATE t2 SET z=2;
  CREATE UNIQUE INDEX t2zx ON t2(z,x);

  EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x;
} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
do_execsql_test 1.2 {
  EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x;
} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
do_execsql_test 1.3 {
  ANALYZE;
  EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x;
} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
do_execsql_test 1.4 {
  EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x;
} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}

finish_test

Added test/whereF.test.







































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
# 2012 November 9
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 
# Test cases for query planning decisions.


#
# The tests in this file demonstrate the behaviour of the query planner
# in determining the order in which joined tables are scanned.
#
# Assume there are two tables being joined - t1 and t2. Each has a cost
# if it is the outer loop, and a cost if it is the inner loop. As follows:
#
#   t1(outer) - cost of scanning t1 as the outer loop.
#   t1(inner) - cost of scanning t1 as the inner loop.
#   t2(outer) - cost of scanning t2 as the outer loop.
#   t2(inner) - cost of scanning t2 as the inner loop.
#
# Depending on the order in which the planner nests the scans, the total
# cost of the join query is one of:
#
#   t1(outer) * t2(inner)
#   t2(outer) * t1(inner)
#
# The tests in this file attempt to verify that the planner nests joins in
# the correct order when the following are true:
#
#   + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer)
#   +  t1(outer) < t2(outer)
#
# In other words, when the best overall query plan has t2 as the outer loop,
# but when the outer loop is considered independent of the inner, t1 is the
# most efficient choice.
#
# In order to make them more predictable, automatic indexes are turned off for
# the tests in this file.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix x

do_execsql_test 1.0 {
  PRAGMA automatic_index = 0;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);
  CREATE UNIQUE INDEX i1 ON t1(a);
  CREATE UNIQUE INDEX i2 ON t2(d);
} {}

foreach {tn sql} {
  1 "SELECT * FROM t1,           t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
  2 "SELECT * FROM t2,           t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
  3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
} {
  do_test 1.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}

do_execsql_test 2.0 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);

  CREATE UNIQUE INDEX i1 ON t1(a);
  CREATE UNIQUE INDEX i2 ON t1(b);
  CREATE UNIQUE INDEX i3 ON t2(d);
} {}

foreach {tn sql} {
  1 "SELECT * FROM t1,           t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
  2 "SELECT * FROM t2,           t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
  3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
} {
  do_test 2.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}

do_execsql_test 3.0 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);

  CREATE UNIQUE INDEX i1 ON t1(a, b);
  CREATE INDEX i2 ON t2(d);
} {}

foreach {tn sql} {
  1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2 
     WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}

  2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1 
     WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}

  3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 
     WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
} {
  do_test 3.$tn {
    db eval "EXPLAIN QUERY PLAN $sql"
   } {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}

finish_test