/ Check-in [ddd5d789]
Login

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

Overview
Comment:Merge the covering-index-scan optimization into trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ddd5d789e7ae4a66cd7b7fa79e48d2777f95350b
User & Date: drh 2012-09-19 00:35:31
Context
2012-09-19
17:31
Add comments to the WHERE_DISTINCT_* macros. No changes to code. check-in: 82320501 user: drh tags: trunk
00:35
Merge the covering-index-scan optimization into trunk. check-in: ddd5d789 user: drh tags: trunk
2012-09-18
23:21
Silence harmless compiler warning seen with SQLITE_OMIT_DEPRECATED. check-in: c5cee969 user: mistachkin tags: trunk
2012-09-17
21:24
Make sure the WHERE_IDX_ONLY flag is not set on query plans that will not be using an index. Closed-Leaf check-in: 698b2a28 user: drh tags: fullscan-covering-index
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/global.c.

129
130
131
132
133
134
135




136
137
138
139
140
141
142
143
144

145
146
147
148
149
150
151
};
#endif

#ifndef SQLITE_USE_URI
# define  SQLITE_USE_URI 0
#endif





/*
** The following singleton contains the global configuration for
** the SQLite library.
*/
SQLITE_WSD struct Sqlite3Config sqlite3Config = {
   SQLITE_DEFAULT_MEMSTATUS,  /* bMemstat */
   1,                         /* bCoreMutex */
   SQLITE_THREADSAFE==1,      /* bFullMutex */
   SQLITE_USE_URI,            /* bOpenUri */

   0x7ffffffe,                /* mxStrlen */
   128,                       /* szLookaside */
   500,                       /* nLookaside */
   {0,0,0,0,0,0,0,0},         /* m */
   {0,0,0,0,0,0,0,0,0},       /* mutex */
   {0,0,0,0,0,0,0,0,0,0,0,0,0},/* pcache2 */
   (void*)0,                  /* pHeap */







>
>
>
>









>







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
};
#endif

#ifndef SQLITE_USE_URI
# define  SQLITE_USE_URI 0
#endif

#ifndef SQLITE_ALLOW_COVERING_INDEX_SCAN
# define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
#endif

/*
** The following singleton contains the global configuration for
** the SQLite library.
*/
SQLITE_WSD struct Sqlite3Config sqlite3Config = {
   SQLITE_DEFAULT_MEMSTATUS,  /* bMemstat */
   1,                         /* bCoreMutex */
   SQLITE_THREADSAFE==1,      /* bFullMutex */
   SQLITE_USE_URI,            /* bOpenUri */
   SQLITE_ALLOW_COVERING_INDEX_SCAN,   /* bUseCis */
   0x7ffffffe,                /* mxStrlen */
   128,                       /* szLookaside */
   500,                       /* nLookaside */
   {0,0,0,0,0,0,0,0},         /* m */
   {0,0,0,0,0,0,0,0,0},       /* mutex */
   {0,0,0,0,0,0,0,0,0,0,0,0,0},/* pcache2 */
   (void*)0,                  /* pHeap */

Changes to src/main.c.

470
471
472
473
474
475
476





477
478
479
480
481
482
483
      break;
    }

    case SQLITE_CONFIG_URI: {
      sqlite3GlobalConfig.bOpenUri = va_arg(ap, int);
      break;
    }






    default: {
      rc = SQLITE_ERROR;
      break;
    }
  }
  va_end(ap);







>
>
>
>
>







470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
      break;
    }

    case SQLITE_CONFIG_URI: {
      sqlite3GlobalConfig.bOpenUri = va_arg(ap, int);
      break;
    }

    case SQLITE_CONFIG_COVERING_INDEX_SCAN: {
      sqlite3GlobalConfig.bUseCis = va_arg(ap, int);
      break;
    }

    default: {
      rc = SQLITE_ERROR;
      break;
    }
  }
  va_end(ap);

Changes to src/sqlite.h.in.

1558
1559
1560
1561
1562
1563
1564












1565
1566
1567
1568
1569
1570
1571
....
1584
1585
1586
1587
1588
1589
1590

1591
1592
1593
1594
1595
1596
1597
** specified as part of [ATTACH] commands are interpreted as URIs, regardless
** of whether or not the [SQLITE_OPEN_URI] flag is set when the database
** connection is opened. If it is globally disabled, filenames are
** only interpreted as URIs if the SQLITE_OPEN_URI flag is set when the
** database connection is opened. By default, URI handling is globally
** disabled. The default value may be changed by compiling with the
** [SQLITE_USE_URI] symbol defined.












**
** [[SQLITE_CONFIG_PCACHE]] [[SQLITE_CONFIG_GETPCACHE]]
** <dt>SQLITE_CONFIG_PCACHE and SQLITE_CONFIG_GETPCACHE
** <dd> These options are obsolete and should not be used by new code.
** They are retained for backwards compatibility but are now no-ops.
** </dl>
*/
................................................................................
#define SQLITE_CONFIG_LOOKASIDE    13  /* int int */
#define SQLITE_CONFIG_PCACHE       14  /* no-op */
#define SQLITE_CONFIG_GETPCACHE    15  /* no-op */
#define SQLITE_CONFIG_LOG          16  /* xFunc, void* */
#define SQLITE_CONFIG_URI          17  /* int */
#define SQLITE_CONFIG_PCACHE2      18  /* sqlite3_pcache_methods2* */
#define SQLITE_CONFIG_GETPCACHE2   19  /* sqlite3_pcache_methods2* */


/*
** CAPI3REF: Database Connection Configuration Options
**
** These constants are the available integer configuration options that
** can be passed as the second argument to the [sqlite3_db_config()] interface.
**







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







 







>







1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
....
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
** specified as part of [ATTACH] commands are interpreted as URIs, regardless
** of whether or not the [SQLITE_OPEN_URI] flag is set when the database
** connection is opened. If it is globally disabled, filenames are
** only interpreted as URIs if the SQLITE_OPEN_URI flag is set when the
** database connection is opened. By default, URI handling is globally
** disabled. The default value may be changed by compiling with the
** [SQLITE_USE_URI] symbol defined.
**
** [[SQLITE_CONFIG_COVERING_INDEX_SCAN]] <dt>SQLITE_CONFIG_COVERING_INDEX_SCAN
** <dd> This option taks a single integer argument which is interpreted as
** a boolean in order to enable or disable the use of covering indices for
** full table scans in the query optimizer.  The default setting is determined
** by the [SQLITE_ALLOW_COVERING_INDEX_SCAN] compile-time option, or is "on"
** if that compile-time option is omitted.
** The ability to disable the use of covering indices for full table scans
** is because some incorrectly coded legacy applications might malfunction
** malfunction when the optimization is enabled.  Providing the ability to
** disable the optimization allows the older, buggy application code to work
** without change even with newer versions of SQLite.
**
** [[SQLITE_CONFIG_PCACHE]] [[SQLITE_CONFIG_GETPCACHE]]
** <dt>SQLITE_CONFIG_PCACHE and SQLITE_CONFIG_GETPCACHE
** <dd> These options are obsolete and should not be used by new code.
** They are retained for backwards compatibility but are now no-ops.
** </dl>
*/
................................................................................
#define SQLITE_CONFIG_LOOKASIDE    13  /* int int */
#define SQLITE_CONFIG_PCACHE       14  /* no-op */
#define SQLITE_CONFIG_GETPCACHE    15  /* no-op */
#define SQLITE_CONFIG_LOG          16  /* xFunc, void* */
#define SQLITE_CONFIG_URI          17  /* int */
#define SQLITE_CONFIG_PCACHE2      18  /* sqlite3_pcache_methods2* */
#define SQLITE_CONFIG_GETPCACHE2   19  /* sqlite3_pcache_methods2* */
#define SQLITE_CONFIG_COVERING_INDEX_SCAN 20  /* int */

