/ Check-in [79befe3a]
Login

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

Overview
Comment:Add the index7.test script for testing partial indices with WITHOUT ROWID tables. Fix bugs in ANALYZE located by that script.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 79befe3ac1f676272b78423b9aa5dac41435420e
User & Date: drh 2013-11-04 22:04:17
Context
2013-11-05
01:59
Add the conflict2.test script. Fix issues discovered by this script. check-in: 294ed337 user: drh tags: omit-rowid
2013-11-04
22:04
Add the index7.test script for testing partial indices with WITHOUT ROWID tables. Fix bugs in ANALYZE located by that script. check-in: 79befe3a user: drh tags: omit-rowid
21:44
Bug fixes in the INSERT constraint checker. Refactor the Rowid handling logic for ANALYZE with STAT3/4. check-in: 1ea43c0f user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   522    522   }
   523    523   
   524    524   /*
   525    525   ** Copy the contents of sample *pNew into the p->a[] array. If necessary,
   526    526   ** remove the least desirable sample from p->a[] to make room.
   527    527   */
   528    528   static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){
   529         -  Stat4Sample *pSample;
          529  +  Stat4Sample *pSample = 0;
   530    530     int i;
   531    531   
   532    532     assert( IsStat4 || nEqZero==0 );
   533    533   
   534    534   #ifdef SQLITE_ENABLE_STAT4
   535    535     if( pNew->isPSample==0 ){
   536    536       Stat4Sample *pUpgrade = 0;
................................................................................
   565    565       Stat4Sample *pMin = &p->a[p->iMin];
   566    566       tRowcnt *anEq = pMin->anEq;
   567    567       tRowcnt *anLt = pMin->anLt;
   568    568       tRowcnt *anDLt = pMin->anDLt;
   569    569       sampleClear(p->db, pMin);
   570    570       memmove(pMin, &pMin[1], sizeof(p->a[0])*(p->nSample-p->iMin-1));
   571    571       pSample = &p->a[p->nSample-1];
          572  +    pSample->nRowid = 0;
   572    573       pSample->anEq = anEq;
   573    574       pSample->anDLt = anDLt;
   574    575       pSample->anLt = anLt;
   575    576       p->nSample = p->mxSample-1;
   576    577     }
   577    578   
   578    579     /* The "rows less-than" for the rowid column must be greater than that

Changes to src/vdbemem.c.

  1027   1027     if( p ){
  1028   1028       UnpackedRecord *pRec = p->ppRec[0];
  1029   1029   
  1030   1030       if( pRec==0 ){
  1031   1031         Index *pIdx = p->pIdx;      /* Index being probed */
  1032   1032         int nByte;                  /* Bytes of space to allocate */
  1033   1033         int i;                      /* Counter variable */
  1034         -      int nCol = pIdx->nKeyCol+1; /* Number of index columns including rowid */
         1034  +      int nCol = pIdx->nColumn;   /* Number of index columns including rowid */
  1035   1035     
  1036   1036         nByte = sizeof(Mem) * nCol + sizeof(UnpackedRecord);
  1037   1037         pRec = (UnpackedRecord*)sqlite3DbMallocZero(db, nByte);
  1038   1038         if( pRec ){
  1039   1039           pRec->pKeyInfo = sqlite3IndexKeyinfo(p->pParse, pIdx);
  1040   1040           if( pRec->pKeyInfo ){
  1041   1041             assert( pRec->pKeyInfo->nField+pRec->pKeyInfo->nXField==nCol );

Added test/index7.test.

            1  +# 2013-11-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  +# Test cases for partial indices in WITHOUT ROWID tables
           13  +#
           14  +
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +ifcapable !vtab {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +load_static_extension db wholenumber;
           25  +do_test index7-1.1 {
           26  +  # Able to parse and manage partial indices
           27  +  execsql {
           28  +    CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
           29  +    CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
           30  +    CREATE INDEX t1b ON t1(b) WHERE b>10;
           31  +    CREATE VIRTUAL TABLE nums USING wholenumber;
           32  +    INSERT INTO t1(a,b,c)
           33  +       SELECT CASE WHEN value%3!=0 THEN value END, value, value
           34  +         FROM nums WHERE value<=20;
           35  +    SELECT count(a), count(b) FROM t1;
           36  +    PRAGMA integrity_check;
           37  +  }
           38  +} {14 20 ok}
           39  +
           40  +# Make sure the count(*) optimization works correctly with
           41  +# partial indices.  Ticket [a5c8ed66cae16243be6] 2013-10-03.
           42  +#
           43  +do_execsql_test index7-1.1.1 {
           44  +  SELECT count(*) FROM t1;
           45  +} {20}
           46  +
           47  +# Error conditions during parsing...
           48  +#
           49  +do_test index7-1.2 {
           50  +  catchsql {
           51  +    CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL;
           52  +  }
           53  +} {1 {no such column: x}}
           54  +do_test index7-1.3 {
           55  +  catchsql {
           56  +    CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
           57  +  }
           58  +} {1 {subqueries prohibited in partial index WHERE clauses}}
           59  +do_test index7-1.4 {
           60  +  catchsql {
           61  +    CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
           62  +  }
           63  +} {1 {parameters prohibited in partial index WHERE clauses}}
           64  +do_test index7-1.5 {
           65  +  catchsql {
           66  +    CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
           67  +  }
           68  +} {1 {functions prohibited in partial index WHERE clauses}}
           69  +do_test index7-1.6 {
           70  +  catchsql {
           71  +    CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
           72  +  }
           73  +} {1 {functions prohibited in partial index WHERE clauses}}
           74  +
           75  +do_test index7-1.10 {
           76  +  execsql {
           77  +    ANALYZE;
           78  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           79  +    PRAGMA integrity_check;
           80  +  }
           81  +} {t1 {20 1} t1a {14 1} t1b {10 1} ok}
           82  +
           83  +# STAT1 shows the partial indices have a reduced number of
           84  +# rows.
           85  +#
           86  +do_test index7-1.11 {
           87  +  execsql {
           88  +    UPDATE t1 SET a=b;
           89  +    ANALYZE;
           90  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
           91  +    PRAGMA integrity_check;
           92  +  }
           93  +} {t1 {20 1} t1a {20 1} t1b {10 1} ok}
           94  +
           95  +do_test index7-1.11b {
           96  +  execsql {
           97  +    UPDATE t1 SET a=NULL WHERE b%3!=0;
           98  +    UPDATE t1 SET b=b+100;
           99  +    ANALYZE;
          100  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          101  +    PRAGMA integrity_check;
          102  +  }
          103  +} {t1 {20 1} t1a {6 1} t1b {20 1} ok}
          104  +
          105  +do_test index7-1.12 {
          106  +  execsql {
          107  +    UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END;
          108  +    UPDATE t1 SET b=b-100;
          109  +    ANALYZE;
          110  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          111  +    PRAGMA integrity_check;
          112  +  }
          113  +} {t1 {20 1} t1a {13 1} t1b {10 1} ok}
          114  +
          115  +do_test index7-1.13 {
          116  +  execsql {
          117  +    DELETE FROM t1 WHERE b BETWEEN 8 AND 12;
          118  +    ANALYZE;
          119  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          120  +    PRAGMA integrity_check;
          121  +  }
          122  +} {t1 {15 1} t1a {10 1} t1b {8 1} ok}
          123  +
          124  +do_test index7-1.14 {
          125  +  execsql {
          126  +    REINDEX;
          127  +    ANALYZE;
          128  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          129  +    PRAGMA integrity_check;
          130  +  }
          131  +} {t1 {15 1} t1a {10 1} t1b {8 1} ok}
          132  +
          133  +do_test index7-1.15 {
          134  +  execsql {
          135  +    CREATE INDEX t1c ON t1(c);
          136  +    ANALYZE;
          137  +    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
          138  +    PRAGMA integrity_check;
          139  +  }
          140  +} {t1 {15 1} t1a {10 1} t1b {8 1} t1c {15 1} ok}
          141  +
          142  +# Queries use partial indices as appropriate times.
          143  +#
          144  +do_test index7-2.1 {
          145  +  execsql {
          146  +    CREATE TABLE t2(a,b PRIMARY KEY) without rowid;
          147  +    INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;
          148  +    UPDATE t2 SET a=NULL WHERE b%5==0;
          149  +    CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;
          150  +    SELECT count(*) FROM t2 WHERE a IS NOT NULL;
          151  +  }
          152  +} {800}
          153  +do_test index7-2.2 {
          154  +  execsql {
          155  +    EXPLAIN QUERY PLAN
          156  +    SELECT * FROM t2 WHERE a=5;
          157  +  }
          158  +} {/.* TABLE t2 USING COVERING INDEX t2a1 .*/}
          159  +ifcapable stat4||stat3 {
          160  +  do_test index7-2.3stat4 {
          161  +    execsql {
          162  +      EXPLAIN QUERY PLAN
          163  +      SELECT * FROM t2 WHERE a IS NOT NULL;
          164  +    }
          165  +  } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/}
          166  +} else {
          167  +  do_test index7-2.3stat4 {
          168  +    execsql {
          169  +      EXPLAIN QUERY PLAN
          170  +      SELECT * FROM t2 WHERE a IS NOT NULL AND a>0;
          171  +    }
          172  +  } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/}
          173  +}
          174  +do_test index7-2.4 {
          175  +  execsql {
          176  +    EXPLAIN QUERY PLAN
          177  +    SELECT * FROM t2 WHERE a IS NULL;
          178  +  }
          179  +} {~/.*INDEX t2a1.*/}
          180  +
          181  +do_execsql_test index7-2.101 {
          182  +  DROP INDEX t2a1;
          183  +  UPDATE t2 SET a=b, b=b+10000;
          184  +  SELECT b FROM t2 WHERE a=15;
          185  +} {10015}
          186  +do_execsql_test index7-2.102 {
          187  +  CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200;
          188  +  SELECT b FROM t2 WHERE a=15;
          189  +  PRAGMA integrity_check;
          190  +} {10015 ok}
          191  +do_execsql_test index7-2.102eqp {
          192  +  EXPLAIN QUERY PLAN
          193  +  SELECT b FROM t2 WHERE a=15;
          194  +} {~/.*INDEX t2a2.*/}
          195  +do_execsql_test index7-2.103 {
          196  +  SELECT b FROM t2 WHERE a=15 AND a<100;
          197  +} {10015}
          198  +do_execsql_test index7-2.103eqp {
          199  +  EXPLAIN QUERY PLAN
          200  +  SELECT b FROM t2 WHERE a=15 AND a<100;
          201  +} {/.*INDEX t2a2.*/}
          202  +do_execsql_test index7-2.104 {
          203  +  SELECT b FROM t2 WHERE a=515 AND a>200;
          204  +} {10515}
          205  +do_execsql_test index7-2.104eqp {
          206  +  EXPLAIN QUERY PLAN
          207  +  SELECT b FROM t2 WHERE a=515 AND a>200;
          208  +} {/.*INDEX t2a2.*/}
          209  +
          210  +# Partial UNIQUE indices
          211  +#
          212  +do_execsql_test index7-3.1 {
          213  +  CREATE TABLE t3(a,b PRIMARY KEY) without rowid;
          214  +  INSERT INTO t3 SELECT value, value FROM nums WHERE value<200;
          215  +  UPDATE t3 SET a=999 WHERE b%5!=0;
          216  +  CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
          217  +} {}
          218  +do_test index7-3.2 {
          219  +  # unable to insert a duplicate row a-value that is not 999.
          220  +  catchsql {
          221  +    INSERT INTO t3(a,b) VALUES(150, 'test1');
          222  +  }
          223  +} {1 {column a is not unique}}
          224  +do_test index7-3.3 {
          225  +  # can insert multiple rows with a==999 because such rows are not
          226  +  # part of the unique index.
          227  +  catchsql {
          228  +    INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');
          229  +  }
          230  +} {0 {}}
          231  +do_execsql_test index7-3.4 {
          232  +  SELECT count(*) FROM t3 WHERE a=999;
          233  +} {162}
          234  +integrity_check index7-3.5
          235  +
          236  +do_execsql_test index7-4.0 {
          237  +  VACUUM;
          238  +  PRAGMA integrity_check;
          239  +} {ok}
          240  +
          241  +# Silently ignore database name qualifiers in partial indices.
          242  +#
          243  +do_execsql_test index7-5.0 {
          244  +  CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10;
          245  +                               /* ^^^^^-- ignored */
          246  +  ANALYZE;
          247  +  SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10;
          248  +  SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b';
          249  +} {6 6}
          250  +
          251  +finish_test