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 |
Timelines: | family | ancestors | descendants | both | omit-rowid |
Files: | files | file ages | folders |
SHA1: |
79befe3ac1f676272b78423b9aa5dac4 |
User & Date: | drh 2013-11-04 22:04:17.582 |
Context
2013-11-05
| ||
01:59 | Add the conflict2.test script. Fix issues discovered by this script. (check-in: 294ed33756 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: 79befe3ac1 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: 1ea43c0f23 user: drh tags: omit-rowid) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
522 523 524 525 526 527 528 | } /* ** Copy the contents of sample *pNew into the p->a[] array. If necessary, ** remove the least desirable sample from p->a[] to make room. */ static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){ | | | 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 | } /* ** Copy the contents of sample *pNew into the p->a[] array. If necessary, ** remove the least desirable sample from p->a[] to make room. */ static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){ Stat4Sample *pSample = 0; int i; assert( IsStat4 || nEqZero==0 ); #ifdef SQLITE_ENABLE_STAT4 if( pNew->isPSample==0 ){ Stat4Sample *pUpgrade = 0; |
︙ | ︙ | |||
565 566 567 568 569 570 571 572 573 574 575 576 577 578 | Stat4Sample *pMin = &p->a[p->iMin]; tRowcnt *anEq = pMin->anEq; tRowcnt *anLt = pMin->anLt; tRowcnt *anDLt = pMin->anDLt; sampleClear(p->db, pMin); memmove(pMin, &pMin[1], sizeof(p->a[0])*(p->nSample-p->iMin-1)); pSample = &p->a[p->nSample-1]; pSample->anEq = anEq; pSample->anDLt = anDLt; pSample->anLt = anLt; p->nSample = p->mxSample-1; } /* The "rows less-than" for the rowid column must be greater than that | > | 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 | Stat4Sample *pMin = &p->a[p->iMin]; tRowcnt *anEq = pMin->anEq; tRowcnt *anLt = pMin->anLt; tRowcnt *anDLt = pMin->anDLt; sampleClear(p->db, pMin); memmove(pMin, &pMin[1], sizeof(p->a[0])*(p->nSample-p->iMin-1)); pSample = &p->a[p->nSample-1]; pSample->nRowid = 0; pSample->anEq = anEq; pSample->anDLt = anDLt; pSample->anLt = anLt; p->nSample = p->mxSample-1; } /* The "rows less-than" for the rowid column must be greater than that |
︙ | ︙ |
Changes to src/vdbemem.c.
︙ | ︙ | |||
1027 1028 1029 1030 1031 1032 1033 | if( p ){ UnpackedRecord *pRec = p->ppRec[0]; if( pRec==0 ){ Index *pIdx = p->pIdx; /* Index being probed */ int nByte; /* Bytes of space to allocate */ int i; /* Counter variable */ | | | 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 | if( p ){ UnpackedRecord *pRec = p->ppRec[0]; if( pRec==0 ){ Index *pIdx = p->pIdx; /* Index being probed */ int nByte; /* Bytes of space to allocate */ int i; /* Counter variable */ int nCol = pIdx->nColumn; /* Number of index columns including rowid */ nByte = sizeof(Mem) * nCol + sizeof(UnpackedRecord); pRec = (UnpackedRecord*)sqlite3DbMallocZero(db, nByte); if( pRec ){ pRec->pKeyInfo = sqlite3IndexKeyinfo(p->pParse, pIdx); if( pRec->pKeyInfo ){ assert( pRec->pKeyInfo->nField+pRec->pKeyInfo->nXField==nCol ); |
︙ | ︙ |
Added test/index7.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 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 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 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 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 194 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 242 243 244 245 246 247 248 249 250 251 | # 2013-11-04 # # 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. # #*********************************************************************** # # Test cases for partial indices in WITHOUT ROWID tables # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !vtab { finish_test return } load_static_extension db wholenumber; do_test index7-1.1 { # Able to parse and manage partial indices execsql { CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid; CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL; CREATE INDEX t1b ON t1(b) WHERE b>10; CREATE VIRTUAL TABLE nums USING wholenumber; INSERT INTO t1(a,b,c) SELECT CASE WHEN value%3!=0 THEN value END, value, value FROM nums WHERE value<=20; SELECT count(a), count(b) FROM t1; PRAGMA integrity_check; } } {14 20 ok} # Make sure the count(*) optimization works correctly with # partial indices. Ticket [a5c8ed66cae16243be6] 2013-10-03. # do_execsql_test index7-1.1.1 { SELECT count(*) FROM t1; } {20} # Error conditions during parsing... # do_test index7-1.2 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL; } } {1 {no such column: x}} do_test index7-1.3 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1); } } {1 {subqueries prohibited in partial index WHERE clauses}} do_test index7-1.4 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1; } } {1 {parameters prohibited in partial index WHERE clauses}} do_test index7-1.5 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE a!=random(); } } {1 {functions prohibited in partial index WHERE clauses}} do_test index7-1.6 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; } } {1 {functions prohibited in partial index WHERE clauses}} do_test index7-1.10 { execsql { ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1 {20 1} t1a {14 1} t1b {10 1} ok} # STAT1 shows the partial indices have a reduced number of # rows. # do_test index7-1.11 { execsql { UPDATE t1 SET a=b; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1 {20 1} t1a {20 1} t1b {10 1} ok} do_test index7-1.11b { execsql { UPDATE t1 SET a=NULL WHERE b%3!=0; UPDATE t1 SET b=b+100; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1 {20 1} t1a {6 1} t1b {20 1} ok} do_test index7-1.12 { execsql { UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END; UPDATE t1 SET b=b-100; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1 {20 1} t1a {13 1} t1b {10 1} ok} do_test index7-1.13 { execsql { DELETE FROM t1 WHERE b BETWEEN 8 AND 12; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1 {15 1} t1a {10 1} t1b {8 1} ok} do_test index7-1.14 { execsql { REINDEX; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1 {15 1} t1a {10 1} t1b {8 1} ok} do_test index7-1.15 { execsql { CREATE INDEX t1c ON t1(c); ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1 {15 1} t1a {10 1} t1b {8 1} t1c {15 1} ok} # Queries use partial indices as appropriate times. # do_test index7-2.1 { execsql { CREATE TABLE t2(a,b PRIMARY KEY) without rowid; INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000; UPDATE t2 SET a=NULL WHERE b%5==0; CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL; SELECT count(*) FROM t2 WHERE a IS NOT NULL; } } {800} do_test index7-2.2 { execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a=5; } } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/} ifcapable stat4||stat3 { do_test index7-2.3stat4 { execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a IS NOT NULL; } } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/} } else { do_test index7-2.3stat4 { execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a IS NOT NULL AND a>0; } } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/} } do_test index7-2.4 { execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a IS NULL; } } {~/.*INDEX t2a1.*/} do_execsql_test index7-2.101 { DROP INDEX t2a1; UPDATE t2 SET a=b, b=b+10000; SELECT b FROM t2 WHERE a=15; } {10015} do_execsql_test index7-2.102 { CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200; SELECT b FROM t2 WHERE a=15; PRAGMA integrity_check; } {10015 ok} do_execsql_test index7-2.102eqp { EXPLAIN QUERY PLAN SELECT b FROM t2 WHERE a=15; } {~/.*INDEX t2a2.*/} do_execsql_test index7-2.103 { SELECT b FROM t2 WHERE a=15 AND a<100; } {10015} do_execsql_test index7-2.103eqp { EXPLAIN QUERY PLAN SELECT b FROM t2 WHERE a=15 AND a<100; } {/.*INDEX t2a2.*/} do_execsql_test index7-2.104 { SELECT b FROM t2 WHERE a=515 AND a>200; } {10515} do_execsql_test index7-2.104eqp { EXPLAIN QUERY PLAN SELECT b FROM t2 WHERE a=515 AND a>200; } {/.*INDEX t2a2.*/} # Partial UNIQUE indices # do_execsql_test index7-3.1 { CREATE TABLE t3(a,b PRIMARY KEY) without rowid; INSERT INTO t3 SELECT value, value FROM nums WHERE value<200; UPDATE t3 SET a=999 WHERE b%5!=0; CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999; } {} do_test index7-3.2 { # unable to insert a duplicate row a-value that is not 999. catchsql { INSERT INTO t3(a,b) VALUES(150, 'test1'); } } {1 {column a is not unique}} do_test index7-3.3 { # can insert multiple rows with a==999 because such rows are not # part of the unique index. catchsql { INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2'); } } {0 {}} do_execsql_test index7-3.4 { SELECT count(*) FROM t3 WHERE a=999; } {162} integrity_check index7-3.5 do_execsql_test index7-4.0 { VACUUM; PRAGMA integrity_check; } {ok} # Silently ignore database name qualifiers in partial indices. # do_execsql_test index7-5.0 { CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10; /* ^^^^^-- ignored */ ANALYZE; SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10; SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b'; } {6 6} finish_test |