/*
** CAPI3REF: Database Connection Configuration Options
**
** These constants are the available integer configuration options that
** can be passed as the second argument to the [sqlite3_db_config()] interface.
**

Changes to src/sqliteInt.h.

964
965
966
967
968
969
970

971
972
973
974
975
976
977
....
2457
2458
2459
2460
2461
2462
2463

2464
2465
2466
2467
2468
2469
2470
*/
#define SQLITE_QueryFlattener 0x01   /* Disable query flattening */
#define SQLITE_ColumnCache    0x02   /* Disable the column cache */
#define SQLITE_GroupByOrder   0x04   /* Disable GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x08   /* Disable factoring out constants */
#define SQLITE_IdxRealAsInt   0x10   /* Store REAL as INT in indices */
#define SQLITE_DistinctOpt    0x20   /* DISTINCT using indexes */

#define SQLITE_OptMask        0xff   /* Mask of all disablable opts */

/*
** Possible values for the sqlite.magic field.
** The numbers are obtained at random and have no special meaning, other
** than being distinct from one another.
*/
................................................................................
** This structure also contains some state information.
*/
struct Sqlite3Config {
  int bMemstat;                     /* True to enable memory status */
  int bCoreMutex;                   /* True to enable core mutexing */
  int bFullMutex;                   /* True to enable full mutexing */
  int bOpenUri;                     /* True to interpret filenames as URIs */

  int mxStrlen;                     /* Maximum string length */
  int szLookaside;                  /* Default lookaside buffer size */
  int nLookaside;                   /* Default lookaside buffer count */
  sqlite3_mem_methods m;            /* Low-level memory allocation interface */
  sqlite3_mutex_methods mutex;      /* Low-level mutex interface */
  sqlite3_pcache_methods2 pcache2;  /* Low-level page-cache interface */
  void *pHeap;                      /* Heap storage space */







>







 







>







964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
....
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
*/
#define SQLITE_QueryFlattener 0x01   /* Disable query flattening */
#define SQLITE_ColumnCache    0x02   /* Disable the column cache */
#define SQLITE_GroupByOrder   0x04   /* Disable GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x08   /* Disable factoring out constants */
#define SQLITE_IdxRealAsInt   0x10   /* Store REAL as INT in indices */
#define SQLITE_DistinctOpt    0x20   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x40   /* Disable covering index scans */
#define SQLITE_OptMask        0xff   /* Mask of all disablable opts */

/*
** Possible values for the sqlite.magic field.
** The numbers are obtained at random and have no special meaning, other
** than being distinct from one another.
*/
................................................................................
** This structure also contains some state information.
*/
struct Sqlite3Config {
  int bMemstat;                     /* True to enable memory status */
  int bCoreMutex;                   /* True to enable core mutexing */
  int bFullMutex;                   /* True to enable full mutexing */
  int bOpenUri;                     /* True to interpret filenames as URIs */
  int bUseCis;                      /* Use covering indices for full-scans */
  int mxStrlen;                     /* Maximum string length */
  int szLookaside;                  /* Default lookaside buffer size */
  int nLookaside;                   /* Default lookaside buffer count */
  sqlite3_mem_methods m;            /* Low-level memory allocation interface */
  sqlite3_mutex_methods mutex;      /* Low-level mutex interface */
  sqlite3_pcache_methods2 pcache2;  /* Low-level page-cache interface */
  void *pHeap;                      /* Heap storage space */

Changes to src/test1.c.

5936
5937
5938
5939
5940
5941
5942

5943
5944
5945
5946
5947
5948
5949
    { "all",              SQLITE_OptMask        },
    { "query-flattener",  SQLITE_QueryFlattener },
    { "column-cache",     SQLITE_ColumnCache    },
    { "groupby-order",    SQLITE_GroupByOrder   },
    { "factor-constants", SQLITE_FactorOutConst },
    { "real-as-int",      SQLITE_IdxRealAsInt   },
    { "distinct-opt",     SQLITE_DistinctOpt    },

  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;







>







5936
5937
5938
5939
5940
5941
5942
5943
5944
5945
5946
5947
5948
5949
5950
    { "all",              SQLITE_OptMask        },
    { "query-flattener",  SQLITE_QueryFlattener },
    { "column-cache",     SQLITE_ColumnCache    },
    { "groupby-order",    SQLITE_GroupByOrder   },
    { "factor-constants", SQLITE_FactorOutConst },
    { "real-as-int",      SQLITE_IdxRealAsInt   },
    { "distinct-opt",     SQLITE_DistinctOpt    },
    { "cover-idx-scan",   SQLITE_CoverIdxScan   },
  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

Changes to src/test_malloc.c.

1192
1193
1194
1195
1196
1197
1198





























1199
1200
1201
1202
1203
1204
1205
....
1443
1444
1445
1446
1447
1448
1449

1450
1451
1452
1453
1454
1455
1456
  }

  rc = sqlite3_config(SQLITE_CONFIG_URI, bOpenUri);
  Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE);

  return TCL_OK;
}






























/*
** Usage:    sqlite3_dump_memsys3  FILENAME
**           sqlite3_dump_memsys5  FILENAME
**
** Write a summary of unfreed memsys3 allocations to FILENAME.
*/
................................................................................
     { "sqlite3_db_status",          test_db_status                ,0 },
     { "install_malloc_faultsim",    test_install_malloc_faultsim  ,0 },
     { "sqlite3_config_heap",        test_config_heap              ,0 },
     { "sqlite3_config_memstatus",   test_config_memstatus         ,0 },
     { "sqlite3_config_lookaside",   test_config_lookaside         ,0 },
     { "sqlite3_config_error",       test_config_error             ,0 },
     { "sqlite3_config_uri",         test_config_uri               ,0 },

     { "sqlite3_db_config_lookaside",test_db_config_lookaside      ,0 },
     { "sqlite3_dump_memsys3",       test_dump_memsys3             ,3 },
     { "sqlite3_dump_memsys5",       test_dump_memsys3             ,5 },
     { "sqlite3_install_memsys3",    test_install_memsys3          ,0 },
     { "sqlite3_memdebug_vfs_oom_test", test_vfs_oom_test          ,0 },
  };
  int i;







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







 







>







1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
....
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
  }

  rc = sqlite3_config(SQLITE_CONFIG_URI, bOpenUri);
  Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE);

  return TCL_OK;
}

/*
** Usage:    sqlite3_config_cis  BOOLEAN
**
** Enables or disables the use of the covering-index scan optimization.
** SQLITE_CONFIG_COVERING_INDEX_SCAN.
*/
static int test_config_cis(
  void * clientData, 
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int rc;
  int bUseCis;

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "BOOL");
    return TCL_ERROR;
  }
  if( Tcl_GetBooleanFromObj(interp, objv[1], &bUseCis) ){
    return TCL_ERROR;
  }

  rc = sqlite3_config(SQLITE_CONFIG_COVERING_INDEX_SCAN, bUseCis);
  Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE);

  return TCL_OK;
}

