/ Check-in [eb434228]
Login

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

Overview
Comment:Add a column to the sqlite_stat2 table that contains the number of entries with exactly the same key as the sample. We do not yet do anything with this extra value. Some tests in analyze2.test are failing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | query-planner-tweaks
Files: files | file ages | folders
SHA1: eb434228277c4bbbb1ad153ed3e6e3eeffe799a4
User & Date: drh 2011-08-05 21:13:42
Original Comment: Add a column to the sqlite_stat2 table that contains the number of entries with exactly the same key as the sample. We do not yet do anything with this extra value. Some tests in analyze2.test are failing.
Context
2011-08-05
22:31
Bug fixes to the sample-count logic for STAT2. A few test cases added. check-in: e93c248c user: drh tags: query-planner-tweaks
21:13
Add a column to the sqlite_stat2 table that contains the number of entries with exactly the same key as the sample. We do not yet do anything with this extra value. Some tests in analyze2.test are failing. check-in: eb434228 user: drh tags: query-planner-tweaks
2011-08-03
22:06
Merge the winopen-retry-logic branch into trunk. The biggest change here is to test scripts, which should now use such as copy_file and delete_file from tester.tcl rather than the raw file commands of TCL. check-in: b90c28be user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
..
90
91
92
93
94
95
96

97
98
99
100
101
102
103
...
115
116
117
118
119
120
121
122













123
124
125
126

127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
...
161
162
163
164
165
166
167


168
169

170
171
172
173
174


175
176
177
178
179
180
181
...
183
184
185
186
187
188
189
190
191
192
193
194
195









196
197
198
199

200
201



202
203
204
205





206
207
208
209


210
211
212
213
214
215
216
...
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259

260
261
262

263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284





285
286
287
288
289
290
291
292
293
294
295
296
297




298
299
300
301
302

303
304
305
306
307



308
309
310
311
312
313
314
){
  static const struct {
    const char *zName;
    const char *zCols;
  } aTable[] = {
    { "sqlite_stat1", "tbl,idx,stat" },
#ifdef SQLITE_ENABLE_STAT2
    { "sqlite_stat2", "tbl,idx,sampleno,sample" },
#endif
  };

  int aRoot[] = {0, 0};
  u8 aCreateTbl[] = {0, 0};

  int i;
................................................................................
  }

  /* Open the sqlite_stat[12] tables for writing. */
  for(i=0; i<ArraySize(aTable); i++){
    sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
    sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
    sqlite3VdbeChangeP5(v, aCreateTbl[i]);

  }
}

/*
** Generate code to do an analysis of all indices associated with
** a single table.
*/
................................................................................
  int i;                       /* Loop counter */
  int topOfLoop;               /* The top of the loop */
  int endOfLoop;               /* The end of the loop */
  int jZeroRows = -1;          /* Jump from here if number of rows is zero */
  int iDb;                     /* Index of database containing pTab */
  int regTabname = iMem++;     /* Register containing table name */
  int regIdxname = iMem++;     /* Register containing index name */
  int regSampleno = iMem++;    /* Register containing next sample number */













  int regCol = iMem++;         /* Content of a column analyzed table */
  int regRec = iMem++;         /* Register holding completed record */
  int regTemp = iMem++;        /* Temporary use register */
  int regRowid = iMem++;       /* Rowid for the inserted record */


#ifdef SQLITE_ENABLE_STAT2
  int addr = 0;                /* Instruction address */
  int regTemp2 = iMem++;       /* Temporary use register */
  int regSamplerecno = iMem++; /* Index of next sample to record */
  int regRecno = iMem++;       /* Current sample index */
  int regLast = iMem++;        /* Index of last sample to record */
  int regFirst = iMem++;       /* Index of first sample to record */
#endif

  v = sqlite3GetVdbe(pParse);
  if( v==0 || NEVER(pTab==0) ){
    return;
  }
  if( pTab->tnum==0 ){
    /* Do not gather statistics on views or virtual tables */
................................................................................
  sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

  iIdxCur = pParse->nTab++;
  sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0);
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int nCol;
    KeyInfo *pKey;



    if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;

    nCol = pIdx->nColumn;
    pKey = sqlite3IndexKeyinfo(pParse, pIdx);
    if( iMem+1+(nCol*2)>pParse->nMem ){
      pParse->nMem = iMem+1+(nCol*2);
    }



    /* Open a cursor to the index to be analyzed. */
    assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) );
    sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb,
        (char *)pKey, P4_KEYINFO_HANDOFF);
    VdbeComment((v, "%s", pIdx->zName));

................................................................................
    sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0);

