SQLite

Check-in [e93c248c84]
Login

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

Overview
Comment:Bug fixes to the sample-count logic for STAT2. A few test cases added.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | query-planner-tweaks
Files: files | file ages | folders
SHA1: e93c248c8470622944cf7f9e5af4f10c4e4809e3
User & Date: drh 2011-08-05 22:31:19.378
Context
2011-08-06
01:22
Fix a compiler warning when STAT2 is off. More test cases. Fix legacy tests to deal with the new STAT2 logic. (check-in: 7e914aa999 user: drh tags: query-planner-tweaks)
2011-08-05
22:31
Bug fixes to the sample-count logic for STAT2. A few test cases added. (check-in: e93c248c84 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: eb43422827 user: drh tags: query-planner-tweaks)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/analyze.c.
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
    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,







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







263
264
265
266
267
268
269






















270
271
272
273
274
275
276
    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 ){






















        /* 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,
317
318
319
320
321
322
323
324
325

























326
327
328
329
330
331
332
#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.







|

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







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
#endif        
      }
      sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
    }
    sqlite3DbFree(db, aChngAddr);

    /* Always jump here after updating the iMem+1...iMem+1+nCol counters */
    sqlite3VdbeResolveLabel(v, endOfLoop);

#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_Le, regNext, 0, iMem);
    VdbeComment((v, "jump if not a sample"));
    shortJump = sqlite3VdbeAddOp1(v, OP_If, regReady);
    sqlite3VdbeAddOp2(v, OP_Copy, iMem+nCol+1, regSample);
    sqlite3VdbeJumpHere(v, shortJump);
    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

    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.
Changes to src/vdbe.c.
2464
2465
2466
2467
2468
2469
2470

2471
2472
2473
2474
2475
2476
2477
  memAboutToChange(p, pOut);

  /* Loop through the elements that will make up the record to figure
  ** out how much space is required for the new record.
  */
  for(pRec=pData0; pRec<=pLast; pRec++){
    assert( memIsValid(pRec) );

    if( zAffinity ){
      applyAffinity(pRec, zAffinity[pRec-pData0], encoding);
    }
    if( pRec->flags&MEM_Zero && pRec->n>0 ){
      sqlite3VdbeMemExpandBlob(pRec);
    }
    serial_type = sqlite3VdbeSerialType(pRec, file_format);







>







2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
  memAboutToChange(p, pOut);

  /* Loop through the elements that will make up the record to figure
  ** out how much space is required for the new record.
  */
  for(pRec=pData0; pRec<=pLast; pRec++){
    assert( memIsValid(pRec) );
    REGISTER_TRACE((pRec-pData0)+pOp->p1, pRec);
    if( zAffinity ){
      applyAffinity(pRec, zAffinity[pRec-pData0], encoding);
    }
    if( pRec->flags&MEM_Zero && pRec->n>0 ){
      sqlite3VdbeMemExpandBlob(pRec);
    }
    serial_type = sqlite3VdbeSerialType(pRec, file_format);
Added test/analyze8.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
# 2011 August 5
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the ANALYZE command under STAT2.
# Testing the logic that computes the number of copies of each sample.
#

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

# There is nothing to test if ANALYZE is disable for this build.
#
ifcapable {!analyze||!vtab||!stat2} {
  finish_test
  return
}

# Generate some test data
#
do_test analyze8-1.0 {
  set x 100
  set y 1
  set ycnt 0
  set yinc 10
  execsql {
    CREATE TABLE t1(x,y);
    ANALYZE;
    BEGIN;
    CREATE INDEX t1x ON t1(x);
    CREATE INDEX t1y ON t1(y);
  }
  for {set i 0} {$i<20} {incr i} {
    for {set j 0} {$j<300} {incr j} {
      execsql {INSERT INTO t1 VALUES($x,$y)}
      incr ycnt
      if {$ycnt>=$yinc} {set ycnt 0; incr y}
    }
    for {set j 0} {$j<100} {incr j} {
      incr x
      execsql {INSERT INTO t1 VALUES($x,$y)}
      incr ycnt
      if {$ycnt>=$yinc} {set ycnt 0; incr y}
    }
  }
  execsql {
    COMMIT;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
  }
} {200 301 400 301 600 301 800 301 1000 301 1200 301 1400 301 1600 301 1800 301 2000 301}
do_test analyze8-1.1 {
  execsql {
    SELECT count(*) FROM t1 WHERE x=200;
  }
} {301}

do_test analyze8-2.0 {
  execsql {
    BEGIN;
    DELETE FROM t1;
  }
  for {set x 1} {$x<200} {incr x} {
    execsql {INSERT INTO t1 VALUES($x,$x)}
  }
  for {set i 0} {$i<200} {incr i} {
    execsql {INSERT INTO t1 VALUES(999,999)}
  }
  execsql {
    COMMIT;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
  }
} {20 1 60 1 100 1 140 1 180 1 999 200 999 200 999 200 999 200 999 200}
do_test analyze8-2.1 {
  for {set i 0} {$i<200} {incr i} {
    execsql {INSERT INTO t1 VALUES(0,999)}
  }
  execsql {
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
  }
} {0 200 0 200 0 200 10 1 70 1 130 1 190 1 999 200 999 200 999 200}