/*
** Usage:    sqlite3_dump_memsys3  FILENAME
**           sqlite3_dump_memsys5  FILENAME
**
** Write a summary of unfreed memsys3 allocations to FILENAME.
*/
................................................................................
     { "sqlite3_db_status",          test_db_status                ,0 },
     { "install_malloc_faultsim",    test_install_malloc_faultsim  ,0 },
     { "sqlite3_config_heap",        test_config_heap              ,0 },
     { "sqlite3_config_memstatus",   test_config_memstatus         ,0 },
     { "sqlite3_config_lookaside",   test_config_lookaside         ,0 },
     { "sqlite3_config_error",       test_config_error             ,0 },
     { "sqlite3_config_uri",         test_config_uri               ,0 },
     { "sqlite3_config_cis",         test_config_cis               ,0 },
     { "sqlite3_db_config_lookaside",test_db_config_lookaside      ,0 },
     { "sqlite3_dump_memsys3",       test_dump_memsys3             ,3 },
     { "sqlite3_dump_memsys5",       test_dump_memsys3             ,5 },
     { "sqlite3_install_memsys3",    test_install_memsys3          ,0 },
     { "sqlite3_memdebug_vfs_oom_test", test_vfs_oom_test          ,0 },
  };
  int i;

Changes to src/where.c.

260
261
262
263
264
265
266

267
268
269
270
271
272
273
....
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
....
3194
3195
3196
3197
3198
3199
3200













3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211

3212
3213
3214
3215
3216
3217
3218
....
4248
4249
4250
4251
4252
4253
4254





4255
4256
4257
4258
4259
4260
4261
....
5124
5125
5126
5127
5128
5129
5130

5131

5132
5133
5134
5135
5136
5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
5157
5158
#define WHERE_ORDERBY      0x01000000  /* Output will appear in correct order */
#define WHERE_REVERSE      0x02000000  /* Scan in reverse order */
#define WHERE_UNIQUE       0x04000000  /* Selects no more than one row */
#define WHERE_VIRTUALTABLE 0x08000000  /* Use virtual-table processing */
#define WHERE_MULTI_OR     0x10000000  /* OR using multiple indices */
#define WHERE_TEMP_INDEX   0x20000000  /* Uses an ephemeral index */
#define WHERE_DISTINCT     0x40000000  /* Correct order for DISTINCT */