#ifdef SQLITE_ENABLE_STAT2

    /* If this iteration of the loop is generating code to analyze the
    ** first index in the pTab->pIndex list, then register regLast has
    ** not been populated. In this case populate it now.  */
    if( pTab->pIndex==pIdx ){
      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regSamplerecno);
      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2-1, regTemp);
      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2, regTemp2);

      sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regLast);









      sqlite3VdbeAddOp2(v, OP_Null, 0, regFirst);
      addr = sqlite3VdbeAddOp3(v, OP_Lt, regSamplerecno, 0, regLast);
      sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regLast, regFirst);
      sqlite3VdbeAddOp3(v, OP_Multiply, regLast, regTemp, regLast);

      sqlite3VdbeAddOp2(v, OP_AddImm, regLast, SQLITE_INDEX_SAMPLES*2-2);
      sqlite3VdbeAddOp3(v, OP_Divide,  regTemp2, regLast, regLast);



      sqlite3VdbeJumpHere(v, addr);
    }

    /* Zero the regSampleno and regRecno registers. */





    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRecno);
    sqlite3VdbeAddOp2(v, OP_Copy, regFirst, regSamplerecno);
#endif



    /* The block of memory cells initialized here is used as follows.
    **
    **    iMem:                
    **        The total number of rows in the table.
    **
    **    iMem+1 .. iMem+nCol: 
................................................................................
    }

    /* Start the analysis loop. This loop runs through all the entries in
    ** the index b-tree.  */
    endOfLoop = sqlite3VdbeMakeLabel(v);
    sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
    topOfLoop = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);

    for(i=0; i<nCol; i++){
      CollSeq *pColl;
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
      if( i==0 ){
#ifdef SQLITE_ENABLE_STAT2
        /* Check if the record that cursor iIdxCur points to contains a
        ** value that should be stored in the sqlite_stat2 table. If so,
        ** store it.  */
        int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno);
        assert( regTabname+1==regIdxname 
             && regTabname+2==regSampleno
             && regTabname+3==regCol
        );
        sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 4, regRec, "aaab", 0);
        sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid);
        sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid);

        /* Calculate new values for regSamplerecno and regSampleno.

        **
        **   sampleno = sampleno + 1
        **   samplerecno = samplerecno+(remaining records)/(remaining samples)

        */
        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1);
        sqlite3VdbeAddOp3(v, OP_Subtract, regRecno, regLast, regTemp);
        sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
        sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2);
        sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2);
        sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp);
        sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno);

        sqlite3VdbeJumpHere(v, ne);
        sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1);
#endif

        /* Always record the very first row */
        sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
      }
      assert( pIdx->azColl!=0 );
      assert( pIdx->azColl[i]!=0 );
      pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
      sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
                       (char*)pColl, P4_COLLSEQ);
      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);





    }
    if( db->mallocFailed ){
      /* If a malloc failure has occurred, then the result of the expression 
      ** passed as the second argument to the call to sqlite3VdbeJumpHere() 
      ** below may be negative. Which causes an assert() to fail (or an
      ** out-of-bounds write if SQLITE_DEBUG is not defined).  */
      return;
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
    for(i=0; i<nCol; i++){
      int addr2 = sqlite3VdbeCurrentAddr(v) - (nCol*2);
      if( i==0 ){
        sqlite3VdbeJumpHere(v, addr2-1);  /* Set jump dest for the OP_IfNot */




      }
      sqlite3VdbeJumpHere(v, addr2);      /* Set jump dest for the OP_Ne */
      sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
    }


    /* End of the analysis loop. */
    sqlite3VdbeResolveLabel(v, endOfLoop);
    sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
    sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);




    /* Store the results in sqlite_stat1.
    **
    ** The result is a single row of the sqlite_stat1 table.  The first
    ** two columns are the names of the table and index.  The third column
    ** is a string composed of a list of integer statistics about the
    ** index.  The first integer in the list is the total number of entries







|







 







>







 







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



>

<
<
<
<
<
<
<
<







 







>
>


>





>
>







 







|
|
<
|

|
>
>
>
>
>
>
>
>
>
|
<
|
<
>
|
<
>
>
>
|

<
<
>
>
>
>
>

|
|
<
>
>







 







|









|
|
|
|
<
<
<
|
<

|
>

<
<
>

|
|
<
<
|
|
<
<

<



|




|
|

>
>
>
>
>
|
<
<
<
<
<
|



|

|
>
>
>
>

<



>





>
>
>







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
..
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
...
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142








143
144
145
146
147
148
149
...
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
...
195
196
197
198
199
200
201
202
203

204
205
206
207
208
209
210
211
212
213
214
215
216

217

218
219

220
221
222
223
224


225
226
227
228
229
230
231
232

233
234
235
236
237
238
239
240
241
...
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277



278

279
280
281
282


283
284
285
286


287
288


289

290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306





307
308
309
310
311
312
313
314
315
316
317
318

319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
){
  static const struct {
    const char *zName;
    const char *zCols;
  } aTable[] = {
    { "sqlite_stat1", "tbl,idx,stat" },
#ifdef SQLITE_ENABLE_STAT2
    { "sqlite_stat2", "tbl,idx,sampleno,sample,cnt" },
#endif
  };

  int aRoot[] = {0, 0};
  u8 aCreateTbl[] = {0, 0};

  int i;
................................................................................
  }

  /* Open the sqlite_stat[12] tables for writing. */
  for(i=0; i<ArraySize(aTable); i++){
    sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
    sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
    sqlite3VdbeChangeP5(v, aCreateTbl[i]);
    VdbeComment((v, "%s", aTable[i].zName));
  }
}

