/ Check-in [cfaa7bc1]
Login

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

Overview
Comment:Attempt to use a covering index even on a full table scan, under the theory that the index will be smaller and require less disk I/O and thus be faster.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fullscan-covering-index
Files: files | file ages | folders
SHA1:cfaa7bc12847a7006ccc93815f2395ad5259744a
User & Date: drh 2012-09-15 18:45:54
Context
2012-09-17
19:26
Merge recent trunk changes into the fullscan-covering-index branch. check-in: 1c0bf030 user: drh tags: fullscan-covering-index
2012-09-15
18:45
Attempt to use a covering index even on a full table scan, under the theory that the index will be smaller and require less disk I/O and thus be faster. check-in: cfaa7bc1 user: drh tags: fullscan-covering-index
13:39
Make sure the name of rollback journal files are double-zero terminated when they are passed into the VFS. check-in: 8711a844 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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
....
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
................................................................................
      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
....
4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
....
5139
5140
5141
5142
5143
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
#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
    ){
      /* 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;
      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
................................................................................
      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
}

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 i2 (~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;