diff options
| -rw-r--r-- | manifest | 14 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/where.c | 13 | ||||
| -rw-r--r-- | test/autoindex3.test | 58 |
4 files changed, 79 insertions, 8 deletions
@@ -1,5 +1,5 @@ -C Fix\sa\smissing\sspace\sin\sa\s"wheretrace"\scomment.\s\sNo\schanges\sto\sproduction\scode. -D 2014-06-17T17:00:42.928 +C Prevent\san\sautomatic\sindex\sfrom\staking\sthe\splace\sof\sa\sdeclared\sindex. +D 2014-06-18T15:11:54.897 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in ed5e4aae4799f724699d5509fac2977786414dbb F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -296,7 +296,7 @@ F src/vtab.c 21b932841e51ebd7d075e2d0ad1415dce8d2d5fd F src/wal.c 264df50a1b33124130b23180ded2e2c5663c652a F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4 F src/walker.c 11edb74d587bc87b33ca96a5173e3ec1b8389e45 -F src/where.c 7f59d3786a41ccb8d76148405f76c31658527c08 +F src/where.c 7b9e13cff91a2f14ac61e6a1bc3a83b5113e6298 F src/whereInt.h 929c1349b5355fd44f22cee5c14d72b3329c58a6 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 @@ -336,6 +336,7 @@ F test/auth3.test a4755e6a2a2fea547ffe63c874eb569e60a28eb5 F test/autoinc.test c58912526998a39e11f66b533e23cfabea7f25b7 F test/autoindex1.test 762ff3f8e25d852aae55c6462ca166a80c0cde61 F test/autoindex2.test 60d2fc6f38364308ce73a9beb01b47ded38697de +F test/autoindex3.test 8254f689c3241081fad52b7bea18ba53e07e14a2 F test/autovacuum.test 941892505d2c0f410a0cb5970dfa1c7c4e5f6e74 F test/autovacuum_ioerr2.test 8a367b224183ad801e0e24dcb7d1501f45f244b4 F test/avtrans.test 0252654f4295ddda3b2cce0e894812259e655a85 @@ -1178,7 +1179,8 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 38965484199153e3e5b999f5196c1d66e5296f60 -R 8d914acb455fa768c8bfdec56d9704e4 +P b500f2a09721b49c95a69c47a0205bc2375af2cd 4ece839d445ff578a449a339ab579a32e64c9d28 +R 1a9a8498bcd344a9075e008919374ef9 +T +closed 4ece839d445ff578a449a339ab579a32e64c9d28 U drh -Z d423ee25f70180aacb18e67f6dbb4662 +Z c3c3ed7d30575f073ff51d887a334d78 diff --git a/manifest.uuid b/manifest.uuid index 0e6051ab6..585f15924 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -b500f2a09721b49c95a69c47a0205bc2375af2cd
\ No newline at end of file +0a52bddd9db49b2b79fc24888b50023d0fe74f7b
\ No newline at end of file diff --git a/src/where.c b/src/where.c index 1865871ed..fd5831872 100644 --- a/src/where.c +++ b/src/where.c @@ -3923,6 +3923,17 @@ static WhereLoop **whereLoopFindLesser( ** 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 @@ -4047,7 +4058,7 @@ static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){ WhereLoop *pToDel; while( *ppTail ){ ppTail = whereLoopFindLesser(ppTail, pTemplate); - if( NEVER(ppTail==0) ) break; + if( ppTail==0 ) break; pToDel = *ppTail; if( pToDel==0 ) break; *ppTail = pToDel->pNextLoop; diff --git a/test/autoindex3.test b/test/autoindex3.test new file mode 100644 index 000000000..33053bba6 --- /dev/null +++ b/test/autoindex3.test @@ -0,0 +1,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 |