/*
** Initialize a preallocated WhereClause structure.
*/
static void whereClauseInit(
  WhereClause *pWC,        /* The WhereClause to be initialized */
  Parse *pParse,           /* The parsing context */
................................................................................
    }

    /* If currently calculating the cost of using an index (not the IPK
    ** index), determine if all required column data may be obtained without 
    ** using the main table (i.e. if the index is a covering
    ** index for this query). If it is, set the WHERE_IDX_ONLY flag in
    ** wsFlags. Otherwise, set the bLookup variable to true.  */
    if( pIdx && wsFlags ){
      Bitmask m = pSrc->colUsed;
      int j;
      for(j=0; j<pIdx->nColumn; j++){
        int x = pIdx->aiColumn[j];
        if( x<BMS-1 ){
          m &= ~(((Bitmask)1)<<x);
        }
................................................................................
    ** on one page and hence more pages have to be fetched.
    **
    ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do
    ** not give us data on the relative sizes of table and index records.
    ** So this computation assumes table records are about twice as big
    ** as index records
    */













    if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ){
      /* The cost of a full table scan is a number of move operations equal
      ** to the number of rows in the table.
      **
      ** We add an additional 4x penalty to full table scans.  This causes
      ** the cost function to err on the side of choosing an index over
      ** choosing a full scan.  This 4x full-scan penalty is an arguable
      ** decision and one which we expect to revisit in the future.  But
      ** it seems to be working well enough at the moment.
      */
      cost = aiRowEst[0]*4;

    }else{
      log10N = estLog(aiRowEst[0]);
      cost = nRow;
      if( pIdx ){
        if( bLookup ){
          /* For an index lookup followed by a table lookup:
          **    nInMul index searches to find the start of each index range
................................................................................
      pLevel->op = OP_Noop;
    }else if( bRev ){
      pLevel->op = OP_Prev;
    }else{
      pLevel->op = OP_Next;
    }
    pLevel->p1 = iIdxCur;





  }else

#ifndef SQLITE_OMIT_OR_OPTIMIZATION
  if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){
    /* Case 4:  Two or more separately indexed terms connected by OR
    **
    ** Example:
................................................................................
  ** is not used, its name is just '{}'.  If no index is used
  ** the index is listed as "{}".  If the primary key is used the
  ** index name is '*'.
  */
  for(i=0; i<nTabList; i++){
    char *z;
    int n;

    pLevel = &pWInfo->a[i];

    pTabItem = &pTabList->a[pLevel->iFrom];
    z = pTabItem->zAlias;
    if( z==0 ) z = pTabItem->pTab->zName;
    n = sqlite3Strlen30(z);
    if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
      if( pLevel->plan.wsFlags & WHERE_IDX_ONLY ){
        memcpy(&sqlite3_query_plan[nQPlan], "{}", 2);
        nQPlan += 2;
      }else{
        memcpy(&sqlite3_query_plan[nQPlan], z, n);
        nQPlan += n;
      }
      sqlite3_query_plan[nQPlan++] = ' ';
    }
    testcase( pLevel->plan.wsFlags & WHERE_ROWID_EQ );
    testcase( pLevel->plan.wsFlags & WHERE_ROWID_RANGE );
    if( pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
      memcpy(&sqlite3_query_plan[nQPlan], "* ", 2);
      nQPlan += 2;
    }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
      n = sqlite3Strlen30(pLevel->plan.u.pIdx->zName);
      if( n+nQPlan < sizeof(sqlite3_query_plan)-2 ){
        memcpy(&sqlite3_query_plan[nQPlan], pLevel->plan.u.pIdx->zName, n);
        nQPlan += n;
        sqlite3_query_plan[nQPlan++] = ' ';
      }
    }else{







>







 







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
|










>







 







>
>
>
>
>







 







>

>





|








|
|
|


|







260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
....
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
....
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
....
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
....
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
5157
5158
5159
5160
5161
5162
5163
5164
5165
5166
5167
5168
5169
5170
5171
5172
5173
5174
5175
5176
5177
5178
5179
5180
#define WHERE_ORDERBY      0x01000000  /* Output will appear in correct order */
#define WHERE_REVERSE      0x02000000  /* Scan in reverse order */
#define WHERE_UNIQUE       0x04000000  /* Selects no more than one row */
#define WHERE_VIRTUALTABLE 0x08000000  /* Use virtual-table processing */
#define WHERE_MULTI_OR     0x10000000  /* OR using multiple indices */
#define WHERE_TEMP_INDEX   0x20000000  /* Uses an ephemeral index */
#define WHERE_DISTINCT     0x40000000  /* Correct order for DISTINCT */
#define WHERE_COVER_SCAN   0x80000000  /* Full scan of a covering index */

/*
** Initialize a preallocated WhereClause structure.
*/
static void whereClauseInit(
  WhereClause *pWC,        /* The WhereClause to be initialized */
  Parse *pParse,           /* The parsing context */
................................................................................
    }

    /* If currently calculating the cost of using an index (not the IPK
    ** index), determine if all required column data may be obtained without 
    ** using the main table (i.e. if the index is a covering
    ** index for this query). If it is, set the WHERE_IDX_ONLY flag in
    ** wsFlags. Otherwise, set the bLookup variable to true.  */
    if( pIdx ){
      Bitmask m = pSrc->colUsed;
      int j;
      for(j=0; j<pIdx->nColumn; j++){
        int x = pIdx->aiColumn[j];
        if( x<BMS-1 ){
          m &= ~(((Bitmask)1)<<x);
        }
................................................................................
    ** on one page and hence more pages have to be fetched.
    **
    ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do
    ** not give us data on the relative sizes of table and index records.
    ** So this computation assumes table records are about twice as big
    ** as index records
    */
    if( wsFlags==WHERE_IDX_ONLY
     && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
     && sqlite3GlobalConfig.bUseCis
#ifndef SQLITE_OMIT_BUILTIN_TEST
     && (pParse->db->flags & SQLITE_CoverIdxScan)==0
#endif
    ){
      /* This index is not useful for indexing, but it is a covering index.
      ** A full-scan of the index might be a little faster than a full-scan
      ** of the table, so give this case a cost slightly less than a table
      ** scan. */
      cost = aiRowEst[0]*3 + pProbe->nColumn;
      wsFlags |= WHERE_COVER_SCAN|WHERE_COLUMN_RANGE;
    }else if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ){
      /* The cost of a full table scan is a number of move operations equal
      ** to the number of rows in the table.
      **
      ** We add an additional 4x penalty to full table scans.  This causes
      ** the cost function to err on the side of choosing an index over
      ** choosing a full scan.  This 4x full-scan penalty is an arguable
      ** decision and one which we expect to revisit in the future.  But
      ** it seems to be working well enough at the moment.
      */
      cost = aiRowEst[0]*4;
      wsFlags &= ~WHERE_IDX_ONLY;
    }else{
      log10N = estLog(aiRowEst[0]);
      cost = nRow;
      if( pIdx ){
        if( bLookup ){
          /* For an index lookup followed by a table lookup:
          **    nInMul index searches to find the start of each index range
................................................................................
      pLevel->op = OP_Noop;
    }else if( bRev ){
      pLevel->op = OP_Prev;
    }else{
      pLevel->op = OP_Next;
    }
    pLevel->p1 = iIdxCur;
    if( pLevel->plan.wsFlags & WHERE_COVER_SCAN ){
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }else{
      assert( pLevel->p5==0 );
    }
  }else

#ifndef SQLITE_OMIT_OR_OPTIMIZATION
  if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){
    /* Case 4:  Two or more separately indexed terms connected by OR
    **
    ** Example:
................................................................................
  ** is not used, its name is just '{}'.  If no index is used
  ** the index is listed as "{}".  If the primary key is used the
  ** index name is '*'.
  */
  for(i=0; i<nTabList; i++){
    char *z;
    int n;
    int w;
    pLevel = &pWInfo->a[i];
    w = pLevel->plan.wsFlags;
    pTabItem = &pTabList->a[pLevel->iFrom];
    z = pTabItem->zAlias;
    if( z==0 ) z = pTabItem->pTab->zName;
    n = sqlite3Strlen30(z);
    if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
      if( (w & WHERE_IDX_ONLY)!=0 && (w & WHERE_COVER_SCAN)==0 ){
        memcpy(&sqlite3_query_plan[nQPlan], "{}", 2);
        nQPlan += 2;
      }else{
        memcpy(&sqlite3_query_plan[nQPlan], z, n);
        nQPlan += n;
      }
      sqlite3_query_plan[nQPlan++] = ' ';
    }
    testcase( w & WHERE_ROWID_EQ );
    testcase( w & WHERE_ROWID_RANGE );
    if( w & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
      memcpy(&sqlite3_query_plan[nQPlan], "* ", 2);
      nQPlan += 2;
    }else if( (w & WHERE_INDEXED)!=0 && (w & WHERE_COVER_SCAN)==0 ){
      n = sqlite3Strlen30(pLevel->plan.u.pIdx->zName);
      if( n+nQPlan < sizeof(sqlite3_query_plan)-2 ){
        memcpy(&sqlite3_query_plan[nQPlan], pLevel->plan.u.pIdx->zName, n);
        nQPlan += n;
        sqlite3_query_plan[nQPlan++] = ' ';
      }
    }else{

Changes to test/analyze6.test.

57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# The lowest cost plan is to scan CAT and for each integer there, do a single
# lookup of the first corresponding entry in EV then read off the equal values
# in EV.  (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
  eqp {SELECT count(*) FROM ev, cat WHERE x=y}
} {0 0 1 {SCAN TABLE cat (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}

# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_test analyze6-1.2 {
  eqp {SELECT count(*) FROM cat, ev WHERE x=y}
} {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}


# Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
# If ANALYZE is run on an empty table, make sure indices are used
# on the table.
#
do_test analyze6-2.1 {







|






|







57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# The lowest cost plan is to scan CAT and for each integer there, do a single
# lookup of the first corresponding entry in EV then read off the equal values
# in EV.  (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
  eqp {SELECT count(*) FROM ev, cat WHERE x=y}
} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}

# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_test analyze6-1.2 {
  eqp {SELECT count(*) FROM cat, ev WHERE x=y}
} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}


# Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
# If ANALYZE is run on an empty table, make sure indices are used
# on the table.
#
do_test analyze6-2.1 {

Changes to test/autovacuum.test.

110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
    # Ensure the data remaining in the table is what was expected.
    foreach d $delete {
      set idx [lsearch $::tbl_data [make_str $d $ENTRY_LEN]]
      set ::tbl_data [lreplace $::tbl_data $idx $idx]
    }
    do_test autovacuum-1.$tn.($delete).3 {
      execsql {
        select a from av1
      }
    } $::tbl_data
  }

  # All rows have been deleted. Ensure the file has shrunk to 4 pages.
  do_test autovacuum-1.$tn.3 {
    file_pages







|







110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
    # Ensure the data remaining in the table is what was expected.
    foreach d $delete {
      set idx [lsearch $::tbl_data [make_str $d $ENTRY_LEN]]
      set ::tbl_data [lreplace $::tbl_data $idx $idx]
    }
    do_test autovacuum-1.$tn.($delete).3 {
      execsql {
        select a from av1 order by rowid
      }
    } $::tbl_data
  }

  # All rows have been deleted. Ensure the file has shrunk to 4 pages.
  do_test autovacuum-1.$tn.3 {
    file_pages

Changes to test/collate4.test.

90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
...
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
...
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
do_test collate4-1.1.4 {
  cksort {SELECT b FROM collate4t1 ORDER BY b}
} {{} A B a b nosort}
do_test collate4-1.1.5 {
  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
} {{} A B a b nosort}
do_test collate4-1.1.6 {
  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
} {{} a A b B sort}

do_test collate4-1.1.7 {
  execsql {
    CREATE TABLE collate4t2(
      a PRIMARY KEY COLLATE NOCASE, 
      b UNIQUE COLLATE TEXT
................................................................................
    INSERT INTO collate4t4 VALUES( 'B', 'B' );
    INSERT INTO collate4t4 VALUES( 'A', 'A' );
    CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
    CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
  }
} {}
do_test collate4-1.1.22 {
  cksort {SELECT a FROM collate4t4 ORDER BY a}
} {{} a A b B sort}
do_test collate4-1.1.23 {
  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
} {{} a A b B sort}
do_test collate4-1.1.24 {
  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
} {{} A B a b nosort}
do_test collate4-1.1.25 {
  cksort {SELECT b FROM collate4t4 ORDER BY b}
} {{} A B a b sort}
do_test collate4-1.1.26 {
  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
} {{} A B a b sort}
................................................................................
do_test collate4-1.2.3 {
  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
} {{} A B a b sort}
do_test collate4-1.2.4 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b}
} {{} A a B b nosort}
do_test collate4-1.2.5 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
} {{} a A b B sort}
do_test collate4-1.2.6 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
} {{} A a B b nosort}

do_test collate4-1.2.7 {
  execsql {
................................................................................
    INSERT INTO collate4t3 VALUES( NULL, NULL );
    INSERT INTO collate4t3 VALUES( 'B', 'B' );
    INSERT INTO collate4t3 VALUES( 'A', 'A' );
    CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
  }
} {}
do_test collate4-1.2.15 {
  cksort {SELECT a FROM collate4t3 ORDER BY a}
} {{} a A b B sort}
do_test collate4-1.2.16 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
} {{} a A b B sort}
do_test collate4-1.2.17 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
} {{} A B a b nosort}
do_test collate4-1.2.18 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
} {{} A B a b sort}
................................................................................
} {A A 19}
do_test collate4-2.1.4 {
  execsql {
    DROP INDEX collate4i1;
    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
  }
  count {
    SELECT * FROM collate4t2, collate4t1 WHERE a = b;

  }
} {A a A A 19}
do_test collate4-2.1.5 {
  count {
    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
  }
} {A A 4}
ifcapable subquery {
  do_test collate4-2.1.6 {
    count {
      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);

    }
  } {a A 10}
  do_test collate4-2.1.7 {
    execsql {
      DROP INDEX collate4i1;
      CREATE INDEX collate4i1 ON collate4t1(a);
    }
    count {
      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);

    }
  } {a A 6}
  do_test collate4-2.1.8 {
    count {
      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
    }
  } {a A 5}
  do_test collate4-2.1.9 {
    execsql {
      DROP INDEX collate4i1;
      CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
    }
    count {
      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
    }
  } {a A 9}
}
do_test collate4-2.1.10 {
  execsql {
    DROP TABLE collate4t1;
    DROP TABLE collate4t2;







|







 







|


|


|







 







|







 







|


|







 







|
>










|
>








|
>













|







90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
...
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
...
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
do_test collate4-1.1.4 {
  cksort {SELECT b FROM collate4t1 ORDER BY b}
} {{} A B a b nosort}
do_test collate4-1.1.5 {
  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
} {{} A B a b nosort}
do_test collate4-1.1.6 {
  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE, rowid}
} {{} a A b B sort}

do_test collate4-1.1.7 {
  execsql {
    CREATE TABLE collate4t2(
      a PRIMARY KEY COLLATE NOCASE, 
      b UNIQUE COLLATE TEXT
................................................................................
    INSERT INTO collate4t4 VALUES( 'B', 'B' );
    INSERT INTO collate4t4 VALUES( 'A', 'A' );
    CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
    CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
  }
} {}
do_test collate4-1.1.22 {
  cksort {SELECT a FROM collate4t4 ORDER BY a, rowid}
} {{} a A b B sort}
do_test collate4-1.1.23 {
  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE, rowid}
} {{} a A b B sort}
do_test collate4-1.1.24 {
  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT, rowid}
} {{} A B a b nosort}
do_test collate4-1.1.25 {
  cksort {SELECT b FROM collate4t4 ORDER BY b}
} {{} A B a b sort}
do_test collate4-1.1.26 {
  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
} {{} A B a b sort}
................................................................................
do_test collate4-1.2.3 {
  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
} {{} A B a b sort}
do_test collate4-1.2.4 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b}
} {{} A a B b nosort}
do_test collate4-1.2.5 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase, rowid}
} {{} a A b B sort}
do_test collate4-1.2.6 {
  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
} {{} A a B b nosort}

