SQLite

Check-in [4ece839d44]
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
Timelines: family | ancestors | descendants | both | autoindex-improvements
Files: files | file ages | folders
SHA1: 4ece839d445ff578a449a339ab579a32e64c9d28
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
Unified Diff Show Whitespace Changes Patch
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
4050
4051
4052
4053
4054
4055
4056
4057
    /* 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( NEVER(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);







|







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