/ Check-in [824c8dd3]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 824c8dd3015bbd5c8a1dd661cfe09fe5bf7a80d3
User & Date: drh 2011-01-04 17:57:54
Context
2011-01-04
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
2010-12-29
18:24
Have testfixture invoke C routine Zipvfs_Init() when creating a new interpreter if SQLITE_ENABLE_ZIPVFS is defined. check-in: 430635da user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   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    236         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
   237         -#ifdef SQLITE_ENABLE_STAT2
   238    237         if( i==0 ){
          238  +#ifdef SQLITE_ENABLE_STAT2
   239    239           /* Check if the record that cursor iIdxCur points to contains a
   240    240           ** value that should be stored in the sqlite_stat2 table. If so,
   241    241           ** store it.  */
   242    242           int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno);
   243    243           assert( regTabname+1==regIdxname 
   244    244                && regTabname+2==regSampleno
   245    245                && regTabname+3==regCol
................................................................................
   260    260           sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2);
   261    261           sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2);
   262    262           sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp);
   263    263           sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno);
   264    264   
   265    265           sqlite3VdbeJumpHere(v, ne);
   266    266           sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1);
   267         -      }
   268    267   #endif
   269    268   
          269  +        /* Always record the very first row */
          270  +        sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
          271  +      }
   270    272         sqlite3VdbeAddOp3(v, OP_Ne, regCol, 0, iMem+nCol+i+1);
   271    273         /**** TODO:  add collating sequence *****/
   272         -      sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
          274  +      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
   273    275       }
   274    276       if( db->mallocFailed ){
   275    277         /* If a malloc failure has occurred, then the result of the expression 
   276    278         ** passed as the second argument to the call to sqlite3VdbeJumpHere() 
   277    279         ** below may be negative. Which causes an assert() to fail (or an
   278    280         ** out-of-bounds write if SQLITE_DEBUG is not defined).  */
   279    281         return;
   280    282       }
   281    283       sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
   282    284       for(i=0; i<nCol; i++){
   283         -      sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-(nCol*2));
          285  +      int addr = sqlite3VdbeCurrentAddr(v) - (nCol*2);
          286  +      if( i==0 ){
          287  +        sqlite3VdbeJumpHere(v, addr-1);  /* Set jump dest for the OP_IfNot */
          288  +      }
          289  +      sqlite3VdbeJumpHere(v, addr);      /* Set jump dest for the OP_Ne */
   284    290         sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
   285    291         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
   286    292       }
   287    293   
   288    294       /* End of the analysis loop. */
   289    295       sqlite3VdbeResolveLabel(v, endOfLoop);
   290    296       sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);

Added test/analyze4.test.

            1  +# 2011 January 04
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library. This file 
           13  +# implements tests for ANALYZE to verify that multiple rows containing
           14  +# a NULL value count as distinct rows for the purposes of analyze 
           15  +# statistics.
           16  +#
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +do_test analyze4-1.0 {
           22  +  db eval {
           23  +    CREATE TABLE t1(a,b);
           24  +    CREATE INDEX t1a ON t1(a);
           25  +    CREATE INDEX t1b ON t1(b);
           26  +    INSERT INTO t1 VALUES(1,NULL);
           27  +    INSERT INTO t1 SELECT a+1, b FROM t1;
           28  +    INSERT INTO t1 SELECT a+2, b FROM t1;
           29  +    INSERT INTO t1 SELECT a+4, b FROM t1;
           30  +    INSERT INTO t1 SELECT a+8, b FROM t1;
           31  +    INSERT INTO t1 SELECT a+16, b FROM t1;
           32  +    INSERT INTO t1 SELECT a+32, b FROM t1;
           33  +    INSERT INTO t1 SELECT a+64, b FROM t1;
           34  +    ANALYZE;
           35  +  }
           36  +
           37  +  # Should choose the t1a index since it is more specific than t1b.
           38  +  db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
           39  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           40  +
           41  +# Verify that the t1b index shows that it does not narrow down the
           42  +# search any at all.
           43  +#
           44  +do_test analyze4-1.1 {
           45  +  db eval {
           46  +    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
           47  +  }
           48  +} {t1a {128 1} t1b {128 128}}
           49  +
           50  +# Change half of the b values from NULL to a constant.  Verify
           51  +# that the number of rows selected in stat1 is half the total 
           52  +# number of rows.
           53  +#
           54  +do_test analyze4-1.2 {
           55  +  db eval {
           56  +    UPDATE t1 SET b='x' WHERE a%2;
           57  +    ANALYZE;
           58  +    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
           59  +  }
           60  +} {t1a {128 1} t1b {128 64}}
           61  +
           62  +# Change the t1.b values all back to NULL.  Add columns t1.c and t1.d.
           63  +# Create a multi-column indices using t1.b and verify that ANALYZE 
           64  +# processes them correctly.
           65  +#
           66  +do_test analyze4-1.3 {
           67  +  db eval {
           68  +    UPDATE t1 SET b=NULL;
           69  +    ALTER TABLE t1 ADD COLUMN c;
           70  +    ALTER TABLE t1 ADD COLUMN d;
           71  +    UPDATE t1 SET c=a/4, d=a/2;
           72  +    CREATE INDEX t1bcd ON t1(b,c,d);
           73  +    CREATE INDEX t1cdb ON t1(c,d,b);
           74  +    CREATE INDEX t1cbd ON t1(c,b,d);
           75  +    ANALYZE;
           76  +    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
           77  +  }
           78  +} {t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}}
           79  +
           80  +finish_test