do_test collate4-1.2.7 {
  execsql {
................................................................................
    INSERT INTO collate4t3 VALUES( NULL, NULL );
    INSERT INTO collate4t3 VALUES( 'B', 'B' );
    INSERT INTO collate4t3 VALUES( 'A', 'A' );
    CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
  }
} {}
do_test collate4-1.2.15 {
  cksort {SELECT a FROM collate4t3 ORDER BY a, rowid}
} {{} a A b B sort}
do_test collate4-1.2.16 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase, rowid}
} {{} a A b B sort}
do_test collate4-1.2.17 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
} {{} A B a b nosort}
do_test collate4-1.2.18 {
  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
} {{} A B a b sort}
................................................................................
} {A A 19}
do_test collate4-2.1.4 {
  execsql {
    DROP INDEX collate4i1;
    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
  }
  count {
    SELECT * FROM collate4t2, collate4t1 WHERE a = b
     ORDER BY collate4t2.rowid, collate4t1.rowid
  }
} {A a A A 19}
do_test collate4-2.1.5 {
  count {
    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
  }
} {A A 4}
ifcapable subquery {
  do_test collate4-2.1.6 {
    count {
      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
       ORDER BY rowid
    }
  } {a A 10}
  do_test collate4-2.1.7 {
    execsql {
      DROP INDEX collate4i1;
      CREATE INDEX collate4i1 ON collate4t1(a);
    }
    count {
      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
       ORDER BY rowid
    }
  } {a A 6}
  do_test collate4-2.1.8 {
    count {
      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
    }
  } {a A 5}
  do_test collate4-2.1.9 {
    execsql {
      DROP INDEX collate4i1;
      CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
    }
    count {
      SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid;
    }
  } {a A 9}
}
do_test collate4-2.1.10 {
  execsql {
    DROP TABLE collate4t1;
    DROP TABLE collate4t2;

Changes to test/corruptD.test.

103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
#-------------------------------------------------------------------------
# The following tests, corruptD-1.1.*, focus on the page header field
# containing the offset of the first free block in a page. 
#
do_test corruptD-1.1.1 {
  incr_change_counter
  hexio_write test.db [expr 1024+1] FFFF
  catchsql { SELECT * FROM t1 }
} {1 {database disk image is malformed}}
do_test corruptD-1.1.2 {
  incr_change_counter
  hexio_write test.db [expr 1024+1] [hexio_render_int32 1021]
  catchsql { SELECT * FROM t1 }
} {1 {database disk image is malformed}}

#-------------------------------------------------------------------------
# The following tests, corruptD-1.2.*, focus on the offsets contained
# in the first 2 byte of each free-block on the free-list.
#
do_test corruptD-1.2.1 {







|




|







103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
#-------------------------------------------------------------------------
# The following tests, corruptD-1.1.*, focus on the page header field
# containing the offset of the first free block in a page. 
#
do_test corruptD-1.1.1 {
  incr_change_counter
  hexio_write test.db [expr 1024+1] FFFF
  catchsql { SELECT * FROM t1 ORDER BY rowid }
} {1 {database disk image is malformed}}
do_test corruptD-1.1.2 {
  incr_change_counter
  hexio_write test.db [expr 1024+1] [hexio_render_int32 1021]
  catchsql { SELECT * FROM t1 ORDER BY rowid }
} {1 {database disk image is malformed}}

#-------------------------------------------------------------------------
# The following tests, corruptD-1.2.*, focus on the offsets contained
# in the first 2 byte of each free-block on the free-list.
#
do_test corruptD-1.2.1 {

Changes to test/corruptE.test.

45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
    INSERT OR IGNORE INTO t1 SELECT x*5,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*7,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*11,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*13,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*17,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*19,y FROM t1;
    CREATE INDEX t1i1 ON t1(x);
    CREATE TABLE t2 AS SELECT x,2 as y FROM t1 WHERE rowid%5!=0;
    COMMIT;
  }
} {}

ifcapable {integrityck} {
  integrity_check corruptE-1.2
}







|







45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
    INSERT OR IGNORE INTO t1 SELECT x*5,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*7,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*11,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*13,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*17,y FROM t1;
    INSERT OR IGNORE INTO t1 SELECT x*19,y FROM t1;
    CREATE INDEX t1i1 ON t1(x);
    CREATE TABLE t2 AS SELECT x,2 as y FROM t1 WHERE rowid%5!=0 ORDER BY rowid;
    COMMIT;
  }
} {}