/*
** Generate code to do an analysis of all indices associated with
** a single table.
*/
................................................................................
  int i;                       /* Loop counter */
  int topOfLoop;               /* The top of the loop */
  int endOfLoop;               /* The end of the loop */
  int jZeroRows = -1;          /* Jump from here if number of rows is zero */
  int iDb;                     /* Index of database containing pTab */
  int regTabname = iMem++;     /* Register containing table name */
  int regIdxname = iMem++;     /* Register containing index name */
  int regSampleno = iMem++;    /* Sampleno (stat2) or stat (stat1) */
#ifdef SQLITE_ENABLE_STAT2
  int regSample = iMem++;      /* The next sample value */
  int regSampleCnt = iMem++;   /* Number of occurrances of regSample value */
  int shortJump = 0;           /* Instruction address */
  int addrStoreStat2 = 0;      /* Address of subroutine to wrote to stat2 */
  int regNext = iMem++;        /* Index of next sample to record */
  int regSampleIdx = iMem++;   /* Index of next sample */
  int regReady = iMem++;       /* True if ready to store a stat2 entry */
  int regGosub = iMem++;       /* Register holding subroutine return addr */
  int regSample2 = iMem++;     /* Number of samples to acquire times 2 */
  int regCount = iMem++;       /* Number of rows in the table */
  int regCount2 = iMem++;      /* regCount*2 */
#endif
  int regCol = iMem++;         /* Content of a column in analyzed table */
  int regRec = iMem++;         /* Register holding completed record */
  int regTemp = iMem++;        /* Temporary use register */
  int regRowid = iMem++;       /* Rowid for the inserted record */
  int once = 1;                /* One-time initialization */










  v = sqlite3GetVdbe(pParse);
  if( v==0 || NEVER(pTab==0) ){
    return;
  }
  if( pTab->tnum==0 ){
    /* Do not gather statistics on views or virtual tables */
................................................................................
  sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

  iIdxCur = pParse->nTab++;
  sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0);
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int nCol;
    KeyInfo *pKey;
    int addrIfNot;               /* address of OP_IfNot */
    int *aChngAddr;              /* Array of jump instruction addresses */

    if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;
    VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
    nCol = pIdx->nColumn;
    pKey = sqlite3IndexKeyinfo(pParse, pIdx);
    if( iMem+1+(nCol*2)>pParse->nMem ){
      pParse->nMem = iMem+1+(nCol*2);
    }
    aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*pIdx->nColumn);
    if( aChngAddr==0 ) continue;

    /* Open a cursor to the index to be analyzed. */
    assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) );
    sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb,
        (char *)pKey, P4_KEYINFO_HANDOFF);
    VdbeComment((v, "%s", pIdx->zName));

................................................................................
    sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0);

#ifdef SQLITE_ENABLE_STAT2

    /* If this iteration of the loop is generating code to analyze the
    ** first index in the pTab->pIndex list, then register regLast has
    ** not been populated. In this case populate it now.  */
    if( once ){
      once = 0;

      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2, regSample2);

      sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount);
      sqlite3VdbeAddOp3(v, OP_Add, regCount, regCount, regCount2);


      /* Generate code for a subroutine that store the most recent sample
      ** in the sqlite_stat2 table
      */
      shortJump = sqlite3VdbeAddOp0(v, OP_Goto);
      sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 5, regRec, "aaaba", 0);
      VdbeComment((v, "begin stat2 write subroutine"));
      sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid);

      sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid);

      sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1);
      sqlite3VdbeAddOp2(v, OP_AddImm, regReady, -1);

      addrStoreStat2 = sqlite3VdbeAddOp2(v, OP_IfPos, regReady, shortJump+1);
      sqlite3VdbeAddOp1(v, OP_Return, regGosub);
      VdbeComment((v, "end stat2 write subroutine"));
      sqlite3VdbeJumpHere(v, shortJump);
    }


    /* Reset state registers */
    sqlite3VdbeAddOp2(v, OP_Copy, regCount2, regNext);
    shortJump = sqlite3VdbeAddOp3(v, OP_Lt, regSample2, 0, regCount);
    sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regCount, regNext);
    sqlite3VdbeJumpHere(v, shortJump);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleIdx);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regReady);


