/ Check-in [4ece839d]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | autoindex-improvements
Files: files | file ages | folders
SHA1: 4ece839d445ff578a449a339ab579a32e64c9d28
User & Date: drh 2014-06-17 20:16:43
Context
2014-06-18
15:11
Prevent an automatic index from taking the place of a declared index. check-in: 0a52bddd 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: 4ece839d user: drh tags: autoindex-improvements
13:23
Add the autoindex2.test testing module. check-in: ffe3fea4 user: drh tags: autoindex-improvements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3918   3918       assert( p->rSetup==0 || pTemplate->rSetup==0 
  3919   3919                    || p->rSetup==pTemplate->rSetup );
  3920   3920   
  3921   3921       /* whereLoopAddBtree() always generates and inserts the automatic index
  3922   3922       ** case first.  Hence compatible candidate WhereLoops never have a larger
  3923   3923       ** rSetup. Call this SETUP-INVARIANT */
  3924   3924       assert( p->rSetup>=pTemplate->rSetup );
         3925  +
         3926  +    /* Any loop using an appliation-defined index (or PRIMARY KEY or
         3927  +    ** UNIQUE constraint) with one or more == constraints is better
         3928  +    ** than an automatic index. */
         3929  +    if( (p->wsFlags & WHERE_AUTO_INDEX)!=0
         3930  +     && (pTemplate->wsFlags & WHERE_INDEXED)!=0
         3931  +     && (pTemplate->wsFlags & WHERE_COLUMN_EQ)!=0
         3932  +     && (p->prereq & pTemplate->prereq)==pTemplate->prereq
         3933  +    ){
         3934  +      break;
         3935  +    }
  3925   3936   
  3926   3937       /* If existing WhereLoop p is better than pTemplate, pTemplate can be
  3927   3938       ** discarded.  WhereLoop p is better if:
  3928   3939       **   (1)  p has no more dependencies than pTemplate, and
  3929   3940       **   (2)  p has an equal or lower cost than pTemplate
  3930   3941       */
  3931   3942       if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */
................................................................................
  4043   4054       /* We will be overwriting WhereLoop p[].  But before we do, first
  4044   4055       ** go through the rest of the list and delete any other entries besides
  4045   4056       ** p[] that are also supplated by pTemplate */
  4046   4057       WhereLoop **ppTail = &p->pNextLoop;
  4047   4058       WhereLoop *pToDel;
  4048   4059       while( *ppTail ){
  4049   4060         ppTail = whereLoopFindLesser(ppTail, pTemplate);
  4050         -      if( NEVER(ppTail==0) ) break;
         4061  +      if( ppTail==0 ) break;
  4051   4062         pToDel = *ppTail;
  4052   4063         if( pToDel==0 ) break;
  4053   4064         *ppTail = pToDel->pNextLoop;
  4054   4065   #if WHERETRACE_ENABLED /* 0x8 */
  4055   4066         if( sqlite3WhereTrace & 0x8 ){
  4056   4067           sqlite3DebugPrintf("ins-del: ");
  4057   4068           whereLoopPrint(pToDel, pBuilder->pWC);

Added test/autoindex3.test.

            1  +# 2014-06-17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library.  The
           13  +# focus of this script is testing automatic index creation logic,
           14  +# and specifically that an automatic index will not be created that
           15  +# shadows a declared index.
           16  +#
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +# The t1b and t2d indexes are not very selective.  It used to be that
           22  +# the autoindex mechanism would create automatic indexes on t1(b) or
           23  +# t2(d), make assumptions that they were reasonably selective, and use
           24  +# them instead of t1b or t2d.  But that would be cheating, because the
           25  +# automatic index cannot be any more selective than the real index.
           26  +#
           27  +# This test verifies that the cheat is no longer allowed.
           28  +#
           29  +do_execsql_test autoindex3-100 {
           30  +  CREATE TABLE t1(a,b,x);
           31  +  CREATE TABLE t2(c,d,y);
           32  +  CREATE INDEX t1b ON t1(b);
           33  +  CREATE INDEX t2d ON t2(d);
           34  +  ANALYZE sqlite_master;
           35  +  INSERT INTO sqlite_stat1 VALUES('t1','t1b','10000 500');
           36  +  INSERT INTO sqlite_stat1 VALUES('t2','t2d','10000 500');
           37  +  ANALYZE sqlite_master;
           38  +  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d=b;
           39  +} {~/AUTO/}
           40  +
           41  +# Automatic indexes can still be used if existing indexes do not
           42  +# participate in == constraints.
           43  +#
           44  +do_execsql_test autoindex3-110 {
           45  +  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d>b AND x=y;
           46  +} {/AUTO/}
           47  +do_execsql_test autoindex3-120 {
           48  +  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d<b AND x=y;
           49  +} {/AUTO/}
           50  +do_execsql_test autoindex3-130 {
           51  +  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y;
           52  +} {/AUTO/}
           53  +do_execsql_test autoindex3-140 {
           54  +  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y;
           55  +} {/AUTO/}
           56  +
           57  +
           58  +finish_test