ifcapable {integrityck} {
  integrity_check corruptE-1.2
}

Added test/coveridxscan.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
# 2012 September 17
#
# 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.
#
#***********************************************************************
#
# Tests for the optimization which attempts to use a covering index
# for a full-table scan (under the theory that the index will be smaller
# and require less I/O and hence will run faster.)
#

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

set testprefix coveridxscan

do_test 1.1 {
  db eval {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1);
    CREATE INDEX t1ab ON t1(a,b);
    CREATE INDEX t1b ON t1(b);
    SELECT a FROM t1;
  }
  # covering index used for the scan, hence values are increasing
} {3 4 5}

do_test 1.2 {
  db eval {
    SELECT a, c FROM t1;
  }
  # There is no covering index, hence the values are in rowid order
} {5 3 4 2 3 1}

do_test 1.3 {
  db eval {
    SELECT b FROM t1;
  }
  # Choice of two indices: use the one with fewest columns
} {2 4 8}

do_test 2.1 {
  optimization_control db cover-idx-scan 0
  db eval {SELECT a FROM t1}
  # With the optimization turned off, output in rowid order
} {5 4 3}
do_test 2.2 {
  db eval {SELECT a, c FROM t1}
} {5 3 4 2 3 1}
do_test 2.3 {
  db eval {SELECT b FROM t1}
} {4 8 2}

db close
sqlite3_shutdown
sqlite3_config_cis 0
sqlite3 db test.db

do_test 3.1 {
  db eval {SELECT a FROM t1}
  # With the optimization configured off, output in rowid order
} {5 4 3}
do_test 3.2 {
  db eval {SELECT a, c FROM t1}
} {5 3 4 2 3 1}
do_test 3.3 {
  db eval {SELECT b FROM t1}
} {4 8 2}

db close
sqlite3_shutdown
sqlite3_config_cis 1
sqlite3 db test.db

# The CIS optimization is enabled again.  Covering indices are once again
# used for all table scans.
do_test 4.1 {
  db eval {SELECT a FROM t1}
} {3 4 5}
do_test 4.2 {
  db eval {SELECT a, c FROM t1}
} {5 3 4 2 3 1}
do_test 4.3 {
  db eval {SELECT b FROM t1}
} {2 4 8}


finish_test

Changes to test/distinct.test.

171
172
173
174
175
176
177
178
179
180
181
182
183
184
  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {B}
} {
  do_execsql_test    2.$tn.1 "SELECT DISTINCT $sql" $res
  do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}

do_execsql_test 2.A {
  SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o;
} {a A a A}




finish_test







|






171
172
173
174
175
176
177
178
179
180
181
182
183
184
  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {B}
} {
  do_execsql_test    2.$tn.1 "SELECT DISTINCT $sql" $res
  do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}

do_execsql_test 2.A {
  SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
} {a A a A}




finish_test

Changes to test/e_createtable.test.

1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
  do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.17.$tn.2 " 
    INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
  " $res

  do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
  do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
}
catchsql COMMIT

# EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
# include a conflict-clause or it is a CHECK constraint, the default
# conflict resolution algorithm is ABORT.
#







|







1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
  do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.17.$tn.2 " 
    INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
  " $res

  do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
  do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data
}
catchsql COMMIT

# EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
# include a conflict-clause or it is a CHECK constraint, the default
# conflict resolution algorithm is ABORT.
#

Changes to test/e_fkey.test.

2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
....
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
    INSERT INTO cA VALUES(X'ABCD');
    INSERT INTO cB VALUES(X'1234');
  }
} {}
do_test e_fkey-45.2 {
  execsql {
    DELETE FROM pA WHERE rowid = 3;
    SELECT quote(x) FROM pA;
  }
} {X'0000' X'9999' X'1234'}
do_test e_fkey-45.3 {
  execsql { SELECT quote(c) FROM cA }
} {X'0000'}
do_test e_fkey-45.4 {
  execsql {
    UPDATE pA SET x = X'8765' WHERE rowid = 4;
    SELECT quote(x) FROM pA;
  }
} {X'0000' X'9999' X'8765'}
do_test e_fkey-45.5 {
  execsql { SELECT quote(c) FROM cB }
} {X'9999'}

#-------------------------------------------------------------------------
................................................................................
    INSERT INTO parent VALUES(1);
    INSERT INTO child VALUES(1);
  }
} {}
do_test e_fkey-51.2 {
  execsql {
    UPDATE parent SET x = 22;
    SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child;
  }
} {22 21 23 xxx 22}
do_test e_fkey-51.3 {
  execsql {
    DELETE FROM child;
    DELETE FROM parent;
    INSERT INTO parent VALUES(-1);
    INSERT INTO child VALUES(-1);
    UPDATE parent SET x = 22;
    SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child;
  }
} {22 23 21 xxx 23}


#-------------------------------------------------------------------------
# Verify that ON UPDATE actions only actually take place if the parent key
# is set to a new value that is distinct from the old value. The default







|








|







 







|









|







2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
....
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
    INSERT INTO cA VALUES(X'ABCD');
    INSERT INTO cB VALUES(X'1234');
  }
} {}
do_test e_fkey-45.2 {
  execsql {
    DELETE FROM pA WHERE rowid = 3;
    SELECT quote(x) FROM pA ORDER BY rowid;
  }
} {X'0000' X'9999' X'1234'}
do_test e_fkey-45.3 {
  execsql { SELECT quote(c) FROM cA }
} {X'0000'}
do_test e_fkey-45.4 {
  execsql {
    UPDATE pA SET x = X'8765' WHERE rowid = 4;
    SELECT quote(x) FROM pA ORDER BY rowid;
  }
} {X'0000' X'9999' X'8765'}
do_test e_fkey-45.5 {
  execsql { SELECT quote(c) FROM cB }
} {X'9999'}

#-------------------------------------------------------------------------
................................................................................
    INSERT INTO parent VALUES(1);
    INSERT INTO child VALUES(1);
  }
} {}
do_test e_fkey-51.2 {
  execsql {
    UPDATE parent SET x = 22;
    SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
  }
} {22 21 23 xxx 22}
do_test e_fkey-51.3 {
  execsql {
    DELETE FROM child;
    DELETE FROM parent;
    INSERT INTO parent VALUES(-1);
    INSERT INTO child VALUES(-1);
    UPDATE parent SET x = 22;
    SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
  }
} {22 23 21 xxx 23}


#-------------------------------------------------------------------------
# Verify that ON UPDATE actions only actually take place if the parent key
# is set to a new value that is distinct from the old value. The default

Changes to test/e_select.test.

1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
  1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    4,5 f   1 o   7,6   s 3,2 t
  }
  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,4,3,2 10    5,7,6 18
  }
  3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    4,3,5,7,6    1,2
  }







|