#endif /* SQLITE_ENABLE_STAT2 */

    /* The block of memory cells initialized here is used as follows.
    **
    **    iMem:                
    **        The total number of rows in the table.
    **
    **    iMem+1 .. iMem+nCol: 
................................................................................
    }

    /* Start the analysis loop. This loop runs through all the entries in
    ** the index b-tree.  */
    endOfLoop = sqlite3VdbeMakeLabel(v);
    sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
    topOfLoop = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);  /* Increment row counter */

    for(i=0; i<nCol; i++){
      CollSeq *pColl;
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
      if( i==0 ){
#ifdef SQLITE_ENABLE_STAT2
        /* Check if the record that cursor iIdxCur points to contains a
        ** value that should be stored in the sqlite_stat2 table. If so,
        ** store it.  */
        int ne = sqlite3VdbeAddOp3(v, OP_Ne, iMem, 0, regNext);
        VdbeComment((v, "jump if not a sample"));
        sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
        sqlite3VdbeAddOp2(v, OP_Copy, regCol, regSample);



        sqlite3VdbeAddOp2(v, OP_AddImm, regReady, 1);


        /* Calculate new values for regNextSample.  Where N is the number
        ** of rows in the table and S is the number of samples to take:
        **


        **   nextSample = (sampleNumber*N*2 + N)/(2*S)
        */
        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleIdx, 1);
        sqlite3VdbeAddOp3(v, OP_Multiply, regSampleIdx, regCount2, regNext);


        sqlite3VdbeAddOp3(v, OP_Add, regNext, regCount, regNext);
        sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regNext, regNext);


        sqlite3VdbeJumpHere(v, ne);

#endif

        /* Always record the very first row */
        addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
      }
      assert( pIdx->azColl!=0 );
      assert( pIdx->azColl[i]!=0 );
      pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
      aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
                                      (char*)pColl, P4_COLLSEQ);
      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
      VdbeComment((v, "jump if column %d changed", i));
#ifdef SQLITE_ENABLE_STAT2
      if( i==0 && addrStoreStat2 ){
        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleCnt, 1);
        VdbeComment((v, "incr repeat count"));
      }





#endif
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
    for(i=0; i<nCol; i++){
      sqlite3VdbeJumpHere(v, aChngAddr[i]);  /* Set jump dest for the OP_Ne */
      if( i==0 ){
        sqlite3VdbeJumpHere(v, addrIfNot);   /* Jump dest for OP_IfNot */
#ifdef SQLITE_ENABLE_STAT2
        sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
        sqlite3VdbeAddOp2(v, OP_Integer, 1, regSampleCnt);
#endif        
      }

      sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
    }
    sqlite3DbFree(db, aChngAddr);

    /* End of the analysis loop. */
    sqlite3VdbeResolveLabel(v, endOfLoop);
    sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
    sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
#ifdef SQLITE_ENABLE_STAT2
    sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
#endif        

    /* Store the results in sqlite_stat1.
    **
    ** The result is a single row of the sqlite_stat1 table.  The first
    ** two columns are the names of the table and index.  The third column
    ** is a string composed of a list of integer statistics about the
    ** index.  The first integer in the list is the total number of entries

Changes to src/vdbe.c.

1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
....
1536
1537
1538
1539
1540
1541
1542

1543
1544
1545
1546
1547
1548
1549
*/
case OP_Copy: {             /* in1, out2 */
  pIn1 = &aMem[pOp->p1];
  pOut = &aMem[pOp->p2];
  assert( pOut!=pIn1 );
  sqlite3VdbeMemShallowCopy(pOut, pIn1, MEM_Ephem);
  Deephemeralize(pOut);
  REGISTER_TRACE(pOp->p2, pOut);
  break;
}

