Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not generate sqlite_stat1 entries for empty tables when running ANALYZE. Ticket [83ea97620bd31016451] |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3a27af5b3c688c651ba1fae261026ef7 |
User & Date: | drh 2011-03-30 14:54:05.390 |
Context
2011-03-30
| ||
19:08 | Further tests for os_unix.c. (check-in: a84f771194 user: dan tags: trunk) | |
14:54 | Do not generate sqlite_stat1 entries for empty tables when running ANALYZE. Ticket [83ea97620bd31016451] (check-in: 3a27af5b3c user: drh tags: trunk) | |
2011-03-29
| ||
18:28 | Add tests to syscall.test and sysfault.test. (check-in: 3d2de01181 user: dan tags: trunk) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
110 111 112 113 114 115 116 | Index *pIdx; /* An index to being analyzed */ int iIdxCur; /* Cursor open on index being analyzed */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ int topOfLoop; /* The top of the loop */ int endOfLoop; /* The end of the loop */ int addr = 0; /* The address of an instruction */ | | | 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | Index *pIdx; /* An index to being analyzed */ int iIdxCur; /* Cursor open on index being analyzed */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ int topOfLoop; /* The top of the loop */ int endOfLoop; /* The end of the loop */ int addr = 0; /* The address of an instruction */ int jZeroRows = -1; /* Jump from here if number of rows is zero */ int iDb; /* Index of database containing pTab */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regSampleno = iMem++; /* Register containing next sample number */ int regCol = iMem++; /* Content of a column analyzed table */ int regRec = iMem++; /* Register holding completed record */ int regTemp = iMem++; /* Temporary use register */ |
︙ | ︙ | |||
315 316 317 318 319 320 321 | ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno); | | | 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 | ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno); if( jZeroRows<0 ){ jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem); } for(i=0; i<nCol; i++){ sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0); sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno); sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp); sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); |
︙ | ︙ | |||
341 342 343 344 345 346 347 348 | ** containing NULL as the index name and the row count as the content. */ if( pTab->pIndex==0 ){ sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb); VdbeComment((v, "%s", pTab->zName)); sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regSampleno); sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); }else{ | > | | < < | < | 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 | ** containing NULL as the index name and the row count as the content. */ if( pTab->pIndex==0 ){ sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb); VdbeComment((v, "%s", pTab->zName)); sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regSampleno); sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regSampleno); }else{ sqlite3VdbeJumpHere(v, jZeroRows); jZeroRows = sqlite3VdbeAddOp0(v, OP_Goto); } sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); if( pParse->nMem<regRec ) pParse->nMem = regRec; sqlite3VdbeJumpHere(v, jZeroRows); } /* ** Generate code that will cause the most recent index analysis to ** be loaded into internal hash tables where is can be used. */ static void loadAnalysis(Parse *pParse, int iDb){ |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
555 556 557 558 559 560 561 | } /* ** Change the P2 operand of instruction addr so that it points to ** the address of the next instruction to be coded. */ void sqlite3VdbeJumpHere(Vdbe *p, int addr){ | | | 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 | } /* ** Change the P2 operand of instruction addr so that it points to ** the address of the next instruction to be coded. */ void sqlite3VdbeJumpHere(Vdbe *p, int addr){ if( addr>=0 ) sqlite3VdbeChangeP2(p, addr, p->nOp); } /* ** If the input FuncDef structure is ephemeral, then free it. If ** the FuncDef is not ephermal, then do nothing. */ |
︙ | ︙ |
Changes to test/analyze.test.
︙ | ︙ | |||
92 93 94 95 96 97 98 | ANALYZE main.t1; } } {0 {}} do_test analyze-1.11 { execsql { SELECT * FROM sqlite_stat1 } | | | | | | | 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 | ANALYZE main.t1; } } {0 {}} do_test analyze-1.11 { execsql { SELECT * FROM sqlite_stat1 } } {} do_test analyze-1.12 { catchsql { ANALYZE t1; } } {0 {}} do_test analyze-1.13 { execsql { SELECT * FROM sqlite_stat1 } } {} # Create some indices that can be analyzed. But do not yet add # data. Without data in the tables, no analysis is done. # do_test analyze-2.1 { execsql { CREATE INDEX t1i1 ON t1(a); ANALYZE main.t1; SELECT * FROM sqlite_stat1 ORDER BY idx; } } {} do_test analyze-2.2 { execsql { CREATE INDEX t1i2 ON t1(b); ANALYZE t1; SELECT * FROM sqlite_stat1 ORDER BY idx; } } {} do_test analyze-2.3 { execsql { CREATE INDEX t1i3 ON t1(a,b); ANALYZE main; SELECT * FROM sqlite_stat1 ORDER BY idx; } } {} # Start adding data to the table. Verify that the analysis # is done correctly. # do_test analyze-3.1 { execsql { INSERT INTO t1 VALUES(1,2); |
︙ | ︙ |
Changes to test/analyze6.test.
︙ | ︙ | |||
67 68 69 70 71 72 73 74 | # FROM clause. # do_test analyze6-1.2 { eqp {SELECT count(*) FROM cat, ev WHERE x=y} } {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | # FROM clause. # do_test analyze6-1.2 { eqp {SELECT count(*) FROM cat, ev WHERE x=y} } {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} # Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30 # If ANALYZE is run on an empty table, make sure indices are used # on the table. # do_test analyze6-2.1 { execsql { CREATE TABLE t201(x INTEGER PRIMARY KEY, y UNIQUE, z); CREATE INDEX t201z ON t201(z); ANALYZE; } eqp {SELECT * FROM t201 WHERE z=5} } {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}} do_test analyze6-2.2 { eqp {SELECT * FROM t201 WHERE y=5} } {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}} do_test analyze6-2.3 { eqp {SELECT * FROM t201 WHERE x=5} } {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} do_test analyze6-2.4 { execsql { INSERT INTO t201 VALUES(1,2,3); ANALYZE t201; } eqp {SELECT * FROM t201 WHERE z=5} } {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}} do_test analyze6-2.5 { eqp {SELECT * FROM t201 WHERE y=5} } {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}} do_test analyze6-2.6 { eqp {SELECT * FROM t201 WHERE x=5} } {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} do_test analyze6-2.7 { execsql { INSERT INTO t201 VALUES(4,5,7); INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201; INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201; INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201; ANALYZE t201; } eqp {SELECT * FROM t201 WHERE z=5} } {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}} do_test analyze6-2.8 { eqp {SELECT * FROM t201 WHERE y=5} } {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}} do_test analyze6-2.9 { eqp {SELECT * FROM t201 WHERE x=5} } {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} finish_test |
Changes to test/where3.test.
︙ | ︙ | |||
218 219 220 221 222 223 224 225 226 227 228 229 230 231 | # if held until an inner loop. # do_execsql_test where3-3.0 { CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); CREATE INDEX t301c ON t301(c); INSERT INTO t301 VALUES(1,2,3); CREATE TABLE t302(x, y); ANALYZE; explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { 0 0 0 {SCAN TABLE t302 (~1 rows)} 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } do_execsql_test where3-3.1 { | > | 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 | # if held until an inner loop. # do_execsql_test where3-3.0 { CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); CREATE INDEX t301c ON t301(c); INSERT INTO t301 VALUES(1,2,3); CREATE TABLE t302(x, y); INSERT INTO t302 VALUES(4,5); ANALYZE; explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { 0 0 0 {SCAN TABLE t302 (~1 rows)} 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } do_execsql_test where3-3.1 { |
︙ | ︙ | |||
336 337 338 339 340 341 342 343 | AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } | < | 337 338 339 340 341 342 343 344 345 | AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test |