1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
  1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    4,5 f   1 o   7,6   s 3,2 t
  }
  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,2,3,4 10    5,6,7 18
  }
  3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    4,3,5,7,6    1,2
  }

Changes to test/eqp.test.

58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
...
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
...
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
...
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
...
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
  SELECT a FROM t1 ORDER BY a
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
}
do_eqp_test 1.4 {
  SELECT a FROM t1 ORDER BY +a
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 1.5 {
  SELECT a FROM t1 WHERE a=4
} {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
}
................................................................................
det 2.3.1 "SELECT max(x) FROM t2" {
  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.2 "SELECT min(x) FROM t2" {
  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.3 "SELECT min(x), max(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
}

det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}


................................................................................
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
}

do_eqp_test 4.3.1 {
  SELECT x FROM t1 UNION SELECT x FROM t2
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 
}

do_eqp_test 4.3.2 {
  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
} {
  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  3 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.3 {
  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
} {
................................................................................
# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
# INDEX i3 (b=?) (~10 rows)
det 5.9 {
  SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
} {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
}

# EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
................................................................................
}

# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
  0 1 1 {SCAN TABLE t1 (~1000000 rows)}
}

# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# USING TEMP B-TREE (UNION)
det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)}
  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}

# EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows)
# 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2







|







 







|







 







|







|







 







|







 







|







|
|







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
...
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
...
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
...
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
...
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
  SELECT a FROM t1 ORDER BY a
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
}
do_eqp_test 1.4 {
  SELECT a FROM t1 ORDER BY +a
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 1.5 {
  SELECT a FROM t1 WHERE a=4
} {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
}
................................................................................
det 2.3.1 "SELECT max(x) FROM t2" {
  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.2 "SELECT min(x) FROM t2" {
  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.3 "SELECT min(x), max(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
}

det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}


................................................................................
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
}

do_eqp_test 4.3.1 {
  SELECT x FROM t1 UNION SELECT x FROM t2
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 
}

do_eqp_test 4.3.2 {
  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
} {
  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.3 {
  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
} {
................................................................................
# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
# INDEX i3 (b=?) (~10 rows)
det 5.9 {
  SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
} {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
}

# EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
................................................................................
}

# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
  0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
}

# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# USING TEMP B-TREE (UNION)
det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
  2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}

# EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows)
# 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2

Changes to test/incrblob.test.

433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
    execsql {
      BEGIN;
      INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection');
    } db2
  } {}
  do_test incrblob-6.2 {
    execsql {
      SELECT rowid FROM blobs
    }
  } {1 2 3}
  do_test incrblob-6.3 {
    set rc [catch {
      db incrblob blobs v 1
    } msg]
    list $rc $msg







|







433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
    execsql {
      BEGIN;
      INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection');
    } db2
  } {}
  do_test incrblob-6.2 {
    execsql {
      SELECT rowid FROM blobs ORDER BY rowid
    }
  } {1 2 3}
  do_test incrblob-6.3 {
    set rc [catch {
      db incrblob blobs v 1
    } msg]
    list $rc $msg

Changes to test/intpkey.test.

372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
  }
  count {
    SELECT * FROM t1 WHERE a=0;
  }
} {0 zero entry 0}
do_test intpkey-5.2 {
  execsql {
    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#
# COPY command has been removed.  But we retain these tests so







|







372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
  }
  count {
    SELECT * FROM t1 WHERE a=0;
  }
} {0 zero entry 0}
do_test intpkey-5.2 {
  execsql {
    SELECT rowid, a FROM t1 ORDER BY rowid
  }
} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#
# COPY command has been removed.  But we retain these tests so

Changes to test/like.test.

402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
...
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
} {ABC {ABC abc xyz} abc abcd nosort {} i1}
do_test like-5.2 {
  set sqlite_like_count
} 12
do_test like-5.3 {
  execsql {
    CREATE TABLE t2(x TEXT COLLATE NOCASE);
    INSERT INTO t2 SELECT * FROM t1;
    CREATE INDEX i2 ON t2(x COLLATE NOCASE);
  }
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
  }
} {abc ABC {ABC abc xyz} abcd nosort {} i2}
................................................................................
    }]
  } {0 {x hello}}
  ifcapable explain {
    do_test like-9.4.3 {
      set res [sqlite3_exec_hex db {
         EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25'
      }]
      regexp {INDEX i2} $res
    } {0}
  }
  do_test like-9.5.1 {
    set res [sqlite3_exec_hex db {
       SELECT x FROM t2 WHERE x LIKE '%fe%25'
    }]
  } {0 {}}
  ifcapable explain {







|







 







|
|







402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
...
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
} {ABC {ABC abc xyz} abc abcd nosort {} i1}
do_test like-5.2 {
  set sqlite_like_count
} 12
do_test like-5.3 {
  execsql {
    CREATE TABLE t2(x TEXT COLLATE NOCASE);
    INSERT INTO t2 SELECT * FROM t1 ORDER BY rowid;
    CREATE INDEX i2 ON t2(x COLLATE NOCASE);
  }
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
  }
} {abc ABC {ABC abc xyz} abcd nosort {} i2}
................................................................................
    }]
  } {0 {x hello}}
  ifcapable explain {
    do_test like-9.4.3 {
      set res [sqlite3_exec_hex db {
         EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25'
      }]
      regexp {SCAN TABLE t2} $res
    } {1}
  }
  do_test like-9.5.1 {
    set res [sqlite3_exec_hex db {
       SELECT x FROM t2 WHERE x LIKE '%fe%25'
    }]
  } {0 {}}
  ifcapable explain {

Changes to test/stat.test.

72
73
74
75
76
77
78
79

80

81

82

83

84
85
86
87
88
89
90
    DROP TABLE t1;
  }
} {}

do_execsql_test stat-2.1 {
  CREATE TABLE t3(a PRIMARY KEY, b);
  INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;

  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;

  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;

  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;

  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;

  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name != 'sqlite_master';
} [list \
  sqlite_autoindex_t3_1 / 3 internal 3 368 623 125       \
  sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123        \
  sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131         \
  sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132      \







|
>
|
>
|
>
|
>
|
>







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
    DROP TABLE t1;
  }
} {}

do_execsql_test stat-2.1 {
  CREATE TABLE t3(a PRIMARY KEY, b);
  INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
   ORDER BY rowid;
  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name != 'sqlite_master';
} [list \
  sqlite_autoindex_t3_1 / 3 internal 3 368 623 125       \
  sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123        \
  sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131         \
  sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132      \

Changes to test/tkt-385a5b56b9.test.

35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
}

do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)}
}

do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
}

do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } {
  0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)}
}

do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } {
  0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)}
}

finish_test








|











<
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

}

do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)}
}

do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y (~1000000 rows)}
}

do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } {
  0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)}
}

do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } {
  0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)}
}

finish_test

Changes to test/tkt-78e04e52ea.test.

40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
  }
} {0 0 0 {SCAN TABLE  (~500000 rows)}}
do_test tkt-78e04-1.5 {
  execsql {
    DROP TABLE "";
    SELECT name FROM sqlite_master;
  }
} {t2}








|







40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
  }
} {0 0 0 {SCAN TABLE  USING COVERING INDEX i1 (~500000 rows)}}
do_test tkt-78e04-1.5 {
  execsql {
    DROP TABLE "";
    SELECT name FROM sqlite_master;
  }
} {t2}

Changes to test/triggerC.test.

