/ Check-in [a5867cfc]
Login

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

Overview
Comment:Fix the ANALYZE command so that it takes collating sequences into account when gathering index statistics.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a5867cfc4c9b9155fa345247dec29e38fffa8002
User & Date: drh 2011-01-04 19:01:27
Context
2011-01-04
20:06
Fix a null-pointer dereference that can occur on an OOM error while running ANALYZE with SQLITE_ENABLE_STAT2. check-in: 73128d4e user: drh tags: trunk
19:01
Fix the ANALYZE command so that it takes collating sequences into account when gathering index statistics. check-in: a5867cfc user: drh tags: trunk
17:57
The ANALYZE command now counts at all rows of an index, even those containing NULL values. A valid sqlite_stat1 entry is created even if the index contains nothing but NULLs. check-in: 824c8dd3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   229    229       ** the index b-tree.  */
   230    230       endOfLoop = sqlite3VdbeMakeLabel(v);
   231    231       sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
   232    232       topOfLoop = sqlite3VdbeCurrentAddr(v);
   233    233       sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);
   234    234   
   235    235       for(i=0; i<nCol; i++){
          236  +      CollSeq *pColl;
   236    237         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
   237    238         if( i==0 ){
   238    239   #ifdef SQLITE_ENABLE_STAT2
   239    240           /* Check if the record that cursor iIdxCur points to contains a
   240    241           ** value that should be stored in the sqlite_stat2 table. If so,
   241    242           ** store it.  */
   242    243           int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno);
................................................................................
   265    266           sqlite3VdbeJumpHere(v, ne);
   266    267           sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1);
   267    268   #endif
   268    269   
   269    270           /* Always record the very first row */
   270    271           sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
   271    272         }
   272         -      sqlite3VdbeAddOp3(v, OP_Ne, regCol, 0, iMem+nCol+i+1);
   273         -      /**** TODO:  add collating sequence *****/
          273  +      assert( pIdx->azColl!=0 );
          274  +      assert( pIdx->azColl[i]!=0 );
          275  +      pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
          276  +      sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
          277  +                       (char*)pColl, P4_COLLSEQ);
   274    278         sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
   275    279       }
   276    280       if( db->mallocFailed ){
   277    281         /* If a malloc failure has occurred, then the result of the expression 
   278    282         ** passed as the second argument to the call to sqlite3VdbeJumpHere() 
   279    283         ** below may be negative. Which causes an assert() to fail (or an
   280    284         ** out-of-bounds write if SQLITE_DEBUG is not defined).  */

Changes to test/analyze4.test.

    10     10   #***********************************************************************
    11     11   #
    12     12   # This file implements regression tests for SQLite library. This file 
    13     13   # implements tests for ANALYZE to verify that multiple rows containing
    14     14   # a NULL value count as distinct rows for the purposes of analyze 
    15     15   # statistics.
    16     16   #
           17  +# Also include test cases for collating sequences on indices.
           18  +#
    17     19   
    18     20   set testdir [file dirname $argv0]
    19     21   source $testdir/tester.tcl
    20     22   
    21     23   do_test analyze4-1.0 {
    22     24     db eval {
    23     25       CREATE TABLE t1(a,b);
................................................................................
    72     74       CREATE INDEX t1bcd ON t1(b,c,d);
    73     75       CREATE INDEX t1cdb ON t1(c,d,b);
    74     76       CREATE INDEX t1cbd ON t1(c,b,d);
    75     77       ANALYZE;
    76     78       SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
    77     79     }
    78     80   } {t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}}
           81  +
           82  +# Verify that collating sequences are taken into account when computing
           83  +# ANALYZE statistics.
           84  +#
           85  +do_test analyze4-2.0 {
           86  +  db eval {
           87  +    CREATE TABLE t2(
           88  +      x INTEGER PRIMARY KEY,
           89  +      a TEXT COLLATE nocase,
           90  +      b TEXT COLLATE rtrim,
           91  +      c TEXT COLLATE binary
           92  +    );
           93  +    CREATE INDEX t2a ON t2(a);
           94  +    CREATE INDEX t2b ON t2(b);
           95  +    CREATE INDEX t2c ON t2(c);
           96  +    CREATE INDEX t2c2 ON t2(c COLLATE nocase);
           97  +    CREATE INDEX t2c3 ON t2(c COLLATE rtrim);
           98  +    INSERT INTO t2 VALUES(1, 'abc', 'abc', 'abc');
           99  +    INSERT INTO t2 VALUES(2, 'abC', 'abC', 'abC');
          100  +    INSERT INTO t2 VALUES(3, 'abc ', 'abc ', 'abc ');
          101  +    INSERT INTO t2 VALUES(4, 'abC ', 'abC ', 'abC ');
          102  +    INSERT INTO t2 VALUES(5, 'aBc', 'aBc', 'aBc');
          103  +    INSERT INTO t2 VALUES(6, 'aBC', 'aBC', 'aBC');
          104  +    INSERT INTO t2 VALUES(7, 'aBc ', 'aBc ', 'aBc ');
          105  +    INSERT INTO t2 VALUES(8, 'aBC ', 'aBC ', 'aBC ');
          106  +    ANALYZE;
          107  +    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t2' ORDER BY idx;
          108  +  }
          109  +} {t2a {8 4} t2b {8 2} t2c {8 1} t2c2 {8 4} t2c3 {8 2}}
    79    110   
    80    111   finish_test