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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a5867cfc4c9b9155fa345247dec29e38 |
User & Date: | drh 2011-01-04 19:01:27.000 |
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: 73128d4ef5 user: drh tags: trunk) | |
19:01 | Fix the ANALYZE command so that it takes collating sequences into account when gathering index statistics. (check-in: a5867cfc4c 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: 824c8dd301 user: drh tags: trunk) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
229 230 231 232 233 234 235 236 237 238 239 240 241 242 | ** the index b-tree. */ endOfLoop = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); topOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); for(i=0; i<nCol; i++){ 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, regRecno, 0, regSamplerecno); | > | 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 | ** the index b-tree. */ endOfLoop = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); topOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); 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, regRecno, 0, regSamplerecno); |
︙ | ︙ | |||
265 266 267 268 269 270 271 | sqlite3VdbeJumpHere(v, ne); sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1); #endif /* Always record the very first row */ sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1); } | > > > | < > | 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 | sqlite3VdbeJumpHere(v, ne); sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1); #endif /* Always record the very first row */ sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1); } assert( pIdx->azColl!=0 ); assert( pIdx->azColl[i]!=0 ); pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]); sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1, (char*)pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); } if( db->mallocFailed ){ /* If a malloc failure has occurred, then the result of the expression ** passed as the second argument to the call to sqlite3VdbeJumpHere() ** below may be negative. Which causes an assert() to fail (or an ** out-of-bounds write if SQLITE_DEBUG is not defined). */ |
︙ | ︙ |
Changes to test/analyze4.test.
︙ | ︙ | |||
10 11 12 13 14 15 16 17 18 19 20 21 22 23 | #*********************************************************************** # # This file implements regression tests for SQLite library. This file # implements tests for ANALYZE to verify that multiple rows containing # a NULL value count as distinct rows for the purposes of analyze # statistics. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test analyze4-1.0 { db eval { CREATE TABLE t1(a,b); | > > | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | #*********************************************************************** # # This file implements regression tests for SQLite library. This file # implements tests for ANALYZE to verify that multiple rows containing # a NULL value count as distinct rows for the purposes of analyze # statistics. # # Also include test cases for collating sequences on indices. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test analyze4-1.0 { db eval { CREATE TABLE t1(a,b); |
︙ | ︙ | |||
72 73 74 75 76 77 78 79 80 | CREATE INDEX t1bcd ON t1(b,c,d); CREATE INDEX t1cdb ON t1(c,d,b); CREATE INDEX t1cbd ON t1(c,b,d); ANALYZE; SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx; } } {t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | CREATE INDEX t1bcd ON t1(b,c,d); CREATE INDEX t1cdb ON t1(c,d,b); CREATE INDEX t1cbd ON t1(c,b,d); ANALYZE; SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx; } } {t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}} # Verify that collating sequences are taken into account when computing # ANALYZE statistics. # do_test analyze4-2.0 { db eval { CREATE TABLE t2( x INTEGER PRIMARY KEY, a TEXT COLLATE nocase, b TEXT COLLATE rtrim, c TEXT COLLATE binary ); CREATE INDEX t2a ON t2(a); CREATE INDEX t2b ON t2(b); CREATE INDEX t2c ON t2(c); CREATE INDEX t2c2 ON t2(c COLLATE nocase); CREATE INDEX t2c3 ON t2(c COLLATE rtrim); INSERT INTO t2 VALUES(1, 'abc', 'abc', 'abc'); INSERT INTO t2 VALUES(2, 'abC', 'abC', 'abC'); INSERT INTO t2 VALUES(3, 'abc ', 'abc ', 'abc '); INSERT INTO t2 VALUES(4, 'abC ', 'abC ', 'abC '); INSERT INTO t2 VALUES(5, 'aBc', 'aBc', 'aBc'); INSERT INTO t2 VALUES(6, 'aBC', 'aBC', 'aBC'); INSERT INTO t2 VALUES(7, 'aBc ', 'aBc ', 'aBc '); INSERT INTO t2 VALUES(8, 'aBC ', 'aBC ', 'aBC '); ANALYZE; SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t2' ORDER BY idx; } } {t2a {8 4} t2b {8 2} t2c {8 1} t2c2 {8 4} t2c3 {8 2}} finish_test |