218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
...
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
...
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
...
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
} {
  do_test triggerC-2.1.$n {
    catchsql { DROP TRIGGER t2_trig }
    execsql  { DELETE FROM t2 }
    execsql  $tdefn
    catchsql {
      INSERT INTO t2 VALUES(10);
      SELECT * FROM t2;
    }
  } $rc
}

do_test triggerC-2.2 {
  execsql "
    CREATE TABLE t22(x);
................................................................................
     2 integer 9.1 text 9.1 real    9.1 real
  }
} {
  do_test triggerC-4.1.$n {
    eval concat [execsql " 
      DELETE FROM log;
      $insert ; 
      SELECT * FROM log;
    "]
  } [join $log " "]
} 

#-------------------------------------------------------------------------
# This block of tests, triggerC-5.*, test that DELETE triggers are fired
# if a row is deleted as a result of OR REPLACE conflict resolution.
................................................................................
  6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {2 b 3 3 c 2} {1 a 2 c}
  7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 3 2 b 2} {1 b}
} {
  do_test triggerC-5.1.$n {
    execsql "
      BEGIN;
        $dml ;
        SELECT * FROM t5g;
        SELECT * FROM t5;
      ROLLBACK;
    "
  } [concat $t5g $t5]
}
do_test triggerC-5.2.0 {
  execsql {
    DROP TRIGGER t5t;
................................................................................
  6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {2 b 2 3 c 1} {1 a 2 c}
  7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 2 2 b 1} {1 b}
} {
  do_test triggerC-5.2.$n {
    execsql "
      BEGIN;
        $dml ;
        SELECT * FROM t5g;
        SELECT * FROM t5;
      ROLLBACK;
    "
  } [concat $t5g $t5]
}
do_test triggerC-5.3.0 {
  execsql { PRAGMA recursive_triggers = off }
} {}
................................................................................
  6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {} {1 a 2 c}
  7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {} {1 b}
} {
  do_test triggerC-5.3.$n {
    execsql "
      BEGIN;
        $dml ;
        SELECT * FROM t5g;
        SELECT * FROM t5;
      ROLLBACK;
    "
  } [concat $t5g $t5]
}
do_test triggerC-5.3.8 {
  execsql { PRAGMA recursive_triggers = on }
} {}







|







 







|







 







|
|







 







|
|







 







|
|







218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
...
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
...
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
...
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
} {
  do_test triggerC-2.1.$n {
    catchsql { DROP TRIGGER t2_trig }
    execsql  { DELETE FROM t2 }
    execsql  $tdefn
    catchsql {
      INSERT INTO t2 VALUES(10);
      SELECT * FROM t2 ORDER BY rowid;
    }
  } $rc
}

do_test triggerC-2.2 {
  execsql "
    CREATE TABLE t22(x);
................................................................................
     2 integer 9.1 text 9.1 real    9.1 real
  }
} {
  do_test triggerC-4.1.$n {
    eval concat [execsql " 
      DELETE FROM log;
      $insert ; 
      SELECT * FROM log ORDER BY rowid;
    "]
  } [join $log " "]
} 

#-------------------------------------------------------------------------
# This block of tests, triggerC-5.*, test that DELETE triggers are fired
# if a row is deleted as a result of OR REPLACE conflict resolution.
................................................................................
  6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {2 b 3 3 c 2} {1 a 2 c}
  7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 3 2 b 2} {1 b}
} {
  do_test triggerC-5.1.$n {
    execsql "
      BEGIN;
        $dml ;
        SELECT * FROM t5g ORDER BY rowid;
        SELECT * FROM t5 ORDER BY rowid;
      ROLLBACK;
    "
  } [concat $t5g $t5]
}
do_test triggerC-5.2.0 {
  execsql {
    DROP TRIGGER t5t;
................................................................................
  6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {2 b 2 3 c 1} {1 a 2 c}
  7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 2 2 b 1} {1 b}
} {
  do_test triggerC-5.2.$n {
    execsql "
      BEGIN;
        $dml ;
        SELECT * FROM t5g ORDER BY rowid;
        SELECT * FROM t5 ORDER BY rowid;
      ROLLBACK;
    "
  } [concat $t5g $t5]
}
do_test triggerC-5.3.0 {
  execsql { PRAGMA recursive_triggers = off }
} {}
................................................................................
  6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {} {1 a 2 c}
  7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {} {1 b}
} {
  do_test triggerC-5.3.$n {
    execsql "
      BEGIN;
        $dml ;
        SELECT * FROM t5g ORDER BY rowid;
        SELECT * FROM t5 ORDER BY rowid;
      ROLLBACK;
    "
  } [concat $t5g $t5]
}
do_test triggerC-5.3.8 {
  execsql { PRAGMA recursive_triggers = on }
} {}

Changes to test/unordered.test.

47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
        {0 0 0 {SCAN TABLE t1 (~42 rows)}}
    3   "SELECT * FROM t1 WHERE a = ? ORDER BY rowid"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)} 
         0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    4   "SELECT max(a) FROM t1"
        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 (~1 rows)}}
    5   "SELECT group_concat(b) FROM t1 GROUP BY a"
        {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
        {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}

    6   "SELECT * FROM t1 WHERE a = ?"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}







|







47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
        {0 0 0 {SCAN TABLE t1 (~42 rows)}}
    3   "SELECT * FROM t1 WHERE a = ? ORDER BY rowid"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)} 
         0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    4   "SELECT max(a) FROM t1"
        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
    5   "SELECT group_concat(b) FROM t1 GROUP BY a"
        {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
        {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}

    6   "SELECT * FROM t1 WHERE a = ?"
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}

Changes to test/where.test.

1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
....
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
  } 
} {1/1 1/4 4/1 4/4 sort}
do_test where-14.3 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
  } 
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.4 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
  } 
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.5 {
  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
  } 
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.6 {
  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
  } 
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
  } 
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.1 {
  cksort {
................................................................................
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
  } 
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.2 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
  } 
} {4/1 4/4 1/1 1/4 nosort}
do_test where-14.8 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
  } 
} {4/4 4/1 1/4 1/1 sort}
do_test where-14.9 {
  cksort {







|




|





|





|







 







|







1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
....
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
  } 
} {1/1 1/4 4/1 4/4 sort}
do_test where-14.3 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
  } 
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.4 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
  } 
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.5 {
  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
  } 
} {4/4 4/1 1/4 1/1 sort}
do_test where-14.6 {
  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
  } 
} {4/4 4/1 1/4 1/1 sort}
do_test where-14.7 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
  } 
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.1 {
  cksort {
................................................................................
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
  } 
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.2 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
  } 
} {4/4 4/1 1/4 1/1 nosort}
do_test where-14.8 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
  } 
} {4/4 4/1 1/4 1/1 sort}
do_test where-14.9 {
  cksort {

Changes to test/where9.test.

688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
        OR f='fghijklmn'
        OR g='hgfedcb'
  }
} {scan 0 sort 0}   ;#  Add 100 to rowids 5 31 57 82 83 84 85 86 87
do_test where9-6.5.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87);
    ROLLBACK;
  }
} {99 105 131 157 182 183 184 185 186 187}

do_test where9-6.6.1 {
  count_steps {
    BEGIN;







|







688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
        OR f='fghijklmn'
        OR g='hgfedcb'
  }
} {scan 0 sort 0}   ;#  Add 100 to rowids 5 31 57 82 83 84 85 86 87
do_test where9-6.5.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid;
    ROLLBACK;
  }
} {99 105 131 157 182 183 184 185 186 187}

do_test where9-6.6.1 {
  count_steps {
    BEGIN;