/* Opcode: SCopy P1 P2 * * *
**
** Make a shallow copy of register P1 into register P2.
**
................................................................................
** To force any register to be an integer, just add 0.
*/
case OP_AddImm: {            /* in1 */
  pIn1 = &aMem[pOp->p1];
  memAboutToChange(p, pIn1);
  sqlite3VdbeMemIntegerify(pIn1);
  pIn1->u.i += pOp->p2;

  break;
}

/* Opcode: MustBeInt P1 P2 * * *
** 
** Force the value in register P1 to be an integer.  If the value
** in P1 is not an integer and cannot be converted into an integer







<







 







>







1049
1050
1051
1052
1053
1054
1055

1056
1057
1058
1059
1060
1061
1062
....
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
*/
case OP_Copy: {             /* in1, out2 */
  pIn1 = &aMem[pOp->p1];
  pOut = &aMem[pOp->p2];
  assert( pOut!=pIn1 );
  sqlite3VdbeMemShallowCopy(pOut, pIn1, MEM_Ephem);
  Deephemeralize(pOut);

  break;
}

/* Opcode: SCopy P1 P2 * * *
**
** Make a shallow copy of register P1 into register P2.
**
................................................................................
** To force any register to be an integer, just add 0.
*/
case OP_AddImm: {            /* in1 */
  pIn1 = &aMem[pOp->p1];
  memAboutToChange(p, pIn1);
  sqlite3VdbeMemIntegerify(pIn1);
  pIn1->u.i += pOp->p2;
  REGISTER_TRACE(pOp->p1, pIn1);
  break;
}

/* Opcode: MustBeInt P1 P2 * * *
** 
** Force the value in register P1 to be an integer.  If the value
** in P1 is not an integer and cannot be converted into an integer

Changes to test/analyze2.test.

67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
...
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
do_test analyze2-1.1 {
  execsql { CREATE TABLE t1(x PRIMARY KEY) }
  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i) }
  }
  execsql { 
    ANALYZE;
    SELECT * FROM sqlite_stat2;
  }
} [list t1 sqlite_autoindex_t1_1 0 50  \
        t1 sqlite_autoindex_t1_1 1 149 \
        t1 sqlite_autoindex_t1_1 2 249 \
        t1 sqlite_autoindex_t1_1 3 349 \
        t1 sqlite_autoindex_t1_1 4 449 \
        t1 sqlite_autoindex_t1_1 5 549 \
        t1 sqlite_autoindex_t1_1 6 649 \
        t1 sqlite_autoindex_t1_1 7 749 \
        t1 sqlite_autoindex_t1_1 8 849 \
        t1 sqlite_autoindex_t1_1 9 949 \
]

do_test analyze2-1.2 {
  execsql {
    DELETE FROM t1 WHERe x>9;
    ANALYZE;
    SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2;
  }
} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}}
do_test analyze2-1.3 {
  execsql {
    DELETE FROM t1 WHERE x>8;
................................................................................
  execsql ANALYZE
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x {100 299 499 699 899 ajj cjj ejj gjj ijj}}
do_test analyze2-3.2 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_y' 
    GROUP BY tbl,idx
  }
} {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}}

do_eqp_test 3.3 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
}
do_eqp_test 3.4 {







|

|













|







 







|







|







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
...
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
do_test analyze2-1.1 {
  execsql { CREATE TABLE t1(x PRIMARY KEY) }
  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i) }
  }
  execsql { 
    ANALYZE;
    SELECT tbl, idx, sampleno, sample FROM sqlite_stat2;
  }
} [list t1 sqlite_autoindex_t1_1 0 49  \
        t1 sqlite_autoindex_t1_1 1 149 \
        t1 sqlite_autoindex_t1_1 2 249 \
        t1 sqlite_autoindex_t1_1 3 349 \
        t1 sqlite_autoindex_t1_1 4 449 \
        t1 sqlite_autoindex_t1_1 5 549 \
        t1 sqlite_autoindex_t1_1 6 649 \
        t1 sqlite_autoindex_t1_1 7 749 \
        t1 sqlite_autoindex_t1_1 8 849 \
        t1 sqlite_autoindex_t1_1 9 949 \
]

do_test analyze2-1.2 {
  execsql {
    DELETE FROM t1 WHERE x>9;
    ANALYZE;
    SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2;
  }
} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}}
do_test analyze2-1.3 {
  execsql {
    DELETE FROM t1 WHERE x>8;
................................................................................
  execsql ANALYZE
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x {99 299 499 699 899 ajj cjj ejj gjj ijj}}
do_test analyze2-3.2 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_y' 
    GROUP BY tbl,idx
  }
} {t1 t1_y {99 299 499 699 899 ajj cjj ejj gjj ijj}}

do_eqp_test 3.3 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
}
do_eqp_test 3.4 {