Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Prevent an automatic index from taking the place of a declared index. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | autoindex-improvements |
Files: | files | file ages | folders |
SHA1: |
4ece839d445ff578a449a339ab579a32 |
User & Date: | drh 2014-06-17 20:16:43.699 |
Context
2014-06-18
| ||
15:11 | Prevent an automatic index from taking the place of a declared index. (check-in: 0a52bddd9d user: drh tags: trunk) | |
2014-06-17
| ||
20:16 | Prevent an automatic index from taking the place of a declared index. (Closed-Leaf check-in: 4ece839d44 user: drh tags: autoindex-improvements) | |
13:23 | Add the autoindex2.test testing module. (check-in: ffe3fea47b user: drh tags: autoindex-improvements) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 | assert( p->rSetup==0 || pTemplate->rSetup==0 || p->rSetup==pTemplate->rSetup ); /* whereLoopAddBtree() always generates and inserts the automatic index ** case first. Hence compatible candidate WhereLoops never have a larger ** rSetup. Call this SETUP-INVARIANT */ assert( p->rSetup>=pTemplate->rSetup ); /* If existing WhereLoop p is better than pTemplate, pTemplate can be ** discarded. WhereLoop p is better if: ** (1) p has no more dependencies than pTemplate, and ** (2) p has an equal or lower cost than pTemplate */ if( (p->prereq & pTemplate->prereq)==p->prereq /* (1) */ | > > > > > > > > > > > | 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 | assert( p->rSetup==0 || pTemplate->rSetup==0 || p->rSetup==pTemplate->rSetup ); /* whereLoopAddBtree() always generates and inserts the automatic index ** case first. Hence compatible candidate WhereLoops never have a larger ** rSetup. Call this SETUP-INVARIANT */ assert( p->rSetup>=pTemplate->rSetup ); /* Any loop using an appliation-defined index (or PRIMARY KEY or ** UNIQUE constraint) with one or more == constraints is better ** than an automatic index. */ if( (p->wsFlags & WHERE_AUTO_INDEX)!=0 && (pTemplate->wsFlags & WHERE_INDEXED)!=0 && (pTemplate->wsFlags & WHERE_COLUMN_EQ)!=0 && (p->prereq & pTemplate->prereq)==pTemplate->prereq ){ break; } /* If existing WhereLoop p is better than pTemplate, pTemplate can be ** discarded. WhereLoop p is better if: ** (1) p has no more dependencies than pTemplate, and ** (2) p has an equal or lower cost than pTemplate */ if( (p->prereq & pTemplate->prereq)==p->prereq /* (1) */ |
︙ | ︙ | |||
4043 4044 4045 4046 4047 4048 4049 | /* We will be overwriting WhereLoop p[]. But before we do, first ** go through the rest of the list and delete any other entries besides ** p[] that are also supplated by pTemplate */ WhereLoop **ppTail = &p->pNextLoop; WhereLoop *pToDel; while( *ppTail ){ ppTail = whereLoopFindLesser(ppTail, pTemplate); | | | 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 | /* We will be overwriting WhereLoop p[]. But before we do, first ** go through the rest of the list and delete any other entries besides ** p[] that are also supplated by pTemplate */ WhereLoop **ppTail = &p->pNextLoop; WhereLoop *pToDel; while( *ppTail ){ ppTail = whereLoopFindLesser(ppTail, pTemplate); if( ppTail==0 ) break; pToDel = *ppTail; if( pToDel==0 ) break; *ppTail = pToDel->pNextLoop; #if WHERETRACE_ENABLED /* 0x8 */ if( sqlite3WhereTrace & 0x8 ){ sqlite3DebugPrintf("ins-del: "); whereLoopPrint(pToDel, pBuilder->pWC); |
︙ | ︙ |
Added test/autoindex3.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 | # 2014-06-17 # # 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. # #************************************************************************* # # This file implements regression tests for SQLite library. The # focus of this script is testing automatic index creation logic, # and specifically that an automatic index will not be created that # shadows a declared index. # set testdir [file dirname $argv0] source $testdir/tester.tcl # The t1b and t2d indexes are not very selective. It used to be that # the autoindex mechanism would create automatic indexes on t1(b) or # t2(d), make assumptions that they were reasonably selective, and use # them instead of t1b or t2d. But that would be cheating, because the # automatic index cannot be any more selective than the real index. # # This test verifies that the cheat is no longer allowed. # do_execsql_test autoindex3-100 { CREATE TABLE t1(a,b,x); CREATE TABLE t2(c,d,y); CREATE INDEX t1b ON t1(b); CREATE INDEX t2d ON t2(d); ANALYZE sqlite_master; INSERT INTO sqlite_stat1 VALUES('t1','t1b','10000 500'); INSERT INTO sqlite_stat1 VALUES('t2','t2d','10000 500'); ANALYZE sqlite_master; EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d=b; } {~/AUTO/} # Automatic indexes can still be used if existing indexes do not # participate in == constraints. # do_execsql_test autoindex3-110 { EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d>b AND x=y; } {/AUTO/} do_execsql_test autoindex3-120 { EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d<b AND x=y; } {/AUTO/} do_execsql_test autoindex3-130 { EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y; } {/AUTO/} do_execsql_test autoindex3-140 { EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y; } {/AUTO/} finish_test |